在C#中调用存储过程

 

1：先在SQL中创建一个存储过程：

create procedure Select_ALL_Employ
as
select EmployeeID ,FirstName ,LastName
from Employees

 存储过程的执行：execute Select_ALL_Employ

2：在VS2008中调用存储过程 ，新建一个控制台应用程序

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Use_procedure
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";
            try
            {
                con.Open ();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Select_ALL_Employ";

                SqlDataReader mydr = cmd.ExecuteReader();
                while (mydr.Read())
                {
                    Console.WriteLine("{0} {1} {2}", mydr[0].ToString().PadRight(5),
                        mydr[1].ToString(), mydr[2].ToString());
                }
                mydr.Close();
            }
            catch(SqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                con.Close();
            }
        }
    }
}

---------------------------------------------------------------------

创建一个带参数的存储过程

1 创建存储过程：

create procedure Orders_By_EmployeeID
@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

 存储过程的执行：

declare @return_value int,
@ordercount int
execute @return_value =Orders_By_EmployeeID
@employeeid=2,
@ordercount =@ordercount output
select @ordercount as '@ordercount'
select 'return value'=@return_value

2创建一个控制台应该程序

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace use_procedure_with_parameter
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Orders_By_EmployeeID";

                //加入参数
                SqlParameter inparm = cmd.Parameters.Add("@employeeID", SqlDbType.Int);
                inparm.Direction = ParameterDirection.Input;
                inparm.Value = 2;

                SqlParameter outparm = cmd.Parameters.Add("@ordercount", SqlDbType.Int);
                outparm.Direction = ParameterDirection.Output;

                SqlParameter return_value = cmd.Parameters.Add("@return_value", SqlDbType.Int);
                return_value.Direction = ParameterDirection.ReturnValue;

                SqlDataReader mydr = cmd.ExecuteReader();
                while (mydr.Read())
                {
                    Console.WriteLine("{0} {1}", mydr[0].ToString().PadRight(5), mydr[1].ToString());
                }
                mydr.Close();

                //输出参数
                Console.WriteLine("The output value is {0}",cmd.Parameters ["@return_value"].Value );
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                con.Close();
            }
        }
    }
}