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