private IEnumerable <ColumnAssociation> GetAssociations()
{
var associations = new List <ColumnAssociation>();
if (!_connection.IsDbc)
{
return(associations);
}
DataTable foreignKeys = null;
DoConnected(() => {
foreignKeys = _connection.GetSchema(SchemaNames.ForeignKeys);
});
associations.AddRange((from row in foreignKeys.AsEnumerable()
select new ColumnAssociation {
RelationshipName = row.Field <string>(SchemaColumnNames.ForeignKey.ForeignKeyName),
ChildTable = row.Field <string>(SchemaColumnNames.ForeignKey.ForeignKeyTableName),
ChildColumn = row.Field <string>(SchemaColumnNames.ForeignKey.ForeignKeyFieldName),
ParentTable = row.Field <string>(SchemaColumnNames.ForeignKey.PrimaryKeyTableName),
ParentColumn = row.Field <string>(SchemaColumnNames.ForeignKey.PrimaryKeyFieldName)
}).ToList());
return(associations);
}
public override DataTable GetSchema(VfpConnection connection)
{
ArgumentUtility.CheckNotNull("connection", connection);
var dataTable = connection.GetSchema(Key);
if (dataTable.Rows.Count > 0)
{
// remove referential integrity procedures
var rows = dataTable.AsEnumerable()
.Where(row => !row.Field <bool>(VfpConnection.SchemaColumnNames.Procedure.ReferentialIntegrity))
.ToArray();
if (rows.Any())
{
dataTable = rows.CopyToDataTable();
}
else
{
dataTable.Clear();
}
}
dataTable.Columns[VfpConnection.SchemaColumnNames.Procedure.ProcedureName].ColumnName = Columns.Id;
dataTable.Columns.Add(Columns.Name, typeof(string), Columns.Id);
dataTable.Columns.Add(Columns.CatalogName);
dataTable.Columns.Add(Columns.SchemaName);
RemoveColumnsWithUpperCaseNames(dataTable);
dataTable = dataTable.DefaultView.ToTable(dataTable.TableName, false, new[] { "Id", "Name", "CatalogName", "SchemaName" });
dataTable.TableName = Key;
return(dataTable);
}
protected override IVsDataReader SelectObjects(string typeName, object[] restrictions, string[] properties, object[] parameters)
{
if (typeName == null)
{
throw new ArgumentNullException("typeName");
}
VfpClientTracing.Tracer.TraceVerbose(string.Format("{0} typeName={1}", MethodBase.GetCurrentMethod().Name, typeName));
VfpConnection vfpConnection = Site.GetLockedProviderObject() as VfpConnection;
if (vfpConnection == null)
{
throw new NotSupportedException();
}
try {
// Ensure the connection is open
if (Site.State != DataConnectionState.Open)
{
Site.Open();
}
var dataTable = vfpConnection.GetSchema(GetSchemaName(typeName), GetRestrictionsStringArray(restrictions));
return(new AdoDotNetReader(dataTable.CreateDataReader()));
}
finally {
Site.UnlockProviderObject();
}
}
public override DataTable GetSchema(VfpConnection connection, string[] restrictionValues)
{
ArgumentUtility.CheckNotNull("connection", connection);
var schema = connection.GetSchema(VfpConnection.SchemaNames.Indexes, restrictionValues);
schema.DefaultView.RowFilter = Index.Candidate + " = true";
schema = schema.DefaultView.ToTable(Name, true, CandidateKey.TableName, CandidateKey.IndexName, CandidateKey.FieldName);
schema.TableName = Name;
if (schema.Rows.Count == 0 || !ShouldExcludeTablesWithMultipleCandidateKeys(restrictionValues))
{
return(schema);
}
var duplicates = schema.AsEnumerable()
.Select(x => x.Field <string>(CandidateKey.TableName))
.GroupBy(x => x)
.Where(x => x.Count() > 1)
.Select(x => x.Key)
.Distinct()
.ToList();
var query = schema.AsEnumerable().Where(x => !duplicates.Contains(x.Field <string>(CandidateKey.TableName)));
if (query.Any())
{
schema = query.CopyToDataTable();
}
return(schema);
}
private static void Main(string[] args)
{
using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
connection.Open();
var tables = connection.GetSchema(VfpConnection.SchemaNames.Tables);
var fields = connection.GetSchema(VfpConnection.SchemaNames.TableFields);
connection.Close();
var dbc = GetNewDbcFullPath();
var dbcCreator = new DataTableDbcCreator(dbc);
dbcCreator.Add(tables);
dbcCreator.Add(fields);
}
}
public override DataTable GetSchema(VfpConnection connection, string[] restrictionValues)
{
ArgumentUtility.CheckNotNull("connection", connection);
var foreignKeys = connection.OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
ModifyColumns(foreignKeys);
if (!connection.IsDbc || foreignKeys.Rows.Count == 0)
{
return(foreignKeys);
}
// using indexes just so that the restrictionValues can be applied to the foreignKeys
var indexes = connection.GetSchema(VfpConnection.SchemaNames.Indexes, restrictionValues);
if (indexes.Rows.Count == 0)
{
return(foreignKeys);
}
var query = from foreignKey in foreignKeys.AsEnumerable()
from index in indexes.AsEnumerable()
where foreignKey.Field <string>(ForeignKey.ForeignKeyIndexName).Equals(index.Field <string>(Index.IndexName), StringComparison.InvariantCultureIgnoreCase) &&
foreignKey.Field <string>(ForeignKey.ForeignKeyTableName).Equals(index.Field <string>(Index.TableName), StringComparison.InvariantCultureIgnoreCase) &&
foreignKey.Field <string>(ForeignKey.ForeignKeyFieldName).Equals(index.Field <string>(Index.FieldName), StringComparison.InvariantCultureIgnoreCase)
select foreignKey;
if (!query.Any())
{
foreignKeys.Clear();
return(foreignKeys);
}
var schema = query.CopyToDataTable();
schema.TableName = Name;
FormatColumnValues(schema,
new[] {
ForeignKey.ForeignKeyTableName,
ForeignKey.ForeignKeyFieldName,
ForeignKey.ForeignKeyIndexName,
ForeignKey.PrimaryKeyTableName,
ForeignKey.PrimaryKeyFieldName,
ForeignKey.PrimaryKeyIndexName
});
return(schema);
}
private static void AddDbcForeignKeys(VfpConnection connection, DataTable dataTable)
{
var foreignKeys = connection.GetSchema(VfpConnection.SchemaNames.ForeignKeys);
foreach (DataRow dataRow in foreignKeys.Rows)
{
var newRow = dataTable.NewRow();
newRow[Columns.Id] = dataRow[ForeignKeySchema.ForeignKeyName];
newRow[Columns.ParentId] = dataRow[ForeignKeySchema.ForeignKeyTableName];
newRow[Columns.Name] = newRow[Columns.Id];
newRow[Columns.ConstraintType] = ConstraintTypes.ForeignKey;
dataTable.Rows.Add(newRow);
}
}
private static void AddDbcPrimaryKeys(VfpConnection connection, DataTable dataTable)
{
var primarykeys = connection.GetSchema(VfpConnection.SchemaNames.PrimaryKeys);
foreach (DataRow dataRow in primarykeys.Rows)
{
DataRow newRow = dataTable.NewRow();
newRow[Columns.Id] = dataRow[PrimaryKeySchema.IndexName];
newRow[Columns.ParentId] = dataRow[PrimaryKeySchema.TableName];
newRow[Columns.Name] = newRow[Columns.Id];
newRow[Columns.ConstraintType] = ConstraintTypes.PrimaryKey;
dataTable.Rows.Add(newRow);
}
}
public override DataTable GetSchema(VfpConnection connection, string[] restrictionValues)
{
ArgumentUtility.CheckNotNull("connection", connection);
var primaryKeys = connection.OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
ModifyColumns(primaryKeys);
var schema = primaryKeys.Clone();
schema.TableName = Name;
schema.Columns.Add(PrimaryKey.AutoInc, typeof(bool));
if (!connection.IsDbc || primaryKeys.Rows.Count == 0)
{
return(schema);
}
// using indexes just so that the restrictionValues can be applied to the primaryKeys
var indexes = connection.GetSchema(VfpConnection.SchemaNames.Indexes, restrictionValues);
if (indexes.Rows.Count == 0)
{
return(schema);
}
var list = from primaryKey in primaryKeys.AsEnumerable()
from index in indexes.AsEnumerable()
where primaryKey.Field <string>(PrimaryKey.IndexName).Equals(index.Field <string>(Index.IndexName), StringComparison.InvariantCultureIgnoreCase) &&
primaryKey.Field <string>(PrimaryKey.TableName).Equals(index.Field <string>(Index.TableName), StringComparison.InvariantCultureIgnoreCase) &&
primaryKey.Field <string>(PrimaryKey.FieldName).Equals(index.Field <string>(Index.FieldName), StringComparison.InvariantCultureIgnoreCase)
select new {
Row = primaryKey,
AutoIncrement = index.Field <bool>(PrimaryKey.AutoInc)
};
foreach (var item in list)
{
var dataArray = item.Row.ItemArray.ToList();
dataArray.Add(item.AutoIncrement);
schema.Rows.Add(dataArray.ToArray());
}
schema.TableName = Name;
FormatColumnValues(schema, new[] { PrimaryKey.TableName, PrimaryKey.FieldName, PrimaryKey.IndexName });
return(schema);
}
public override DataTable GetSchema(VfpConnection connection)
{
ArgumentUtility.CheckNotNull("connection", connection);
var dataTable = connection.GetSchema(this.GetSchemaName());
RelabelColumns(dataTable);
dataTable = dataTable.DefaultView.ToTable(dataTable.TableName, false, new[] { "Name" });
AddColumns(dataTable);
dataTable = dataTable.DefaultView.ToTable(dataTable.TableName, false, GetColumnNames().ToArray());
return(dataTable);
}
public IEnumerable <string> GetTableNames()
{
using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
connection.Open();
var tableNames = connection.GetSchema(VfpConnection.SchemaNames.Tables)
.AsEnumerable()
.Select(x => x.Field <string>(VfpConnection.SchemaColumnNames.Table.TableName))
.OrderBy(x => x)
.ToArray();
connection.Close();
return(tableNames);
}
}
private void AddDbcForeignKeys(VfpConnection connection, DataTable dataTable)
{
var foreignKeys = connection.GetSchema(VfpConnection.SchemaNames.ForeignKeys);
foreach (DataRow dataRow in foreignKeys.Rows)
{
var newRow = dataTable.NewRow();
newRow["ConstraintId"] = dataRow[ForeignKeySchema.ForeignKeyName];
newRow["ColumnId"] = string.Format("{0}.{1}",
dataRow[ForeignKeySchema.ForeignKeyTableName],
dataRow[ForeignKeySchema.ForeignKeyFieldName]);
dataTable.Rows.Add(newRow);
}
}
private static void AddDbcPrimaryKeys(VfpConnection connection, DataTable dataTable)
{
var primarykeys = connection.GetSchema(VfpConnection.SchemaNames.PrimaryKeys);
foreach (DataRow dataRow in primarykeys.Rows)
{
var newRow = dataTable.NewRow();
newRow["ConstraintId"] = dataRow[PrimaryKeySchema.IndexName];
newRow["ColumnId"] = string.Format("{0}.{1}",
dataRow[PrimaryKeySchema.TableName],
dataRow[PrimaryKeySchema.FieldName]);
dataTable.Rows.Add(newRow);
}
}
private static void AddFreeTablePrimaryKeys(VfpConnection connection, DataTable dataTable)
{
var indexes = connection.GetSchema(VfpConnection.SchemaNames.Indexes);
if (indexes.Rows.Count == 0)
{
return;
}
indexes.DefaultView.RowFilter = IndexSchema.Candidate + " = true";
indexes = indexes.DefaultView.ToTable();
if (indexes.Rows.Count == 0)
{
return;
}
var duplicates = indexes.AsEnumerable()
.Select(x => x.Field <string>(IndexSchema.TableName))
.GroupBy(x => x)
.Where(x => x.Count() > 1)
.Select(x => x.Key)
.Distinct()
.ToList();
var query = indexes.AsEnumerable()
.Where(x => !duplicates.Contains(x.Field <string>(IndexSchema.TableName)));
if (!query.Any())
{
return;
}
indexes = query.CopyToDataTable();
foreach (DataRow dataRow in indexes.Rows)
{
var newRow = dataTable.NewRow();
newRow["ConstraintId"] = "PK_" + dataRow[IndexSchema.TableName];
newRow["ColumnId"] = string.Format("{0}.{1}",
dataRow[IndexSchema.TableName],
dataRow[IndexSchema.FieldName]);
dataTable.Rows.Add(newRow);
}
}
public override DataTable GetSchema(VfpConnection connection)
{
ArgumentUtility.CheckNotNull("connection", connection);
var foreignKeys = connection.GetSchema(VfpConnection.SchemaNames.ForeignKeys);
foreignKeys = foreignKeys.DefaultView.ToTable(foreignKeys.TableName, true, ForeignKeySchema.ForeignKeyName);
foreignKeys.Columns[ForeignKeySchema.ForeignKeyName].ColumnName = "Id";
foreignKeys.Columns.Add("UpdateRule");
foreignKeys.Columns.Add("DeleteRule");
foreach (DataRow row in foreignKeys.Rows)
{
row["UpdateRule"] = "NO ACTION";
row["DeleteRule"] = "NO ACTION";
}
return(foreignKeys);
}
public override DataTable GetSchema(VfpConnection connection, string[] restrictionValues)
{
ArgumentUtility.CheckNotNull("connection", connection);
var schema = CreateEmptyProcedureParametersDataTable();
if (!connection.IsDbc)
{
return(schema);
}
var procedures = connection.GetSchema(VfpConnection.SchemaNames.Procedures, restrictionValues);
if (procedures.Rows.Count == 0)
{
return(schema);
}
foreach (DataRow row in procedures.Rows)
{
var name = row[Procedure.ProcedureName].ToString();
var code = row[Procedure.ProcedureDefinition].ToString();
if (string.IsNullOrEmpty(code))
{
continue;
}
code = RemoveParameterName(name, code);
code = GetParameterCode(code);
AddParameters(schema, name, GetParameters(code));
}
ParseHungarianNotation(schema);
SetDataType(schema);
FormatColumnValues(schema, new[] { ProcedureParameter.ProcedureName, ProcedureParameter.ParameterName });
return(schema);
}
private static void AddForeignKeys(VfpConnection connection, DataTable dataTable)
{
var foreignKeys = connection.GetSchema(VfpConnection.SchemaNames.ForeignKeys);
foreach (DataRow dataRow in foreignKeys.Rows)
{
var newRow = dataTable.NewRow();
newRow["Id"] = dataRow[ForeignKeySchema.ForeignKeyName];
newRow["ConstraintId"] = dataRow[ForeignKeySchema.ForeignKeyName];
// TODO: might need to revisit this ... I guess it is possible to have multiple keys
newRow["Ordinal"] = 1;
newRow["ToColumnId"] = string.Format("{0}.{1}",
dataRow[ForeignKeySchema.PrimaryKeyTableName],
dataRow[ForeignKeySchema.PrimaryKeyFieldName]);
newRow["FromColumnId"] = string.Format("{0}.{1}",
dataRow[ForeignKeySchema.ForeignKeyTableName],
dataRow[ForeignKeySchema.ForeignKeyFieldName]);
dataTable.Rows.Add(newRow);
}
}
public override DataTable GetSchema(VfpConnection connection)
{
ArgumentUtility.CheckNotNull("connection", connection);
var dataTable = connection.GetSchema((_isView ? VfpClientSchemaNames.ViewFields : VfpClientSchemaNames.TableFields));
RelabelColumns(dataTable);
AddColumns(dataTable);
SetValues(dataTable);
RemoveColumnsWithUpperCaseNames(dataTable);
SetIdentity(connection, dataTable);
dataTable = dataTable.DefaultView.ToTable(dataTable.TableName, false, new[] {
"ParentId",
"Name",
"Ordinal",
"IsNullable",
"MaxLength",
"Precision",
"Scale",
"DateTimePrecision",
"CharacterSetCatalog",
"CharacterSetSchema",
"CharacterSetName",
"CollationCatalog",
"CollationSchema",
"CollationName",
"Id",
"IsMultiset",
"IsStoreGenerated",
"IsIdentity",
"Default",
"TypeName"
});
return(dataTable);
}
public override DataTable GetSchema(VfpConnection connection)
{
ArgumentUtility.CheckNotNull("connection", connection);
var dataTable = connection.GetSchema(Key);
dataTable = RemoveReferentialIntegrityProcedures(connection, dataTable);
RelabelColumns(dataTable);
AddColumns(dataTable);
FixDataTypes(dataTable);
dataTable = dataTable.DefaultView.ToTable(dataTable.TableName, false, new[] {
Columns.Id,
Columns.ParentId,
Columns.Name,
Columns.Ordinal,
Columns.TypeName,
Columns.MaxLength,
Columns.Precision,
Columns.DateTimePrecision,
Columns.Scale,
Columns.CollationCatalog,
Columns.CollationSchema,
Columns.CollationName,
Columns.CharacterSetCatalog,
Columns.CharacterSetSchema,
Columns.CharacterSetName,
Columns.IsMultiSet,
Columns.Mode,
Columns.Default
});
dataTable.TableName = Key;
return(dataTable);
}
private static void SetIdentity(VfpConnection connection, DataTable dataTable)
{
var indexes = connection.GetSchema(VfpConnection.SchemaNames.Indexes);
if (indexes.Rows.Count == 0)
{
return;
}
var primaryKeylist = (from columnRow in dataTable.AsEnumerable()
from indexRow in indexes.AsEnumerable()
where columnRow.Field <string>("ParentId") == indexRow.Field <string>(IndexSchema.TableName) &&
columnRow.Field <string>("Name") == indexRow.Field <string>(IndexSchema.FieldName) &&
indexRow.Field <bool>(IndexSchema.PrimaryKey)
select new {
ColumnRow = columnRow,
PrimaryKey = indexRow.Field <bool>(IndexSchema.PrimaryKey),
AutoIncrement = indexRow.Field <bool>(IndexSchema.AutoInc)
}).ToList();
var tableNames = new HashSet <string>();
foreach (var item in primaryKeylist)
{
tableNames.Add(item.ColumnRow.Field <string>("ParentId"));
item.ColumnRow["IsIdentity"] = item.AutoIncrement;
item.ColumnRow["IsStoreGenerated"] = item.AutoIncrement;
if (item.PrimaryKey)
{
item.ColumnRow["IsNullable"] = false;
}
}
var candidateKeyList = (from columnRow in dataTable.AsEnumerable()
from indexRow in indexes.AsEnumerable()
where columnRow.Field <string>("ParentId") == indexRow.Field <string>(IndexSchema.TableName) &&
columnRow.Field <string>("Name") == indexRow.Field <string>(IndexSchema.FieldName) &&
indexRow.Field <bool>(IndexSchema.Candidate) &&
!tableNames.Contains(columnRow.Field <string>("ParentId"))
select columnRow).ToList();
if (candidateKeyList.Any())
{
// filter out tables that have multiple candidate key columns
var duplicates = candidateKeyList.Select(x => x.Field <string>("ParentId"))
.GroupBy(x => x)
.Where(x => x.Count() > 1)
.Select(x => x.Key)
.Distinct()
.ToList();
candidateKeyList = candidateKeyList.Where(x => !duplicates.Contains(x.Field <string>("ParentId"))).ToList();
foreach (var item in candidateKeyList)
{
item["IsIdentity"] = true;
item["IsNullable"] = false;
}
}
}
public override DataTable GetSchema(VfpConnection connection, string[] restrictionValues)
{
ArgumentUtility.CheckNotNull("connection", connection);
var schema = CreateEmptyDataTable();
if (!connection.IsDbc)
{
return(schema);
}
var parameters = connection.GetSchema(VfpConnection.SchemaNames.ProcedureParameters, restrictionValues);
if (parameters.Rows.Count == 0)
{
return(schema);
}
var list = (from p in parameters.AsEnumerable()
group p by p.Field <string>(ProcedureParameter.ProcedureName) into g
select new {
ProcedureName = g.Key,
Parameters = g.Select(item => new {
ParameterName = item.Field <string>(ProcedureParameter.ParameterName),
VfpType = (VfpType)item.Field <int>(ProcedureParameter.VfpType)
}).ToList()
}).ToList();
foreach (var item in list)
{
using (var command = connection.CreateCommand()) {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = item.ProcedureName;
command.CommandText += "(";
if (item.Parameters.Count > 0)
{
for (int index = 0, total = item.Parameters.Count; index < total; index++)
{
if (index > 0)
{
command.CommandText += ", ";
}
var parameter = item.Parameters[index];
var value = parameter.VfpType.GetDefaultValue();
command.CommandText += value;
}
}
command.CommandText += ")";
try {
var reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
var schemaTable = reader.GetSchemaTable();
AddSchemaRows(item.ProcedureName, schema, schemaTable);
}
catch (VfpException ex) {
VfpClientTracing.Tracer.TraceError(ex);
}
}
}
FormatColumnValues(schema, new[] { ProcedureColumn.ProcedureName, ProcedureColumn.FieldName });
return(schema);
}
