public bool IsSatisfiedBy(Column existingType)
{
if (Type.HasValue && existingType.Type != Type)
{
return false;
}
if (Length.HasValue && existingType.Length != Length)
{
return false;
}
if (AllowNulls.HasValue && existingType.AllowNulls != AllowNulls)
{
return false;
}
if (Identity != null && !Identity.Equals(existingType.Identity))
{
return false;
}
return true;
}
private IList<Column> GetColumns(int objId)
{
/* To get primary keys/index
*
select unique_index_id from sys.key_constraints where parent_object_id = 741577680 and type = 'PK'
select * from sys.indexes where object_id = 741577680 and index_id = 1
--741577680
SELECT * FROM sys.index_columns where object_id = 741577680 and index_id = 1
SELECT * FROM sys.columns where object_id = 741577680 and column_id in (1,2)
* */
var columns = new List<Column>();
using (var conn = new SqlConnection(_connectionString))
{
var cmd = new SqlCommand(@"SELECT c.Name, t.Name [type], c.max_length, c.is_nullable, c.is_identity, ic.Seed_Value, ic.increment_value
FROM Sys.columns c
Inner Join Sys.Types t on c.user_Type_id = t.user_type_id
Left Join [sys].[identity_columns] ic ON ic.object_id = c.object_id and ic.column_id = c.column_id
where c.object_id = @objectId", conn);
cmd.Parameters.Add("@objectId", SqlDbType.Int).Value = objId;
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
var nameOrd = reader.GetOrdinal("Name");
var typeOrd = reader.GetOrdinal("type");
var lengthOrd = reader.GetOrdinal("max_length");
var allowNullsOrd = reader.GetOrdinal("is_nullable");
var identityOrd = reader.GetOrdinal("is_identity");
var identitySeedOrd = reader.GetOrdinal("Seed_Value");
var identityIncrementOrd = reader.GetOrdinal("increment_value");
while (reader.Read())
{
var column = new Column(reader.GetString(nameOrd));
SqlDbType type;
var typeStr = reader.GetString(typeOrd);
const bool ignoreCase = true;
if (Enum.TryParse(typeStr, ignoreCase, out type))
{
column.Type = type;
}
if (typeStr[0] == 'n')// unicode
{
column.Length = reader.GetInt16(lengthOrd)/2;
}
else
{
column.Length = reader.GetInt16(lengthOrd);
}
column.AllowNulls = reader.GetBoolean(allowNullsOrd);
//Only Int Identities supported at the moment
if (reader.GetBoolean(identityOrd) && column.Type == SqlDbType.Int)
{
var identity = new IdentityInt(reader.GetInt32(identitySeedOrd),
reader.GetInt32(identityIncrementOrd));
column.Identity = identity;
}
columns.Add(column);
}
}
}
return columns;
}