bulk update operation

Question

I want To Update Multiple Table. It's Taking To Much Time, Some Time Memory Problem. Is There Are Any Solution For That?

Sudhakar Chaudhary · Answer

I am Tried Store Procedure. It is taking approx same time. i have rowCont>5000. Problem is server trip.

Shivanand Arur · Answer

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!!!

bulk update operation

Answers (2)