using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using Dapper;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.ComponentModel; namespace CodeFirst
{
class Program
{
static readonly string SchemaName;
static readonly string ConnectionString; static Program()
{
SchemaName = "22TopWeb";
if (string.IsNullOrWhiteSpace(SchemaName))
{
throw new Exception("'SchemaName' load failed");
}
if (new[] { "master", "model", "msdb", "tempdb" }.Contains(SchemaName))
{
throw new Exception("'SchemaName' illegal");
}
ConnectionString = "Data Source=192.168.8.119;User ID=EQCCD_HUNTER;Password=zhey1bu2012;Initial Catalog=master;Pooling=true";
if (string.IsNullOrWhiteSpace(ConnectionString))
{
throw new Exception("'ConnectionString' load failed");
}
var pattern = @"Initial\s*Catalog\s*=\s*master";
Match match = Regex.Match(ConnectionString, pattern, RegexOptions.IgnoreCase);
if (match.Groups.Count > )
{
//可能需要创建数据库
CheckSchema(ConnectionString, SchemaName);
ConnectionString = ConnectionString.Replace(match.Groups[].Value, "Initial Catalog=" + SchemaName);
}
} static void Main(string[] args)
{
var sql = GetTableCreateSql("CodeFirst.TB_Enterprise"); ExcuteSql(ConnectionString, sql.Replace("GO", "")); //GO只能在查询分析器里使用 Console.ReadKey();
} /// <summary>
///
/// </summary>
/// <param name="fullName"></param>
/// <param name="overwrite">如果表已存在，drop后重新创建（true慎用）</param>
/// <returns></returns>
static string GetTableCreateSql(string fullName, bool overwrite = false)
{
var type = Type.GetType(fullName); var columnDefinitionList = GetColumnDefinitionList(type); //数据库 表名
var tableName = type.Name;
var dbTableNameAttr = type.GetCustomAttributes(false).Where(attr => attr.GetType().Name == "DBTableNameAttribute").SingleOrDefault() as
dynamic;
if (dbTableNameAttr != null)
tableName = dbTableNameAttr.Name;
//主键列
var primaryKeyArr = (from clmn in columnDefinitionList where clmn.IsPrimaryKey select clmn.ColumnName).ToArray();
//是否 TEXTIMAGE ON
var isTextImageOn = type.GetCustomAttributes(false).Where(attr => attr.GetType().Name == "TextImageOn").Any(); if (!string.IsNullOrWhiteSpace(tableName) && columnDefinitionList.Count > )
{
var sb = new StringBuilder(); sb.AppendFormat(@"USE [{0}]
GO", SchemaName); if (overwrite)
{
sb.AppendFormat(@" if exists (select 1 from sysobjects where id = object_id('{0}') and type = 'U')
drop table {0}
GO", tableName);
} sb.AppendFormat(@" /****** Object: Table [dbo].[{1}] Script Date: {2} Generate By CodeFrist ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO SET ANSI_PADDING ON
GO CREATE TABLE [dbo].[{1}](", SchemaName, tableName, DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")); columnDefinitionList.ForEach(p =>
{
//组合主键 不能定义 IDENTITY
sb.AppendFormat(@"
[{0}] [{1}]{2} {3} {4},", p.ColumnName, p.DbType, p.MaxLength > ? "(" + p.MaxLength + ")" : "", p.IsPrimaryKey && primaryKeyArr.Length <= ? "IDENTITY(" + p.Seed + "," + p.Incr + ")" : "", p.IsNullable ? "NULL" : "NOT NULL");
}); if (primaryKeyArr != null && primaryKeyArr.Length > )
{
//主键列
sb.AppendFormat(@"
CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED
(
{1}
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
", tableName, primaryKeyArr.Aggregate("", (current, cName) => current += string.Format(",[{0}] ASC", cName)).Trim(','));
}
//else //多余的这个逗号可以不去掉 sb.AppendFormat(@"
) ON [PRIMARY] {0} GO SET ANSI_PADDING OFF
GO
", isTextImageOn ? "TEXTIMAGE_ON [PRIMARY]" : ""); columnDefinitionList.Where(p => !string.IsNullOrWhiteSpace(p.Description)).ToList().ForEach(p =>
{
//字段说明
sb.AppendFormat(@"
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{2}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{1}'
GO
", tableName, p.ColumnName, ToSqlLike(p.Description));
}); return sb.ToString(); //这个格式和Management Studio生成的sql内容一致 } return string.Empty;
} /// <summary>
/// 获取所有列定义（此为重点，反射+特性）
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
static List<ColumnDefinition> GetColumnDefinitionList(Type type)
{
var columnDefinitionList = new List<ColumnDefinition>(); var pInfoArr = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo pInfo in pInfoArr)
{
var columnDefinition = new ColumnDefinition() { ColumnName = pInfo.Name }; Console.WriteLine("----------Property Name:{0}-----------", pInfo.Name); foreach (dynamic attr in pInfo.GetCustomAttributes(false))
{
var attributeName = attr.GetType().Name as string; var attributeInfoStr = string.Format("Attribute Name:{0}", attributeName);
switch (attributeName)
{
case "PrimaryKeyAttribute":
columnDefinition.IsPrimaryKey = true;
columnDefinition.Seed = attr.Seed;
columnDefinition.Incr = attr.Incr;
columnDefinition.IsPrimaryKey = true;
Console.WriteLine(attributeInfoStr);
break;
case "DataTypeAttribute":
columnDefinition.DbType = attr.DbType;
columnDefinition.MaxLength = attr.MaxLength;
attributeInfoStr += string.Format("(DbType:{0}{1})", columnDefinition.DbType, columnDefinition.MaxLength > ? ",MaxLength:" + columnDefinition.MaxLength : "");
Console.WriteLine(attributeInfoStr);
break;
case "IsNullableAttribute":
columnDefinition.IsNullable = true;
Console.WriteLine(attributeInfoStr);
break;
case "DescriptionAttribute":
columnDefinition.Description = attr.Description; //字段说明
attributeInfoStr += string.Format("(说明:{0})", columnDefinition.Description);
Console.WriteLine(attributeInfoStr);
break;
default:
break;
}
} if (!string.IsNullOrWhiteSpace(columnDefinition.ColumnName) && !string.IsNullOrWhiteSpace(columnDefinition.DbType))
{
columnDefinitionList.Add(columnDefinition);
} Console.WriteLine();
} return columnDefinitionList;
} #region DBHelper /// <summary>
/// check数据库是否已存在，不存在则自动创建
/// </summary>
/// <param name="connectionString"></param>
/// <param name="schemaName"></param>
static void CheckSchema(string connectionString, string schemaName)
{
var pattern = @"Initial\s*Catalog\s*=\s*master";
Match match = Regex.Match(connectionString, pattern, RegexOptions.IgnoreCase);
if (match.Groups.Count == )
{
throw new ArgumentException();
}
var sql = string.Format(@"
if not exists(select 1 from sysdatabases where name='{0}')
create database {0}
", schemaName);
ExcuteSql(connectionString, sql);
} static bool ExcuteSql(string connectionString, string sql)
{
try
{
using (var conn = new SqlConnection(connectionString))
{
conn.Execute(sql);
}
return true;
}
catch (Exception ex)
{
return false;
}
} /// <summary>
/// 对字符串进行sql格式化，并且符合like查询的格式。
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
static string ToSqlLike(string sqlstr)
{
if (string.IsNullOrEmpty(sqlstr)) return string.Empty;
StringBuilder str = new StringBuilder(sqlstr);
str.Replace("'", "''");
str.Replace("[", "[[]");
str.Replace("%", "[%]");
str.Replace("_", "[_]");
return str.ToString();
} #endregion } /// <summary>
/// 数据库 列定义
/// </summary>
public class ColumnDefinition
{
public string ColumnName { get; set; }
public bool IsPrimaryKey { get; set; }
/// <summary>
/// 标示种子
/// </summary>
public int Seed { get; set; }
/// <summary>
/// 标示增量
/// </summary>
public int Incr { get; set; }
public string DbType { get; set; }
public int MaxLength { get; set; }
/// <summary>
/// true 可为空， 否则 false 不可为空
/// </summary>
public bool IsNullable { get; set; }
public string Description { get; set; }
} #region Custom Attributes [AttributeUsage(AttributeTargets.Class)]
/// <summary>
/// 数据库 表名
/// </summary>
public class DBTableNameAttribute : Attribute
{
public string Name { get; set; }
} [AttributeUsage(AttributeTargets.Class)]
/// <summary>
/// 表的TEXTIMAGE ON特性
/// </summary>
public class TextImageOnAttribute : Attribute
{ } [AttributeUsage(AttributeTargets.Property)]
/// <summary>
/// 主键
/// </summary>
public class PrimaryKeyAttribute : Attribute
{
/// <summary>
/// 标示种子
/// </summary>
public int Seed { get; set; }
/// <summary>
/// 标示增量
/// </summary>
public int Incr { get; set; }
} [AttributeUsage(AttributeTargets.Property)]
/// <summary>
/// 数据类型
/// </summary>
public class DataTypeAttribute : Attribute
{
public string DbType { get; set; }
public int MaxLength { get; set; }
} [AttributeUsage(AttributeTargets.Property)]
/// <summary>
/// 允许Null值
/// </summary>
public class IsNullableAttribute : Attribute
{ } #endregion #region Table Model [TextImageOn]
/// <summary>
///
/// </summary>
public class TB_Enterprise
{
[PrimaryKey(Seed = , Incr = )]
[DataType(DbType = "int")]
public int EnterpriseId { get; set; } [DataType(DbType = "int")]
public int Status { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? IsFamous { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? CustomerLevel { get; set; } [IsNullable]
[DataType(DbType = "nvarchar", MaxLength = )]
[Description("企业名称")]
/// <summary>
/// 企业名称
/// </summary>
public string Name { get; set; } [IsNullable]
[DataType(DbType = "nvarchar", MaxLength = )]
public string Industry { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? Mode { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? Scale { get; set; } [DataType(DbType = "nvarchar", MaxLength = )]
[IsNullable]
public string City { get; set; } [DataType(DbType = "nvarchar", MaxLength = )]
[IsNullable]
public string WebSite { get; set; } [DataType(DbType = "ntext")]
[IsNullable]
public string DescText { get; set; } [DataType(DbType = "datetime")]
public DateTime CreateDate { get; set; } [DataType(DbType = "datetime")]
public DateTime ModifyDate { get; set; } [DataType(DbType = "datetime")]
[IsNullable]
public DateTime? ApproveDate { get; set; } [DataType(DbType = "nvarchar", MaxLength = )]
[IsNullable]
public string SourceName { get; set; } [DataType(DbType = "nvarchar", MaxLength = )]
[IsNullable]
public string License { get; set; } [DataType(DbType = "varchar", MaxLength = )]
[IsNullable]
public string CreateUser { get; set; } [DataType(DbType = "varchar", MaxLength = )]
[IsNullable]
public string ModifyUser { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? ProcessStatus { get; set; } [DataType(DbType = "varchar", MaxLength = )]
[IsNullable]
public string Abbr { get; set; } [DataType(DbType = "varchar", MaxLength = )]
[IsNullable]
public string NameInitial { get; set; } [DataType(DbType = "float")]
[IsNullable]
public decimal? Activity { get; set; } [DataType(DbType = "nvarchar", MaxLength = )]
[IsNullable]
public string Tags { get; set; } [DataType(DbType = "nvarchar", MaxLength = )]
[IsNullable]
public string ConsultantName { get; set; } [DataType(DbType = "nvarchar", MaxLength = )]
[IsNullable]
public string ConsultantComment { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? ConsultantId { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? DecoratePercent { get; set; } [DataType(DbType = "nvarchar", MaxLength = )]
[IsNullable]
public string ShortDesc { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? CertificationStatus { get; set; } [DataType(DbType = "bit")]
[IsNullable]
public bool? IsBDRecommended { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? ApproveStatus { get; set; } [DataType(DbType = "varchar", MaxLength = )]
[IsNullable]
public string ApproveResult { get; set; } [DataType(DbType = "int")]
[IsNullable]
public int? ApproveByUserId { get; set; }
} #endregion }