为EntityFrame 实体类 更新数据表结构
最近用EntityFramew 写应用，感觉更新表结构太费事了，于是干脆自己写一个小程序用于将实体类更新到数据库中。
方法如下：
利用C#的反射，读取EntityFramework实体类的属性
遍历DLL的所有类，找出所有[Table("...")]属性的类，（注:当有设置[NotMapped]不做导入）
再遍历所有[Column("...")] 属性，
Required 对应生成 not null
key 对应更新主键
生成过程：（生成相应的SQL语句）
发现不存在的表，则增加 表
发现不存在的列，则增加 列
发现主键与实体类不一致，则删除原主键，再插入新主键
未解决问题：
1 数据列名称更改。
2 字段类型变更
3 预准备的基础数据怎么样导入
欢迎讨论。
实体类如下：
注： Column 属性里必须指定TypeName
[Table("t_user")] // [NotMapped] public class User { [Key, DatabaseGenerated(DatabaseGeneratedOption.None)] [Column("ukey",TypeName="int")] public int key { get; set; } [Column("uName" ,TypeName="varchar(100)")] [Required, StringLength(200)] public string Name { get; set; } [Column("uAMT", TypeName = "decimal(18,4)")] public float amt { get; set; } }
生成代码如下： 为省事直接建了一个console应用程序
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Reflection; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Data.SqlClient; namespace GenerateDatabase { class Program { static bool DEBUG = true; static void Main(string[] args) { string conn = "sql 连接字符串"; string dll = @"ERP.Businesses.dll"; //DLL及相关需引用的文件最好与EXE放在同一目录下 Assembly A = Assembly.LoadFrom(dll); //遍历类 foreach (Type T in A.GetTypes()) { myTable table = new myTable(); //遍历属性 foreach (Object obj in T.GetCustomAttributes(false)) { //为Table属性 if (obj.GetType().Name == "TableAttribute") { TableAttribute tbl = (TableAttribute)obj; table.Name = tbl.Name; table.isTable = true; if (DEBUG) Console.WriteLine(string.Format("发现表{0}", table.Name)); foreach (PropertyInfo p in T.GetProperties()) { myCol col = new myCol(); foreach (Object o in p.GetCustomAttributes(false)) { if (o.GetType().Name == "ColumnAttribute") { ColumnAttribute att = (ColumnAttribute)o; col.Name = att.Name; col.Type = att.TypeName; col.isCol = true; } if (o.GetType().Name == "RequiredAttribute") { col.isRequire = true; } if ( o.GetType().Name == "KeyAttribute") { col.isRequire = true; col.isKey = true; } } if (col.isCol) { table.Columns.Add(col); } if (DEBUG && col.isCol) Console.WriteLine(string.Format("\t列{0} , {1}",col.Name , col.Type )); } } if (obj.GetType().Name == "NotMappedAttribute") { table.noMap = true; } } //是table时,开始检查表结构 if (table.isTable && !table.noMap) { table.UpdateDatabase(conn); } } Console.WriteLine("success!"); Console.Read(); } } public class myCol { public myCol() { isRequire = false; isKey = false; isCol = false; } public string Name { set; get; } public string Type { set; get; } public bool isCol { set; get; } public bool isRequire { set; get; } public bool isKey { set; get; } public string NullableString { get { return isRequire ? " not null " : ""; } } } public class myTable { public myTable() { Columns = new List<myCol>(); isTable = false; } //表名 public string Name { set; get; } public bool noMap { set; get; } public bool isTable { set; get; } public List<myCol> Columns ; private string _conn; public void UpdateDatabase(string connectstring) { if (noMap) return; if (!isTable) return; _conn = connectstring; //this.executeCommand(this.fun_split()); //如果表不存在,则生成表 this.executeCommand(this.updateTable()); //表存在的时候,检查各列,是否存在, 不存在则增加 foreach (myCol col in Columns) { this.executeCommand(this.updateCol(col)); } //检查主键是否一致,更新主键 string sql = this.updatePaimaryKey(); this.executeCommand(sql); } private int executeCommand(string cmdText) { if (string.IsNullOrEmpty(_conn)) return -999; SqlConnection conn = new SqlConnection(_conn); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = cmdText; int rlt = cmd.ExecuteNonQuery(); conn.Close(); return rlt; } private string updateTable() { string sql =string.Format(@" if object_id('{0}') is null create table dbo.{0} ( {1} PRIMARY KEY CLUSTERED ( {2} )ON [PRIMARY] ) ", this.Name , this.getCols() , this.getKeyCols()); return sql ; } private string updateCol(myCol col) { string sql = string.Format(@" if COLUMNPROPERTY( object_id('{0}') , '{1}','ColumnId') is null alter table {0} add {1} {2} {3} ", this.Name, col.Name , col.Type, col.NullableString ); return sql; } private string getCols() { string sql = ""; foreach (myCol col in Columns) { sql += string.Format(",{0} {1} {2} ", col.Name, col.Type, col.NullableString); } if (sql.Length >= 1) { sql = sql.Substring(1, sql.Length - 1); } return sql; } private string getKeyCols() { string sql = ""; foreach (myCol col in Columns) { if(col.isKey) sql += string.Format(", {0} ", col.Name); } if (sql.Length >= 1) { sql = sql.Substring(1, sql.Length - 1); } else { throw new Exception("表不可以没有主键"); } return sql; } private string updatePaimaryKey() { return string.Format(@" declare @keystring nvarchar(max) , @tablename varchar(1000) set @keystring = '{1}' set @tablename = '{0}' -------------------------拆分字串为表----------------------------------------------------------------------------- declare @new table(colNew varchar(1000)) declare @commastr varchar(max),@splitstr varchar(max),@splitlen int,@length int declare @splitChar char(1) select @splitChar = ',' select @splitstr=@keystring, @splitlen=charindex(@splitChar,@splitstr), @length=datalength(@splitstr) while @length>0 begin if @splitlen=0 set @splitlen=@length+1 set @commastr =@splitstr insert @new(colNew) values(substring(@commastr,1,@splitlen-1)) select @splitstr = substring(@commastr,@splitlen+1,@length), @length=datalength(@splitstr), @splitlen=charindex(@splitChar,@splitstr) end -------------------------------------------------------------------------------------- declare @Old table(f1 varchar(1000) , f2 varchar(1000) , f3 varchar(1000) ,colOld varchar(500) ,f5 int , PK_Name varchar(1000) ) insert @Old(f1,f2,f3,colOld,f5,PK_Name) exec sp_pkeys @tablename if exists( select * from @new a full join @Old b on a.colNew = b.colOld where a.colNew is null or b.colOld is null ) begin declare @pkName varchar(1000) select @pkName = PK_Name from @Old exec('alter table '+@tablename+' drop constraint ' + @pkName) exec('alter table '+@tablename+' add constraint '+ @pkName+' primary key('+@keystring+')') end ", this.Name ,getKeyCols().Trim()) ; } } }
