//----------------------------------------------------------------------------------------------------
public static SchemaInfo[] Schemas__GetAll(int connectionId)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new List <SchemaInfo>();
string connectionString = DataAccess.SQLObjectsCRUD.ASPdb_Connection__Get(connectionId).GetConnectionString();
string sql = @"
SELECT t1.Schema_Id, t1.principal_id,
t2.Catalog_Name, t2.Schema_Name, t2.Schema_Owner FROM sys.schemas t1
Inner Join INFORMATION_SCHEMA.SCHEMATA t2 on t1.name = t2.SCHEMA_NAME
where t2.SCHEMA_OWNER = 'dbo'
order by t1.name
";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionString, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var schemaInfo = new SchemaInfo()
{
SchemaId = reader.Get("Schema_Id", -1),
PrincipalId = reader.Get("Principal_Id", -1),
CatalogName = reader.Get("Catalog_Name", ""),
SchemaName = reader.Get("Schema_Name", ""),
SchemaOwner = reader.Get("Schema_Owner", "")
};
rtn.Add(schemaInfo);
}
}
}
return(rtn.ToArray());
}
//----------------------------------------------------------------------------------------------------
public static PrimaryKey PrimaryKey__Get(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
PrimaryKey rtn = null;
var colDict = new Dictionary <string, Column>();
foreach (var item in tableStructure.Columns)
{
if (!colDict.ContainsKey(item.ColumnName.ToLower()))
{
colDict.Add(item.ColumnName.ToLower(), item);
}
}
var primaryKeyColumns_List = new List <PrimaryKeyColumn>();
string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Select(tableStructure);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
if (rtn == null)
{
rtn = new PrimaryKey()
{
ConnectionId = tableStructure.ConnectionId,
TableId = tableStructure.TableId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
ConstraintName = reader.Get("ConstraintName", "")
}
}
;
var primaryKeyItem = new PrimaryKeyColumn()
{
ColumnName = reader.Get("ColumnName", ""),
OrdinalPosition = reader.Get("OrdinalPosition", 0)
};
if (colDict.ContainsKey(primaryKeyItem.ColumnName.ToLower()))
{
primaryKeyItem.Identity = colDict[primaryKeyItem.ColumnName.ToLower()].Identity;
}
primaryKeyColumns_List.Add(primaryKeyItem);
}
}
}
if (rtn != null)
{
rtn.Columns = primaryKeyColumns_List.ToArray();
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
private static Column[] Tables__Get__GetColumns(TableStructure tableStructure)
{
var rtn = new List <Column>();
string sql = SQLServer_SQLBuilders.BuildSql__Column_SelectAll(tableStructure);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var column = new Column()
{
ConnectionId = tableStructure.ConnectionId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
OrdinalPosition = reader.Get("OrdinalPosition", 0),
ColumnName = reader.Get("ColumnName", ""),
DataType_Name = reader.Get("DataType", ""),
MaxLength = reader.Get("MaxLength", 0),
Precision = reader.Get("Precision", 0),
Scale = reader.Get("Scale", 0),
AllowNulls = reader.Get("AllowNulls", "NO").ToUpper() == "YES",
DefaultValue = reader.GetString_OrNullDefault("DefaultValue"),
IsPrimaryKey = reader.Get("IsPrimaryKey", 0) == 1,
IsIdentity = reader.Get("IsIdentity", 0) == 1,
Identity = null
};
if (column.IsIdentity)
{
column.Identity = new Identity()
{
ConnectionId = tableStructure.ConnectionId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
ColumnName = reader.Get("ColumnName", ""),
Seed = reader.Get("Identity_Seed", 0),
Increment = reader.Get("Identity_Increment", 0),
CurrentIdentity = reader.Get("Identity_CurrentIdentity", 0)
}
}
;
column.Populate__DataType_FullString();
column.Fix_DefaultValue_ForDisplay();
rtn.Add(column);
}
}
}
return(rtn.ToArray());
}
//----------------------------------------------------------------------------------------------------
public static DefaultConstraint DefaultConstraint__Get(Column column)
{
AjaxService.ASPdatabaseService.GetSetVal();
var dict = new Dictionary <string, DefaultConstraint>();
string sql = SQLServer_SQLBuilders.BuildSql__DefaultConstraint_Select(column);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(column.ConnectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
string columnName = reader.Get("ColumnName", "");
string columnName_L = columnName.ToLower();
var defaultConstraint = new DefaultConstraint()
{
ConnectionId = column.ConnectionId,
Schema = column.Schema,
TableName = column.TableName,
ColumnName = reader.Get("ColumnName", ""),
DefaultConstraintName = reader.Get("DefaultConstraintName", ""),
DefaultDefinition = reader.Get("DefaultDefinition", "")
};
if (!dict.ContainsKey(columnName_L))
{
dict.Add(columnName_L, defaultConstraint);
}
}
}
}
if (dict.ContainsKey(column.ColumnName.ToLower()))
{
return(dict[column.ColumnName.ToLower()]);
}
else
{
return(null);
}
}
//----------------------------------------------------------------------------------------------------
/// <summary>
/// Key == lowercase ConstraintName.
/// Value == ConstraintName.
/// </summary>
public static Dictionary <string, string> Constraint__GetAll_InConnection(int connectionId)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new Dictionary <string, string>();
string sql = SQLServer_SQLBuilders.BuildSql__Constraint_SelectAll();
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
string constraintName = reader.Get("ConstraintName", "");
string constraintName_L = constraintName.ToLower();
if (!rtn.ContainsKey(constraintName_L))
{
rtn.Add(constraintName_L, constraintName);
}
}
}
}
return(rtn);
}