コード例 #1
0
        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);
        }
コード例 #2
0
        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;
        }