using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace SqlServerOperation
{
///
/// ASP.NET SQLSERVER数据库通用操作类（封装了一些常用数据）
///
public class SqlHelper
{
public SqlHelper()
{
//
// 此处: 添加构造函数
//
}
public static string ConnectionString = ConfigurationManager.ConnectionStrings["zzyjsConn"].ConnectionString;
//根据sql语句返回一个DataSet
#region GetDataSet
public DataSet GetDataSet(string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
try
{
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
#endregion
//根据sql语句返回一个SqlDataReader
#region ExecSqlDataReader
public SqlDataReader ExecSqlDataReader(string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sdr = cmd.ExecuteReader();
try
{
return sdr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
#endregion
//执行sql语句，返回影响行数
#region ExecCommand
public static int ExecCommand(string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(sql, conn);
sqlcom.Connection = conn;
conn.Open();
try
{
int rtn = cmd.ExecuteNonQuery();
return rtn;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return 0;
}
//重写ExecCommand：这个方法可以调用两种类型的参数。
//返回受影像的纪录数目,返回值：Int类型，参数为SQL语句insert into, update ,delete
public static int ExecCommand(string sql)
{
if (sql.EndsWith(",")) sql = sql.Substring(0, sql.Length - 1);
SqlCommand sqlcom = new SqlCommand(sql);
return ExecCommand(sqlcom);
}
#endregion
//根据sql语句返回查询结果的第一行
#region ExecuteScalar
public object ExecuteScalar(string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand sqlcom = new SqlCommand(sql, conn);
conn.Open();
try
{
object rtn = sqlcom.ExecuteScalar();
return rtn;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return null;
}
#endregion
//执行存储过程无返回值，SQL语句含有参数
#region ExecSPCommand
public void ExecSPCommand(string sql, System.Data.IDataParameter[] paramers)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand sqlcom = new SqlCommand(sql, conn);
sqlcom.CommandType = CommandType.StoredProcedure;
foreach (System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
try
{
sqlcom.ExecuteNonQuery();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
conn.Close();
}
}
#endregion
//执行存储过程无返回值，SQL语句不含有参数
#region ExecSPCommand2
public void ExecSPCommand2(string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand sqlcom = new SqlCommand(sql, conn);
sqlcom.CommandType = CommandType.StoredProcedure;
conn.Open();
try
{
sqlcom.ExecuteNonQuery();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
conn.Close();
}
}
#endregion
//执行存储过程，返回SqlDataReader，SQL语句含有参数
#region ExecSPSqlDataReader
public SqlDataReader ExecSPSqlDataReader(string sql, System.Data.IDataParameter[] paramers)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand sqlcom = new SqlCommand(sql, conn);
sqlcom.CommandType = CommandType.StoredProcedure;
foreach (System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
SqlDataReader sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
#endregion
//执行存储过程，返回SqlDataReader，SQL语句不含有参数
#region ExecSPSqlDataReader2
public SqlDataReader ExecSPSqlDataReader2(string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand sqlcom = new SqlCommand(sql, conn);
sqlcom.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
#endregion
//执行存储过程返回DataSet类型，SQL语句含有参数
#region ExecSPDataSet
public DataSet ExecSPDataSet(string sql, System.Data.IDataParameter[] paramers)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand sqlcom = new SqlCommand(sql, conn);
sqlcom.CommandType = CommandType.StoredProcedure;
foreach (System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlcom;
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
return ds;
}
#endregion
//执行存储过程返回DataSet类型，SQL语句不含有参数
#region ExecSPDataSet2
public DataSet ExecSPDataSet2(string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand sqlcom = new SqlCommand(sql, conn);
sqlcom.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlcom;
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
return ds;
}
#endregion
///利用存储过程分页
///参数说明：
///TableList 要取得的字段列表，全部为*
///TableName 要查询的表名
///SelectWhere 查询条件，不可为空
///SelectOrderID 排序字段
///SelectOrder 排序方式
///intPageNo 当前页码
///intPageSize 每页显示的记录数
///RecordCount 返回值，查询出来的总记录数，传递参数时无需初始化
///RowCount 返回值，当前页中的记录数，在最后一页不等于PageSize，在传递参数时无需初始化
#region DataSelect
public DataSet dataSelect(string TableList, string TableName, string SelectWhere, string SelectOrderId, string SelectOrder, int intPageNo, int intPageSize, out int RecordCount, out int RowCount)
{
SqlParameter pTableList = new SqlParameter("@TableList", TableList);
SqlParameter pTableName = new SqlParameter("@TableName", TableName);
SqlParameter pSelectWhere = new SqlParameter("@SelectWhere", SelectWhere);
SqlParameter pSelectOrderId = new SqlParameter("@SelectOrderId", SelectOrderId);
SqlParameter pSelectOrder = new SqlParameter("@SelectOrder", SelectOrder);
SqlParameter pintPageNo = new SqlParameter("@intPageNo", intPageNo);
SqlParameter pintPageSize = new SqlParameter("@intPageSize", intPageSize);
SqlParameter pRecordCount = new SqlParameter("@RecordCount", RecordCount);
SqlParameter pRowCount = new SqlParameter("RowCount", RowCount);
IDataParameter[] parameters = new IDataParameter[] { pTableList, pTableName, pSelectWhere, pSelectOrderId, pSelectOrder, pintPageNo, pintPageSize, pRecordCount, pRowCount };
//IDataParameter[] parameters = new IDataParameter[] {new SqlParameter("@TableList",TableList),new SqlParameter("@TableName",TableName)}
DataSet ds = ExecSPDataSet("GetDataSet", parameters);
RecordCount = (Int32)da.SelectCommand.Parameters["@RecordCount"].Value;
RowCount = (Int32)da.SelectCommand.Parameters["RowCount"].Value;
return ds;
}
#endregion
}
}
====================================================================
分页存储过程
CREATE PROCEDURE GetDataSet
@TableList Varchar(200)='*',--搜索表的字段,比如：’id，datatime，job‘，用逗号隔开
@TableName Varchar(30), --搜索的表名
@SelectWhere Varchar(500),--搜索条件，这里不用写where，比如：job=’teacher‘and class='2'
@SelectOrderId Varchar(20),--表主键字段名。比如：id
@SelectOrder Varchar(200)='', --排序，可以使用多字段排序但主键字段必需在最前面.也可以不写，比如：order by class asc
@intPageNo int=1, --页号
@intPageSize int=10 ,--每页显示数
@RecordCount int OUTPUT --总记录数(存储过程输出参数)
as
declare @TmpSelect NVarchar(600)
declare @Tmp NVarchar(600)
set nocount on--关闭计数
set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' '+@SelectWhere
execute sp_executesql
@TmpSelect, --执行上面的sql语句
N'@RecordCount int OUTPUT' , --执行输出数据的sql语句，output出总记录数
@RecordCount OUTPUT
--if (@RecordCount = 0) --如果没有贴子，则返回零
--return 0
if (@intPageNo - 1) * @intPageSize > @RecordCount --页号大于总页数，返回错误
return (-1)
set nocount off--打开计数
if @SelectWhere != ''
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' '+@SelectWhere+' and '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectWhere +' '+@SelectOrder+') '+@SelectOrder
end
else
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrder
end
execute sp_executesql @TmpSelect
return(@@rowcount)