有提供PagedDataSource 的
我这倒是有一系列的个连接，有兴趣看下嘛
/*----------------------------------------------------------------------------
* 代码作者：ATPKING来自cnblogs.com
* 版本号码：SqlCon 0.1
* 功能介绍：在一般的.net开发中，我们经常需要把SqldataSource拖到aspx里面，
* 之后看aspx页面，就觉得特别的乱，也不利于三层结构的实现，于是在
* cs文件中生成SqlDataSource，提供给数据显示控件链接。
* 使用方法：放在项目文件夹App_Code下即可。
* 完成时间：2008-4-20
* 版权声明：引用时请将此注释保留，代码可以任意拷贝及其修改。
* 附送内容：理智爱国，抵制家乐福最好办法就是不去家乐福购物，而不是去店门口闹
* --------------------------------------------------------------------------*/
using System;
using System.Data;
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;
using System.Data.Sql;
using System.Data.SqlClient;
/// <summary>
/// 数据库连接相关
/// </summary>
public class SqlCon
{
private string _sqlConnStr;//链接字符串
private SqlConnection _conn;//链接数据库控件
public string SqlConnStr//对外显示的连接字符串
{
set
{
_sqlConnStr = value;
}
get
{
return _sqlConnStr;
}
}
/// <summary>
/// 链接到web.config 的第一个连接字符串
/// </summary>
public SqlCon()
{
_sqlConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
_conn = new SqlConnection(_sqlConnStr);
}
/// <summary>
/// 链接到web.config 的制定的链接字符串
/// </summary>
/// <param name="connectName">链接名</param>
public SqlCon(string connectName)
{
_sqlConnStr = ConfigurationManager.ConnectionStrings[connectName].ConnectionString;
_conn = new SqlConnection(_sqlConnStr);
}
/// <summary>
/// 将select语句实行并返回一个table
/// </summary>
/// <param name="sqlStr">select 语句</param>
/// <returns>执行select后的一个table</returns>
public DataTable tableFromSql(string sqlStr)
{
_conn.Open();
DataTable dt = new DataTable ();
DataSet dataset = new DataSet();
SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlStr, _conn);
dataAdapter.Fill(dt);
return dt;
}
/// <summary>
/// 返回一个DataSource,它的链接为类的链接字符串，语句为参数语句
/// </summary>
/// <param name="sqlStr">slecet语句</param>
/// <returns>一个dataSource</returns>
public SqlDataSource dataSourceFromSql(string sqlStr)
{
SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
return ds;
}
/// <summary>
/// 返回一个带 QueryString参数的SqldataSource
/// </summary>
/// <param name="sqlStr">select_sql语句，注意要加类似"where 字段名=@字段名"</param>
/// <param name="colid">where语句中的字段名</param>
/// <param name="paraid">链接中类似aspx?id=1 那个地的?后面的字段</param>
/// <returns>SqldataSource</returns>
public SqlDataSource dataSourceFromSql(string sqlStr, string colid, string paraid)
{
SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
QueryStringParameter pa = new QueryStringParameter(colid, paraid);
ds.SelectParameters.Add(pa);
return ds;
}
/// <summary>
/// 返回一个带 QueryString参数的SqldataSource
/// </summary>
/// <param name="sqlStr">select_sql语句，注意要加类似"where 字段名=@字段名"</param>
/// <param name="colid">where语句中的字段名</param>
/// <param name="paraid">链接中类似aspx?id=1 那个地的?后面的字段</param>
/// <param name="defaultQueryString">默认的值</param>
/// <returns>SqldataSource</returns>
public SqlDataSource dataSourceFromSql(string sqlStr, string colid, string paraid,string defaultQueryString)
{
SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
QueryStringParameter pa = new QueryStringParameter(colid, paraid);
pa.DefaultValue = defaultQueryString;
ds.SelectParameters.Add(pa);
return ds;
}
// 由ATPKING 在2008.4.24日改进，将当前页放到了参数里
/// <summary>
/// 返回一个支持分页的PageDataSource,分页大小在web.config文件中设置
/// </summary>
/// <param name="sqlStr">Select_sql语句</param>
/// <param name="pageNo">当前页，0为最前页</param>
/// <returns>PageDataSource</returns>
public PagedDataSource pagedSourceFromSql(string sqlStr,int pageNo)
{
int pageSize = int.Parse(ConfigurationManager.AppSettings["pageSize"]);
PagedDataSource objPds = new PagedDataSource();
SqlDataSource SqlDataSource1 = this.dataSourceFromSql(sqlStr);
objPds.DataSource = SqlDataSource1.Select(DataSourceSelectArguments.Empty);
objPds.AllowPaging = true;
objPds.PageSize = pageSize;
objPds.CurrentPageIndex = pageNo;
return objPds;
}
// 由ATPKING 在2008.4.26日改进，将页大小放到了参数里
/// <summary>
/// 返回一个支持分页的PageDataSource
/// </summary>
/// <param name="sqlStr">Select_sql语句</param>
/// <param name="pageNo">当前页，0为最前页</param>
/// <param name="pageSize">页面能存放的记录</param>
/// <returns>PageDataSource</returns>
public PagedDataSource pagedSourceFromSql(string sqlStr, int pageNo,int pageSize)
{
PagedDataSource objPds = new PagedDataSource();
SqlDataSource SqlDataSource1 = this.dataSourceFromSql(sqlStr);
objPds.DataSource = SqlDataSource1.Select(DataSourceSelectArguments.Empty);
objPds.AllowPaging = true;
objPds.PageSize = pageSize;
objPds.CurrentPageIndex = pageNo;
return objPds;
}
/// <summary>
/// 执行insert 语句
/// </summary>
/// <param name="_sql">insertSQL语句</param>
public void insertSql(string _sql)
{
_conn.Open();
SqlCommand comm = new SqlCommand(_sql, _conn);
comm.ExecuteNonQuery();
_conn.Close();
}
}