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  --11asbegin    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 =100end

代碼

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

        }