public DataSet GetList(int PageSize,int PageIndex,string strWhere)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "Cases";
parameters[1].Value = "ID";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere; return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
}
DbHelperSQL方法:
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);//这步出错
connection.Close();
return dataSet;
}
}连接字符串:
public static string connectionString = @"Data Source=PC-200203241858\SQLEXPRESS;Initial Catalog=DATA;Integrated Security=True";
// public static string connection = @"Data Source=PC-200203241858\SQLEXPRESS;Initial Catalog=DATA;Integrated Security=True"; 存储过程:
CREATE PROCEDURE [dbo].[UPGetRecordByPage]
@tblName varchar(255),
@fldName varchar(255),
@PageSize int = 10,
@PageIndex int = 1,
@IsReCount bit = 0,
@OrderType bit = 0,
@strWhere varchar(1000) = ''
ASdeclare @strSQL varchar(6000)
declare @strTmp varchar(100)
declare @strOrder varchar(400) if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
endset @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrderif @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
endif @IsReCount != 0
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhereexec (@strSQL)GOexec UPGetRecordByPage 'Product','ID',10,1,0,1,'1=1'
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "Cases";
parameters[1].Value = "ID";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere; return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
}
DbHelperSQL方法:
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);//这步出错
connection.Close();
return dataSet;
}
}连接字符串:
public static string connectionString = @"Data Source=PC-200203241858\SQLEXPRESS;Initial Catalog=DATA;Integrated Security=True";
// public static string connection = @"Data Source=PC-200203241858\SQLEXPRESS;Initial Catalog=DATA;Integrated Security=True"; 存储过程:
CREATE PROCEDURE [dbo].[UPGetRecordByPage]
@tblName varchar(255),
@fldName varchar(255),
@PageSize int = 10,
@PageIndex int = 1,
@IsReCount bit = 0,
@OrderType bit = 0,
@strWhere varchar(1000) = ''
ASdeclare @strSQL varchar(6000)
declare @strTmp varchar(100)
declare @strOrder varchar(400) if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
endset @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrderif @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
endif @IsReCount != 0
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhereexec (@strSQL)GOexec UPGetRecordByPage 'Product','ID',10,1,0,1,'1=1'
解决方案 »
- C#操作txt如何在指定位子写入一行
- vs2010与数据库SQL2005 连接伪问题!跪求答案!
- Properties["Processorid"].Value.ToString()异常
- 小弟愚昧,请问各位大虾一个问题。
- 关于vs.net 2005(.net2.0)做的项目中引用了vs.net 2003生成的类库(.net1.1)
- 求教:ADO.NET连接字符串.和localhost的区别
- ilist转换json
- ado.net问题
- 500分再问带有界面的Web自定义控件的建立
- MDI中父窗口按钮与子窗口排行顺序问题
- Castle IOC或spring.net依赖注入好像没有反射功能?
- 水晶报表问题啊~rystaldecisions.crystalreports.engine
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "Cases";
parameters[1].Value = "ID";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere;
你这么传参做什么?
直接这么写就可以了new SqlParameter("@tblName", "Cases"),
你可以这样改下试试
这一句后面的逗号去掉试试