using System; using System.Data; using System.Data.SqlClient; class ExecuteAddProduct3 { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); mySqlConnection.Open(); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "EXECUTE @MyProductID = AddProduct @MyProductName, " + "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " + "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " + "@MyReorderLevel, @MyDiscontinued"; mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int); mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output; mySqlCommand.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget"; mySqlCommand.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box"; mySqlCommand.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99; mySqlCommand.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10; mySqlCommand.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1; SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[" ProductName"] = " + mySqlDataReader["ProductName"]); Console.WriteLine("mySqlDataReader[" UnitPrice"] = " + mySqlDataReader["UnitPrice"]); } mySqlDataReader.Close(); Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value); mySqlConnection.Close(); } }
SqlException detail info: line number, procedure, server
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(); } } } }
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(); } } }