CRUD Operations Using Connected Architecture

In this article I show how to use CRUD Operations using a Connection Oriented Architecture.

I will first explain what is meant by CRUD Operations.

CRUD stands for:

C: Create
R:  Read (Select)
U: Update
D:  Delete

Demo

Let's start with a demo.

  1. Open Visual Studio 2012.
  2. Select "File" -> "New" -> "Project...".

    New Project

  3. Select the language as “Visual C#” and  template as “Windows Forms Application”, provide an appropritate name, I used the name "CrudOperationDemo". Select the Location then click "OK" .

    Windows Forms Application

  4. Design the form as in the following:

    Design the form

  5. Open SQL Server Management Studio.

    Open Sql Server Management Studio

  6. Select SQL Server Authentication, provide Login as “sa” and provide a password and click on "Connect".
  7. On the left hand side is the Object Explorer, right-click on Database and select "New Database..." as in the following:

    Select New DataBase

  8. Provide a name for the database (I used “employee”) then click on "OK".

    name of database

  9. Expand the database “Employee” then right-click on "Table" then select "New Table".

    select New Table

  10. Add the field and datatype, save the table with the name tbl_Customer then click on "OK".

    add the field and datatype

  11. Write down the following code.

First import the namespaces "System.Data.SqlClient" and "System.Data".

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_Operation

{

    public partial class CustomerRegistration : Form

    {

        SqlConnection cn;

        SqlCommand cmd;

        SqlDataReader dr;

        string sqlstr;

 

        public CustomerRegistration()

        {

            InitializeComponent();

        }

 

        private void CustomerRegistration_Load(object sender, EventArgs e)

        {

            cn = new SqlConnection("Data Source=ADMIN\\SQLEXPRESS;User Id=sa;Password=abhijit;DataBase=Employee");

            cmd = new SqlCommand();

            cmd.Connection = cn;

            cmd.CommandText = "Select Cust_Id, FName,LName From tbl_Custmer order by Cust_Id";

            cn.Open();

            dr = cmd.ExecuteReader();

            showdata();

        } 

        private void showdata()

        {

            if (dr.Read())

            {

                txtCustId.Text = dr.GetValue(0).ToString();

                txtFName.Text = dr.GetValue(1).ToString();

                txtLName.Text = dr.GetValue(2).ToString();

            }

            else

            {

                MessageBox.Show("Last Record Of the Table");

            }

        }

 

        private void btnNext_Click(object sender, EventArgs e)

        {

            showdata();

        }

 

        private void btnNew_Click(object sender, EventArgs e)

        {

            txtCustId.Text = txtFName.Text = txtLName.Text = "";

            sqlstr = "select MAX(Cust_id)+1 from tbl_Custmer";

            cn.Close();

            cmd.CommandText = sqlstr;

            cn.Open();

            txtCustId.Text = cmd.ExecuteScalar().ToString();

            txtFName.Focus();

        }

 

        private void btnInsert_Click(object sender, EventArgs e)

        {

            sqlstr = "insert into tbl_Custmer(Cust_Id,FName,LName)values ('" + txtCustId.Text + "','" + txtFName.Text + "','" + txtLName.Text + "')";

            ExecuteDML();

        }

        private void ExecuteDML()

        {

            DialogResult dr = MessageBox.Show(sqlstr + "Are You Sure want to Execute Above OPeration ?", "Conformation", MessageBoxButtons.YesNo, MessageBoxIcon.Information);

            if (dr == DialogResult.Yes)

            {

                setstmt();

                int count = cmd.ExecuteNonQuery();

                if (count > 0)

                {

                    MessageBox.Show("Operation Perform Succesfully");

                }

                else

                {

                    MessageBox.Show("Operation failed");

                }

            }

        }

        private void setstmt()

        {

            if (cn.State != ConnectionState.Closed)

            {

                cn.Close();

            }

            cmd.CommandText = sqlstr;

            cn.Open();

        }

 

        private void btnUpdate_Click(object sender, EventArgs e)

        {

            sqlstr = "update tbl_Custmer set Fname='" + txtFName.Text + "',LName='" + txtLName.Text + "' where Cust_Id ='"+txtCustId.Text+"'";

            ExecuteDML();

        }

 

        private void btnDelete_Click(object sender, EventArgs e)

        {

            sqlstr = "delete from tbl_Custmer where Cust_Id ='" + txtCustId.Text + "'";

            ExecuteDML();     

        }

 

        private void btnClose_Click(object sender, EventArgs e)

        {

            if (cn.State != ConnectionState.Closed)

            {

                cn.Close();

                this.Close();

            }

        }

    }

}

Now save the application and run the application. Check that all the operations are working properly; if not then I provided a screen also.
 
OutPut Screen

1. Form Load

run the application

2. New

Form Load

3. Insert

 Insert
4. Update

Update

Delete Operation

Delete Operation
 
Next Button
 
Next Button

If any problem occurs then frankly send mail to me at [email protected] or for more articles visit my blog: dotnetbyabhipatil.
 
Your feedback and suggestions are always welcome for me.