C#_自定义简单ORM

一，基本思路：利用C#的标签和反射功能实现自定义轻量级ORM
标签Attribute附着在实体属性或实体类名上，这样可以取到实体对应的表名，属性对应的表字段名，数据类型，是否主键，字段注释等基本信息
反射实现对实体动态赋值取值。
二，比较重要的类
1、ISqlHelper：接口，定义数据操作的基本方法，增删改查，事务。
SqlHelper：实现了ISqlHelper接口，将数据操作基本方法实现，不同数据库oracle,db2,sqlserver需要的参数类型不同，将这些不确定的信息以抽象方法的形式开出来在一层设置。 抽象类，定义了GetPasType抽象方法(不同数据库命令参数类不一样，如oracle用OracleParameter类型，sqlserver用SqlParameter类型，用这个方法取参数类信息)，GetCmdType抽象方法(如Oracle用OracleCommand类型，sqlserver用SqlCommand类型)；GetPageSql抽象方法(不同数据库查询分页sql语句写法不一样，因此分页方法在下一层实现)，SetPasChar抽象方法(不同数据库参数符号不一样，oracel用':',sqlserver用'@')
DB2SqlHelper,MSSQLSqlHelper,OracleSqlHelper都继承自SqlHelper，实现常用数据库的基本操作方法。
2、ColumnAttribute:继承自Attribute，实体类属性标签，有4个属性(字段名，字段说明，数据类型，是否主键)；TableAttribute继承自Attribute，实体名标签，记录实体对应的表名和表注释。
3、MyBaseClass:实体类的基类，包含了一些基本数据操作,实体里如果有int,double,datetime等类型都要设置为可空类型(因为是基础类型的话都有默认值，这样在增改查的时候就不清楚究竟是默认值还是程序员传入的参数值，用可空类型就能区分)
SetHelper方法:设置数据库基本信息，如连接字符串，数据库类型。参数为ISqlHelper。必须设置不然后面的增删改方法不能使用
Insert方法:将实体对应数据插入数据库
InsertCmd方法：返回插入数据库的命令
DeleteByPk方法:通过主键删除数据，主键对应属性必须有值，没有值操作失败
DeleteCmdByPk方法：返回通过主键删除数据的命令
UpdateByPk方法：通过主键更新数据，主键对应属性必须有值，没有值操作失败
UpdateCmdByPk方法：返回通过主键更新数据的命令
Select方法：查询数据，返回DataTable，重载方法返回对象集合List<T>,查询参数就是本实体里属性值不为null的所有属性
4、MyDbHelper：包含数据操作常用方法，基本功能与MyBaseClass差不多。
Select方法：查询数据，参数为实体，返回实体集合
GetEntityFromTable方法：把datable里的数据转换成实体集合，参数为datatable，返回值List<T>
Update方法：更新数据，更新条件和更新值都是实体
Delete方法：删除数据，以实体属性值为条件删除
5、EntityHelper：用于创建实体，保存为实体类的cs文件或返回实体类的字符串

三、部分代码

public interface ISqlHelper
{
/// <summary>
/// 执行查询操作
/// </summary>
DataTable Select(string sql, List<DbParameter> pas);

/// <summary>
/// 执行查询操作
/// </summary>
DataTable Select(string sql);

/// <summary>
/// 执行新增，修改，删除操作
/// </summary>
int ExeNoQuery(string sql, List<DbParameter> pas);

/// <summary>
/// 执行新增，修改，删除操作
/// </summary>
int ExeNoQuery(string sql);

/// <summary>
/// 执行事务，sql,参数各不相同
/// </summary>
bool ExeTrans(Dictionary<string, List<DbParameter>> dic, out string errMsg);

/// <summary>
/// 执行事务，相同的sql,参数不同
/// </summary>
bool ExeTrans(string sql, List<List<DbParameter>> pass, out string errMsg);

/// <summary>
/// 执行事务
/// </summary>
bool ExeTrans(List<DbCommand> cmds, out string errMsg);

/// <summary>
/// 设置SQL参数形式如：MSSQL的‘@’，DB2‘@’，ORACLE‘：’
/// </summary>
string SetPasChar();

/// <summary>
/// 根据表名，字段(如C1,C2,C3),where条件(如A='a' AND B= 'b'),起止行号得到不同区间数据
/// </summary>
DataTable GetPageTable(string tableName, string fields, string where, int fromIndex, int toIndex);

/// <summary>
/// 根据表名，where条件得到总行数
/// </summary>
int GetCountLines(string tableName, string where);

/// <summary>
/// 得到参数实例,因为不同数据库参数类型不一样
/// </summary>
Type GetPasType();

/// <summary>
/// 得到命令类型,因为不同数据库命令类型不一样
/// </summary>
Type GetCmdType();
}

 

