Mikel
上篇文章（深入浅出三层架构）分析了简单三层架构的实现。包括Model,DAL（数据访问层）,BLL（业务逻辑层）的实现。
实际开发中，由于重复代码的操作，会花费大量时间，如果以代码生成器来自动生成三层架构代码，即节省精力，又可以节省大量的时间来做其他业务逻辑的代码，提高开发效率。
常用的代码生成器有：动软，CodeSmith 等。
简单代码生成器的基本功能描述：
一键生成Model，DAL，BLL，包括对应数据库中表的Model的自动生成，包括生成属性、添加、修改、删除、查询。
界面展示：
生成器开发技术要点：
- 查询系统视图：INFORMATION_SCHEMA.TABLES、 INFORMATION_SCHEMA.COLUMNS 可以获得数据库中表、列的相关信息。
- 字符串的拼接：StringBuilder的使用，其AppendLine()会自动换行。
- 将字符串写入文本文件：File.WriteAllText()
- 为了降低开发难度，先假设条件多一些，如表的主键都为Id,且自动增长，之后再逐步完善
关键代码：
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.IO; namespace CodeGenerator { public partial class Form1 : Form { public Form1() { InitializeComponent(); } /// <summary> /// 执行ExecuteDataTable()，得到DataTable /// </summary> /// <param name="cmdText"></param> /// <param name="parameters"></param> /// <returns></returns> public DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters) { using (SqlConnection conn=new SqlConnection(txtConnStr.Text)) { conn.Open(); using(SqlCommand cmd=conn.CreateCommand()) { cmd.CommandText = cmdText; cmd.Parameters.AddRange(parameters); using (SqlDataAdapter adapter=new SqlDataAdapter (cmd)) { DataTable dt = new DataTable(); adapter.Fill(dt); return dt; } } } } private void Form1_Load(object sender, EventArgs e) { txtConnStr.Text = @"Data Source=EYES\SQLEXPRESS;Initial Catalog=SanCeng;Integrated Security=True"; } private void btnConnStr_Click(object sender, EventArgs e) { //清空 clbTables.Items.Clear(); //查询系统试图 string sql = "select * from INFORMATION_SCHEMA.TABLES"; DataTable dt = ExecuteDataTable(sql); //根据系统视图取得TABLE_NAME foreach (DataRow row in dt.Rows) { string tablename = Convert.ToString(row["TABLE_NAME"]); clbTables.Items.Add(tablename); } } private void btnGo_Click(object sender, EventArgs e) { //连接字符串 //方法AppendLine()追加字符串且自动执行换行 foreach (string tableName in clbTables.CheckedItems) { string sql = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLE_NAME "; DataTable dt = ExecuteDataTable(sql,new SqlParameter("TABLE_NAME",tableName)); #region 生成Model CreatModel(tableName, dt); #endregion #region 生成DAL CreatDAL(tableName, dt); #endregion #region 生成BLL CreatBLL(tableName, dt); #endregion } } private static void CreatDAL(string tableName, DataTable dt) { 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 三层架构Demo.Model;"); sb.AppendLine("using System.Data.SqlClient;"); sb.AppendLine("using System.Data;"); sb.AppendLine("namespace 三层架构Demo.DAL"); sb.AppendLine("{"); sb.AppendLine("class " + tableName + "DAL"); sb.AppendLine("{"); //去掉Id sb.AppendLine(" public int Addnew(" + tableName + " model)"); sb.AppendLine("{"); List<String> cols = new List<string>(); List<String> parameters = new List<string>(); foreach (DataRow row in dt.Rows) { string col = Convert.ToString(row["COLUMN_NAME"]); string parameter = ""; if (col.ToLower()!="id") { parameter= "@" + Convert.ToString(row["COLUMN_NAME"]); cols.Add(col); parameters.Add(parameter); } //parameters.Add(parameter)放外面加上一个NULL，所以会多出一个逗号 // parameters.Add(parameter); } sb.AppendLine("string sql = \"insert into " + tableName + "(" + String.Join(",", cols) + ") output inserted.Id values(" + String.Join(",", parameters) + ")\";"); sb.AppendLine("object obj= SQLHelper.ExecuteScalar(sql"); foreach (DataRow row in dt.Rows) { string col = Convert.ToString(row["COLUMN_NAME"]); if (col.ToLower() != "id") { sb.AppendLine(",new SqlParameter(\"" + col + "\",model." + col + ")"); } } sb.AppendLine(");"); sb.AppendLine("return Convert.ToInt32(obj);"); sb.AppendLine("}"); //Delete方法 sb.AppendLine(" public int Delete(int id)"); sb.AppendLine("{"); sb.AppendLine(" string sql = \"delete from " + tableName + " where Id=@Id\";"); sb.AppendLine("return SQLHelper.ExecuteNonQuery(sql,new SqlParameter(\"Id\",id));"); sb.AppendLine("}"); //Update方法 sb.AppendLine("public int Update("+tableName+" model)"); sb.AppendLine("{"); string[] uParams1=(from col in cols select col+"=@"+col).ToArray(); sb.AppendLine(" string sql = \"update "+tableName+" set "+String.Join(",",uParams1)+" where Id=@Id\";"); string[] uParams2 = (from col in cols select "new SqlParameter(\"" + col + "\",model." + col + ")").ToArray(); sb.AppendLine(" return SQLHelper.ExecuteNonQuery(sql, " + String.Join(",", uParams2) + " ,new SqlParameter(\"Id\",model.Id));"); sb.AppendLine("}"); //GetId方法 sb.AppendLine(" public "+tableName+" Get(int id)"); sb.AppendLine("{"); sb.AppendLine("string sql=\"select * from "+tableName+" where Id=@Id\";"); sb.AppendLine(" DataTable dt=SQLHelper.ExecuteDataTable(sql,new SqlParameter(\"Id\",id));"); sb.AppendLine("if (dt.Rows.Count<=0)"); sb.AppendLine("{"); sb.AppendLine(" return null;"); sb.AppendLine("}"); sb.AppendLine(" else if (dt.Rows.Count==1)"); sb.AppendLine("{"); sb.AppendLine(""+tableName+" model1 = new "+tableName+"();"); foreach (DataRow row in dt.Rows) { string col = Convert.ToString(row["COLUMN_NAME"]); string dataType = Convert.ToString(row["data_TYPe"]); sb.AppendLine("model1." + col + " = Convert." + Get(GetType(dataType).ToString()) + "(dt.Rows[0][\"" + col + "\"]);"); } sb.AppendLine("return model1;"); sb.AppendLine("}"); sb.AppendLine("else"); sb.AppendLine("{"); sb.AppendLine(" throw new Exception(\"数据库中有两条及以上重复数据\");"); sb.AppendLine("}"); sb.AppendLine("}"); //IEnumerable()方法 sb.AppendLine(" public IEnumerable<"+tableName+"> GetAll()"); sb.AppendLine("{"); sb.AppendLine(" string sql = \"select * from "+tableName+"\";"); sb.AppendLine("DataTable dt = SQLHelper.ExecuteDataTable(sql);"); sb.AppendLine(" List<"+tableName+"> list = new List<"+tableName+">();"); sb.AppendLine(" foreach (DataRow row in dt.Rows)"); sb.AppendLine("{"); sb.AppendLine("" + tableName + " model = new " + tableName + "();"); foreach (DataRow row in dt.Rows) { string col = Convert.ToString(row["COLUMN_NAME"]); string dataType = Convert.ToString(row["data_TYPE"]); sb.AppendLine("model." + col + " = Convert." + Get(GetType(dataType).ToString()) + "(row[\"" + col + "\"]);"); } sb.AppendLine(" list.Add(model);"); sb.AppendLine("}"); sb.AppendLine("return list;"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine("}"); File.WriteAllText(@"d:\"+tableName+"DAL.cs",sb.ToString()); } /// <summary> /// 数据库类型转换为C#类型 /// </summary> /// <param name="dataType"></param> /// <returns></returns> private static Type GetType(string dataType) { switch (dataType.ToLower()) { case "nvarchar": case "varchar": case "nchar": case "char": return typeof(string); case "int" : return typeof(int); case "bigint": return typeof(long); case "bit": return typeof(bool); case "datetime": return typeof(DateTime); default: return typeof(object); } } private static string Get(string dataType) { switch (dataType.ToLower()) { case "system.string": return "ToString"; case "system.int32": return "ToInt32"; case "system.int64": return "ToInt64"; case "system.datetime": return "ToDateTime"; case "system.boolean": return "ToBoolean"; default: throw new Exception("找不到匹配的数据类型"); } } private static void CreatModel(string tableName, DataTable dt) { 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("namespace 三层架构Demo.Model"); sb.AppendLine("{"); sb.AppendLine(""); sb.AppendLine("class " + tableName); sb.AppendLine("{"); foreach (DataRow row in dt.Rows) { string dataType = Convert.ToString(row["DATA_TYPE"]); string columnName = Convert.ToString(row["COLUMN_NAME"]); sb.AppendLine("public " + GetType(dataType) + " " + columnName + " { get;set;}"); } sb.AppendLine("}"); sb.AppendLine("}"); File.WriteAllText(@"d:\" + tableName + ".cs", sb.ToString()); //MessageBox.Show(sb.ToString()); } private static void CreatBLL(string tableName, DataTable dt) { 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 三层架构Demo.Model;"); sb.AppendLine("using 三层架构Demo.DAL;"); sb.AppendLine("using System.Data.SqlClient;"); sb.AppendLine("using System.Data;"); sb.AppendLine("namespace 三层架构Demo.BLL"); sb.AppendLine("{"); sb.AppendLine("class " + tableName+"BLL"); sb.AppendLine("{"); sb.AppendLine("public int Addnew("+tableName+" model)"); sb.AppendLine("{"); sb.AppendLine(" return new "+tableName+"DAL().Addnew(model);"); sb.AppendLine("}"); sb.AppendLine(" public int Delete(int id)"); sb.AppendLine("{"); sb.AppendLine(" return new "+tableName+"DAL().Delete(id);"); sb.AppendLine("}"); sb.AppendLine(" public int Update("+tableName+" model)"); sb.AppendLine("{"); sb.AppendLine(" return new " + tableName + "DAL().Update(model);"); sb.AppendLine("}"); sb.AppendLine(" public "+tableName+" Get(int id)"); sb.AppendLine("{"); sb.AppendLine(" return new "+tableName+"DAL().Get(id);"); sb.AppendLine("}"); sb.AppendLine(" public IEnumerable<"+tableName+"> GetAll()"); sb.AppendLine("{"); sb.AppendLine(" return new "+tableName+"DAL().GetAll();"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine("}"); File.WriteAllText(@"d:\" + tableName + "BLL.cs", sb.ToString()); } } }
