数据库分页（存储过程返回DataTable 方法）

来源：互联网发布：远程软件teamviewer 编辑：程序博客网时间：2024/06/03 23:47

/// <summary>
        /// 根据数据库存储过程分页，返回Datatable
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="wherestring"></param>
        /// <param name="pagenum"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable spCommonPagedQuery(string tableName, string wherestring, int pagenum,int pagesize)
        {
            string strconnectstring = ConfigurationManager.AppSettings["ConnectionString"].ToString();
            string connectstring = Utility.EncryptionHelper.DESDecryption(strconnectstring);
            using (SqlConnection conn = new SqlConnection(connectstring))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "spCommonPagedQuery";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter();
                p1.ParameterName = "@tablename";
                p1.Value = tableName;

                SqlParameter p2 = new SqlParameter();
                p2.ParameterName = "@wherestring";
                p2.Value = wherestring;

                SqlParameter p3 = new SqlParameter();
                p3.ParameterName = "@pagenum";
                p3.Value = pagenum;

                SqlParameter p4 = new SqlParameter();
                p4.ParameterName = "@pagesize";
                p4.Value = pagesize;

                cmd.Parameters.Add(p1);
                cmd.Parameters.Add(p2);
                cmd.Parameters.Add(p3);
                cmd.Parameters.Add(p4);

                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds);

                    if (ds != null && ds.Tables.Count > 0)
                    {
                        return ds.Tables[0];
                    }

                    conn.Close();
                }
                catch (Exception ex)
                {

}
}

return null;
}