CRUD Operations Using ADO.NET DataReader in Windows Forms

Simple C# CRUD Operation using ADO.NET DataReader with Windows Form Application for beginners to learn how SqlDataConnection, SqlDataCommand & SqlDataReader work & how to establish a connection with an SQL server.

Introduction

Hi, Welcome,  C# CRUD Operation using ADO.NET DataReader with Windows Form Application (WFA). We will simplify learning Full CRUD operation with Visual Studio and Microsoft SQL Server Management Studio(MSSMS) by creating an Employee table in the database, and we will access this table data from Visual Studio's WFA.

Prerequisites

1. Microsoft SQL Server Management Studio
2. Visual Studio 

Implementation of CRUD Operation using ADO.NET

Step 1. Create the database. Ex: CSDB. To create a database, write the following query in MSSMS.

CREATE DATABASE CSDB ;

Step 2. Now select the CSDB database and write and execute the following SQL statement to Create a table naming it as an Ex: Employee in the Ex: CSDB database.

CREATE TABLE Employee (
    Eno INT PRIMARY KEY,
    Ename VARCHAR(50),
    Job VARCHAR(50),
    Salary MONEY,
    Status BIT NOT NULL DEFAULT 1
);

INSERT INTO Employee VALUES (1, 'Mangesh', 'Developer', 2500.00, 1);
INSERT INTO Employee VALUES (102, 'Rajesh', 'Developer', 25000.00, 0);
INSERT INTO Employee VALUES (103, 'Manoj', 'Tester', 3000.00, 1);
INSERT INTO Employee VALUES (104, 'Eshwar', 'Developer', 29000.00, 1);
INSERT INTO Employee VALUES (105, 'Ankit', 'Tester', 30000.00, 1);
INSERT INTO Employee VALUES (106, 'Mark', 'Team Leader', 60000.00, 1);

Wow!!! Now we have successfully created a table in the database. With the help of the primary key, which is unique and not null, we can access data properly as we have added it in the Eno int primary key. Now Open the Visual Studio.

Step 1. Open Visual Studio and select Create a New Project.

Step 2. In the Search for the template,  Type Windows, and you will find Windows Form App (.Net Framework). Select it.

Step 3. Write your project name, Ex:  CRUD_DATA_READER, and Select the Create option.

Step 4. You will get, by default Windows Form1 design it as follows. To design it use the "Toolbox" label, text boxes, checkboxes, and buttons.

Note. If you can't see Toolbox, you will find it in the Visual Studio View option. 

Step 5. Now just set  textBox1 Enable Property as False and also Insert button Enable property as False.

Step 6. Write the following code under the "Form1.cs" file.

Note

• Before establishing the connection with the server, check your data source name; here, the data source name is Mangesh. It is different for you, so replace it.
• We are establishing the connection with the MS-SQL server using Windows authentication, so we need of User ID and Password.

Add using System.Data.SqlClient; namespace.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace CRUD_DATA_READER
{
    public partial class Form1 : Form
    {
        SqlDataReader dr;
        SqlConnection con;
        SqlCommand cmd;
        string sqlstr;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new SqlConnection("DATA SOURCE=Mangesh; DATABASE=CSDB; INTEGRATED SECURITY=SSPI;");
            cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            LoadData();
        }

        private void LoadData()
        {
            cmd.CommandText = "SELECT Eno, Ename, Job, Salary, Status FROM EMPLOYEE ORDER BY ENO";
            dr = cmd.ExecuteReader();
            ShowData();
        }

        private void ShowData()
        {
            if (dr.Read())
            {
                textBox1.Text = dr[0].ToString();
                textBox2.Text = dr[1].ToString();
                textBox3.Text = dr[2].ToString();
                textBox4.Text = dr[3].ToString();
                checkBox1.Checked = Convert.ToBoolean(dr[4]);
            }
            else
                MessageBox.Show("No Data exist");
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            ShowData();
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = " ";
            checkBox1.Checked = false;
            dr.Close();
            cmd.CommandText = "SELECT ISNULL(MAX(Eno), 1000) + 1 FROM EMPLOYEE";
            textBox1.Text = cmd.ExecuteScalar().ToString();
            btnInsert.Enabled = true;
            btnNext.Enabled = false;
            textBox2.Focus();
        }

        private void ExecuteDML()
        {
            DialogResult dr = MessageBox.Show("Are you sure of executing the below sql statement" + sqlstr, "information", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dr == DialogResult.Yes)
            {
                cmd.CommandText = sqlstr;
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                    MessageBox.Show("Statement executed successfully!");
                else
                    MessageBox.Show("Statement failed execution!");
            }
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            try
            {
                if (textBox2.Text == " ")
                    MessageBox.Show("Please enter Ename!", "Employee Name", MessageBoxButtons.OK, MessageBoxIcon.Information);
                else if (textBox3.Text == " ")
                    MessageBox.Show("Please enter Job details!", "Job Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
                else if (textBox4.Text == " ")
                    MessageBox.Show("Please enter Salary Details!", "Salary Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
                else if (textBox2.Text != " " && textBox3.Text != " " && textBox4.Text != " ")
                {
                    sqlstr = String.Format("INSERT INTO EMPLOYEE(Eno, Ename, Job, Salary, Status) VALUES({0}, '{1}', '{2}', {3}, {4})", textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, Convert.ToInt32(checkBox1.Checked));
                    ExecuteDML();
                    btnInsert.Enabled = false;
                    LoadData();
                    btnNext.Enabled = true;
                }
            }
            catch (Exception ex)
            {
                // Handle the exception
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                if (textBox2.Text == " ")
                    MessageBox.Show("Please enter Ename!", "Employee Name", MessageBoxButtons.OK, MessageBoxIcon.Information);
                else if (textBox3.Text == " ")
                    MessageBox.Show("Please enter Job details!", "Job Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
                else if (textBox4.Text == " ")
                    MessageBox.Show("Please enter Salary Details!", "Salary Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
                else if (textBox2.Text != " " && textBox3.Text != " " && textBox4.Text != " ")
                {
                    sqlstr = String.Format("UPDATE EMPLOYEE SET Ename= '{0}', Job= '{1}', Salary={2}, Status={3} WHERE Eno={4}", textBox2.Text, textBox3.Text, textBox4.Text, Convert.ToInt32(checkBox1.Checked), textBox1.Text);
                    dr.Close();
                    ExecuteDML();
                    LoadData();
                }
            }
            catch (Exception ex)
            {
                // Handle the exception
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            sqlstr = "DELETE FROM Employee WHERE Eno=" + textBox1.Text;
            dr.Close();
            ExecuteDML();
            LoadData();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            if (con.State != ConnectionState.Closed)
                con.Close();
            this.Close();
        }
    }
}

Step 7. Code under the Program.cs.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CRUD_DATA_READER
{
    internal static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

Now just run the application and check the result.

Hohoo!! Here we have completed CRUD Operation using ADO.NET DataReader with Windows Form Application.

Please do comment if you have any doubts and also comment and like if the blog helps. Your review and comments motivate me to write more blogs for the audience :)

Thank you!  Best of Luck!