建立一个表:post,字段:ID(int,主键),a1,a2,a3,a4,a5,a6
建立一个存储过程,代码如下:
- ALTER proc [dbo].[GetDataSet]
- @TableList Varchar(200)='*',--搜索表的字段,比如:’id,datatime,job‘,用逗号隔开
- @TableName Varchar(100), --搜索的表名
- @SelectWhere Varchar(500),--搜索条件,这里不用写where,比如:job=’teacher‘and class='2'
- @SelectOrderId Varchar(20),--表主键字段名。比如:id
- @SelectOrder Varchar(200)='', --排序,可以使用多字段排序但主键字段必需在最前面.也可以不写,比如:order by class asc
- @intPageNo int=1, --页号
- @intPageSize int=10 ,--每页显示数
- @RecordCount int OUTPUT --总记录数(存储过程输出参数)
- as
- declare @TmpSelect NVarchar(600)
- declare @Tmp NVarchar(600)
- set nocount on--关闭计数
- set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' '+@SelectWhere
- execute sp_executesql
- @TmpSelect, --执行上面的sql语句
- N'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数
- @RecordCount OUTPUT
- --if (@RecordCount = 0) --如果没有贴子,则返回零
- --return 0
- /*判断页数是否正确*/
- if (@intPageNo - 1) * @intPageSize > @RecordCount --页号大于总页数,返回错误
- return (-1)
- set nocount off--打开计数
- if @SelectWhere != ''
- begin
- set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' '+@SelectWhere+' and '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectWhere +' '+@SelectOrder+') '+@SelectOrder
- end
- else
- begin
- set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrder
- end
- execute sp_executesql @TmpSelect
- return(@@rowcount)
新建一个post.aspx文件,写入以下代码:
- <%@ Page Language="C#" AutoEventWireup="true" CodeFile="post.aspx.cs" Inherits="post" %>
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml" >
- <head runat="server">
- <title>无标题页</title>
- <script type="text/javascript">
- function CheckAllGridCheckBox(formName,obj, name)
- {
- var elements = document.forms[formName].elements;
- for (var i=0; i<elements.length; i++)
- {
- if (elements[i].type == 'checkbox')
- {
- if (elements[i].name.indexOf(name) > -1)
- {
- elements[i].checked = obj.checked;
- }
- }
- }
- }
- function showsubmenu(sid)
- {
- whichEl = eval("info" + sid);
- if (whichEl.style.display == "none")
- {
- eval("info" + sid + ".style.display=/"/";");
- }
- else
- {
- eval("info" + sid + ".style.display=/"none/";");
- }
- }
- </script>
- </head>
- <body>
- <form id="form1" runat="server">
- <asp:Repeater ID="PostList" runat="server">
- <HeaderTemplate>
- <table width="100%" border="0" cellpadding="0" cellspacing="0">
- <tr>
- <td style="text-align:center; background:#F1D4D8; height: 30px; width:5%; border-top:#DAC6C7 1px solid;"> </td>
- <td style="text-align:center; background:#F1D4D8; height: 30px; width:5%; border-top:#DAC6C7 1px solid;"> </td>
- <td style="text-align:center; background:#F1D4D8; height: 30px; width:10%; border-top:#DAC6C7 1px solid;">标题</td>
- <td style="text-align:center; background:#F1D4D8; height: 30px; width:45%; border-top:#DAC6C7 1px solid;">标题</td>
- <td style="text-align:center; background:#F1D4D8; height: 30px; width:10%; border-top:#DAC6C7 1px solid;">标题</td>
- <td style="text-align:center; background:#F1D4D8; height: 30px; width:10%; border-top:#DAC6C7 1px solid;">标题</td>
- <td style="text-align:center; background:#F1D4D8; height: 30px; width:10%; border-top:#DAC6C7 1px solid;">标题</td>
- <td style="text-align:center; background:#F1D4D8; height: 30px; width:5%; border-top:#DAC6C7 1px solid;">标题</td>
- </tr>
- </HeaderTemplate>
- <ItemTemplate>
- <tr>
- <td height="30" align="center"><input id="PostID" name="PostID" runat="server" type="checkbox" value='<%# Eval("ID") %>' /></td>
- <td align="center"><%# Eval("ID") %></td>
- <td align="center"><%# Eval("a1") %></td>
- <td align="center"><%# Eval("a2") %></td>
- <td align="center"><%# Eval("a3") %></td>
- <td align="center"><%# Eval("a4") %></td>
- <td align="center"><%# Eval("a5") %></td>
- <td align="center"><%# Eval("a6") %></td>
- </tr>
- </ItemTemplate>
- <FooterTemplate>
- </table>
- </FooterTemplate>
- </asp:Repeater>
- <div style="width:100%; text-align:left;">
- <input type=checkbox id="allcheck" onclick="CheckAllGridCheckBox('form1',this,'PostID')" />全选
- </div>
- <div style="text-align:center;">
- <asp:HyperLink ID="hylfirst" runat="server">首页</asp:HyperLink>
- <asp:HyperLink ID="hylprev" runat="server">上一页</asp:HyperLink>
- <asp:HyperLink ID="hylnext" runat="server">下一页</asp:HyperLink>
- <asp:HyperLink ID="hylend" runat="server">尾页</asp:HyperLink>
- 第<asp:Label ID="lbRow" runat="server" Text="Label"></asp:Label>页,共<asp:Label ID="lbpage" runat="server" Text="Label"></asp:Label>页,共<asp:Label
- ID="lbRecord" runat="server" Text="Label"></asp:Label>条记录 转到<asp:TextBox ID="txtlink"
- runat="server" Width="29px"></asp:TextBox>
- 页 <asp:LinkButton ID="link" runat="server" OnClick="link_Click" TabIndex="1">转到</asp:LinkButton>
- </div>
- </form>
- </body>
- </html>
post.aspx.cs文件的代码:
- using System;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
- using System.Collections;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- public partial class post : System.Web.UI.Page
- {
- SqlHelper SH = new SqlHelper();
- //这里定义存储过程分页中需要的参数
- //TableList:要读取的字段名
- //TableName:要读取的表名
- //SelectWhere:读取数据的条件,如果没有条件,默认必须为:where 1=1
- //SelectOrderId:需要索引的字段
- //SelectOrder:需要排序的条件
- //intPageNo:默认第几页
- //intPageSize:每页显示多少条
- string TableList = "ID,a1,a2,a3,a4,a5,a6";
- string TableName = "post";
- string SelectWhere = "where 1=1";
- string SelectOrderId = "id";
- string SelectOrder = "order by id desc";
- int intPageNo = 1;
- int intPageSize = 15;
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!Page.IsPostBack)
- {
- //调用绑定数据的过程,并且传递参数
- bind(TableList, TableName, SelectWhere, SelectOrderId, SelectOrder, intPageNo, intPageSize);
- }
- }
- #region 绑定数据,带分页
- protected void bind(string TableList, string TableName, string SelectWhere, string SelectOrderId, string SelectOrder, int intPageNo, int intPageSize)
- {
- int sumPage;
- int pageNo = 1;
- int pageSize = 15;
- if (Request.QueryString["Page"] == null)
- {
- pageNo = 1;
- }
- else
- {
- pageNo = Int32.Parse(Request.QueryString["Page"]);
- }
- int RecordCount;
- DataSet ds = SH.DataSelect(TableList, TableName, SelectWhere, SelectOrderId, SelectOrder, pageNo, intPageSize, out RecordCount);
- if (ds.Tables.Count == 0)
- {
- Response.Redirect("post.aspx");
- }
- PostList.DataSource = ds;
- PostList.DataBind();
- PostList.Dispose();
- lbRecord.Text = RecordCount.ToString();
- lbRow.Text = pageNo.ToString();
- sumPage = (Int32)RecordCount / pageSize;
- if (RecordCount % pageSize > 0)
- {
- sumPage = sumPage + 1;
- }
- lbpage.Text = sumPage.ToString();
- if (pageNo > 1)
- {
- hylfirst.NavigateUrl = "?Page=1";
- hylprev.NavigateUrl = string.Concat("?Page=", "", pageNo - 1);
- }
- else
- {
- hylprev.NavigateUrl = "";
- hylfirst.NavigateUrl = "";
- hylfirst.Visible = false;
- hylprev.Visible = false;
- }
- if (pageNo < sumPage)
- {
- hylend.NavigateUrl = string.Concat("?Page=", "", sumPage);
- hylnext.NavigateUrl = string.Concat("?Page=", "", pageNo + 1);
- }
- else
- {
- hylnext.NavigateUrl = "";
- hylend.NavigateUrl = "";
- hylend.Visible = false;
- hylnext.Visible = false;
- }
- }
- #endregion
- #region 点击转到第几页按钮
- protected void link_Click(object sender, EventArgs e)
- {
- int page = Convert.ToInt32(txtlink.Text);
- Response.Redirect("post.aspx?Page=" + page + "");
- }
- #endregion
- #region 删除选定的数据
- protected void btnDelete_Click(object sender, EventArgs e)
- {
- string idList = GetIdList();
- if (idList.Length > 0)
- {
- string sql = "delete from post where ID in (" + idList + ")";
- SH.ExecNon(sql);
- //调用绑定数据的过程,并且传递参数
- bind(TableList, TableName, SelectWhere, SelectOrderId, SelectOrder, intPageNo, intPageSize);
- }
- }
- #endregion
- #region 获取选中新闻的ID列表
- private string GetIdList()
- {
- string idList = "";
- HtmlInputCheckBox chk;
- foreach (RepeaterItem item in PostList.Items)
- {
- chk = item.FindControl("PostID") as HtmlInputCheckBox;
- if (chk != null && chk.Checked)
- {
- //字符类型
- idList += ((HtmlInputCheckBox)(item.FindControl("PostID"))).Value + ",";
- }
- }
- idList = idList.TrimEnd(',');
- return idList;
- }
- #endregion
- }
写一个数据库通用读取类(其中包括调用存储过程):
- using System;
- using System.Text;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- ///
- /// ASP.NET SQLSERVER数据库通用操作类(封装了一些常用数据)
- ///
- public class SqlHelper
- {
- public SqlHelper()
- {
- //
- // 此处: 添加构造函数
- //
- }
- public static string ConnectionString = ConfigurationManager.ConnectionStrings["zzyjsConn"].ConnectionString;
- //这里的zzyjsconn是web.config中的数据库连接字符串名称
- //执行SQL语句,无返回值
- #region ExecNon
- public void ExecNon(string sql)
- {
- using (SqlConnection conn = new SqlConnection(ConnectionString))
- {
- using (SqlCommand cmd = new SqlCommand(sql, conn))
- {
- cmd.Connection = conn;
- conn.Open();
- try
- {
- cmd.ExecuteNonQuery();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- conn.Close();
- conn.Dispose();
- }
- }
- }
- }
- #endregion
- ///利用存储过程分页
- #region DataSelect
- ///参数说明:
- ///TableList 要取得的字段列表,全部为*
- ///TableName 要查询的表名
- ///SelectWhere 查询条件,不可为空
- ///SelectOrderID 排序字段
- ///SelectOrder 排序方式
- ///intPageNo 当前页码
- ///intPageSize 每页显示的记录数
- ///RecordCount 返回值,查询出来的总记录数,传递参数时无需初始化
- public DataSet DataSelect(string TableList, string TableName, string SelectWhere, string SelectOrderId, string SelectOrder, int intPageNo, int intPageSize, out int RecordCount)
- {
- SqlConnection conn = new SqlConnection(ConnectionString);
- SqlDataAdapter da = new SqlDataAdapter();
- da.SelectCommand = new SqlCommand();
- da.SelectCommand.Connection = conn;
- da.SelectCommand.CommandText = "getdataset";
- da.SelectCommand.CommandType = CommandType.StoredProcedure;
- da.SelectCommand.Parameters.Add("@TableList", SqlDbType.VarChar, 200).Value = TableList;
- da.SelectCommand.Parameters.Add("@TableName", SqlDbType.VarChar, 100).Value = TableName;
- da.SelectCommand.Parameters.Add("@SelectWhere", SqlDbType.VarChar, 500).Value = SelectWhere;
- da.SelectCommand.Parameters.Add("@SelectOrderId", SqlDbType.VarChar, 20).Value = SelectOrderId;
- da.SelectCommand.Parameters.Add("@SelectOrder", SqlDbType.VarChar, 200).Value = SelectOrder;
- da.SelectCommand.Parameters.Add("@intPageNo", SqlDbType.Int).Value = intPageNo;
- da.SelectCommand.Parameters.Add("@intPageSize", SqlDbType.Int).Value = intPageSize;
- da.SelectCommand.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
- DataSet ds = new DataSet();
- da.Fill(ds);
- RecordCount = (Int32)da.SelectCommand.Parameters["@RecordCount"].Value; //求出总记录数,该值是output出来的值
- return ds;
- conn.Close();
- }
- #endregion
- }