一个分页排序存储过程

一下代码 在SQL2005+VS2008下面通过

存储过程

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[customer_Search]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[customer_Search]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[customer_Search]
( 
   @CompanyName nvarchar(40), 
   @ContactName nvarchar(30),
   @PageSize int,
   @PageNumber int,
   @SortField varchar(100),
   @SortAscending varchar(10),
   @TotalCount int out
) 
AS 
BEGIN 
SELECT @TotalCount=COUNT(1)
   FROM dbo.Customers
  WHERE  (CompanyName LIKE '%'+ ISNULL(@CompanyName,'')+'%')
 AND (ContactName LIKE '%'+ ISNULL(@ContactName,'')+'%')

DECLARE @SqlStr varchar(MAX)

SET @SqlStr =
   'SELECT CustomerID,CompanyName,ContactName,ContactTitle,Address,
    ROW_NUMBER() OVER(ORDER BY '+ISNULL(@SortField,'CustomerID')+' '+@SortAscending+') AS RowNo
      INTO #TEMP
   FROM Customers
     WHERE  (CompanyName LIKE ''%'+ ISNULL(@CompanyName,'')+'%'')
    AND (ContactName LIKE ''%'+ ISNULL(@ContactName,'')+'%'')

    SELECT  CustomerID,CompanyName,ContactName,ContactTitle,Address
      FROM #TEMP WHERE RowNo > '+CAST((@PageNumber-1)*@PageSize AS VARCHAR(100))+' AND RowNo <= '+CAST (@PageNumber*@PageSize AS VARCHAR(100))+'
    DROP TABLE #TEMP'

EXEC(@SqlStr)
END

 

C#代码

  //  <add name="DefaultConnectionString"
      //connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True"
      //providerName="System.Data.SqlClient" />
        int pageNumber = 1;
        int totalpage = 1;
        int pageSize = 10;
        private void Form1_Load(object sender, EventArgs e)
        {

            labelPageOf.Text = string.Empty;
            BindData();
        }
        private void BindData()
        {
            int resultsCount;
            DataSet ds = SearchCustomers(string.Empty, string.Empty, pageSize, pageNumber, string.Empty, true, out resultsCount);
            this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
            totalpage = resultsCount / pageSize;
            if (totalpage < 1) totalpage = 1;
            labelPageOf.Text = pageNumber.ToString() + "/" + totalpage.ToString();
        }
        private void GetData()
        {
            string ConnectionName = "DefaultConnectionString";
            ConnectionStringSettings config = ConfigurationManager.ConnectionStrings[ConnectionName];
            DbProviderFactory factory = DbProviderFactories.GetFactory(config.ProviderName);
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = config.ConnectionString;
            DbCommand cmd = factory.CreateCommand();
            cmd.Connection = connection;
            cmd.CommandText = "ckbx_ResponseTemplate_Get";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(CreateParameter("@ResponseTemplateID", DbType.Int32, null));
            cmd.Parameters.Add(CreateParameter("@AsscSurvID", DbType.Guid, null));
            cmd.Parameters.Add(CreateParameter("@EncryptKeyValue", DbType.String, ConfigurationManager.AppSettings["EncryptKey"].ToString()));
            cmd.Parameters.Add(CreateParameter("@EncryptSaltValue", DbType.String, ConfigurationManager.AppSettings["EncryptSalt"].ToString()));
            DbParameter parameter = cmd.CreateParameter();

            DbDataAdapter ap = factory.CreateDataAdapter();
            ap.SelectCommand = cmd;
            DataSet ds = new DataSet();
            ap.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];

        }
        public DataSet SearchCustomers(string companyName, string contactName, int pageSize, int pageNumber,
            string sortField, bool sortAscending, out int resultsCount)
        {
            resultsCount = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "customer_Search";
            cmd.CommandType = CommandType.StoredProcedure;
            string Sort = "ASC";
            if (!sortAscending)
                Sort = "DESC";

            if (string.IsNullOrEmpty(sortField))
                sortField = null;

            cmd.Parameters.Add(CreateParameter("@CompanyName", DbType.String, companyName));
            cmd.Parameters.Add(CreateParameter("@ContactName", DbType.String, contactName));
            cmd.Parameters.Add(CreateParameter("@PageSize", DbType.Int32, pageSize));
            cmd.Parameters.Add(CreateParameter("@PageNumber", DbType.Int32, pageNumber));
            cmd.Parameters.Add(CreateParameter("@SortField", DbType.String, sortField));
            cmd.Parameters.Add(CreateParameter("@SortAscending", DbType.String, Sort));
            cmd.Parameters.Add(CreateParameter("@TotalCount", DbType.Int32, resultsCount, ParameterDirection.Output));

            DataSet ds = ExecProcedure(cmd, pageSize, pageNumber, sortField, sortAscending);
            resultsCount = Convert.ToInt32(cmd.Parameters[cmd.Parameters.Count - 1].Value);
            return ds;
        }
        public DataSet ExecProcedure(DbCommand cmd, int pageSize, int pageNumber,
            string sortField, bool sortAscending)
        {
            string ConnectionName = "DefaultConnectionString";
            ConnectionStringSettings config = ConfigurationManager.ConnectionStrings[ConnectionName];
            DbProviderFactory factory = DbProviderFactories.GetFactory(config.ProviderName);
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = config.ConnectionString;
            // DbCommand cmd = factory.CreateCommand();
            cmd.Connection = connection;

            DbDataAdapter ap = factory.CreateDataAdapter();
            ap.SelectCommand = cmd;
            DataSet ds = new DataSet();
            ap.Fill(ds);

            return ds;
        }
        private DbParameter CreateParameter(string name, DbType Dbtype, object value)
        {
            return CreateParameter(name, Dbtype, value, ParameterDirection.Input);
        }
        private DbParameter CreateParameter(string name, DbType Dbtype, object value, ParameterDirection direction)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.DbType = Dbtype;
            parameter.ParameterName = name;
            parameter.Direction = direction;
            parameter.IsNullable = true;
            parameter.Value = (value == null) ? DBNull.Value : value;
            return parameter;
        }

        private void butNext_Click(object sender, EventArgs e)
        {
            if (pageNumber < totalpage)
                pageNumber += 1;
            else return;
            BindData();
        }

        private void butPrevious_Click(object sender, EventArgs e)
        {
            if (pageNumber < totalpage)
                pageNumber -= 1;
            else return;
            BindData();
        }

 

在http://dz45693.download.csdn.net/可以下载源代码