using System.Data.SqlClient; using System.Data; public static class SqlErrorRepro { private static string sqlBatchText = @" BEGIN TRY SELECT name, log(max_length) FROM sys.columns END TRY BEGIN CATCH ; THROW END CATCH"; public static string connString = @"Data Source=.;Integrated Security=SSPI;Database=tempdb"; public static void ReaderWithoutNextResult() { using (SqlConnection cn = new SqlConnection(connString)) using (SqlCommand cmd = new SqlCommand(sqlBatchText, cn)) { try { cn.Open(); int rowCount = 0; using (SqlDataReader reader = cmd.ExecuteReader()) { while(reader.Read()) { ++rowCount; }; } System.Console.WriteLine("{0} rows read", rowCount); } catch (System.Exception ex){ System.Console.WriteLine("ERROR: " + ex.Message); } } } public static void ReaderWithNextResult() { using (SqlConnection cn = new SqlConnection(connString)) using (SqlCommand cmd = new SqlCommand(sqlBatchText, cn)) { try { cn.Open(); int rowCount = 0; using (SqlDataReader reader = cmd.ExecuteReader()) { while(reader.Read()) { ++rowCount; }; while(reader.NextResult()); } System.Console.WriteLine("{0} rows read", rowCount); } catch (System.Exception ex){ System.Console.WriteLine("ERROR: " + ex.Message); } } } public static void Scalar() { using (SqlConnection cn = new SqlConnection(connString)) using (SqlCommand cmd = new SqlCommand(sqlBatchText, cn)) { try { cn.Open(); System.Object obj = cmd.ExecuteScalar(); System.Console.WriteLine(obj.ToString()); } catch (System.Exception ex){ System.Console.WriteLine("ERROR: " + ex.Message); } } } public static void NonQuery() { using (SqlConnection cn = new SqlConnection(connString)) using (SqlCommand cmd = new SqlCommand(sqlBatchText, cn)) { try { cn.Open(); cmd.ExecuteNonQuery(); System.Console.WriteLine("Query completed without error."); } catch (System.Exception ex){ System.Console.WriteLine("ERROR: " + ex.Message); } } } public static void DtLoad() { using (SqlConnection cn = new SqlConnection(connString)) using (SqlCommand cmd = new SqlCommand(sqlBatchText, cn)) { try { cn.Open(); DataTable dt = new DataTable(); dt.Load(cmd.ExecuteReader()); System.Console.WriteLine("{0} rows read", dt.Rows.Count); } catch (System.Exception ex){ System.Console.WriteLine("ERROR: " + ex.Message); } } } public static void DaFillDt() { using (SqlConnection cn = new SqlConnection(connString)) using (SqlCommand cmd = new SqlCommand(sqlBatchText, cn)) { try { cn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); System.Console.WriteLine("{0} rows read", dt.Rows.Count); } catch (System.Exception ex){ System.Console.WriteLine("ERROR: " + ex.Message); } } } public static void DaFillDs() { using (SqlConnection cn = new SqlConnection(connString)) using (SqlCommand cmd = new SqlCommand(sqlBatchText, cn)) { try { cn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); System.Console.WriteLine("{0} rows read", ds.Tables[0].Rows.Count); } catch (System.Exception ex){ System.Console.WriteLine("ERROR: " + ex.Message); } } } public static void Main() { System.Console.WriteLine("---- ExecuteReader with no NextResult ----"); ReaderWithoutNextResult(); System.Console.WriteLine("---- ExecuteReader with NextResult ----"); ReaderWithNextResult(); System.Console.WriteLine("---- ExecuteScalar ----"); Scalar(); System.Console.WriteLine("---- ExecuteNonQuery ---"); NonQuery(); System.Console.WriteLine("--- DataTable.load ---"); DtLoad(); System.Console.WriteLine("--- DataAdataper.Fill(DataTable) ----"); DaFillDt(); System.Console.WriteLine("--- DataAdataper.Fill(DataSet) ----"); DaFillDs(); } }