public static SqlServerView SqlServerViewCollectionToView(DataRow sqlServerView, DataTable sqlServerColumnsColletions) { SqlServerView view = new SqlServerView(); view.Name = sqlServerView["table_name"].ToString(); view.Schema = sqlServerView["table_schema"].ToString(); foreach (DataRow row in sqlServerColumnsColletions.Rows) { SqlServerColumn column = new SqlServerColumn(); SqlServerDbDataType dbDataType = new SqlServerDbDataType(); column.Name = row["ColumnName"].ToString(); column.IsNullable = (bool)row["AllowDBNull"]; dbDataType.ProviderType = SqlServerDataTypeConverter.SqlDbType2DatabaseType(row["ProviderType"]); dbDataType.Type = (Type)row["DataType"]; dbDataType.Size = (int)row["ColumnSize"]; dbDataType.Precision = (Int16)row["NumericPrecision"]; dbDataType.Scale = (Int16)row["NumericScale"]; column.DbDataType = dbDataType; view.Columns.Add(column); } return(view); }
public static SqlServerTable SqlServerTableCollectionToTable(DataRow sqlServerTable, DataTable sqlServerColumnsColletions, DataTable sqlServerForeignKeysCollection) { SqlServerTable table = new SqlServerTable(); table.Name = sqlServerTable["table_name"].ToString(); table.Schema = sqlServerTable["table_schema"].ToString(); DataRow[] rows = sqlServerColumnsColletions.Select("", "ColumnName ASC"); foreach (DataRow row in rows) { SqlServerColumn column = new SqlServerColumn(); SqlServerDbDataType dbDataType = new SqlServerDbDataType(); column.Name = row["ColumnName"].ToString(); column.IsPrimaryKey = (bool)row["IsKey"]; column.IsNullable = (bool)row["AllowDBNull"]; column.IsUnique = (bool)row["IsUnique"]; column.IsIdentity = (bool)row["IsIdentity"]; column.IsReadOnly = (bool)row["IsReadOnly"]; dbDataType.ProviderType = SqlServerDataTypeConverter.SqlDbType2DatabaseType(row["ProviderType"]); dbDataType.Type = (Type)row["DataType"]; dbDataType.Size = (int)row["ColumnSize"]; dbDataType.Precision = (Int16)row["NumericPrecision"]; dbDataType.Scale = (Int16)row["NumericScale"]; column.DbDataType = dbDataType; table.Columns.Add(column); } foreach (DataRow row in sqlServerForeignKeysCollection.Rows) { string columnName = row["ColumnName"].ToString(); table.Columns.Find( delegate(Column column) { return(column.Name.Equals(columnName)); }).IsForeignKey = true; } return(table); }
string GetClrType(SqlServerTable table, SqlServerColumn column) { string sqlDataType = column.SqlDataType; switch (sqlDataType) { case "bigint": return(typeof(long).FullName); case "smallint": return(typeof(short).FullName); case "int": return(typeof(int).FullName); case "uniqueidentifier": return(typeof(Guid).FullName); case "smalldatetime": case "datetime": case "datetime2": case "date": case "time": return(typeof(DateTime).FullName); case "datetimeoffset": return(typeof(DateTimeOffset).FullName); case "float": return(typeof(double).FullName); case "real": return(typeof(float).FullName); case "numeric": case "smallmoney": case "decimal": case "money": return(typeof(decimal).FullName); case "tinyint": return(typeof(byte).FullName); case "bit": return(typeof(bool).FullName); case "image": case "binary": case "varbinary": case "timestamp": return(typeof(byte[]).FullName); case "nvarchar": case "varchar": case "nchar": case "char": case "text": case "ntext": case "xml": return(typeof(string).FullName); default: Console.WriteLine($"Unknown sqlDataType for {table.TableName}.{column.ColumnName}: {sqlDataType}"); return(null); // Vendor-specific types case "hierarchyid": return("Microsoft.SqlServer.Types.SqlHierarchyId"); // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.34+) case "geography": return("Microsoft.SqlServer.Types.SqlGeography"); // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.32+) case "geometry": return("Microsoft.SqlServer.Types.SqlGeometry"); // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.33)+ } }
private void LoadSchema() { SqlServerService sqlServerService = new SqlServerService(ConnectionString, null); string primaryKeyQuery; SqlBuilder sqlBuilder = new SqlBuilder(); sqlBuilder.SELECT("c.TABLE_CATALOG,c.TABLE_SCHEMA,c.TABLE_NAME,c.CONSTRAINT_NAME,c.CONSTRAINT_TYPE,u.COLUMN_NAME"); sqlBuilder.FROM("INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c"); sqlBuilder.INNER_JOIN("INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME"); sqlBuilder.WHERE("c.CONSTRAINT_TYPE = 'PRIMARY KEY'"); primaryKeyQuery = sqlBuilder.ToString(); DataTable primaryKeyColumnSchema = sqlServerService.GenericExecuteResultQuery(primaryKeyQuery); foreach (DataRow primaryKeyColumnSchemaRow in primaryKeyColumnSchema.Rows) { SqlServerPrimaryKeyColumn primaryKeyColumn = new SqlServerPrimaryKeyColumn(primaryKeyColumnSchemaRow); PrimaryKeyColumns.Add(primaryKeyColumn); } using (SqlConnection sqlConnection = new SqlConnection(ConnectionString)) { sqlConnection.Open(); DataTable columnSchema = sqlConnection.GetSchema("Columns"); foreach (DataRow columnSchemaRow in columnSchema.Rows) { SqlServerColumn column = new SqlServerColumn(columnSchemaRow); SqlServerPrimaryKeyColumn primaryKeyColumn = PrimaryKeyColumns.Find(pkc => pkc.Name == column.Name && pkc.TableName == column.TableName && pkc.Schema == column.Schema && pkc.Catalog == column.Catalog); if (primaryKeyColumn != null) { column.ConstraintName = primaryKeyColumn.ConstraintName; column.IsPrimaryKey = true; } Columns.Add(column); } DataTable tableSchema = sqlConnection.GetSchema("Tables"); foreach (DataRow tableSchemaRow in tableSchema.Rows) { SqlServerTable table = new SqlServerTable(tableSchemaRow); Tables.Add(table); List <SqlServerColumn> columns = Columns.FindAll(c => c.Schema == table.Schema && c.TableName == table.Name); foreach (SqlServerColumn column in columns) { column.Table = table; table.Columns.Add(column); } } sqlConnection.Close(); } }
private void SetColumns(SqlServerTableSchema oracleTable) { if (oracleTable == null) { return; } oracleTable.Columns = new ColumnCollection(); string sql = @"SELECT a.colorder COLUMN_ID, a.name COLUMN_NAME, (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end) AUTOINCREMENT, (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then 1 else 0 end) PK, b.name DATA_TYPE, a.length BYTE_LEN, COLUMNPROPERTY(a.id,a.name,'PRECISION') as DATA_LENGTH, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as DATA_SCALE, (case when a.isnullable=1 then 1 else 0 end) NULLABLE, isnull(e.text,'') DATA_DEFAULT, isnull(g.[value], ' ') AS COMMENTS FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and (d.xtype='U' or d.xtype='V') and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id left join sys.extended_properties f on d.id=f.class and f.minor_id=0 where b.name is not null and d.name=@table_name order by a.id,a.colorder"; List <IColumn> columns = new List <IColumn>(); var para = new SqlParameter("@table_name", oracleTable.Name); DbHelper helper = new DbHelper(this._connectionString); var table = helper.ListBySql(sql, para); foreach (DataRow row in table.Rows) { int scale = Convert.ToInt32(row["DATA_SCALE"]); string data_type = row["DATA_TYPE"] + string.Empty; int len = Convert.ToInt32(row["DATA_LENGTH"]); var column = new SqlServerColumn { Name = row["COLUMN_NAME"] + string.Empty, Comment = row["COMMENTS"] + string.Empty, CsharpType = SqlServerUtils.TransformDatabaseType(data_type, len, scale), DbType = data_type, DefaultValue = (row["DATA_DEFAULT"] + string.Empty).Trim('\r', '\n'), IsNullable = (row["NULLABLE"] + string.Empty) == "1", Length = len, Scale = scale, Table = oracleTable, IsAutoIncrement = Convert.ToInt32(row["AUTOINCREMENT"]) == 1, IsNumeric = SqlServerUtils.IsNumeric(data_type), }; oracleTable.Columns.Add(column); } }