Talk to Webmaster Tony John |
Resources » .NET programming » .NET Framework
Insertion , Updation, Deletion of records to Sql Server with ADO.NET
|
This article is for beginners to ADO.NET.
First, you need a database to run the example code in give in this article. I have used Northwind database of Sql Server 2000 and I have created table named Stu. The table contains the following Fields
Fields DataType
StuId make it autonumber
StName nvarchar(50)
Addr nvarchar(50)
The following code sample illustrates how to create a table in a Sql Server database
using System.Data.SqlClient; //Use this namespace for Sql connection
//The code below is an event handler for a button click event
void ButtonClick(object sender, EventArgs e)
{
SqlConnection con = CreateConn(); //Create Conn is fun for creating Sql //Connection
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;//check 3 overloads
cmd.CommandText = "insert into Stu(StName, Addr) values (@StudentName, @Address) ";
//1st way
//cmd.Parameters.AddWithValue("@StudentName", txtUName.Text); //direct with value
//cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
///2nd way create parameters and give d values to it
///bcoz @studentName is parameter
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar);
cmd.Parameters["@StudentName"].Value = txtUName.Text;
cmd.Parameters.Add("@Address", SqlDbType.NVarChar);
cmd.Parameters["@Address"].Value = txtAddress.Text;
con.Open(); ///Open d connection late and close it early
int i= cmd.ExecuteNonQuery();
if (i > 0)
Label1.Text = "Insert Data Successfully";
txtAddress.Text = "";
txtUName.Text = "";
if (con.State == ConnectionState.Open)
con.Close();
BindRecords(); ///Bind Records is the Function which shows records on the ///Grid View after insertion in the database at the same time
}
///
/// Creating Sql Connection
///
///
private static SqlConnection CreateConn()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";
return con;
}
///
/// Bind Records Function
///
private void BindRecords()
{
SqlConnection con = CreateConn();
///way , disconnected architecture
SqlDataAdapter da = new SqlDataAdapter("Select * from Stu", con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
//reader closes automatically through using stmt
if (con.State == ConnectionState.Open)
con.Close();
}
/*Paste the Following code on Show button's Click event to show records on the GridView e.g. Btn id is btnShow*/
protected void btnShow_Click(object sender, EventArgs e)
{
//automatically calls bind record function to show record
BindRecords();
}
Now we go further with DataGrid control for Updation, Edit, Delete , Cancel Event on its own. Follow these steps:
- Take the Data Grid on Next Page
- Apply Update,Edit,Cancel of its button
- Set its DataKeyField property to PrimaryKey(StuId in this case)
The following is the complete code of a page for Updation , Deletion , Cancel , Edit Events for a DataGrid.
using System.Data.SqlClient; //Add this namespace for Sql connection
///
/// Bind Data Initially with Data Grid
///
///
///
protected void Page_Load(object sender, EventArgs e)
{
//Page Refreshes only 1 time
if (!IsPostBack)
{
BindRecords();
}
}
///
/// Bind Records with Data Grid
///
private void BindRecords()
{
SqlConnection con = CreateConn();
///way , disconnected architecture
SqlDataAdapter da = new SqlDataAdapter("Select * from Stu", con);
DataSet ds = new DataSet();
da.Fill(ds);
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
//reader closes automatically through using stmt
if (con.State == ConnectionState.Open)
con.Close();
}
///
/// Edit Event of Data Grid
///
///
///
protected void DataGrid1_EditCommand(object source, DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = e.Item.ItemIndex; //gets the index where to update
BindRecords();
}
///
/// Cancel Event of Data Grid
///
///
///
protected void DataGrid1_CancelCommand(object source, DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = -1;
BindRecords();
}
///
/// Creating Sql Connection
///
///
private static SqlConnection CreateConn()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";
return con;
}
//Updating Records in Data Grid
protected void DataGrid1_UpdateCommand(object source, DataGridCommandEventArgs e)
{
int StudentID = Convert.ToInt32(DataGrid1.DataKeys[e.Item.ItemIndex]);
SqlConnection con = CreateConn(); ///1st Create Connection
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update Stu set StName = @StudentName , Addr = @Address where StuId = " + StudentID + "";
string StudentName = ((TextBox)e.Item.Cells[0].Controls[0]).Text; /// cells[0] is
/// 1st location
/// in data grid
/// which is
/// studentName
string Addr = ((TextBox)e.Item.Cells[1].Controls[0]).Text; ///cells[1] is address
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar);
cmd.Parameters["@StudentName"].Value = StudentName;
cmd.Parameters.Add("@Address", SqlDbType.NVarChar);
cmd.Parameters["@Address"].Value = Addr;
con.Open();
cmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open)
con.Close();
BindRecords();
}
///
/// Deleting Records
///
///
///
protected void DataGrid1_DeleteCommand(object source, DataGridCommandEventArgs e)
{
int StudentID = Convert.ToInt32(DataGrid1.DataKeys[e.Item.ItemIndex]);
SqlConnection con = CreateConn(); //1st Create Connection
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from Stu where StuId = " + StudentID + "";
con.Open();
cmd.ExecuteNonQuery(); ///This is used for inset, update , delete
if (con.State == ConnectionState.Open)
con.Close();
BindRecords();//Bind again to see the Records
}
|
Did you like this resource? Share it with your friends and show your love!
|
|
|
No responses found. Be the first to respond...
|
|
Active MembersTodayLast 7 Daysmore... |