C#操作MSSQL数据库通用类

public class SystemDB { public SystemDB() { // // 此处: 添加构造函数 // } public static string ConnectionString = "Data Source=数据库地址;Initial Catalog=数据库名称;Persist Security Info=True;User ID=帐号;Password=密码"; //根据sql语句返回一个DataSet #region GetDataSet public DataSet GetDataSet(string sql) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.Text, sql); SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = sqlcom; DataSet ds = new DataSet(); sda.Fill(ds); sqlcom.Parameters.Clear(); return ds; } } #endregion //根据sql语句返回一个SqlDataReader #region ExecSqlDataReader public SqlDataReader ExecSqlDataReader(string sql) { SqlCommand sqlcom = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectionString); try { PrepareCommand(sqlcom, conn, CommandType.Text, sql); SqlDataReader sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection); sqlcom.Parameters.Clear(); return sdr; } catch (Exception ex) { conn.Close(); throw ex; } } #endregion //执行sql语句，返回影响行数 #region ExecCommand public int ExecCommand(string sql) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.Text, sql); int rtn = sqlcom.ExecuteNonQuery(); sqlcom.Parameters.Clear(); return rtn; } } #endregion //执行SQL语句，无返回值 #region ExecNon public void ExecNon(string sql) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.Text, sql); sqlcom.ExecuteNonQuery(); sqlcom.Parameters.Clear(); } } #endregion //根据sql语句返回查询结果的第一行 #region ExecuteScalar public object ExecuteScalar(string sql) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.Text, sql); object rtn = sqlcom.ExecuteScalar(); sqlcom.Parameters.Clear(); return rtn; } } #endregion //执行存储过程无返回值，SQL语句含有参数 #region ExecSPCommand public void ExecSPCommand(string sql, System.Data.IDataParameter[] paramers) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql); foreach (System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } sqlcom.ExecuteNonQuery(); sqlcom.Parameters.Clear(); } } #endregion //执行存储过程无返回值，SQL语句不含有参数 #region ExecSPCommand2 public void ExecSPCommand2(string sql) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql); sqlcom.ExecuteNonQuery(); sqlcom.Parameters.Clear(); } } #endregion //执行存储过程，返回SqlDataReader，SQL语句含有参数 #region ExecSPSqlDataReader public SqlDataReader ExecSPSqlDataReader(string sql, System.Data.IDataParameter[] paramers) { SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand sqlcom = new SqlCommand(); try { PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql); foreach (System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } SqlDataReader sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection); sqlcom.Parameters.Clear(); return sdr; } catch (Exception ex) { conn.Close(); throw ex; } } #endregion //执行存储过程，返回SqlDataReader，SQL语句不含有参数 #region ExecSPSqlDataReader2 public SqlDataReader ExecSPSqlDataReader2(string sql) { SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand sqlcom = new SqlCommand(); try { PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql); SqlDataReader sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection); sqlcom.Parameters.Clear(); return sdr; } catch (Exception ex) { conn.Close(); throw ex; } } #endregion //执行存储过程返回DataSet类型，SQL语句含有参数 #region ExecSPDataSet public DataSet ExecSPDataSet(string sql, System.Data.IDataParameter[] paramers) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql); foreach (System.Data.IDataParameter paramer in paramers) { sqlcom.Parameters.Add(paramer); } SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = sqlcom; DataSet ds = new DataSet(); sda.Fill(ds); sqlcom.Parameters.Clear(); return ds; } } #endregion //执行存储过程返回DataSet类型，SQL语句不含有参数 #region ExecSPDataSet2 public DataSet ExecSPDataSet2(string sql) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql); SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = sqlcom; DataSet ds = new DataSet(); sda.Fill(ds); sqlcom.Parameters.Clear(); return ds; } } #endregion ///利用存储过程分页 #region DataSelect ///参数说明： ///TableList 要取得的字段列表，全部为* ///TableName 要查询的表名 ///SelectWhere 查询条件，不可为空 ///SelectOrderID 排序字段 ///SelectOrder 排序方式 ///intPageNo 当前页码 ///intPageSize 每页显示的记录数 ///RecordCount 返回值，查询出来的总记录数，传递参数时无需初始化 public DataSet DataSelect(string TableList, string TableName, string SelectWhere, string SelectOrderId, string SelectOrder, int intPageNo, int intPageSize, out int RecordCount) { SqlCommand sqlcom = new SqlCommand(); using (SqlConnection conn = new SqlConnection(ConnectionString)) { PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, "getdataset"); sqlcom.Parameters.Add("@TableList", SqlDbType.VarChar, 500).Value = TableList; sqlcom.Parameters.Add("@TableName", SqlDbType.VarChar, 100).Value = TableName; sqlcom.Parameters.Add("@SelectWhere", SqlDbType.VarChar, 500).Value = SelectWhere; sqlcom.Parameters.Add("@SelectOrderId", SqlDbType.VarChar, 20).Value = SelectOrderId; sqlcom.Parameters.Add("@SelectOrder", SqlDbType.VarChar, 500).Value = SelectOrder; sqlcom.Parameters.Add("@intPageNo", SqlDbType.Int).Value = intPageNo; sqlcom.Parameters.Add("@intPageSize", SqlDbType.Int).Value = intPageSize; sqlcom.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = sqlcom; DataSet ds = new DataSet(); da.Fill(ds); RecordCount = (Int32)da.SelectCommand.Parameters["@RecordCount"].Value; //求出总记录数，该值是output出来的值 return ds; } } #endregion private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, CommandType cmdType, string cmdText) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; cmd.CommandType = cmdType; } }

 

上面用代码中利用存储过程分页中使用到得存储过程“getdataset"如下：

ALTER proc [dbo].[GetDataSet]
@TableList Varchar(500)='*',--搜索表的字段,比如：’id，datatime，job‘，用逗号隔开
@TableName Varchar(100), --搜索的表名
@SelectWhere Varchar(500),--搜索条件，这里不用写where，比如：job=’teacher‘and class='2'
@SelectOrderId Varchar(20),--表主键字段名。比如：id
@SelectOrder Varchar(500)='', --排序，可以使用多字段排序但主键字段必需在最前面.也可以不写，比如：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)