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

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);
    
  }
}

Call stored procedure

/*
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);
            }
        }
    }

Call storedprocedure and pass in the parameter

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"]);
                    }
                }
            }
        }
    }
}

Call StoredProcedure with input and output parameters

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 );
            }
        }    
        
    }
}

Call stored procedure with no parameter

/*
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
*/

Call stored procedure with parameter and return value

/*
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
*/

Call stored procedure with parameters using SqlCommand

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();
    }
}

Call stored procedure with SqlCommand

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();
    }
  }

Catch exception when calling stored procedure

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();
            }
         }
   }
}

Raising and Handling Stored Procedure Errors

/*
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);
        }
    }

Retrieves stored procedure parameter information

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);
    }
}

Retrieving a Return Value from a Stored Procedure

/*
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( );
            }
        }
    }

Retrieving a Stored Procedure Output Parameter

/*
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( );
            }
        }
    }

Retrieving Data Using a SQL Server Stored Procedure

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"]);
            }
        }
    }

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

Содержание

Call a stored procedure

Call stored procedure

Call storedprocedure and pass in the parameter

Call StoredProcedure with input and output parameters

Call stored procedure with no parameter

Call stored procedure with parameter and return value

Call stored procedure with parameters using SqlCommand

Call stored procedure with SqlCommand

Catch exception when calling stored procedure

Raising and Handling Stored Procedure Errors

Retrieves stored procedure parameter information

Retrieving a Return Value from a Stored Procedure

Retrieving a Stored Procedure Output Parameter

Retrieving Data Using a SQL Server Stored Procedure

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация