public Schema(Database database)
{
this.tableByName = new Dictionary<string, SchemaTable>();
this.tableTypeByName = new Dictionary<string, SchemaTableType>();
this.procedureByName = new Dictionary<string, SchemaProcedure>();
this.indexByIndexNameByTableName = new Dictionary<string, Dictionary<string, SchemaIndex>>();
using (var connection = new SqlConnection(database.ConnectionString))
{
connection.Open();
try
{
// Schema
var cmdText = @"
SELECT count(schema_name)
FROM information_schema.schemata
WHERE schema_name = @schemaName";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@schemaName", SqlDbType.NVarChar).Value = database.SchemaName;
var schemaCount = (int)command.ExecuteScalar();
this.Exists = schemaCount != 0;
}
// Tables
cmdText = @"
SELECT T.table_name,
C.column_name,
C.data_type,
C.character_maximum_length,
C.numeric_precision,
C.numeric_scale
FROM information_schema.tables AS T
FULL OUTER JOIN information_schema.columns AS C
ON T.table_name = C.table_name
WHERE T.table_type = 'BASE TABLE'
AND T.table_schema = @tableSchema
AND C.table_schema = @tableSchema";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@tableSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
var tableNameOrdinal = reader.GetOrdinal("table_name");
var columnNameOrdinal = reader.GetOrdinal("column_name");
var dataTypeOrdinal = reader.GetOrdinal("data_type");
var characterMaximumLengthOrdinal = reader.GetOrdinal("character_maximum_length");
var numericPrecisionOrdinal = reader.GetOrdinal("numeric_precision");
var numericScaleOrdinal = reader.GetOrdinal("numeric_scale");
while (reader.Read())
{
var tableName = reader.GetString(tableNameOrdinal);
var columnName = reader.GetString(columnNameOrdinal);
var dataType = reader.GetString(dataTypeOrdinal).Trim().ToLowerInvariant();
var characterMaximumLength = reader.IsDBNull(characterMaximumLengthOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(characterMaximumLengthOrdinal));
var numericPrecision = reader.IsDBNull(numericPrecisionOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(numericPrecisionOrdinal));
var numericScale = reader.IsDBNull(numericScaleOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(numericScaleOrdinal));
tableName = tableName.Trim().ToLowerInvariant();
tableName = database.Mapping.NormalizeName(tableName);
var fullyQualifiedTableName = database.SchemaName + "." + tableName;
columnName = database.Mapping.NormalizeName(columnName);
SchemaTable table;
if (!this.tableByName.TryGetValue(fullyQualifiedTableName, out table))
{
table = new SchemaTable(this, fullyQualifiedTableName);
this.tableByName[fullyQualifiedTableName] = table;
}
if (!reader.IsDBNull(columnNameOrdinal))
{
var column = new SchemaTableColumn(table, columnName, dataType, characterMaximumLength, numericPrecision, numericScale);
table.ColumnByLowercaseColumnName[column.LowercaseName] = column;
}
}
}
}
// Table Types
cmdText = @"
select tt.name as table_name, c.name as column_name, t.name AS data_type, c.max_length, c.precision, c.scale
from sys.table_types tt
inner join sys.columns c on c.object_id = tt.type_table_object_id
INNER JOIN sys.types AS t ON t.user_type_id = c.user_type_id
where tt.schema_id = SCHEMA_ID(@domainSchema)";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@domainSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
var tableNameOrdinal = reader.GetOrdinal("table_name");
var columnNameOrdinal = reader.GetOrdinal("column_name");
var dataTypeOrdinal = reader.GetOrdinal("data_type");
var maximumLengthOrdinal = reader.GetOrdinal("max_length");
var precisionOrdinal = reader.GetOrdinal("precision");
var scaleOrdinal = reader.GetOrdinal("scale");
while (reader.Read())
{
var tableName = reader.GetString(tableNameOrdinal);
var columnName = reader.GetString(columnNameOrdinal);
var dataType = reader.GetString(dataTypeOrdinal).Trim().ToLowerInvariant();
var maximumLength = reader.IsDBNull(maximumLengthOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(maximumLengthOrdinal));
var precision = reader.IsDBNull(precisionOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(precisionOrdinal));
var scale = reader.IsDBNull(scaleOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(scaleOrdinal));
tableName = tableName.Trim().ToLowerInvariant();
var fullyQualifiedTableName = database.SchemaName + "." + tableName;
SchemaTableType tableType;
if (!this.tableTypeByName.TryGetValue(fullyQualifiedTableName, out tableType))
{
tableType = new SchemaTableType(this, fullyQualifiedTableName);
this.tableTypeByName[fullyQualifiedTableName] = tableType;
}
if (!reader.IsDBNull(columnNameOrdinal))
{
var column = new SchemaTableTypeColumn(tableType, columnName, dataType, maximumLength, precision, scale);
tableType.ColumnByLowercaseColumnName[column.Name] = column;
}
}
}
}
// Procedures
cmdText = @"
SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE routine_schema = @routineSchema";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@routineSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var routineName = (string)reader["routine_name"];
var routineDefinition = (string)reader["routine_definition"];
var lowercaseRoutineName = routineName.Trim().ToLowerInvariant();
var fullyQualifiedName = database.SchemaName + "." + lowercaseRoutineName;
this.procedureByName[fullyQualifiedName] = new SchemaProcedure(this, routineName, routineDefinition);
}
}
}
// Indeces
cmdText = @"
SELECT o.name AS table_name,
i.name AS index_name
FROM
sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE
i.name IS NOT NULL
AND o.type = 'U'
AND i.type = 2
AND s.name = @tableSchema";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@tableSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
var tableNameOrdinal = reader.GetOrdinal("table_name");
var indexNameOrdinal = reader.GetOrdinal("index_name");
while (reader.Read())
{
var tableName = reader.GetString(tableNameOrdinal);
var indexName = reader.GetString(indexNameOrdinal);
tableName = tableName.Trim().ToLowerInvariant();
indexName = indexName.Trim().ToLowerInvariant();
Dictionary<string, SchemaIndex> indexByLowercaseIndexName;
if (!this.indexByIndexNameByTableName.TryGetValue(tableName, out indexByLowercaseIndexName))
{
indexByLowercaseIndexName = new Dictionary<string, SchemaIndex>();
this.indexByIndexNameByTableName[tableName] = indexByLowercaseIndexName;
}
SchemaIndex index;
if (!indexByLowercaseIndexName.TryGetValue(indexName, out index))
{
index = new SchemaIndex(this, indexName);
indexByLowercaseIndexName[indexName] = index;
}
}
}
}
}
finally
{
connection.Close();
}
}
}
public Schema(Database database)
{
this.tableByName = new Dictionary <string, SchemaTable>();
this.tableTypeByName = new Dictionary <string, SchemaTableType>();
this.procedureByName = new Dictionary <string, SchemaProcedure>();
this.indexByIndexNameByTableName = new Dictionary <string, Dictionary <string, SchemaIndex> >();
using (var connection = new SqlConnection(database.ConnectionString))
{
connection.Open();
try
{
// Schema
var cmdText = @"
SELECT count(schema_name)
FROM information_schema.schemata
WHERE schema_name = @schemaName";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@schemaName", SqlDbType.NVarChar).Value = database.SchemaName;
var schemaCount = (int)command.ExecuteScalar();
this.Exists = schemaCount != 0;
}
// Tables
cmdText = @"
SELECT T.table_name,
C.column_name,
C.data_type,
C.character_maximum_length,
C.numeric_precision,
C.numeric_scale
FROM information_schema.tables AS T
FULL OUTER JOIN information_schema.columns AS C
ON T.table_name = C.table_name
WHERE T.table_type = 'BASE TABLE'
AND T.table_schema = @tableSchema
AND C.table_schema = @tableSchema";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@tableSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
var tableNameOrdinal = reader.GetOrdinal("table_name");
var columnNameOrdinal = reader.GetOrdinal("column_name");
var dataTypeOrdinal = reader.GetOrdinal("data_type");
var characterMaximumLengthOrdinal = reader.GetOrdinal("character_maximum_length");
var numericPrecisionOrdinal = reader.GetOrdinal("numeric_precision");
var numericScaleOrdinal = reader.GetOrdinal("numeric_scale");
while (reader.Read())
{
var tableName = reader.GetString(tableNameOrdinal);
var columnName = reader.GetString(columnNameOrdinal);
var dataType = reader.GetString(dataTypeOrdinal).Trim().ToLowerInvariant();
var characterMaximumLength = reader.IsDBNull(characterMaximumLengthOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(characterMaximumLengthOrdinal));
var numericPrecision = reader.IsDBNull(numericPrecisionOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(numericPrecisionOrdinal));
var numericScale = reader.IsDBNull(numericScaleOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(numericScaleOrdinal));
tableName = tableName.Trim().ToLowerInvariant();
tableName = database.Mapping.NormalizeName(tableName);
var fullyQualifiedTableName = database.SchemaName + "." + tableName;
columnName = database.Mapping.NormalizeName(columnName);
SchemaTable table;
if (!this.tableByName.TryGetValue(fullyQualifiedTableName, out table))
{
table = new SchemaTable(this, fullyQualifiedTableName);
this.tableByName[fullyQualifiedTableName] = table;
}
if (!reader.IsDBNull(columnNameOrdinal))
{
var column = new SchemaTableColumn(table, columnName, dataType, characterMaximumLength, numericPrecision, numericScale);
table.ColumnByLowercaseColumnName[column.LowercaseName] = column;
}
}
}
}
// Table Types
cmdText = @"
select tt.name as table_name, c.name as column_name, t.name AS data_type, c.max_length, c.precision, c.scale
from sys.table_types tt
inner join sys.columns c on c.object_id = tt.type_table_object_id
INNER JOIN sys.types AS t ON t.user_type_id = c.user_type_id
where tt.schema_id = SCHEMA_ID(@domainSchema)";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@domainSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
var tableNameOrdinal = reader.GetOrdinal("table_name");
var columnNameOrdinal = reader.GetOrdinal("column_name");
var dataTypeOrdinal = reader.GetOrdinal("data_type");
var maximumLengthOrdinal = reader.GetOrdinal("max_length");
var precisionOrdinal = reader.GetOrdinal("precision");
var scaleOrdinal = reader.GetOrdinal("scale");
while (reader.Read())
{
var tableName = reader.GetString(tableNameOrdinal);
var columnName = reader.GetString(columnNameOrdinal);
var dataType = reader.GetString(dataTypeOrdinal).Trim().ToLowerInvariant();
var maximumLength = reader.IsDBNull(maximumLengthOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(maximumLengthOrdinal));
var precision = reader.IsDBNull(precisionOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(precisionOrdinal));
var scale = reader.IsDBNull(scaleOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(scaleOrdinal));
tableName = tableName.Trim().ToLowerInvariant();
var fullyQualifiedTableName = database.SchemaName + "." + tableName;
SchemaTableType tableType;
if (!this.tableTypeByName.TryGetValue(fullyQualifiedTableName, out tableType))
{
tableType = new SchemaTableType(this, fullyQualifiedTableName);
this.tableTypeByName[fullyQualifiedTableName] = tableType;
}
if (!reader.IsDBNull(columnNameOrdinal))
{
var column = new SchemaTableTypeColumn(tableType, columnName, dataType, maximumLength, precision, scale);
tableType.ColumnByLowercaseColumnName[column.Name] = column;
}
}
}
}
// Procedures
cmdText = @"
SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE routine_schema = @routineSchema";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@routineSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var routineName = (string)reader["routine_name"];
var routineDefinition = (string)reader["routine_definition"];
var lowercaseRoutineName = routineName.Trim().ToLowerInvariant();
var fullyQualifiedName = database.SchemaName + "." + lowercaseRoutineName;
this.procedureByName[fullyQualifiedName] = new SchemaProcedure(this, routineName, routineDefinition);
}
}
}
// Indeces
cmdText = @"
SELECT o.name AS table_name,
i.name AS index_name
FROM
sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE
i.name IS NOT NULL
AND o.type = 'U'
AND i.type = 2
AND s.name = @tableSchema" ;
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@tableSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
var tableNameOrdinal = reader.GetOrdinal("table_name");
var indexNameOrdinal = reader.GetOrdinal("index_name");
while (reader.Read())
{
var tableName = reader.GetString(tableNameOrdinal);
var indexName = reader.GetString(indexNameOrdinal);
tableName = tableName.Trim().ToLowerInvariant();
indexName = indexName.Trim().ToLowerInvariant();
Dictionary <string, SchemaIndex> indexByLowercaseIndexName;
if (!this.indexByIndexNameByTableName.TryGetValue(tableName, out indexByLowercaseIndexName))
{
indexByLowercaseIndexName = new Dictionary <string, SchemaIndex>();
this.indexByIndexNameByTableName[tableName] = indexByLowercaseIndexName;
}
SchemaIndex index;
if (!indexByLowercaseIndexName.TryGetValue(indexName, out index))
{
index = new SchemaIndex(this, indexName);
indexByLowercaseIndexName[indexName] = index;
}
}
}
}
}
finally
{
connection.Close();
}
}
}