public abstract class SqlHelper : ISqlHelper
{
protected string connStr = "";
protected DbConnection conn = null;
protected DbCommand cmd = null;
protected DbDataAdapter adapter = null;

public SqlHelper(string connStr)
{
this.connStr = connStr;
}

/// <summary>
/// 查询数据
/// </summary>
public DataTable Select(string sql, List<DbParameter> pas)
{
DataTable dt = new DataTable();
try
{
InitDataEnviroment();
conn.Open();
if (conn.State == ConnectionState.Open)
{
cmd.Connection = conn;
cmd.CommandText = sql;
//添加参数
if (pas != null)
{
foreach (DbParameter pa in pas)
{
cmd.Parameters.Add(pa);
}
}
adapter.SelectCommand = cmd;
adapter.Fill(dt);
}
return dt;
}
catch (Exception ex)
{
return null;
}
finally
{
DisposeDataEnviroment();
}
}

/// <summary>
/// 查询数据
/// </summary>
public DataTable Select(string sql)
{
DataTable dt = new DataTable();
try
{
InitDataEnviroment();
conn.Open();
if (conn.State == ConnectionState.Open)
{
cmd.Connection = conn;
cmd.CommandText = sql;
adapter.SelectCommand = cmd;
adapter.Fill(dt);
}
return dt;
}
catch (Exception ex)
{
return null;
}
finally
{
DisposeDataEnviroment();
}
}

/// <summary>
/// 执行新增，修改，删除操作
/// </summary>
public int ExeNoQuery(string sql, List<DbParameter> pas)
{
int cols = 0;
try
{
InitDataEnviroment();
conn.Open();
if (conn.State == ConnectionState.Open)
{
cmd.Connection = conn;
cmd.CommandText = sql;
//添加参数
if (pas != null)
{
foreach (DbParameter pa in pas)
{
cmd.Parameters.Add(pa);
}
}
cols = cmd.ExecuteNonQuery();
}
return cols;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return 0;
}
finally
{
DisposeDataEnviroment();
}
}

/// <summary>
/// 执行新增，修改，删除操作
/// </summary>
public int ExeNoQuery(string sql)
{
int cols = 0;
try
{
InitDataEnviroment();
conn.Open();
if (conn.State == ConnectionState.Open)
{
cmd.Connection = conn;
cmd.CommandText = sql;
cols = cmd.ExecuteNonQuery();
}
return cols;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return 0;
}
finally
{
DisposeDataEnviroment();
}
}

/// <summary>
/// 执行事务
/// </summary>
public bool ExeTrans(Dictionary<string, List<DbParameter>> dic, out string errMsg)
{
if (dic != null)
{
errMsg = "";
try
{
InitConnection();
InitCommand();
conn.Open();
if (conn.State == ConnectionState.Open)
{
cmd.Connection = conn;
using (IDbTransaction trans = conn.BeginTransaction())
{
//这一层try.catch防止有命令执行错误而用来回滚
try
{
foreach (KeyValuePair<string, List<DbParameter>> kv in dic)
{
cmd.CommandText = kv.Key;
foreach (DbParameter pa in kv.Value)
{
cmd.Parameters.Add(pa);
}
int cols = cmd.ExecuteNonQuery();
//清空参数，避免下一次执行时干扰
cmd.Parameters.Clear();
}
trans.Commit();
}
catch (Exception ep)
{
errMsg = ep.Message;
trans.Rollback();
return false;
}
}
}
return true;
}
catch (Exception ex)
{
errMsg = ex.Message;
return false;
}
finally
{
DisposeDataEnviroment();
}
}
else
{

errMsg = "sql及参数为null";
return false;
}
}

/// <summary>
/// 执行事务
/// </summary>
public bool ExeTrans(string sql, List<List<DbParameter>> pass, out string errMsg)
{
if (pass != null && (!string.IsNullOrEmpty(sql)))
{
errMsg = "";
try
{
InitConnection();
InitCommand();
conn.Open();
if (conn.State == ConnectionState.Open)
{
cmd.Connection = conn;
using (DbTransaction trans = conn.BeginTransaction())
{
//这一层try.catch防止有命令执行错误而用来回滚
try
{
cmd.Transaction = trans;
foreach (List<DbParameter> pas in pass)
{
cmd.CommandText = sql;
foreach (DbParameter pa in pas)
{
cmd.Parameters.Add(pa);
}
int cols = cmd.ExecuteNonQuery();
//清空参数，避免下一次执行时干扰
cmd.Parameters.Clear();
}
trans.Commit();
}
catch (Exception ep)
{
errMsg = ep.Message;
trans.Rollback();
return false;
}
}
}
return true;
}
catch (Exception ex)
{
errMsg = ex.Message;
return false;
}
finally
{
DisposeDataEnviroment();
}
}
else
{

errMsg = "sql及参数为null";
return false;
}
}

/// <summary>
/// 执行事务
/// </summary>
public bool ExeTrans(List<DbCommand> cmds, out string errMsg)
{
errMsg = "";
try
{
InitConnection();
InitCommand();
conn.Open();
if (conn.State == ConnectionState.Open)
{
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
foreach (DbCommand dbcmd in cmds)
{
dbcmd.Connection = conn;
dbcmd.Transaction = trans;
dbcmd.ExecuteNonQuery();
dbcmd.Dispose();
}
trans.Commit();
}
catch (Exception ep)
{
errMsg = ep.Message;
trans.Rollback();
return false;
}
}
}
return true;
}
catch (Exception ex)
{
errMsg = ex.Message;
return false;
}
finally
{
DisposeDataEnviroment();
}
}

/// <summary>
/// 初始化连接，命令，适配器
/// </summary>
protected abstract void InitDataEnviroment();

protected abstract void InitConnection();

private void InitCommand()
{
cmd = Activator.CreateInstance(GetCmdType()) as DbCommand;
}

/// <summary>
/// 关闭连接，释放资源
/// </summary>
private void DisposeDataEnviroment()
{
if (conn != null)
{
if (!conn.State.Equals(ConnectionState.Closed))
{
conn.Close();
}
conn.Dispose();
}
if (cmd != null) cmd.Dispose();
if (adapter != null) adapter.Dispose();
}

/// <summary>
/// 设置SQL参数形式如：MSSQL的‘@’，DBA2‘@’，ORACLE‘：’
/// </summary>
protected abstract string SetPas();

/// <summary>
/// 实现ISqlHelper接口的参数方法
/// </summary>
public string SetPasChar()
{
return SetPas();
}

/// <summary>
/// 根据表名，字段(如C1,C2,C3),where条件(如A='a' AND B= 'b'),起止行号得到不同区间数据
/// </summary>
public DataTable GetPageTable(string tableName, string fields, string where, int fromIndex, int toIndex)
{
//判断表名，字段是否为空
if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(fields))
{
return null;
}
//拼接where条件
if (!string.IsNullOrEmpty(where))
{
where = string.Format(" WHERE {0}", where);
}
return Select(GetPageSql(tableName, fields, where, fromIndex, toIndex), null);
}

