Commit and Rollback Transaction possible??(in CSV to SQL datacopy)
Hi team,Am inserting data from one CSV file to SQL table with below code. If one row fails then it should rollback the the data.Can you please guide me.
var reader2 = ReadAsLines(claim);
List<string> myValues2 = new List<string>();
var i2 = 0;
string total2;
SqlConnection con2 = new SqlConnection("server=DEAWD03045.emea.zurich.dev,1282;database=CHZDS1LCTASH8;connection timeout=30;Integrated Security=True");
using (con2)
{
foreach (var temp in reader2)
{
string[] fields = temp.Split(',');
if (reader == null)
{
// fields[i2] = "null";
}
con2.Open();
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO T33_LC_POLICY(POL_NR,VN_NAME,VERTRAGSTYP,NOGAP_C,FACHRICHTUNG,MUT_TSTP) VALUES(@POL_NR,@VN_NAME,@VERTRAGSTYP,@NOGAP_C,@FACHRICHTUNG,@MUT_TSTP)", con2);
cmd.Parameters.AddWithValue("@POL_NR", Convert.ToDecimal(fields[0]));
cmd.Parameters.AddWithValue("@VN_NAME", fields[1].ToString());
if (fields[2] == "")
{
cmd.Parameters.AddWithValue("@VERTRAGSTYP", "");
}
else
{
cmd.Parameters.AddWithValue("@VERTRAGSTYP", fields[2].ToString());
}
cmd.Parameters.AddWithValue("@NOGAP_C", fields[3].ToString());
cmd.Parameters.AddWithValue("@FACHRICHTUNG", fields[4].ToString());
if (fields[5] == "")
{
cmd.Parameters.AddWithValue("@MUT_TSTP", "");
}
else
{
cmd.Parameters.AddWithValue("@MUT_TSTP", Convert.ToDateTime(fields[5]));
}
cmd.ExecuteNonQuery();
i2++;
con2.Close();
}
catch (Exception e)
{
Files.WriteToErrorLog("Invalid Data and Daten kopieren to' T33_LC_Policy' table failed", e.Message.ToString());
con2.Close();
File.Delete(@"C:\Users\Public\DeleteTest\test.txt");//change the file name
}
}