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.
- Open Visual Studio 2012.
- Select "File" -> "New" -> "Project...".
- 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" .
- Design the form as in the following:
- Open SQL Server Management Studio.
- Select SQL Server Authentication, provide Login as “sa” and provide a password and click on "Connect".
- On the left hand side is the Object Explorer, right-click on Database and select "New Database..." as in the following:
- Provide a name for the database (I used “employee”) then click on "OK".
- Expand the database “Employee” then right-click on "Table" then select "New Table".
- Add the field and datatype, save the table with the name tbl_Customer then click on "OK".
- 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
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.