存储过程和分页控件的使用

最新推荐文章于 2024-09-15 14:31:39 发布

whtao

最新推荐文章于 2024-09-15 14:31:39 发布

阅读量333

点赞数

文章标签：存储 parameters dataset datagrid string webform

上一篇我们得到了一个经典的分页存储过程，现在就是用它！

1、Aspx文件：

<%@ Register TagPrefix="cc1" Namespace="Sdtcn.WebControl" Assembly="Sdtcn" %>
<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="ManagementWeb.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
  <title>WebForm1</title>
  <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
  <meta name="CODE_LANGUAGE" Content="C#">
  <meta name="vs_defaultClientScript" content="JavaScript">
  <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
  <form id="Form1" method="post" runat="server">
   <cc1:SqlPager id="SqlPager1" runat="server" PageSize="16" Width="100%" NumericPage="True" BackColor="Silver"></cc1:SqlPager>
   <asp:DataGrid id="DataGrid1" runat="server" Width="100%" BorderColor="Silver" BorderStyle="None"
    BorderWidth="1px" BackColor="White" CellPadding="4">
    <FooterStyle ForeColor="#330099" BackColor="#FFFFCC"></FooterStyle>
    <SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66"></SelectedItemStyle>
    <ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
    <HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#8080FF"></HeaderStyle>
    <PagerStyle HorizontalAlign="Center" ForeColor="#330099" BackColor="#FFFFCC"></PagerStyle>
   </asp:DataGrid>
  </form>
</body>
</HTML>

2、CS文件：

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace ManagementWeb
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
  protected Sdtcn.WebControl.SqlPager SqlPager1;
  protected System.Web.UI.WebControls.DataGrid DataGrid1;
  private string connectionString = "Server=127.0.0.1;UID=sa;PWD=sapassword;database=pubs";

  private string tableNames = "authors inner join titleauthor on authors.au_id = titleauthor.au_id";
  private string primaryKey = "authors.au_id";
  private string fields  = "authors.au_id, authors.au_lname, authors.au_fname, titleauthor.title_id, titleauthor.au_ord, titleauthor.royaltyper";
  private string filter  = "authors.au_id like '%1%'";
  private string group  = string.Empty;
  private string order  = "authors.au_id desc";

  private void Page_Load(object sender, System.EventArgs e)
  {
   if(!this.IsPostBack)
   {
    SqlPager1.RecordCount = GetRecordCount();
    BindData();
   }
  }

  #region Web 窗体设计器生成的代码
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }

  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.SqlPager1.PageIndexChanged += new Sdtcn.WebControl.SqlPager.PageChangedEventHandler(this.SqlPager1_PageIndexChanged);
   this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

  private void SqlPager1_PageIndexChanged(object sender, Sdtcn.WebControl.PageChangedEventArgs e)
  {
   BindData();
  }

  private int GetRecordCount()
  {
   string sqlString = string.Format("Select Count(*) From {0} Where {1}",tableNames,filter);
   int result;
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    connection.Open();
    SqlCommand command = new SqlCommand(sqlString,connection);
    result = int.Parse(command.ExecuteScalar().ToString());
   }
   return result;
  }

  private void BindData()
  {
   int pageSize  = SqlPager1.ItemsPerPage;
   int currentPage  = SqlPager1.CurrentPageIndex;
   DataGrid1.DataSource = GetList(tableNames,primaryKey,fields,pageSize,currentPage,filter,group,order);
   DataGrid1.DataBind();
  }

  /// <summary>
  /// 分页获取数据列表
  /// </summary>
  public DataSet GetList(string tableNames,string primaryKey,string fields,int pageSize,int currentPage,string filter,string group,string order)
  {
   //@TableNames VARCHAR(200),  --表名，可以是多个表，但不能用别名
   //@PrimaryKey VARCHAR(100),  --主键，可以为空，但@Order为空时该值不能为空
   //@Fields    VARCHAR(200),  --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
   //@PageSize INT,    --每页记录数
   //@CurrentPage INT,    --当前页，0表示第1页
   //@Filter VARCHAR(200) = '',    --条件，可以为空，不用填 where
   //@Group VARCHAR(200) = '',  --分组依据，可以为空，不用填 group by
   //@Order VARCHAR(200) = ''  --排序，可以为空，为空默认按主键升序排列，不用填 order by

   SqlParameter[] parameters = {
           new SqlParameter("@TableNames", SqlDbType.VarChar, 500),
           new SqlParameter("@PrimaryKey", SqlDbType.VarChar, 100),
           new SqlParameter("@Fields", SqlDbType.VarChar, 500),
           new SqlParameter("@PageSize", SqlDbType.Int),
           new SqlParameter("@CurrentPage", SqlDbType.Int),
           new SqlParameter("@Filter", SqlDbType.VarChar, 500),
           new SqlParameter("@Group", SqlDbType.VarChar, 500),
           new SqlParameter("@Order", SqlDbType.VarChar, 500)
          };
   parameters[0].Value = tableNames;
   parameters[1].Value = primaryKey;
   parameters[2].Value = fields;
   parameters[3].Value = pageSize;
   parameters[4].Value = currentPage;
   parameters[5].Value = filter;
   parameters[6].Value = group;
   parameters[7].Value = order;
   return RunProcedure("UP_GetRecordByPage",parameters,"ds");
  }

  public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    DataSet dataSet = new DataSet();
    connection.Open();
    SqlDataAdapter sqlDA = new SqlDataAdapter();

    SqlCommand command = new SqlCommand( storedProcName, connection );
    command.CommandType = CommandType.StoredProcedure;
    foreach (SqlParameter parameter in parameters)
    {
     command.Parameters.Add( parameter );
    }

    sqlDA.SelectCommand = command;
    sqlDA.Fill( dataSet, tableName );
    connection.Close();
    return dataSet;
   }
  }
}
}