ADO.NET 2.调用存储过程

目录：

1.调用存储过程

1. 1.个参数调用存储过程

存储过程 usp_GetBookCateId

1. 2.多个参数调用存储过程

存储过程 usp_GetPagesBooks

1. 3.调用带输出参数的存储过程 output

 

1.调用存储过程

1.个参数调用存储过程

存储过程 usp_GetBookCateId

代码

        static void Main(string[] args)
        {
            //Console.WriteLine("增加数据：");
            //string ss = Convert.ToString(Console.ReadLine());//字符串使用Console.ReadLine()
            //AddRecord(ss);
            //QuerySingle();
            //QueryList();
            //QueryListByAdapter();
            QueryListByProcedure();
        }

        //调用存储过程
        public static void QueryListByProcedure()
        {
            SqlConnection conn = new SqlConnection(sqlCon);
            SqlCommand cmd = new SqlCommand("usp_GetBookByCateId", conn);
          

            cmd.CommandType = CommandType.StoredProcedure;//指定命令类型为存储过程

            SqlParameter sp = new SqlParameter();//指定存储过程中的参数

            sp.ParameterName = "@cateId";

            sp.SqlDbType = SqlDbType.Int;

            sp.Size = 4;

            sp.Value = 2;//该参数的值

            cmd.Parameters.Add(sp);

//将参数对象加入命令对象的参数集合中

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Console.WriteLine(dr[0].ToString() + " " 
                    + dr[1].ToString()+ " " + dr[2].ToString()+ " " + dr[3].ToString());
            }
        }

结果

 

2.多个参数调用存储过程

存储过程 usp_GetPagesBooks

ALTER procedure [dbo].[usp_GetPagesBooks]
@PageIndex int = 1,@PageSize int = 5,
@RowCount int output,
@PageCount int output  --11
as
begin    
select * from     
(    
select ROW_NUMBER() over(order by c_id) as nid,* from Category    
)     
as a    
 where a.nid > (@PageIndex - 1)* @PageSize and a.nid<= @PageIndex * @PageSize    
 select @RowCount = COUNT(*) from Category
 select @PageCount = ceiling(CONVERT(float,@RowCount)/CONVERT(float,@PageSize))
 set @PageIndex =10000    
 set @PageSize =100
end

代码

        static void Main(string[] args)
        {
            //Console.WriteLine("增加数据：");
            //string ss = Convert.ToString(Console.ReadLine());//字符串使用Console.ReadLine()
            //AddRecord(ss);
            //QuerySingle();
            //QueryList();
            //QueryListByAdapter();
            //QueryListByProcedure();
            QueryListByProcedure2();
        }

        //调用多参数存储过程
        public static void QueryListByProcedure2()
        {
            SqlConnection conn = new SqlConnection(sqlCon);
            SqlCommand cmd = new SqlCommand("usp_GetPagesBooks", conn);

            cmd.CommandType = CommandType.StoredProcedure;//指定命令类型为存储过程
            SqlParameter[] sp = {
                new SqlParameter("@PageIndex", SqlDbType.Int, 4),
                new SqlParameter("@PageSize", SqlDbType.Int, 4),
                new SqlParameter("@RowCount", SqlDbType.Int, 4),
                new SqlParameter("@PageCount", SqlDbType.Int, 4)
                };
            sp[0].Value = 2;//变量：保存页码

            sp[1].Value = 4;//变量：保存页容量（每页显示多少行)

            sp[2].Value = 0;//总行数

          　sp[3].Value = 0;//总页数 

            cmd.Parameters.AddRange(sp);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Console.WriteLine(dr[0].ToString() + " "
                    + dr[1].ToString() + " " + dr[2].ToString() + " " + dr[3].ToString());
            }
        }

结果

 

3.调用带输出参数的存储过程 output

代码：

//3.调用带输出参数的存储过程
        public static void QueryListByProcedure3()
        {
            SqlConnection conn = new SqlConnection(sqlCon);
            SqlCommand cmd = new SqlCommand("usp_GetPagesBooks", conn);

            cmd.CommandType = CommandType.StoredProcedure;//指定命令类型为存储过程
            SqlParameter[] sp = {
                new SqlParameter("@PageIndex", SqlDbType.Int, 4),
                new SqlParameter("@PageSize", SqlDbType.Int, 4),
                new SqlParameter("@RowCount", SqlDbType.Int, 4),
                new SqlParameter("@PageCount", SqlDbType.Int, 4)
                };
            sp[0].Value = 2;
            sp[1].Value = 4;
            sp[2].Direction = ParameterDirection.Output;//设置参数的输出方向 

　　　　　　　sp[3].Direction = ParameterDirection.Output; 

            cmd.Parameters.AddRange(sp);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Console.WriteLine(dr[0].ToString() + " "
                    + dr[1].ToString() + " " + dr[2].ToString() + " " + dr[3].ToString());
            }
            Console.WriteLine();
             //输入RowCount和PageCount的值 

　　　　　　　　int RowCount =Convert.ToInt32(cmd.Parameters[2].Value); 

　　　　　　　　int PageCount = Convert.ToInt32(cmd.Parameters[3].Value); 

　　　　　　　　Console.WriteLine("RowCount=" + RowCount + ",PageCount=" + PageCount); 

        }