public Columns(string owner, string tableName)
{
_tableName = tableName;
Owner = owner;
Sql = @"select c.TABLE_SCHEMA,
c.TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
DATETIME_PRECISION
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME
where
(c.TABLE_SCHEMA = @Owner or (@Owner is null)) and
(c.TABLE_NAME = @TableName or (@TableName is null)) AND
TABLE_TYPE = 'BASE TABLE'
order by
c.TABLE_SCHEMA, c.TABLE_NAME, ORDINAL_POSITION";
var keyMap = new ColumnsKeyMap();
_converter = new ColumnRowConverter(keyMap);
}
public ViewColumns(string owner, string viewName, string[] additionalViewColumnPropertyNames, int commandTimeout) : base(additionalViewColumnPropertyNames, commandTimeout)
{
_viewName = viewName;
Owner = owner;
Sql = @"select c.TABLE_SCHEMA,
c.TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
DATETIME_PRECISION
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.VIEWS v
ON c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
where
(c.TABLE_SCHEMA = @Owner or (@Owner is null)) and
(c.TABLE_NAME = @TableName or (@TableName is null))
order by
c.TABLE_SCHEMA, c.TABLE_NAME, ORDINAL_POSITION";
var keyMap = new ColumnsKeyMap();
_converter = new ColumnRowConverter(keyMap);
}
private void ConvertDataTable()
{
var columnsKeyMap = new ColumnsKeyMap(ColumnsDataTable);
foreach (DataRowView row in ColumnsDataTable.DefaultView)
{
var column = new DatabaseColumn();
column.Name = row[columnsKeyMap.Key].ToString();
column.TableName = row[columnsKeyMap.TableKey].ToString();
if (!string.IsNullOrEmpty(columnsKeyMap.OrdinalKey))
{
column.Ordinal = Convert.ToInt32(row[columnsKeyMap.OrdinalKey], CultureInfo.CurrentCulture);
}
if (!string.IsNullOrEmpty(columnsKeyMap.DatatypeKey))
{
column.DbDataType = row[columnsKeyMap.DatatypeKey].ToString();
}
AddNullability(row, columnsKeyMap.NullableKey, column);
//the length unless it's an OleDb blob or clob
if (!string.IsNullOrEmpty(columnsKeyMap.LengthKey))
{
column.Length = GetNullableInt(row[columnsKeyMap.LengthKey]);
}
if (!string.IsNullOrEmpty(columnsKeyMap.PrecisionKey))
{
column.Precision = GetNullableInt(row[columnsKeyMap.PrecisionKey]);
}
if (!string.IsNullOrEmpty(columnsKeyMap.ScaleKey))
{
column.Scale = GetNullableInt(row[columnsKeyMap.ScaleKey]);
}
if (columnsKeyMap.DateTimePrecision != null)
{
column.DateTimePrecision = GetNullableInt(row[columnsKeyMap.DateTimePrecision]);
}
AddColumnDefault(row, columnsKeyMap.DefaultKey, column);
if (!string.IsNullOrEmpty(columnsKeyMap.PrimaryKeyKey) && (bool)row[columnsKeyMap.PrimaryKeyKey])
{
column.IsPrimaryKey = true;
}
if (!string.IsNullOrEmpty(columnsKeyMap.AutoIncrementKey) && (bool)row[columnsKeyMap.AutoIncrementKey])
{
column.IsIdentity = true;
}
if (!string.IsNullOrEmpty(columnsKeyMap.UniqueKey) && CastToBoolean(row, columnsKeyMap.UniqueKey))
{
column.IsUniqueKey = true;
}
_list.Add(column);
}
// Sort columns according to ordinal to get the original order in CREATE TABLE
_list.Sort((x, y) => x.Ordinal.CompareTo(y.Ordinal));
}
protected override ColumnsKeyMap LoadColumnsKeyMap()
{
var columnsKeyMap = new ColumnsKeyMap(ColumnsDataTable);
if (ColumnsDataTable.Columns.Contains("VIEW_NAME"))
{
columnsKeyMap.TableKey = "VIEW_NAME";
}
return(columnsKeyMap);
}
public Columns(string owner, string tableName, string[] additionalPropertyNames, int commandTimeout) : base(additionalPropertyNames, commandTimeout)
{
_tableName = tableName;
Owner = owner;
Sql = @"select c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
c.ORDINAL_POSITION,
c.COLUMN_DEFAULT,
c.IS_NULLABLE,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.NUMERIC_PRECISION,
c.NUMERIC_SCALE,
c.DATETIME_PRECISION
{0}
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME
{1}
where
(c.TABLE_SCHEMA = @Owner or (@Owner is null)) and
(c.TABLE_NAME = @TableName or (@TableName is null)) AND
TABLE_TYPE = 'BASE TABLE'
order by
c.TABLE_SCHEMA, c.TABLE_NAME, ORDINAL_POSITION";
AdditionalPropertiesJoin = @"LEFT OUTER JOIN sys.tables syst
ON c.TABLE_SCHEMA = schema_name(syst.schema_id) AND
c.TABLE_NAME = syst.name
LEFT OUTER JOIN sys.columns {ai} ON
syst.object_id = {ai}.object_id AND
c.COLUMN_NAME = {ai}.Name".Replace("{ai}", ADDITIONAL_INFO);
var keyMap = new ColumnsKeyMap();
_converter = new ColumnRowConverter(keyMap);
}
public void KeyMapForColumnSchemaTest()
{
//arrange
var dataTable = new DataTable("Columns")
{
Locale = CultureInfo.InvariantCulture
};
dataTable.Columns.Add("CatalogName", typeof(string));
dataTable.Columns.Add("SchemaName", typeof(string));
dataTable.Columns.Add("TableName", typeof(string));
dataTable.Columns.Add("ColumnName", typeof(string));
dataTable.Columns.Add("OrdinalPosition", typeof(int));
dataTable.Columns.Add("ColumnDefault", typeof(string));
dataTable.Columns.Add("IsNullable", typeof(bool));
dataTable.Columns.Add("DataType", typeof(string));
dataTable.Columns.Add("CharacterMaximumLength", typeof(int));
dataTable.Columns.Add("CharacterOctetLength", typeof(int));
dataTable.Columns.Add("NumericPrecision", typeof(int));
dataTable.Columns.Add("NumericPrecisionRadix", typeof(int));
dataTable.Columns.Add("NumericScale", typeof(int));
//act
var keymap = new ColumnsKeyMap(dataTable);
//assert
Assert.AreEqual("SchemaName", keymap.SchemaKey);
Assert.AreEqual("TableName", keymap.TableKey);
Assert.AreEqual("ColumnName", keymap.Key);
Assert.AreEqual("OrdinalPosition", keymap.OrdinalKey);
Assert.AreEqual("ColumnDefault", keymap.DefaultKey);
Assert.AreEqual("IsNullable", keymap.NullableKey);
Assert.AreEqual("datatype", keymap.DatatypeKey);
Assert.AreEqual("CharacterMaximumLength", keymap.LengthKey);
Assert.AreEqual("NumericPrecision", keymap.PrecisionKey);
Assert.AreEqual("NumericScale", keymap.ScaleKey);
}
private void ConvertDataTable()
{
var columnsKeyMap = new ColumnsKeyMap(ColumnsDataTable);
var hasIsUnsigned = !string.IsNullOrEmpty(columnsKeyMap.IsUnsignedKey);
foreach (DataRowView row in ColumnsDataTable.DefaultView)
{
var column = new DatabaseColumn();
column.Name = row[columnsKeyMap.Key].ToString();
column.TableName = row[columnsKeyMap.TableKey].ToString();
if (!string.IsNullOrEmpty(columnsKeyMap.SchemaKey))
{
column.SchemaOwner = row[columnsKeyMap.SchemaKey].ToString();
}
if (string.Equals("sqlite_default_schema", column.SchemaOwner, StringComparison.OrdinalIgnoreCase))
{
column.SchemaOwner = string.Empty;
}
if (!string.IsNullOrEmpty(columnsKeyMap.OrdinalKey))
{
column.Ordinal = Convert.ToInt32(row[columnsKeyMap.OrdinalKey], CultureInfo.CurrentCulture);
}
if (!string.IsNullOrEmpty(columnsKeyMap.DatatypeKey))
{
column.DbDataType = row[columnsKeyMap.DatatypeKey].ToString();
}
if (hasIsUnsigned && CastToBoolean(row, columnsKeyMap.IsUnsignedKey))
{
column.DbDataType += " unsigned";
}
AddNullability(row, columnsKeyMap.NullableKey, column);
//the length unless it's an OleDb blob or clob
if (!string.IsNullOrEmpty(columnsKeyMap.LengthKey))
{
column.Length = GetNullableInt(row[columnsKeyMap.LengthKey]);
}
if (!string.IsNullOrEmpty(columnsKeyMap.DataLengthKey))
{
//oracle only
var dataLength = GetNullableInt(row[columnsKeyMap.DataLengthKey]);
//column length already set for char/varchar. For other data types, get data length
if (column.Length < 1)
{
column.Length = dataLength;
}
}
if (!string.IsNullOrEmpty(columnsKeyMap.PrecisionKey))
{
column.Precision = GetNullableInt(row[columnsKeyMap.PrecisionKey]);
}
if (!string.IsNullOrEmpty(columnsKeyMap.ScaleKey))
{
column.Scale = GetNullableInt(row[columnsKeyMap.ScaleKey]);
}
if (columnsKeyMap.DateTimePrecision != null)
{
column.DateTimePrecision = GetNullableInt(row[columnsKeyMap.DateTimePrecision]);
}
AddColumnDefault(row, columnsKeyMap.DefaultKey, column);
if (!string.IsNullOrEmpty(columnsKeyMap.PrimaryKeyKey) && (bool)row[columnsKeyMap.PrimaryKeyKey])
{
column.IsPrimaryKey = true;
}
if (!string.IsNullOrEmpty(columnsKeyMap.AutoIncrementKey) && (bool)row[columnsKeyMap.AutoIncrementKey])
{
column.IsAutoNumber = true;
}
if (!string.IsNullOrEmpty(columnsKeyMap.UniqueKey) && CastToBoolean(row, columnsKeyMap.UniqueKey))
{
column.IsUniqueKey = true;
}
_list.Add(column);
}
// Sort columns according to ordinal to get the original order in CREATE TABLE
_list.Sort((x, y) => x.Ordinal.CompareTo(y.Ordinal));
}
public ColumnRowConverter(ColumnsKeyMap keyMap)
{
_keyMap = keyMap;
}
