public List <SBD.DbObject> GetDbObjects(SBD.DbType dbType, SBD.DbObjectType objectType, string connectionString, bool includeProperties = true)
{
List <SBD.DbObject> dbObjects = new List <SBD.DbObject>();
using (var dbConnection = GetDbConnection(dbType, connectionString))
{
var tables = dbConnection.GetSchema(objectType == SBD.DbObjectType.Table ? "Tables" : "Views");
var catalogCol = dbType == SBD.DbType.SqlServer ? tables.Columns["TABLE_CATALOG"] : null;
var ownerCol = dbType == SBD.DbType.SqlServer ? tables.Columns["TABLE_SCHEMA"] : tables.Columns["OWNER"];
var tableNameCol = objectType == SBD.DbObjectType.View && dbType != SBD.DbType.SqlServer ? tables.Columns["VIEW_NAME"] : tables.Columns["TABLE_NAME"];
foreach (DataRow tableRow in tables.Rows)
{
var objectCatalog = dbType == SBD.DbType.SqlServer ? (string)tableRow[catalogCol] : string.Empty;
var objectOwner = (string)tableRow[ownerCol];
var objectName = (string)tableRow[tableNameCol];
var dbObject = new SBD.DbObject()
{
SchemaName = objectOwner,
Name = objectName,
ObjectType = objectType,
//Properties = includeProperties ? GetDbObjectProperties(dbType, objectType, dbConnection, objectCatalog, objectOwner, objectName)
// : new List<SBD.DbObjectProperty>()
};
dbObjects.Add(dbObject);
}
}
return(dbObjects);
}
public List <SBD.DbObjectProperty> GetDbObjectProperties(SBD.DbType dbType, SBD.DbObjectType objectType, DbConnection dbConnection, string objectCatalog, string objectOwner, string objectName)
{
List <SBD.DbObjectProperty> dbObjectProperties = new List <SBD.DbObjectProperty>();
var columns = dbType == SBD.DbType.SqlServer ?
dbConnection.GetSchema(objectType == SBD.DbObjectType.Table ? "Columns" : "ViewColumns", new string[] { objectCatalog, objectOwner, objectName }) :
dbConnection.GetSchema("Columns", new string[] { objectOwner, objectName });
var columnNameCol = columns.Columns["COLUMN_NAME"];
var columnTypeCol = dbType == SBD.DbType.SqlServer ? columns.Columns["DATA_TYPE"] : columns.Columns["DATATYPE"];
var lengthCol = dbType == SBD.DbType.SqlServer ? columns.Columns["CHARACTER_MAXIMUM_LENGTH"] : columns.Columns["LENGTH"];
var nullableCol = dbType == SBD.DbType.SqlServer ? columns.Columns["IS_NULLABLE"] : columns.Columns["NULLABLE"];
foreach (DataRow columnRow in columns.Rows)
{
var columnName = (string)columnRow[columnNameCol];
var columnType = columnTypeCol == null ? null : (string)columnRow[columnTypeCol];
var columnLength = lengthCol == null || columnRow.IsNull(lengthCol.Ordinal) ? 0 : int.Parse(columnRow[lengthCol].ToString());
var isNullable = nullableCol == null || columnRow.IsNull(nullableCol.Ordinal) ? true : ((string)columnRow[nullableCol]).ToLower()[0] == 'y';
var dbObjectProperty = new SBD.DbObjectProperty()
{
SchemaName = objectOwner,
ObjectName = objectName,
Name = columnName
};
dbObjectProperties.Add(dbObjectProperty);
}
return(dbObjectProperties);
}
public List <SBD.DbObject> GetDbObjects(SBD.DatabaseType dbType, SBD.DbObjectType objectType, string connectionString, bool includeProperties = true)
{
using (var dbConnection = GetDbConnection(dbType, connectionString))
{
return(GetDbObjects(dbType, dbConnection, objectType));
}
}
public List <SBD.DbObject> GetDbObjects(SBD.DatabaseType dbType, DbConnection dbConnection, SBD.DbObjectType objectType)
{
List <SBD.DbObject> dbObjects = new List <SBD.DbObject>();
var tables = dbConnection.GetSchema(objectType == SBD.DbObjectType.Table ? "Tables" : "Views");
var catalogCol = dbType == SBD.DatabaseType.SqlServer ? tables.Columns["TABLE_CATALOG"] : null;
var ownerCol = dbType == SBD.DatabaseType.SqlServer ? tables.Columns["TABLE_SCHEMA"] : tables.Columns["OWNER"];
var tableNameCol = objectType == SBD.DbObjectType.View && dbType != SBD.DatabaseType.SqlServer ? tables.Columns["VIEW_NAME"] : tables.Columns["TABLE_NAME"];
foreach (DataRow tableRow in tables.Rows)
{
var objectCatalog = dbType == SBD.DatabaseType.SqlServer ? (string)tableRow[catalogCol] : string.Empty;
var objectOwner = (string)tableRow[ownerCol];
var objectName = (string)tableRow[tableNameCol];
var dbObject = new SBD.DbObject()
{
SchemaName = objectOwner,
Name = objectName,
ObjectType = objectType
};
dbObjects.Add(dbObject);
}
return(dbObjects);
}
public List <SBD.DbObjectProperty> GetDbObjectProperties(SBD.DatabaseType dbType, SBD.DbObjectType objectType, DbConnection dbConnection)
{
List <SBD.DbObject> dbObjects = null;
List <SBD.DbObjectProperty> dbObjectProperties = new List <SBD.DbObjectProperty>();
var columns = dbType == SBD.DatabaseType.SqlServer ?
dbConnection.GetSchema(objectType == SBD.DbObjectType.Table ? "Columns" : "ViewColumns") :
dbConnection.GetSchema("Columns");
if (dbType == SBD.DatabaseType.Oracle)
{
dbObjects = GetDbObjects(dbType, dbConnection, objectType);
}
var ownerCol = dbType == SBD.DatabaseType.SqlServer ? columns.Columns["TABLE_SCHEMA"] : columns.Columns["OWNER"];
var tableNameCol = columns.Columns["TABLE_NAME"];
var columnNameCol = columns.Columns["COLUMN_NAME"];
var columnTypeCol = dbType == SBD.DatabaseType.SqlServer ? columns.Columns["DATA_TYPE"] : columns.Columns["DATATYPE"];
var lengthCol = dbType == SBD.DatabaseType.SqlServer ? columns.Columns["CHARACTER_MAXIMUM_LENGTH"] : columns.Columns["LENGTH"];
var nullableCol = dbType == SBD.DatabaseType.SqlServer ? columns.Columns["IS_NULLABLE"] : columns.Columns["NULLABLE"];
foreach (DataRow columnRow in columns.Rows)
{
var objectOwner = (string)columnRow[ownerCol];
var objectName = (string)columnRow[tableNameCol];
if (dbType == SBD.DatabaseType.SqlServer || (dbType == SBD.DatabaseType.Oracle && dbObjects.Any(o => o.SchemaName == objectOwner && o.Name == objectName)))
{
var columnName = (string)columnRow[columnNameCol];
var columnType = columnTypeCol == null ? null : ((string)columnRow[columnTypeCol]).ToUpper();
var columnLength = lengthCol == null || columnRow.IsNull(lengthCol.Ordinal) ? 0 : int.Parse(columnRow[lengthCol].ToString());
var isNullable = nullableCol == null || columnRow.IsNull(nullableCol.Ordinal) ? true : ((string)columnRow[nullableCol]).ToLower()[0] == 'y';
var dbObjectProperty = new SBD.DbObjectProperty()
{
SchemaName = objectOwner,
ObjectName = objectName,
Name = columnName,
ColumnType = columnType,
NetType = SBD.DbTypeConversion.SqlTypeToNetType(columnType),
ColumnLength = columnLength,
IsNullable = isNullable
};
dbObjectProperties.Add(dbObjectProperty);
}
}
return(dbObjectProperties);
}
