How to Insert and Update in SQLite using SQLite-net

Question

I have the following :

1) SQL Server in Backend Server:

tblCustomer

ID   CompanyName  Group      Product group   GST
--   -----------  -------    -------------   ----
1    The One       Exp        A               4    
2    The One       Exp        A               8     
.
.
20

2) SQLite-DB inside Tablet device.


tblCustomer

ID   CompanyName  Group      Product group   GST
--   -----------  -------    -------------   ----
1    The One       Exp        A               4    
2    The One       Exp        A               8 
.    The One
.
20  The One

3) I use webservice to get the Data from the Server and insert records into SQLite Db using Sqlite-Net api using below Code


The Problems:

1). How to I update the tblCustomer in SQLite for changes below:

Note: ID in SQL sever and Sqlite ARE not the same.


ID   CompanyName  Group      Product group   GST
--   -----------  -------    -------------   ----
1    The One       Exp        A               6   < -- before it was 4


2) Someone add data in SQL Server and the total record is 21 now.

 How to add this record in SQLite tbl customer?


foreach ( var client in Customers)
{
 InsertNewCustomer(client.Company, client.Group, client.ProductGroup, client.GST)
}


private void InsertNewCustomer(string Company,string Grp, string PGrp, int Gst)
{


1) How to create SQL Statement for this case?

 var existingCustomer = (db2.Table<Customer>().Where(c => c.No == Company)) ???

 if (existingCustomer != null)
  {

     ??-- how to handle?


     int success = db2.Update(existingCustomer);

   }
   else
   {
      int success = db2.Insert(new Customer()
      {
          Name = Company,
          Group = Grp,
          ProductGroup = PGrp,
          GST = Gst

      });
    }

}

Dmitry Ilukhin Dmitry Ilukhin · Accepted Answer · 2014-01-22T05:49:46

1) UPDATE tblCustomer SET GST=6 WHERE ID=1;

2) You need to execute SQLite statement like this: INSERT INTO tblCustomer(CompanyName, Group, [Product group], GST) VALUES('Newcompany', 'EXP', 'A', 2);

3) Same as 1), but UPDATE tblCustomer SET CompanyName='Newcompany', Group='EXP', [Product group]='A', GST=6 WHERE ID=1;

Of course you must replace 'Newcompany', 'EXP', ID=1 ... with your actual data

How to Insert and Update in SQLite using SQLite-net

1 Answers