Basic insert and select commands for SQLite in .NET


/ Published in: C#
Save to your folder(s)



Copy this code and paste it in your HTML
  1. Our "SAVE" button click code looks like the following:
  2.  
  3. private void button1_Click(object sender, EventArgs e)
  4. {
  5. SQLiteTransaction trans;
  6. string SQL = "INSERT INTO PERSONS (ID, FIRSTNAME,LASTNAME,EMAIL,PHONE) VALUES";
  7. SQL += "(@ID, @firstname, @lastname, @email, @phone)";
  8.  
  9. SQLiteCommand cmd = new SQLiteCommand(SQL);
  10. cmd.Parameters.AddWithValue("@ID", Guid.NewGuid());
  11. cmd.Parameters.AddWithValue("@firstname", this.txtFirst.Text);
  12. cmd.Parameters.AddWithValue("@lastname", this.txtLast.Text);
  13. cmd.Parameters.AddWithValue("@email", this.txtEmail.Text);
  14. cmd.Parameters.AddWithValue("@phone", this.txtPhone.Text);
  15.  
  16. cmd.Connection = sqLiteConnection1;
  17. sqLiteConnection1.Open();
  18. trans = sqLiteConnection1.BeginTransaction();
  19. int retval = 0;
  20. try
  21. {
  22. retval= cmd.ExecuteNonQuery();
  23. if (retval == 1)
  24. MessageBox.Show("Row inserted!");
  25. else
  26. MessageBox.Show("Row NOT inserted.");
  27. }
  28. catch (Exception ex)
  29. {
  30. trans.Rollback();
  31. }
  32. finally
  33. {
  34. trans.Commit();
  35. cmd.Dispose();
  36. sqLiteConnection1.Close();
  37. }
  38.  
  39. }
  40.  
  41. Finally, our "DISPLAY" button click handler code looks like this:
  42.  
  43. private void button2_Click(object sender, EventArgs e)
  44. {
  45. string SQL = "SELECT * FROM PERSONS";
  46. SQLiteCommand cmd = new SQLiteCommand(SQL);
  47. cmd.Connection = sqLiteConnection1;
  48. SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
  49. DataSet ds = new DataSet();
  50. try
  51. {
  52. da.Fill(ds);
  53. DataTable dt = ds.Tables[0];
  54. this.dataGridView1.DataSource = dt;
  55. }
  56. catch (Exception ex)
  57. {
  58.  
  59. }
  60. finally
  61. {
  62. cmd.Dispose();
  63. sqLiteConnection1.Close();
  64. }
  65.  
  66. }

URL: http://www.eggheadcafe.com/tutorials/aspnet/20f7912e-6fa7-40eb-b31b-b6f46d4f2c6a/get-started-with-sqlite-a.aspx

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.