BASIC INSERT AND SELECT COMMANDS FOR SQLITE IN .NET

1. Our "SAVE" button click code looks like the following:
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)";
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);
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. }
39. }
41. Finally, our "DISPLAY" button click handler code looks like this:
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. {
59. }
60. finally
61. {
62. cmd.Dispose();
63. sqLiteConnection1.Close();
64. }
66. }