/// <summary>
/// 分页语句需要的函数row_number不同，在下一层实现
/// </summary>
protected abstract string GetPageSql(string tableName, string fields, string where, int fromIndex, int toIndex);

/// <summary>
/// 根据表名，where条件(如A='a' AND B = 'b')得到总行数
/// </summary>
public int GetCountLines(string tableName, string where)
{
if (!string.IsNullOrEmpty(where))
{
where = string.Format(" WHERE {0}", where);
}
string sql = string.Format("SELECT COUNT(1) FROM {0} {1}", tableName, where);
int cols = 0;
DataTable dt = this.Select(sql, null);
if (dt != null && dt.Rows.Count > 0)
{
cols = int.Parse(dt.Rows[0][0].ToString());
}
return cols;
}

public abstract Type GetPasType();

public abstract Type GetCmdType();
}

 

public class OracleSqlHelper : SqlHelper
{
public OracleSqlHelper(string connStr)
: base(connStr)
{

}

protected override void InitDataEnviroment()
{
conn = new OracleConnection();
conn.ConnectionString = connStr;
cmd = new OracleCommand();
adapter = new OracleDataAdapter();
}

protected override void InitConnection()
{
conn = new OracleConnection();
conn.ConnectionString = connStr;
}

protected override string SetPas()
{
return ":";
}

/// <summary>
/// 根据表名，字段(C1,C2,C3的形式),where条件,起止行号得到不同区间数据的SQL语句
/// </summary>
protected override string GetPageSql(string tableName, string fields, string where, int fromIndex, int toIndex)
{
string sql = string.Format("SELECT * FROM (SELECT ROWNUM RN,{0} FROM {1} {2}) WHERE RN>={3} AND RN<={4}", fields, tableName, where, fromIndex, toIndex);
return sql;
}

public override Type GetPasType()
{
return typeof(OracleParameter);
}

public override Type GetCmdType()
{
return typeof(OracleCommand);
}
}

 

/// <summary>
/// 反射和标记实现ORM,insert,update,delete，select
/// </summary>
public class MyBaseClass
{
private ISqlHelper _helper = null; //操作数据的处理函数

/// <summary>
/// 插入一条记录，需要先调用SetHelper设置数据库操作函数
/// </summary>
public int Insert()
{
int cols = 0;
string fields = GetFields();
if (_helper != null)
{
//sql参数形式 MSSQL@，DB2@，ORACLE：
string paschar = _helper.SetPasChar();
//拼接insert脚本
string sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", GetTableName(), fields, paschar + fields.Replace(",", "," + paschar));
List<DbParameter> pas = new List<DbParameter>();
List<ColumnNameValueRelation> lst = GetColumns(false);
foreach (ColumnNameValueRelation cv in lst)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name;
para.Value = cv.Value;
pas.Add(para);
}
cols = _helper.ExeNoQuery(sql, pas);
}
return cols;
}

/// <summary>
/// 插入记录命令，需要先调用SetHelper设置数据库操作函数
/// </summary>
public DbCommand InsertCmd()
{
DbCommand cmd = null;
string fields = GetFields();
if (_helper != null)
{
cmd = (DbCommand)Activator.CreateInstance(_helper.GetCmdType());
//cmd.Connection = _helper.GetConnection();在helper的ExeTrans中设置
//sql参数形式 MSSQL@，DB2@，ORACLE：
string paschar = _helper.SetPasChar();
//拼接insert脚本
string sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", GetTableName(), fields, paschar + fields.Replace(",", "," + paschar));
cmd.CommandText = sql;
List<DbParameter> pas = new List<DbParameter>();
List<ColumnNameValueRelation> lst = GetColumns(false);
foreach (ColumnNameValueRelation cv in lst)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name;
para.Value = cv.Value;
cmd.Parameters.Add(para);
}
}
return cmd;
}

/// <summary>
/// 通过主键删除，需要先调用SetHelper设置数据库操作函数
/// </summary>
public int DeleteByPk()
{
int cols = 0;
string keyName = GetKeyName();
//主键不能为空，处理程序不能为null
if ((!string.IsNullOrEmpty(keyName)) && _helper != null)
{
string sql = string.Format("DELETE FROM {0} WHERE {1} = {2}{3}", GetTableName(), keyName, _helper.SetPasChar(), keyName);
object value = GetColumns(false).Where(en => en.Name.Equals(keyName)).ToList()[0].Value;
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = keyName;
para.Value = value;
List<DbParameter> pas = new List<DbParameter>();
pas.Add(para);
cols = _helper.ExeNoQuery(sql, pas);
}
return cols;
}

/// <summary>
/// 删除命令，需要先调用SetHelper设置数据库操作函数
/// </summary>
public DbCommand DeleteCmdByPk()
{
DbCommand cmd = null;
string keyName = GetKeyName();
//主键不能为空，处理程序不能为null
if ((!string.IsNullOrEmpty(keyName)) && _helper != null)
{
cmd = (DbCommand)Activator.CreateInstance(_helper.GetCmdType());
//cmd.Connection = _helper.GetConnection();在helper的ExeTrans中设置
string sql = string.Format("DELETE FROM {0} WHERE {1} = {2}{3}", GetTableName(), keyName, _helper.SetPasChar(), keyName);
cmd.CommandText = sql;
object value = GetColumns(false).Where(en => en.Name.Equals(keyName)).ToList()[0].Value;
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = keyName;
para.Value = value;
cmd.Parameters.Add(para);
}
return cmd;
}

