CRUD Operation in C# Windows Application - Codebun (2023)

How to create, read, update and delete operations in C# using MYSQL. In this C# Windows applicationLet's create a simple Windows application to perform the CRUD operations.

What are CRUD operations?

CRUD Meaning:CRUD is a combination of four functions that arecreate, read, updateandClearrequired to maintain any storage application basically shows the four main functions that can be performed on any database connected to our application.

CRUD databases and their associated applications to manage them

The CRUD applications are the ones that contain the basic CRUD functions present in various RDBMS systems like Microsoft SQL Server, MySQL, Oracle database and others. The four functions used by users to perform different types of operations on selected data in the database. This could be done using just code or via a GUI (graphical user interface).

CRUD operations in C# Windows application

Let's create a C# window application and database, then we will see how to connect them together and perform CRUD operations on them.

Creating a C# window form and database with table

In the example below, we'll see how to create a Windows application and add controls to the form, as well as create a database and tables.

Step 1:

OpenStudio visuals> then clickfile menu> clickNeu>then click onProject.

Step 2:

New Project dialog box.is displayed, selectVisual C# templates> then select .Net-Framework> Select"WindowForms-App (.Net Framework)“.

Step 3:

Once the application is created, we'll start adding controls from the Toolbox section and then provide the correct names for the controls.

Step 4:

Now let's create a database and a table in SQL Server. open that"Microsoft SQL Server Management Studio"> then right click on"Database"Folder > then click on"New Database".

Among"New Database"dialog box appears, enter a new database name as"dbEmployee Details"and clickOK.

Step 5:

Then run a new make-table query given below in the SQL window to create a new oneEmp_details table.

CREATE TABLE [dbo].[Emp_details]([EmpId] [int] NOT NULL,[EmpName] [nvarchar](50) NULL,[EmpAge] [int] NULL,[EmpContact] [int] NULL,[EmpGender] [ nchar](10) NULL )

Now we can also check table columns by right clicking and clicking on the newly created table"Design". The table looks like below in SQL Studio, now we have successfully created a database.

Connect database to C# window application

We will now see how to create a database connection between Windows application and database, how to get, insert, update and delete data from database (CRUD operation).

Step 1:

We can easily add database connection in windows application, just follow the simple steps,
Go toTools menuthen > click"Connect to database".

Step 2:

By default, theMicrosoft SQL-Server (SQL-Client)is selected as the data source.
Then provide"Servername"topremises HostandSelect our database as in the drop-down list„dbEmployeeDetails“.

Step 3:

We need to check the database connection by clicking"test connection"button, once the connection is confirmed you will see the"Test connection successful"message box.

Step 4:

When the database connection is created, the connection details are displayed.

Step 5:

How can we know the connection string?
Select right clickdatabase connection>Characteristics> Copy"connection string"for reference,

Step 6:

As a first step, we need to add a connection string„App.config“file to connect the database,

Step 7: Enter the employee details

First we need to create an Employee class and its properties,

public class { public string EmpId { get; set; } public string EmpName { get; set; } public string age { get; set; } public string ContactNo { get; set; }}

Now add the connection string read outApp.configuseconfiguration manager,

privatestaticstringmyConn=ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

The method accepts employee details and then opens the SQL connection and runs the command to insert the employee details into the databaseINSERTIONQuery if successful then returntrueor butNOT CORRECT.

private const string InsertQuery = "Insert In Emp_details(EmpId, EmpName, EmpAge, EmpContact, EmpGender) Values ​​(@EmpId, @EmpName, @EmpAge, @EmpContact, @EmpGender)";public bool InsertEmployee(Employee employee){int rows; using (SqlConnection con = new SqlConnection(myConn)){con.Open();using (SqlCommand com = new SqlCommand(InsertQuery, con)){com.Parameters.AddWithValue("@EmpId", employee.EmpId);com. Parameters.AddWithValue("@EmpName", Employee.EmpName);com.Parameters.AddWithValue("@EmpAge", Employee.Age);com.Parameters.AddWithValue("@EmpContact", Employee.ContactNo);com.Parameters. AddWithValue("@EmpGender", employee.Gender);rows = com.ExecuteNonQuery();} }return (rows > 0) ? wahr falsch;}

Step 8: Update employees

