這僅僅是一個新手寫給新手共同入門的博文！這是一個使用MVC，和一些主流框架（Autofac,Log4Net等）來完成的一個簡單的項目。和各位學習MVC的朋友們一起學習。
如左圖所示，先建好文件夾，然后再來填充內容。
一：Zero,MVC4.0項目
二：Domain: Abstract倉儲類接口，Concrete倉儲類實現，Entities實體模型
三：Infrastructure：基礎設施
四：IOC：主要用于解耦倉儲類接口
首先，從最底層寫起：
第一步，寫一個操作數據庫的類
我這邊采用的是底層使用ADO.NET，通過泛型約束和反射來實現一個簡單的ORM
1：先在Conntion里面放一個SQLHelper類（找個自己熟悉的），這里就不做工廠了。怎么簡單怎么來，先讓東西跑起來
2：寫個屬于自己的ORM類：我們先來想想，如果不考慮存儲過程（返回集合，直接LINQ處理數據），我們需要什么東西來拼接SQL語句，首先，要有表名，然后主鍵，然后各個字段名稱，其他的先不考慮，現在寫一個IDateBase抽象類來約束實體類，往Infrastructure層的IBase文件夾里新建一個IDateBase抽象類，代碼如下：
namespace Zero.Infrastructure.IBase{ public abstract class IDataBase { public virtual string TableName { get; set; } public virtual int ID { get; set; } }}
這樣就解決了表名和主鍵名稱在用泛型的時候，取不到的問題了，但是字段名稱不行啊，每個表的字段都不一樣，所以最后還是要用到反射，寫一個特性來反射，在Infrastructure層的Attributes文件夾下面建立一個類DataFieldAttribute
代碼如下：
namespace Zero.Infrastructure.Attributes{ public class DataFieldAttribute : Attribute { PRivate string _FieldName; public DataFieldAttribute(string fieldname) { this._FieldName = fieldname; } public string FieldName { get { return this._FieldName; } set { this._FieldName = value; } } }}
完事具備，我們先寫一個實體類，然后開始寫ORM
實體里建立在Domain的Entities文件夾下面，記得Domain層要引用Infrastructure層
using System;using System.Collections.Generic;using System.Linq;using System.Text;using Zero.Infrastructure.Attributes;using Zero.Infrastructure.IBase;namespace Zero.Domain.Entities{ public class User: IDataBase { public User() { TableName = "User"; } private string _TableName; public override string TableName { get { if (_TableName == null) { return "User"; } else { return _TableName; } } set { _TableName = value; } } public override int ID { get; set; } [DataFieldAttribute("UserName")] public string UserName { get; set; } }}
代碼如上
現在開始寫自己的ORM類
在Infrastructure層的Conntion文件夾下面建立一個ZeroORM類，ORM需要實現的功能有：查詢，添加，更改，刪除，4個基本功能
public class ZeroORMwhere<T> where T : IBase.IDataBase{ public string SqlConnctionString { get; set; } public SqlConnection conn { get; set; } public SqlTransaction tran { get; set; }}
然后增加查詢方法：
/// <summary> /// 獲得實體T所有數據 /// </summary> /// <returns></returns> public List<T> Select(T t) { List<T> list = new List<T>(); string sql = "select * from " + t.TableName + " WITH (NOLOCK) order by id desc"; DataSet ds = SqlHelper.ExecuteDataset(SqlConnctionString, CommandType.Text, sql); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { list.Add(DataSetToEntity.DsToEntity<T>(ds, i)); } return list; }
DataSetToEntity這個類就是網上找的Dataset轉實體的方法，大家可以網上找下，最后返回一個List集合
然后是添加方法
/// <summary> /// 插入新數據 /// </summary> /// <param name="t">實體類</param> /// <returns></returns> public int Insert(T t) { try { Type mytype = t.GetType(); // 獲取類的所有公共屬性 System.Reflection.PropertyInfo[] pInfo = mytype.GetProperties(); string FieldName = "";//字段名稱 string Values = "";//值 StringBuilder sql = new StringBuilder(); List<SqlParameter> paras = new List<SqlParameter>();//不定參集合，防注入 sql.Append("Insert into "); sql.Append(mytype.Name);//數據庫表名，可以放t.TableName sql.Append("("); object[] objDataFieldAttribute = null; foreach (System.Reflection.PropertyInfo pio in pInfo) { objDataFieldAttribute = pio.GetCustomAttributes(typeof(DataFieldAttribute), false); if (objDataFieldAttribute != null && objDataFieldAttribute.Length > 0) { FieldName += ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + ",";//給字段賦值 Values += "@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + ",";//給對應字段的值賦值 paras.Add(new SqlParameter("@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName, pio.GetValue(t, null)));//添加不定參 } } FieldName = FieldName.TrimEnd(','); Values = Values.TrimEnd(','); sql.Append(FieldName); sql.Append(") VAlUES ("); sql.Append(Values); sql.Append(")"); int i = SqlHelper.ExecuteNonQuery(SqlConnctionString, CommandType.Text, sql.ToString(), paras.ToArray()); return i; } catch (Exception) { return -1; throw; } }
還有修改和刪除方法，以及帶事務的方法，一起貼出來。
using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Reflection;using System.Text;using Zero.Infrastructure.Attributes;using Zero.Infrastructure.Utilities;namespace Zero.Infrastructure.Conntion{ public class ZeroORM<T> where T : IBase.IDataBase { public string SqlConnctionString { get; set; } public SqlConnection conn { get; set; } public SqlTransaction tran { get; set; } /// <summary> /// 獲得實體T所有數據 /// </summary> /// <returns></returns> public List<T> Select(T t) { List<T> list = new List<T>(); string sql = "select * from [" + t.TableName + "] WITH (NOLOCK) order by id desc"; //出于性能考慮，不用反射來獲取表名 DataSet ds = SqlHelper.ExecuteDataset(SqlConnctionString, CommandType.Text, sql); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { list.Add(DataSetToEntity.DsToEntity<T>(ds, i)); } return list; } /// <summary> /// 根據主鍵ID獲取數據（一條） /// </summary> /// <param name="id"></param> /// <param name="TableName"></param> /// <returns></returns> public T SelectByID(int id, T t) { string sql = "select * from [" + t.TableName + "] WITH (NOLOCK) where ID=@ID"; //出于性能考慮，不用反射來獲取表名 DataSet ds = SqlHelper.ExecuteDataset(SqlConnctionString, CommandType.Text, sql, new SqlParameter("@ID", id)); t = DataSetToEntity.DsToEntity<T>(ds, 0); PropertyInfo[] prop = t.GetType().GetProperties(); return t; } /// <summary> /// 插入新數據 /// </summary> /// <param name="t">實體類</param> /// <returns></returns> public int Insert(T t) { try { Type mytype = t.GetType(); // 獲取類的所有公共屬性 System.Reflection.PropertyInfo[] pInfo = mytype.GetProperties(); string FieldName = "";//字段名稱 string Values = "";//值 StringBuilder sql = new StringBuilder(); List<SqlParameter> paras = new List<SqlParameter>(); sql.Append("Insert into ["); sql.Append(mytype.Name);//數據庫表名 sql.Append("]("); object[] objDataFieldAttribute = null; foreach (System.Reflection.PropertyInfo pio in pInfo) { objDataFieldAttribute = pio.GetCustomAttributes(typeof(DataFieldAttribute), false); if (objDataFieldAttribute != null && objDataFieldAttribute.Length > 0) { FieldName += ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + ","; Values += "@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + ","; paras.Add(new SqlParameter("@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName, pio.GetValue(t, null))); } } FieldName = FieldName.TrimEnd(','); Values = Values.TrimEnd(','); sql.Append(FieldName); sql.Append(") VAlUES ("); sql.Append(Values); sql.Append(")"); int i = SqlHelper.ExecuteNonQuery(SqlConnctionString, CommandType.Text, sql.ToString(), paras.ToArray()); return i; } catch (Exception) { return -1; throw; } } /// <summary> /// 更新數據 /// </summary> /// <param name="t">需更新的實體類</param> /// <returns></returns> public int Update(T t) { try { int i = 0; int primarykey = t.ID; T oldT = t; Type mytype = t.GetType(); System.Reflection.PropertyInfo[] pInfo = mytype.GetProperties(); oldT = SelectByID(primarykey, oldT);//獲得原始值，為日志做準備 if (t != oldT) { string SetValue = "";//字段名稱 string Where = " where ID=@ID";//值 StringBuilder sql = new StringBuilder(); List<SqlParameter> paras = new List<SqlParameter>(); sql.Append("Update ["); sql.Append(mytype.Name); sql.Append("] Set "); object[] objDataFieldAttribute = null; foreach (System.Reflection.PropertyInfo pio in pInfo) { objDataFieldAttribute = pio.GetCustomAttributes(typeof(DataFieldAttribute), false); if (objDataFieldAttribute != null && objDataFieldAttribute.Length > 0 && pio.GetValue(t, null).ToString() != pio.GetValue(oldT, null).ToString()) { SetValue += ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + "=" + "@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + ","; paras.Add(new SqlParameter("@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName, pio.GetValue(t, null))); } } SetValue = SetValue.TrimEnd(','); sql.Append(SetValue); sql.Append(Where); paras.Add(new SqlParameter("@ID", primarykey)); i = SqlHelper.ExecuteNonQuery(SqlConnctionString, CommandType.Text, sql.ToString(), paras.ToArray()); return i; } else { return -2; } } catch (Exception) { return -1; throw; } } /// <summary> /// 刪除數據 /// </summary> /// <param name="t"></param> /// <returns></returns> public int Delete(T t) { int i = 0; int primarykey = t.ID; Type mytype = t.GetType(); string TableName = mytype.Name; string Where = " where ID =@ID"; string sql = "DELETE FROM " + TableName + Where; try { i = SqlHelper.ExecuteNonQuery(SqlConnctionString, CommandType.Text, sql, new SqlParameter("@ID", primarykey)); return i; } catch (Exception) { return -1; throw; } } /// <summary> /// 開始事務 /// </summary> /// <returns></returns> public void BeginTran() { try { conn = new SqlConnection(SqlConnctionString); conn.Open(); tran = conn.BeginTransaction(); } catch (Exception) { tran.Rollback(); conn.Close(); throw; } } /// <summary> /// 帶事務的插入方法 /// </summary> /// <param name="t"></param> /// <returns></returns> public int InsertByTran(T t) { try { Type mytype = t.GetType(); // 獲取類的所有公共屬性 System.Reflection.PropertyInfo[] pInfo = mytype.GetProperties(); string FieldName = "";//字段名稱 string Values = "";//值 StringBuilder sql = new StringBuilder(); List<SqlParameter> paras = new List<SqlParameter>(); sql.Append("Insert into ["); sql.Append(mytype.Name);//數據庫表名 sql.Append("]("); object[] objDataFieldAttribute = null; foreach (System.Reflection.PropertyInfo pio in pInfo) { objDataFieldAttribute = pio.GetCustomAttributes(typeof(DataFieldAttribute), false); if (objDataFieldAttribute != null && objDataFieldAttribute.Length > 0) { FieldName += ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + ","; Values += "@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + ","; paras.Add(new SqlParameter("@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName, pio.GetValue(t, null))); } } FieldName = FieldName.TrimEnd(','); Values = Values.TrimEnd(','); sql.Append(FieldName); sql.Append(") VAlUES ("); sql.Append(Values); sql.Append(")"); int i = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql.ToString(), paras.ToArray()); return i; } catch (Exception) { tran.Rollback(); return -1; throw; } } /// <summary> /// 帶事務的更新方法 /// </summary> /// <param name="t"></param> /// <returns></returns> public int UpdateByTran(T t) { try { int i = 0; int primarykey = t.ID; T oldT = t; Type mytype = t.GetType(); System.Reflection.PropertyInfo[] pInfo = mytype.GetProperties(); oldT = SelectByID(primarykey, oldT);//獲得原始值，為日志做準備 if (t != oldT) { string SetValue = "";//字段名稱 string Where = " where ID=@ID";//值 StringBuilder sql = new StringBuilder(); List<SqlParameter> paras = new List<SqlParameter>(); sql.Append("Update ["); sql.Append(mytype.Name); sql.Append("] Set "); object[] objDataFieldAttribute = null; foreach (System.Reflection.PropertyInfo pio in pInfo) { objDataFieldAttribute = pio.GetCustomAttributes(typeof(DataFieldAttribute), false); if (objDataFieldAttribute != null && objDataFieldAttribute.Length > 0 && pio.GetValue(t, null).ToString() != pio.GetValue(oldT, null).ToString()) { SetValue += ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + "=" + "@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName + ","; paras.Add(new SqlParameter("@" + ((DataFieldAttribute)objDataFieldAttribute[0]).FieldName, pio.GetValue(t, null))); } } SetValue = SetValue.TrimEnd(','); sql.Append(SetValue); sql.Append(Where); paras.Add(new SqlParameter("@ID", primarykey)); i = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql.ToString(), paras.ToArray()); return i; } else { tran.Rollback(); return -2; } } catch (Exception) { tran.Rollback(); return -1; throw; } } /// <summary> /// 帶事務的刪除方法 /// </summary> /// <param name="t"></param> /// <returns></returns> public int DeleteByTran(T t) { int i = 0; int primarykey = t.ID; Type mytype = t.GetType(); string TableName = mytype.Name; string Where = "] where ID =@ID"; string sql = "DELETE FROM [" + TableName + Where; try { i = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql, new SqlParameter("@ID", primarykey)); return i; } catch (Exception) { tran.Rollback(); return -1; throw; } } /// <summary> /// 帶事務和條件的刪除方法 /// </summary> /// <param name="t"></param> /// <returns></returns> public int DeleteByTran(T t, string where) { if (where.IndexOf("1=1") > 0) { return -1; } int i = 0; string Where = "] where " + where; string sql = "DELETE FROM [" + t.TableName + Where; try { i = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql, null); return i; } catch (Exception) { tran.Rollback(); return -1; throw; } } /// <summary> /// 提交事務 /// </summary> /// <returns></returns> public void CommitTran() { try { tran.Commit(); conn.Close(); } catch (Exception) { tran.Rollback(); conn.Close(); throw; } finally { tran.Dispose(); conn.Dispose(); } } /// <summary> /// 回滾事務 /// </summary> public void RollBackTran() { try { tran.Rollback(); } catch (Exception) { throw; } } }}
好了，整個ORM就寫完了，這樣我們數據處理的類就寫完了！
三：Domain層
ORM寫完后，因為不同的表可能在不同的庫中，所以ZeroORM還不能直接拿來用，需要在上面隔離一層，我們這里選擇最簡單的方式，大牛勿噴。
首先寫一個Repository基類（這里就不抽象類了），代碼如下
using System;using System.Collections.Generic;using System.Linq;using System.Text;using Zero.Domain.Abstract;using Zero.Infrastructure.IBase;namespace Zero.Domain.Concrete{ public class ConcreteBase<T> where T : IDataBase { public Zero.Infrastructure.Conntion.ZeroORM<T> DbHelper; public ConcreteBase() { DbHelper = new Infrastructure.Conntion.ZeroORM<T>(); } public string SqlConnctionString { get { return DbHelper.SqlConnctionString; } set { DbHelper.SqlConnctionString = value; } } public List<T> GetAllList(T t) { List<T> ubiList = new List<T>(); ubiList = DbHelper.Select(t); return ubiList; } public bool Insert(T t) { int i = 0; i = DbHelper.Insert(t); return i > 0; } public bool Update(T t) { int i = 0; i = DbHelper.Update(t); return i > 0; } public bool Delete(T t) { int i = 0; i = DbHelper.Delete(t); return i > 0; } /// <summary> /// 帶事務的插入方法 /// </summary> /// <param name="t"></param> /// <returns></returns> public bool InsertByTran(T t) { if (DbHelper.tran == null) { DbHelper.BeginTran(); } int i = DbHelper.InsertByTran(t); return i > 0; } /// <summary> /// 帶事務的更新方法 /// </summary> /// <param name="t"></param> /// <returns></returns> public bool UpdateByTran(T t) { if (DbHelper.tran == null) { DbHelper.BeginTran(); } int i = DbHelper.UpdateByTran(t); return i > 0; } /// <summary> /// 帶事務的刪除方法 /// </summary> /// <param name="t"></param> /// <returns></returns> public bool DeleteByTran(T t) { if (DbHelper.tran == null) { DbHelper.BeginTran(); } int i = DbHelper.DeleteByTran(t); return i > 0; } /// <summary> /// 帶事務和條件的刪除方法 /// </summary> /// <param name="t"></param> /// <returns></returns> public bool DeleteByTran(T t, string where) { if (DbHelper.tran == null) { DbHelper.BeginTran(); } int i = DbHelper.DeleteByTran(t, where); return i > 0; } /// <summary> /// /// </summary> /// <param name="t"></param> /// <returns></returns> public void RollBackTran() { DbHelper.RollBackTran(); } /// <summary> /// /// </summary> /// <param name="t"></param> /// <returns></returns> public void BeginTran() { DbHelper.BeginTran(); } /// <summary> /// 提交事務 提交成功返回"";失敗返回錯誤信息 /// </summary> /// <returns>提交成功返回"";失敗返回錯誤信息</returns> public string CommitTran() { if (DbHelper.tran != null) { try { DbHelper.CommitTran(); return ""; } catch (Exception e) { return e.ToString(); throw; } } else { return "不存在可提交的事務"; } } /// <summary> /// 根據主鍵ID查詢結果，返回T /// </summary> /// <param name="id"></param> /// <returns></returns> public T SelectByID(string id, T t) { if (id != "") { return DbHelper.SelectByID(int.Parse(id), t); } else { return null; } } }}
然后去寫Repository類和IRepository接口
using System;using System.Collections.Generic;using System.Linq;using System.Text;using Zero.Domain.Entities;namespace Zero.Domain.Abstract{ public interface IUserRepository { User GetEntity(); IQueryable<User> Users { get; } }}
實現類：
using System;using System.Collections.Generic;using System.Configuration;using System.Linq;using System.Text;using Zero.Domain.Abstract;using Zero.Domain.Entities;namespace Zero.Domain.Concrete{ public class UserRepository : ConcreteBase<User>, IUserRepository { public User GetEntity() { return new User(); } public IQueryable<User> BaseTitleTypes { get { return GetAllList(GetEntity()).AsQueryable(); } } public UserRepository() : base() { SqlConnctionString = ConfigurationManager.ConnectionStrings["ZeroTest"].ConnectionString;//獲取連接字符串 } }}
然后去數據庫建表
鏈接字符串的位置寫的有點蠢，不過先這樣做吧，一切已快速為目的
現在，所有的準備工作都做完了，開始測試一下數據交互是否有問題！
在Zero項目中建一個Index控制器
然后右鍵Index添加視圖。先不做IOC解耦，引用Domain層和基礎設施層。
添加如下代碼
// // GET: /Index/ public ActionResult Index() { UserRepository us = new UserRepository(); bool b =us.Insert(new Domain.Entities.User { UserName = "Ambre" }); if (b) { var ListEntity = from o in us.Users select o; return Json(ListEntity, JsonRequestBehavior.AllowGet); } else { return Content(b.ToString()); } }
然后去改下路由設置
啟動項目
好了，完成！下篇文章將寫如何快速的將IOC應用到項目中，然后前端類似于EasyUi的grid控件如何編寫。
謝謝大家，喜歡的話，點個贊，這可是我的處女文呢！
新聞熱點
疑難解答