/// <summary>
/// 通过主键修改，需要先调用SetHelper设置数据库操作函数
/// </summary>
public int UpdateByPk()
{
int cols = 0;
string keyName = GetKeyName();
//主键不能为空，处理程序不能为null
if ((!string.IsNullOrEmpty(keyName)) && _helper != null)
{
List<ColumnNameValueRelation> lst = GetColumns(false);

//构建sql的修改字段部分，C1=:C1,C2=:C2....
//Where找出不是主键的实例，Select用实例的Name值构造List<string>,Aggregate拼接不同字段
string updateFields = lst.Where(en => (!en.Name.Equals(keyName))).ToList().Select(en => (string.Format("{0}={1}{2}", en.Name, _helper.SetPasChar(), en.Name))).ToList().Aggregate<string>((curr, next) => string.Format("{0},{1}", curr, next));
//拼接update的sql
string sql = string.Format("UPDATE {0} SET {1} WHERE {2} = {3}{4}", GetTableName(), updateFields, keyName, _helper.SetPasChar(), keyName);
//参数序列
List<DbParameter> pas = new List<DbParameter>();
foreach (ColumnNameValueRelation cv in lst)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name;
para.Value = cv.Value;
pas.Add(para);
}
cols = _helper.ExeNoQuery(sql, pas);
}
return cols;
}

/// <summary>
/// 修改命令，需要先调用SetHelper设置数据库操作函数
/// </summary>
public DbCommand UpdateCmdByPk()
{
DbCommand cmd = null;
string keyName = GetKeyName();
//主键不能为空，处理程序不能为null
if ((!string.IsNullOrEmpty(keyName)) && _helper != null)
{
cmd = (DbCommand)Activator.CreateInstance(_helper.GetCmdType());
//cmd.Connection = _helper.GetConnection();在helper的ExeTrans中设置
List<ColumnNameValueRelation> lst = GetColumns(false);
//构建sql的修改字段部分，C1=:C1,C2=:C2....
//Where找出不是主键的实例，Select用实例的Name值构造List<string>,Aggregate拼接不同字段
string updateFields = lst.Where(en => (!en.Name.Equals(keyName))).ToList().Select(en => (string.Format("{0}={1}{2}", en.Name, _helper.SetPasChar(), en.Name))).ToList().Aggregate<string>((curr, next) => string.Format("{0},{1}", curr, next));
//拼接update的sql
string sql = string.Format("UPDATE {0} SET {1} WHERE {2} = {3}{4}", GetTableName(), updateFields, keyName, _helper.SetPasChar(), keyName);
cmd.CommandText = sql;
foreach (ColumnNameValueRelation cv in lst)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name;
para.Value = cv.Value;
cmd.Parameters.Add(para);
}
}
return cmd;
}

/// <summary>
/// 根据实例的属性值查询,引用类型属性的null值不会加入到where条件,字段是值类型(int,double,decimal)需要改为可空类型才能加入到where中，DateTime也要设置为可空类型(因为DateTime有默认值)。需要先调用SetHelper设置数据库操作函数
/// </summary>
public List<T> Select<T>()
{
List<T> lst = new List<T>();
DataTable dt = Select();
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
object obj = Activator.CreateInstance(this.GetType());
PropertyInfo[] props = this.GetType().GetProperties();
if (props != null)
{
foreach (PropertyInfo prop in props)
{
object[] attrs = prop.GetCustomAttributes(typeof(ColumnAttribute), true);
string columnName = ((ColumnAttribute)attrs[0]).ColumnName;
//给实例的属性赋值
//if判断是否为可空类型
if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))//属性是可空类型
{
//对可空类型赋值 如果值是DBNull则不需要赋值
if (!DBNull.Value.Equals(dr[columnName]))
{
//反射给可空类型赋值 ChangeType的Type参数值要为基础类型 如int?的基础类型是int decimal?的基础类型是decimal
prop.SetValue(obj, Convert.ChangeType(dr[columnName], Nullable.GetUnderlyingType(prop.PropertyType)), null);
}
}
else //不是可空类型
{
prop.SetValue(obj, Convert.ChangeType(dr[columnName], prop.PropertyType), null);
}

}
}
lst.Add((T)obj);
}
}
return lst;
}

//根据实例的属性值查询,null值不作where条件
private DataTable Select()
{
DataTable dt = null;
string keyName = GetKeyName();
//设置了处理程序才能取到数据
if (_helper != null)
{
//null值不做where条件
List<ColumnNameValueRelation> lst = GetColumns(false);

//构建sql的where条件字段部分，C1=:C1 AND C2=:C2....
//Select用实例的Name值构造List<string>,Aggregate拼接不同字段
string selectFields = lst.Select(en => (string.Format("{0}={1}{2}", en.Name, _helper.SetPasChar(), en.Name))).ToList().Aggregate<string>((curr, next) => string.Format("{0} AND {1}", curr, next));
//拼接select的sql
string sql = string.Format("SELECT * FROM {0} WHERE {1}", GetTableName(), selectFields);
//参数序列
List<DbParameter> pas = new List<DbParameter>();
//添加参数值
foreach (ColumnNameValueRelation cv in lst)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name;
para.Value = cv.Value;
pas.Add(para);
}
dt = _helper.Select(sql, pas);
}
return dt;
}

//设置操作数据的处理函数
public void SetHelper(ISqlHelper helper)
{
_helper = helper;
}

//取得表名
private string GetTableName()
{
string name = "";
Type tableType = this.GetType();
object[] attrs = tableType.GetCustomAttributes(typeof(TableAttribute), true);
if (attrs != null && attrs.Length > 0)
{
name = ((TableAttribute)attrs[0]).TableName;
}
return name;
}

//获取主键名称
private string GetKeyName()
{
string keyName = "";
Type type = this.GetType();
PropertyInfo[] props = type.GetProperties();
if (props != null)
{
foreach (PropertyInfo pi in props)
{
//获取属性的标记 字段信息
object[] colAttrs = pi.GetCustomAttributes(typeof(ColumnAttribute), true);
if (colAttrs != null && colAttrs.Length > 0)
{
ColumnAttribute attr = colAttrs[0] as ColumnAttribute;
if (attr.IsPK == true)
{
keyName = attr.ColumnName;//如果设置了主键，可以取到，没有就返回""
}
}
}
}
return keyName;
}

