一下代码 在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();
}