private void GetColumns(Table table) { DataSet set = this.dbHelper.ExecuteDataset(CommandType.Text, string.Format(@"SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull FROM pg_class c,pg_attribute a,pg_type t WHERE c.relname = '{0}' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum", table.Name), null); foreach (DataRow row in set.Tables[0].Rows) { Field field = new Field(); field.AllowNull = SchemaHelper.GetBool(row["notnull"]); field.FieldType = SchemaHelper.GetString(row["type"]); field.Length = SchemaHelper.GetInt(row["length"]); field.Name = SchemaHelper.GetString(row["field"]); field.Size = SchemaHelper.GetInt(row["lengthvar"]); //field.DefaultValue = SchemaHelper.GetString(row["DecimalDigits"]); //field.Pos = SchemaHelper.GetInt(row["Position"]); table.AddField(field); } DataSet set2 = this.dbHelper.ExecuteDataset(CommandType.Text, string.Format(@"select a.attname from pg_constraint c join pg_attribute a on c.conrelid=a.attrelid and a.attnum = ANY (c.conkey) join pg_class tc on c.conrelid=tc.oid where c.contype = 'p' and tc.relname ='{0}' order by a.attnum", table.Name), null); foreach (DataRow row in set2.Tables[0].Rows) { string str = SchemaHelper.GetString(row[0]); foreach (Field field in table.Fields) { if (field.Name.Equals(str, StringComparison.CurrentCultureIgnoreCase)) { field.IsKey = true; } } } }
private List <Model.Table> GetSQLTableList(DataSet ds) { DataTable dt = ds.Tables[0]; List <Model.Table> tables = new List <Model.Table>(); foreach (DataRow r in dt.Rows) { Model.Field field = GetField(r); string parentTableName = SchemaHelper.GetString(r["TableName"]); bool hasTable = false; foreach (Model.Table modelTable in tables) { if (parentTableName == modelTable.Name) { modelTable.AddField(field); hasTable = true; break; } } if (!hasTable) { Model.Table newTable = new Model.Table(); newTable.Name = parentTableName; newTable.AddField(field); tables.Add(newTable); } } foreach (Model.Table table in tables) { table.Fields.Sort(); } return(tables); }
private void GetColumns(Table table) { DataSet set = this.dbHelper.ExecuteDataset(CommandType.Text, string.Format(@"select b.name as TableName,a.colid as Position,a.name as FieldName,c.name as FieldType,a.length as FieldLength,a.prec as FieldSize,a.scale as DecimalDigits,case isnull(a.status,0) when 0 then 'NOT NULL' else 'NULL' end as AllowNull from syscolumns a,sysobjects b,systypes c where a.id=b.id and a.usertype=c.usertype and b.name='{0}' order by object_name(a.id),a.colid", table.Name), null); foreach (DataRow row in set.Tables[0].Rows) { Field field = new Field(); field.AllowNull = SchemaHelper.GetBool(row["AllowNull"]); field.DefaultValue = SchemaHelper.GetString(row["DecimalDigits"]); field.FieldType = SchemaHelper.GetString(row["FieldType"]); field.Length = SchemaHelper.GetInt(row["FieldLength"]); field.Name = SchemaHelper.GetString(row["FieldName"]); field.Pos = SchemaHelper.GetInt(row["Position"]); field.Size = SchemaHelper.GetInt(row["FieldSize"]); table.AddField(field); } DataSet set2 = this.dbHelper.ExecuteDataset(CommandType.Text, string.Format("select index_col(o.name, i.indid, 1), i.keycnt from sysindexes i join sysobjects o on o.id = i.id where o.name = '{0}' and i.keycnt > 1 and i.status & 2048 = 0", table.Name), null); foreach (DataRow row in set2.Tables[0].Rows) { string str = SchemaHelper.GetString(row[0]); foreach (Field field in table.Fields) { if (field.Name.Equals(str, StringComparison.CurrentCultureIgnoreCase)) { field.IsKey = true; } } } }
private void GetProcedures(Database db) { DataSet dataSet = this.dbHelper.ExecuteDataset(CommandType.Text, "select distinct(name) from sysobjects where type='P'order by name", null); foreach (DataRow dataRow in dataSet.Tables[0].Rows) { db.StoreProcedures.Add(SchemaHelper.GetString(dataRow[0])); } }
private void GetProcedures(Model.Database db) { DataSet ds = helper.ExecuteQuery(CommandType.Text, "SELECT * FROM USER_PROCEDURES WHERE PROCEDURE_NAME IS NULL", null); foreach (DataRow r in ds.Tables[0].Rows) { db.StoreProcedures.Add(SchemaHelper.GetString(r[0])); } }
private void GetTables(Model.Database database) { DataSet dsTables = dbHelper.ExecuteQuery(CommandType.Text, string.Format("select distinct TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_TYPE='BASE TABLE'", database.Name), null); foreach (DataRow rTable in dsTables.Tables[0].Rows) { Model.Table table = new Model.Table(); table.Name = SchemaHelper.GetString(rTable[0]); GetColumns(database, table); table.Fields.Sort(); database.AddTable(table); } }
private void GetViews(Model.Database database) { DataSet dsViews = dbHelper.ExecuteQuery(CommandType.Text, string.Format("select distinct TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_TYPE='VIEW'", database.Name), null); foreach (DataRow rView in dsViews.Tables[0].Rows) { Model.Table view = new Model.Table(); view.Name = SchemaHelper.GetString(rView[0]); GetColumns(database, view); view.Fields.Sort(); database.AddView(view); } }
private Model.Field GetField(DataRow r) { Model.Field model = new Model.Field(); model.AllowNull = SchemaHelper.GetBool(r["AllowNull"]); model.DefaultValue = SchemaHelper.GetString(r["DefaultValue"]); model.Descn = SchemaHelper.GetString(r["FieldDescn"]); model.Length = SchemaHelper.GetInt(r["FieldLength"]); model.Name = SchemaHelper.GetString(r["FieldName"]); model.Pos = SchemaHelper.GetInt(r["FieldNumber"]); model.Size = SchemaHelper.GetInt(r["FieldSize"]); model.FieldType = SchemaHelper.GetString(r["FieldType"]); model.IsId = SchemaHelper.GetBool(r["IsId"]); model.IsKey = SchemaHelper.GetBool(r["IsKey"]); return(model); }
private Model.Field GetField(DataRow r) { Model.Field model = new Model.Field(); model.AllowNull = SchemaHelper.GetBool(r["AllowNull"]); model.DefaultValue = SchemaHelper.GetString(r["DefaultValue"]); model.Descn = SchemaHelper.GetString(r["FieldDescn"]); model.Length = SchemaHelper.GetInt(r["FieldLength"]); model.Name = SchemaHelper.GetString(r["FieldName"]); model.Position = SchemaHelper.GetInt(r["FieldNumber"]); model.Size = SchemaHelper.GetInt(r["FieldSize"]); model.SetDbType(Model.DatabaseTypes.Sql2000, SchemaHelper.GetString(r["FieldType"])); model.IsIdentifier = SchemaHelper.GetBool(r["IsIdentifier"]); model.IsKeyField = SchemaHelper.GetBool(r["IsKeyField"]); return(model); }
private void GetColumns(Table table) { Dictionary <string, string> dictionary = new Dictionary <string, string>(); DataRowCollection rows = this.helper.ExecuteDataset(CommandType.Text, "select * from SYSCAT.COLUMNS where TABNAME='" + table + "' ORDER BY COLNO", null).Tables[0].Rows; foreach (DataRow row2 in rows) { Field fieldex = new Field(); fieldex.FieldName = row2["COLNAME"].ToString(); fieldex.FieldType = SchemaHelper.GetString(row2["TYPENAME"]); if (dictionary.ContainsKey(fieldex.FieldName)) { fieldex.IsKey = true; } else { fieldex.IsKey = false; } if (row2["NULLS"].ToString() == "N") { fieldex.AllowNull = false; } else { fieldex.AllowNull = true; } if (row2["IDENTITY"].ToString() == "Y") { fieldex.IsId = true; } else { fieldex.IsId = false; } fieldex.FieldNumber = Convert.ToInt32(row2["COLNO"]); fieldex.FieldLength = Convert.ToInt32(row2["LENGTH"]); fieldex.DefaultValue = row2["DEFAULT"].ToString(); fieldex.FieldDescn = fieldex.FieldName; table.AddField(fieldex); } }
private void GetColumns(Model.Table table) { DataSet dsTableColumns = helper.ExecuteQuery(CommandType.Text, string.Format("select * from user_tab_cols where table_name = '{0}'", table.Name), null); //获取所有字段 foreach (DataRow r in dsTableColumns.Tables[0].Rows) { Model.Field field = new Model.Field(); field.AllowNull = SchemaHelper.GetString(r["NULLABLE"]).Equals("Y", StringComparison.CurrentCultureIgnoreCase); field.DefaultValue = r["DATA_DEFAULT"].ToString(); //field.Descn 暂时获取不到 field.SetDbType(Model.DatabaseTypes.Oracle, SchemaHelper.GetString(r["DATA_TYPE"])); //field.IsIdentifier 暂时获取不到 //field.IsKeyField field.Length = SchemaHelper.GetInt(r["DATA_LENGTH"]); field.Name = SchemaHelper.GetString(r["COLUMN_NAME"]); field.Position = SchemaHelper.GetInt(r["COLUMN_ID"]); field.Size = (int)field.Length; table.AddField(field); } //获取主键 DataSet ds = helper.ExecuteQuery(CommandType.Text, string.Format("select col.column_name from user_constraints con, user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = '{0}'", table.Name), null); foreach (DataRow r in ds.Tables[0].Rows) { string key = SchemaHelper.GetString(r[0]); foreach (Model.Field field in table.Fields) { if (field.Name.Equals(key, StringComparison.CurrentCultureIgnoreCase)) { field.IsKeyField = true; } } } }
private void GetColumns(Model.Database db, Model.Table table) { // 对每个表取字段属性 DataSet dsColumns = dbHelper.ExecuteQuery(CommandType.Text, string.Format("select * from COLUMNS where TABLE_SCHEMA='{0}' and TABLE_NAME='{1}'", db.Name, table.Name), null); foreach (DataRow rField in dsColumns.Tables[0].Rows) { Model.Field field = new Model.Field(); field.IsIdentifier = rField["EXTRA"].ToString().ToLower() == "auto_increment"; field.IsKeyField = rField["COLUMN_KEY"].ToString().ToLower() == "pri"; field.AllowNull = rField["IS_NULLABLE"].ToString().ToLower() == "yes"; field.SetDbType(Model.DatabaseTypes.MySql, SchemaHelper.GetString(rField["DATA_TYPE"])); field.DefaultValue = SchemaHelper.GetString(rField["COLUMN_DEFAULT"]); field.Descn = SchemaHelper.GetString(rField["COLUMN_COMMENT"]); field.Length = SchemaHelper.GetLong(rField["CHARACTER_MAXIMUM_LENGTH"]); field.Name = SchemaHelper.GetString(rField["COLUMN_NAME"]); field.Position = SchemaHelper.GetInt(rField["ORDINAL_POSITION"]); table.AddField(field); } }
private Model.Field GetField(string connectionString, string tbName, DataRow r) { Model.Field model = new Model.Field(); model.AllowNull = SchemaHelper.GetBool(r["IS_NULLABLE"]); model.DefaultValue = SchemaHelper.GetString(r["COLUMN_DEFAULT"]); model.Descn = SchemaHelper.GetString(r["DESCRIPTION"]); model.Name = SchemaHelper.GetString(r["COLUMN_NAME"]); model.Position = SchemaHelper.GetInt(r["ORDINAL_POSITION"]); model.Size = SchemaHelper.GetInt(r["CHARACTER_OCTET_LENGTH"]); model.SetDbType(Model.DatabaseTypes.Access, SchemaHelper.GetString(r["DATA_TYPE"])); model.Length = SchemaHelper.GetInt(r["CHARACTER_MAXIMUM_LENGTH"]); model.IsIdentifier = SchemaHelper.GetInt(r["COLUMN_FLAGS"]) == 90 && SchemaHelper.GetInt(r["DATA_TYPE"]) == 3; DataTable dtPrimanyKey = GetDbSchema(connectionString, OleDbSchemaGuid.Primary_Keys, null); foreach (DataRow rp in dtPrimanyKey.Rows) { if (rp[2].ToString() == tbName && rp[3].ToString() == model.Name) { model.IsKeyField = true; } } return(model); }
private void GetColumns(Table table) { DataTable schema; using (SQLiteConnection connection = new SQLiteConnection(helper.ConnectionString)) { connection.Open(); schema = connection.GetSchema("COLUMNS"); } foreach (DataRow r in schema.Select(string.Format("TABLE_NAME = '{0}'", table))) { Model.Field model = new Model.Field(); model.AllowNull = Convert.ToBoolean(r["IS_NULLABLE"]); model.Position = Convert.ToInt32(r["ORDINAL_POSITION"]); model.Name = r["COLUMN_NAME"].ToString(); model.SetDbType(Model.DatabaseTypes.SQLite, SchemaHelper.GetString(r["DATA_TYPE"])); model.DefaultValue = r["COLUMN_DEFAULT"].ToString(); model.Descn = SchemaHelper.GetString(r["DESCRIPTION"]); model.Size = Convert.ToInt32((r["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value) ? 0 : r["CHARACTER_MAXIMUM_LENGTH"]); model.Length = SchemaHelper.GetInt(r["CHARACTER_MAXIMUM_LENGTH"]); model.IsIdentifier = SchemaHelper.GetBool(r["AUTOINCREMENT"]); if (Convert.ToBoolean(r["PRIMARY_KEY"])) { model.IsKeyField = true; } else { model.IsKeyField = false; } table.AddField(model); } }