You must Sign In to post a response.
  • Category: ASP.NET

    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
    }

    }
  • #768465
    There are multiple ways to handle your condition
    1.SQL BULK INSERT query
    2.BCP or SqlBulkCopy library to insert bulk data using C# or VB
    3.SQl Server Integration Service (SSIS)
    4.Normal SQL command library in C# or VB
    simply you can use SQL transaction object as follows

    EGIN TRANSACTION
    BEGIN TRY
    BULK INSERT dbo.BulkDataTable
    FROM 'C:\TestFiles\Bulk3.csv'
    WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    ROWS_PER_BATCH = 10000,
    TABLOCK
    )
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION
    END CATCH

    checkout below link for more details
    https://www.codeproject.com/articles/439843/handling-bulk-data-insert-from-csv-to-sql-server

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments