public static object ColumnasPorTabla(string valor, params object[] parametros)
{
using (ispDB db = new ispDB())
{
LinqToDB.SchemaProvider.GetSchemaOptions GetSchemaOptions =
new LinqToDB.SchemaProvider.GetSchemaOptions();
LinqToDB.SchemaProvider.TableSchema ts = new LinqToDB.SchemaProvider.TableSchema();
var sp = db.DataProvider.GetSchemaProvider();
LinqToDB.SchemaProvider.DatabaseSchema dbs = sp.GetSchema(db, GetSchemaOptions);
List <ColumnaSis> cols = dbs.Tables.Where(x => x.TableName == parametros[0].ToString()).SingleOrDefault().Columns.Select(x => new ColumnaSis()
{
ColumnName = x.ColumnName, ColumnType = x.ColumnName, DataType = x.DataType, Description = x.Description, IsIdentity = x.IsIdentity, IsNullable = x.IsNullable, IsPrimaryKey = x.IsPrimaryKey, MemberName = x.MemberName, MemberType = x.MemberType, PrimaryKeyOrder = x.PrimaryKeyOrder, SystemType = x.SystemType, SkipOnInsert = x.SkipOnInsert, SkipOnUpdate = x.SkipOnUpdate, Table = x.Table
}).ToList();
if (cols.Count > 0)
{
cols.Add(new ColumnaSis()
{
ColumnName = "modificado"
});
}
return(cols);
}
}
public void CargarTablas(ComboBox unCombo)
{
LinqToDB.SchemaProvider.GetSchemaOptions GetSchemaOptions =
new LinqToDB.SchemaProvider.GetSchemaOptions();
LinqToDB.SchemaProvider.TableSchema ts = new LinqToDB.SchemaProvider.TableSchema();
using (ispDB dbcn = new ispDB())
{
var sp = dbcn.DataProvider.GetSchemaProvider();
LinqToDB.SchemaProvider.DatabaseSchema db = sp.GetSchema(dbcn, GetSchemaOptions);
unCombo.ValueMember = "TableName";
unCombo.DisplayMember = "TableName";
unCombo.DataSource = null;
unCombo.DataSource = db.Tables.OrderBy(x => x.TableName).ToList();
}
}
protected virtual DatabaseSchema ProcessSchema(DatabaseSchema databaseSchema)
{
foreach (var t in databaseSchema.Tables)
{
foreach (var key in t.ForeignKeys.ToList())
{
if (!key.KeyName.EndsWith("_BackReference"))
{
key.OtherTable.ForeignKeys.Add(
key.BackReference = new ForeignKeySchema
{
KeyName = key.KeyName + "_BackReference",
MemberName = key.MemberName + "_BackReference",
AssociationType = AssociationType.Auto,
OtherTable = t,
ThisColumns = key.OtherColumns,
OtherColumns = key.ThisColumns,
});
}
}
}
foreach (var t in databaseSchema.Tables)
{
foreach (var key in t.ForeignKeys)
{
if (key.BackReference != null && key.AssociationType == AssociationType.Auto)
{
if (key.ThisColumns.All(_ => _.IsPrimaryKey))
{
if (t.Columns.Count(_ => _.IsPrimaryKey) == key.ThisColumns.Count)
key.AssociationType = AssociationType.OneToOne;
else
key.AssociationType = AssociationType.ManyToOne;
}
else
key.AssociationType = AssociationType.ManyToOne;
key.CanBeNull = key.ThisColumns.All(_ => _.IsNullable);
}
}
foreach (var key in t.ForeignKeys)
{
var name = key.MemberName;
if (key.BackReference != null && key.ThisColumns.Count == 1 && key.ThisColumns[0].MemberName.ToLower().EndsWith("id"))
{
name = key.ThisColumns[0].MemberName;
name = name.Substring(0, name.Length - "id".Length);
if (t.ForeignKeys.Select(_ => _.MemberName). Concat(
t.Columns. Select(_ => _.MemberName)).Concat(
new[] { t.TypeName }).All(_ => _ != name))
{
name = key.MemberName;
}
}
if (name == key.MemberName)
{
if (name.StartsWith("FK_"))
name = name.Substring(3);
if (name.EndsWith("_BackReference"))
name = name.Substring(0, name.Length - "_BackReference".Length);
name = string.Join("", name
.Split('_')
.Where(_ => _.Length > 0 && _ != t.TableName)
.ToArray());
}
if (name.Length != 0 &&
t.ForeignKeys.Select(_ => _.MemberName).Concat(
t.Columns. Select(_ => _.MemberName)).Concat(
new[] { t.TypeName }).All(_ => _ != name))
{
key.MemberName = name;
}
}
}
return databaseSchema;
}
protected virtual DatabaseSchema ProcessSchema(DatabaseSchema databaseSchema, GetSchemaOptions schemaOptions)
{
foreach (var t in databaseSchema.Tables)
{
foreach (var key in t.ForeignKeys.ToList())
{
if (!key.KeyName.EndsWith("_BackReference"))
{
key.OtherTable.ForeignKeys.Add(
key.BackReference = new ForeignKeySchema
{
KeyName = key.KeyName + "_BackReference",
MemberName = key.MemberName + "_BackReference",
AssociationType = AssociationType.Auto,
OtherTable = t,
ThisColumns = key.OtherColumns,
OtherColumns = key.ThisColumns,
CanBeNull = true,
});
}
}
}
foreach (var t in databaseSchema.Tables)
{
foreach (var key in t.ForeignKeys)
{
if (key.BackReference != null && key.AssociationType == AssociationType.Auto)
{
if (key.ThisColumns.All(_ => _.IsPrimaryKey))
{
if (t.Columns.Count(_ => _.IsPrimaryKey) == key.ThisColumns.Count)
{
key.AssociationType = AssociationType.OneToOne;
}
else
{
key.AssociationType = AssociationType.ManyToOne;
}
}
else
{
key.AssociationType = AssociationType.ManyToOne;
}
key.CanBeNull = key.ThisColumns.All(_ => _.IsNullable);
}
}
foreach (var key in t.ForeignKeys)
{
string name = null;
if (schemaOptions.GetAssociationMemberName != null)
{
name = schemaOptions.GetAssociationMemberName(key);
if (name != null)
{
key.MemberName = ToValidName(name);
}
}
if (name == null)
{
name = key.MemberName;
if (key.BackReference != null && key.ThisColumns.Count == 1 && key.ThisColumns[0].MemberName.ToLower().EndsWith("id"))
{
name = key.ThisColumns[0].MemberName;
name = name.Substring(0, name.Length - "id".Length);
if (t.ForeignKeys.Select(_ => _.MemberName).Concat(
t.Columns.Select(_ => _.MemberName)).Concat(
new[] { t.TypeName }).All(_ => _ != name))
{
name = key.MemberName;
}
}
if (name == key.MemberName)
{
if (name.StartsWith("FK_"))
{
name = name.Substring(3);
}
if (name.EndsWith("_BackReference"))
{
name = name.Substring(0, name.Length - "_BackReference".Length);
}
name = string.Join("", name
.Split('_')
.Where(_ =>
_.Length > 0 && _ != t.TableName &&
(t.SchemaName == null || t.IsDefaultSchema || _ != t.SchemaName))
.ToArray());
var digitEnd = 0;
for (var i = name.Length - 1; i >= 0; i--)
{
if (char.IsDigit(name[i]))
{
digitEnd++;
}
else
{
break;
}
}
if (digitEnd > 0)
{
name = name.Substring(0, name.Length - digitEnd);
}
}
if (string.IsNullOrEmpty(name))
{
name = key.OtherTable.TableName;
}
if (t.ForeignKeys.Select(_ => _.MemberName).Concat(
t.Columns.Select(_ => _.MemberName)).Concat(
new[] { t.TypeName }).All(_ => _ != name))
{
key.MemberName = ToValidName(name);
}
}
}
}
return(databaseSchema);
}
protected virtual DatabaseSchema ProcessSchema(DatabaseSchema databaseSchema, GetSchemaOptions schemaOptions)
{
foreach (var t in databaseSchema.Tables)
{
foreach (var key in t.ForeignKeys.ToList())
{
if (!key.KeyName.EndsWith("_BackReference"))
{
key.OtherTable.ForeignKeys.Add(
key.BackReference = new ForeignKeySchema
{
KeyName = key.KeyName + "_BackReference",
MemberName = key.MemberName + "_BackReference",
AssociationType = AssociationType.Auto,
OtherTable = t,
ThisColumns = key.OtherColumns,
OtherColumns = key.ThisColumns,
CanBeNull = true,
});
}
}
}
foreach (var t in databaseSchema.Tables)
{
foreach (var key in t.ForeignKeys)
{
if (key.BackReference != null && key.AssociationType == AssociationType.Auto)
{
if (key.ThisColumns.All(_ => _.IsPrimaryKey))
{
if (t.Columns.Count(_ => _.IsPrimaryKey) == key.ThisColumns.Count)
{
key.AssociationType = AssociationType.OneToOne;
}
else
{
key.AssociationType = AssociationType.ManyToOne;
}
}
else
{
key.AssociationType = AssociationType.ManyToOne;
}
key.CanBeNull = key.ThisColumns.All(_ => _.IsNullable);
}
}
if (t.TableName == "Employees")
{
}
foreach (var key in t.ForeignKeys)
{
SetForeignKeyMemberName(schemaOptions, t, key);
}
}
return(databaseSchema);
}
protected virtual DatabaseSchema ProcessSchema(DatabaseSchema databaseSchema)
{
foreach (var t in databaseSchema.Tables)
{
foreach (var key in t.ForeignKeys.ToList())
{
if (!key.KeyName.EndsWith("_BackReference"))
{
key.OtherTable.ForeignKeys.Add(
key.BackReference = new ForeignKeySchema
{
KeyName = key.KeyName + "_BackReference",
MemberName = key.MemberName + "_BackReference",
AssociationType = AssociationType.Auto,
OtherTable = t,
ThisColumns = key.OtherColumns,
OtherColumns = key.ThisColumns,
CanBeNull = true,
});
}
}
}
foreach (var t in databaseSchema.Tables)
{
foreach (var key in t.ForeignKeys)
{
if (key.BackReference != null && key.AssociationType == AssociationType.Auto)
{
if (key.ThisColumns.All(_ => _.IsPrimaryKey))
{
if (t.Columns.Count(_ => _.IsPrimaryKey) == key.ThisColumns.Count)
{
key.AssociationType = AssociationType.OneToOne;
}
else
{
key.AssociationType = AssociationType.ManyToOne;
}
}
else
{
key.AssociationType = AssociationType.ManyToOne;
}
key.CanBeNull = key.ThisColumns.All(_ => _.IsNullable);
}
}
foreach (var key in t.ForeignKeys)
{
var name = key.MemberName;
if (key.BackReference != null && key.ThisColumns.Count == 1 && key.ThisColumns[0].MemberName.ToLower().EndsWith("id"))
{
name = key.ThisColumns[0].MemberName;
name = name.Substring(0, name.Length - "id".Length);
if (t.ForeignKeys.Select(_ => _.MemberName).Concat(
t.Columns.Select(_ => _.MemberName)).Concat(
new[] { t.TypeName }).All(_ => _ != name))
{
name = key.MemberName;
}
}
if (name == key.MemberName)
{
if (name.StartsWith("FK_"))
{
name = name.Substring(3);
}
if (name.EndsWith("_BackReference"))
{
name = name.Substring(0, name.Length - "_BackReference".Length);
}
name = string.Join("", name
.Split('_')
.Where(_ => _.Length > 0 && _ != t.TableName)
.ToArray());
}
if (name.Length != 0 &&
t.ForeignKeys.Select(_ => _.MemberName).Concat(
t.Columns.Select(_ => _.MemberName)).Concat(
new[] { t.TypeName }).All(_ => _ != name))
{
key.MemberName = name;
}
}
}
return(databaseSchema);
}