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; }