set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[PagingRecord]
@TableName varchar(50), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int , --指定当前为第几页
@TotalPage int OUT --返回总页数
as
-- declare @pageSize int
-- select @pageSize=40
begin
print '@PageSize='
print @PageSize
Begin Tran --开始事务 Declare @sql nvarchar(4000);
Declare @totalRecord int; --计算总记录数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere --处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage --处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
print @pageIndex
set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
print @Sql
Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end
在程序里, 怎么将上面存储过程的值绑定到datagridview, 刚入门做项目, 还不太懂存储过程的用法. 期望各位指点指点.
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[PagingRecord]
@TableName varchar(50), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int , --指定当前为第几页
@TotalPage int OUT --返回总页数
as
-- declare @pageSize int
-- select @pageSize=40
begin
print '@PageSize='
print @PageSize
Begin Tran --开始事务 Declare @sql nvarchar(4000);
Declare @totalRecord int; --计算总记录数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere --处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage --处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
print @pageIndex
set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
print @Sql
Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end
在程序里, 怎么将上面存储过程的值绑定到datagridview, 刚入门做项目, 还不太懂存储过程的用法. 期望各位指点指点.
dataGridView.datasour=dataTable;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
.....//声明参数数组.....
SqlParameter[] parms =
{ new SqlParameter("TableName",SqlDbType.VarChar,50),
new SqlParameter("Fields",SqlDbType.VarChar,5000),
new SqlParameter("OrderField",SqlDbType.VarChar,5000),
new SqlParameter("sqlWhere",SqlDbType.VarChar,5000),
new SqlParameter("pageSize",SqlDbType.Int),
new SqlParameter("pageIndex",SqlDbType.Int)
};
SqlConnection conn = new SqlConnection("//连接字符串");
SqlCommand cmd = new SqlCommand(); TranParms(cmd, conn, parms);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.Fill(ds); GridView1.DataSource = ds;...
//参数传入
protected void TranParms(SqlCommand cmd, SqlConnection conn, SqlParameter parms)
{
cmd.CommandText = "PagingRecord";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.Add(parms); }
...
{
cmd.CommandText = "PagingRecord";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
if (parms != null)
{
foreach (SqlParameter parameter in parms)
cmd.Parameters.Add(parameter);
};
}
private void dgv_databing()
{
#region
SqlParameter[] parms =
{ new SqlParameter("tb_员工",SqlDbType.VarChar,50),
new SqlParameter("*",SqlDbType.VarChar,5000),
new SqlParameter("员工编号",SqlDbType.VarChar,5000),
new SqlParameter("Null",SqlDbType.VarChar,5000),
new SqlParameter("40",SqlDbType.Int),
new SqlParameter("1",SqlDbType.Int)
};
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ToString());
SqlCommand cmd = new SqlCommand();
TranParms(cmd, conn, parms);
DataSet dgvds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.Fill(dgvds);
dgv.DataSource = dgvds; toolStripStatusLabel1.Text = " 公司员工共有[ " + dgv.RowCount.ToString() + " ]人 ";
#endregion
} 报错:在调用“Fill”前,SelectCommand 属性尚未初始化。
{
#region
SqlParameter[] parms =
{ new SqlParameter("tb_员工",SqlDbType.VarChar,50),
new SqlParameter("*",SqlDbType.VarChar,5000),
new SqlParameter("员工编号",SqlDbType.VarChar,5000),
new SqlParameter("Null",SqlDbType.VarChar,5000),
new SqlParameter("40",SqlDbType.Int),
new SqlParameter("1",SqlDbType.Int)
}; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ToString());
SqlCommand cmd = dbclass.CreateCommand("PagingRecord", parms);
DataSet dgvds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("PagingRecord", conn);
adapter.Fill(dgvds);
dgv.DataSource = dgvds; toolStripStatusLabel1.Text = " 公司员工共有[ " + dgv.RowCount.ToString() + " ]人 ";
#endregion
} public SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
#region
// 确认打开连接
Open();
cmd = new SqlCommand(procName, sqlcn);
cmd.CommandType = CommandType.StoredProcedure; // 依次把参数传入存储过程
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
} // 加入返回参数
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null)); return cmd;
#endregion
} 错误提示: 过程或函数 'PagingRecord' 需要参数 '@TableName',但未提供该参数。
{
cmd.CommandText = "PagingRecord";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
if (parms != null)
{
foreach (SqlParameter parameter in parms)
cmd.Parameters.Add(parameter);
};
}
private void dgv_databing()
{
#region
SqlParameter[] parms =
{ new SqlParameter("tb_员工",SqlDbType.VarChar,50),
new SqlParameter("*",SqlDbType.VarChar,5000),
new SqlParameter("员工编号",SqlDbType.VarChar,5000),
new SqlParameter("Null",SqlDbType.VarChar,5000),
new SqlParameter("40",SqlDbType.Int),
new SqlParameter("1",SqlDbType.Int)
};
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ToString());
SqlCommand cmd = new SqlCommand();
TranParms(ref cmd, conn, parms);
DataSet dgvds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dgvds);
dgv.DataSource = dgvds; toolStripStatusLabel1.Text = " 公司员工共有[ " + dgv.RowCount.ToString() + " ]人 ";
#endregion
} 还是报错过程或函数 'PagingRecord' 需要参数 '@TableName',但未提供该参数。
new SqlParameter("tb_员工",SqlDbType.VarChar,50),
请使用nvarchar类型的,另外你可以在sp中加print来调试。
我本来以为你提问分页的,说了一下在这里:
http://www.cnprog.com/questions/23/C#FROM datagridview分页问题