// 获取实例中字段名称与字段值的对应关系,containsNull表示是否包含NULL值及对应字段名称
private List<ColumnNameValueRelation> GetColumns(bool containsNull)
{
//存放字段名称和值的对应关系
List<ColumnNameValueRelation> lst = new List<ColumnNameValueRelation>();
Type type = this.GetType();
//取到实例的属性
PropertyInfo[] props = type.GetProperties();
if (props != null)
{
foreach (PropertyInfo pi in props)
{
//获取属性的标记 取字段信息
object[] colAttrs = pi.GetCustomAttributes(typeof(ColumnAttribute), true);
if (colAttrs != null && colAttrs.Length > 0)
{
ColumnAttribute attr = colAttrs[0] as ColumnAttribute;
object value = pi.GetValue(this, null);//取属性值
if (value == null)
{
if (containsNull) //值为null并且需要获取null数据
{
lst.Add(new ColumnNameValueRelation(attr.ColumnName, value));
}
}
else
{
lst.Add(new ColumnNameValueRelation(attr.ColumnName, value));
}
}
}
}
return lst;
}

//字段名的序列 用,连接 如f1,f2,f3,f4
private string GetFields()
{
List<ColumnNameValueRelation> lst = GetColumns(false);
//获取字段的序列
List<string> fields = lst.Select<ColumnNameValueRelation, string>(fr => fr.Name).ToList<string>();
//将字段名称用','连接
return fields.Aggregate<string>((curr, next) => string.Format("{0},{1}", curr, next));
}
}

 

public class MyDbHelper
{
private ISqlHelper _helper = null; //操作数据的处理函数

public MyDbHelper(ISqlHelper helper)
{
_helper = helper;
}

/// <summary>
/// 根据实例的属性值查询,引用类型属性的null值不会加入到where条件,字段是值类型(int,double,decimal)需要改为可空类型才能加入到where中，DateTime也要设置为可空类型(因为DateTime有默认值)。需要先调用SetHelper设置数据库操作函数
/// </summary>
public List<T> Select<T>(T where)
{
DataTable dt = SelectData<T>(where);
return GetEntityFromTable<T>(dt);
}

public List<T> Select<T>()
{
DataTable dt = SelectData<T>();
return GetEntityFromTable<T>(dt);
}

/// <summary>
/// 将DataTable的数据转换成实体数据集合,T类型必须从MyBaseClass类型继承,属性要加上字段相关信息的标记
/// </summary>
public List<T> GetEntityFromTable<T>(DataTable dt)
{
List<T> lst = new List<T>();
if (dt != null)
{
PropertyInfo[] props = typeof(T).GetProperties();
foreach (DataRow dr in dt.Rows)
{
object obj = Activator.CreateInstance(typeof(T));
if (props != null)
{
foreach (PropertyInfo prop in props)
{
object[] attrs = prop.GetCustomAttributes(typeof(ColumnAttribute), true);
string columnName = ((ColumnAttribute)attrs[0]).ColumnName;
if (dt.Columns.Contains(columnName))//源数据的列中是否包含实体属性对应字段
{
//给实例的属性赋值
//if判断是否为可空类型
if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))//属性是可空类型
{
//对可空类型赋值 如果值是DBNull则不需要赋值
if (!DBNull.Value.Equals(dr[columnName]))
{
//反射给可空类型赋值 ChangeType的Type参数值要为基础类型 如int?的基础类型是int decimal?的基础类型是decimal
prop.SetValue(obj, Convert.ChangeType(dr[columnName], Nullable.GetUnderlyingType(prop.PropertyType)), null);
}
}
else //不是可空类型
{
prop.SetValue(obj, Convert.ChangeType(dr[columnName], prop.PropertyType), null);
}
}
}
}
lst.Add((T)obj);
}
}
return lst;
}

/// <summary>
/// 以where的值作条件，更新为value中的值
/// </summary>
public int Update<T>(T where, T value)
{
int cols = 0;
//必需要有操作函数
if (_helper != null)
{
//条件和更新值都不能为null
if (where != null && value != null)
{
//取where条件
List<ColumnNameValueRelation> whereList = GetColumns<T>(false, where);
//取value值
List<ColumnNameValueRelation> valueList = GetColumns<T>(false, value);
if (whereList != null && valueList != null && whereList.Count > 0 && valueList.Count > 0)
{
//set和where中的字段有可能相同，为了避免参数名重复，给set字段的参数末加sp,给where字段的参数末加wp
string wp = "W";
string sp = "S";
//拼sql set部分 Select先组装成A=:A的形式，Aggregate累加成A=:A,B=:B
string sqlSet = valueList.Select(en => string.Format("{0}={1}{2}{3}", en.Name, _helper.SetPasChar(), en.Name, sp)).Aggregate((x, y) => (string.Format("{0},{1}", x, y)));
//拼sql where条件部分 Select先组装成A=:A的形式，Aggregate累加成A=:A AND B=:B
string sqlWhere = whereList.Select(en => string.Format("{0}={1}{2}{3}", en.Name, _helper.SetPasChar(), en.Name, wp)).Aggregate((x, y) => (string.Format("{0} AND {1}", x, y)));
//参数序列
List<DbParameter> pas = new List<DbParameter>();
//添加参数值 set值
foreach (ColumnNameValueRelation cv in valueList)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name + sp;
para.Value = cv.Value;
pas.Add(para);
}
//添加参数值 where值
foreach (ColumnNameValueRelation cv in whereList)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name + wp;
para.Value = cv.Value;
pas.Add(para);
}
//形成完整sql
string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", GetTableName<T>(), sqlSet, sqlWhere);
cols = _helper.ExeNoQuery(sql, pas);
}
}
}
return cols;
}

