Windows Forms – Databases

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
);

Connected Data Access Architecture

Activity

Sample Code available at http://online.ase.ro – “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:

public 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

public void AddParticipant(Participant participant)
{
	var queryString = "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(queryString, connection);
		var lastNameParameter = new SQLiteParameter("@lastName");
		lastNameParameter.Value = participant.LastName;
		var firstNameParameter = new SQLiteParameter("@firstName");
		firstNameParameter.Value = participant.FirstName;
		var birthDateParameter = new SQLiteParameter("@birthDate");
		birthDateParameter.Value = participant.BirthDate;

		command.Parameters.Add(lastNameParameter);
		command.Parameters.Add(firstNameParameter);
		command.Parameters.Add(birthDateParameter);

		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

public void LoadParticipants()
{
	const string stringSql = "SELECT * FROM Participant";

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

		var command = new SQLiteCommand(stringSql, connection);

		SQLiteDataReader sqlReader = command.ExecuteReader();
		try
		{
			while (sqlReader.Read())
			{
				_participants.Add(new Participant((long) sqlReader["Id"], (string) sqlReader["LastName"],
					(string) sqlReader["FirstName"], DateTime.Parse((string) sqlReader["BirthDate"])));
			}
		}
		finally
		{
			// Always call Close when done reading.
			sqlReader.Close();
		}
	}
}

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

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

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

		//Remove from the database
		SQLiteCommand command = new SQLiteCommand(stringSql, connection);

		var idParameter = new SQLiteParameter("@id");
		idParameter.Value = participant.Id;
		command.Parameters.Add(idParameter);

		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
		{
			DeleteParticipant((Participant) lvParticipants.SelectedItems[0].Tag);
			DisplayParticipants();
		}
		catch (Exception ex)
		{
			MessageBox.Show(ex.Message);
		}
	}
}

Implement the edit functionality in order to allow the user to modify the data, for previously entered participants

Disconnected Data Access Architecture

Activity

Sample Code available at http://online.ase.ro – “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
}

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

Contents

Creating the database

Connected Data Access Architecture

Disconnected Data Access Architecture

Files

10 - WinForms - Databases - SQLite.md

Latest commit

History

10 - WinForms - Databases - SQLite.md

File metadata and controls

Windows Forms – Databases

Contents

Creating the database

Connected Data Access Architecture

Disconnected Data Access Architecture