蛙蛙推荐：简化基于数据库的DotNet应用程序开发
分析
要做一个基于数据库的应用程序，我们有大量的重复劳动要去做，建表，写增删改查的SQL语句，写与数据库表对应的实体类，写执行SQL的c#代码，写添加、修改、列表、详细页面等等。这些活动都是围绕着一个个都数据表来开展的，在.NET领域有很多的OR Mapping的方案，但好多方案用起来好用，但原理很复杂，而且性能也不好把握，所以我们可以做一个轻型的ORM方案。有了ORM框架，根据数据表写c#实体类这些劳动，其实也可以写一个代码生成器来帮我们生成，甚至代码生成器还能帮我们生成一些界面的代码。我们大概需要解决如下问题
1、我们要有一个通用的数据库操作帮助类，类似微软的DAAB，但最好能支持多种数据库；
2、我们要有一个使用简单的orm框架，能方便的用c#代码来进行数据库存取操作，而且要尽量保证性能，比如使用参数化查询；
3、我们要有一个代码生成器帮助我们解决一些重复性劳动，比如生成实体类，生成调用存储过程的c#代码等；
围绕这3个问题，我们一一来展开
一、通用的数据库吃操作帮助类
ADO.NET 2.0为我们访问数据库提供了一套与具体数据库无关的模型，其核心类是DbProviderFactory，它遵循了Provider模式，就是把对各种数据库的操作抽象出一个Provider，再由各种数据库去写与具体数据库相关的Provider，然后通过配置在运行时方便的切换数据库，而尽量少的不修改业务逻辑层的代码，业务逻辑层依赖的是抽象的Provider。这也是典型的依赖倒置，就是说业务逻辑说我需要哪些接口，我依赖这些接口，而让别人去实现这些接口，在运行的时候再去加载调用实现这些接口的具体类。
为了提高性能，减少SQLSERVER执行计划的重编译，我们尽量使用参数化的查询，而一个固定的语句或者存储过程它的ADO.NET参数是固定的，所以我们可以把这些参数缓存起来，避免每次执行SQL语句都创新新的参数对象。另外oledb的ado.net provider的参数是不能命名的，所以给参数赋值要按顺序赋值。
为了使用方便，我们为执行SQL语句提供如下的API
public System.Data.DataSet SqlExecuteDateSet(string sql, string[] paramters, params object[] values)
public System.Data.DataTable SqlExecuteDateTable(string sql, string[] paramters, params object[] values)
public int SqlExecuteNonQuery(string sql, string[] paramters, params object[] values)
public System.Data.Common.DbDataReader SqlExecuteReader(string sql, string[] paramters, params object[] values)
public object SqlExecuteScalar(string sql, string[] paramters, params object[] values)
public System.Data.DataTable SqlExecuteDateTable(string sql, string[] paramters, params object[] values)
public int SqlExecuteNonQuery(string sql, string[] paramters, params object[] values)
public System.Data.Common.DbDataReader SqlExecuteReader(string sql, string[] paramters, params object[] values)
public object SqlExecuteScalar(string sql, string[] paramters, params object[] values)
当然，为了支持存储过程的执行，以及数据库事务，还需要提供相关的重载的API。大概的使用示例（面向SQLSERVER）如下：
DbHelper dbhelper = new DbHelper();
string sql = "delete from Citys where CityId = @id";
using (DatabaseTrans trans = new DatabaseTrans(dbhelper))
{
try
{
dbhelper.SqlExecuteNonQuery(trans, sql, new string[] { "@id" }, 1);
dbhelper.SqlExecuteNonQuery(trans, sql, new string[] { "@id" }, 2);
trans.Commit();
OutPut("ok");
}
catch (Exception)
{
trans.RollBack();
OutPut("no ok");
}
}
string sql = "delete from Citys where CityId = @id";
using (DatabaseTrans trans = new DatabaseTrans(dbhelper))
{
try
{
dbhelper.SqlExecuteNonQuery(trans, sql, new string[] { "@id" }, 1);
dbhelper.SqlExecuteNonQuery(trans, sql, new string[] { "@id" }, 2);
trans.Commit();
OutPut("ok");
}
catch (Exception)
{
trans.RollBack();
OutPut("no ok");
}
}
二、通用的ORM框架
先看如下的代码
//1、添加
xxxCase xxxCase = new xxxCase();
xxxCase.Title = "abc";
xxxCase.Content = "呵呵";
xxxCase.CaseFrom = CaseFrom.客服投诉;
xxxCase.PostUser = "huhao";
xxxCase.CreateTime = DateTime.Now;
xxxCase.CaseType = CaseType.生产环境查询;
xxxCase.Priority = CasePriority.中;
xxxCase.ReleationServices = "aaa,bbb";
xxxCase.ReleationClient = "ccc,ddd";
EntityBase.Insert(xxxCase);
//2、修改
xxxCase.ClearInnerData();
xxxCase.CaseId = 1;
xxxCase.Title = "嘿嘿";
EntityBase.Update(xxxCase);
//3、删除
xxxCase.ClearInnerData();
xxxCase.CaseId = 1;
EntityBase.Delete(xxxCase);
//4、复杂条件查询，查询大于昨天的客服投诉或者wawa关闭的问题
WhereCondition condition = new WhereCondition(
xxxCase.CaseFromColName,SqlOperator.Equal, (short)CaseFrom.客服投诉)
.And(
new WhereCondition(xxxCase.CreateTimeColName, SqlOperator.GreaterThan ,
DateTime.Now.AddDays(-1)))
.Group()
.Or(
new WhereCondition(xxxCase.CloseUserColName, SqlOperator.Equal, "wawa"));
IList<xxxCase> list = EntityBase.Select<xxxCase>(
new string[] {"Title", "PostUser"}, condition);
foreach (xxxCase item in list)
{
Console.WriteLine("{0}-{1}",item.Title,item.PostUser);
}
Console.ReadKey();
xxxCase xxxCase = new xxxCase();
xxxCase.Title = "abc";
xxxCase.Content = "呵呵";
xxxCase.CaseFrom = CaseFrom.客服投诉;
xxxCase.PostUser = "huhao";
xxxCase.CreateTime = DateTime.Now;
xxxCase.CaseType = CaseType.生产环境查询;
xxxCase.Priority = CasePriority.中;
xxxCase.ReleationServices = "aaa,bbb";
xxxCase.ReleationClient = "ccc,ddd";
EntityBase.Insert(xxxCase);
//2、修改
xxxCase.ClearInnerData();
xxxCase.CaseId = 1;
xxxCase.Title = "嘿嘿";
EntityBase.Update(xxxCase);
//3、删除
xxxCase.ClearInnerData();
xxxCase.CaseId = 1;
EntityBase.Delete(xxxCase);
//4、复杂条件查询，查询大于昨天的客服投诉或者wawa关闭的问题
WhereCondition condition = new WhereCondition(
xxxCase.CaseFromColName,SqlOperator.Equal, (short)CaseFrom.客服投诉)
.And(
new WhereCondition(xxxCase.CreateTimeColName, SqlOperator.GreaterThan ,
DateTime.Now.AddDays(-1)))
.Group()
.Or(
new WhereCondition(xxxCase.CloseUserColName, SqlOperator.Equal, "wawa"));
IList<xxxCase> list = EntityBase.Select<xxxCase>(
new string[] {"Title", "PostUser"}, condition);
foreach (xxxCase item in list)
{
Console.WriteLine("{0}-{1}",item.Title,item.PostUser);
}
Console.ReadKey();
上面的代码是以面向对象（请忽略那些关于贫血模型的讨论，说上面的代码不够OO，上面的代码至少相对的面向对象，而且看起来很直观）的方式去执行一些业务，这应该比到处写SQL语句要强很多吧，而且如果这些操作内部使用的仍然是参数化查询而不是拼sql字符串的话，性能也不会很差（请忽略具体语句是否能使用索引的讨论，那得具体分析）。
我们看一下EntityBase.Insert方法的实现，逻辑很简单明了，其他的Update,Delete,Select也是类似的思路。
private static DbHelper _db = new DbHelper();
public static void Insert(EntityBase entity) {
string sql = GetInsertSql(entity);
string[] parameters = GetParameters(entity.InnerData);
object[] parameterValues = GetParameterValuess(entity.InnerData);
_db.SqlExecuteNonQuery(sql, parameters, parameterValues);
}
private static string GetInsertSql(EntityBase entity) {
int len = entity.InnerData.Count;
StringBuilder sql = new StringBuilder();
sql.AppendFormat("INSERT INTO [{0}]\r\n", entity.TableName);
sql.Append("(\r\n");
for (int i = 0; i < len; i++) {
if (i != len - 1)
sql.AppendFormat("[{0}],", entity.InnerData[i].Key);
else
sql.AppendFormat("[{0}]", entity.InnerData[i].Key);
}
sql.Append(")\r\n");
sql.Append("VALUES(\r\n");
for (int i = 0; i < len; i++) {
if (i != len - 1)
sql.AppendFormat("@{0},", entity.InnerData[i].Key);
else
sql.AppendFormat("@{0}", entity.InnerData[i].Key);
}
sql.Append(")\r\n");
return sql.ToString();
}
private static string[] GetParameters(IList<DbCommonClass<string, object>> items) {
int len = items.Count;
List<string> parameters = new List<string>();
for (int i = 0; i < len; i++) {
parameters.Add(string.Format("@{0}", items[i].Key));
}
return parameters.ToArray();
}
private static object[] GetParameterValuess(List<DbCommonClass<string, object>> items) {
int len = items.Count;
List<object> parameters = new List<object>();
for (int i = 0; i < len; i++) {
parameters.Add(items[i].Value);
}
return parameters.ToArray();
}
public static void Insert(EntityBase entity) {
string sql = GetInsertSql(entity);
string[] parameters = GetParameters(entity.InnerData);
object[] parameterValues = GetParameterValuess(entity.InnerData);
_db.SqlExecuteNonQuery(sql, parameters, parameterValues);
}
private static string GetInsertSql(EntityBase entity) {
int len = entity.InnerData.Count;
StringBuilder sql = new StringBuilder();
sql.AppendFormat("INSERT INTO [{0}]\r\n", entity.TableName);
sql.Append("(\r\n");
for (int i = 0; i < len; i++) {
if (i != len - 1)
sql.AppendFormat("[{0}],", entity.InnerData[i].Key);
else
sql.AppendFormat("[{0}]", entity.InnerData[i].Key);
}
sql.Append(")\r\n");
sql.Append("VALUES(\r\n");
for (int i = 0; i < len; i++) {
if (i != len - 1)
sql.AppendFormat("@{0},", entity.InnerData[i].Key);
else
sql.AppendFormat("@{0}", entity.InnerData[i].Key);
}
sql.Append(")\r\n");
return sql.ToString();
}
private static string[] GetParameters(IList<DbCommonClass<string, object>> items) {
int len = items.Count;
List<string> parameters = new List<string>();
for (int i = 0; i < len; i++) {
parameters.Add(string.Format("@{0}", items[i].Key));
}
return parameters.ToArray();
}
private static object[] GetParameterValuess(List<DbCommonClass<string, object>> items) {
int len = items.Count;
List<object> parameters = new List<object>();
for (int i = 0; i < len; i++) {
parameters.Add(items[i].Value);
}
return parameters.ToArray();
}
当然Select方法稍微复杂一些，因为我们要考虑复杂的Where字句，Top字句，OrderBy字句等，我们为Where字句建立了一个WhereCondition对象，来方便的用c#代码来描述SQL的where语句，但是为了实现简单，我们不去实现表连接，复杂的子语句等支持（我个人认为向NBear等框架做的过于强大了）。
三、代码生成器
ADO.NET的各种数据库实现都有获取某个数据库Schema的API，其中最重要的是SqlConnection.GetSchema(SqlClientMetaDataCollectionNames.Tables)和SqlCommand.ExecuteReader( CommandBehavior.KeyInfo | CommandBehavior.CloseConnection)方法，有了这两个方法，我们可以枚举一个数据库的所有表，及某个表的所有字段，及每个字段的类型，长度、可否为空，是否为主键，是否为标识列等信息，有了这些元数据，我们再根据一个模板就可以生成特定格式的代码了。而且我们需要新增加一种代码生成的格式的话，只需添加一个模板就可以了，这样的代码生成器还有扩展性，而不是一个写死的针对特定框架的代码生成器。
为了脱离对特定数据库的依赖，我们建立一个代码生成器的元数据模型，如下
public class CodeModel
{
public string ClassName;
public string TableName;
public string Descript;
public string Namespace;
public string PkColName;
public List<CodeProperty> Properties;
}
public class CodeProperty
{
public string DbColName;
public int? DbLength;
public bool DbAllowNull
public SqlDbType DbType;
public string DbTypeStr;
public bool DbIsIdentity;
public bool DbIsPk;
public string Descript;
public string PropertyName;
public System.Type CSharpType;
public string CSharpTypeStr;
public bool UiAllowEmpty;
public bool UiIsShowOn;
public long? UiMaxCheck;
public long? UiMinCheck;
public string UiRegxCheck;
}
{
public string ClassName;
public string TableName;
public string Descript;
public string Namespace;
public string PkColName;
public List<CodeProperty> Properties;
}
public class CodeProperty
{
public string DbColName;
public int? DbLength;
public bool DbAllowNull
public SqlDbType DbType;
public string DbTypeStr;
public bool DbIsIdentity;
public bool DbIsPk;
public string Descript;
public string PropertyName;
public System.Type CSharpType;
public string CSharpTypeStr;
public bool UiAllowEmpty;
public bool UiIsShowOn;
public long? UiMaxCheck;
public long? UiMinCheck;
public string UiRegxCheck;
}
得到元数据后，剩下的就是读取模板，然后替换字符串了，比如实体类的模板，如下
using System;
using System.Collections.Generic;
using WawaSoft.Common;
namespace $model.namespace$ {
public class $model.classname$ : EntityBase {
$foreach.prop$
public const string $prop.property$ColName = "$prop.dbcolname$";
$endforeach$
private static readonly List<string> _Cols = new List<string>();
static $model.classname$()
{
$foreach.prop$
_Cols.Add($prop.property$ColName);
$endforeach$
}
public $model.classname$() {
_tableName = "$model.tablename$";
_PkName = "$model.pkcolname$";
}
$foreach.prop$
private $prop.csharptype$ $prop.property2$;
$endforeach$
$foreach.prop$
public $prop.csharptype$ $prop.property$ {
get { return $prop.property2$; }
set {
$prop.property2$ = value;
AddInnerData("$prop.property2$", value);
}
}
$endforeach$
protected override IList<string> Cols
{
get { return _Cols; }
}
public override void ConvertToEntity(IEnumerable<DbCommonClass<string, object>> items) {
foreach (DbCommonClass<string, object> item in items) {
switch (item.Key) {
$foreach.prop$
case $prop.property$ColName:
$prop.property2$ = ($prop.csharptype$)item.Value;
break;
$endforeach$
}
}
}
}
}
using System.Collections.Generic;
using WawaSoft.Common;
namespace $model.namespace$ {
public class $model.classname$ : EntityBase {
$foreach.prop$
public const string $prop.property$ColName = "$prop.dbcolname$";
$endforeach$
private static readonly List<string> _Cols = new List<string>();
static $model.classname$()
{
$foreach.prop$
_Cols.Add($prop.property$ColName);
$endforeach$
}
public $model.classname$() {
_tableName = "$model.tablename$";
_PkName = "$model.pkcolname$";
}
$foreach.prop$
private $prop.csharptype$ $prop.property2$;
$endforeach$
$foreach.prop$
public $prop.csharptype$ $prop.property$ {
get { return $prop.property2$; }
set {
$prop.property2$ = value;
AddInnerData("$prop.property2$", value);
}
}
$endforeach$
protected override IList<string> Cols
{
get { return _Cols; }
}
public override void ConvertToEntity(IEnumerable<DbCommonClass<string, object>> items) {
foreach (DbCommonClass<string, object> item in items) {
switch (item.Key) {
$foreach.prop$
case $prop.property$ColName:
$prop.property2$ = ($prop.csharptype$)item.Value;
break;
$endforeach$
}
}
}
}
}
生成的实体类，如下
using System;
using System.Collections.Generic;
using WawaSoft.Common;
namespace Entities {
public class User : EntityBase {
public const string UserIdColName = "UserId";
public const string UsernameColName = "Username";
public const string NameColName = "Name";
public const string PasswordColName = "Password";
public const string CreateTimeColName = "CreateTime";
public const string IsAdminColName = "IsAdmin";
private static readonly List<string> _Cols = new List<string>();
static User() {
_Cols.Add(UserIdColName);
_Cols.Add(UsernameColName);
_Cols.Add(NameColName);
_Cols.Add(PasswordColName);
_Cols.Add(CreateTimeColName);
_Cols.Add(IsAdminColName);
}
public User() {
_tableName = "User";
_PkName = "UserId";
}
private Nullable<Int32> userid;
private String username;
private String name;
private String password;
private Nullable<DateTime> createtime;
private Nullable<Boolean> isadmin;
public Nullable<Int32> UserId {
get { return userid; }
set {
userid = value;
AddInnerData("userid", value);
}
}
public String Username {
get { return username; }
set {
username = value;
AddInnerData("username", value);
}
}
public String Name {
get { return name; }
set {
name = value;
AddInnerData("name", value);
}
}
public String Password {
get { return password; }
set {
password = value;
AddInnerData("password", value);
}
}
public Nullable<DateTime> CreateTime {
get { return createtime; }
set {
createtime = value;
AddInnerData("createtime", value);
}
}
public Nullable<Boolean> IsAdmin {
get { return isadmin; }
set {
isadmin = value;
AddInnerData("isadmin", value);
}
}
protected override IList<string> Cols {
get { return _Cols; }
}
public override void ConvertToEntity(IEnumerable<DbCommonClass<string, object>> items) {
foreach (DbCommonClass<string, object> item in items) {
switch (item.Key) {
case UserIdColName:
userid = (Nullable<Int32>)item.Value;
break;
case UsernameColName:
username = (String)item.Value;
break;
case NameColName:
name = (String)item.Value;
break;
case PasswordColName:
password = (String)item.Value;
break;
case CreateTimeColName:
if (item.Value != DBNull.Value)
createtime = (Nullable<DateTime>)item.Value;
break;
case IsAdminColName:
if (item.Value != DBNull.Value)
isadmin = (Nullable<Boolean>)item.Value;
break;
}
}
}
}
}
using System.Collections.Generic;
using WawaSoft.Common;
namespace Entities {
public class User : EntityBase {
public const string UserIdColName = "UserId";
public const string UsernameColName = "Username";
public const string NameColName = "Name";
public const string PasswordColName = "Password";
public const string CreateTimeColName = "CreateTime";
public const string IsAdminColName = "IsAdmin";
private static readonly List<string> _Cols = new List<string>();
static User() {
_Cols.Add(UserIdColName);
_Cols.Add(UsernameColName);
_Cols.Add(NameColName);
_Cols.Add(PasswordColName);
_Cols.Add(CreateTimeColName);
_Cols.Add(IsAdminColName);
}
public User() {
_tableName = "User";
_PkName = "UserId";
}
private Nullable<Int32> userid;
private String username;
private String name;
private String password;
private Nullable<DateTime> createtime;
private Nullable<Boolean> isadmin;
public Nullable<Int32> UserId {
get { return userid; }
set {
userid = value;
AddInnerData("userid", value);
}
}
public String Username {
get { return username; }
set {
username = value;
AddInnerData("username", value);
}
}
public String Name {
get { return name; }
set {
name = value;
AddInnerData("name", value);
}
}
public String Password {
get { return password; }
set {
password = value;
AddInnerData("password", value);
}
}
public Nullable<DateTime> CreateTime {
get { return createtime; }
set {
createtime = value;
AddInnerData("createtime", value);
}
}
public Nullable<Boolean> IsAdmin {
get { return isadmin; }
set {
isadmin = value;
AddInnerData("isadmin", value);
}
}
protected override IList<string> Cols {
get { return _Cols; }
}
public override void ConvertToEntity(IEnumerable<DbCommonClass<string, object>> items) {
foreach (DbCommonClass<string, object> item in items) {
switch (item.Key) {
case UserIdColName:
userid = (Nullable<Int32>)item.Value;
break;
case UsernameColName:
username = (String)item.Value;
break;
case NameColName:
name = (String)item.Value;
break;
case PasswordColName:
password = (String)item.Value;
break;
case CreateTimeColName:
if (item.Value != DBNull.Value)
createtime = (Nullable<DateTime>)item.Value;
break;
case IsAdminColName:
if (item.Value != DBNull.Value)
isadmin = (Nullable<Boolean>)item.Value;
break;
}
}
}
}
}
小结
解决了以上几个问题，再开发数据库应用，应该会提高不少效率。
相关代码下载：code_wawa.zip