public int Delete<T>(T where)
{
int cols = 0;
if (_helper != null)
{
//条件不能为null
if (where != null)
{
//取where条件
List<ColumnNameValueRelation> whereList = GetColumns<T>(false, where);
if (whereList != null && whereList.Count > 0)
{
//拼sql where条件 Select先组装成A=:A的形式，Aggregate累加成A=:A AND B=:B
string sqlWhere = whereList.Select(en => string.Format("{0}={1}{2}", en.Name, _helper.SetPasChar(), en.Name)).Aggregate((x, y) => (string.Format("{0} AND {1}", x, y)));
//参数序列
List<DbParameter> pas = new List<DbParameter>();
//添加参数值 where
foreach (ColumnNameValueRelation cv in whereList)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name;
para.Value = cv.Value;
pas.Add(para);
}
//拼接sql
string sql = string.Format("DELETE FROM {0} WHERE {1}", GetTableName<T>(), sqlWhere);
cols = _helper.ExeNoQuery(sql, pas);
}
}
}
return cols;
}

//根据实例的属性值查询,null值不作where条件
internal DataTable SelectData<T>(T where)
{
DataTable dt = null;
//string keyName = GetKeyName<T>();
//设置了处理程序才能取到数据
if (_helper != null)
{
//null值不做where条件
List<ColumnNameValueRelation> lst = GetColumns<T>(false, where);

//构建sql的where条件字段部分，C1=:C1 AND C2=:C2....
//Select用实例的Name值构造List<string>,Aggregate拼接不同字段
string selectFields = lst.Select(en => (string.Format("{0}={1}{2}", en.Name, _helper.SetPasChar(), en.Name))).ToList().Aggregate<string>((curr, next) => string.Format("{0} AND {1}", curr, next));
//拼接select的sql
string sql = string.Format("SELECT * FROM {0} WHERE {1}", GetTableName<T>(), selectFields);
//参数序列
List<DbParameter> pas = new List<DbParameter>();
//添加参数值
foreach (ColumnNameValueRelation cv in lst)
{
DbParameter para = (DbParameter)Activator.CreateInstance(_helper.GetPasType());
para.ParameterName = cv.Name;
para.Value = cv.Value;
pas.Add(para);
}
dt = _helper.Select(sql, pas);
}
return dt;
}

internal DataTable SelectData<T>()
{
DataTable dt = null;
//设置了处理程序才能取到数据
if (_helper != null)
{
string sql = string.Format("SELECT * FROM {0}", GetTableName<T>());
dt = _helper.Select(sql);
}
return dt;
}

//取得表名
internal string GetTableName<T>()
{
string name = "";
Type tableType = typeof(T);
object[] attrs = tableType.GetCustomAttributes(typeof(TableAttribute), true);
if (attrs != null && attrs.Length > 0)
{
name = ((TableAttribute)attrs[0]).TableName;
}
return name;
}

//获取实例中字段名称与字段值的对应关系,containsNull表示是否包含NULL值及对应字段名称
internal List<ColumnNameValueRelation> GetColumns<T>(bool containsNull, T where)
{
if (where == null)
{
return null;
}

//存放字段名称和值的对应关系
List<ColumnNameValueRelation> lst = new List<ColumnNameValueRelation>();
Type type = typeof(T);
//取到实例的属性
PropertyInfo[] props = type.GetProperties();
if (props != null)
{
foreach (PropertyInfo pi in props)
{
//获取属性的标记 取字段信息
object[] colAttrs = pi.GetCustomAttributes(typeof(ColumnAttribute), true);
if (colAttrs != null && colAttrs.Length > 0)
{
ColumnAttribute attr = colAttrs[0] as ColumnAttribute;
object value = pi.GetValue(where, null);//取属性值
if (value == null)
{
if (containsNull) //值为null并且需要获取null数据
{
lst.Add(new ColumnNameValueRelation(attr.ColumnName, value));
}
}
else
{
lst.Add(new ColumnNameValueRelation(attr.ColumnName, value));
}
}
}
}
return lst;
}

//获取主键名称
internal string GetKeyName<T>()
{
string keyName = "";
Type type = typeof(T);
PropertyInfo[] props = type.GetProperties();
if (props != null)
{
foreach (PropertyInfo pi in props)
{
//获取属性的标记 字段信息
object[] colAttrs = pi.GetCustomAttributes(typeof(ColumnAttribute), true);
if (colAttrs != null && colAttrs.Length > 0)
{
ColumnAttribute attr = colAttrs[0] as ColumnAttribute;
if (attr.IsPK == true)
{
keyName = attr.ColumnName;//如果设置了主键，可以取到，没有就返回""
}
}
}
}
return keyName;
}

//字段名的序列 用,连接 如f1,f2,f3,f4
internal string GetFields<T>()
{
List<string> lst = new List<string>();
Type type = typeof(T);
//取到实例的属性
PropertyInfo[] props = type.GetProperties();
if (props != null)
{
foreach (PropertyInfo pi in props)
{
//获取属性的标记 取字段信息
object[] colAttrs = pi.GetCustomAttributes(typeof(ColumnAttribute), true);
if (colAttrs != null && colAttrs.Length > 0)
{
ColumnAttribute attr = colAttrs[0] as ColumnAttribute;
lst.Add(attr.ColumnName);
}
}
}
//将字段名称用','连接
return lst.Aggregate<string>((curr, next) => string.Format("{0},{1}", curr, next));
}
}

 

