C# DBHelper 第二版, 持续更新 直到完美。 欢迎大家提出意见。
作者:ChaoVer
/******************************** * Produce: DbHelper * Version: beta 2 * Date: 2012.10.11 ********************************/ using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections.Generic; namespace DbHelper { public class SqlDbHelper :IDisposable { protected SqlConnection conn; protected SqlCommand cmd; protected SqlDataReader reader; protected SqlDataAdapter adapter; protected string connectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; public static SqlDbHelper Instance = null; public string ConnectionString { get { return this.connectionString; } set { this.connectionString = value; } } static SqlDbHelper() { SqlDbHelper.Instance = new SqlDbHelper(); } /// <summary> /// 获取一个未打开连接的SqlConnection对象 /// </summary> /// <returns>SqlConnection对象</returns> public SqlConnection GetConnection() { if (conn != null) return this.conn; return this.conn = new SqlConnection(connectionString); } /// <summary> /// 使用连接字符串获取未打开连接SqlConnection对象 /// </summary> /// <param name="_connStr">连接字符串</param> /// <returns>SqlConnection对象</returns> public SqlConnection GetConnection(string _connStr) { if (this.conn != null) this.conn.ConnectionString = _connStr; else this.conn = new SqlConnection(_connStr); return this.conn; } /// <summary> /// 使用指定的Sql语句创建SqlCommand对象 /// </summary> /// <param name="sqlStr">Sql语句</param> /// <returns>SqlCommand对象</returns> private SqlCommand GetCommand(string sqlStr) { if (this.conn == null) this.conn = GetConnection(); if (this.cmd == null) this.cmd = this.GetCommand(sqlStr, CommandType.Text, null); else { this.cmd.CommandText = sqlStr; this.cmd.CommandType = CommandType.Text; this.cmd.Parameters.Clear(); } return this.cmd; } /// <summary> /// 使用指定的Sql语句,CommandType,SqlParameter数组创建SqlCommand对象 /// </summary> /// <param name="sqlStr">Sql语句</param> /// <param name="type">命令类型</param> /// <param name="paras">SqlParameter数组</param> /// <returns>SqlCommand对象</returns> public SqlCommand GetCommand(string sqlStr, CommandType type, SqlParameter[] paras) { if (conn == null) this.conn = this.GetConnection(); if (cmd == null) this.cmd = conn.CreateCommand(); this.cmd.CommandType = type; this.cmd.CommandText = sqlStr; this.cmd.Parameters.Clear(); if (paras != null) this.cmd.Parameters.AddRange(paras); return this.cmd; } /// <summary> /// 执行Sql语句返回受影响的行数 /// </summary> /// <param name="sqlStr">Sql语句</param> /// <returns>受影响的行数,失败则返回-1</returns> public int ExecuteNonQuery(string sqlStr) { int line = -1; try { line = this.ExecuteNonQuery(sqlStr,CommandType.Text,null); } catch (SqlException e) { throw e; } return line; } /// <summary> /// 使用指定的Sql语句,CommandType,SqlParameter数组执行Sql语句,返回受影响的行数 /// </summary> /// <param name="sqlStr">Sql语句</param> /// <param name="type">命令类型</param> /// <param name="paras">SqlParameter数组</param> /// <returns>受影响的行数</returns> public int ExecuteNonQuery(string sqlStr, CommandType type, SqlParameter[] paras) { int line = -1; CheckArgs(sqlStr); if (this.cmd == null) GetCommand(sqlStr, type, paras); this.cmd.Parameters.Clear(); this.cmd.CommandText = sqlStr; this.cmd.CommandType = type; if(paras != null) this.cmd.Parameters.AddRange(paras); try { OpenConn(); line = this.cmd.ExecuteNonQuery(); } catch (SqlException e) { throw e; } return line; } /// <summary> /// 使用指定Sql语句获取dataTable /// </summary> /// <param name="sqlStr">Sql语句</param> /// <returns>DataTable对象</returns> public DataTable GetDataTable(string sqlStr) { CheckArgs(sqlStr); if (this.conn == null) this.conn = GetConnection(); this.adapter = new SqlDataAdapter(sqlStr, this.conn); DataTable table = new DataTable(); try { adapter.Fill(table); } catch (SqlException e) { throw e; } finally { this.adapter.Dispose(); } return table; } /// <summary> /// 使用指定的Sql语句获取SqlDataReader /// </summary> /// <param name="sqlStr">sql语句</param> /// <returns>SqlDataReader对象</returns> public SqlDataReader GetSqlDataReader(string sqlStr) { CheckArgs(sqlStr); if (cmd == null) GetCommand(sqlStr); if(reader != null) reader.Dispose(); try { OpenConn(); this.reader = this.cmd.ExecuteReader(); } catch (SqlException e) { throw e; } return this.reader; } /// <summary> /// 使用事务执行多条Sql语句 /// </summary> /// <param name="sqlCommands">sql语句数组</param> /// <returns>全部成功则返回true否则返回false</returns> public bool ExecuteSqls(List<string> sqlCommands) { if (sqlCommands == null) throw new ArgumentNullException(); if (sqlCommands.Count == 0) throw new ArgumentOutOfRangeException(); if(this.cmd == null) GetCommand(null); SqlTransaction tran = null; try { OpenConn(); tran = this.conn.BeginTransaction(); this.cmd.Transaction = tran; foreach (string sql in sqlCommands) { if (ExecuteNonQuery(sql) == 0) { tran.Rollback(); return false; } } } catch { if (tran != null) tran.Rollback(); throw; } tran.Commit(); return true; } public virtual void Dispose() { if (this.reader != null) { reader.Dispose(); this.reader = null; } if (this.cmd != null) { this.cmd.Dispose(); this.cmd = null; } if (this.conn != null) { this.conn.Dispose(); conn = null; } } protected void OpenConn() { try { if (this.conn.State != ConnectionState.Open) conn.Open(); } catch (SqlException e) { throw e; } } /// <summary> /// 关闭连接 /// </summary> public void CloseConn() { if (this.conn != null && this.conn.State == ConnectionState.Open) this.conn.Close(); } /// <summary> /// 检查Sql语句是否合法 /// </summary> /// <param name="sqlStr">Sql语句</param> protected virtual void CheckArgs(string sqlStr) { if (sqlStr == null) throw new ArgumentNullException(); if (sqlStr.Length == 0) throw new ArgumentOutOfRangeException(); } } }