Windows Forms CRUD (Create/Update/Delete) Application In .NET Core And C#

Hello,

Here's a simple tutorial on how to create a CRUD (Create/Update/Delete) application using Windows Forms In .NET Core. The same concept is applied to a Windows Forms application in .NET Framework. First is to create a Students table on your SQL Server Database using the script below.

USE [TestDatabase]
GO

/****** Object:  Table [dbo].[Students]    Script Date: 03/31/2014 14:11:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Students](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Age] [int] NULL,
	[Address] [varchar](50) NULL,
	[Contact] [varchar](50) NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Create a Windows Forms App called "CRUDApplicationWindowsForms" using latest Visual Studio IDE (Visual Studio 2022) and set the target framework to .NET 8.

Next is to setup the project structure by adding two folders specifically Model and DataAccess. Create a Student class inside the Model folder. And then DALHelper and StudentService classes in the DataAccess. Also add an App.config file for the connectionString. Rename the default form to FStudents.

Update your App.config file by setting the connectionString node that points to the database as to where the table is created.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="TestDatabase" connectionString="Data Source=.;Initial Catalog=TestDatabase;Integrated Security=True;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

In your Student.cs class, add properties that holds information for a particular student like ID, Name, Age, and etc..

public class Student
{
   public int ID { get; set; } = 0;
   public string Name { get; set; } = string.Empty;
   public int Age { get; set; } = 0;
   public string Address { get; set; } = string.Empty;
   public string Contact { get; set; } = string.Empty;
}

For the DALHelper class, these are methods that communicates to the database that performs specific actions such as inserting, retrieving, deleting or updating records. What each method needs is the query passed from the caller into the the argument of the function.

public class DALHelper
{
   #region Variable Declarations

   private SqlDataAdapter myAdapter;
   private SqlConnection myConn;
   private string connectionString;

   #endregion

   #region Constructor

   public DALHelper()
   {
      connectionString = ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString;
      myAdapter = new SqlDataAdapter();
      myConn = new SqlConnection(connectionString);
   }

   #endregion

   private SqlConnection OpenConnection()
   {
      if (myConn.State == ConnectionState.Closed || myConn.State == ConnectionState.Broken)
      {
         myConn.Open();
      }

      return myConn;
   }

   private SqlConnection CloseConnection()
   {
      if (myConn.State == ConnectionState.Open || myConn.State == ConnectionState.Broken)
      {
         myConn.Close();
      }

      return myConn;
   }

   #region Execute Scalar

   public int ExecuteScalar(String _query)
   {
      int id = -1;
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.CommandText = _query;

         object objId = command.ExecuteScalar();
         if (objId != DBNull.Value && objId != null)
         {
            id = (int)objId;
         }
      }
      catch (Exception ex)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }

      return id;
   }

   public int ExecuteScalar(String _query, SqlParameter[] sqlParameter)
   {
      int id = -1;
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.Parameters.AddRange(sqlParameter);
         command.CommandText = _query;

         object objId = command.ExecuteScalar();
         if (objId != DBNull.Value && objId != null)
         {
            id = (int)objId;
         }
      }
      catch (Exception ex)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }

      return id;
   }

   /// <summary>
   /// Return bool instead of int
   /// </summary>        
   public bool BoolExecuteScalar(String _query, SqlParameter[] sqlParameter)
   {
      bool isExisting = false;
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.Parameters.AddRange(sqlParameter);
         command.CommandText = _query;

         object objId = command.ExecuteScalar();
         if (objId != DBNull.Value && objId != null)
         {
            isExisting = true;
         }
      }
      catch (Exception ex)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }

      return isExisting;
   }

   #endregion

   #region Execute Select

   public DataSet ExecuteSelectQuery(String _query)
   {
      DataSet ds = new DataSet();
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.CommandText = _query;

         myAdapter.SelectCommand = command;
         myAdapter.Fill(ds);
      }
      catch (Exception ex)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }

      return ds;
   }

   public DataTable ExecuteSelectQueryDataTable(String _query)
   {
      DataTable dt = new DataTable();
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.CommandText = _query;

         myAdapter.SelectCommand = command;
         myAdapter.Fill(dt);
      }
      catch (Exception ex)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }

      return dt;
   }

   public DataTable ExecuteSelectQueryDataTable(String _query, SqlParameter[] sqlParameter)
   {
      DataTable dt = new DataTable();
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.Parameters.AddRange(sqlParameter);
         command.CommandText = _query;

         myAdapter.SelectCommand = command;
         myAdapter.Fill(dt);
      }
      catch (Exception ex)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }

      return dt;
   }

   public DataSet ExecuteSelectQuery(String _query, SqlParameter[] sqlParameter)
   {
      DataSet ds = new DataSet();
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.Parameters.AddRange(sqlParameter);
         command.CommandText = _query;

         myAdapter.SelectCommand = command;
         myAdapter.Fill(ds);
      }
      catch (Exception ex)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }

      return ds;
   }

   #endregion

   #region Execute Non Query

   public void ExecuteNonQuery(string query)
   {
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.CommandText = query;
         command.CommandTimeout = 0;
         command.ExecuteNonQuery();
      }
      catch (Exception e)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }
   }

   public void ExecuteNonQuery(string query, SqlParameter[] sqlParameters)
   {
      SqlCommand command = myConn.CreateCommand();

      try
      {
         command.Connection = OpenConnection();
         command.Parameters.AddRange(sqlParameters);
         command.CommandText = query;
         command.CommandTimeout = 0;
         command.ExecuteNonQuery();
      }
      catch (Exception e)
      {
         //throw exception or pass exception to caller
      }
      finally
      {
         CloseConnection();
         command.Dispose();
         myConn.Dispose();
      }
   }

   #endregion
}

The StudentService sets the query with the model values and then calls the methods of the DALHelper class passing in the query string.

public class StudentService
{
   //members
   private DALHelper helper;
   private DataTable dtStudent;
   private SqlParameter[] parameter;
   private string query;

   /// <summary>
   /// Save student record
   /// </summary>
   /// <param name="student"></param>
   public void SaveStudent(Student student)
   {
      helper = new DALHelper();
      query = "Insert Into Students(Name,Age,Address,Contact) "
      + " Values (@Name,@Age, @Address,@Contact)";
      parameter = new SqlParameter[4];
      parameter[0] = new SqlParameter("@Name", SqlDbType.VarChar) { Value = student.Name };
      parameter[1] = new SqlParameter("@Age", SqlDbType.Int) { Value = student.Age };
      parameter[2] = new SqlParameter("@Address", SqlDbType.VarChar) { Value = student.Address };
      parameter[3] = new SqlParameter("@Contact", SqlDbType.VarChar) { Value = student.Contact };
      helper.ExecuteNonQuery(query, parameter);
   }

   /// <summary>
   /// Edit Student Record
   /// </summary>
   /// <param name="student"></param>
   public void UpdateStudent(Student student)
   {
      helper = new DALHelper();
      query = "Update Students set Name=@Name,Age=@Age,Address=@Address,Contact=@Contact Where ID=@ID";
      parameter = new SqlParameter[5];
      parameter[0] = new SqlParameter("@Name", SqlDbType.VarChar) { Value = student.Name };
      parameter[1] = new SqlParameter("@Age", SqlDbType.Int) { Value = student.Age };
      parameter[2] = new SqlParameter("@Address", SqlDbType.VarChar) { Value = student.Address };
      parameter[3] = new SqlParameter("@Contact", SqlDbType.VarChar) { Value = student.Contact };
      parameter[4] = new SqlParameter("@ID", SqlDbType.Int) { Value = student.ID };
      helper.ExecuteNonQuery(query, parameter);
   }

   /// <summary>
   /// Delete Student Record
   /// </summary>
   /// <param name="student"></param>
   public void DeleteStudent(Student student)
   {
      helper = new DALHelper();
      query = "DELETE FROM Students WHERE ID=@id";
      parameter = new SqlParameter[1];
      parameter[0] = new SqlParameter("@id", SqlDbType.Int) { Value = student.ID };
      helper.ExecuteNonQuery(query, parameter);
   }

   /// <summary>
   /// Fetch current record
   /// </summary>
   /// <returns></returns>
   public DataTable Fetch()
   {
      helper = new DALHelper();
      dtStudent = new DataTable();
      query = "Select * from Students";
      dtStudent = helper.ExecuteSelectQueryDataTable(query);
      return dtStudent;
   }
}

To proceed with the UI, add four Labels, four Textboxes (txtName,txtAge,txtAddress and txtContact), two Buttons (btnSave & btnCancel) and a DataGridView control (dgvStudents).The TextBoxes and the Label controls are inside a GroupBox control.

Add multiple columns to the DataGridView controls and set their corresponding DataPropertyName property. This will bind the field of the model to it's corresponding column.
ID - DataPropertyName (ID)
Name - DataPropertyName (Name)
Age - DataPropertyName (Age)
Address - DataPropertyName (Address)
Contact - DataPropertyName (Contact)
Optionally, you may set other properties such as Width, ReadOnly and other properties depending on the project requirements.

As for the code-behind implementation, it contains methods that fetches data from the database, saving and cancelling events, and DataGridView CellContent click event. Each event calls a specific service method and pass the model object to that method. Referring to the CellContext click event, this method does two things: deleting data from the database if the user wish to proceed and selection of records that will be modified.

public partial class FStudents : Form
{
   private Student student;
   private int ID { get; set; }
   private StudentService service;

   public FStudents()
   {
      InitializeComponent();
   }

   private void FStudents_Load(object sender, EventArgs e)
   {
      FetchData();
   }

   private void FetchData()
   {
      service = new StudentService();
      dgvStudents.DataSource = service.Fetch();
   }

   private void btnSave_Click(object sender, EventArgs e)
   {
      student = new Student();

      if (ID <= 0) //perform saving
      {
         if ((txtName.Text != string.Empty))
         {
            student.Name = txtName.Text;
            student.Address = txtAddress.Text;
            student.Age = string.IsNullOrEmpty(txtAge.Text) ? 0 : Convert.ToInt32(txtAge.Text);
            student.Contact = txtContact.Text;
            service.SaveStudent(student);
         }
         else
         {
            MessageBox.Show("Add at least a value to name!", "Add value", MessageBoxButtons.OK, MessageBoxIcon.Error);
         }
      }
      else //perform updating
      {
         student.ID = ID;
         student.Name = txtName.Text;
         student.Address = txtAddress.Text;
         student.Age = string.IsNullOrEmpty(txtAge.Text) ? 0 : Convert.ToInt32(txtAge.Text);
         student.Contact = txtContact.Text;
         service.UpdateStudent(student);
      }

      ClearControls();
      ResetID();
      FetchData();
   }

   private void btnCancel_Click(object sender, EventArgs e)
   {
      ClearControls();
      ResetID();
   }

   private void ClearControls()
   {
      txtAddress.Text = string.Empty;
      txtAge.Text = string.Empty;
      txtContact.Text = string.Empty;
      txtName.Text = string.Empty;
   }

   private void ResetID()
   {
      ID = -1;
   }

   private void dgvStudents_CellContentClick(object sender, DataGridViewCellEventArgs e)
   {
      //Delete column has index 1
      if (e.RowIndex >= 0 && ((DataGridView)sender).Columns[e.ColumnIndex].GetType() == typeof(DataGridViewButtonColumn) && (e.ColumnIndex == 1))
      {
         if (MessageBox.Show("You sure about this? Student will be deleted from database!", "Remove?", MessageBoxButtons.OKCancel) == DialogResult.OK)
         {
            if (((DataGridView)sender).CurrentRow.Cells[2].Value != null)
            {
               student = new Student();
               student.ID = Convert.ToInt32(((DataGridView)sender).CurrentRow.Cells[2].Value);
               service.DeleteStudent(student);
               FetchData(); //update grid and database
            }
         }
      }
      else
      {
         //edit button has column index 0
         if (e.RowIndex >= 0 && ((DataGridView)sender).Columns[e.ColumnIndex].GetType() == typeof(DataGridViewButtonColumn) && (e.ColumnIndex == 0))
         {
            ID = Convert.ToInt32(((DataGridView)sender).CurrentRow.Cells[2].Value); //ID column in datagrid has column index of 2
            txtAddress.Text = ((DataGridView)sender).CurrentRow.Cells[5].Value.ToString();
            txtAge.Text = ((DataGridView)sender).CurrentRow.Cells[4].Value.ToString();
            txtContact.Text = ((DataGridView)sender).CurrentRow.Cells[6].Value.ToString();
            txtName.Text = ((DataGridView)sender).CurrentRow.Cells[3].Value.ToString();
         }
      }
   }
}

Output

Source Code: Github

That's It!