// GetForeignKeyConstraintColumns
public static DataTable GetForeignKeys(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_ForeignKeys, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
dataTable.Rows.Add(
table["FK_NAME"] + "." + table["ORDINAL"], // Id
table["ORDINAL"], // Ordinal
table["FK_NAME"], // ConstraintId
table["FK_TABLE_NAME"] + "." + table["FK_COLUMN_NAME"], // FromColumnId
table["PK_TABLE_NAME"] + "." + table["PK_COLUMN_NAME"], // ToColumnId
table["FK_TABLE_NAME"], // FromTable
table["FK_COLUMN_NAME"], // FromColumn
table["PK_TABLE_NAME"], // ToTable
table["PK_COLUMN_NAME"] // ToColumn
);
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetIndexColumns(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_IndexColumns, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Indexes,
new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
dataTable.Rows.Add(
(string)table["TABLE_NAME"] + "." + (string)table["INDEX_NAME"] + "." + (string)table["COLUMN_NAME"], // Id
table["TABLE_NAME"] + "." + (string)table["INDEX_NAME"], // ParentId
table["TABLE_NAME"] + "." + table["COLUMN_NAME"], // ColumnId
table["TABLE_NAME"], // Table
table["INDEX_NAME"], // Index
table["COLUMN_NAME"], // Name
Convert.ToInt32(table["ORDINAL_POSITION"]) // Ordinal
);
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetForeignKeyConstraints(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_ForeignKeyConstraints, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
if (Convert.ToInt32(table["ORDINAL"]) == 1)
{
dataTable.Rows.Add(
table["FK_NAME"], // Id
table["PK_TABLE_NAME"], // ToTableId
table["FK_TABLE_NAME"], // FromTableId
table["UPDATE_RULE"], // Update rule
table["DELETE_RULE"] // Delete rule
);
}
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetIndexes(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_Indexes, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Indexes,
new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
if (
Convert.ToInt32(table["ORDINAL_POSITION"]) == 1 // Only the first field of the index
)
{
dataTable.Rows.Add(
(string)table["TABLE_NAME"] + "." + (string)table["INDEX_NAME"], // Id
table["TABLE_NAME"], // ParentId
table["TABLE_NAME"], // Table
table["INDEX_NAME"], // Name
Convert.ToBoolean(table["UNIQUE"]), // IsUnique
Convert.ToBoolean(table["PRIMARY_KEY"]) // IsPrimary
);
}
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetTables(OleDbConnection connection)
{
bool includeSystemTables = false;
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_Tables, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
if (!IsSystemTable(table["TABLE_NAME"].ToString()) || includeSystemTables)
{
dataTable.Rows.Add(
table["TABLE_NAME"],
"Jet",
"Jet",
table["TABLE_NAME"],
IsSystemTable(table["TABLE_NAME"].ToString()) ? "SYSTEM" : "USER");
}
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetViewColumns(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_ViewColumns, typeof(DataTable));
Dictionary <string, string> objectsToGet = GetTablesOrViewDictionary(connection, false);
GetTableOrViewColumns(connection, dataTable, objectsToGet);
return(dataTable);
}
public static DataTable GetViews(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_Views, typeof(DataTable));
DataTable schemaTable = connection.GetSchema("Views");
foreach (System.Data.DataRow table in schemaTable.Rows)
{
dataTable.Rows.Add(table["TABLE_NAME"], "Jet", "Jet", table["TABLE_NAME"], table["VIEW_DEFINITION"], table["IS_UPDATABLE"]);
}
dataTable.AcceptChanges();
return(dataTable);
}
/// <summary>
/// Gets the views via OleDb schema table.
/// No definition can be retrieved
/// </summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public static DataTable GetViewsViaGetOleDbSchemaTable(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_Views, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "VIEW" });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
dataTable.Rows.Add(table["TABLE_NAME"], "Jet", "Jet", table["TABLE_NAME"], DBNull.Value, 0);
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetConstraintColumns(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_ConstraintColumns, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
dataTable.Rows.Add(
table["FK_NAME"], // ConstraintId
table["FK_TABLE_NAME"] + "." + table["FK_COLUMN_NAME"] // ColumnId
);
}
schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
dataTable.Rows.Add(
table["TABLE_NAME"] + "." + table["PK_NAME"], // ConstraintId
table["TABLE_NAME"] + "." + table["COLUMN_NAME"] // ColumnId
);
}
schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
if (
Convert.ToBoolean(table["PRIMARY_KEY"]) == false && // Not a primary key
Convert.ToBoolean(table["UNIQUE"]) == true // Unique constraint
)
{
dataTable.Rows.Add(
table["TABLE_NAME"] + "." + table["INDEX_NAME"], // ConstraintId
table["TABLE_NAME"] + "." + table["COLUMN_NAME"] // ColumnId
);
}
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetCheckConstraints(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_CheckConstraints, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Check_Constraints, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
dataTable.Rows.Add(
table["CONSTRAINT_NAME"], // Id
table["CHECK_CLAUSE"] // Expression
);
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetFunctions(DbConnection connection)
{
return((DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_Functions, typeof(DataTable)));
}
public static DataTable GetProcedures(OleDbConnection oleDbConnection)
{
return((DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_Procedures, typeof(DataTable)));
}
public static DataTable GetConstraints(OleDbConnection connection)
{
DataTable dataTable = (DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_Constraints, typeof(DataTable));
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Check_Constraints, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
dataTable.Rows.Add(
table["CONSTRAINT_NAME"], // Id
DBNull.Value, // ParentId
table["CONSTRAINT_NAME"], // Name
"CHECK", // ConstraintType
false, // IsDeferrable
false // IsIntiallyDeferred
);
}
schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
if (Convert.ToInt32(table["ORDINAL"]) == 1)
{
dataTable.Rows.Add(
table["FK_NAME"], // Id
table["FK_TABLE_NAME"], // ParentId
table["FK_NAME"], // Name
"FOREIGN KEY", // ConstraintType
table["DEFERRABILITY"], // IsDeferrable
false // IsIntiallyDeferred
);
}
}
schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
if (Convert.ToInt32(table["ORDINAL"]) == 1)
{
dataTable.Rows.Add(
table["TABLE_NAME"] + "." + table["PK_NAME"], // Id
table["TABLE_NAME"], // ParentId
table["PK_NAME"], // Name
"PRIMARY KEY", // ConstraintType
false, // IsDeferrable
false // IsIntiallyDeferred
);
}
}
schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, new object[] { });
foreach (System.Data.DataRow table in schemaTable.Rows)
{
if (
Convert.ToInt32(table["ORDINAL_POSITION"]) == 1 && // Only the first field of the index
Convert.ToBoolean(table["PRIMARY_KEY"]) == false && // Not a primary key
Convert.ToBoolean(table["UNIQUE"]) == true // Unique constraint
)
{
dataTable.Rows.Add(
(string)table["TABLE_NAME"] + "." + (string)table["INDEX_NAME"], // Id
table["TABLE_NAME"], // ParentId
table["INDEX_NAME"], // Name
"UNIQUE", // ConstraintType
false, // IsDeferrable
false // IsIntiallyDeferred
);
}
}
dataTable.AcceptChanges();
return(dataTable);
}
public static DataTable GetViewConstraints(OleDbConnection connection)
{
return((DataTable)XmlObjectSerializer.GetObject(Properties.Resources.StoreSchemaDefinition_ViewConstraints, typeof(DataTable)));
}
