110,534
社区成员
发帖
与我相关
我的任务
分享
using (SqlConnection sqlcon = new SqlConnection("server=.;database=Platform;user id=reportuser;password=reportuser"))
{
using (SqlCommand sqlcom = new SqlCommand())
{
sqlcom.Connection = sqlcon;
sqlcom.CommandType = CommandType.StoredProcedure;
sqlcom.CommandText = "getData";
sqlcom.Parameters.AddWithValue("@PageSize", SqlDbType.Int).Value = int.Parse(this.txtPageSize.Text);
sqlcom.Parameters.AddWithValue("@PageIndex", SqlDbType.Int).Value = _CurPage;
sqlcom.Parameters.AddWithValue("@TableName", SqlDbType.VarChar).Value = _spName;
sqlcom.Parameters.AddWithValue("@SelField", SqlDbType.VarChar).Value = _SelField;
sqlcom.Parameters.AddWithValue("@ParaField", SqlDbType.VarChar).Value = _ParaStr;
sqlcom.Parameters.AddWithValue("@SortField", SqlDbType.VarChar).Value = _ReturnField;
sqlcom.Parameters.Add("@TotalCount", SqlDbType.VarChar, 255).Direction = ParameterDirection.Output;
sqlcon.Open();
sqlcom.ExecuteReader();
_TotalRecord = int.Parse(sqlcom.Parameters["@TotalCount"].Value.ToString()); //未将对象引用设置到对象的实例。
if (ds.Tables["Data"] != null)
ds.Tables.Remove("Data");
using (SqlDataAdapter sqladp = new SqlDataAdapter(sqlcom))
{
sqladp.Fill(ds, "Data");
this.dataGridView1.DataSource = ds.Tables["Data"];
if (this.chkPage.Checked)
{
_TotalPage = _TotalRecord / int.Parse(this.txtPageSize.Text);
if (_TotalRecord % int.Parse(this.txtPageSize.Text) > 0)
_TotalPage++;
}
}
}
}
sqlcon.Open();
SqlDataReader reader = sqlcom.ExecuteReader();
reader.Close();
_TotalRecord = int.Parse(sqlcom.Parameters["@TotalCount"].Value.ToString());
if object_id('getData','p') is not null
drop proc getData
go
create proc getData
(
@PageSize int,
@PageIndex int,
@TableName varchar(20),
@SelField varchar(2000),
@ParaField varchar(2000),
@SortField varchar(200),
@TotalCount int output
)
as
declare @SQLStr nvarchar(4000)
--set @SQLStr='select top '+convert(varchar(20),@PageSize)+' Row_number() over(order by getdate()) as F0,'+@SelField+' from '+@TableName+' where 1=1 and '+@ParaField+'
--and '+@KeyField+' in (select top '+convert(varchar(20),@PageSize*@PageIndex)+' '+@KeyField+' from '+@TableName+' where 1=1 and '+@ParaField+' order by '+@SortField+' desc)
--order by '+@SortField
set @SQLStr='with tt as (
select top '+convert(varchar(20),@PageSize*@PageIndex)+' Row_number() over(order by '+@SortField+') as F0,'+@SelField
+' from '+@TableName+' where 1=1 and '+@ParaField+' order by '+@SortField
+') select * from tt where F0 between '+convert(varchar(20),@PageSize*(@PageIndex-1)+1)+' and '+convert(varchar(20),@PageSize*@PageIndex)+' order by '+@SortField
EXEC sp_executesql @SQLStr
--declare @TotalCount int
set @SQLStr='select @iRet=count(1) from '+@TableName+' where 1=1 and '+@ParaField
EXEC sp_executesql @SQLStr,N'@iRet int output',@TotalCount output
return --@TotalCount
go
declare @out int
exec getData 20,1,'sp7101','F1,F3,F5,F6,F11,F10,F4,F7,F8,F9','F5=''200''','F1', @out output
print @out
sqlcon.Open();
sqlcom.ExecuteReader();
_TotalRecord = int.Parse(sqlcom.Parameters["@TotalCount"].Value.ToString()); //未将对象引用设置到对象的实例。
//改为
sqlcon.Open();
SqlDataReader reader = sqlcom.ExecuteReader();
while(reader.Read())
{
string res = int.Parse(reader["TotalCount"].ToString());
}