The UpdateEmployee method accepts updated employee details and opens a SQL connection and updates the employee details in the database with itTO UPDATEQuery returns on successtrueor butNOT CORRECT.

private const string UpdateQuery = "Emp_details aktualisieren set EmpName=@EmpName, EmpAge=@EmpAge, EmpContact=@EmpContact, EmpGender=@EmpGender where EmpId=@EmpId";public bool UpdateEmployee(Employee employee){int rows;using (SqlConnection con = new SqlConnection(myConn)){con.Open();using (SqlCommand com = new SqlCommand(UpdateQuery, con)){com.Parameters.AddWithValue("@EmpName", employee.EmpName);com.Parameters.AddWithValue( "@EmpAge", Mitarbeiter.Alter); com.Parameters.AddWithValue("@EmpContact", Employee.ContactNo);com.Parameters.AddWithValue("@EmpGender", Employee.Gender); com.Parameters.AddWithValue("@EmpId", employee.EmpId);rows = com.ExecuteNonQuery();} }return (rows > 0) ? wahr falsch;}

Step 9: Delete employees

The DeleteEmplee method passes the selected employee ID to delete the employee details in the databaseCLEARInquiry. if it succeeds, then it returnstrueotherwise it returns false.

private const string DeleteQuery = "Delete from Emp_details where EmpId=@EmpId";public bool DeleteEmployee(Employee employee){int rows;using (SqlConnection con = new SqlConnection(myConn)){con.Open();using (SqlCommand com = new SqlCommand(DeleteQuery, con)){com.Parameters.AddWithValue("@EmpId", employee.EmpId);rows = com.ExecuteNonQuery();} }return (rows > 0) ? wahr falsch;}

Step 10: Get employee details

The methodGet staffreturns all employees stored in the databaseSELECTquery as shown below,

privateconststringSelectQuery="Select*fromEmp_details";public DataTable GetEmployees(){var datatable = new DataTable();using (SqlConnection con = new SqlConnection(myConn)){con.Open();using(SqlCommand com = new SqlCommand(SelectQuery, con)){using(SqlDataAdapter adapter = new SqlDataAdapter(com)){adapter.Fill(datatable);} } } return datatable;}

The Employee class now looks like this,

Using System;Using System.Collections.Generic;Using System.Configuration;Using System.Data;Using System.Data.SqlClient;Using System.Linq;Using System.Text;Using System.Threading. Tasks;Namespace EmployeeDetails

{class Employee{private static string myConn = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;public string EmpId { get; set; } public string EmpName { get; set; } public string age { get; set; }public string ContactNumber { get; set; } public string gender { get; set; }private const string SelectQuery = "Select * from Emp_details";private const string InsertQuery = "Insert In Emp_details(EmpId, EmpName, EmpAge, EmpContact, EmpGender) Values ​​​​(@EmpId, @EmpName, @EmpAge, @EmpContact, @ EmpGender) ";private const string UpdateQuery = "Update Emp_details set EmpName=@EmpName, EmpAge=@EmpAge, EmpContact=@EmpContact, EmpGender=@EmpGender where EmpId=@EmpId";private const string DeleteQuery = "Delete from Emp_details where EmpId => EmpId";public DataTable GetEmployees(){var datatable = new DataTable();using (SqlConnection con = new SqlConnection(myConn)){con.Open();using(SqlCommand com = new SqlCommand(SelectQuery, con)) {using (SqlDataAdapter adapter = new SqlDataAdapter(com)){adapter.Fill(datatable);} } }return datatable;}public bool InsertEmployee(Employee employee){int rows;using (SqlConnection con = new SqlConnection(myConn))){ con. Open();using (SqlCommand com = new SqlCommand(InsertQuery, con)){com.Parameters.AddWithValue("@EmpId", employee.EmpId);com.Param eters.AddWithValue("@EmpName", Employee.EmpName) ;com.Parameters.AddWithValue("@EmpAge", Employee.Age);com.Parameters.AddWithValue("@EmpContact", Employee.ContactNo);com.Parameters. AddWithValue("@EmpGender", employee.Gender);rows = com.ExecuteNonQuery();} }return (rows > 0) ? true: false;}public bool UpdateEmployee(Employee employee){int rows;using (SqlConnection con = new SqlConnection(myConn)){con.Open();using (SqlCommand com = new SqlCommand(UpdateQuery, con)){ com. Parameters.AddWithValue("@EmpName", Employee.EmpName);com.Parameters.AddWithValue("@EmpAge", Employee.Age);com.Parameters.AddWithValue("@EmpContact", Employee.ContactNo);com.Parameters. AddWithValue("@EmpGender", employee.Gender);com.Parameters.AddWithValue("@EmpId", employee.EmpId);rows = com.ExecuteNonQuery();} }return (rows > 0) ? true : false;}public bool DeleteEmployee(Employee employee){int rows;using (SqlConnection con = new SqlConnection(myConn)){con.Open();using (SqlCommand com = new SqlCommand(DeleteQuery, con)){com. Parameters.AddWithValue("@EmpId", employee.EmpId);rows = com.ExecuteNonQuery();} }return (rows > 0) ? true false;} } }

