首页 »DotNet » sqlserver数据库:C#下一个SQL SERVER数据库操作类 »正文
sqlserver数据库:C#下一个SQL SERVER数据库操作类
来源: 发布时间:星期五, 2009年1月9日 浏览:96次 评论:0
主要参考了M$ ![](/icons/96069de.gif) SqlHelper类, 在参考了SqlHelper ![](/icons/96069de.gif) 基础进行修改而成,对 ![](/icons/96069yi.gif) 些基本 ![](/icons/96069de.gif) 数据库操作进行了封装. 推荐使用 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> /// 构造![](/icons/96069hanshu.gif) /// </summary> /// <param name="connStr">数据库连接 串</param> public DBHelper( connStr) { m_ConnStr = connStr; Initialization ; } /// <summary> /// 构造![](/icons/96069hanshu.gif) /// </summary> /// <param name="connStr">数据库连接 串</param> /// <param name="commandTimeout">执行时间</param> public DBHelper( connStr, commandTimeout) { m_ConnStr = connStr; m_CommandTimeout = commandTimeout; Initialization ; } /// <summary> /// 化![](/icons/96069hanshu.gif) /// </summary> protected void Initialization![](/icons/96069kh.gif) { try { m_Connection = SqlConnection(m_ConnStr); (m_Connection.State ConnectionState.Closed) m_Connection.Open ; m_Command = SqlCommand ; } catch (Exception ex) { throw Exception(ex.Message.![](/icons/96069ToString.gif) ); } } #endregion #region Dispose /// <summary> /// 析构![](/icons/96069hanshu.gif) /// </summary> ~DBHelper![](/icons/96069kh.gif) { 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.![](/icons/96069ToString.gif) ); } finally { m_Connection = null; } } } m_AlreadyDispose = true;//已经进行 处理 } /// <summary> /// 释放资源 /// </summary> public void Dispose![](/icons/96069kh.gif) { 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.![](/icons/96069ToString.gif) ,@"C:sql.txt"); throw Exception(ex.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) , @"C:sql.txt"); throw Exception(ex.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) , @"C:sql.txt"); throw Exception(ex.![](/icons/96069ToString.gif) ); } finally { (m_Command != null) m_Command.Dispose ; m_Command.Parameters.Clear ; } } #endregion #region ExecuteTransaction public bool ExecuteTransaction(![](/icons/96069string.gif) 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.![](/icons/96069ToString.gif) ); } finally { (m_Command != null) m_Command.Dispose ; trans.Dispose ; } } public bool ExecuteTransaction(![](/icons/96069string.gif) 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) , @"C:sql.txt"); throw Exception(ex.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) ); } 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.![](/icons/96069ToString.gif) .Trim .Empty) para.Value = DBNull.Value; ![](/icons/96069else.gif) 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.![](/icons/96069ToString.gif) .Trim .Empty) para.Value = DBNull.Value; ![](/icons/96069else.gif) para.Value = value; para; } #endregion } }
相关文章
读者评论
发表评论
|
|