using System; using System.Data; using System.Data.SqlClient; class SqlExceptionDemo { static void Main(){ string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "wrong command"; try { conn.Open(); cmd.ExecuteNonQuery(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:"+ ex.Source; str += " "+ "Number:"+ ex.Number.ToString(); str += " "+ "Message:"+ ex.Message; str += " "+ "Class:"+ ex.Class.ToString (); str += " "+ "Procedure:"+ ex.Procedure.ToString(); str += " "+ "Line Number:"+ex.LineNumber.ToString(); str += " "+ "Server:"+ ex.Server.ToString(); Console.WriteLine (str, "Database Exception"); } catch (System.Exception ex) { string str; str = "Source:"+ ex.Source; str += " "+ "Error Message:"+ ex.Message; Console.WriteLine (str, "General Exception"); } finally { if (conn.State == ConnectionState.Open) { Console.WriteLine ("Finally block closing the connection", "Finally"); conn.Close(); } } } }
Author: coder
Deal with multiple Sql error in SqlException
using System;
using System.Data;
using System.Data.SqlClient;
class SqlExceptionDemo {
static void Main(){
string connString = “server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI”;
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “wrong”;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string str =””;
for (int i = 0; i < ex.Errors.Count; i++)
{
str += "
" + "Index #" + i + "
" +
"Exception : " + ex.Errors[i].ToString() + "
" +
"Number:" + ex.Errors[i].Number.ToString() + "
"
;
}
Console.WriteLine(str);
}
catch (System.Exception ex)
{
string str;
str = "Source:"+ ex.Source;
str += "
"+ "Error Message:"+ ex.Message;
Console.WriteLine (str);
}
finally
{
if (conn.State == ConnectionState.Open)
{
Console.WriteLine ("Finally block closing the connection", "Finally");
conn.Close();
}
}
}
}
[/csharp]
Catch Sql command exceptions
using System; using System.Data; using System.Data.SqlClient; class SqlExceptionDemo { static void Main(){ string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "something wrong here"; try { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); string str = dr.GetValue(20).ToString(); dr.Close(); } catch (System.InvalidOperationException ex) { string str; str = "Source:" + ex.Source; str += " " + "Message:"+ ex.Message; str += " " + " "; str += " " + "Stack Trace :" + ex.StackTrace; Console.WriteLine (str, "Specific Exception"); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += " " + "Message:" + ex.Message; Console.WriteLine (str, "Database Exception"); } catch (System.Exception ex) { string str; str = "Source:" + ex.Source; str += " "+ "Message:" + ex.Message; Console.WriteLine (str, "Generic Exception"); } finally { if ( conn.State == ConnectionState.Open) { Console.WriteLine ("Finally block closing the connection", "Finally"); conn.Close(); } } } }
SqlException message
using System; using System.Data; using System.Data.SqlClient; class SqlExceptionDemo { static void Main(){ string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "wrong sql"; try{ conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Close(); } catch (System.Data.SqlClient.SqlException ex) { string str; str = "Source:" + ex.Source; str += " " + "Exception Message:" + ex.Message; Console.WriteLine(str); } catch (System.Exception ex) { string str; str = "Source:" + ex.Source; str += " " + "Exception Message:" + ex.Message; Console.WriteLine(str); } finally { if (conn.State == ConnectionState.Open) { Console.WriteLine("Finally block closing the connection", "Finally"); conn.Close(); } } } }
GetDataSources
using System; using System.Data; using System.Data.Sql; class MainClass { public static void Main(string[] args) { using (DataTable SqlSources = SqlDataSourceEnumerator.Instance.GetDataSources()) { Console.WriteLine("Discover SQL Server Instances:"); foreach (DataRow source in SqlSources.Rows) { Console.WriteLine(" Server Name:{0}", source["ServerName"]); Console.WriteLine(" Instance Name:{0}", source["InstanceName"]); Console.WriteLine(" Is Clustered:{0}", source["IsClustered"]); Console.WriteLine(" Version:{0}", source["Version"]); } } } }
SqlDataReader Ordinal Indexer
using System; using System.Data; using System.Data.SqlClient; class OrdinalIndexer { static void Main(string[] args) { string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string sql = @"select FirstName, LastName from Employee"; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); Console.WriteLine(" {0} {1}", "First Name".PadRight(25),"Last Name".PadRight(20)); Console.WriteLine(" {0} {1}", "============".PadRight(25), "============".PadRight(20)); while(reader.Read()) { Console.WriteLine(" {0} | {1}", reader[0].ToString().PadLeft(25), reader[1].ToString().PadLeft(20)); } reader.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }
Deal with Multiple Results
using System; using System.Data; using System.Data.SqlClient; class MultipleResults { static void Main(string[] args) { string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string sql1 = @"select FirstName from Employee;"; string sql2 = @"select FirstName, LastName from Employee;"; string sql = sql1 + sql2; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); do{ while(reader.Read()) { Console.WriteLine("{0} : {1}", reader[0], reader[0]); } Console.WriteLine("".PadLeft(60, '=')); } while(reader.NextResult()); reader.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }