public IList<Column> GetSqlTableSchema(SqlTable mySqlTable, string mySqlDbOwner)
{
var mySqlTableColumns = new List<Column>();
SqlConnection mySqlConn = _connectionString.GetSqlConnectionInstance(_connectionString.ConnString);
mySqlConn.Open();
using (mySqlConn)
{
SqlCommand mySqlComm = _connectionString.GetSqlCommandInstance(mySqlConn);
mySqlComm.CommandText = SqlQuery.GetSqlForTableSchema(mySqlTable.SqlTableName, mySqlDbOwner);
SqlDataReader mySqlDataReader = _connectionString.GetSqlDataReaderInstance(mySqlComm,
CommandBehavior.
CloseConnection);
while (mySqlDataReader != null && mySqlDataReader.Read())
{
string columnName = mySqlDataReader.GetString(0);
string dataType = mySqlDataReader.GetString(1);
bool isNullable = mySqlDataReader.GetString(2).Equals("YES",
StringComparison.CurrentCultureIgnoreCase);
var characterMaxLenth = mySqlDataReader["character_maximum_length"] as int?;
var numericPrecision = mySqlDataReader["numeric_precision"] as int?;
var numericScale = mySqlDataReader["numeric_scale"] as int?;
bool isPrimaryKey = (!mySqlDataReader.IsDBNull(3)
? mySqlDataReader.GetString(3).Equals(
SqlServerConstType.PrimaryKey.ToString(),
StringComparison.CurrentCultureIgnoreCase)
: false);
bool isForeignKey = (!mySqlDataReader.IsDBNull(3)
? mySqlDataReader.GetString(3).Equals(
SqlServerConstType.ForeignKey.ToString(),
StringComparison.CurrentCultureIgnoreCase)
: false);
var m = new SqlDataType();
mySqlTableColumns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = isForeignKey,
MappedDataType =
m.MapFromDbType(dataType, characterMaxLenth, numericPrecision,
numericScale).ToString(),
DataLength = characterMaxLenth,
ConstraintName = mySqlDataReader["constraint_name"].ToString()
});
mySqlTable.Columns = mySqlTableColumns;
}
mySqlTable.PrimaryKey = GetPrimaryKeys(mySqlTable);
mySqlTable.ForeignKeys = GetForeignKeyReferences(mySqlTable);
mySqlTable.HasManyRelationships = ChecknGetHasManyRelationships(mySqlTable);
}
return mySqlTableColumns;
}
// http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
private IList<HasMany> ChecknGetHasManyRelationships(SqlTable sqlTable)
{
var hasManyRelationships = new List<HasMany>();
SqlConnection mySqlConn = _connectionString.GetSqlConnectionInstance(_connectionString.ConnString);
mySqlConn.Open();
using (mySqlConn)
{
using (SqlCommand mySqlComm = _connectionString.GetSqlCommandInstance())
{
mySqlComm.CommandText =
SqlQuery.GetSqlToFetchHasManyRelationships(sqlTable.SqlTableName);
mySqlComm.Connection = mySqlConn;
SqlDataReader mySqlDataReader = _connectionString.GetSqlDataReaderInstance(mySqlComm);
GetRefrences(hasManyRelationships, mySqlDataReader);
}
}
return hasManyRelationships;
}
private IList<ForeignKey> GetForeignKeyReferences(SqlTable sqltable)
{
List<string> constraints =
sqltable.Columns.Where(x => x.IsForeignKey.Equals(true)).Select(x => x.ConstraintName).Distinct().ToList
();
var foreignKeys = new List<ForeignKey>();
constraints.ForEach(c =>
{
Column[] fkColumns =
sqltable.Columns.Where(x => x.ConstraintName.Equals(c)).ToArray();
var fk = new ForeignKey
{
Name = fkColumns[0].Name,
References =
GetForeignKeyReferenceTableName(sqltable.SqlTableName,
fkColumns[0].Name),
AllColumnsNamesForTheSameConstraint =
fkColumns.Select(f => f.Name).ToArray()
};
foreignKeys.Add(fk);
});
SqlTable.SetUniqueNamesForForeignKeyProperties(foreignKeys);
return foreignKeys;
}
private static PrimaryKey GetPrimaryKeys(SqlTable sqltable)
{
IEnumerable<Column> primaryKeys = sqltable.Columns.Where(x => x.IsPrimaryKey.Equals(true));
switch (primaryKeys.Count())
{
case 1:
{
Column col = primaryKeys.First();
var pkey = new PrimaryKey
{
Type = PrimaryKeyType.PrimaryKey,
Columns =
{
new Column
{
DataType = col.DataType,
Name = col.Name
}
}
};
return pkey;
}
default:
{
var pkey = new PrimaryKey {Type = PrimaryKeyType.CompositeKey};
foreach (Column primaryKey in primaryKeys)
{
pkey.Columns.Add(new Column
{
DataType = primaryKey.DataType,
Name = primaryKey.Name
});
}
return pkey;
}
}
}