/// <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
case when c.autoinc_next is null then 0 else 1 end 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 (SqlCeCommand cmd = new SqlCeCommand(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();
return result;
// string cmdtext = @"
//select
// 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 (SqlCeCommand cmd = new SqlCeCommand(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("is_nullable", (reader["is_nullable"].ToString().Equals("YES")));
// result.Add(new ForeignKeyColumnSchema(this, new TableSchema(this, tableName), sqlType, dataType, name, length, props));
// }
// }
// }
// return result;
}
