public List <TableColumnInfo> GetTableColumnInfoList(string connectionString, string databaseName, string tableName, string tableId)
{
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConnectionString;
}
var cacheList = SqlUtils.Cache_GetTableColumnInfoListCache(connectionString, databaseName, tableId);
if (cacheList != null && cacheList.Count > 0)
{
return(cacheList);
}
var list = new List <TableColumnInfo>();
var isIdentityExist = false;
if (WebConfigUtils.IsMySql)
{
string sqlString = $"select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_KEY from information_schema.columns where table_schema = '{databaseName}' and table_name = '{tableName}' order by table_name,ordinal_position; ";
using (var rdr = ExecuteReader(connectionString, sqlString))
{
while (rdr.Read())
{
var columnName = Convert.ToString(rdr.GetValue(0));
var isNullable = Convert.ToString(rdr.GetValue(1)) == "YES";
var dataType = EDataTypeUtils.FromMySql(Convert.ToString(rdr.GetValue(2)));
var length = rdr.IsDBNull(3) || dataType == EDataType.NText || dataType == EDataType.Text ? 0 : Convert.ToInt32(rdr.GetValue(3));
var precision = rdr.IsDBNull(4) ? 0 : Convert.ToInt32(rdr.GetValue(4));
var scale = rdr.IsDBNull(5) ? 0 : Convert.ToInt32(rdr.GetValue(5));
var isPrimaryKey = Convert.ToString(rdr.GetValue(6)) == "PRI";
var isIdentity = isPrimaryKey && StringUtils.EqualsIgnoreCase(columnName, "ID");
var info = new TableColumnInfo(databaseName, tableId, columnName, dataType, length, precision, scale, isPrimaryKey, isNullable, isIdentity);
list.Add(info);
}
rdr.Close();
}
}
else
{
string sqlString =
$"select C.name, T.name, C.length, C.xprec, C.xscale, C.colstat, C.isnullable, case when C.autoval is null then 0 else 1 end, SC.text, (select CForgin.name from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1), (select O.name from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1), (select Sr.rkeyid from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1) from [{databaseName}].dbo.systypes T, [{databaseName}].dbo.syscolumns C left join [{databaseName}].dbo.syscomments SC on C.cdefault=SC.id where C.id={tableId} and C.xtype=T.xusertype order by C.colid";
using (var rdr = ExecuteReader(connectionString, sqlString))
{
while (rdr.Read())
{
var columnName = Convert.ToString(rdr.GetValue(0));
if (columnName == "msrepl_tran_version")//sqlserver 发布订阅字段，忽略
{
continue;
}
var dataType = EDataTypeUtils.FromSqlServer(Convert.ToString(rdr.GetValue(1)));
var length = GetDataLength(dataType, Convert.ToInt32(rdr.GetValue(2)));
var precision = Convert.ToInt32(rdr.GetValue(3));
var scale = Convert.ToInt32(rdr.GetValue(4));
var isPrimaryKeyInt = Convert.ToInt32(rdr.GetValue(5));
var isNullableInt = Convert.ToInt32(rdr.GetValue(6));
var isIdentityInt = Convert.ToInt32(rdr.GetValue(7));
var isPrimaryKey = isPrimaryKeyInt == 1;
var isNullable = isNullableInt == 1;
var isIdentity = isIdentityInt == 1 || StringUtils.EqualsIgnoreCase(columnName, "ID");
//sqlserver 2005 返回isIdentity结果不正确,so 在此假设所有ID字段为Idenity字段
if (isIdentity)
{
isIdentityExist = true;
}
var info = new TableColumnInfo(databaseName, tableId, columnName, dataType, length, precision, scale, isPrimaryKey, isNullable, isIdentity);
list.Add(info);
}
rdr.Close();
}
if (!isIdentityExist)
{
var sqlIdentity = "select name from syscolumns where id = object_id(N'" + tableName + "') and COLUMNPROPERTY(id, name,'IsIdentity')= 1";
var clName = "";
using (var rdr = ExecuteReader(sqlIdentity))
{
if (rdr.Read())
{
clName = GetString(rdr, 0);
}
rdr.Close();
}
foreach (var info in list)
{
if (clName == info.ColumnName)
{
info.IsIdentity = true;
}
}
}
}
SqlUtils.Cache_CacheTableColumnInfoList(connectionString, databaseName, tableId, list);
return(list);
}
