关于调用存储过程的问题，急求
一个任务，存储过程写好了，数据库类的方法也写好了，然后怎么执行呀，然后执行完后得到一个DATATABLE的表，要转换为JSON。各种不懂，求大神科普。我把内容奉上。
ALTER proc [dbo].[pager]
@pageIndex int,--第几页
@pageSize int,--每页显示几行
@primaryKey varchar(1000),--主键
@tableName varchar(2000),--表名
@strWhere varchar(3000),--查询条件
@returnColumn varchar(1000),--返回列
@orderField varchar(1000),--排序字段
@orderType varchar(1000),--排序类别 desc asc
@tolPage int output,--总页数
@tolRecord int output--总条数
as
declare @sql nvarchar(4000)
if(@strwhere='')
set @sql='select @total=count(*) from '+@tableName
else
set @sql='select @total=count(*) from '+@tableName+' where '+@strWhere
exec sp_executesql @sql,N'@total int output',@tolRecord output
set @tolPage=ceiling(@tolRecord*1.0/@pageSize)
if(@strWhere='')
set @sql='select top '+convert(varchar(50),@pageSize)
+' '+@returnColumn+' from '+@tableName+' where '+@primaryKey
+' not in(select top '+convert(varchar(50),(@pageIndex-1)*@pageSize)
+' '+@primaryKey+' from '+@tableName+' order by '+@orderField+' '
+@orderType+') order by '+@orderField+' '+@orderType
else
set @sql='select top '+convert(varchar(50),@pageSize)
+' '+@returnColumn+' from '+@tableName+' where('+@strWhere+') and ('
+@primaryKey+' not in(select top '+convert(varchar(50),(@pageIndex-1)*@pageSize)
+' '+@primaryKey+' from '+@tableName+' where '+@strWhere+' order by '+@orderField+' '
+' '+@orderType+')) order by '+@orderField+' '+@orderType
print @sql
exec (@sql)
public static DataTable SelectPage(int pageIndex, int pageSize, string primaryKey, string tableName,
string strWhere, string returnColumn, string orderField, string orderType, out int tolPage,
out int tolRecord)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText = "pager";
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Connection = connection;
sqlcmd.Parameters.Add(new SqlParameter("@pageIndex", pageIndex));
sqlcmd.Parameters.Add(new SqlParameter("@pageSize", pageSize));
sqlcmd.Parameters.Add(new SqlParameter("@primaryKey", primaryKey));
sqlcmd.Parameters.Add(new SqlParameter("@tableName", tableName));
sqlcmd.Parameters.Add(new SqlParameter("@strWhere", strWhere));
sqlcmd.Parameters.Add(new SqlParameter("@returnColumn", returnColumn));
sqlcmd.Parameters.Add(new SqlParameter("@orderField", orderField));
sqlcmd.Parameters.Add(new SqlParameter("@orderType", orderType));
SqlParameter ptolPage = new SqlParameter("@tolPage", SqlDbType.Int);
ptolPage.Direction = ParameterDirection.Output;
sqlcmd.Parameters.Add(ptolPage);
SqlParameter ptolRecord = new SqlParameter("@tolRecord", SqlDbType.Int);
ptolRecord.Direction = ParameterDirection.Output;
sqlcmd.Parameters.Add(ptolRecord);
SqlDataAdapter sqladapter = new SqlDataAdapter();
sqladapter.SelectCommand = sqlcmd;
DataTable dt = new DataTable();
sqladapter.Fill(dt);
tolRecord = Convert.ToInt32(ptolRecord.Value);
tolPage = Convert.ToInt32(ptolPage.Value);
return dt;
}
}
string sql="exec [dbo].[pager] (传进来的参数)";
