private string connectionString = "server=(local);database=database;uid=****;pwd=*** "; //数据库连接字符串
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindDataList();
}
}
protected void BindDataList()
{
string strSql = "SELECT * FROM t1";
string fieldOrder = "id ASC";
int records = 0;
Repeater1.DataSource = GetDataList(strSql, fieldOrder, Pager1.PageSize, Pager1.PageIndex, out records); //读取数据源并绑定
Repeater1.DataBind();
//设置页面(必须)
Pager1.SetPage(records); //初始化分页条
}
/// <summary>
/// 使用存储过程取出数据
/// </summary>
/// <param name="strSql"></param>
/// <param name="fieldOrder"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="records"></param>
/// <returns>数据表datatable</returns>
public DataTable GetDataList(string strSql, string fieldOrder, int pageSize, int pageIndex, out int records)
{
DataTable dt = null;//返回的数据集
records = 0;//事先赋值
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
//打开连接
sqlConn.Open();
//初始化参数
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandText = "SP_Page";
sqlCmd.CommandType = CommandType.StoredProcedure;
#region ___存储过程参数___
SqlParameter recordsParam = new SqlParameter("@TotalCount", SqlDbType.Int, 32);
recordsParam.Direction = ParameterDirection.Output;
//创建
sqlCmd.Parameters.Add(new SqlParameter("@Sql", SqlDbType.NVarChar, 1024));
sqlCmd.Parameters.Add(new SqlParameter("@Sort", SqlDbType.NVarChar, 100));
sqlCmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 32));
sqlCmd.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int, 32));
sqlCmd.Parameters.Add(recordsParam);
//赋值
sqlCmd.Parameters[0].Value = strSql;
sqlCmd.Parameters[1].Value = fieldOrder;
sqlCmd.Parameters[2].Value = pageSize;
sqlCmd.Parameters[3].Value = pageIndex;
sqlCmd.Parameters[4].Direction = ParameterDirection.Output;
#endregion ___存储过程参数___
//取数据
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
sda.Fill(ds);
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
records = (int)recordsParam.Value; //返回记录数
//释放资源
if (sqlConn != null)
{
sqlConn.Close();
sqlConn.Dispose();
}
}
return dt;
}