Csharp/CSharp Tutorial/ADO.Net/SqlConnection Stored Procedure
Содержание
- 1 Call a stored procedure
- 2 Call stored procedure
- 3 Call storedprocedure and pass in the parameter
- 4 Call StoredProcedure with input and output parameters
- 5 Call stored procedure with no parameter
- 6 Call stored procedure with parameter and return value
- 7 Call stored procedure with parameters using SqlCommand
- 8 Call stored procedure with SqlCommand
- 9 Catch exception when calling stored procedure
- 10 Raising and Handling Stored Procedure Errors
- 11 Retrieves stored procedure parameter information
- 12 Retrieving a Return Value from a Stored Procedure
- 13 Retrieving a Stored Procedure Output Parameter
- 14 Retrieving Data Using a SQL Server Stored Procedure
Call a stored procedure
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { SqlConnection cn = new SqlConnection("Data Source=(local); Initial Catalog = MyDatabase; User ID=sa;Password="); SqlCommand cmd = new SqlCommand("MyStoredProcedure", cn); cmd.rumandType = CommandType.StoredProcedure; SqlParameter param = new SqlParameter("@ReturnValue", SqlDbType.Int); cmd.Parameters.Add(param); cmd.Parameters.Add("MyFirstParameter", SqlDbType.Int); cmd.Parameters.Add("MySecondParameter", SqlDbType.Int).Direction = ParameterDirection.Output; SqlDataAdapter da = new SqlDataAdapter(cmd); }
}</source>
Call stored procedure
<source lang="csharp">/* CREATE PROCEDURE Person.GetContacts
@RowCount int OUTPUT
AS
SET NOCOUNT ON SELECT * FROM Person.Contact set @RowCount = @@ROWCOUNT RETURN @RowCount
- /
using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect ="SELECT COUNT(*) FROM Person.Contact; SELECT * FROM Person.Contact;"; using (SqlConnection connection = new SqlConnection(sqlConnectString)) { SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open( ); SqlDataReader dr = command.ExecuteReader( ); dr.Read( ); Console.WriteLine(dr.GetInt32(0)); dr.NextResult( ); int count = 0; while (dr.Read( )){ count++; } Console.WriteLine(count); dr.Close( ); command = new SqlCommand("Person.GetContacts", connection); command.rumandType = CommandType.StoredProcedure; command.Parameters.Add("@RowCount", SqlDbType.Int).Direction =ParameterDirection.Output; dr = command.ExecuteReader( ); dr.Close( ); Console.WriteLine("Record count, using @@ROWCOUNT = {0}",command.Parameters["@RowCount"].Value); } } }</source>
Call storedprocedure and pass in the parameter
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
public static void Main() { using (SqlConnection con = new SqlConnection()) { con.ConnectionString = @"Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI"; con.Open(); string category = "Seafood"; string year = "1999"; // Create and configure a new command. using (SqlCommand com = con.CreateCommand()) { com.rumandType = CommandType.StoredProcedure; com.rumandText = "SalesByCategory"; // Create a SqlParameter object for the category parameter. com.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = category; // Create a SqlParameter object for the year parameter. com.Parameters.Add("@OrdYear", SqlDbType.NVarChar).Value = year; // Execute the command and process the results. using (IDataReader reader = com.ExecuteReader()) { Console.WriteLine("Sales By Category ({0}).", year); while (reader.Read()) { // Display the product details. Console.WriteLine(" {0} = {1}", reader["ProductName"], reader["TotalPurchase"]); } } } } }
}</source>
Call StoredProcedure with input and output parameters
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; class MainClass {
static void Main() { string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; using ( SqlConnection conn = new SqlConnection( cstr ) ) { conn.Open(); SqlCommand cmd = new SqlCommand( "QueryVendor", conn ); cmd.rumandType = CommandType.StoredProcedure; // input parm SqlParameter name = cmd.Parameters.Add( "@name", SqlDbType.NVarChar, 15 ); name.Value = "Tom"; // output parm SqlParameter vendor = cmd.Parameters.Add( "@vendor", SqlDbType.NVarChar, 15 ); vendor.Direction = ParameterDirection.Output; // return value SqlParameter rowCount = cmd.Parameters.Add( "@rowCount", SqlDbType.Int ); rowCount.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); if ( (int)rowCount.Value > 0 ) { Console.WriteLine(" is available from " + vendor.Value ); } else { Console.WriteLine(" not available from " + vendor.Value ); } } }
}</source>
Call stored procedure with no parameter
<source lang="csharp">/* Quote from
Beginning C# 2005 Databases From Novice to Professional
- Paperback: 528 pages
- Publisher: Apress (December 18, 2006)
- Language: English
- ISBN-10: 159059777X
- ISBN-13: 978-1590597774
- /
using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main() { SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind"); try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.rumandType = CommandType.StoredProcedure; cmd.rumandText = "sp_select_all_employees"; SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()){ Console.WriteLine("{0} {1} {2}" , rdr[0].ToString().PadRight(5) , rdr[1].ToString() , rdr[2].ToString() ); } rdr.Close(); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { conn.Close(); } }
} /* create procedure sp_Select_All_Employees as
select employeeid, firstname, lastname from employees
- /</source>
Call stored procedure with parameter and return value
<source lang="csharp">/* Quote from
Beginning C# 2005 Databases From Novice to Professional
- Paperback: 528 pages
- Publisher: Apress (December 18, 2006)
- Language: English
- ISBN-10: 159059777X
- ISBN-13: 978-1590597774
- /
using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main() { SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind"); try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.rumandType = CommandType.StoredProcedure; cmd.rumandText = "sp_orders_by_employeeid2"; SqlParameter inparm = cmd.Parameters.Add("@employeeid", SqlDbType.Int); inparm.Direction = ParameterDirection.Input; inparm.Value = 2; SqlParameter ouparm = cmd.Parameters.Add("@ordercount", SqlDbType.Int); ouparm.Direction = ParameterDirection.Output; SqlParameter retval = cmd.Parameters.Add("return_value", SqlDbType.Int); retval.Direction = ParameterDirection.ReturnValue; SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine("{0} {1}" , rdr[0].ToString().PadRight(5) , rdr[1].ToString() ); } rdr.Close(); Console.WriteLine("The output parameter value is {0}", cmd.Parameters["@ordercount"].Value); Console.WriteLine("The return value is {0}", cmd.Parameters["return_value"].Value); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { conn.Close(); } }
} /* create procedure sp_Orders_By_EmployeeId2
@employeeid int, @ordercount int = 0 output
as
select orderid, customerid from orders where employeeid = @employeeid; select @ordercount = count(*) from orders where employeeid = @employeeid return @ordercount
- /</source>
Call stored procedure with parameters using SqlCommand
<source lang="csharp">using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string SQL = "SELECT * FROM Orders"; string ConnectionString ="Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=localhost;"; SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand StoredProcedureCommand = new SqlCommand("Sales By Year", conn); StoredProcedureCommand.rumandType = CommandType.StoredProcedure; SqlParameter myParm1 = StoredProcedureCommand.Parameters.Add( "@Beginning_Date", SqlDbType.DateTime, 20); myParm1.Value = "7/1/1996"; SqlParameter myParm2 = StoredProcedureCommand.Parameters.Add("@Ending_Date", SqlDbType.DateTime, 20); myParm2.Value = "7/31/1996"; conn.Open(); SqlDataReader TheReader = StoredProcedureCommand.ExecuteReader(); string orderlist = ""; while (TheReader.Read()) { string nextID = TheReader["OrderID"].ToString(); string nextSubtotal = TheReader["Subtotal"].ToString(); orderlist += nextID + "\t" + nextSubtotal + "\n"; } conn.Close(); }
}</source>
Call stored procedure with SqlCommand
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Data Source=.\SQLEXPRESS;" + @"AttachDbFilename="NORTHWND.MDF";" + @"Integrated Security=True;Connect Timeout=30;User Instance=true"); thisConnection.Open(); SqlCommand thisCommand = thisConnection.CreateCommand(); thisCommand.rumandType = CommandType.StoredProcedure; thisCommand.rumandText ="TenRecoreds"; SqlDataReader thisReader = thisCommand.ExecuteReader(); while (thisReader.Read()) { Console.WriteLine(thisReader["UnitPrice"]); } thisReader.Close(); thisConnection.Close(); } }</source>
Catch exception when calling stored procedure
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main() { SqlConnection conn = new SqlConnection(@"data source = .\sqlexpress;integrated security = true;database = northwind"); SqlCommand cmd = conn.CreateCommand(); cmd.rumandType = CommandType.StoredProcedure; try { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Close(); } catch (System.Data.SqlClient.SqlException ex) { Console.WriteLine("Source: " + ex.Source); Console.WriteLine("Exception Message: " + ex.Message); } catch (System.Exception ex) { Console.WriteLine("Source: " + ex.Source); Console.WriteLine("Exception Message: " + ex.Message); } finally { if (conn.State == ConnectionState.Open) { Console.WriteLine("Finally block closing the connection"); conn.Close(); } } }
}</source>
Raising and Handling Stored Procedure Errors
<source lang="csharp">/* CREATE PROCEDURE RaiseError
@Severity int, @State int = 1
AS
RAISERROR ("Error of severity %d raised from stored procedure RaiseError.", @Severity, @State, @Severity) RAISERROR ("Error of severity %d raised from stored procedure RaiseError.", @Severity, @State, @Severity) WITH LOG RETURN
- /
using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; using (SqlConnection connection = new SqlConnection(sqlConnectString)) { connection.InfoMessage += new SqlInfoMessageEventHandler(SqlMessageEventHandler); SqlCommand command = new SqlCommand("RaiseError", connection); command.rumandType = CommandType.StoredProcedure; command.Parameters.Add("@Severity", SqlDbType.Int); command.Parameters.Add("@State", SqlDbType.Int); for(int severity = -1; severity <= 26; severity++){ command.Parameters["@Severity"].Value = severity; command.Parameters["@State"].Value = 0; connection.Open( ); command.ExecuteNonQuery( ); connection.Close( ); } } } private static void SqlMessageEventHandler(object sender,SqlInfoMessageEventArgs e) { Console.WriteLine("MESSAGE: {0}", e.Message); } }</source>
Retrieves stored procedure parameter information
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; public class AddCustomer {
public static void Main() { string connectionString = "Data Source=localhost;Initial Catalog=store;Integrated Security=SSPI"; string procedure = "CustomerAdd"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(procedure, con); cmd.rumandType = CommandType.StoredProcedure; con.Open(); SqlCommandBuilder.DeriveParameters(cmd); cmd.Parameters[1].Value = "name"; cmd.Parameters[2].Value = "j@m.ru"; cmd.Parameters[3].Value = "password"; cmd.Parameters[4].Value = DBNull.Value; cmd.ExecuteNonQuery(); con.Close(); Console.WriteLine("New ID: " +cmd.Parameters[4].Value); }
}</source>
Retrieving a Return Value from a Stored Procedure
<source lang="csharp">/* CREATE PROCEDURE Person.GetContacts
@RowCount int OUTPUT
AS
SET NOCOUNT ON SELECT * FROM Person.Contact SET @RowCount = @@ROWCOUNT RETURN @RowCount
- /
using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; using (SqlConnection connection = new SqlConnection(sqlConnectString)) { SqlCommand command =new SqlCommand("Person.GetContacts", connection); command.rumandType = CommandType.StoredProcedure; command.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output; SqlParameter retParam = command.Parameters.Add("@RetVal", SqlDbType.Int); retParam.Direction = ParameterDirection.ReturnValue; connection.Open( ); SqlDataReader dr = command.ExecuteReader( ); Console.WriteLine("After execution, return value = {0}", retParam.Value); int rowCount = 0; while (dr.Read( )){ rowCount++; } dr.Close( ); connection.Close( ); } } }</source>
Retrieving a Stored Procedure Output Parameter
<source lang="csharp">/* CREATE PROCEDURE Person.GetContacts
@RowCount int OUTPUT
AS
SET NOCOUNT ON SELECT * FROM Person.Contact SET @RowCount = @@ROWCOUNT RETURN @RowCount
- /
using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; using (SqlConnection connection = new SqlConnection(sqlConnectString)) { SqlCommand command = new SqlCommand("Person.GetContacts", connection); command.rumandType = CommandType.StoredProcedure; command.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output; connection.Open( ); SqlDataReader dr = command.ExecuteReader( ); int rowCount = 0; while (dr.Read( )) { rowCount++; } dr.Close( ); connection.Close( ); } } }</source>
Retrieving Data Using a SQL Server Stored Procedure
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "uspGetEmployeeManagers"; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); command.rumandType = CommandType.StoredProcedure; command.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = 100; DataTable dt = new DataTable( ); SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); foreach (DataRow row in dt.Rows) { Console.WriteLine(row["RecursionLevel"]); Console.WriteLine(row["EmployeeID"]); Console.WriteLine(row["LastName"]); Console.WriteLine(row["FirstName"]); Console.WriteLine(row["ManagerID"]); Console.WriteLine(row["ManagerLastName"]); Console.WriteLine(row["ManagerFirstName"]); } } }</source>