namespace MultipleRecordsUpdation
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlDataAdapter da;
DataSet ds;
DataTable dt;
DataRow dr;
SqlConnection Con = new SqlConnection("Data Source=.\\SqlExpress;Initial Catalog=SEKHARDB;Integrated Security=True");
private void Form1_Load(object sender, EventArgs e)
{
da = new SqlDataAdapter("Select EmpID,EmpName,EmpSalary from MyEmployee", Con);
ds = new DataSet();
da.Fill(ds, "Emp");
dt = ds.Tables[0];
dgEmp.DataSource = ds.Tables[0];
//INSERT COMMAND...
SqlCommand InsertCmd = new SqlCommand("Insert into MyEmployee(EmpName,EmpSalary) values(@P1,@P2)");
SqlParameter P1;
SqlParameter P2;
SqlParameter P3;
SqlParameter Orig_EmpID;
InsertCmd.Connection = Con;
P1 = InsertCmd.Parameters.Add("@P1", SqlDbType.NVarChar,50);
P2 = InsertCmd.Parameters.Add("@P2", SqlDbType.Money);
P1.SourceColumn = "EmpName";
P1.SourceVersion = DataRowVersion.Current;
P2.SourceColumn = "EmpSalary";
P2.SourceVersion = DataRowVersion.Current;
da.InsertCommand = InsertCmd;
//UPDATE COMMAND...
SqlCommand UpdateCmd = new SqlCommand("Update MyEmployee set EmpName=@P1,EmpSalary=@P2 where EmpID=@P3");
P1 = UpdateCmd.Parameters.Add("@P1", SqlDbType.NVarChar, 50);
P2 = UpdateCmd.Parameters.Add("@P2", SqlDbType.Money);
P3 = UpdateCmd.Parameters.Add("@P3", SqlDbType.Int);
UpdateCmd.Connection = Con;
P1.SourceColumn = "EmpName";
P1.SourceVersion = DataRowVersion.Current;
P2.SourceColumn = "EmpSalary";
P2.SourceVersion = DataRowVersion.Current;
P3.SourceColumn = "EmpID";
P3.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = UpdateCmd;
//ATTACH ROW UPDATE EVENT.....
da.RowUpdated+=new SqlRowUpdatedEventHandler(da_RowUpdated);
}
private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
//GET INSERTED ROW IDENTITY COLUMN ID AND PLACE IT ON ADDED ROW...
if (e.StatementType == StatementType.Insert)
{
SqlCommand Cmd = new SqlCommand("select @@Identity", e.Command.Connection);
e.Row["EmpID"] = Cmd.ExecuteScalar();
}
}
private void btnUpdateAll_Click(object sender, EventArgs e)
{
da.Update(ds, "Emp");
MessageBox.Show("Records Updated successfully...");
}
private void btnAdd_Click(object sender, EventArgs e)
{
dr = dt.NewRow();
dt.Rows.Add(dr);
}
}
}
