首页 »DotNet » sqlserver数据库:C#下一个SQL SERVER数据库操作类 »正文
sqlserver数据库:C#下一个SQL SERVER数据库操作类
来源: 发布时间:星期五, 2009年1月9日 浏览:96次 评论:0
主要参考了M$ SqlHelper类, 在参考了SqlHelper 基础进行修改而成,对 些基本 数据库操作进行了封装. 推荐使用 using (DBHelper db = DBHelper(Config.ConnStr)) { } 代码如下: using ; using .Collections.Generic; using .Text; using .Data.SqlClient; using .Data; Liydotnet.Data.Core { public DBHelper : IDisposable { #region private private bool m_AlreadyDispose = false; private m_CommandTimeout = 30; private m_ConnStr; private SqlConnection m_Connection; private SqlCommand m_Command; #endregion #region 属性 /// <summary> /// 数据库连接串 /// </summary> public ConnStr { { m_ConnStr = value; } get { m_ConnStr; } } /// <summary> /// 执行时间 /// </summary> public CommandTimeout { { m_CommandTimeout = value; } get { m_CommandTimeout; } } #endregion #region DBHelper /// <summary> /// 构造 /// </summary> /// <param name="connStr">数据库连接串</param> public DBHelper( connStr) { m_ConnStr = connStr; Initialization; } /// <summary> /// 构造 /// </summary> /// <param name="connStr">数据库连接串</param> /// <param name="commandTimeout">执行时间</param> public DBHelper( connStr, commandTimeout) { m_ConnStr = connStr; m_CommandTimeout = commandTimeout; Initialization; } /// <summary> /// 化 /// </summary> protected void Initialization { try { m_Connection = SqlConnection(m_ConnStr); (m_Connection.State ConnectionState.Closed) m_Connection.Open; m_Command = SqlCommand; } catch (Exception ex) { throw Exception(ex.Message.); } } #endregion #region Dispose /// <summary> /// 析构 /// </summary> ~DBHelper { Dispose; } /// <summary> /// 释放资源 /// </summary> /// <param name="isDisposing">标志</param> protected virtual void Dispose(bool isDisposing) { (m_AlreadyDispose) ; (isDisposing) { (m_Command != null) { m_Command.Cancel; m_Command.Dispose; } (m_Connection != null) { try { (m_Connection.State != ConnectionState.Closed) m_Connection.Close; m_Connection.Dispose; } catch (Exception ex) { throw Exception(ex.); } finally { m_Connection = null; } } } m_AlreadyDispose = true;//已经进行处理 } /// <summary> /// 释放资源 /// </summary> public void Dispose { Dispose(true); GC.SuppressFinalize(this); } #endregion #region #endregion #region ExecuteNonQuery public ExecuteNonQuery( cmdText) { try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; iRet = m_Command.ExecuteNonQuery; iRet; } catch (Exception ex) { //Loger.Debug(ex.,@"C:sql.txt"); throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; } } public ExecuteNonQuery( cmdText, SqlParameter para) { (para null) { ExecuteNonQuery(cmdText); } try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); iRet = m_Command.ExecuteNonQuery; iRet; } catch (Exception ex) { //Loger.Debug(ex., @"C:sql.txt"); throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; m_Command.Parameters.Clear; } } public ExecuteNonQuery( cmdText, SqlParameter para, bool isStoreProdure) { (!isStoreProdure) { ExecuteNonQuery(cmdText, para); } try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; m_Command.CommandType = CommandType.StoredProcedure; (para != null) { for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); } iRet = m_Command.ExecuteNonQuery; iRet; } catch (Exception ex) { //Loger.Debug(ex., @"C:sql.txt"); throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; m_Command.Parameters.Clear; } } #endregion #region ExecuteTransaction public bool ExecuteTransaction( cmdText) { SqlTransaction trans = m_Connection.BeginTransaction; try { m_Command = SqlCommand; m_Command.Connection = m_Connection; m_Command.CommandTimeout = m_CommandTimeout; m_Command.Transaction = trans; for ( i = 0; i < cmdText.Length; i) { (cmdText[i] != null && cmdText[i] != .Empty) { m_Command.CommandText = cmdText[i]; m_Command.ExecuteNonQuery; } } trans.Commit; true; } catch (Exception ex) { trans.Rollback; throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; trans.Dispose; } } public bool ExecuteTransaction( cmdText, SqlParameter para) { (para null) ExecuteTransaction(cmdText); SqlTransaction trans = m_Connection.BeginTransaction; try { m_Command = SqlCommand; m_Command.Connection = m_Connection; m_Command.CommandTimeout = m_CommandTimeout; m_Command.Transaction = trans; for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); for ( i = 0; i < cmdText.Length; i) { (cmdText[i] != null && cmdText[i] != .Empty) { m_Command.CommandText = cmdText[i]; m_Command.ExecuteNonQuery; } } trans.Commit; true; } catch (Exception ex) { trans.Rollback; throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; trans.Dispose; } } #endregion #region ExecuteScalar public object ExecuteScalar( cmdText) { try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; object obj = m_Command.ExecuteScalar; (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { obj = null; } obj; } catch (Exception ex) { throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; } } public object ExecuteScalar( cmdText, SqlParameter para) { (para null) ExecuteScalar(cmdText); try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); object obj = m_Command.ExecuteScalar; (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) obj = null; obj; } catch (Exception ex) { throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; m_Command.Parameters.Clear; } } public object ExecuteScalar( cmdText, SqlParameter para, bool isStoreProdure) { (!isStoreProdure) ExecuteScalar(cmdText, para); try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; m_Command.CommandType = CommandType.StoredProcedure; (para != null) for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); object obj = m_Command.ExecuteScalar; (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) obj = null; obj; } catch (Exception ex) { throw Exception(ex.); } finally { (m_Command != null) { m_Command.Dispose; } m_Command.Parameters.Clear; } } #endregion #region ExecuteDataTable public DataTable ExecuteDataTable( tableName, cmdText) { try { DataTable myTable = DataTable(tableName); m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; SqlDataAdapter da = SqlDataAdapter(m_Command); da.Fill(myTable); da.Dispose; myTable; } catch (Exception ex) { throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; } } public DataTable ExecuteDataTable( tableName, cmdText, SqlParameter para) { (para null) ExecuteDataTable(tableName, cmdText); try { DataTable myTable = DataTable(tableName); m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); SqlDataAdapter da = SqlDataAdapter(m_Command); da.Fill(myTable); da.Dispose; myTable; } catch (Exception ex) { //Loger.Debug(ex., @"C:sql.txt"); throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; m_Command.Parameters.Clear; } } public DataTable ExecuteDataTable( tableName, cmdText, SqlParameter para, bool isStoreProdure) { (!isStoreProdure) { ExecuteDataTable(tableName, cmdText, para); } try { DataTable myTable = DataTable(tableName); m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; m_Command.CommandType = CommandType.StoredProcedure; (para != null) for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); SqlDataAdapter da = SqlDataAdapter(m_Command); da.Fill(myTable); da.Dispose; myTable; } catch (Exception ex) { throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; m_Command.Parameters.Clear; } } #endregion #region ExecuteDataReader public SqlDataReader ExecuteDataReader( cmdText) { try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; SqlDataReader reader = m_Command.ExecuteReader; reader; } catch (Exception ex) { throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; } } public SqlDataReader ExecuteDataReader( cmdText, SqlParameter para) { (para null) { ExecuteDataReader(cmdText); } try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); SqlDataReader reader = m_Command.ExecuteReader; reader; } catch (Exception ex) { throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; m_Command.Parameters.Clear; } } public SqlDataReader ExecuteDataReader( cmdText, SqlParameter para, bool isStoreProdure) { (!isStoreProdure) { ExecuteDataReader(cmdText, para); } try { m_Command = SqlCommand(cmdText, m_Connection); m_Command.CommandTimeout = m_CommandTimeout; m_Command.CommandType = CommandType.StoredProcedure; (para != null) for ( i = 0; i < para.Length; i) m_Command.Parameters.Add(para[i]); SqlDataReader reader = m_Command.ExecuteReader; reader; } catch (Exception ex) { throw Exception(ex.); } finally { (m_Command != null) m_Command.Dispose; m_Command.Parameters.Clear; } } #endregion #region Static public SqlParameter MakeInParam( paraName, SqlDbType paraType, object value) { SqlParameter para = SqlParameter(paraName, paraType); (Object.Equals(value, null) || Object.Equals(value, DBNull.Value) || value..Trim .Empty) para.Value = DBNull.Value; para.Value = value; para; } public SqlParameter MakeInParam( paraName, SqlDbType paraType, len, object value) { SqlParameter para = SqlParameter(paraName, paraType, len); (Object.Equals(value, null) || Object.Equals(value, DBNull.Value) || value..Trim .Empty) para.Value = DBNull.Value; para.Value = value; para; } #endregion } }
相关文章
读者评论
发表评论
|
|