Inserting and updating data grid row in the linked db table using sqlite, wpf and C#

Question

My sqlite code for linking db table to datagrid is:

 sqlitecon.Open();
 string Query2 = "Select * from Security_details "; 
 SQLiteCommand createCommand2 = new SQLiteCommand(Query2, sqlitecon);  createCommand2.ExecuteNonQuery();
 SQLiteDataAdapter dataAdp2 = new SQLiteDataAdapter(createCommand2);
 DataTable dt2 = new DataTable("Security_details");
 dataAdp.Fill(dt2);
 datagrid_security.ItemsSource = dt2.DefaultView;
 dataAdp2.Update(dt2); 
 sqlitecon.Close();

This code links db table to datagrid during form load event.

I want user to be able to:

add new rows on datagrid get inserted into db
edit existing rows on datagrid get updated into db.

Here in following query are my database fields

SQLiteCommand comm = new SQLiteCommand("update Security_details  " + 
      "set id=@id,Code=@Code,Description=@Description,Rate=@Rate," + 
      "Qty=@Qty,Amount=@Amount,Remarks=@Remarks where id=@id", sqlitecon);

Please tell me the set of commands for inserting and editing db table through datagrid ?Thanks

Note: Here I am not using any FrameWork...I have searched it on google i found many articles that use entity framework for doing this task on datagrid — getc he
– getc he, Commented Oct 13, 2013 at 16:24

Steve · Accepted Answer · 2013-10-13 16:37:12Z

3

Move the declaration of the SQLiteDataAdapter to the global class level and declare also a SQLiteCommandBuilder there. Then, before binding your data to the grid, initialize the SQLiteCommandBuilder that will create the UPDATE/INSERT/DELETE commands appropriate for your table automatically (provided that the SELECT returns the primary key of the table)

At this point, when you are ready to submit your data to the database, call the Update method of the global SQLiteDataAdapter instance

public class YourClass
{
     SQLiteDataAdapter dataAdp2;
     SQLiteCommandBuilder cmdBuilder;
     .....


     public void BindMyGrid()
     {
          sqlitecon.Open();
          string Query2 = "Select * from Security_details "; 
          SQLiteCommand createCommand2 = new SQLiteCommand(Query2, sqlitecon);                

          dataAdp2 = new SQLiteDataAdapter(createCommand2);
          cmdBuilder = new SQLiteCommandBuilder(dataAdp2);
          DataTable dt2 = new DataTable("Security_details");
          dataAdp2.Fill(dt2);
          datagrid_security.ItemsSource = dt2.DefaultView;
          sqlitecon.Close();
     }
     ....
     public void SubmitData()
     {
          dataAdp2.Update((datagrid_security.ItemsSource As DataView).Table);
     }
     ......
}

If you need a more in depth discussion of this process you could read this MSDN article, it is rather old, regards SQL Server and it is in VB.NET, but the base concept is the same.

answered Oct 13, 2013 at 16:37

Steve

217k22 gold badges242 silver badges296 bronze badges

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

2 Comments

getc he Over a year ago

public void SubmitData() { dataAdp2.Update((datagrid_security.ItemsSource As DataView).Table); } will this function edit as well as insert into db table?

Steve Over a year ago

Can't find now the exact reference page for SQLite, but this page for the base class DbDataAdapter explains the functionality expected for this method. Yes, it should update, insert and delete your MODIFIED/INSERTED and DELETED rows

Collectives™ on Stack Overflow

Inserting and updating data grid row in the linked db table using sqlite, wpf and C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related