Batch Update

We usually come across scenarios where we have to update records in batch. A simple example of this scenario is "User preferences" when subscribing to any website. User initially selects some 'preferences' & later he can add some more, or remove the preferences that are already selected.

In my example I am going to batch update user regions. An user can be assigned any number of regions & he can add some more or remove the existing.

Believe me this works fine & this is one of the best methods for batch update.

Initially I used to delete the assigned records and used to bulk insert new records. But I got a foreign key violation error once when I was doing this. Then I was forced to do batch update. Eventhough I have felt little bit difficulty in doing this for the first time but now I am very comfortable with batch update.

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

Simple Idea: (I am talking in respect of user-regions).

First we need to get a dataset of assigned regions of the user from DB. (This means initially assigned records)

Now we need to construct another dataset with the new user- regions. (The one's that are selected & modified by user)

Now we need to compare these two datasets and we have to find the records added by user , modifed by user (in this specific example I dont have this) & deleted by the user.

Now in just one database operation, new records will be inserted to database, modifed records will be updated & deleted records will be deleted.

We have to specify insertcommand, updatecommand & deletecommand of dataadapter and with .Update method we can do this in one step.

(If you are using stored procedure, you must define them in db).

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

Note: Eventhough CommandBuilder can do the same, I feel comfortable with DataAdapter's BatchUpdate. (Update)

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

public int UpdateRegionGroups(int UserId, DataSet dsRegionGroups)

{

DataColumn[] ColPk = new DataColumn[2]; //In my case primary key is defined by 2 columns. It can be 1 or many. Using appropriate size of array for Primary key.

ArrayList ArrorigValues = new ArrayList();

DataRow updrow, newrow, delrow;

DataSet dsOrigRegionGroups = new UserRegionGroups().SelectAssignedRegionGroups(UserId); //This is one database fetch operation.

ColPk[0] = dsOrigRegionGroups.Tables[0].Columns["RegionGroupId"];

ColPk[1] = dsOrigRegionGroups.Tables[0].Columns["UserId"];

dsOrigRegionGroups.Tables[0].PrimaryKey = ColPk;

if (dsOrigRegionGroups.Tables[0].Rows.Count > 0)

for (int i = 0; i < dsOrigRegionGroups.Tables[0].Rows.Count; i++)

{

clsobj2DArray objPK = new clsobj2DArray();

objPK.intField1 = Convert.ToInt32(dsOrigRegionGroups.Tables[0].Rows[i]["RegionGroupId"].ToString());

objPK.intField2 = Convert.ToInt32(dsOrigRegionGroups.Tables[0].Rows[i]["UserId"].ToString());

ArrorigValues.Add(objPK);

}

if (dsRegionGroups.Tables[0].Rows.Count > 0)

{

int OriginalCount = dsRegionGroups.Tables[0].Rows.Count;

for (int i = 0; i < OriginalCount; i++)

{

string[] ArrtoFind = new string[2];

ArrtoFind[0] = dsRegionGroups.Tables[0].Rows[i]["RegionGroupId"].ToString();

ArrtoFind[1] = dsRegionGroups.Tables[0].Rows[i]["UserId"].ToString();

updrow = dsOrigRegionGroups.Tables[0].Rows.Find(ArrtoFind);

if (updrow == null)

{

//InsertRowValues(i, dsRegionGroups);

dsOrigRegionGroups.Tables[0].DefaultView.AllowNew = true;

newrow = dsOrigRegionGroups.Tables[0].NewRow();

newrow.BeginEdit();

newrow["RegionGroupId"] = dsRegionGroups.Tables[0].Rows[i]["RegionGroupId"];

newrow["UserId"] = dsRegionGroups.Tables[0].Rows[i]["UserId"];

dsOrigRegionGroups.Tables[0].Rows.Add(newrow);

newrow.EndEdit();

}

else

{

//You can update the row here. In my specific example I dont need it.

int j, k = -1;

if (ArrorigValues.Count > 0)

{

for (j = 0; j < ArrorigValues.Count; j++)

{

clsobj2DArray objPk1 = new clsobj2DArray();

objPk1 = (clsobj2DArray)ArrorigValues[j];

if ((ArrtoFind[0] == objPk1.intField1.ToString()) && (ArrtoFind[1] == objPk1.intField2.ToString()))

{

k = j;

break;

}

}

if (k != -1)

ArrorigValues.RemoveAt(k);

}

}

}

}

if (ArrorigValues.Count > 0)

{

string[] ArrtoDel = new string[2];

clsobj2DArray objPkDel = new clsobj2DArray();

for (int i = 0; i < ArrorigValues.Count; i++)

{

objPkDel = (clsobj2DArray)ArrorigValues[i];

ArrtoDel[0] = objPkDel.intField1.ToString();

ArrtoDel[1] = objPkDel.intField2.ToString();

delrow = dsOrigRegionGroups.Tables[0].Rows.Find(ArrtoDel);

if (delrow != null)

{

delrow.Delete();

}

}

}

return BatchUpdateUserRegionGroups(dsOrigRegionGroups);

}

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

private int BatchUpdateUserRegionGroups(DataSet dsRegionGroups)

{

SqlConnection cnBatchUpdate = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);

SqlCommand insCommand, updCommand, delCommand;

SqlDataAdapter da = new SqlDataAdapter();

da.TableMappings.Add("Table", "UserRegionGroups");

da.SelectCommand = new SqlCommand();

insCommand = new SqlCommand("InsertUserRegionGroups_BatchUpd", cnBatchUpdate);

insCommand.CommandType = CommandType.StoredProcedure;

insCommand.Parameters.Add("@RegionGroupId", SqlDbType.Int);

insCommand.Parameters.Add("@UserId", SqlDbType.Int);

insCommand.Parameters["@RegionGroupId"].SourceColumn = "RegionGroupId";

insCommand.Parameters["@UserId"].SourceColumn = "UserId";

updCommand = new SqlCommand("InsertUserRegionGroups_BatchUpd", cnBatchUpdate);

updCommand.CommandType = CommandType.StoredProcedure;

updCommand.Parameters.Add("@RegionGroupId", SqlDbType.Int);

updCommand.Parameters.Add("@UserId", SqlDbType.Int);

updCommand.Parameters["@RegionGroupId"].SourceColumn = "RegionGroupId";

updCommand.Parameters["@UserId"].SourceColumn = "UserId";

delCommand = new SqlCommand("DELUserRegionGroups_BatchUpd", cnBatchUpdate);

delCommand.CommandType = CommandType.StoredProcedure;

delCommand.Parameters.Add("@RegionGroupId", SqlDbType.Int);

delCommand.Parameters.Add("@UserId", SqlDbType.Int);

delCommand.Parameters["@RegionGroupId"].SourceColumn = "RegionGroupId";

delCommand.Parameters["@UserId"].SourceColumn = "UserId";

da.InsertCommand = insCommand;

da.UpdateCommand = updCommand;

da.DeleteCommand = delCommand;

int retval = 0;

try

{

cnBatchUpdate.Open();

retval = da.Update(dsRegionGroups.Tables[0]);

}

catch (Exception ex)

{

throw new Exception(ex.Message, ex.InnerException);

}

finally

{

cnBatchUpdate.Close();

}

return retval;

}

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

public class clsobj2DArray

{

private int m_var1;

private int m_var2;

public clsobj2DArray()

{ }

public clsobj2DArray(int var1val, int var2val, int var3val)

{

this.m_var1 = var1val;

this.m_var2 = var2val;

}

public int intField1

{

get { return m_var1; }

set { m_var1 = value; }

}

public int intField2

{

get { return m_var2; }

set { m_var2 = value; }

}

}