Catch error's command lines while executing multiple inserts in a single sqlcommand?

Use try catch in SQL statement see this MSDN link and CodeProject link.

You can do a for or for each loop on all available commands to execute: inside this loop you put a try catch and in the catch block you log/report the exception but do not throw so the loop will continue with next iteration. Notice that you could also use SqlBulk objects to do many inserts in a way similar to what you describe. Edit: if this is to slow you can definitely use the SqlBulkCopy: check here for a step by step example: Bulk Insert into SQL from C# App.

– Davide Piras Oct 19 at 10:22 trying the query and catch errors for each line , in a loop in c# is too slow when I have 35179 lines , which each one is a single insert – Mohsen Oct 19 at 10:34 see my edit and try with: SqlBulkCopy – Davide Piras Oct 19 at 10:36.

This worked void ConvertCsv(string sourcePath, string ResultPath) { #region using (StreamReader sr = new StreamReader(sourcePath)) { using (StreamWriter sw = new StreamWriter(ResultPath)) { sw. WriteLine(@"DECLARE @er NVARCHAR(MAX)='',@i INT=0 BEGIN TRY"); if (sr.Peek() >= 0) { string currentLine = sr.ReadLine(); while (currentLine.Trim() == "" && sr.Peek() >= 0) currentLine = sr.ReadLine(); if (currentLine.Trim() == "") { //error :the file is empty } sw. WriteLine(currentLine); } while (sr.Peek() >= 0) { string currentLine = sr.ReadLine(); if (currentLine.Trim() == "") continue; if (currentLine.Trim().

StartsWith("INSERT")) { while (!currentLine.Trim(). StartsWith("INSERT")) currentLine += sr.ReadLine(); currentLine = @"END TRY BEGIN CATCH SELECT @er+=','+LTRIM(RTRIM(STR(ERROR_LINE()))),@i+=1 END CATCH BEGIN TRY" + Environment. NewLine + currentLine.Trim(); } sw.

WriteLine(currentLine.Trim()); } sw. WriteLine(@"END TRY BEGIN CATCH SELECT @er+=','+LTRIM(RTRIM(STR(ERROR_LINE()))) END CATCH SELECT @er AS errorLines,@i AS errorCount"); sw.Close(); sr.Close(); } } #endregion ExecuteConvertedFile(ResultPath,Server. MapPath(@"~/Data/Uploads/csv/ErrorLogs/ErrorLog.

Sql")); } void ExecuteConvertedFile(string ResultPath, string errorResultPath) { string wholeQuery = System.IO.File. ReadAllText(ResultPath); using (SqlCommand cmd = new SqlCommand { CommandType = CommandType. Text, CommandText = wholeQuery, Connection = new SqlConnection(((SqlConnection)((EntityConnection)new NezaratEntities().

Connection). StoreConnection). ConnectionString) }) { cmd.Connection.Open(); var dr = cmd.ExecuteReader(); dr.Read(); WriteErrorLogs(ResultPath,errorResultPath,dr"errorLines".ToString().Trim()); Label1.

Text = dr"errorCount".ToString()+"unsuccessful transactions"; } } to save unsuccessful sql commands: void WriteErrorLogs(string sourcePath, string ResultPath,string errorLinesTolog) { string lines = File. ReadAllLines(sourcePath); string ErrorLog=""; errorLinesTolog=errorLinesTolog. Remove(0, 1); foreach (var line in errorLinesTolog.

Split(',')) { ErrorLog += linesint. Parse(line)-1 + Environment. NewLine; } System.IO.File.

WriteAllText(ResultPath, ErrorLog); }.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.