存储过程:
--建数据库
create database test
--建表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[laolao]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[laolao]
GO
CREATE TABLE [dbo].[laolao] (
[MyName] [int] NOT NULL ,
[Mycompany] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--在表里面插入1000记录
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create proc ps_InsertRow as declare @i INT set @i=1000 while @i>0 begin insert into laolao values(cast(@i as varchar(10)),cast(@i as varchar(10))+'whn') set @i=@i-1 end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
--一个带output参数的存储过程
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER proc DepartPage @PageIndex int, @PageSize int, @SumCount int output as begin declare @RecordCount int set @RecordCount=@PageIndex * @PageSize set @SumCount=(select Count(*) from laolao) SET ROWCOUNT @RecordCount select * into #TempTable from laolao order by MyName asc SET ROWCOUNT @PageSize select * from #TempTable order by MyName desc SET ROWCOUNT 0 drop table #TempTable end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOc#代码(在App_Code中添加)
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.SqlClient;
///
/// Class1 的摘要说明
///
public class db
{
public db()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static DataTable GetList(int PageIndex, int PageSize, out int SumCount)
{
SumCount = 0;
//string sqlstr = ConfigurationManager.ConnectionStrings["sqlstr"].ConnectionString;
SqlConnection con = new SqlConnection("server=.;database=test;uid=sa;pwd=sa");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "DepartPage";
SqlParameter[] par ={ new SqlParameter("@PageIndex", DbType.Int32), new SqlParameter("@PageSize", DbType.Int32), new SqlParameter("@SumCount", DbType.Int32) };
par[0].Value = PageIndex;
par[1].Value = PageSize;
par[2].Value = SumCount;
par[2].Direction = ParameterDirection.Output;
for (int i = 0; i < par.Length; i++)
{
cmd.Parameters.Add(par[i]);
}
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable ds = new DataTable();
dataAdapter.Fill(ds);
SumCount = Convert.ToInt32( cmd.Parameters["@SumCount"].Value);
return ds;
}
}
//页面调用
int SumCount=0;
db.GetList(1, 10, out SumCount);