用于jqGrid获取SQL Server中数据的简单分页存储过程及sp_executesql的一点使用方法
之前我第一篇有关jqGrid JSON数据的获取是动态拼接的SQL语句(因为是Access数据库),然后我自己也用SQL Server中的数据作了测试,为了方便写了一个分页的存储过程,过程中也认识了下sp_executesql的用法,在SQL查询分析器中的测试如下:
/**********************************************************
=============================================================================
* 执行带参数的SQL, sp_executesql
Syntax
sp_executesql [ @stmt = ] stmt
[
{, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]
MSDN网址:http://msdn.microsoft.com/zh-cn/library/ms188001.aspx
=============================================================================
*/
declare @id int,
@SqlString nvarchar(200); --注意:使用exec sp_executesql时,一定要使用Nvarchar即 unicode
set @id=10
set @SqlString=N'select * from area where id=@myid' --前面的N就是使其成为Unicode
exec sp_executesql @SqlString,N'@myid int',@myid=@id
--就相当于这种语法 sp_executesql 一个带参数的字符串,定义前面字符串中的参数,给参数赋值
--带输出参数
declare @newid int;
declare @SQLString nvarchar(500);
declare @areaName varchar(20);
set @newid=10;
set @SQLString=N'select @areaNameOUT=name from area where id=@id'
execute sp_executesql @SQLString,N'@areaNameOUT varchar(20) OUTPUT,@id int',@id=@newid,@areaNameOUT=@areaName OUTPUT;
select @areaName; --这里就可以获取OUTPUT参数的值了
--通常在使用exec sp_executesql时,都会将 语句 和 变量定义(因为太长) 保存在变量中,如:
declare @anotherId int;
declare @execSQLString nvarchar(500); --用于保存要执行的语句
declare @ParmDefinition nvarchar(500); --这个变量用于保存变量声明
declare @newAreaName varchar(20);
set @anotherId=10
set @execSQLString=N'select @areaNameOUT=name from area where id=@id';
set @ParmDefinition=N'@areaNameOUT varchar(20) OUTPUT,@id int';
exec sp_executesql @execSQLString,@ParmDefinition,@id=@anotherId,@areaNameOUT=@newAreaName OUTPUT; --后面是给语句中的参数赋值
select @newAreaName;
--在存储过程中的使用:
--一个简单的分页存储过程,使用SQL2005中的Row_Number()
--其实分页的原型语句就比如下面这条语句:------取按id 升序的 第11到20条数据,借助了row_number()
-- select * from (select row_number() over (order by id asc) as rows, * from area) as temp where rows between 11 and 20
create proc AspNetPager
(
@pageSize int=20, --分页大小
@curPage int=1, --当前页码
@viewName varchar(100), --表或视图名称
@fieldName varchar(400)='*', --要查询的字段
@orderField varchar(50)='id', --排序字段
@orderType varchar(5)='asc', --排序类型
@where varchar(200)='1=1', --查询条件
@recordCount int OUTPUT --总的记录数,为输出参数,用于页面获取
)
AS
BEGIN
declare @beginRow int; --开始行
declare @endRow int; --结束行
declare @tempLimit varchar(200); --用于查询是从第几条到第几条数据的拼接语句
declare @tempSQL NVARCHAR(1000); --用于临时保存要获取输出参数值的SQL语句
declare @selectSQL varchar(1000); --最后要查询的语句
set @beginRow=(@curPage-1)*@pageSize+1;
set @endRow=@curPage*@pageSize;
set @tempLimit='rows between '+CAST(@beginRow as VARCHAR) +' and '+CAST(@endRow as varchar);
set @tempSQL=N'select @countOutPut=count(1) from '+@viewName+' where '+@where;
exec sp_executesql @tempSQL,N'@countOutPut int OUTPUT',@countOutPut=@recordCount OUTPUT;
set @selectSQL='select * from(select row_number() over ( order by '+@orderField+' '+@orderType+') as rows,'+@fieldName+' from '+@viewName+' where '+@where+') as main_temp where '+@tempLimit;
exec(@selectSQL);
END
--use area --下面尝试的使用一下创建的存储过程,
--declare @outCount int;
--exec AspNetPager 10,1,'area','*','id','asc','1=1',@outCount output;
在C#方法中的使用(一个取数据的静态方法):
/// <summary>
/// 查询按指定条件的记录总数
/// </summary>
/// <param name="pagesize">每页显示多少条记录</param>
/// <param name="curpage">当前页索引</param>
/// <param name="orderfield">排序字段</param>
/// <param name="ordertype">排序类型:asc或desc</param>
/// <param name="where">查询条件</param>
/// <param name="recordTotal">总的记录数</param>
/// <returns>返回查询到的数据</returns>
public static IList<SqlCityInfo> GetCityGridData(int pagesize, int curpage, string orderfield, string ordertype, string where,ref int recordTotal)
{
IList<SqlCityInfo> cities = new List<SqlCityInfo>();
SqlParameter[] prams = new SqlParameter[] {
new SqlParameter("@pageSize",pagesize),
new SqlParameter("@curPage",curpage),
new SqlParameter("@viewName","city"),
new SqlParameter("@orderField",orderfield),
new SqlParameter("@orderType",ordertype),
new SqlParameter("@recordCount",SqlDbType.Int)
};
prams[5].Direction = ParameterDirection.Output;
SqlDataReader reader = SqlHelper.ExecuteReader("AspNetPager", CommandType.StoredProcedure, prams); // 使用AspNetPager存储过程
while (reader.Read())
{
SqlCityInfo city = new SqlCityInfo();
city.Id = Convert.ToInt32(reader["id"]);
city.Code = reader["code"].ToString();
city.Name = reader["name"].ToString();
city.Fid = reader["FID"].ToString();
cities.Add(city);
}
reader.Close();
recordTotal = Convert.ToInt32(prams[5].Value);
return cities;
}
【推荐】FFA 2024大会视频回放:Apache Flink 的过去、现在及未来
【推荐】中国电信天翼云云端翼购节,2核2G云服务器一口价38元/年
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步