C#数据库入门-006：使用存储过程

一：介绍
    存储过程运行重复执行某个任务SQL语句。只要创建某个过程一次，就可以在程序中多次重用它。这就提供了程序的可维护性，允许程序以统一优化的方式访问数据库。
    1： 优点
        存储过程可以有用于输入或输出的参数。
        可以返回一个整数集（默认为0），也可以返回一个或多个结果集。
        可以在客户程序或其他存储过程中调用，功能强大，是数据库编程的首选模式，尤其适用于多层程序和WEB服务，源于它可以显著降低客户和数据库服务器之间的网络通讯量。

二：创建存储过程
    1： CREATE PROCEDURE sp_select_all_employees
        AS  //AS关键字把存储过程的签名与存储过程体分隔开来
         SELECT employeeid, firstname, lastname
         FROM employees;

        EXECUTE sp_select_all_employees;

    2： 创建带参数的存储过程
        CREATE PROCEDURE sp_orders_by_employeeid
         @employeeid int //默认是输入参数
        AS
         SELECT orderid, customerid
         FROM orders
         WHERE employeeid = @employeeid;

        EXECUTE sp_orders_by_employeeid 2;
    3： 带输出的存储过程
        CREATE PROCEDURE sp_orders_by_employeeid2
         @employeeid int,
         @ordercount int = 0 output//输出需要加标识
        AS
         SELECT orderid, customerid
         FROM orders
         WHERE employeeid = @employeeid;
         SELECT @ordercount = count(*)   //将新查询返回的标量赋给SELECT列表中的输出参数
         FROM orders
         WHERE employeeid = @employeeid;
         RETURN @ordercount;

        DECLARE @return_value int, @ordercount int;
        EXECUTE @return_value = sp_orders_by_employeeid2
          @employeeid = 2,
          @ordercount = @ordercount output;
        SELECT @ordercount as '@ordercount';
        SELECT 'Return value' = @return_value;

三：修改存储过程
    ALTER PROCEDURE sp_select_all_employees
    AS 
        SELECT employeeid, firstname, lastname
        FROM employees
     ORDER BY lastname, firstname;

    EXECUTE sp_select_all_employees;

四：显示存储过程的定义
    SP_HELPTEXT语句对表对象不起作用。
    EXECUTE SP_HELPTEXT 'sp_select_all_employees';
    查询->将结果保存到->将结果保存到文件（CTRL+SHIFT+F）
    Text
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    CREATE PROCEDURE sp_select_all_employees
    AS 
        SELECT employeeid, firstname, lastname
        FROM employees
     ORDER BY lastname, firstname;

五：在C#中使用存储过程
    static void Main(string[] args)
    {
        SqlConnection conn = new SqlConnection(@"server=./sqlexpress; integrated security=true; database=northwind");
        try
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_orders_by_employeeid2";

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

六：删除存储过程
    DROP PROCEDURE sp_select_all_employees;