(精华)2020年6月27日 C#类库 DbHelper(Ado.net数据库封装)
【摘要】
using EFCore.Sharding;
using System;
using System.Collections.Generic;
using System.Data;
using System...
using EFCore.Sharding;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
namespace Core.Util
{
/// <summary>
/// 描述：数据库操作抽象帮助类
/// </summary>
public abstract class DbHelper
{
#region 构造函数
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="conString">完整连接字符串</param>
public DbHelper(DatabaseType dbType, string conString)
{
_dbType = dbType;
_conString = conString;
}
#endregion
#region 私有成员
/// <summary>
/// 数据库类型
/// </summary>
protected DatabaseType _dbType;
/// <summary>
/// 连接字符串
/// </summary>
protected string _conString;
/// <summary>
/// 实体需要引用的额外命名空间
/// </summary>
protected string _extraUsingNamespace { get; set; } = string.Empty;
/// <summary>
/// 类型映射字典
/// </summary>
protected abstract Dictionary<string, Type> DbTypeDic { get; }
#endregion
#region 外部接口
/// <summary>
/// 通过数据库连接字符串和Sql语句查询返回DataTable
/// </summary>
/// <param name="sql">Sql语句</param>
/// <returns></returns>
public DataTable GetDataTableWithSql(string sql)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
adapter.SelectCommand = cmd;
DataSet table = new DataSet();
adapter.Fill(table);
return table.Tables[0];
}
}
}
/// <summary>
/// 通过数据库连接字符串和Sql语句查询返回DataTable,参数化查询
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public DataTable GetDataTableWithSql(string sql, List<DbParameter> parameters)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null && parameters.Count > 0)
{
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
}
DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
adapter.SelectCommand = cmd;
DataSet table = new DataSet();
adapter.Fill(table);
cmd.Parameters.Clear();
return table.Tables[0];
}
}
}
/// <summary>
/// 通过数据库连接字符串和Sql语句查询返回List
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="sqlStr">Sql语句</param>
/// <returns></returns>
public List<T> GetListBySql<T>(string sqlStr)
{
return GetDataTableWithSql(sqlStr).ToList<T>();
}
/// <summary>
/// 通过数据库连接字符串和Sql语句查询返回List,参数化查询
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="sqlStr">Sql语句</param>
/// <param name="param">查询参数</param>
/// <returns></returns>
public List<T> GetListBySql<T>(string sqlStr, List<DbParameter> param)
{
return GetDataTableWithSql(sqlStr, param).ToList<T>();
}
/// <summary>
/// 执行无返回值的Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
public int ExecuteSql(string sql)
{
int count = 0;
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = dbProviderFactory.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
count = cmd.ExecuteNonQuery();
return count;
}
}
}
/// <summary>
/// 执行无返回值的Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="paramters"></param>
public int ExecuteSql(string sql, List<DbParameter> paramters)
{
int count = 0;
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = dbProviderFactory.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
if (paramters != null && paramters.Count > 0)
{
foreach (var item in paramters)
{
cmd.Parameters.Add(item);
}
}
count = cmd.ExecuteNonQuery();
return count;
}
}
}
/// <summary>
/// 获取数据库中的所有表
/// </summary>
/// <param name="schemaName">模式（架构）</param>
/// <returns></returns>
public abstract List<DbTableInfo> GetDbAllTables(string schemaName = null);
/// <summary>
/// 通过连接字符串和表名获取数据库表的信息
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public abstract List<TableInfo> GetDbTableInfo(string tableName);
/// <summary>
/// 将数据库类型转为对应C#数据类型
/// </summary>
/// <param name="dbTypeStr">数据类型</param>
/// <returns></returns>
public virtual Type DbTypeStr_To_CsharpType(string dbTypeStr)
{
string _dbTypeStr = dbTypeStr.ToLower();
Type type = null;
if (DbTypeDic.ContainsKey(_dbTypeStr))
type = DbTypeDic[_dbTypeStr];
else
type = typeof(string);
return type;
}
/// <summary>
/// 生成实体文件
/// </summary>
/// <param name="infos">表字段信息</param>
/// <param name="tableName">表名</param>
/// <param name="tableDescription">表描述信息</param>
/// <param name="filePath">文件路径（包含文件名）</param>
/// <param name="nameSpace">实体命名空间</param>
/// <param name="schemaName">架构（模式）名</param>
public virtual void SaveEntityToFile(List<TableInfo> infos, string tableName, string tableDescription, string filePath, string nameSpace, string schemaName = null)
{
string properties = "";
string schema = "";
if (!schemaName.IsNullOrEmpty())
schema = $@", Schema = ""{schemaName}""";
infos.ForEach((item, index) =>
{
string isKey = item.IsKey ? $@"
[Key, Column(Order = {index + 1})]" : "";
Type type = DbTypeStr_To_CsharpType(item.Type);
string isNullable = item.IsNullable && type.IsValueType ? "?" : "";
string description = item.Description.IsNullOrEmpty() ? item.Name : item.Description;
string newPropertyStr =
$@"
/// <summary>
/// {description}
/// </summary>{isKey}
public {type.Name}{isNullable} {item.Name} {{ get; set; }}
";
properties += newPropertyStr;
});
string fileStr =
$@"using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
{_extraUsingNamespace}
namespace {nameSpace}
{{
/// <summary>
/// {tableDescription}
/// </summary>
[Table(""{tableName}""{schema})]
public class {tableName}
{{
{properties}
}}
}}";
FileHelper.WriteTxt(fileStr, filePath, FileMode.Create);
}
#endregion
}
}
- 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
- 290
- 291
- 292
- 293
- 294
- 295
文章来源: codeboy.blog.csdn.net，作者：愚公搬代码，版权归原作者所有，如需转载，请联系作者。
原文链接：codeboy.blog.csdn.net/article/details/106979924
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）