We have now completed all the operations and we can insert, update, delete and get the employee details using the methods.

Step 11: How Can We Perform the CRUD Operations Using Windows Forms?

Double-click the"Add"button, It automatically opens theForm1.csand create below click event method and we need to write some logic here. All buttons have their click events when we click that button and fire the associated event at that time.

private void btnAdd_Click(object sender, EventArgs e){}

using System;using System.Windows.Forms;namespace EmployeeDetails{public subclass Form1 : Form{Employee employee = new Employee();public Form1(){InitializeComponent();dgvEmployeeDetails.DataSource = employee.GetEmployees();} // Employee details add when Add button is clicked private void btnAdd_Click(object sender, EventArgs e){ employee.EmpId = txtEmpId.Text;employee.EmpName = txtEmpName.Text;employee.Age = txtAge.Text;employee.ContactNo = txtContactNo .Text; employee.Gender = cboGender.SelectedItem.ToString();// Call the Insert Employee method to insert the employee details into the databasevar success = employee.InsertEmployee(employee); // Refresh the grid to show the updated employee details dgvEmployeeDetails.DataSource = employee.GetEmployees();if (success){// Clear controls once employee is inserted successfullyClearControls();MessageBox.Show("Employee has been been added successfully" );}elseMessageBox.Show("An error occurred. Please try again...");}//Updates the details of the selected employee when you click the refresh button. employee.Age = txtAge.Text;employee.ContactNo = txtContactNo.Text;employee.Gender = cboGender.SelectedItem.ToString();// Call the Update Employee method to update the selected employee details to the databasevar success = employee.UpdateEmployee(employee) ;// Refresh the grid to see the updated employee details. );}elseMessageBox.Show("An error occurred. Please try again...");}// Delete the selected employee when the delete button is clickedprivate void btnDelete_Click(object sender, EventArgs e){employee. EmpId = txtEmpId.Text;// Call DeleteEmployee method to delete the selected employee from the databasevar success = employee.DeleteEmployee(employee);// Refresh the grid to show the updated employee details ("Employee was added successfully" );}elseMessageBox.Show("An error occurred. Please try again...");}// Clear all controls when clicks clear buttonprivate void btnClear_Click(object sender, EventArgs e){ClearControls( );}private void ClearControls(){txtEmpId.Text = "";txtEmpName.Text = "";txtAge.Text = "";txtContactNo.Text = "";cboGender.Text = "";}// This data grid event is fired when You select the grid rows and the controls with selected collaborators Fill eiterdetailsprivate void dgvEmployeeDeta ils_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e){var index = e.RowIndex;txtEmpId.Text = dgvEmployeeDetails.Rows[index].Cells[0].Value.ToString();txtEmpName.Text = dgvEmployeeDetails.Rows[ index].Cells[1].Value.ToString();txtAge.Text = dgvEmployeeDetails.Rows[index].Cells[2].Value.ToString();txtContactNo.Text = dgvEmployeeDetails.Rows[index].Cells[3 ].Value. ToString();cboGender.Text = dgvEmployeeDetails.Rows[index].Cells[4].Value.ToString();} } }

Output:

Add employee details and click the Add button.

The employee data was successfully inserted.

Selecting the grid row (Employees) populates selected employee details in the update or delete controls.

So this way we can just create a C# windows application and database connected together and then we can do basic CRUD operations on it. I hope you all have now learned the process.