C#调用SQl存储过程

Question

一个小程序无法获取分页后数据。请高手帮忙DeBug一下，留个地址我把源代码发过去！谢谢！

Answer 1

using (SqlConnection sqlCon = new SqlConnection(""))
{
sqlCon.Open();
sqlComm = new SqlCommand("", sqlCon);
sqlComm.CommandType = CommandType.StoredProcedure;sqlComm.Parameters.Add("@tblName ", SqlDbType.VarChar,200);
sqlComm.Parameters.Add("@fldName ", SqlDbType.VarChar, 200);
sqlComm.Parameters.Add("@pageCountint ", SqlDbType.int);
...
sqlComm.Parameters["@pageCountint "].Direction = ParameterDirection.Output;
sqlComm.Parameters["@tblName "].Value = "";
sqlComm.Parameters["@fldName "].Value = "";
...
sqlComm.ExecuteNonQuery();
strng c= sqlComm.Parameters["@pageCountint "].Value.ToString();
}
http://topic.csdn.net/u/20091204/21/722689e1-7824-497c-b709-4b1118264633.html

Answer 2

//对应SQL Server，调用存储过程， public  class DataGridPaging : System.Web.UI.Page     {         private static string connString = ConfigurationSettings.AppSettings["ConnString"];         private int recordCount;         private int pageCount;          protected System.Web.UI.WebControls.LinkButton LBtnFirst;         protected System.Web.UI.WebControls.LinkButton LBtnPrev;         protected System.Web.UI.WebControls.LinkButton LBtnNext;         protected System.Web.UI.WebControls.LinkButton LBtnLast;          protected System.Web.UI.WebControls.Literal LtlPageIndex;         protected System.Web.UI.WebControls.Literal LtlPageCount;         protected System.Web.UI.WebControls.Literal LtlPageSize;         protected System.Web.UI.WebControls.Literal LtlRecordCount;         protected System.Web.UI.WebControls.DataGrid DataGrid1;             private void Page_Load(object sender, System.EventArgs e)         {              if(!Page.IsPostBack)              {                   DataGridDataBind();              }         }          //绑定数据         private void DataGridDataBind()         {              DataSet ds = GetCustomersData();              recordCount = ds.Tables[0].Rows.Count;              //获取当前的页数              pageCount = (int)Math.Ceiling( recordCount * 1.0 / PageSize);              //避免纪录从有到无时，并且已经进行过反页的情况下CurrentPageIndex > PageCount出错              if(recordCount ==0)              {                   this.DataGrid1.CurrentPageIndex = 0;              }              else if(this.DataGrid1.CurrentPageIndex >= pageCount)              {                   this.DataGrid1.CurrentPageIndex = pageCount - 1;              }              this.DataGrid1.DataSource = ds;              this.DataGrid1.DataBind();              NavigationStateChange();         }          #region Web 窗体设计器生成的代码         override protected void OnInit(EventArgs e)         {              //              // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。              //              InitializeComponent();              base.OnInit(e);         }                 /// <summary>         /// 设计器支持所需的方法 - 不要使用代码编辑器修改         /// 此方法的内容。         /// </summary>         private void InitializeComponent()         {                 this.LBtnFirst.Click += new System.EventHandler(this.LBtnNavigation_Click);              this.LBtnPrev.Click += new System.EventHandler(this.LBtnNavigation_Click);              this.LBtnNext.Click += new System.EventHandler(this.LBtnNavigation_Click);              this.LBtnLast.Click += new System.EventHandler(this.LBtnNavigation_Click);              this.Load += new System.EventHandler(this.Page_Load);          }         #endregion          private void LBtnNavigation_Click(object sender, System.EventArgs e)         {              LinkButton btn = (LinkButton)sender;              switch(btn.CommandName)              {                   case "First":                       PageIndex = 0;                       break;                   case "Prev"://if( PageIndex > 0 )                            PageIndex = PageIndex - 1;                       break;                   case "Next"://if( PageIndex < PageCount -1)                            PageIndex = PageIndex + 1;                       break;                   case "Last":                       PageIndex = PageCount - 1;                       break;              }              DataGridDataBind();                      }         //数据绑定         public static DataSet GetCustomersData()         {              SqlConnection conn = new SqlConnection(connString);              string sqlStr = "SELECT CustomerID, CompanyName,Address,Phone FROM Customers";              SqlCommand comm = new SqlCommand( sqlStr ,conn);              SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);              DataSet ds = new DataSet();              dataAdapter.Fill(ds);              return ds;         }          /// <summary>         /// 控制导航按钮或数字的状态         /// </summary>         public void NavigationStateChange()         {              if( PageCount <= 1 )//( RecordCount <= PageSize )//小于等于一页              {                   this.LBtnFirst.Enabled = false;                   this.LBtnPrev.Enabled = false;                   this.LBtnNext.Enabled = false;                   this.LBtnLast.Enabled = false;              }              else //有多页              {                   if( PageIndex == 0 )//当前为第一页                   {                       this.LBtnFirst.Enabled = false;                       this.LBtnPrev.Enabled = false;                       this.LBtnNext.Enabled = true;                       this.LBtnLast.Enabled = true;                                                       }                   else if( PageIndex == PageCount - 1 )//当前为最后页                   {                       this.LBtnFirst.Enabled = true;                       this.LBtnPrev.Enabled = true;                       this.LBtnNext.Enabled = false;                       this.LBtnLast.Enabled = false;                                                       }                   else //中间页                   {                       this.LBtnFirst.Enabled = true;                       this.LBtnPrev.Enabled = true;                       this.LBtnNext.Enabled = true;                       this.LBtnLast.Enabled = true;                   }                                }              if(RecordCount == 0)//当没有纪录时DataGrid.PageCount会显示1页                   this.LtlPageCount.Text = "0";              else                   this.LtlPageCount.Text = PageCount.ToString();              if(RecordCount == 0)                   this.LtlPageIndex.Text = "0";              else                   this.LtlPageIndex.Text = (PageIndex + 1).ToString();//在有页数的情况下前台显示页数加1              this.LtlPageSize.Text = PageSize.ToString();              this.LtlRecordCount.Text = RecordCount.ToString();         }                  // 总页数         public int PageCount         {              get{return this.DataGrid1.PageCount;}         }         //页大小         public int PageSize         {              get{return this.DataGrid1.PageSize;}         }         //页索引，从零开始         public int PageIndex         {              get{return this.DataGrid1.CurrentPageIndex;}              set{this.DataGrid1.CurrentPageIndex = value;}         }         // 纪录总数         public int RecordCount         {              get{return recordCount;}              set{recordCount = value;}         }     }
更多可以参考我的文章：
http://blog.csdn.net/zhzuo/archive/2004/09/22/113429.aspx
http://blog.csdn.net/zhzuo/archive/2004/10/28/156647.aspx

