C# 操作数据库就的那点代码
操作数据库的那点代码,别在费劲每个数据库都写一遍SQLHelper,SQLiteHelper,OleDbHelper,了,这里都有了.
接口不发了,自己抽取定义就行了.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 | public abstract class DbHelperBase : IDbHelper { /// <summary> /// 直接获得连接字符串 /// </summary> /// <param name="connStr"></param> public DbHelperBase( string connStr) { _ConnStr = connStr; _LockExecNonQuery = new object (); _LockGetDataReader = new object (); _LockGetScalar = new object (); _LockGetDataSet = new object (); } /// <summary> /// 连接字符串 /// </summary> protected string _ConnStr; /// <summary> /// 是否事务 /// </summary> bool _IsTrans = false ; object _LockExecNonQuery; object _LockGetDataReader; object _LockGetScalar; object _LockGetDataSet; protected abstract DbConnection DBConnectionObj { get ; } protected abstract DbCommand DbCommandObj { get ; } protected abstract DbDataAdapter DbDataAdapterObj { get ; } protected DbTransaction DbTransObj; /// <summary> /// 当前连接 /// </summary> public DbConnection CurrentConnection { get { return DBConnectionObj; } } /// <summary> /// 执行sql问错误写日志方法 /// </summary> protected virtual void WriteErrLog(Exception ex, string sqlText, params DbParameter[] param) { StringPlus logs = new StringPlus(); logs.AppendLine( "SQL文本:" ); logs.AppendLine(sqlText); logs.AppendLine( "Sql文参数:" ); if (param != null ) { foreach (DbParameter item in param) { logs.AppendSpaceLine(1, "参数名:{0},参数值:{1}" , item.ParameterName, item.Value); } } logs.AppendLine( "原始错误:" ); logs.AppendLine(ex.Message); LogHelper.WriteLog( "DbErrorLog" , logs.ToString()); } /// <summary> /// 打开连接,如果已经打开则什么都不执行了 /// </summary> protected virtual void OpenConnection() { if (DBConnectionObj.State != ConnectionState.Open) { DBConnectionObj.ConnectionString = _ConnStr; DBConnectionObj.Open(); } } /// <summary> /// 关闭连接,如果没有开始事务或连接打开时才关闭 /// </summary> void CloseConnect() { if (!_IsTrans) { if (DBConnectionObj.State == ConnectionState.Open) { DBConnectionObj.Close(); DBConnectionObj.Dispose(); } } } /// <summary> /// 给当前DbCommand对象赋值,并且OpenConnection(); /// </summary> void SetCommandAndOpenConnect( string sqlText, CommandType cmdType, params DbParameter[] param) { //按说赋值Connection,CommandType,是不用多次赋值的 DbCommandObj.CommandType = cmdType; DbCommandObj.Connection = DBConnectionObj; DbCommandObj.Parameters.Clear(); if (param != null ) { DbCommandObj.Parameters.AddRange(param); } DbCommandObj.CommandText = sqlText; OpenConnection(); } /// <summary> /// 开始执行事务 /// </summary> public virtual void TransStart() { OpenConnection(); DbTransObj = DBConnectionObj.BeginTransaction(); DbCommandObj.Transaction = DbTransObj; _IsTrans = true ; } /// <summary> /// 事务提交 /// </summary> public virtual void TransCommit() { _IsTrans = false ; DbTransObj.Commit(); CloseConnect(); } /// <summary> /// 事务回滚 /// </summary> public virtual void TransRollback() { _IsTrans = false ; DbTransObj.Rollback(); CloseConnect(); } /// <summary> /// 执行一条指定命令类型(SQL语句或存储过程等)的SQL语句,返回所影响行数 /// </summary> public virtual int ExecNonQuery( string sqlText, CommandType cmdType, params DbParameter[] param) { lock (_LockExecNonQuery) { try { SetCommandAndOpenConnect(sqlText, cmdType, param); return DbCommandObj.ExecuteNonQuery(); } catch (Exception ex) { WriteErrLog(ex, sqlText, param); throw new DbDataException(); } finally { CloseConnect(); } } } /// <summary> /// 执行一条普通SQL语句的命令,返回所影响行数 /// </summary> public virtual int ExecNonQuery( string sqlText, params DbParameter[] param) { return ExecNonQuery(sqlText, CommandType.Text, param); } /// <summary> /// 获得DataReader对象 /// </summary> public virtual DbDataReader GetDataReader( string sqlText, CommandType cmdType, CommandBehavior cmdBehavior, params DbParameter[] param) { lock (_LockGetDataReader) { try { SetCommandAndOpenConnect(sqlText, cmdType, param); DbDataReader dbReader = DbCommandObj.ExecuteReader(cmdBehavior); return dbReader; } catch (Exception ex) { WriteErrLog(ex, sqlText, param); throw new DbDataException(); } finally { //DataReader用dbReader对象来关闭,即使非事务也是,不要把注释取消 //CloseConnect(); } } } /// <summary> /// 获得DataReader对象 /// </summary> public virtual DbDataReader GetDataReader( string sqlText, CommandType cmdType, params DbParameter[] param) { CommandBehavior cmdBehavior; if (_IsTrans) { cmdBehavior = CommandBehavior.Default; } else { //非事务时,关闭DataReader则关闭当前连接 cmdBehavior = CommandBehavior.CloseConnection; } return GetDataReader(sqlText, cmdType, cmdBehavior, param); } /// <summary> /// 执行sql语句返回DataReader对象 /// </summary> public virtual DbDataReader GetDataReader( string sqlText, params DbParameter[] param) { return GetDataReader(sqlText, CommandType.Text, param); } /// <summary> /// 获得首行首列 /// </summary> public virtual object GetScalar( string sqlText, CommandType cmdType, params DbParameter[] param) { lock (_LockGetScalar) { try { SetCommandAndOpenConnect(sqlText, cmdType, param); return DbCommandObj.ExecuteScalar(); } catch (Exception ex) { WriteErrLog(ex, sqlText, param); throw new DbDataException(); } finally { CloseConnect(); } } } /// <summary> /// 执行SQL语句,返回首行首列 /// </summary> public virtual object GetScalar( string sqlText, params DbParameter[] param) { return GetScalar(sqlText, CommandType.Text, param); } /// <summary> /// 执行一条SQL语句返回DataSet对象 /// </summary> public virtual DataSet GetDataSet( string sqlText, CommandType cmdType, params DbParameter[] param) { lock (_LockGetDataSet) { try { SetCommandAndOpenConnect(sqlText, cmdType, param); DbDataAdapterObj.SelectCommand = DbCommandObj; DataSet ds = new DataSet(); DbDataAdapterObj.Fill(ds); return ds; } catch (Exception ex) { WriteErrLog(ex, sqlText, param); throw new DbDataException(); } finally { CloseConnect(); } } } /// <summary> /// 执行一条SQL语句返回DataSet对象 /// </summary> public virtual DataSet GetDataSet( string sqlText, params DbParameter[] param) { return GetDataSet(sqlText, CommandType.Text, param); } /// <summary> /// 执行一条SQL语句返回DataTable对象(调用GetDataSet) /// </summary> public virtual DataTable GetDataTable( string sqlText, params DbParameter[] param) { return GetDataTable(sqlText, CommandType.Text, param); } /// <summary> /// 执行一条SQL语句返回DataTable对象(调用GetDataSet) /// </summary> public virtual DataTable GetDataTable( string sqlText, CommandType cmdType, params DbParameter[] param) { return (GetDataSet(sqlText, cmdType, param)).Tables[0]; } } |
下面是一个sqlserver的实现
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | public class SQLHelper : DbHelperBase { public SQLHelper( string connStr) : base (connStr) { } SqlConnection _DBConnectionObj; SqlCommand _DbCommandObj; SqlDataAdapter _DbDataAdapterObj; protected override DbConnection DBConnectionObj { get { //SqlBulkCopy aa = new SqlBulkCopy(new SqlConnection()); if (_DBConnectionObj == null ) { _DBConnectionObj = new SqlConnection(_ConnStr); } return _DBConnectionObj; } } protected override DbCommand DbCommandObj { get { if (_DbCommandObj == null ) { _DbCommandObj = new SqlCommand(); } return _DbCommandObj; } } protected override DbDataAdapter DbDataAdapterObj { get { if (_DbDataAdapterObj == null ) { _DbDataAdapterObj = new SqlDataAdapter(); } return _DbDataAdapterObj; } } } |
使用示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | IDbHelper sql = new SQLHelper( "连接字符串" ); try { sql.TransStart(); //事务开始 sql.ExecNonQuery( "执行sql语句1" ); //执行(增删改查)一条sql语句 sql.ExecNonQuery( "执行sql语句2" ); //执行(增删改查)一条sql语句 DataTable dt = sql.GetDataTable( "查询sql语句1" ); //获取一些数据 sql.TransCommit(); //提交事务 } catch (Exception ex) { sql.TransRollback(); //事务回滚 throw ; } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