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...".
   
   
   
   
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" .
   
   
   
   
4. Design the form as in the following:
   
   
   
   
5. 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:
   
   
   
   
8. Provide a name for the database (I used “employee”) then click on "OK".
   
   
   
   
9. Expand the database “Employee” then right-click on "Table" then select "New Table".
   
   
   
   
10. Add the field and datatype, save the table with the name tbl_Customer then click on "OK".
    
    
    
    
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



2. New

3. Insert


4. Update

Delete Operation

 
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.