public List <TableDefinition> GetBy(string connectionName, string schema)
{
var where = @"WHERE t.TABLE_TYPE = 'BASE TABLE' AND t.TABLE_SCHEMA = @schema AND t.TABLE_NAME <> 'sysdiagrams'";
var command = new SqlDataCommand($"{SqlQuery} {where} {OrderBy}", connectionName)
{
Parameters = { ["@schema"] = schema }
};
return(GetTableDefinitions(command));
}
public List <ColumnDefinition> GetBy(string connectionName, string schema)
{
var where = @"AND c.TABLE_SCHEMA = @schema";
var command = new SqlDataCommand($"{SqlQuery} {where} {OrderBy}", connectionName)
{
Parameters = { ["@schema"] = schema }
};
return(GetColumnDefinitions(command));
}
private List <ColumnDefinition> GetColumnDefinitions(SqlDataCommand dataCommand)
{
var columnDefinitions = new List <ColumnDefinition>();
void GetColumnDefinitions(SqlDataReader dataReader)
{
while (dataReader.Read())
{
var tableSchema = dataReader.GetString(0);
var tableName = dataReader.GetString(1);
var columnName = dataReader.GetString(2);
var dataType = dataReader.GetString(3);
var constraint = GetConstraint(dataReader);
var existingColumnDefinition = columnDefinitions.FirstOrDefault(cd =>
cd.TableSchema == tableSchema &&
cd.TableName == tableName &&
cd.ColumnName == columnName &&
cd.DataType == dataType);
if (existingColumnDefinition != null)
{
existingColumnDefinition.Constraints.Add(constraint);
continue;
}
var columnDefinition = new ColumnDefinition
{
TableSchema = tableSchema,
TableName = tableName,
ColumnName = columnName,
DataType = dataType,
CharacterMaxLength = !dataReader.IsDBNull(4) ? dataReader.GetInt32(4) : (long?)null,
IsNullable = dataReader.GetBoolean(5),
Constraints = constraint == null ? null : new List <Constraint> {
constraint
},
IsComputed = dataReader.GetBoolean(8),
IsIdentity = dataReader.GetBoolean(9),
IsGenerated = dataReader.GetBoolean(10),
HasDefault = dataReader.GetBoolean(11)
};
columnDefinitions.Add(columnDefinition);
}
}
ExecuteQuery(() => dataCommand, GetColumnDefinitions);
return(columnDefinitions);
}
public ColumnDefinition GetBy(string connectionName, string schema, string table, string column)
{
var where = @"AND c.TABLE_SCHEMA = @schema AND c.TABLE_NAME = @table AND c.COLUMN_NAME = @column";
var command = new SqlDataCommand($"{SqlQuery} {where} {OrderBy}", connectionName)
{
Parameters =
{
["@schema"] = schema,
["@table"] = table,
["@column"] = column
}
};
//TODO: if column two key types, multiple rows returned!!!
return(GetColumnDefinitions(command).FirstOrDefault());
}
public static SqlCommand ToSqlCommand(this SqlDataCommand sqlDataCommand, ISqlConnectionManager connectionManager)
{
var sqlCommand = new SqlCommand(sqlDataCommand.CommandText);
if (sqlDataCommand.Parameters != null && sqlDataCommand.Parameters.Count > 0)
{
sqlCommand.Parameters.AddRange(sqlDataCommand.Parameters.Select(p => new SqlParameter(p.Key, p.Value)).ToArray());
}
if (!string.IsNullOrEmpty(sqlDataCommand.ConnectionName))
{
sqlCommand.Connection = connectionManager.GetConnection(sqlDataCommand.ConnectionName);
}
return(sqlCommand);
}
private List <TableDefinition> GetTableDefinitions(SqlDataCommand dataCommand)
{
var tableDefinitions = new List <TableDefinition>();
void GetTableDefinitions(SqlDataReader dataReader)
{
while (dataReader.Read())
{
var tableDefinition = new TableDefinition
{
DatabaseName = dataReader.GetString(0),
TableSchema = dataReader.GetString(1),
TableName = dataReader.GetString(2)
};
tableDefinitions.Add(tableDefinition);
}
}
ExecuteQuery(() => dataCommand, GetTableDefinitions);
return(tableDefinitions);
}