/// <summary>
/// 生成实体类
/// </summary>
public class EntityHelper
{
private ISqlHelper helper = null;
private DataBaseEnum dbenum;

/// <summary>
/// 需要知道数据库类型和连接字符串
/// </summary>
public EntityHelper(DataBaseEnum dbe, string connStr)
{
dbenum = dbe;
if (dbenum.Equals(DataBaseEnum.Oracle))
{
helper = new OracleSqlHelper(connStr);
}
if (dbenum.Equals(DataBaseEnum.SqlServer))
{
helper = new MSSQLSqlHelper(connStr);
}
if (dbenum.Equals(DataBaseEnum.DB2))
{
helper = new DB2SqlHelper(connStr);
}
}

/// <summary>
/// 生成实体字符串
/// </summary>
public string CreateEntity(string tableName, string spaceName)
{
string str = "";
string nameSpace = "ABC.TEST";
if (!string.IsNullOrEmpty(spaceName))
{
nameSpace = spaceName;
}
tableName = tableName.Trim().ToUpper();
if (!string.IsNullOrEmpty(tableName))
{
List<ColumnInfo> lst = GetColumn(tableName);
if (lst != null)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("using System;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using System.Data;");
sb.AppendLine("using MyORM;");
sb.AppendLine(null);//空一行
sb.AppendLine(string.Format("namespace {0}", nameSpace));
sb.AppendLine("{");
sb.AppendLine(string.Format("[TableAttribute(\"{0}\")]", tableName));
sb.AppendLine(string.Format("public class {0}Entity : MyBaseClass", ForMatName(tableName)));
sb.AppendLine("{");

//属性序列
sb.AppendLine(lst.Select(en => string.Format("///<summary>\r\n///{0}\r\n///</summary>\r\n[ColumnAttribute(\"{1}\", DbType.{2} {3})]\r\npublic {4} {5}\r\n{6} get; set; {7}", en.Note, en.ColumnName, GetDbType(en.ColumnType), en.PkInfo.Equals("Y") ? ", IsPK = true" : "", GetOrmType(en.ColumnType), ForMatName(en.ColumnName), "{", "}")).Aggregate((x, y) => x + "\r\n" + y));

sb.AppendLine("}");
sb.AppendLine("}");
str = sb.ToString();
}
}
return str;
}

/// <summary>
/// 生成实体类 fullFileName如c:\123.cs
/// </summary>
public void CreateEntity(string tableName, string spaceName, string fullFileName)
{
if (!string.IsNullOrEmpty(fullFileName))
{
string es = CreateEntity(tableName, spaceName);
try
{
//先判断文件目录是否存在
int index = fullFileName.LastIndexOf('\\');
if (index - 1 > 0)
{
if (Directory.Exists(fullFileName.Substring(0, index)))
{
using (FileStream ins = new FileStream(fullFileName, FileMode.Create))
{
byte[] bts = Encoding.UTF8.GetBytes(es);
ins.Write(bts, 0, bts.Length);
}
}
}
}
catch (Exception ex)
{ }
}
}

/// <summary>
/// 将字符串用-分割，首字母大写；如： hello_world->HelloWorld
/// </summary>
private string ForMatName(string name)
{
string rtn = name.Split('_').Where(a => !string.IsNullOrEmpty(a)).Select(b => (b.Substring(0, 1).ToUpper() + b.Substring(1).ToLower())).Aggregate((x, y) => x + y);
return rtn;
}

private string GetSql(string tableName)
{
string sql = "";
if (!string.IsNullOrEmpty(tableName))
{
if (dbenum.Equals(DataBaseEnum.Oracle))
{
sql = @"SELECT T.COLUMN_NAME,
T.DATA_TYPE,
A.COMMENTS NOTE,
DECODE(C.CONSTRAINT_TYPE, 'P', 'Y', 'N') PK_INFO
FROM USER_TAB_COLS T
LEFT JOIN USER_COL_COMMENTS A
ON T.TABLE_NAME = A.TABLE_NAME
AND T.COLUMN_NAME = A.COLUMN_NAME
LEFT JOIN (SELECT UC.TABLE_NAME,
UC.CONSTRAINT_NAME,
UC.COLUMN_NAME,
U.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS UC
INNER JOIN USER_CONSTRAINTS U
ON UC.TABLE_NAME = U.TABLE_NAME
AND UC.CONSTRAINT_NAME = U.CONSTRAINT_NAME
AND U.CONSTRAINT_TYPE = 'P') UC
ON T.TABLE_NAME = UC.TABLE_NAME
AND T.COLUMN_NAME = UC.COLUMN_NAME
LEFT JOIN USER_CONSTRAINTS C
ON UC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE UPPER(T.TABLE_NAME) = '" + tableName.Trim().ToUpper() + "'";
}
if (dbenum.Equals(DataBaseEnum.SqlServer))
{
sql = @"SELECT TAB.NAME,COL.NAME AS COLUMN_NAME,TP.NAME AS DATA_TYPE,ISNULL (DS.VALUE, '') AS NOTE,CASE WHEN EXISTS (SELECT 1 FROM SYSOBJECTS WHERE XTYPE = 'PK' AND PARENT_OBJ = COL.ID AND NAME IN (SELECT NAME FROM SYSINDEXES WHERE INDID IN (SELECT INDID FROM SYSINDEXKEYS WHERE ID = COL.ID AND COLID = COL.COLID))) THEN 'Y' ELSE 'N' END AS PK_INFO FROM SYSOBJECTS TAB LEFT JOIN SYSCOLUMNS COL ON TAB.ID = COL.ID LEFT JOIN SYSTYPES TP ON COL.XUSERTYPE = TP.XUSERTYPE LEFT JOIN SYS.EXTENDED_PROPERTIES DS ON COL.ID = DS.MAJOR_ID AND COL.COLID = DS.MINOR_ID WHERE TAB.XTYPE = 'U' AND TAB.NAME = '" + tableName.Trim().ToUpper() + "'";
}
if (dbenum.Equals(DataBaseEnum.DB2))
{
sql = @"SELECT d.name tbName,
COALESCE(d.remarks, '') tbDesc,
a.name COLUMN_NAME,
a.coltype DATA_TYPE,
decode(a.nulls, 'Y', '1', '0') NOT_NULL,
decode(a.identity, 'Y', '1', '0') auto,
a.longlength width,
a.scale precision,
a.remarks NOTE,
decode(n.unique_colcount, '1', '1', '0') unique,
decode(n.uniquerule, 'P', 'Y', 'N') PK_INFO,
COALESCE(n.name, '') indexName
FROM sysibm.syscolumns a
INNER JOIN sysibm.systables d
on a.tbname = d.name
LEFT JOIN sysibm.sysindexes n
on n.tbname = d.name
and SUBSTR(colnames, 2) = a.name
where d.name = '" + tableName + "'";
}
}
return sql;
}

