Windows Forms – Databases - SQLite

1. Creating the database
1. Connected Data Access Architecture
1. Disconnected Data Access Architecture
1. Bibliography

1. Objectives

perform CRUD (Create, Read, Update and Delete) operations using the connected data access approach;
perform CRUD (Create, Read, Update and Delete) operations using the disconnected data access approach;
working with multiple related entities;
basic understanding of SQL Injection attacks.

2. Documentation

ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain.

Further reading: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-overview

SQLite is probably the most used database engine in the world.

Want to learn more about SQLite? Check the official website: https://www.sqlite.org

3. Creating the database

Activity

Install DB Browser for SQLite http://sqlitebrowser.org/
Choose the option “New Database”

Add a new table as follows (you can also use the designer)

CREATE TABLE `Participant` (
	`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	`LastName` TEXT,
	`FirstName` TEXT,
	`BirthDate` TEXT
);

2. Connected Data Access Architecture

Activity

Full source code available, check the DatabaseCommandSQLite sample

Create a copy of the “ListViewBasicSample” project and name it “DatabaseCommandSQLite”
Add SQLite libraries using NuGet (recommended) or directly from the website http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki
1. Open the NuGet Package Manager by right clicking on the “References” node in the “Solution Explorer” window, and choosing the “Manage NuGet Packages” option, as shown below
2. Go to the “Browse” tab of the “NuGet Package Manager” and search for “sqlite”. Choose the “System.Data.SQLite.Core” package and hit the “Install” button
3. The package will be downloaded and Installed. A new reference to “System.Data.SQLite” will be automatically added to the “References” node, as shown below

Add the database connection string, as an attribute (ConnectionString) of the MainForm class as follows:

public partial class MainForm : Form
{
	#region Attributes
	//Best practice
	//Define the connection string in the settings of the application
	//private string connectionString = Properties.Settings.Default.Database;
	private const string ConnectionString = "Data Source=database.db";
	private readonly List<Participant> _participants;
	#endregion
	
	. . .
}

Set the Tag property for the ListViewItems as follows:

private void DisplayParticipants()
{
	lvParticipants.Items.Clear();
	
	foreach (Participant participant in _participants)
	{
		var listViewItem = new ListViewItem(participant.LastName);
		listViewItem.SubItems.Add(participant.FirstName);
		listViewItem.SubItems.Add(participant.BirthDate.ToShortDateString());
		
		listViewItem.Tag = participant;
		
		lvParticipants.Items.Add(listViewItem);
	}
}

Add the method that will be used to insert new participants in the database

private void AddParticipant(Participant participant)
{
	var query = "insert into Participant(LastName, FirstName, BirthDate)" +
						" values(@lastName,@firstName,@birthDate);  " +
						"SELECT last_insert_rowid()";

	using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
	{
		connection.Open();

		//1. Add the new participant to the database
		var command = new SQLiteCommand(query, connection);
		command.Parameters.AddWithValue("@lastName", participant.LastName);
		command.Parameters.AddWithValue("@firstName", participant.FirstName);
		command.Parameters.AddWithValue("@birthDate", participant.BirthDate);

		participant.Id = (long)command.ExecuteScalar();

		//2. Add the new participants to the local collection
		_participants.Add(participant);
	}
}

Change the btnAdd_Click event handler as follows:

private void btnAdd_Click(object sender, EventArgs e)
{
	var lastName = tbLastName.Text;
	var firstName = tbFirstName.Text;
	var birthDate = dtpBirthDate.Value;
	
	var participant = new Participant(lastName, firstName, birthDate);
	
	try
	{
		AddParticipant(participant);
		DisplayParticipants();
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}
}

Add the method that will be used to get the existing participants from the database

private void LoadParticipants()
{
	const string query = "SELECT * FROM Participant";

	using(SQLiteConnection connection = new SQLiteConnection(ConnectionString))
	{
		connection.Open();

		var command = new SQLiteCommand(query, connection);

		using (SQLiteDataReader reader = command.ExecuteReader())
		{
			while (reader.Read())
			{
				long id = (long)reader["Id"];
				string lastName = (string)reader["LastName"];
				string firstName = (string)reader["FirstName"];
				DateTime birthDate = DateTime.Parse((string)reader["BirthDate"]);

				Participant participant = new Participant(id, lastName, firstName, birthDate);
				_participants.Add(participant);
			}
		}
	}
}

Handle the Load events of the MainForm class as follows:

private void MainForm_Load(object sender, EventArgs e)
{
	try
	{
		LoadParticipants();
		DisplayParticipants();
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}
}

Add the method that will be used to delete existing participants from the database

private void DeleteParticipant(Participant participant)
{
	const string query = "DELETE FROM Participant WHERE Id=@id";

	using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
	{
		connection.Open();

		//Remove from the database
		SQLiteCommand command = new SQLiteCommand(query, connection);
		command.Parameters.AddWithValue("@id", participant.Id);

		command.ExecuteNonQuery();

		//Remove from the local copy
		_participants.Remove(participant);
	}
}

Handle the “Delete” button as follows:

private void btnDelete_Click(object sender, EventArgs e)
{
	if (lvParticipants.SelectedItems.Count == 0)
	{
		MessageBox.Show("Choose a participant");
		return;
	}
	
	if (MessageBox.Show("Are you sure?", "Delete participant", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
	{
		try
		{
			ListViewItem selectedItem = lvParticipants.SelectedItems[0];
			Participant participant = (Participant)selectedItem.Tag;

			DeleteParticipant(participant);

			DisplayParticipants();
		}
		catch (Exception ex)
		{
			MessageBox.Show(ex.Message);
		}
	}
}

Why do we use command parameters instead of building the query using string concatenation instead?

Read more about the SQL Injection attack at: https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-injection

Assignments (for you to try) 🎮

Implement the edit functionality in order to allow the user to modify the data, for previously entered participants
Try to implement an SQL Injection attack by modifing the insert query in order to use string concatenation, instead of command parameters.
Allow the user to choose the competition in which the participant is going to run as shown below. Define a Race (RaceId: int or long, Name:string) class and add a property RaceId to the Participant class (the value of the property will be persisted in the database).

Note: For the complete sample (using MSAccess instead), check the "DatabaseCommandMSAccess2Entities" project in the "code samples" folder. For the complete sample (using SQL Server instead), check the "DatabaseCommandSQLServer2Entities" project in the "code samples" folder.

(Optional) Further study: Check how Dapper (a popular, but relatively simple ORM) handles table joins: https://stackoverflow.com/a/7478958 and https://www.learndapper.com/relationships
Replace the ListView control used in the MainForm with a DataGridView control. Make sure that the edit and delete functionalities work correctly.

Hint: you can either use databinding (easier, better - recommended) or you can work directly with the Rows property of the DataGridView.

Note: The "DatabaseCommandSQLiteDataGridView" project in the "code samples" folder uses the DataGridView control (databinding) and implements the delete functionality.
(Optional) Rewrite the example using Dapper (https://github.com/DapperLib/Dapper) as an ORM.

3. (Optional) Disconnected Data Access Architecture

Activity

Full source code available, check the DatabaseDataAdapterSQLite sample

Create a copy of the “BasicListView” project and name it “DatabaseDataAdapterSQLite”
Replace the “ListView” control with a “DataGrid” control (Name: dgvParticipants)

Modify the “MainForm” class as follows:

public partial class MainForm : Form
{
    private readonly SQLiteConnection _dbConnection ;
	private readonly SQLiteDataAdapter _dbDataAdapter;
    private readonly DataSet _dsParticipants;

    public MainForm()
    {
        InitializeComponent();

		//Best practice
		//Define the connection string in the settings of the application
		//var dbConnection = new SQLiteConnection(Properties.Settings.Default.Database);
		_dbConnection = new SQLiteConnection("Data Source = database.db");

		_dsParticipants = new DataSet();

        _dbDataAdapter = new SQLiteDataAdapter();

		var selectCommand = new SQLiteCommand("SELECT Id, LastName, FirstName, BirthDate FROM Participant", _dbConnection);
        _dbDataAdapter.SelectCommand = selectCommand;

		var deleteCommand = new SQLiteCommand(
			"DELETE FROM Participant WHERE Id = @Id", _dbConnection);
		deleteCommand.Parameters.Add(
			new SQLiteParameter("@Id",DbType.Int64, "Id"));
		_dbDataAdapter.DeleteCommand = deleteCommand;

		var insertCommand = new SQLiteCommand("INSERT INTO Participant (LastName, FirstName, BirthDate) VALUES (@LastName, @FirstName, @BirthDate);", _dbConnection);
		insertCommand.Parameters.Add(
			new SQLiteParameter("@LastName", DbType.String, "LastName"));
		insertCommand.Parameters.Add(
			new SQLiteParameter("@FirstName", DbType.String, "FirstName"));
		insertCommand.Parameters.Add(
			new SQLiteParameter("@BirthDate", DbType.String, "BirthDate"));
		_dbDataAdapter.InsertCommand = insertCommand;

		var updateCommand = new SQLiteCommand("UPDATE Participant SET LastName = @LastName, FirstName=@FirstName, BirthDate = @BirthDate WHERE Id = @Id", _dbConnection);
		updateCommand.Parameters.Add(
			new SQLiteParameter("@LastName", DbType.String, "LastName"));
		updateCommand.Parameters.Add(
			new SQLiteParameter("@FirstName", DbType.String, "FirstName"));
		updateCommand.Parameters.Add(
			new SQLiteParameter("@BirthDate", DbType.String, "BirthDate"));
		updateCommand.Parameters.Add(
			new SQLiteParameter("@Id", DbType.Int64, "Id"));
		_dbDataAdapter.UpdateCommand = updateCommand;

        _dbDataAdapter.RowUpdated += _dbDataAdapter_RowUpdated;
	}

	#region Events
	private void MainForm_Load(object sender, EventArgs e)
	{
		try
		{
			_dbDataAdapter.Fill(_dsParticipants, "Participant");
		}
		catch (Exception ex)
		{
			MessageBox.Show(ex.Message);
		}

		//DataBinding Grid
		dgvParticipants.DataSource = _dsParticipants.Tables["Participant"];
		//dgvParticipants.Columns["Id"].Visible = false;
	}

	private void btnAdd_Click(object sender, EventArgs e)
	{
		DataRow newParticipantRow = 
			_dsParticipants.Tables["Participant"].NewRow();

		newParticipantRow["LastName"] = tbLastName.Text;
		newParticipantRow["FirstName"] = tbFirstName.Text;
		newParticipantRow["BirthDate"] = dtpBirthDate.Value;

		_dsParticipants.Tables["Participant"].Rows.Add(newParticipantRow);
	}

	private void btnPersistChanges_Click(object sender, EventArgs e)
	{
		try
		{
			_dbDataAdapter.Update(_dsParticipants, "Participant");
			//_dsParticipants.AcceptChanges();
		}
		catch (Exception ex)
		{
			MessageBox.Show(ex.Message);
		}
	}

	private void _dbDataAdapter_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
	{
		//https://msdn.microsoft.com/en-us/library/ks9f57t0%28v=vs.110%29.aspx
		if (e.StatementType == StatementType.Insert)
		{
			var getIdCommand = new SQLiteCommand("SELECT last_insert_rowid()", _dbConnection);
			e.Row["Id"] = (long)getIdCommand.ExecuteScalar();
		}
	}
	#endregion
}

4. Bibliography

ADO.NET overview: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-overview
odbc namespace (for Microsoft Access): https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc
sqlclient namespace (for Microsoft SQL Server): https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

10 - WinForms - Databases - SQLite.md

10 - WinForms - Databases - SQLite.md

Windows Forms – Databases - SQLite

1. Objectives

2. Documentation

3. Creating the database

2. Connected Data Access Architecture

3. (Optional) Disconnected Data Access Architecture

4. Bibliography

Files

10 - WinForms - Databases - SQLite.md

Latest commit

History

10 - WinForms - Databases - SQLite.md

File metadata and controls

Windows Forms – Databases - SQLite

1. Objectives

2. Documentation

3. Creating the database

2. Connected Data Access Architecture

3. (Optional) Disconnected Data Access Architecture

4. Bibliography