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;
}

  

 

posted @   老张一笑  阅读(1871)  评论(2编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示