基于存储过程和sql语句操作数据库类封装

1、using System;using System.Linq;using System.Text;using System.Data;namespace ADODemo? / ? / 操作数据库方法? / ummary?public? class SqlHelper? ? / ? / 返回连接字符串? / ? public string MyConnString? ? get ? return Data Source=Tiger-PCTIGERME;Initial Catalog=ADODB; User ID=sa;Pwd=123456;? ? ? / ? / 基于SQL命令或基于存储过程 实

2、现数据库操作? / ? / insert,update,delete相关命令? / 受影响的行数 int ? public int ExcuteSQLReturnInt(string sql,SqlParameter pars,CommandType type? ? ? /创建连接对象? SqlConnection conn = new SqlConnection(MyConnString;? try? ? /实例化命令对象? SqlCommand cmd = new SqlCommand(sql, conn;? /打开连接对象? if (conn.State = ConnectionStat

3、e.Closed | conn.State = ConnectionState.Broken? ? conn.Open(;? ? #region 给命令对象参数集合添加参数? if (pars != null & pars.Length 0? ? foreach (SqlParameter p in pars? ? ? ? ? #endregion? cmd.CommandType = type;? int count = cmd.ExecuteNonQuery(;? return count;? ? catch (Exception ex? ? return 0;? ? finally? ?

4、 conn.Close(;? ? ? ? ? / ? / 基于SQL实现查询，返回数据集合? / ? / select * from ? / sdataset ? public DataSet SelectSqlReturnDataSet(string sql? ? SqlConnection conn = new SqlConnection(MyConnString;? SqlDataAdapter sda = new SqlDataAdapter(sql, conn;? DataSet ds = new DataSet(;? sda.Fill(ds;? return ds;? ? ? /

5、? / 基于SQL命令或基于存储过程 实现数据库操作? / ? / sql=select * from tbUser where userid=userid and departmentID=departmentid? / m name=pars ? / ? public DataSet SelectSqlReturnDataSet(string sql,SqlParameter pars,CommandType type? ? SqlConnection conn = new SqlConnection(MyConnString;? ? SqlDataAdapter sda = new Sq

6、lDataAdapter(sql, conn;? if (pars != null & pars.Length 0? ? foreach (SqlParameter p in pars? ? ? ? DataSet ds = new DataSet(;? sda.Fill(ds;? return ds;? ? / ? /执行一个 SQL 命令或存储过程 返回一个数据表对象? / ? / SQL 命令或存储过程? / 参数集合? / 命令类型? / DataTable ? public DataTable SelectSqlReturnDataTable(string sql, SqlParam

7、eter pars, CommandType type? ? SqlConnection conn = new SqlConnection(MyConnString;? SqlDataAdapter sda = new SqlDataAdapter(sql, conn;? if (pars != null & pars.Length 0? ? foreach (SqlParameter p in pars? ? ? ? if (type != null? ? ? DataSet ds = new DataSet(;? sda.Fill(ds,myTable;? return ds.Tables

8、myTable;? ? / ? / 基于SQL实现查询，返回数据阅读器对象? / ? / select * from tbUser where userid=123? / SqlDataReader ? public SqlDataReader SelectSqlReturnDataReader(string sql? ? SqlConnection conn = new SqlConnection(MyConnString;? SqlCommand cmd = new SqlCommand(sql, conn;? if (conn.State = ConnectionState.Closed

9、? ? conn.Open(;? ? SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection;? ? return reader;? ? ? ? / ? /? 基于SQL命令或基于存储过程 实现数据库查询操作? / ? / select * from tbUser where userid=userid? / SqlDataReader ? public SqlDataReader SelectSqlReturnDataReader(string sql,CommandType type,SqlParameterpars? ? SqlConnection conn = new SqlConnection(MyConnString;? SqlCommand cmd = new SqlCommand(sql, conn;? if (pars != null & pars.Length 0? ? foreac