在开发基于三层架构的项目初期，都会按照数据库的表结构定义一堆的实体类，但是一个表里有那么多的字段，一个库又有那么多个表，建这些实体类的工作量不少，作为一个懒人，肯定想些法子解决这么麻烦的问题。写一个实体生成器。
为了生成器能兼容各仲类型的数据库，我把生成器的架构定义成如下形式
还是说明一下吧！在数据库的上一层定义了各种数据的Controler,负责建立各种数据的连接等等，在DAL的每一个类都实现IDAL接口，每一种数据库的DAL类都定义了各自查询SQL，在BLL处通过读取配置，获知当前需要对哪个类型的数据库操作，再通过反射机制动态生成相应的DAL实例。这样如果再有新的数据库类型，只要添加新的Controler类和新的DAL类，在配置中改一下就行了。
看一下程序的界面
项目的结构
Entitiy目录下的是本项目中需要的一些实体类，包括储存表信息的TableENT，列信息的ColumnENT,实体类文件信息的CSFileENT。此生成器生成的类文件并不是通过模板文件来构造文件内容的，文件信息是通过配置文件来配置的，用EntityFileConfig.xml来配置。
生成实体类的时候要知道数据库里有什么表，表里头有什么字段，相关的SQL语句我只有SQL Server的，其他的数据库还没搞(或者有需要的时候才去搞，呵呵),所以这里暂时只能生成SQL Server的。
下面则各个类的代码，DBC里面的就不展示了，
首先是IDAL接口的，实现IDAL接口的类要实现4个方法，GetTableNames获取数据库表的名称，GetTableInfo根据表的名称获取表的字段信息，ConvertType把数据库的数据类型转换成.NET Framework的类型。
1 public interface IDAL 2 { 3 List<TableENT> GetTabelNames( ); 4 5 List<ColumnENT> GetTabelInfo(string tableName); 6 7 string ConvertType(string DbTypeName); 8 9 bool TestConnect(); 10 }
到MSSQLDAL
1 public class MSSQLDAL:IDAL 2 { 3 public List<TableENT> GetTabelNames( ) 4 { 5 List<TableENT> list = new List<TableENT>(); 6 MSSQLControler db = new MSSQLControler(); 7 string cmdText = "SELECT name FROM sysobjects WHERE type='U' "; 8 DataTable table = db.GetDataTable(cmdText, null); 9 10 TableENT ent = null; 11 foreach (DataRow row in table.Rows) 12 { 13 ent = new TableENT(); 14 ent.TableName = row["name"]==DBNull.Value?string.Empty:row["name"].ToString(); 15 ent.State = StateEnum.Ready; 16 list.Add(ent); 17 } 18 19 return list; 20 } 21 22 public List<ColumnENT> GetTabelInfo(string tableName) 23 { 24 List<ColumnENT> list = new List<ColumnENT>(); 25 26 MSSQLControler db = new MSSQLControler(); 27 string cmdText = "sp_columns @TabelName "; 28 SqlParameter[] paraArr = new SqlParameter[] { new SqlParameter("@TabelName",tableName) }; 29 DataTable table = db.GetDataTable(cmdText, paraArr); 30 31 ColumnENT ent = null; 32 foreach (DataRow row in table.Rows) 33 { 34 ent = new ColumnENT(); 35 ent.ColumnName = row["COLUMN_NAME"] == DBNull.Value ? string.Empty : row["COLUMN_NAME"].ToString(); 36 ent.DBTypeName = row["TYPE_NAME"] == DBNull.Value ? string.Empty : row["TYPE_NAME"].ToString(); 37 list.Add(ent); 38 } 39 return list; 40 } 41 42 //分割类型名 43 public string ConvertType(string DbTypeName) 44 { 45 switch (DbTypeName) 46 { 47 case "bigint": return "Int64"; 48 case "datetimeoffset": return "DateTimeOffset"; 49 case "float": return "Double"; 50 case "int": return "Int32"; 51 case "real": return "Single"; 52 case "smallint": return "Int16"; 53 case "time": return "TimeSpan"; 54 case "uniqueidentifier": return "Guid"; 55 case "xml": return "Xml"; 56 case "bit": return "Boolean"; 57 case "tinyint": return "Byte"; 58 } 59 if (DbTypeName == "char" || 60 DbTypeName == "nchar" || 61 DbTypeName == "ntext" || 62 DbTypeName == "nvarchar" || 63 DbTypeName == "text" || 64 DbTypeName == "varchar") return "String"; 65 66 if (DbTypeName == "FILESTREAM" || 67 DbTypeName == "binary" || 68 DbTypeName == "timestamp" || 69 DbTypeName == "image" || 70 DbTypeName == "rowversion" || 71 DbTypeName == "varbinary") return "Byte[]"; 72 73 if (DbTypeName == "datetime" || 74 DbTypeName == "date" || 75 DbTypeName == "datetime2" || 76 DbTypeName == "smalldatetime") return "DateTime"; 77 78 79 if (DbTypeName == "decimal" || 80 DbTypeName == "money" || 81 DbTypeName == "numeric" || 82 DbTypeName == "smallmoney") return "Decimal"; 83 84 return "object"; 85 } 86 87 public bool TestConnect() 88 { 89 MSSQLControler db = new MSSQLControler(); 90 return db.TestConnect(); 91 } 92 }
到DbBLL,DbBLL是直接给窗体的事件方法调用的，
1 public class DbBLL 2 { 3 private static string typeName = ConfigurationManager.AppSettings["TypeName"]; 4 5 private static string dllPath = ConfigurationManager.AppSettings["dllPath"]; 6 7 public List<TableENT> GetTabelNames( ) 8 { 9 List<TableENT> list = new List<TableENT>(); 10 IDAL dal = CreateDAL(); 11 if (dal != null) list = dal.GetTabelNames(); 12 return list; 13 } 14 15 public bool CreateEntity(string TableName,string outPath) 16 { 17 IDAL dal = CreateDAL(); 18 FileCommon fileBLL = new FileCommon(); 19 if (dal == null) return false; 20 List<ColumnENT> colLlist = dal.GetTabelInfo(TableName); 21 22 CSFileENT file = fileBLL.LoadModelFile(); 23 foreach (ColumnENT item in colLlist) 24 { 25 string colTemp = item.DBTypeName.Split().FirstOrDefault(); 26 item.EntTypeName = dal.ConvertType(colTemp); 27 } 28 file.ClassName = TableName; 29 string content = fileBLL.CreateFileContent(file, colLlist); 30 31 bool result = false; 32 outPath = outPath.Trim('\\')+"\\" + TableName + "_ENT.cs"; 33 result = fileBLL.OutputFile(content,outPath); 34 return result; 35 } 36 37 public bool TestConnect() 38 { 39 bool result=false; 40 IDAL dal = CreateDAL(); 41 if (dal != null) result = dal.TestConnect(); 42 return result; 43 } 44 45 private IDAL CreateDAL() 46 { 47 if (string.IsNullOrEmpty(typeName)) 48 throw new Exception("配置文件里的TypeName不存在或为空"); 49 Assembly assembly; 50 if(string.IsNullOrEmpty(dllPath))assembly=Assembly.GetExecutingAssembly(); 51 else assembly= Assembly.LoadFile(""); 52 Type dalType = assembly.GetType(typeName); 53 IDAL typeIns =assembly.CreateInstance(typeName) as IDAL; 54 return typeIns; 55 } 56 }
FileCommon是生成代码的以及生成实体类文件的类
1 public class FileCommon 2 { 3 public CSFileENT LoadModelFile( ) 4 { 5 CSFileENT ent = new CSFileENT(); 6 XmlDocument config = new XmlDocument(); 7 config.Load("EntityFileConfig.xml"); 8 XmlNodeList nodeList = config.ChildNodes[1].ChildNodes; 9 foreach (XmlNode node in nodeList) 10 { 11 switch (node.Name) 12 { 13 case "using": 14 XmlNodeList usingList = node.ChildNodes; 15 foreach (XmlNode child in usingList) 16 { 17 ent.UsingText += "using " + child.Attributes["value"].Value + ";\r\n"; 18 } 19 break; 20 case "namespace": 21 ent.NameSpaceText = node.Attributes["value"].Value; 22 break; 23 case "expand": 24 XmlNodeList expandList = node.ChildNodes; 25 foreach (XmlNode child in expandList) 26 { 27 ent.Expand += child.Attributes["value"].Value + ","; 28 } 29 ent.Expand=ent.Expand.Trim(','); 30 break; 31 case "fieldAttribute": 32 ent.FieldAttribute = node.Attributes["value"].Value; 33 break; 34 case "classAttribute": 35 ent.ClassAttribute = node.Attributes["value"].Value; 36 break; 37 } 38 } 39 return ent; 40 } 41 42 public string CreateFileContent(CSFileENT file, List<ColumnENT> colList) 43 { 44 string result = string.Empty; 45 46 result += file.UsingText+"{0}"; 47 if (!string.IsNullOrEmpty(file.NameSpaceText)) 48 result = string.Format(result, "\r\nnamespace " + file.NameSpaceText + "\r\n{\r\n\t $$ \r\n}").Replace("$$","{0}"); 49 result= result.Replace("{0}",file.ClassAttribute + "\r\n\tpublic class " + file.ClassName+"{0}"); 50 if (!string.IsNullOrEmpty(file.Expand)) 51 result =result.Replace("{0}", ":"+file.Expand+"{0}"); 52 result =result.Replace("{0}", "\r\n\t{\r\n\t\t{0}\r\n\t}"); 53 54 foreach (ColumnENT col in colList) 55 { 56 string fildText = string.Empty; 57 if (!string.IsNullOrEmpty(file.FieldAttribute)) 58 fildText += file.FieldAttribute + "\r\n\t\t"; 59 fildText += "public " + col.EntTypeName + " " + col.ColumnName; 60 result = result.Replace("{0}", fildText + " {get;set;}\r\n\r\n\t\t{0}"); 61 } 62 63 result = result.Replace("{0}",""); 64 65 return result; 66 } 67 68 public bool OutputFile(string context,string outputPath) 69 { 70 bool result = false; 71 72 try 73 { 74 File.WriteAllText(outputPath, context); 75 result=true; 76 } 77 catch { result = false; } 78 return result; 79 } 80 }
界面的代码就不列举了，接下来到介绍程序的配置
生成实体类的配置
1 <entityConfig> 2 <using> 3 <add value="System" /> 4 </using> 5 <namespace value="ENT"/> 6 <expand> 7 <add value=""/> 8 </expand> 9 <fieldAttribute value=""/> 10 <classAttribute value=""/> 11 </entityConfig>
using填的是要引用到的命名空间，namespace是生成类的命名空间的名字，expand是生成类要继承的类和实现的接口的列表，fieldAttribute是字段的Attribute值，classAttribute是类的Attribute，这两个Attribute在WCF方面有用的。
程序配置
1 <configuration> 2 <appSettings> 3 <add key="connectionstring" value="Data Source=localhost\SQLEXPRESS;Initial Catalog=HOMEMAKINGSYSTEM;Integrated Security=true;"/> 4 <add key="TypeName" value="EntityCreator.DAL.MSSQLDAL"/><!--SQL Server:EntityCreator.DAL.MSSQLDAL --> 5 <add key="dllPath" value=""/> 6 </appSettings> 7 8 </configuration>
connectionstring不用说了；TypeName是当前要调用那个DAL类的完全限定名；若添加了新类型的数据库，可以把新的DAL和Controler类编译成dll,然后把dll的路径放到dllPath里，程序则会生成相应的实例了。
经各位过路大侠们指教才发现原来有codesmith这么一个好东西，鄙人真是孤陋寡闻了。
补充两篇博文，是写这个生成器前的准备知识 《各种数据库查询表及表信息的SQL》和《各种数据库与.NET Framework类型对照》