bulk update operation

Hey, I think you should use Stored Procedure to update the table.... Instead of keeping all the Updation logic on the page, you can simply create a Stored Procedure and can add the Update Commands in that SP.

And then call the Stored Procedure from the Code... Take a look at this Example....

I have 2 tables - Tbl_MemberUser and Tbl_AdminUser

Columns in Tbl_MemberUser -
MemID MemName MemPass          MemAddr
1          Shiv          shiv@pass          Mumbai
2          Anand          anand@pass          Delhi

Columns in Tbl_AdminUser
AdmID AdmName AdmPass            AdmAddr
1          Admin1 adm1@pass          Mumbai
2          Admin2 adm2@pass Kolkata

--------------------------------------------------------------------------------------

Now I want to update these tables from the Stored Procedure...
Create Procedure Update_Members
(
@iMemID int,
@iAdmID int,
@sMemAddr varchar(50),
@sAdmAddr varchar(50),
)
AS
BEGIN
UPDATE Tbl_MemberUser SET MemAddr = @sMemAddr where MemID = @iMemID
UPDATE Tbl_AdminUser SET AdmAddr = @sAdmAddr where AdmID = @iAdmID
END

--------------------------------------------------------------------------------------

Now I'll write my code like this...

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace InsertUsers

{

Class Cls_InsertUserInfo

{
protected void btnUpdate_Click(Object sender, EventArgs e)
{

try

{

SqlConnection con = new SqlConnection("Connection String"); // Create Connection Object.

SqlCommand cmd = new SqlCommand("Update_Members", con);// Create Command Object and pass St Proc Name and connection Object

con.Open();// Open the Connection

cmd.CommandType = CommandType.StoredProcedure; // Set the Command Type

cmd.Parameters.Add("@sMemAddr", SqlDbType.VarChar, 50).Value = txtMemAddr.Text; // Accept Parameter

cmd.Parameters.Add("@sAdmAddr", SqlDbType.VarChar, 50).Value = txtAdmAddr.Text;

cmd.Parameters.Add("@iMemID", SqlDbType.VarChar, 50).Value = Pass Member ID;

cmd.Parameters.Add("@iAdmID", SqlDbType.VarChar, 50).Value = Pass Admin ID;

cmd.ExecuteNonQuery(); // Execute the St Proc Query

con.Close(); // Close the Connection.

}

catch (Exception ex)

{

throw;

}
}

}

}

This may not be a proper example, since I am trying to pass the ID, but you can just relate the code and try to change yours!!! Hope this helps you out.

PLEASE MARK THE ANSWER AS ACCEPTED IF IT HELPED!!!

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sudhakar Chaudhary14y ago
I am Tried Store Procedure. It is taking approx same time. i have rowCont>5000. Problem is server trip.
Shivanand Arur14y ago
Hey, I think you should use Stored Procedure to update the table.... Instead of keeping all the Updation logic on the page, you can simply create a Stored Procedure and can add the Update Commands in that SP.

And then call the Stored Procedure from the Code... Take a look at this Example....

I have 2 tables - Tbl_MemberUser and Tbl_AdminUser

Columns in Tbl_MemberUser -
MemID MemName MemPass          MemAddr
1          Shiv          shiv@pass          Mumbai
2          Anand          anand@pass          Delhi

Columns in Tbl_AdminUser
AdmID AdmName AdmPass            AdmAddr
1          Admin1 adm1@pass          Mumbai
2          Admin2 adm2@pass Kolkata

--------------------------------------------------------------------------------------

Now I want to update these tables from the Stored Procedure...
Create Procedure Update_Members
(
@iMemID int,
@iAdmID int,
@sMemAddr varchar(50),
@sAdmAddr varchar(50),
)
AS
BEGIN
UPDATE Tbl_MemberUser SET MemAddr = @sMemAddr where MemID = @iMemID
UPDATE Tbl_AdminUser SET AdmAddr = @sAdmAddr where AdmID = @iAdmID
END

--------------------------------------------------------------------------------------

Now I'll write my code like this...
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace InsertUsers
{
Class Cls_InsertUserInfo
{
protected void btnUpdate_Click(Object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection("Connection String"); // Create Connection Object.
SqlCommand cmd = new SqlCommand("Update_Members", con);// Create Command Object and pass St Proc Name and connection Object
con.Open();// Open the Connection
cmd.CommandType = CommandType.StoredProcedure; // Set the Command Type
cmd.Parameters.Add("@sMemAddr", SqlDbType.VarChar, 50).Value = txtMemAddr.Text; // Accept Parameter
cmd.Parameters.Add("@sAdmAddr", SqlDbType.VarChar, 50).Value = txtAdmAddr.Text;
cmd.Parameters.Add("@iMemID", SqlDbType.VarChar, 50).Value = Pass Member ID;
cmd.Parameters.Add("@iAdmID", SqlDbType.VarChar, 50).Value = Pass Admin ID;
cmd.ExecuteNonQuery(); // Execute the St Proc Query
con.Close(); // Close the Connection.
}
catch (Exception ex)
{
throw;
}
}
}
}

This may not be a proper example, since I am trying to pass the ID, but you can just relate the code and try to change yours!!! Hope this helps you out.

PLEASE MARK THE ANSWER AS ACCEPTED IF IT HELPED!!!