Csharp/CSharp Tutorial/ADO.Net/SqlConnection Stored Procedure

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

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

  1. Paperback: 528 pages
  2. Publisher: Apress (December 18, 2006)
  3. Language: English
  4. ISBN-10: 159059777X
  5. 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

  1. Paperback: 528 pages
  2. Publisher: Apress (December 18, 2006)
  3. Language: English
  4. ISBN-10: 159059777X
  5. 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>