Below is the code I'm using to bulk update (about 14k records) our ZipCodeTerritory
table. The code runs fine, without any exceptions, however when I check the table after the connection
is closed I see that none of the changes have been made.
I am holding all the records that need to be updated in the List
object I pass between methods. I use this List
to fill the DataTable
and place that into a DataSet
to perform the update.
This is my first attempt trying to use this technique so any suggestions greatly appreciated.
private static string selectCommand =
"SELECT ChannelCode, DrmTerrDesc, IndDistrnId, StateCode, ZipCode, EndDate,
EffectiveDate, LastUpdateId, LastUpdateDate, Id, ErrorCodes, Status FROM
ZipCodeTerritory";
private static string updateCommand = "UPDATE ZipCodeTerritory SET ChannelCode =
@ChannelCode, DrmTerrDesc = @DrmTerrDesc,
IndDistrnId = @IndDistrnId," +
"StateCode = @StateCode, ZipCode = @ZipCode,
EndDate = @EndDate, EffectiveDate =
@EffectiveDate," +
"LastUpdateId = @LastUpdateId, LastUpdateDate
= @LastUpdateDate, ErrorCodes = @ErrorCodes,"
+ "Status = @Status " +
"WHERE Id = @Id";
public static void Update(List<ZipCodeTerritory> updates, Dictionary<ZipCodeTerritory, string> errorList)
{
using (SqlConnection connection = new SqlConnection(connString))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommand, connection))
{
try
{
SqlCommand updateCmd = connection.CreateCommand();
updateCmd.CommandText = updateCommand;
updateCmd.Parameters.Add(new SqlParameter("@ChannelCode", SqlDbType.Char, 1, "ChannelCode"));
updateCmd.Parameters.Add(new SqlParameter("@DrmTerrDesc", SqlDbType.Char, 1, "DrmTerrDesc"));
updateCmd.Parameters.Add(new SqlParameter("@IndDistrnId", SqlDbType.Char, 1, "IndistrnId"));
updateCmd.Parameters.Add(new SqlParameter("@StateCode", SqlDbType.Char, 1, "StateCode"));
updateCmd.Parameters.Add(new SqlParameter("@ZipCode", SqlDbType.Char, 1, "ZipCode"));
updateCmd.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.Char, 1, "EndDate"));
updateCmd.Parameters.Add(new SqlParameter("@EffectiveDate", SqlDbType.Char, 1, "EffectiveDate"));
updateCmd.Parameters.Add(new SqlParameter("@LastUpdateId", SqlDbType.Char, 1, "LastUpdateId"));
updateCmd.Parameters.Add(new SqlParameter("@LastUpdateDate", SqlDbType.Char, 1, "LastUpdateDate"));
updateCmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Char, 1, "Id"));
updateCmd.Parameters.Add(new SqlParameter("@ErrorCodes", SqlDbType.Char, 1, "ErrorCodes"));
updateCmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Char, 1, "Status"));
updateCmd.UpdatedRowSource = UpdateRowSource.None;
adapter.UpdateCommand = updateCmd;
adapter.AcceptChangesDuringUpdate = true;
DataSet ds = LoadDataSet(updates);
connection.Open();
adapter.Fill(ds, "ZipCodeTerritory");
adapter.Update(ds, "ZipCodeTerritory");
connection.Close();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
}
private static DataSet LoadDataSet(List<ZipCodeTerritory> zipcodeList)
{
DataSet ds = new DataSet();
DataTable data = LoadData(zipcodeList);
ds.Tables.Add(data);
return ds;
}
private static DataTable LoadData(List<ZipCodeTerritory>zipCodeList)
{
DataTable dataTable = InitializeStructure();
foreach (var zipcode in zipCodeList)
{
DataRow row = dataTable.NewRow();
try
{
row[0] = zipcode.ChannelCode.Trim();
row[1] = zipcode.DrmTerrDesc.Trim();
row[2] = zipcode.IndDistrnId.Trim();
row[3] = zipcode.StateCode.Trim();
row[4] = zipcode.ZipCode.Trim();
row[5] = zipcode.EndDate.Date;
row[6] = zipcode.EffectiveDate.Date;
row[7] = zipcode.LastUpdateId;
row[8] = DateTime.Now.Date;
//row[9] = zipcode.Id;
row[10] = zipcode.ErrorCodes;
row[11] = zipcode.Status;
}
catch (Exception ex)
{
}
dataTable.Rows.Add(row);
}
return dataTable;
}
private static DataTable InitializeStructure()
{
DataTable dt = new DataTable();
dt.Columns.Add("ChannelCode", typeof (string));
dt.Columns.Add("DrmTerrDesc", typeof (string));
dt.Columns.Add("IndDistrnId", typeof (string));
dt.Columns.Add("StateCode", typeof (string));
dt.Columns.Add("ZipCode", typeof (string));
dt.Columns.Add("EndDate", typeof (DateTime));
dt.Columns.Add("EffectiveDate", typeof (DateTime));
dt.Columns.Add("LastUpdateId", typeof (string));
dt.Columns.Add("LastUpdateDate", typeof (DateTime));
dt.Columns.Add("Id", typeof (int));
dt.Columns.Add("ErrorCodes", typeof (string));
dt.Columns.Add("Status", typeof (string));
return dt;
}
EDIT
I've changed the order for the .Fill
and LoadDataSet()
methods to first load the data from the database as it currently sits and THEN merge the changes into that DataSet
. However, I'm now getting the following error:
`Update unable to find TableMapping['ZipCodeTerritory'] or DataTable 'ZipCodeTerritory'`
I realized I hadn't named the table so I changed the InitializeStructure()
method to the following
private static DataTable InitializeStructure()
{
DataTable dt = new DataTable("ZipCodeTerritory");
However.... now when I attempt the .Update
on this line
adapter.Update(ds, "ZipCodeTerritory");
I get this exception:
Update requires a valid InsertCommand when passed DataRow collection with new rows.
SECOND EDIT
I've added the following InsertCommand
to the Update
method (this is the first few lines of code inside the try
that contains the updateCommand
):
SqlCommand insertCmd = new SqlCommand(insertCommand, connection);
insertCmd.CommandText = updateCommand;
insertCmd.Parameters.Add(new SqlParameter("@ChannelCode", SqlDbType.Char, 1, "ChannelCode"));
insertCmd.Parameters.Add(new SqlParameter("@DrmTerrDesc", SqlDbType.NVarChar, 30, "DrmTerrDesc"));
insertCmd.Parameters.Add(new SqlParameter("@IndDistrnId", SqlDbType.Char, 3, "IndDistrnId"));
insertCmd.Parameters.Add(new SqlParameter("@StateCode", SqlDbType.Char, 3, "StateCode"));
insertCmd.Parameters.Add(new SqlParameter("@ZipCode", SqlDbType.Char, 9, "ZipCode"));
insertCmd.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.Date, 10, "EndDate"));
insertCmd.Parameters.Add(new SqlParameter("@EffectiveDate", SqlDbType.Date, 10, "EffectiveDate"));
insertCmd.Parameters.Add(new SqlParameter("@LastUpdateId", SqlDbType.Char, 8, "LastUpdateId"));
insertCmd.Parameters.Add(new SqlParameter("@LastUpdateDate", SqlDbType.Date, 10, "LastUpdateDate"));
insertCmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int, 20, "Id"));
insertCmd.Parameters.Add(new SqlParameter("@ErrorCodes", SqlDbType.VarChar, 255, "ErrorCodes"));
insertCmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Char, 1, "Status"));
adapter.InsertCommand = insertCmd;
However I am still not seeing any changes in the database.