Introduction
Learn the basics for working with a single database table for displaying in a DataGridView along with CRUD operations with two separate paths working with Framework 4.8 which provides a path to better interact with data and with that a path to upgrade to .NET Core Framework. Having a good foundation interacting with data can also assist moving to web projects using a data provider or EF Core.
Note
The goal of this article is to have developers working Window Form projects to consider not writing data operations in each form, instead use a separate class for data operations. All code is presented in Framework 4.8 which started off as NET 8. Secondary goal, get the reader to consider using Dapper for performance with data operations and Goal three, have developer consider change notification and code that the developer never needs to touch controls but instead integrate BindingList and BindingSource.
Next logical step is take this code to NET 8 with EF Core, which is included in this project.
Source code
The initial idea was to show how a novice developer tends to approach working with a database but could not bring myself to do this. Instead, Form1 is several steps above and Form1UpDate goes beyond Form1.
Clone the following GitHub repository and work with DapperSimpleApp. Note that the repository has many other useful code samples that in some cases goes beyond that is shown in DapperSimpleApp.
I urge the reader to take time to
- Study the code to understand the benefits
- Step through the code with the debugger if something does not make sense.
How the average developer starts working with data
When someone starts working with data in Windows Forms they may start out with
A TableAdapter which makes working with data easy but as task complexity increase the seemingly easy TableAdapter although still easy which comes with experience for the new developer will becomes frustrating.
A DataAdapter which is the next step above a TableAdapter but unlike a TableAdapter requires knowledge of the underlying database tables. Most times the new developer does not have enough knowledge of working with databases which leads to frustration.
Using connection and command objects which provides full control to the developer which is not always suitable for the new developer.
All the above house data in DataSet and/or a DataTable containers which is fine but for this article we want a clear path to web development were a DataSet and/or DataTable containers can affect performance.
What about Entity Framework? This is a valid path but lessens the learning process for working with data. Once creating web and other project types that include Window Forms the choices are working with a data provider or Microsoft EF Core.
Code presented can easily be updated to EF Core with little effort.
Breaking bad habits
New developers working with Windows Forms unknowingly start off with a bad habit of the following.
❌ Writing all data operations in their forms
✔️ It is best to write data operations outside of a form for separating of concerns, when there are many forms that may need the same functionality and allows code to be used in other projects.
❌ Creating a single connection to a database at form level. Using a single connection outside of forms is fine which will be shown later.
❌ Touching for controls to get at data, for example, iterating rows and columns to integrate and/or manipulate data while when understanding how to setup data properly which is presented later in most cases one need not touch controls.
❌ Not validating data properly
✔️ there are native methods to validate form data while for this article a free third-party library will be introduced for validation.
❌ Not properly naming controls, all controls in the code samples have proper names. When a developer has perhaps ten TextBox controls named TextBox1 through TextBox10, their purpose for the time being is known but how about one year from now? Do you remember what TextBox5 is for?
❌ Not scoping variables, property and controls properly. For instance, attempting to access a control on a child form where the control modifiers property is set to private, the new developer will change the modifiers property to public. When accessing a control this way it can cause collisions with other controls, put to much into memory. Later in code one method is shown using delegates and events to pass data from controls in another form. Another method is to have a public readonly property that provides access to only what is needed, for example, the text in a TextBox rather than the entire TextBox.
Scenario
For learning, the project task are:
Display data in a DataGridView with the ability to perform in place editing, deleting the current row in the DataGridView. For editing, validation is done first, for deleting a record, a question in a dialog first ask permission to delete the record. In regards to deleting a record, there is always the possibility for performing a soft delete which is not done here but worth knowing about.
Add a new record to the database table and add the new record to the DataGridView without the need to refresh the DataGridView for the new record to show up. Many new developers tend to struggle with this and either refresh the DataGridView and even reload the entire form. Code presented will add a new record without a refresh and also perform validation.
IMPORTANT
What follows is for learning, not for creating an actual application as with an application we would not be showing the primary key or have some of the functionality like a reset table button although a current button may be needed for instance.
In the form below, first button is step one for working with data, can we connect? Some developers will write their code to display data and get stuck with connecting to the desired database. When first starting out, test the connection and once confirm a connection can be made, leave the code as later the connection may need to be validated again.
The second button is responsible for reading data from the database table and presenting in the DataGridView. Many developers will perform this in form constructor or form load event and does not work. If the loading of data works in a button and fails in an event than the code is sound and time to reevaluate were the loading of data happens or better, use Form Shown event while in some cases form load event is known to sometimes swallow runtime exceptions.
The third button shows how to get the current DataGridView row data without touching the DataGridView along with reading the current person data from the database table. Code presented is a must to understand which will be gone over later.
Next, the reset button allows for reverting the database table back to a fresh copy.
The Add button opens a child form that allows for adding a new record to the database table and the DataGridView along with proper validation. As coded clicking the add new button in the child form will add a new record and close the form. This may not be suitable in a real application and can be change by commenting out the form close method in the add new record button.
Data operation basics
Moving away from using DataSet/DataTable containers and using list instead.
For reference, a method to get data using a DataTable.
The following is for the next few samples.
private static string connectionString =
"Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=InsertExamples;Integrated Security=True;Encrypt=False";
Using a DataTable.
public static DataTable ConventionalDataTable()
{
var statement = "SELECT Id,FirstName,LastName,BirthDate FROM dbo.Person;";
using (var cn = new SqlConnection(connectionString))
{
using (var cmd = new SqlCommand(statement, cn))
{
DataTable table = new DataTable();
cn.Open();
table.Load(cmd.ExecuteReader());
return table;
}
}
}
Now for moving to a light weight list.
The model which has properties matching columns in the database table.
internal class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime BirthDate { get; set; }
}
When moving to NET8 we can use DateOnly for BirthDate since the column is date in the database table.
internal class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateOnly BirthDate { get; set; }
}
Conventional code requires connection and command objects for reading all people records. In the sample below, a while statement loops through the data. Countless developers have trouble with this, for example, getting the method to get data back or indexing the reader to set a property.
public static List<Person> ConventionalPeopleList()
{
List<Person> list = new List<Person>();
var statement = "SELECT Id,FirstName,LastName,BirthDate FROM dbo.Person;";
using (var cn = new SqlConnection(connectionString))
{
using (var cmd = new SqlCommand(statement,cn))
{
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
list.Add(new Person()
{
Id = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
BirthDate = reader.GetDateTime(3)
});
}
}
}
return list;
}
Next, read one record by primary key.
For each parameter, in this case there is only one for the primary key a parameter needs to be added to the command object using Parameters.Add and there is also Parameters.AddWithValue which infers the type which sometimes gets it wrong.
public static Person ConventionalSinglePerson(int id)
{
var statement = "SELECT Id,FirstName,LastName,BirthDate FROM dbo.Person WHERE Id = @Id";
using (var cn = new SqlConnection(connectionString))
{
using (var cmd = new SqlCommand(statement, cn))
{
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
cn.Open();
var reader = cmd.ExecuteReader();
reader.Read();
Person person = new Person()
{
Id = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
BirthDate = reader.GetDateTime(3)
};
return person;
}
}
}
In both samples to get all records and to get a single record works fine but there is a better way to perform the same work using NuGet package Dapper.
In both samples to get all records and to get a single record works fine but there is a better way to perform the same work using NuGet package Dapper. Dapper is extremely easy to use and built with performance in mind. In the source code provided the basic operations are covered, to take things to the next level and read the information at GitHub, the following page and other code samples in the following repository.
Also for those using Visual Studio 2022 NET 8
- Using Dapper - C# Part 1
- Using Dapper - C# Part 1-A
- Multiple Result Sets for SQL-Server (C#) including Dapper
And will have an advance level Dapper article shortly.
Okay, lets check out Dapper methods for get all records and a single record. Note the absence of a command object and a method to open the connection. Dapper handles opening an closing a connection for you.
public static List<Person> DapperPeopleList()
{
var statement = "SELECT Id,FirstName,LastName,BirthDate FROM dbo.Person;";
using (var cn = new SqlConnection(connectionString))
{
return cn.Query<Person>(statement).AsList();
}
}
public static Person DapperSingPerson(int id)
{
var statement = "SELECT Id,FirstName,LastName,BirthDate FROM dbo.Person WHERE Id = @Id";
using (var cn = new SqlConnection(connectionString))
{
return cn.QueryFirst<Person>(statement, new { Id = id });
}
}
Conventional form work
When a developer starts out data operations are typically placed in button click events which means they are siloed and only useful as shown in Form1. Unlike code in Form1 usually a developer will populate data directly into a DataGridView which in turn means they must always interact with the DataGridView rather than data.
When dealing with a list of objects, in this case the Person model shown above its better to set the DataSource to the list.
Refactoring the Person class for change notification
Next step, refactor the Person model as shown below to provide change notification back to a DataGridView along with other bound controls such as a TextBox.
This is done by implementing INotifyPropertyChanged interface.
If using Jetbrains ReSharper, ReSharper will do the heavy lifting for you for setting up each property.
public class Person : INotifyPropertyChanged
{
private int _id;
private string _firstName;
private string _lastName;
private DateTime _birthDate;
public int Id
{
get => _id;
set
{
if (value == _id) return;
_id = value;
OnPropertyChanged();
}
}
public string FirstName
{
get => _firstName;
set
{
if (value == _firstName) return;
_firstName = value;
OnPropertyChanged();
}
}
public string LastName
{
get => _lastName;
set
{
if (value == _lastName) return;
_lastName = value;
OnPropertyChanged();
}
}
public DateTime BirthDate
{
get => _birthDate;
set
{
if (value.Equals(_birthDate)) return;
_birthDate = value;
OnPropertyChanged();
}
}
public override string ToString() => $"{FirstName} {LastName}";
public event PropertyChangedEventHandler PropertyChanged;
protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
{
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
}
protected bool SetField<T>(ref T field, T value, [CallerMemberName] string propertyName = null)
{
if (EqualityComparer<T>.Default.Equals(field, value)) return false;
field = value;
OnPropertyChanged(propertyName);
return true;
}
}
BindingSource/BindingList<T>
A BindingList allows easy access to your data along with a BindingSource.
When loading data from the database table as a list, assign the list to the BindingList.
var operations = new PersonOperations();
var list = operations.GetList();
_bindingList = new SortableBindingList<Person>(list);
_bindingSource.DataSource = _bindingList;
dataGridView1.DataSource = _bindingSource;
Note in this case a special BindingList is used which is included in source code. A standard BindingList does not know how to sort data when clicking a column header in a DataGridView whole SortableBindingList
does.
Once the BindingList is set, the BindingSource.DataSource is assigned to the BindingList. Now to access the current row in the DataGridView, the following code uses the BindingSource to get the position of the current row for indexing into the BindingList.
Person currentPerson = _bindingList[_bindingSource.Position];
Example, when clicking the ❌ button in the BindingNavigator to delete a record. A dialog is presented with the first and last name of the person slated to be removed.
private void BindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
if (_bindingSource.Current != null)
{
var currentPerson = _bindingList[_bindingSource.Position];
if (Dialogs.Question($"Delete {currentPerson.FirstName} {currentPerson.LastName} ?"))
{
var operations = new PersonOperations();
if (operations.Delete(currentPerson.Id))
{
_bindingSource.RemoveCurrent();
bindingNavigatorDeleteItem.Enabled = _bindingList.Count > 0;
}
else
{
MessageBox.Show("Failed to remove record");
}
}
}
}
By the way, one step below above, same method but with the data operation present in the button click event. Normally the SQL is there too but for this sample the query resides in a separate class that is resuable.
private void BindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
if (_bindingSource.Current != null)
{
var currentPerson = _bindingList[_bindingSource.Position];
if (Dialogs.Question($"Delete {currentPerson.FirstName} {currentPerson.LastName} ?"))
{
using (var cn = new SqlConnection(connectionString))
{
var affected = cn.Execute(SqlStatements.RemovePerson, new { currentPerson.Id });
if (affected == 1)
{
_bindingSource.RemoveCurrent();
bindingNavigatorDeleteItem.Enabled = _bindingList.Count > 0;
}
else
{
MessageBox.Show("Failed to remove record");
}
}
}
}
}
SQL Statements
For this article, SQL statements are in a class as per below. Some will argue that these statements should be in stored procedures and can be with virtually much change, add the following to any Dapper method CommandType.StoredProcedure.
namespace DapperSimpleApp.Classes
{
/// <summary>
/// Collection of SQL statements, feel free to move to stored procedures.
/// </summary>
/// <remarks>
/// If at some point this code is ported to NET8+, refactor strings to raw string literals
/// </remarks>
internal class SqlStatements
{
/// <summary>
/// Read all people from database
/// </summary>
public static string GetAllPeople = "SELECT Id,FirstName,LastName,BirthDate FROM dbo.Person;";
/// <summary>
/// Read a person by primary key
/// </summary>
public static string GetPerson = "SELECT Id,FirstName,LastName,BirthDate FROM dbo.Person WHERE Id = @Id";
/// <summary>
/// Update a person by primary key
/// </summary>
public static string UpdatePerson =
"UPDATE [dbo].[Person] SET [FirstName] = @FirstName,[LastName] = @LastName,[BirthDate] = @BirthDate WHERE Id = @Id";
/// <summary>
/// Remove a person by primary key
/// </summary>
public static string RemovePerson = "DELETE FROM dbo.Person WHERE Id = @Id;";
/// <summary>
/// Insert a new person, return the new primary key
/// </summary>
public static string InsertPerson =
"INSERT INTO dbo.Person (FirstName,LastName,BirthDate) " +
"VALUES (@FirstName, @LastName, @BirthDate);" +
"SELECT CAST(scope_identity() AS int);";
/// <summary>
/// For resetting Person table to default data. Also demonstrates and
/// easy way to add multiple records at once. Note that Dapper does
/// a foreach internally to perform the add operation and is not a bulk
/// insert operation so use this for small additions only.
/// </summary>
public static string ResetTable =
"INSERT INTO dbo.Person ([FirstName], [LastName], [BirthDate]) " +
"VALUES " +
"( N'Benny', N'Anderson', N'2005-05-27' ), " +
"( N'Teri', N'Schaefer', N'2002-12-19' ), " +
"( N'Clint', N'Mante', N'2005-09-15' ), " +
"( N'Drew', N'Green', N'2002-01-08' ), " +
"( N'Denise', N'Schaden', N'2001-01-08' )";
}
}
Proper data operations
Are done in a class, not in a form as shown below. There is a shared connection which when creating a new instance of PersonOperations class lives until the instance is out of scope. For each operation, the connection is used and disposed off. For all operations Dapper opens and closes the connection for us.
Note private IDbConnection _cn; below. Represents an open connection to a data source, and is implemented by .NET data providers that access relational databases. What does this mean? If we were to change the data provider in the class constructor to another data provider the code still works. For instance in the GitHub source repository the project (NET 8) uses the same style for a simple Microsoft Access database. Yes some developers will start off with Microsoft Access which is fine for a first project but there are so many benefits for using SQL-Server to not us Microsoft Access.
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using DapperSimpleApp.Models;
// ReSharper disable ConvertConstructorToMemberInitializers
namespace DapperSimpleApp.Classes
{
/// <summary>
/// CRUD operations void of exception handling
/// </summary>
internal class PersonOperations
{
private IDbConnection _cn;
public PersonOperations()
=> _cn = new SqlConnection(ConfigureSettings.ConnectionString());
public List<Person> GetList()
=> _cn.Query<Person>(SqlStatements.GetAllPeople).AsList();
public void Update(Person currentPerson)
{
_cn.Execute(SqlStatements.UpdatePerson, new
{
currentPerson.FirstName,
currentPerson.LastName,
currentPerson.BirthDate, currentPerson.Id
});
}
public Person Get(int id)
=> _cn.QueryFirst<Person>(SqlStatements.GetPerson, new { Id = id });
public void Add(Person person)
{
person.Id = _cn.QueryFirst<int>(SqlStatements.InsertPerson, person);
}
public bool Delete(int id)
=> _cn.Execute(SqlStatements.RemovePerson, new { Id = id }) == 1;
public void ResetData()
{
_cn.Execute($"DELETE FROM dbo.{nameof(Person)}");
_cn.Execute($"DBCC CHECKIDENT ({nameof(Person)}, RESEED, 0)");
_cn.Execute(SqlStatements.ResetTable);
}
}
}
Form1 is done better than most beginner code other than data operations are in the form while Form1Update is the model to follow.
Data Validation
Generally, a developer will validate changes and additions by assertion. For instance, if a string property empty or a date is in a specific range directly in code and is isolated so that the code is not useable in another part of the project.
For this sample, FluentValidation NuGet package is used.
The rules for the Person model
- First and last name can not be empty
- BirthDate must be less than 1/1/2006
This is done by using the follow validator and remember this is just a simple example, check out the docs to see what is possible.
public class PersonValidator : AbstractValidator<Person>
{
public PersonValidator()
{
RuleFor(x => x.FirstName).NotEmpty();
RuleFor(x => x.LastName).NotEmpty();
RuleFor(x => x.BirthDate).LessThan(x => new DateTime(2006,1,1));
}
}
Usage
Back in our form, the BindingSource has a ListChanged event which we subscribe too below.
- React to something changed in the current row
- Get the current person
- Create an instance of the validator above.
- Perform validation
If valid, create an instance of the PersonOperations class and update the database table. If not valid, read the person from the database and restore the record in the BindingList which reflects automatically in the DataGridView because of change notification.
Now in a real application that someone may have altered the record since this application read the data, more logic and notification would be in order rather than simply restore the person data. This is outside the scope of this article.
private void BindingSource_ListChanged(object sender, ListChangedEventArgs e)
{
if (e.ListChangedType == ListChangedType.ItemChanged)
{
Person currentPerson = _bindingList[e.OldIndex];
PersonValidator validator = new PersonValidator();
ValidationResult result = validator.Validate(currentPerson);
if (result.IsValid)
{
var operations = new PersonOperations();
operations.Update(currentPerson);
}
else
{
/*
* Reset from database table and in a multi-user environment there may have
* been changes to this record since the app started so be aware of this.
*/
var operations = new PersonOperations();
var person = operations.Get(currentPerson.Id);
_bindingList[e.OldIndex] = person;
}
}
else if (e.ListChangedType == ListChangedType.ItemDeleted)
{
// this is after a delete operation
}
}
In the provided source code, a dialog form provides the ability to add a new record, before adding a record the same validator is used. If one or more properties are invalid they are notified.
Passing data from a child dialog to the calling form.
There are several ways to do this, in the code provided, the calling for subscribes to a custom event as shown below.
public delegate void OnValidatePerson(Person person);
public event OnValidatePerson ValidPerson;
Calling form code which subscribes to the above event and shows the dialog.
private void AddNewPersonButton_Click(object sender, EventArgs e)
{
using (var f = new AddPersonForm())
{
f.ValidPerson += ValidPersonFromChildForm;
f.ShowDialog();
}
}
Then the event in the form which adds the new record and assigns the new primary key to the record.
private void ValidPersonFromChildForm(Person person)
{
if (CurrentButton.Enabled)
{
var operations = new PersonOperations();
operations.Add(person);
_bindingList.Add(person);
}
}
Back in the dialog
- Reset the error provider for each control
- Create an instance of Person and set properties from controls
- Create the validator and validate
if there are issues, show them as shown in the last screenshot, otherwise pass the new Person back to ValidPersonFromChildForm event above.
private void AddNewButton_Click(object sender, EventArgs e)
{
// clear error provider text on each control
foreach (var control in _controls)
{
errorProvider1.SetError(control.Value, "");
}
Person = new Person()
{
FirstName = FirstNameTextBox.Text,
LastName = LastNameTextBox.Text,
BirthDate = BrthDateTimePicker.Value
};
/*
* Validate Person
*/
PersonValidator validator = new PersonValidator();
ValidationResult result = validator.Validate(Person);
if (!result.IsValid)
{
/*
* Show issues
*
*/
//var builder = new StringBuilder();
//result.Errors.Select(x => x.ErrorMessage).ToList().ForEach(x => builder.AppendLine(x));
//MessageBox.Show(builder.ToString());
foreach (var item in result.Errors)
{
if (_controls.TryGetValue(item.PropertyName, out var control))
{
errorProvider1.SetError(control, item.ErrorMessage);
}
}
}
else
{
ValidPerson?.Invoke(Person);
Close();
}
}
EF Core 8 mirror image
Upgrading from Framework 4.8, Dapper to EF Core NET 8.
This project came from the .NET Framework 4.8 project DapperSimple which uses Dapper while this project uses EF Core 8.
The model Person BirthDate property should be DateOnly but that would mean losing the custom DataGridViewColumn for the calendar functionality. None of the code is affected going to and from the database as SQL Server understands how to handle the DateTime to Date.
Lessons
Having a sound architecture in a Framework 4.8 project can make it easy to upgrade to NET8/EF Core 8. Bad architecture, its still possible to upgrade to NET8/EF Core 8 but requires a great deal of an effort and in some cases may not be possible to use the old framework project as a model.
So with that learn to write clean code.
Summary
With the information provided the reader that was unfamiliar with these topics can build better applications with ,.NET Framework or .NET Core Framework.
See also
Top comments (0)