internal static string MakeSelectItemTemplate(ITableSchema schema)
{
StringBuilder sb = new StringBuilder(100);
sb.Append("SELECT TOP 1 ");
int nx = 0;
foreach (ColumnAttribute col in schema.Cols.Values)
{
if (nx++ > 0)
{
sb.Append(",");
}
sb.Append(col.Name);
}
sb.AppendFormat(" FROM {0}", schema.TableName);
sb.Append(" WITH (NOLOCK) WHERE ");
nx = 0;
foreach (ColumnAttribute key in schema.Keys.Values)
{
if (nx++ > 0)
{
sb.Append(" AND ");
}
sb.Append(string.Format("{0}=@{0}", key.Name));
}
sb.Append(";");
return(sb.ToString());
}
private void ValidateColumns(EntityMapping mapping, ITableSchema tb, StringBuilder errors)
{
var scriptGenerator = this.Option.ScriptGenerator();
foreach (var m in mapping.innerMappingMembers.Where(p => p.isColumn))
{
var c = tb.AllColumns.FirstOrDefault(p => p.ColumnName.ToLower() == m.columnName.ToLower());
if (c == null)
{
errors.AppendFormat("Missing column: {0} in {1}", m.columnName, tb.TableName).AppendLine();
continue;
}
var memberType = m.memberType.IsNullable() ? Nullable.GetUnderlyingType(m.memberType) : m.memberType;
if (c.Type == memberType)
{
continue;
}
var currentDbType = scriptGenerator.GetDbType(m.sqlType).ToLower();
if (!currentDbType.StartsWith(c.ColumnType.ToLower()))
{
errors.AppendFormat("Wrong column type in {0} for column {1}. Found: {2}, Expected {3}", tb.TableName,
c.ColumnName,
currentDbType,
c.ColumnType)
.AppendLine();
}
}
}
public static string GetEnumNameColumnName(ITableSchema table)
{
string result = String.Empty;
foreach (ColumnSchema column in table.Columns)
{
if (Configuration.Instance.EnumRegexIsMatch(column.Name, true))
{
result = column.Name;
break;
}
}
// If no Regex match found, use first column of type String.
if (String.IsNullOrEmpty(result))
{
foreach (ColumnSchema column in table.Columns)
{
if (column.SystemType == typeof(string))
{
result = column.Name;
break;
}
}
}
return(result);
}
public IEntityModel Convert(ITableSchema table)
{
var entity = new EntityModel {
Table = table
};
entity.ClassName = NamingConversion.ClassName(table.TableName);
foreach (var col in table.AllColumns)
{
var member = new MemberModel {
Column = col, MemberName = PopulateMemberName(entity, NamingConversion.PropertyName(col.ColumnName))
};
entity.members.Add(member);
}
foreach (var col in table.ForeignKeys)
{
var member = new NavigationMemberModel {
IsManyToOne = true, Relation = col, MemberName = PopulateMemberName(entity, NamingConversion.ManyToOneName(col))
};
member.DeclareTypeName = NamingConversion.ClassName(col.OtherTable.TableName);
entity.manyToOnes.Add(member);
}
foreach (var col in table.Children)
{
var member = new NavigationMemberModel {
Relation = col, MemberName = PopulateMemberName(entity, NamingConversion.QueryableName(col.OtherTable.TableName))
};
member.DeclareTypeName = NamingConversion.ClassName(col.OtherTable.TableName);
entity.manyToOnes.Add(member);
}
return(entity);
}
private IEnumerable <IColumn> GetColumns(ITableSchema tableSchema)
{
var list = new List <IColumn>();
foreach (var columnSchema in tableSchema.Columns)
{
var column = new Column(columnSchema);
var columnType = this.GetColumnType(columnSchema);
if (columnType != null)
{
column.PropertyType = columnType.Type.FullName;
var primaryKey = tableSchema.PrimaryKeys.FirstOrDefault(x => x.ColumnName.Equals(columnSchema.ColumnName, StringComparison.InvariantCultureIgnoreCase));
if (primaryKey != null)
{
column.PrimaryKey = true;
column.Generated = primaryKey.AutoIncrement;
}
column.DbType = this._dbTypeService.GetDbType(columnSchema, columnType, primaryKey);
}
list.Add(column);
}
return(list);
}
private static ITableSchema GetToManyTable(ITableSchema manyToTable, ITableSchema sourceTable)
{
if (manyToTable == null || sourceTable == null)
{
return(null);
}
// This will return null if the one or two of the foreign keys point to the manyToMany Table.
if (!manyToTable.IsManyToMany() && manyToTable.ForeignKeys.Count(m => m.PrimaryKeyTable.FullName.Equals(sourceTable.FullName, StringComparison.InvariantCulture)) > 0)
{
return(null);
}
ITableSchema result = null;
foreach (TableKeySchema key in manyToTable.ForeignKeys)
{
if (!key.PrimaryKeyTable.FullName.Equals(sourceTable.FullName, StringComparison.InvariantCulture))
{
result = key.PrimaryKeyTable;
break;
}
}
return(result);
}
public IEntityModel Convert(ITableSchema table)
{
var entity = new EntityModel { Table = table };
entity.ClassName = NamingConversion.ClassName(table.TableName);
foreach (var col in table.AllColumns)
{
var member = new MemberModel { Column = col, MemberName = PopulateMemberName(entity, NamingConversion.PropertyName(col.ColumnName)) };
entity.members.Add(member);
}
foreach (var col in table.ForeignKeys)
{
var member = new NavigationMemberModel { IsManyToOne = true, Relation = col, MemberName = PopulateMemberName(entity, NamingConversion.ManyToOneName(col)) };
member.DeclareTypeName = NamingConversion.ClassName(col.OtherTable.TableName);
entity.manyToOnes.Add(member);
}
foreach (var col in table.Children)
{
var member = new NavigationMemberModel { Relation = col, MemberName = PopulateMemberName(entity, NamingConversion.QueryableName(col.OtherTable.TableName)) };
member.DeclareTypeName = NamingConversion.ClassName(col.OtherTable.TableName);
entity.manyToOnes.Add(member);
}
return entity;
}
public static SqlCommand GetSelectItemCommand <T>()
where T : BusinessBase <T>
{
ITableSchema schema = BusinessBase <T> .Schema;
string commandtext = String.Format(MakeSelectItemTemplate(schema));
SqlCommand cmd = null;
SqlCommand tmpCmd = null;
try
{
tmpCmd = new SqlCommand();
tmpCmd.CommandText = commandtext;
tmpCmd.CommandType = CommandType.Text;
cmd = tmpCmd;
tmpCmd = null;
}
finally
{
if (tmpCmd != null)
{
tmpCmd.Dispose(); tmpCmd = null;
}
}
return(cmd);
}
public static string GenerateTemplate(ITableSchema schema, SqlScriptType type, bool ifExists)
{
TableName tableName = schema.TableName;
TableClause script = new TableClause(schema);
switch (type)
{
case SqlScriptType.INSERT:
return(script.INSERT(schema.Columns));
case SqlScriptType.SELECT:
return(script.SELECT(schema.Columns));
case SqlScriptType.UPDATE:
return(script.UPDATE(schema.Columns));
case SqlScriptType.INSERT_OR_UPDATE:
return(script.INSERT_OR_UPDATE(schema.Columns));
case SqlScriptType.DELETE:
return(new Dependency(tableName.DatabaseName).DELETE(tableName)
+ script.DELETE(schema.Columns));
case SqlScriptType.DROP:
return(new Dependency(tableName.DatabaseName).DROP_TABLE(tableName, ifExists)
+ script.DROP_TABLE(ifExists));
}
return(null);
}
public static SqlCommand GetSelectItemCommand <T>(string template, T instance)
where T : BusinessBase <T>
{
ITableSchema schema = BusinessBase <T> .Schema;
SqlCommand cmd = null;
SqlCommand tmpCmd = null;
try
{
tmpCmd = new SqlCommand();
tmpCmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder();
int nx = 0;
foreach (ColumnAttribute key in schema.Keys.Values)
{
System.Reflection.FieldInfo field = typeof(T).GetField(key.Storage, BindingFlags.NonPublic | BindingFlags.Instance);
tmpCmd.Parameters.AddWithValue("@" + key.Name, field.GetValue(instance));
sb.Append(string.Format("{0}{1}=@{1}", nx == 0 ? "" : " and ", key.Name));
nx++;
}
tmpCmd.CommandText = String.Format(template, " TOP 1", (sb.Length == 0 ? "" : " WHERE " + sb.ToString()), "");
cmd = tmpCmd;
tmpCmd = null;
}
finally
{
if (tmpCmd != null)
{
tmpCmd.Dispose(); tmpCmd = null;
}
}
return(cmd);
}
public static SqlCommand GetSelectItemCommand <T>(T instance)
where T : BusinessBase <T>
{
ITableSchema schema = BusinessBase <T> .Schema;
string commandtext = String.Format(MakeSelectItemTemplate(schema));
SqlCommand cmd = null;
SqlCommand tmpCmd = null;
try
{
tmpCmd = new SqlCommand();
tmpCmd.CommandText = commandtext;
tmpCmd.CommandType = CommandType.Text;
// Fill in parameter values
foreach (ColumnAttribute key in schema.Keys.Values)
{
System.Reflection.FieldInfo field = typeof(T).GetField(key.Storage, BindingFlags.NonPublic | BindingFlags.Instance);
tmpCmd.Parameters.AddWithValue("@" + key.Name, field.GetValue(instance));
}
cmd = tmpCmd;
tmpCmd = null;
}
finally
{
if (tmpCmd != null)
{
tmpCmd.Dispose(); tmpCmd = null;
}
}
return(cmd);
}
public static SqlCommand GetSelectCommand <T>(SqlFilter filter)
where T : BusinessBase <T>
{
ITableSchema schema = BusinessBase <T> .Schema;
string commandtext = String.Format(MakeSelectTemplate(schema),
filter.TopN > 0 ? " TOP " + filter.TopN : "",
filter.Where.Length > 0 ? " WHERE " + filter.Where : "",
filter.OrderBy.Length > 0 ? " ORDER BY " + filter.OrderBy : "");
SqlCommand cmd = null;
SqlCommand tmpCmd = null;
try
{
tmpCmd = new SqlCommand();
tmpCmd.CommandText = commandtext;
tmpCmd.CommandType = CommandType.Text;
cmd = tmpCmd;
tmpCmd = null;
}
finally
{
if (tmpCmd != null)
{
tmpCmd.Dispose(); tmpCmd = null;
}
}
return(cmd);
}
public DpoClass(ITableSchema metaTable, ClassName cname, DpoOption option)
{
this.metaTable = metaTable;
this.nameSpace = cname.Namespace;
this.className = cname.Class;
this.option = option;
this.code = new CSharpBuilder {
Namespace = cname.Namespace,
};
code.AddUsing("System");
code.AddUsing("System.Collections.Generic");
code.AddUsing("System.Text");
code.AddUsing("System.Data");
code.AddUsing("System.Drawing");
code.AddUsing("Sys.Data");
code.AddUsing("Sys.Data.Manager");
clss = new Class(cname.Class, new CodeBuilder.TypeInfo {
Type = typeof(DPObject)
})
{
Modifier = Modifier.Public | Modifier.Partial,
Sorted = option.CodeSorted
};
this.code.AddClass(clss);
nonvalized = NonvalizedList(nameSpace, className);
nullableFields = NullableList(nameSpace, className);
}
internal static string MakeSelectKeyTemplate(ITableSchema schema, string selectTemplate)
{
if (!selectTemplate.StartsWith("SELECT{0}"))
{
throw new Exception("Invalid select template for key query");
}
int np = selectTemplate.IndexOf(" FROM ");
if (np < 0)
{
throw new Exception("Invalid select template for key query");
}
StringBuilder sb = new StringBuilder(100);
sb.Append("SELECT{0} ");
int nx = 0;
foreach (ColumnAttribute col in schema.Keys.Values)
{
if (nx++ > 0)
{
sb.Append(",");
}
sb.Append(col.Name);
}
// Now append everything from the select template starting from the " FROM " marker...
sb.Append(selectTemplate.Substring(np));
return(sb.ToString());
}
/// <summary>
/// </summary>
/// <param name="source"></param>
/// <param name="tableKeySchema"></param>
/// <returns></returns>
public static TableAssociation FromChildPrimaryKey(TableEntity source, ITableKeySchema tableKeySchema)
{
//In Child Associations, the ForeignKey should be the associated entity
IEntity associationEntity = EntityStore.Instance.GetEntity(tableKeySchema.ForeignKeyTable.FullName);
if (associationEntity == null && !Configuration.Instance.IncludeManyToManyEntity && tableKeySchema.ForeignKeyTable.IsManyToMany())
{
associationEntity = EntityStore.Instance.GetExcludedEntity(tableKeySchema.ForeignKeyTable.FullName);
}
if (associationEntity == null)
{
return(null);
}
TableAssociation association = null;
foreach (IMemberColumnSchema foreignColumn in tableKeySchema.ForeignKeyMemberColumns)
{
//Added a check to see if the FK is also a Foreign composite key (http://community.codesmithtools.com/forums/t/10266.aspx).
bool isForeignKeyAlsoComposite = foreignColumn.Table.HasPrimaryKey && foreignColumn.Table.PrimaryKey.MemberColumns.Count > 1 && foreignColumn.IsPrimaryKeyMember && foreignColumn.IsForeignKeyMember;
if (!foreignColumn.IsPrimaryKeyMember || isForeignKeyAlsoComposite)
{
if (foreignColumn.Table.IsManyToMany())
{
//&& foreignTable != null) // NOTE: This can because a ManyToMany can be to itself.
ITableSchema foreignTable = GetToManyTable(foreignColumn.Table, source.EntitySource) ?? source.EntitySource;
association = FromChildManyToMany(source, foreignColumn, foreignTable.FullName);
}
else
{
var type = AssociationType.OneToMany;
if (tableKeySchema.ForeignKeyMemberColumns.Count(m => m.AllowDBNull) == tableKeySchema.ForeignKeyMemberColumns.Count)
{
type = AssociationType.ZeroOrOneToMany;
}
else if (!isForeignKeyAlsoComposite && tableKeySchema.ForeignKeyMemberColumns.Count(m => m.IsUnique || m.IsPrimaryKeyMember) == tableKeySchema.ForeignKeyMemberColumns.Count)
{
type = AssociationType.OneToZeroOrOne;
}
association = new TableAssociation(tableKeySchema, type, source, associationEntity, true);
}
}
else if (GetToManyTable(foreignColumn.Table, source.EntitySource) == null)
{
association = new TableAssociation(tableKeySchema, AssociationType.OneToZeroOrOne, source, associationEntity, true);
}
}
//End Association between AspNetUser -> ASpNetRole with Intermediary table as a property
if (association == null || association.Properties.Count <= 0 || String.IsNullOrEmpty(association.AssociationKey))
{
return(null);
}
return(association);
}
/// <summary>
/// Checks to see if a Table is part of a ManyToMany relationship. It checks for the following:
/// 1) Table must have Two ForeignKeys.
/// 2) All columns must be either...
/// a) IProperty of the Primary Key.
/// b) IProperty of a Foreign Key.
/// c) A DateTime stamp (CreateDate, EditDate, etc).
/// d) Name matches Version Regex.
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static bool IsManyToMany(this ITableSchema table)
{
if (!Configuration.Instance.IncludeManyToManyAssociations)
{
return(false);
}
// Bypass logic if table contains Extended Property for ManyToMany
if (table.ExtendedPropertiesContainsKey(Configuration.Instance.ManyToManyExtendedProperty))
{
bool manyToMany;
if (Boolean.TryParse(table.ExtendedProperties[Configuration.Instance.ManyToManyExtendedProperty].Value.ToString(), out manyToMany))
{
if (!manyToMany)
{
return(false);
}
// Table must have atleast two fk's...
if (table.ForeignKeys.Count < 2)
{
return(false);
}
return(true);
}
}
// 1) Table must have Two ForeignKeys.
// 2) All columns must be either...
// a) IProperty of the Primary Key.
// b) IProperty of a Foreign Key.
// c) A DateTime stamp (CreateDate, EditDate, etc).
// d) Name matches Version Regex.
// has to be at least 2 columns
if (table.Columns.Count < 2)
{
return(false);
}
if (table.ForeignKeys.Count != 2)
{
return(false);
}
foreach (IColumnSchema column in table.Columns)
{
//bool isManyToMany = (column.IsForeignKeyMember || column.IsPrimaryKeyMember || column.SystemType.Equals(typeof(DateTime)) || (Configuration.Instance.UseRowVersionRegex && Configuration.Instance.RowVersionColumnRegex.IsMatch(column.Name)));
// Had to apply this stupid workaround because Microsoft IEntity Framework doesn't support IdentityColumns as Part of a Many-To-Many.
bool isManyToMany = (column.IsForeignKeyMember || (column.IsColumnComputed() && !column.IsColumnIdentity()) || (Configuration.Instance.UseRowVersionRegex && Configuration.Instance.RowVersionColumnRegex.IsMatch(column.Name))) && !column.AllowDBNull;
if (!isManyToMany)
{
return(false);
}
}
return(true);
}
public BrokerOfDataContract1()
{
this.type = typeof(TEntity);
this.extension = HostType.GetType(type.FullName + EXTENSION);
this.Schema = extension.GetTableSchemaFromType();
this.functionToDictionary = extension.GetMethod(nameof(ToDictionary), BindingFlags.Public | BindingFlags.Static);
}
/// <summary>
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static bool IsEnum(this ITableSchema table)
{
return(Configuration.Instance.EnumRegexIsMatch(table.FullName, false) && // 1) Matches the Enum Regex.
table.HasPrimaryKey && // 2) Has a Primary Key...
table.PrimaryKey.MemberColumns.Count == 1 && // 3) ...that is a single column...
IsEnumSystemType(table.PrimaryKey.MemberColumns[0]) && // 4) ...of a number type.
!String.IsNullOrEmpty(GetEnumNameColumnName(table))); // 5) Contains a column for name.
}
public TableCompare(ITableSchema schema1, ITableSchema schema2)
{
this.SideType = CompareSideType.compare;
this.ExceptColumns = new string[] { };
this.schema1 = schema1;
this.schema2 = schema2;
}
public TableDataClause(ITableSchema schema)
{
this.schema = schema;
this.tableName = schema.TableName;
this.template = new SqlTemplate(tableName);
this.pk = schema.PrimaryKeys.Keys;
this.ik = schema.Identity.ColumnNames;
this.ck = schema.Columns.Where(column => column.IsComputed).Select(column => column.ColumnName).ToArray();
}
public static int GenerateRows(SqlScriptType type, StreamWriter writer, ITableSchema schema, Locator where, SqlScriptGenerationOption option, IProgress <int> progress)
{
SqlScriptGeneration gen = new SqlScriptGeneration(type, schema)
{
Where = where,
Option = option,
};
return(gen.Generate(writer, progress));
}
public static string FormalTableName(this ITableSchema schema)
{
if (schema.SchemaName == SchemaName.dbo || schema.SchemaName == SchemaName.empty)
{
return(string.Format("[{0}]", schema.TableName));
}
else
{
return($"[{schema.SchemaName}].[{schema.TableName}]");
}
}
private IEnumerable <IAssociation> GetAssociations(ITableSchema tableSchema)
{
var list = new List <IAssociation>();
foreach (var associationSchema in tableSchema.Associations)
{
list.Add(new Association(associationSchema));
}
return(list);
}
public Table(ITableSchema schema, IPluralizationService pluralizationService)
{
ArgumentUtility.CheckNotNull("schema", schema);
ArgumentUtility.CheckNotNullOrEmpty("schema.TableName", schema.TableName);
ArgumentUtility.CheckNotNull("pluralizationService", pluralizationService);
this.TableName = schema.TableName;
this.EntityName = pluralizationService.Singularize(schema.TableName)
.ToSafeClrName(schema.TableName.ShouldForceProperCase());
this.IsView = schema.IsView;
}
/// <summary>
/// use default locator to save records into database, primary keys must be defined
/// </summary>
/// <param name="tableName"></param>
public TableWriter(TableName tableName)
{
this.schema = tableName.GetTableSchema();
IPrimaryKeys primary = schema.PrimaryKeys;
if (primary.Length != 0)
{
this.locator = new Locator(primary);
}
}
/// <summary>
/// 初始化
/// </summary>
/// <param name="databaseSchema">获取数据库结构信息接口</param>
/// <param name="tableSchema">表架构接口</param>
public BuildDatabase(IDatabaseSchema databaseSchema, ITableSchema tableSchema)
{
if (databaseSchema == null)
{
throw new ArgumentNullException("databaseSchema");
}
if (tableSchema == null)
{
throw new ArgumentNullException("tableSchema");
}
this.databaseSchema = databaseSchema;
this.tableSchema = tableSchema;
}
internal static string GenerateCREATE_TABLE(ITableSchema table)
{
string fields = string.Join(Environment.NewLine, table.Columns.Select(column => "\t" + column.GetSQLField() + ","));
if (table.TableName.Provider.DpType == DbProviderType.Sqlite)
{
return(CREATE_TABLE(fields, table.PrimaryKeys, table.ForeignKeys));
}
else
{
return(CREATE_TABLE(fields, table.PrimaryKeys));
}
}
/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
if (databaseSchema != null)
{
databaseSchema.Dispose();
}
if (tableSchema != null)
{
tableSchema.Dispose();
}
this.databaseSchema = null;
this.tableSchema = null;
}
internal Table(DataContext context)
{
this.Context = context;
this.broker = BrokerOfDataContract <TEntity> .CreateBroker(context.ClassType);
this.schema = broker.Schema;
this.formalName = schema.FormalTableName();
this.Generator = new SqlMaker(formalName)
{
PrimaryKeys = schema.PrimaryKeys,
IdentityKeys = schema.IdentityKeys,
};
}
public bool Build(string _codefilesavepath, string guid)
{
try
{
string templateFileName;//= Path.GetFileName(_template_name);
string templateDirPath;
templateFileFullPath = GetTemplateInfo(_template_name, out templateDirPath, out templateFileName);
List <ITableSchema> allTables = GetAllTableSchema(_setting, _tables, guid);
ITableSchema currentTable = allTables.Find(it => it.Name.Equals(_table_name));
this.Constant.TemplateName = templateFileName;
TemplateResolver th = new TemplateResolver(templateDirPath);
xUtils util = new xUtils();
th.Put("Tables", allTables);
th.Put("Table", currentTable);
th.Put("Utils", util);
th.Put("Const", this.Constant);
if (ContainRows(templateFileFullPath))
{
var fac = DatabaseResolver.GetDataFactory(this._setting);
th.Put("Rows", fac.GetTableData(this._table_name).ToDynamic());
}
string text = th.BuildString(templateFileName);
var customerDefine = BlockCommentDictionary(text);
string filename = util.ToPascalCase(_table_name) + ".generate.cs";
if (customerDefine.Count > 0 && customerDefine.ContainsKey("filename"))
{
filename = customerDefine["filename"];
}
if (!Directory.Exists(_codefilesavepath))
{
Directory.CreateDirectory(_codefilesavepath);
}
string fullsavefilename = _codefilesavepath + "/" + filename;
if (File.Exists(fullsavefilename))
{
File.Delete(fullsavefilename);
}
File.AppendAllText(fullsavefilename, text);
return(true);
}
catch (Exception ex)
{
this.ExceptionMessage = ex.Message;
LogText(string.Concat(Environment.NewLine, ex.StackTrace));
return(false);
}
}
internal static string MakeSelectKeyTemplate(ITableSchema schema)
{
StringBuilder sb = new StringBuilder(100);
sb.Append("SELECT{0} ");
int nx = 0;
foreach (ColumnAttribute col in schema.Keys.Values)
{
if (nx++ > 0)
{
sb.Append(",");
}
sb.Append(col.Name);
}
sb.AppendFormat(" FROM {0}", schema.TableName);
sb.Append(" WITH (NOLOCK){1}{2}");
return(sb.ToString());
}
public void Setup()
{
Database.SetUp();
Database.CreateTables();
GribbleDatabase = Gribble.TableSchema.Create(Database.Connection, profiler: Profiler);
}
