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