Because Entity Framework screwed me with ADO.NET, we need to get ADO and SQLite together in holy .NET-ism? Is that a thing? Anyway, just to get everyone started, we are just going to get all the CRUD statements out of the way. Then we can focus on developing a better organized object in the next post. That way we can provide a simple, I need it know and don’t care if it is pretty solution and a better more refined solution for those needing something like that. Besides it gives us a change to code review something and those are always fun posts. We have not done a noob to pro kind of thing in a while.
Lets dig into the basics CRUD statements.
CRUD Code Behind for SQLite ADO.NET Solution
With the you should not code behind your code warning out of the way. Lets code behind our solution for those that want a fast and dirty solution.
First things first, we have to have a database. In this instance we are going to create a real simple database. We will call it Demo. It will have one table called DemoTable. In this table we will have three columns. One a auto number, primary key called pKey, a varchar(50), unique constraint, column called value 1 and a varchar(50) column called value2.
With that out of the way lets look at the interface.
Interface
Here we have an application. The select button will select all records if the pKey textbox is empty. If it had a value then it will pull the value. Similarly the delete key will delete all records if pkey is empty and will only delete the record specified.
In the INSERT/UPDATE we have a Pkey value that if empty causes a new row to be created and if populated will cause the stated record to update both its value 1 and value 2 fields.
Finally we have the listbox, lbResults, that will provide the results of the select statement.
Lets look at the code.
using System; using System.Windows.Forms; using Microsoft.Data.Sqlite; namespace WindowsFormsApp2 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void btnSELECT_Click(object sender, EventArgs e) { int PKey; lbResults.Items.Clear(); using (var connection = new SqliteConnection("Data Source=" + @"Y:\Projects\SPD\Demo.s3db")) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = @"SELECT * FROM DemoTable"; if (int.TryParse(txtSPKey.Text.Trim(), out PKey)) { command.CommandText += " WHERE pKey = $id"; command.Parameters.AddWithValue("$id", PKey); } using (var reader = command.ExecuteReader()) { while (reader.Read()) { var pkey1 = reader.GetString(0); var value1 = reader.GetString(1); var value2 = reader.GetString(2); lbResults.Items.Add(pkey1 + "\t" + value1 + "\t" + value2); } } } } private void btnINSERTUPDATE_Click(object sender, EventArgs e) { bool isInsert = true; string value1 = txtIUValue1.Text; string value2 = txtIUValue2.Text; int pkey; if(int.TryParse(txtIUPKey.Text.Trim(), out pkey)) { isInsert = false; } if (!isInsert) { using (var connection = new SqliteConnection("Data Source=" + @"Y:\Projects\SPD\Demo.s3db")) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = $"UPDATE DemoTable SET VALUE1 = \"{value1}\", VALUE2 = \"{value2}\" WHERE PKey = {pkey};"; var reader = command.ExecuteNonQuery(); } } else { using (var connection = new SqliteConnection("Data Source=" + @"Y:\Projects\SPD\Demo.s3db")) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = "INSERT INTO DemoTable (VALUE1, VALUE2) VALUES ($value1, $value2);"; command.Parameters.AddWithValue("$value1", value1); command.Parameters.AddWithValue("$value2", value2); var reader = command.ExecuteNonQuery(); } } } private void btnDelete_Click(object sender, EventArgs e) { int PKey; using (var connection = new SqliteConnection("Data Source=" + @"Y:\Projects\SPD\Demo.s3db")) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = @"DELETE FROM DemoTable"; if (int.TryParse(txtSPKey.Text.Trim(), out PKey)) { command.CommandText += " WHERE pKey = $id"; command.Parameters.AddWithValue("$id", PKey); } var reader = command.ExecuteNonQuery(); } } } }
So I have not optimized this code much as I wanted to make it as copy and paste-able as I could. First lets look at the btnSelect_Click.
btnSelect_Click
First we have a variable declaration for storing the pkey value as a integer. We have this because we will need to convert the text value from the textbox to a integer value. Next we clear the listbox of any previous results.
We have a using statement that creates the SQLite database connection object. We open the connection, and set the base command text. From there we check if we can successfully parse the text into an integer. If we can then we know it is an update operation. If we can’t then it is an insert. In the true result we append the where clause and assign a parameter to the command text. If it is false we do nothing and keep the base statement.
Then we hit the using that executes the reader object that executes are created command text. Then runs a while loop that will read the executed dataset. Se set the values for each field in the database and display then in the results list box.
btnINSERTUPDATE_Click
Here we set the boolean that will tell us which function we are doing, insert or update. We grab the text from the textboxes and setup our pKey integer, just like above.
We run the if statement that checks if we can parse the statement. If we can we set the isInsert to false because we will be updating. Otherwise we keep the default true value.
We check that boolean and if yes, execute the insert block of code and if no then execute the update block of code.
The insert block starts with a using statement that setups our database connection. We then open it, create the update command text and the run the nonQuery statement. Here I use the $ and allow us to insert the values into our string directly.
In the insert block, we go through the exact same process but this time add some parameters. I think this is the actual preferred way Microsoft will want you to handle this. You can decide which works for you better but I though I would show both ways of dealing with it.
btnDelete_Click
Like the al the other statements this one starts with the storage object for the pKey integer, and a using statement that sets up the connection. We open the connection and set the command text. We build the base delete statement then check if the pkey value from the textbox will parse. If it does then we know we want only a single record so we append the parameter using what I think Microsoft’s preferred way. Then we execute the non query.
Wrapping up the SQLite ADO.NET solution
This is ugly, not really defined code but it will get you started on your solution. We have duplicated code that is normally bad but I wanted to create simple copy and paste kind of thing for people who want it. In the next post we will start the improvements.
Despite it problems, this should get your started with your SQLite ADO.NET solution.
Let me know if this helps you down in the comments below.