这一篇是我写的第一篇博客,可能文笔比较差还望大家见谅!小弟还有6天就毕业了结束大学生活!打算写这么一篇算是给我3年的大学生活改个墓志铭一样的东西。
今天写的是一个我最近写的一个简易版的数据库映射工具(ORM),相信大家对于ORM工具已经有一定的了解了,现在网络上有几个比较主流的ORM工具如nhibernate和entity framework等。我是从nhibernate开始接触ORM的,对于其简单及方便的操作也不多说了。
我的设计还是比较简单的通过反射来实现其功能,比较耗性能,上次看见一个用领域驱动设计可以不使用反射就可以实现ORM功能。表示自己还是个小菜!
这个工具目前已经实现了插入,修改,删除和工具主键查找对象(查询方面目前还没有非常好的思路,不想使用拼接SQL的方法,感觉HQL用起来非常不错,不过现在不会移植过来,依然表示小菜!)。使用面向接口方式来实现多数据库的操作,目前有sqlserver和oracle。
下面是项目分层:
DALFactory层:用于生成一个IDBOperate(对于数据库操作的接口)对象。
DBModelAttribute层:自定义特征类用于数据库实体类。
DBModel层:数据库实体类。
DBUtility层:ado.net操作类库,存放SQLHelper和OracleHelper2个类。
IDAL层:数据库操作接口及其他接口。
OracleDAL层:oracle数据库的实际操作类库。
SQLServerDAL层:sqlserver数据库的实际操作类库。
考虑到反射比较耗性能所以在第一次访问的时候就生成几张表的sql语句放到字典中。
/// <summary> /// 用于存放各个表的SQL语句 /// </summary> public class SqlSentenceStorage : ISqlSentenceStorage { private static IDictionary<string, SqlSentenceData> _DataBaseIntegration = new Dictionary<string, SqlSentenceData>(); public static IDictionary<string, SqlSentenceData> DataBaseIntegration { get { if (_DataBaseIntegration == null || _DataBaseIntegration.Count == default(int)) { ChangDicData(); } return _DataBaseIntegration; } } private static void ChangDicData() { SqlSentenceStorage sqlSentenceStorage = new SqlSentenceStorage(); Assembly assembly = Assembly.Load("DBModel"); foreach (Type type in assembly.GetTypes()) { if (type.IsClass && type.IsSealed) { SqlSentenceData sqlData = new SqlSentenceData(); sqlData.InSertSql = sqlSentenceStorage.InSertSql(type); sqlData.UpDateSql = sqlSentenceStorage.UpDateSql(type); sqlData.DeleteSql = sqlSentenceStorage.DeleteSql(type); sqlData.GetSql = sqlSentenceStorage.GetSql(type); _DataBaseIntegration.Add(type.Name, sqlData); } } } #region ISqlSentenceStorage 成员 public string InSertSql(Type modeltype) { string inSertSql = "INSERT INTO {0}({1}) VALUES ({2})"; string tablename = ""; object[] members = modeltype.GetCustomAttributes(true); for (int i = 0; i < members.Length; i++) { if (members[i].GetType() == typeof(SqlTableAttribute)) { tablename = ((SqlTableAttribute)members[i]).TableName; } } PropertyInfo[] proInfos = modeltype.GetProperties(); //存放字段名 StringBuilder content1 = new StringBuilder(); //存放参数 StringBuilder content2 = new StringBuilder(); foreach (PropertyInfo info in proInfos) { foreach (Attribute ab in Attribute.GetCustomAttributes(info)) { if (ab.GetType() == typeof(SqlColumnAttribute)) { SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab); string name = sqlcolumn.ColumName; content1.Append(name + ","); content2.Append("@" + name + ","); } } } content1.Remove(content1.Length - 1, 1); content2.Remove(content2.Length - 1, 1); return string.Format(inSertSql, tablename, content1, content2); } public string DeleteSql(Type modeltype) { string inSertSql = "DELETE FROM {0} WHERE 1=1{1}"; string tablename = ""; object[] members = modeltype.GetCustomAttributes(true); for (int i = 0; i < members.Length; i++) { if (members[i].GetType() == typeof(SqlTableAttribute)) { tablename = ((SqlTableAttribute)members[i]).TableName; } } PropertyInfo[] proInfos = modeltype.GetProperties(); //条件 StringBuilder content1 = new StringBuilder(); foreach (PropertyInfo info in proInfos) { foreach (Attribute ab in Attribute.GetCustomAttributes(info)) { if (ab.GetType() == typeof(SqlColumnAttribute)) { SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab); if (sqlcolumn.IsPrimaryKey) { content1.Append(" and " + sqlcolumn.ColumName + "=@" + sqlcolumn.ColumName); } } } } return string.Format(inSertSql, tablename, content1); } public string UpDateSql(Type modeltype) { string inSertSql = "UPDATE {0} SET {1} WHERE 1=1{2}"; string tablename = ""; object[] members = modeltype.GetCustomAttributes(true); for (int i = 0; i < members.Length; i++) { if (members[i].GetType() == typeof(SqlTableAttribute)) { tablename = ((SqlTableAttribute)members[i]).TableName; } } PropertyInfo[] proInfos = modeltype.GetProperties(); StringBuilder content1 = new StringBuilder(); StringBuilder content2 = new StringBuilder(); foreach (PropertyInfo info in proInfos) { foreach (Attribute ab in Attribute.GetCustomAttributes(info)) { if (ab.GetType() == typeof(SqlColumnAttribute)) { SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab); if (sqlcolumn.IsPrimaryKey) { content2.Append(" and " + sqlcolumn.ColumName + "=@" + sqlcolumn.ColumName); } else { content1.Append(sqlcolumn.ColumName + "=@" + sqlcolumn.ColumName + ","); } } } } content1.Remove(content1.Length - 1, 1); return string.Format(inSertSql, tablename, content1, content2); } public string GetSql(Type modeltype) { string inSertSql = "SELECT {0} FROM {1} WHERE 1=1{2}"; string tablename = ""; object[] members = modeltype.GetCustomAttributes(true); for (int i = 0; i < members.Length; i++) { if (members[i].GetType() == typeof(SqlTableAttribute)) { tablename = ((SqlTableAttribute)members[i]).TableName; } } PropertyInfo[] proInfos = modeltype.GetProperties(); StringBuilder content1 = new StringBuilder(); StringBuilder content2 = new StringBuilder(); foreach (PropertyInfo info in proInfos) { foreach (Attribute ab in Attribute.GetCustomAttributes(info)) { if (ab.GetType() == typeof(SqlColumnAttribute)) { SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab); content1.Append(sqlcolumn.ColumName + ","); if (sqlcolumn.IsPrimaryKey) { content2.Append(" and " + sqlcolumn.ColumName + "=@" + sqlcolumn.ColumName); } } } } content1.Remove(content1.Length - 1, 1); return string.Format(inSertSql, content1, tablename, content2); } #endregion } public class SqlSentenceData { public virtual string InSertSql { get; set; } public virtual string DeleteSql { get; set; } public virtual string UpDateSql { get; set; } public virtual string GetSql { get; set; } }
然后在下面要使用时就可以很简单的直接查找出来
private string GetSqlStr(string name, SqlMethod method) { switch (method) { case SqlMethod.Delete: return SqlSentenceStorage.DataBaseIntegration[name].DeleteSql; case SqlMethod.InSert: return SqlSentenceStorage.DataBaseIntegration[name].InSertSql; case SqlMethod.UpData: return SqlSentenceStorage.DataBaseIntegration[name].UpDateSql; case SqlMethod.Get: return SqlSentenceStorage.DataBaseIntegration[name].GetSql; } return string.Empty; }
接下来是最主要的部分,SQLDBOperate类基础数据库操作接口(IDBOperate)已实现对sqlserver的操作。
public class SQLDBOperate : IDBOperate { #region IDBOperate 成员 public bool InSert(object model) { SqlParameter[] paramList = GetParams(model); string cmdtxt = GetSqlStr(model.GetType().Name, SqlMethod.InSert); int count = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionStringLocal, CommandType.Text, cmdtxt, paramList); if (count > default(int)) { return true; } else { return false; } } public bool UpDate(object model) { SqlParameter[] paramList = GetParams(model); string cmdtxt = GetSqlStr(model.GetType().Name, SqlMethod.UpData); int count = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionStringLocal, CommandType.Text, cmdtxt, paramList); if (count > default(int)) { return true; } else { return false; } } /// <summary> /// 根据主键来删除数据 /// </summary> /// <typeparam name="TModel"></typeparam> /// <param name="model"></param> /// <returns></returns> public bool Delete(object model) { SqlParameter paramList = null; PropertyInfo[] proInfos = model.GetType().GetProperties(); foreach (PropertyInfo info in proInfos) { foreach (Attribute ab in Attribute.GetCustomAttributes(info)) { if (ab.GetType() == typeof(SqlColumnAttribute)) { SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab); if (sqlcolumn.IsPrimaryKey) { paramList = new SqlParameter("@" + sqlcolumn.ColumName, info.GetValue(model, null)); break; } } } } string cmdtxt = GetSqlStr(model.GetType().Name, SqlMethod.Delete); int count = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionStringLocal, CommandType.Text, cmdtxt, paramList); if (count > default(int)) { return true; } else { return false; } } public TModel Get<TModel>(object keyID) where TModel : new() { Type modeltype = typeof(TModel); string cmdtxt = GetSqlStr(modeltype.Name, SqlMethod.Get); SqlParameter paramList = GetParamsKey(modeltype, keyID); SqlDataReader sdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionStringLocal, CommandType.Text, cmdtxt, paramList); if (sdr.Read()) { #region 读取数据插入实体对象中 TModel model = new TModel(); PropertyInfo[] proInfos = modeltype.GetProperties(); int i = 0; foreach (PropertyInfo info in proInfos) { info.SetValue(model, GetsdrValue(info.PropertyType, ref sdr, i), null); i++; } #endregion sdr.Dispose(); sdr.Close(); return model; } sdr.Dispose(); sdr.Close(); return default(TModel); } #endregion #region 内定方法用于操作一些公共方法 /// <summary> /// 根据类型来读取SqlDataReader中的值 /// </summary> /// <param name="type"></param> /// <param name="sdr"></param> /// <param name="i"></param> /// <returns></returns> private object GetsdrValue(Type type, ref SqlDataReader sdr, int i) { if (type == typeof(int)) { return sdr.GetInt32(i); } else if (type == typeof(string)) { return sdr.GetString(i); } else if (type == typeof(double)) { return sdr.GetDouble(i); } else if (type == typeof(DateTime)) { return sdr.GetDateTime(i); } else if (type == typeof(Decimal)) { return sdr.GetDecimal(i); } else if (type == typeof(Boolean)) { return sdr.GetBoolean(i); } else if (type == typeof(float)) { return sdr.GetFloat(i); } return null; } /// <summary> /// 获取参数和值 /// </summary> /// <typeparam name="TModel"></typeparam> /// <param name="model"></param> /// <returns></returns> private SqlParameter[] GetParams(Object model) { PropertyInfo[] proInfos = model.GetType().GetProperties(); IDictionary<string, object> dataList = new Dictionary<string, object>(); foreach (PropertyInfo info in proInfos) { foreach (Attribute ab in Attribute.GetCustomAttributes(info)) { if (ab.GetType() == typeof(SqlColumnAttribute)) { SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab); string name = sqlcolumn.ColumName; object value = info.GetValue(model, null); dataList.Add(name, value); } } } SqlParameter[] paramList = new SqlParameter[dataList.Count]; int i = 0; foreach (string key in dataList.Keys) { SqlParameter item = new SqlParameter("@" + key, dataList[key]); paramList[i] = item; i++; } return paramList; } /// <summary> /// 获取主键的参数和值 /// </summary> /// <typeparam name="TModel"></typeparam> /// <param name="model"></param> /// <returns></returns> private SqlParameter GetParamsKey(Type model, object keyID) { SqlParameter paramitem = null; PropertyInfo[] proInfos = model.GetProperties(); foreach (PropertyInfo info in proInfos) { foreach (Attribute ab in Attribute.GetCustomAttributes(info)) { if (ab.GetType() == typeof(SqlColumnAttribute)) { SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab); if (sqlcolumn.IsPrimaryKey) paramitem = new SqlParameter("@" + sqlcolumn.ColumName, keyID); } } } return paramitem; } }
接下来我写了个测试数据表,数据库里需要有一个表与它对应。
[SqlTable("T_User")] public sealed class UserInfo { [SqlColumn("User_Id",true,true)] public int Id { get; set; } [SqlColumn("User_Name",true)] public string Name { get; set; } [SqlColumn("Pass_Word", true)] public string PassWord { get; set; } [SqlColumn("LastDateTime")] public DateTime LastDateTime { get; set; } }
SqlTableAttribute特征类用于表名的存放。
[Serializable] public class SqlTableAttribute : Attribute { public SqlTableAttribute(string name) { this.TableName = name; } public string TableName { get; set; } }
SqlColumnAttribute用于字段的存放。
[Serializable] public class SqlColumnAttribute:Attribute { public SqlColumnAttribute(string name) { this.ColumName = name; this.IsPrimaryKey = false; this.IsNotNull = false; } public SqlColumnAttribute(string name,bool notNull) { this.ColumName = name; this.IsPrimaryKey = false; this.IsNotNull = notNull; } public SqlColumnAttribute(string name, bool notNull,bool iskey) { this.ColumName = name; this.IsPrimaryKey = iskey; this.IsNotNull = notNull; } /// <summary> /// 是否为空 /// </summary> public bool IsNotNull { get; set; } /// <summary> /// 列名 /// </summary> public string ColumName { get; set; } /// <summary> /// 是否为主键 /// </summary> public bool IsPrimaryKey { get; set; } }
好了!到这一步已经基本完工了,我们来测试一下:
IDBOperate opens = DataAccess.CreateDBOperate(); UserInfo user = new UserInfo(); user.Id = 2; user.LastDateTime = DateTime.Now; user.Name = "hj"; user.PassWord = "str"; opens.InSert(user);
数据库插入成功。
oracle数据库现在还没写,不过基本和sqlserver差不多。
我写的不太清楚,还望见谅,以下提供源码下载,说在多也是无用,运行以下就清楚了!
源码下载https://files.cnblogs.com/hangjian/MyORM.zip