private TableSchema GetTable(string tableName, SqlConnection conn, OleDbConnection pkConn,
Dictionary <string, ForeignKeyInfo> fkOneTable)
{
string[] restrictions = new string[4];
restrictions[1] = "dbo";
restrictions[2] = tableName;
DataTable tt = conn.GetSchema("Tables", restrictions);
if (tt.Rows.Count == 0)
{
return(null);
}
TableSchema tableSchema = new SqlTableSchema();
tableSchema.Name = tt.Rows[0]["TABLE_NAME"].ToString();
tableSchema.IsView = tt.Rows[0]["TABLE_TYPE"].ToString().Equals("VIEW", StringComparison.OrdinalIgnoreCase);
restrictions = new string[4];
restrictions[1] = "dbo";
restrictions[2] = tableSchema.Name;
DataTable ff = conn.GetSchema("Columns", restrictions);
restrictions[2] = tableSchema.Name;
if (pkConn.State == ConnectionState.Closed)
{
pkConn.Open();
}
List <string> pkColumns = new List <string>();
string[] parameters = new string[] { conn.Database, "dbo", tableSchema.Name };
DataTable pkTable = pkConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, parameters);
pkConn.Close();
foreach (DataRow indexRow in pkTable.Rows)
{
pkColumns.Add(indexRow["column_name"].ToString());
}
string sql = string.Format(@"
select COLUMNPROPERTY(a.id, a.name, 'IsIdentity') as IsIdentity,
a.name
,g.value
from syscolumns a
inner join systypes b on a.xtype = b.xusertype
inner join sysobjects d on a.id = d.id
and d.xtype = 'U'
left join sys.extended_properties g on d.id = g.major_id and a.colid = g.minor_id
where d.name = '{0}'", tableName);
DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
Dictionary <string, bool> dict_IsIdentity = new Dictionary <string, bool>();
Dictionary <string, string> dict_Description = new Dictionary <string, string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
dict_IsIdentity.Add((string)row["name"], row["IsIdentity"].Equals(1)?true:false);
if (row["Value"] != DBNull.Value)
{
dict_Description.Add((string)row["name"], (string)row["Value"]);
}
}
foreach (DataRow fRow in ff.Rows)
{
string name = fRow["COLUMN_NAME"].ToString();
bool allowDBNull = fRow["IS_NULLABLE"].ToString().Equals("NO") ? false : true;
string dataType = fRow["DATA_TYPE"].ToString();
string nativeType = fRow["DATA_TYPE"].ToString();
byte precision = 0;
byte.TryParse(fRow["NUMERIC_PRECISION"].ToString(), out precision);
int scale = 0;
int.TryParse(fRow["NUMERIC_SCALE"].ToString(), out scale);
int size = 0;
int.TryParse(fRow["CHARACTER_MAXIMUM_LENGTH"].ToString(), out size);
if (size == 0)
{
int.TryParse(fRow["CHARACTER_OCTET_LENGTH"].ToString(), out size);
}
SqlColumnSchema columnSchema = new SqlColumnSchema(
pkColumns.Contains(name), name, dataType, nativeType, size, precision, scale, allowDBNull);
tableSchema.Columns.Add(columnSchema);
columnSchema.IsIdent = dict_IsIdentity.ContainsKey(name)?dict_IsIdentity[name]:false;
columnSchema.Description = dict_Description.ContainsKey(name) ? dict_Description[name] : "";
if (fkOneTable != null)
{
if (fkOneTable.ContainsKey(columnSchema.Name))
{
ForeignKeyInfo fkInfo = fkOneTable[columnSchema.Name];
columnSchema.ForeignKeyTable = fkInfo.FK_Table;
columnSchema.ForeignKeyColumn = fkInfo.FK_Column;
}
}
}
//获得外键
//DataTable fk = conn.GetSchema("ForeignKeys", restrictions);
//foreach (DataRow fkRow in fk.Rows)
//{
// fk.WriteXml("1.xml");
//}
return(tableSchema);
}
private TableSchema GetTable(string tableName,SqlConnection conn, OleDbConnection pkConn ,
Dictionary<string,ForeignKeyInfo> fkOneTable)
{
string[] restrictions = new string[4];
restrictions[1] = "dbo";
restrictions[2] = tableName;
DataTable tt = conn.GetSchema("Tables", restrictions);
if (tt.Rows.Count == 0)
return null;
TableSchema tableSchema = new SqlTableSchema();
tableSchema.Name = tt.Rows[0]["TABLE_NAME"].ToString();
tableSchema.IsView = tt.Rows[0]["TABLE_TYPE"].ToString().Equals("VIEW", StringComparison.OrdinalIgnoreCase);
restrictions = new string[4];
restrictions[1] = "dbo";
restrictions[2] = tableSchema.Name;
DataTable ff = conn.GetSchema("Columns", restrictions);
restrictions[2] = tableSchema.Name;
if (pkConn.State == ConnectionState.Closed)
{
pkConn.Open();
}
List<string> pkColumns = new List<string>();
string[] parameters = new string[] { conn.Database, "dbo", tableSchema.Name };
DataTable pkTable = pkConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, parameters);
pkConn.Close();
foreach (DataRow indexRow in pkTable.Rows)
{
pkColumns.Add(indexRow["column_name"].ToString());
}
string sql = string.Format(@"
select COLUMNPROPERTY(a.id, a.name, 'IsIdentity') as IsIdentity,
a.name
,g.value
from syscolumns a
inner join systypes b on a.xtype = b.xusertype
inner join sysobjects d on a.id = d.id
and d.xtype = 'U'
left join sys.extended_properties g on d.id = g.major_id and a.colid = g.minor_id
where d.name = '{0}'", tableName);
DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
Dictionary<string, bool> dict_IsIdentity = new Dictionary<string, bool>();
Dictionary<string,string> dict_Description = new Dictionary<string,string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
dict_IsIdentity.Add((string)row["name"], row["IsIdentity"].Equals(1)?true:false);
if (row["Value"] != DBNull.Value)
{
dict_Description.Add((string)row["name"], (string)row["Value"]);
}
}
foreach (DataRow fRow in ff.Rows)
{
string name = fRow["COLUMN_NAME"].ToString();
bool allowDBNull = fRow["IS_NULLABLE"].ToString().Equals("NO") ? false : true;
string dataType = fRow["DATA_TYPE"].ToString();
string nativeType = fRow["DATA_TYPE"].ToString();
byte precision = 0;
byte.TryParse(fRow["NUMERIC_PRECISION"].ToString(), out precision);
int scale = 0;
int.TryParse(fRow["NUMERIC_SCALE"].ToString(), out scale);
int size = 0;
int.TryParse(fRow["CHARACTER_MAXIMUM_LENGTH"].ToString(), out size);
if (size == 0)
{
int.TryParse(fRow["CHARACTER_OCTET_LENGTH"].ToString(), out size);
}
SqlColumnSchema columnSchema = new SqlColumnSchema(
pkColumns.Contains(name), name, dataType, nativeType, size, precision, scale, allowDBNull);
tableSchema.Columns.Add(columnSchema);
columnSchema.IsIdent = dict_IsIdentity.ContainsKey(name)?dict_IsIdentity[name]:false;
columnSchema.Description = dict_Description.ContainsKey(name) ? dict_Description[name] : "";
if (fkOneTable != null)
{
if (fkOneTable.ContainsKey(columnSchema.Name))
{
ForeignKeyInfo fkInfo = fkOneTable[columnSchema.Name];
columnSchema.ForeignKeyTable = fkInfo.FK_Table;
columnSchema.ForeignKeyColumn = fkInfo.FK_Column;
}
}
}
//获得外键
//DataTable fk = conn.GetSchema("ForeignKeys", restrictions);
//foreach (DataRow fkRow in fk.Rows)
//{
// fk.WriteXml("1.xml");
//}
return tableSchema;
}
