private void SetColumns(SqlServerTableSchema SqlserverTable) { if (SqlserverTable == null) { return; } SqlserverTable.Columns = new List <IColumn>(); 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' 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 table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", SqlserverTable.Name)); 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) == "Y", Length = len, Scale = scale, Table = SqlserverTable, IsAutoIncrement = Convert.ToInt32(row["AUTOINCREMENT"]) == 1, IsNumeric = SqlServerUtils.IsNumeric(data_type) }; SqlserverTable.Columns.Add(column); } }
private void SetvViewColumns(SqlServerTableSchema SqlserverTable) { if (SqlserverTable == null) { return; } SqlserverTable.Columns = new List <IColumn>(); string sql = @" Select c.name As COLUMN_NAME , t.name As DATA_TYPE , o.type As OBJECT_TYPE, c.length As DATA_LENGTH, c.isnullable as NULLABLE, isnull(c.scale,0) as DATA_SCALE, c.colid as colid From SysObjects As o , SysColumns As c , SysTypes As t Where o.type in ('u','v') And o.id = c.id And c.xtype = t.xtype and t.name !='sysname' And o.Name =@TABLE_NAME "; var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", SqlserverTable.Name)); 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, CsharpType = SqlServerUtils.TransformDatabaseType(data_type, len, scale), DbType = data_type, IsNullable = (row["NULLABLE"] + string.Empty) == "1", Length = len, Scale = scale, Table = SqlserverTable, IsNumeric = SqlServerUtils.IsNumeric(data_type) }; SqlserverTable.Columns.Add(column); } }