I have a c# code which reads from a flat file and inserts/update into a database. It is taking very long to process. I need help with changing the code to bulk insert/update. Code is shown below:
public static void ReadSouthCarolinaLienholders()
{
Logger.Instance.LogEvent("State: SC");
//string file = @"c:\SC\liens.txt";
string file = ConfigurationManager.AppSettings["sc_file_path"];
List<string> list = new List<string>();
using (StreamReader reader = new StreamReader(file))
{
string line;
while ((line = reader.ReadLine()) != null)
{
list.Add(line); // Add to list.
}
}
using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["sc_DSN"]))
{
string sql1 = "UPDATE sc_lienholder SET is_active = 'N'";
string sql = "INSERT INTO sc_lienholder(lienholder_code,name,address,address2,city,state,zipcode,elt_status,is_active,insert_dtim,insert_person) VALUES(@lienholder_code,@name,@address,@address2,@city,@state,@zipcode,@elt_status,@is_active,@insert_dtim,@insert_person)";
string sqlUpdate = "UPDATE sc_lienholder SET name= @name, address = @address, address2 = @address2, city = @city, state = @state, zipcode = @zipcode, elt_status = @elt_status, is_active = @is_active, update_dtim = @update_dtim, update_person=@update_person " +
"where lienholder_code = @lienholder_code";
Logger.Instance.LogEvent("Begin Updating existing records to inactive in DB");
using (SqlCommand cmd = new SqlCommand(sql1, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
Logger.Instance.LogEvent("Complete Updating existing records to inactive in DB");
string sBackupTable = "sc_lienholder_" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
string sqlbackup = @"SELECT * into " + sBackupTable + " FROM sc_lienholder";
Logger.Instance.LogEvent("Begin backing up the table");
using (SqlCommand cmd = new SqlCommand(sqlbackup, cn))
{
cmd.ExecuteNonQuery();
}
Logger.Instance.LogEvent("End backing up the table");
// define the SqlCommmand to do the insert - use the using() approach again
Logger.Instance.LogEvent("Begin updating the DB");
//using (SqlCommand cmd = new SqlCommand(sql, cn))
//{
// cn.Open();
for (int index = 0; index < list.Count; index++)
// try
{
string[] items = list[index].Substring(53).Split(new char[] { ',' });
string item = list[index].Substring(0, 51);
SqlCommand check_User_Name = new SqlCommand("select COUNT(*) FROM sc_lienholder WHERE lienholder_code=@lienholder_code", cn);
check_User_Name.Parameters.AddWithValue("@lienholder_code", items[9].Trim());
int UserExist = (int)check_User_Name.ExecuteScalar();
if (UserExist > 0)
{
SqlCommand cmd_update = new SqlCommand(sqlUpdate, cn);
try
{
cmd_update.Parameters.AddWithValue("@name", item.Replace("'", ""));
cmd_update.Parameters.AddWithValue("@address", items[4]);
cmd_update.Parameters.AddWithValue("@address2", items[5]);
cmd_update.Parameters.AddWithValue("@city", items[6]);
cmd_update.Parameters.AddWithValue("@state", items[7].Trim());//sf SC-275 04/02/2015
cmd_update.Parameters.AddWithValue("@zipcode", items[8]);
cmd_update.Parameters.AddWithValue("@lienholder_code", items[9].Trim()); //sf SC-77 03/09/2015
if (items[10].Trim() == "E") //sf SC-77 03/23/2015
{
cmd_update.Parameters.AddWithValue("@elt_status", "Y"); //sf SC-77 03/23/2015
}
else
{
cmd_update.Parameters.AddWithValue("@elt_status", "N"); //sf SC-77 03/23/2015
}
cmd_update.Parameters.AddWithValue("@is_active", "Y");
cmd_update.Parameters.AddWithValue("@update_dtim", DateTime.Now);
cmd_update.Parameters.AddWithValue("@update_person", "Batch Job");
cmd_update.ExecuteNonQuery();
cmd_update.Parameters.Clear();
Logger.Instance.LogEvent(String.Format("{0} lienholders updated", index));
}
catch (Exception ex)
{
cmd_update.Parameters.Clear();
Logger.Instance.LogException(String.Format("Exception: {0}", ex.Message), ex);
Emailer.SendMail(ex.Message, "Error in South Carolina Lien Console Application");
}
}
else
{
SqlCommand cmd = new SqlCommand(sql, cn);
try
{
cmd.Parameters.AddWithValue("@name", item.Replace("'", ""));
cmd.Parameters.AddWithValue("@address", items[4]);
cmd.Parameters.AddWithValue("@address2", items[5]);
cmd.Parameters.AddWithValue("@city", items[6]);
cmd.Parameters.AddWithValue("@state", items[7].Trim());//sf SC-275 04/02/2015
cmd.Parameters.AddWithValue("@zipcode", items[8]);
cmd.Parameters.AddWithValue("@lienholder_code", items[9].Trim()); //sf SC-77 03/09/2015
if (items[10].Trim() == "E") //sf SC-77 03/23/2015
{
cmd.Parameters.AddWithValue("@elt_status", "Y"); //sf SC-77 03/23/2015
}
else
{
cmd.Parameters.AddWithValue("@elt_status", "N"); //sf SC-77 03/23/2015
}
cmd.Parameters.AddWithValue("@is_active", "Y");
cmd.Parameters.AddWithValue("@insert_dtim", DateTime.Now);
cmd.Parameters.AddWithValue("@insert_person", "Batch Job");
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Logger.Instance.LogEvent(String.Format("{0} lienholders inserted", index));
}
catch (Exception ex)
{
cmd.Parameters.Clear();
Logger.Instance.LogException(String.Format("Exception: {0}", ex.Message), ex);
Emailer.SendMail(ex.Message, "Error in South Carolina Lien Console Application");
}
}
}
cn.Close();