/// <summary>
/// Gets the primary key for a table.
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public virtual PrimaryKeyColumnSchema GetPrimaryKey(TableSchema table)
{
PrimaryKeyColumnSchema result = null;
if (table == null)
return result;
string cmdtext = @"
select
columnproperty(object_id(c.table_name), c.column_name, 'IsIdentity') as is_identity,
c.*
from information_schema.table_constraints tc
inner join information_schema.key_column_usage kcu
on kcu.constraint_name = tc.constraint_name
and kcu.table_name = tc.table_name
inner join information_schema.columns c
on c.table_name = kcu.table_name
and c.column_name = kcu.column_name
where tc.constraint_type = 'PRIMARY KEY'
and c.table_name = @table_name
";
using (SqlCommand cmd = new SqlCommand(cmdtext))
{
cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = table.Name;
using (IDataReader reader = this._helper.ExecuteReader(cmd))
{
if (reader.Read())
{
SqlDbType sqlType = Enum<SqlDbType>.Parse(reader["DATA_TYPE"].ToString());
Type dataType = getDataType(reader["DATA_TYPE"].ToString());
string name = reader["COLUMN_NAME"].ToString();
Dictionary<string, object> props = new Dictionary<string, object>();
props.Add("is_identity", Convert.ToBoolean(reader["is_identity"]));
int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1;
props.Add("is_primary_key", true);
props.Add("is_foreign_key", false);
props.Add("is_nullable", (reader["is_nullable"].ToString().Equals("YES")));
result = new PrimaryKeyColumnSchema(this, table, sqlType, dataType, name, length, props);
}
}
}
return result;
}
/// <summary>
/// Gets all of the objects representing tables that have references to a primary key.
/// </summary>
/// <param name="primaryKey"></param>
/// <returns></returns>
public virtual ForeignKeyColumnSchemaList GetForeignKeyReferences(PrimaryKeyColumnSchema primaryKey)
{
ForeignKeyColumnSchemaList result = new ForeignKeyColumnSchemaList();
string cmdtext = @"
select distinct
col_name(fc.parent_object_id, fc.parent_column_id) as foreign_key_column,
object_name(fc.parent_object_id) as foreign_key_table,
col_name(fc.referenced_object_id, fc.referenced_column_id) as primary_key_column,
object_name(fc.referenced_object_id) as primary_key_table,
c.data_type,
c.character_maximum_length,
c.is_nullable,
c.table_name,
c.column_name
from sys.foreign_keys as f
inner join sys.foreign_key_columns as fc
on f.object_id = fc.constraint_object_id
inner join information_schema.columns c
on c.column_name = col_name(fc.parent_object_id, fc.parent_column_id)
and c.table_name = object_name(f.parent_object_id)
where object_name (f.referenced_object_id) = @table_name
";
using (SqlCommand cmd = new SqlCommand(cmdtext))
{
cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = primaryKey.Table.Name;
using (IDataReader reader = this._helper.ExecuteReader(cmd))
{
while (reader.Read())
{
string tableName = reader["TABLE_NAME"].ToString();
SqlDbType sqlType = Enum<SqlDbType>.Parse(reader["DATA_TYPE"].ToString());
Type dataType = getDataType(reader["DATA_TYPE"].ToString());
string name = reader["COLUMN_NAME"].ToString();
Dictionary<string, object> props = new Dictionary<string, object>();
props.Add("is_identity", false);
int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1;
props.Add("is_primary_key", false);
props.Add("is_foreign_key", true);
props.Add("primary_key_table", reader["primary_key_table"]);
props.Add("primary_key_column", reader["primary_key_column"]);
props.Add("foreign_key_column", reader["foreign_key_column"]);
props.Add("foreign_key_table", reader["foreign_key_table"]);
props.Add("is_nullable", (reader["is_nullable"].ToString().Equals("YES")));
result.Add(new ForeignKeyColumnSchema(this, new TableSchema(this, tableName), sqlType, dataType, name, length, props));
}
}
}
return result;
}