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