Answer 3

这里使用的数据源还是假设为Northwind的Customers表。下面是访问单页的存储过程，实现方式很多，不过这个是最普通的，CREATE PROCEDURE [GetCustomersDataPage]         @PageIndex INT,         @PageSize  INT,         @RecordCount INT OUT,         @PageCount INT OUTASSELECT @RecordCount = COUNT(*)  FROM   CustomersSET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)DECLARE @SQLSTR NVARCHAR(1000)IF @PageIndex = 0 OR @PageCount <= 1         SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+'  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID DESC'ELSE IF     @PageIndex = @PageCount - 1                     SET @SQLSTR =N' SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+'  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID ASC ) TempTable  ORDER BY CustomerID DESC'ELSE                 SET @SQLSTR =N' SELECT TOP  '+STR( @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+'  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC'EXEC (@SQLSTR) GO获取记录数和页数都采用存储过程的输出参数。获取数据源，这里返回一个DataSet。先定义了连个数据成员，private int pageCount;//页数private int recordCount;//记录数//获取单页数据private static DataSet GetCustomersData(int pageIndex,int pageSize,ref int recordCount,ref int pageCount){     string connString = ConfigurationSettings.AppSettings["ConnString"];     SqlConnection conn = new SqlConnection(connString);     SqlCommand comm = new SqlCommand("GetCustomersDataPage",conn);      comm.Parameters.Add(new SqlParameter("@PageIndex",SqlDbType.Int));     comm.Parameters[0].Value = pageIndex;      comm.Parameters.Add(new SqlParameter("@PageSize",SqlDbType.Int));     comm.Parameters[1].Value = pageSize;      comm.Parameters.Add(new SqlParameter("@RecordCount",SqlDbType.Int));     comm.Parameters[2].Direction = ParameterDirection.Output;      comm.Parameters.Add(new SqlParameter("@PageCount",SqlDbType.Int));     comm.Parameters[3].Direction = ParameterDirection.Output;      comm.CommandType = CommandType.StoredProcedure;     SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);     DataSet ds = new DataSet();     dataAdapter.Fill(ds);      recordCount = (int)comm.Parameters[2].Value;     pageCount = (int)comm.Parameters[3].Value;      return ds;}

Answer 4

SQL Server 2005 对分页存储过程有了更多的支持，参考这里
简单谈基于SQL SERVER 分页存储过程的演进 
http://blog.csdn.net/zhzuo/archive/2006/09/30/1313274.aspx