/// <summary>
/// Gets all of the columns for a table.
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public virtual ColumnSchemaList GetColumnSchemas(TableSchema table)
{
IDictionary<string, string> constraints = GetConstraints(table.Name);
string tableName = table.Name;
ColumnSchemaList result = null;
string cmdtext = @"
select
case when c.autoinc_next is null then 0 else 1 end is_identity,
c.*
from information_schema.columns c
where c.table_name = @table_name
";
using (SqlCeCommand cmd = new SqlCeCommand(cmdtext))
{
cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = tableName;
using (IDataReader reader = _helper.ExecuteReader(cmd))
{
while (reader.Read())
{
if (result == null) result = new ColumnSchemaList();
SqlDbType sqlType = getSqlDbType(reader["DATA_TYPE"].ToString());
Type dataType = getDataType(reader["DATA_TYPE"].ToString());
string name = reader["COLUMN_NAME"].ToString();
int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1;
Dictionary<string, object> props = new Dictionary<string, object>();
props.Add("is_identity", Convert.ToBoolean(reader["is_identity"]));
props.Add("is_nullable", (reader["is_nullable"].ToString().Equals("YES")));
props.Add("is_computed", false);
props.Add("is_primary_key", constraints.ContainsKey(name) && constraints[name] == "PRIMARY KEY");
props.Add("is_foreign_key", constraints.ContainsKey(name) && constraints[name] == "FOREIGN KEY");
props.Add("formula", "");
result.Add(new ColumnSchema(this, table, sqlType, dataType, name, length, props));
}
}
}
return result;
}
/// <summary>
/// Gets all of the columns for a table.
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public virtual ColumnSchemaList GetColumnSchemas(TableSchema table)
{
string tableName = table.Name;
ColumnSchemaList result = null;
string cmdtext = @"
select distinct
case when cc.definition is null then 0 else 1 end as is_computed,
case when cc.definition is null then 0 else 1 end as formula,
isnull(columnproperty(object_id(c.table_name), c.column_name, 'IsIdentity'),0) as is_identity,
case when (select tc.constraint_type from information_schema.table_constraints tc where tc.table_name = kcu.table_name and kcu.constraint_name = tc.constraint_name and tc.constraint_type = 'PRIMARY KEY') is null then 0 else 1 end as 'is_primary_key',
case when (select tc.constraint_type from information_schema.table_constraints tc where tc.table_name = kcu.table_name and kcu.constraint_name = tc.constraint_name and tc.constraint_type = 'FOREIGN KEY') is null then 0 else 1 end as 'is_foreign_key',
c.*
from information_schema.columns c
left join information_schema.key_column_usage kcu
on c.table_name = kcu.table_name
and c.column_name = kcu.column_name
left join sys.computed_columns cc
on cc.[object_id] = object_id(@table_name)
and cc.[name] = c.column_name
where c.table_name = @table_name
";
try
{
using (SqlCommand cmd = new SqlCommand(cmdtext))
{
cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = tableName;
using (IDataReader reader = _helper.ExecuteReader(cmd))
{
while (reader.Read())
{
if (result == null) result = new ColumnSchemaList();
SqlDbType sqlType = getSqlDbType(reader["DATA_TYPE"].ToString());
Type dataType = getDataType(reader["DATA_TYPE"].ToString());
string name = reader["COLUMN_NAME"].ToString();
int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1;
if (length < 0 && dataType == typeof(string))
length = 8000; //patch for varchar(max), not sure if this works in all situations.
Dictionary<string, object> props = new Dictionary<string, object>();
props.Add("is_identity", Convert.ToBoolean(reader["is_identity"]));
props.Add("is_primary_key", Convert.ToBoolean(reader["is_primary_key"]));
props.Add("is_foreign_key", Convert.ToBoolean(reader["is_foreign_key"]));
props.Add("is_nullable", (reader["is_nullable"].ToString().Equals("YES")));
props.Add("is_computed", Convert.ToBoolean(reader["is_computed"]));
props.Add("formula", (reader["formula"] ?? "").ToString());
result.Add(new ColumnSchema(this, table, sqlType, dataType, name, length, props));
}
}
}
}
catch (Exception ex)
{
throw new SchemaProberException(String.Format("An error occurred while processing the table '{0}'", tableName), ex);
}
return result;
}
