How to drop table in Xamarin from sqlite database

Question

I am unable to drop tables completely from sqLite database in XAMARIN using command db.DropTable<tablename>. It gets dropped at first but appears again every time I restart the application. Also I have multiple tables with the same name and I need to drop them all. What should I do?

I am developing an android application in Visual Studio using XAMARIN and C#.

Unfortunately I created many tables with the same name. Now I need to drop them.

I am using following code to create tables and database:-

namespace Test
{
[Table("OrderDetails")]
public class OrderDetails
{
    [PrimaryKey, AutoIncrement, Column("_id")]
    public int Id { get; set; }
    [MaxLength(50)]
    public string payeename { get; set; }
    [MaxLength(100)]
    public string commodity { get; set; }

    public int give { get; set; }

    public int take { get; set; }
    public DateTime date { get; set; }
}

[Activity(Label = "Test", MainLauncher = true)]
public class MainActivity : Activity
{

    [Table("Items")]
    public class PayeeMaster
    {
        [PrimaryKey, AutoIncrement, Column("_id")]
        public int Id { get; set; }
        [MaxLength(8)]
        public string FirstName { get; set; }
        [MaxLength(8)]
        public string LastName { get; set; }
    }
    SQLiteConnection db;

    protected override void OnCreate(Bundle savedInstanceState)
    {
        base.OnCreate(savedInstanceState);

        // Set our view from the "main" layout resource
        SetContentView(Resource.Layout.Main);

        CreateDatabase();



    }


   public bool ifExists_Table(string tablename)
    {



        SQLiteCommand command = db.CreateCommand("SELECT COUNT(1) FROM SQLITE_MASTER WHERE TYPE = @TYPE AND NAME = @NAME");
        command.Bind("@TYPE", "table");
        command.Bind("@NAME", tablename);

        int result = command.ExecuteQuery<int>();
        return (result > 0);


    }

  public void CreateDatabase() {
     try
     {  
            string dbPath = Path.Combine(

System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal),
            "Master.db3");

            db = new SQLiteConnection(dbPath);

        }
        catch (IOException ex)
        {
            var reason = string.Format("The database failed to create - 
     reason {0}", ex.Message);

        }



            if (!ifExists_Table("PayeeMaster"))
            {

                db.CreateTable<PayeeMaster>();
            }

            else
            {
                var count = db.Table<PayeeMaster>().Count();

            }



        if (!ifExists_Table("OrderDetails"))
            {

                db.CreateTable<OrderDetails>();
            }

            else {
                var count = db.Table<OrderDetails>().Count();
            }


     }
 }

Could you post some source code of how you are using the database? Isn't it possible that you are creating the tables after first connection? Or how do the tables come into existence in the first place? — Martin Zikmund
– Martin Zikmund, Commented Apr 1, 2018 at 11:28
And which tables are you dropping at runtime? In the code you are clearly creating the payeemaster table at launch — Martin Zikmund
– Martin Zikmund, Commented Apr 2, 2018 at 8:01
Dropping the same table i.e. PayeeMaster (Added Code for the same). But it appears again after restarting the application — VIPUL SHARMA
– VIPUL SHARMA, Commented Apr 2, 2018 at 19:42
Because you are creating it again :-D! See - if (db.Table<PayeeMaster>().Count() ==0) db.CreateTable<PayeeMaster>(); ⬅️⬅️⬅️ — Martin Zikmund
– Martin Zikmund, Commented Apr 2, 2018 at 19:45

Martin Zikmund · Accepted Answer · 2018-04-02 20:19:45Z

0

I finally understand the problem, sorry for the confusion.

First of all - doing Table<T>().Count() doesn't actually check for table existence, but instead checks if the table contains any rows.

To actually check if the table exists, you will have to do an actual SQL query:

private bool DoesTableExist(string name)
{
    SQLiteCommand command = _connection.CreateCommand("SELECT COUNT(1) FROM SQLITE_MASTER WHERE TYPE = @TYPE AND NAME = @NAME");
    command.Bind("@TYPE", "table");
    command.Bind("@NAME", name);

    int result = command.ExecuteScalar<int>();
    return (result > 0);
}

answered Apr 2, 2018 at 20:19

Martin Zikmund

39.2k7 gold badges74 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

VIPUL SHARMA Over a year ago

I am getting zero as the value of result while the table still exists in the database.

VIPUL SHARMA Over a year ago

Is there anything missing?

Martin Zikmund Over a year ago

That is really weird :-O ! Could you upload the sqlite database with the two same tables somewhere? I really would like to see where the problem is

Martin Zikmund Over a year ago

I would prefer the sqlite file itself, could you post it somewhere?

VIPUL SHARMA Over a year ago

Where can I find that sqlite file ? Is there a standard path where it is stored

|

Collectives™ on Stack Overflow

How to drop table in Xamarin from sqlite database

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related