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