private void GetColumns()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
NULL [schema]
, c.TABLE_NAME [table]
, c.DATA_TYPE [typename]
, c.COLUMN_NAME [column_name]
, CAST(c.ORDINAL_POSITION as integer) [ordinal]
, CAST(CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [nullable]
, ix.ORDINAL_POSITION as [primary_key_ordinal]
, RTRIM(LTRIM(c.COLUMN_DEFAULT)) as [default_sql]
, c.NUMERIC_PRECISION [precision]
, c.NUMERIC_SCALE [scale]
, c.CHARACTER_MAXIMUM_LENGTH [max_length]
, CAST(CASE WHEN c.AUTOINC_INCREMENT IS NULL THEN 0 ELSE 1 END AS bit) [is_identity]
, CAST(CASE WHEN c.DATA_TYPE = 'rowversion' THEN 1 ELSE 0 END AS bit) [is_computed]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_NAME = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.INDEXES ix
ON c.TABLE_NAME = ix.TABLE_NAME AND c.COLUMN_NAME = ix.COLUMN_NAME AND ix.PRIMARY_KEY = 1
WHERE SUBSTRING(c.COLUMN_NAME, 1,5) != '__sys'
AND t.TABLE_TYPE = 'TABLE'
AND (SUBSTRING(t.TABLE_NAME, 1,2) <> '__');";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var tableName = reader.GetValueOrDefault <string>("table");
if (!_tableSelectionSet.Allows(tableName))
{
continue;
}
var dataTypeName = reader.GetValueOrDefault <string>("typename");
var nullable = reader.GetValueOrDefault <bool>("nullable");
var maxLength = reader.GetValueOrDefault <int?>("max_length");
int?precision = null;
int?scale = null;
if ((dataTypeName == "decimal") ||
(dataTypeName == "numeric"))
{
precision = reader.IsDBNull(8) ? default(int?) : Convert.ToInt32(reader[8], System.Globalization.CultureInfo.InvariantCulture);
scale = reader.IsDBNull(9) ? default(int?) : Convert.ToInt32(reader[9], System.Globalization.CultureInfo.InvariantCulture);
// maxlength here represents storage bytes. The server determines this, not the client.
maxLength = null;
}
if (dataTypeName == "rowversion")
{
maxLength = null;
}
if (maxLength.HasValue && (maxLength.Value > 8000))
{
maxLength = null;
}
var isIdentity = reader.GetValueOrDefault <bool>("is_identity");
var isComputed = reader.GetValueOrDefault <bool>("is_computed") || (dataTypeName == "rowversion");
var table = _tables[TableKey(tableName)];
var columnName = reader.GetValueOrDefault <string>("column_name");
var column = new ColumnModel
{
Table = table,
DataType = dataTypeName,
Name = columnName,
Ordinal = reader.GetValueOrDefault <int>("ordinal") - 1,
IsNullable = nullable,
PrimaryKeyOrdinal = reader.GetValueOrDefault <int?>("primary_key_ordinal"),
DefaultValue = reader.GetValueOrDefault <string>("default_sql"),
Precision = precision,
Scale = scale,
MaxLength = maxLength <= 0 ? default(int?) : maxLength,
ValueGenerated = isIdentity ?
ValueGenerated.OnAdd :
isComputed ?
ValueGenerated.OnAddOrUpdate : default(ValueGenerated?)
};
column.SqlCe().IsIdentity = isIdentity;
table.Columns.Add(column);
_tableColumns.Add(ColumnKey(table, column.Name), column);
}
}
}