/// <summary>
/// 将查出的表结构信息组装成实体;COLUMN_NAME DATA_TYPE NOTE PK_INFO
/// </summary>
private List<ColumnInfo> GetColumn(string tableName)
{
List<ColumnInfo> lst = null;
string sql = GetSql(tableName);
if (!string.IsNullOrEmpty(sql))
{
DataTable dt = helper.Select(sql, null);
if (dt != null)
{
lst = new List<ColumnInfo>();
ColumnInfo ci = null;
foreach (DataRow dr in dt.Rows)
{
ci = new ColumnInfo();
ci.ColumnName = dr["COLUMN_NAME"].ToString().ToUpper();
ci.ColumnType = GetEnType(dr["DATA_TYPE"].ToString());
ci.Note = dr["NOTE"].ToString();
ci.PkInfo = dr["PK_INFO"].ToString();
lst.Add(ci);
}
}
}
return lst;
}

/// <summary>
/// C#基础类型转换为DbType对应类型
/// </summary>
private string GetDbType(string type)
{
string dbtype = "";
if (type.Equals("string"))
{
dbtype = "AnsiString";
}
else if (type.Equals("byte[]"))
{
dbtype = "Binary";
}
else
{
dbtype = type;
}
return dbtype;
}

/// <summary>
/// 将c#基础数据类型转成实体数据类型：除引用类型以外的数字日期等转为可空类型
/// </summary>
private string GetOrmType(string type)
{
string ormtype = "";
if (type.Equals("string") || type.Equals("byte[]"))
{
ormtype = type; //string和数组是引用类型
}
else
{
ormtype = type + "?"; //数字日期等转为可空类型
}
return ormtype;
}


/// <summary>
/// 将数据库基本数据类型转为C#数据类型
/// </summary>
private string GetEnType(string dataType)
{
string type = "";
//基础类型string Int32 Decimal DateTime
switch (dataType.Trim())
{
//1、oracle
case "CHAR": type = "string"; break;
case "NCHAR": type = "string"; break;
case "VARCHAR": type = "string"; break;
case "VARCHAR2": type = "string"; break;
case "NVARCHAR2": type = "string"; break;
case "NUMBER": type = "Decimal"; break;
case "INTEGER": type = "Int32"; break;
case "BINARY_FLOAT": type = "Decimal"; break;
case "BINARY_DOUBLE": type = "Decimal"; break;
case "DATE": type = "DateTime"; break;
case "TIMESTAMP": type = "DateTime"; break;
case "FLOAT": type = "Decimal"; break;
case "CLOB": type = "string"; break;
case "NCLOB": type = "string"; break;
case "LONG": type = "string"; break; //oracle LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB

//2、sqlserver
case "bit": type = "Boolean"; break;
case "tinyint": type = "Byte"; break;
case "smallint": type = "Int16"; break;
case "int": type = "Int64"; break;
case "bigint": type = "Int64"; break;
case "real": type = "Decimal"; break;
case "float": type = "Decimal"; break;
case "money": type = "Decimal"; break;
case "numeric": type = "Decimal"; break;
case "datetime": type = "DateTime"; break;
case "date": type = "DateTime"; break;
case "char": type = "string"; break;
case "varchar": type = "string"; break;
case "nchar": type = "string"; break;
case "nvarchar": type = "string"; break;
case "text": type = "string"; break;
case "ntext": type = "string"; break;
case "image": type = "string"; break;
case "binary": type = "string"; break;
//case "uniqueidentifier": type = "Guid"; break; //改为string?

//3.DB2
case "ARRAY": type = "string"; break;
case "BLOB": type = "byte[]"; break;
case "DBCLOB": type = "string"; break;
case "BOOLEAN": type = "string"; break;
case "VARGRAPHIC": type = "string"; break;
case "VARBINARY": type = "string"; break;
case "LONG VARGRAPHIC": type = "string"; break;
case "LONG VARCHAR": type = "string"; break;
case "GRAPHIC": type = "string"; break;
case "TIME": type = "DateTime"; break;
case "BIGINT": type = "Int32"; break;
case "BINARY LARGE OBJECT": type = "string"; break;
case "CHARACTER": type = "string"; break;
case "CHARACTER LARGE OBJECT": type = "string"; break;
case "CHARACTER VARYING": type = "string"; break;
case "DECFLOAT": type = "Decimal"; break;
case "DECIMAL": type = "Decimal"; break;
case "DOUBLE PRECISION": type = "Decimal"; break;
case "REAL": type = "Decimal"; break;
case "SMALLINT": type = "Int32"; break;
case "USER-DEFINED": type = "string"; break;
case "XML": type = "string"; break;
case "TIMESTMP": type = "DateTime"; break;
//case "INTEGER": type = "Int32"; break;
//case "TIMESTAMP": type = "DateTime"; break;
}
return type;
}

View Code

 

下载代码 需要解压密码发邮件至519409748@qq.com

转载于:https://www.cnblogs.com/cy2011/p/6374687.html