public class BuildSqlTool { public static string GetCreateTableSql(object t) { //CREATE TABLE "StructureView2"("Id" INTEGER PRIMARY KEY AUTOINCREMENT , "IdxNo" VARCHAR, "Name" VARCHAR, "Content" VARCHAR, "Price" FLOAT, "Order" INTEGER) string sqlStr = "CREATE TABLE \"{0}\" ({1})"; Type type = t.GetType(); PropertyInfo[] pi = type.GetProperties(); string sqlFormat = "\"{0}\" {1}"; string sqlStr2 = ""; foreach (PropertyInfo p in pi) { string name=p.Name; object[]abc=p.GetCustomAttributes(true); if (name.ToLower()=="id") { sqlStr2 += string.Format(sqlFormat, "Id", "INTEGER PRIMARY KEY AUTOINCREMENT"); } else { sqlStr2 +=","+string.Format(sqlFormat, name, SqlType(p)); } } return string.Format(sqlStr, GetTableName(type), sqlStr2); } static string GetTableName(Type type) { var tableAttr = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault(); return tableAttr != null ? tableAttr.Name : type.Name; } static string SqlType(PropertyInfo p) { Type clrType = p.PropertyType; if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32)) { return "integer"; } else if (clrType == typeof(UInt32) || clrType == typeof(Int64)) { return "bigint"; } else if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal)) { return "float"; } else if (clrType == typeof(String)) { return "varchar"; //int len = MaxStringLength(p); //return "varchar(" + len + ")"; } else if (clrType == typeof(DateTime)) { return "datetime"; } else if (clrType.IsEnum) { return "integer"; } else if (clrType == typeof(byte[])) { return "blob"; } else if (clrType == typeof(Guid)) { return "varchar(36)"; } else { throw new NotSupportedException("Don't know about " + clrType); } } int MaxStringLength(PropertyInfo p) { var attrs = p.GetCustomAttributes(typeof(MaxLengthAttribute), true); if (attrs.Length > 0) { return ((MaxLengthAttribute)attrs[0]).Value; } else { return 140; } } }
主要目的是为了减少见表,同理,可自动生成orm的sql
使用范例
// 获取当前程序集
Assembly assembly = Assembly.GetExecutingAssembly();// 创建类的实例,返回为 object 类型类的完全限定名(即包括命名空间)object obj = assembly.CreateInstance("ConstructionBudget.Model.VModel1"); string sql = BuildSqlTool.GetCreateTableSql(obj);