public static int WriteSql(this DataSet ds, TextWriter writer, DatabaseName dname)
{
int count = 0;
foreach (DataTable dt in ds.Tables)
{
TableName tname = new TableName(dname, SchemaName.dbo, dt.TableName);
count += WriteSql(dt, writer, tname);
}
return(count);
}
public override bool Exists(DatabaseName dname)
{
try
{
string SQL = sp_databases(provider);
var dnames = provider.FillDataTable(SQL).ToArray <string>("DATABASE_NAME");
return(dnames.FirstOrDefault(row => row.ToLower().Equals(dname.Name.ToLower())) != null);
}
catch (Exception)
{
}
return(false);
}
public override TableName[] GetTableNames(DatabaseName dname)
{
var table = new SqlCmd(dname.Provider, $"SELECT NULL AS SchemaName, NAME as TableName FROM sqlite_master WHERE TYPE='table' AND NOT (name LIKE 'sqlite_%') ORDER BY NAME")
.FillDataTable();
if (table != null)
{
return(table
.AsEnumerable()
.Select(row => new TableName(dname, row["SchemaName"].IsNull(string.Empty), row.Field <string>("TableName")))
.ToArray());
}
return(new TableName[] { });
}
public override TableName[] GetTableNames(DatabaseName dname)
{
var table = new SqlCmd(dname.Provider, $"SELECT TABLE_SCHEMA AS SchemaName, TABLE_NAME as TableName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE' ORDER BY TABLE_SCHEMA,TABLE_NAME")
.FillDataTable();
if (table != null)
{
return(table
.AsEnumerable()
.Select(row => new TableName(dname, row["SchemaName"].IsNull(string.Empty), row.Field <string>("TableName")))
.ToArray());
}
return(new TableName[] { });
}
internal static TableName[] XmlTableNames(this DatabaseName databaseName, DataTable schema)
{
var rows = schema.AsEnumerable()
.Select(row => new { schema = row.Field <string>("SchemaName"), name = row.Field <string>("TableName") })
.Distinct();
List <TableName> tnames = new List <TableName>();
foreach (var row in rows)
{
TableName tname = new TableName(databaseName, row.schema, row.name);
tnames.Add(tname);
}
return(tnames.ToArray());
}
private static DataTable LoadDatabaseSchema(DatabaseName dname)
{
List <SchemaRow> rows = new List <SchemaRow>();
foreach (TableName tname in dname.GetTableNames())
{
LoadColumns(rows, tname);
LoadForeignKeys(rows, tname);
}
DataTable schemaTable = SchemaRowExtension.CreateTable();
rows.ToDataTable(schemaTable);
schemaTable.AcceptChanges();
return(schemaTable);
}
public override DatabaseName[] GetDatabaseNames()
{
List <DatabaseName> dnames = new List <DatabaseName>();
foreach (DataTable table in dbSchema.Tables)
{
DatabaseName dname = new DatabaseName(provider, table.TableName)
{
NameSpace = table.Namespace,
};
dnames.Add(dname);
}
return(dnames.ToArray());
}
public override TableName[] GetProcedureNames(DatabaseName dname)
{
var table = dname.FillDataTable($"USE [{dname.Name}]; SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES");
var L = table.AsEnumerable().Select(row => new
{
type = row.Field <string>("ROUTINE_TYPE"),
schema = row.Field <string>("ROUTINE_SCHEMA"),
name = row.Field <string>("ROUTINE_NAME")
})
.Where(row => !(row.name.StartsWith("sp_") || row.name.StartsWith("fn_"))) //System Procedure or Function
.OrderByDescending(row => row.type) // Sort: Procedure, Function
.ThenBy(row => row.name)
.ToList();
List <TableName> list = new List <TableName>();
foreach (var row in L)
{
TableNameType type = TableNameType.Table;
switch (row.type)
{
case "PROCEDURE":
type = TableNameType.Procedure;
break;
case "FUNCTION":
type = TableNameType.Function;
break;
}
TableName tname = new TableName(dname, row.schema, row.name)
{
Type = type
};
list.Add(tname);
}
return(list.ToArray());
}
public override DependencyInfo[] GetDependencySchema(DatabaseName dname)
{
var dt = this.dbSchema.Tables[0];
var L = dt.AsEnumerable().Where(row =>
row[nameof(ColumnSchema.PK_Schema)] != DBNull.Value &&
row[nameof(ColumnSchema.PK_Table)] != DBNull.Value &&
row[nameof(ColumnSchema.PK_Column)] != DBNull.Value);
DependencyInfo[] rows = L.Select(
row => new DependencyInfo
{
FkTable = new TableName(dname, (string)row["SchemaName"], (string)row["TableName"]),
PkTable = new TableName(dname, (string)row[nameof(ColumnSchema.PK_Schema)], (string)row[nameof(ColumnSchema.PK_Table)]),
PkColumn = (string)row[nameof(ColumnSchema.PK_Column)],
FkColumn = (string)row[nameof(ColumnSchema.ColumnName)]
})
.ToArray();
return(rows);
}
public override TableName[] GetProcedureNames(DatabaseName dname)
{
var table = dname.FillDataTable($"USE [{dname.Name}]; SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES");
List <TableName> list = new List <TableName>();
foreach (DataRow row in table.Rows)
{
string type = row.Field <string>("ROUTINE_TYPE");
string schema = row.Field <string>("ROUTINE_SCHEMA");
string name = row.Field <string>("ROUTINE_NAME");
TableNameType _type = TableNameType.Table;
//System Procedure or Function
if (name.StartsWith("sp_") || name.StartsWith("fn_"))
{
continue;
}
switch (type)
{
case "PROCEDURE":
_type = TableNameType.Procedure;
break;
case "FUNCTION":
_type = TableNameType.Function;
break;
}
TableName tname = new TableName(dname, schema, name)
{
Type = _type
};
list.Add(tname);
}
return(list.ToArray());
}
public override DependencyInfo[] GetDependencySchema(DatabaseName dname)
{
const string sql = @"
SELECT
FK.TABLE_SCHEMA AS FK_SCHEMA,
FK.TABLE_NAME AS FK_Table,
PK.TABLE_SCHEMA AS PK_SCHEMA,
PK.TABLE_NAME AS PK_Table,
PT.COLUMN_NAME AS PK_Column,
CU.COLUMN_NAME AS FK_Column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN ( SELECT i1.TABLE_NAME ,
i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME <> PK.TABLE_NAME
";
var dt = new SqlCmd(dname.Provider, sql).FillDataTable();
DependencyInfo[] rows = dt.AsEnumerable().Select(
row => new DependencyInfo
{
FkTable = new TableName(dname, row["FK_SCHEMA"].IsNull(string.Empty), (string)row["FK_Table"]),
PkTable = new TableName(dname, row["PK_SCHEMA"].IsNull(string.Empty), (string)row["PK_Table"]),
PkColumn = (string)row["PK_Column"],
FkColumn = (string)row["FK_Column"]
})
.ToArray();
return(rows);
}
public override DataTable GetDatabaseSchema(DatabaseName dname)
{
return(InformationSchema.LoadDatabaseSchema(dname.ServerName, new DatabaseName[] { dname }, CreateSQLOfDatabaseSchema)
.Tables[dname.Name]);
}
public virtual bool Exists(DatabaseName dname)
{
return(GetDatabaseNames().FirstOrDefault(row => row.Equals(dname)) != null);
}
public abstract TableName[] GetViewNames(DatabaseName dname);
public override TableName[] GetProcedureNames(DatabaseName dname)
{
return(new TableName[] { });
}
public override DataTable GetDatabaseSchema(DatabaseName dname)
{
return(LoadDatabaseSchema(dname.ServerName, new DatabaseName[] { dname })
.Tables[dname.Name]);
}
public SqlBuilder USE(DatabaseName databaseName)
{
return(Append($"USE {databaseName.Name}").AppendLine());
}
public override DataTable GetDatabaseSchema(DatabaseName dname)
{
return(dbSchema.Tables[dname.Name]);
}
/// <summary> /// Get stored procedure names and function names /// </summary> /// <param name="dname"></param> /// <returns></returns> public abstract TableName[] GetProcedureNames(DatabaseName dname);
public abstract DataTable GetDatabaseSchema(DatabaseName dname);
public override TableName[] GetViewNames(DatabaseName dname)
{
return(new TableName[] { });
}
public override bool Exists(DatabaseName dname)
{
return(true);
}
public TableName(DatabaseName databaseName, string schemaName, string tableName)
{
this.baseName = databaseName;
this.schema = schemaName;
this.tableName = tableName;
}
public DatabaseClause(DatabaseName databaseName)
{
this.databaseName = databaseName;
}
public override DependencyInfo[] GetDependencySchema(DatabaseName dname)
{
throw new NotImplementedException();
}
public override DataTable GetDatabaseSchema(DatabaseName dname)
{
throw new NotImplementedException();
}
public override TableName[] GetTableNames(DatabaseName dname)
{
return(InformationSchema.XmlTableNames(dname, dbSchema.Tables[dname.Name]));
}
public abstract DependencyInfo[] GetDependencySchema(DatabaseName dname);
public static DataTable FillDataTable(this DatabaseName dname, string script)
{
SqlCmd cmd = new SqlCmd(dname.Provider, script);
return(cmd.FillDataTable());
}
private string getSchemaFilName(DatabaseName dname) => string.Format("{0}\\{1}.{2}", getPath(dname.ServerName), dname.Name, EXT);
