//public override bool AlterColumn(string table, ColumnInfoModel column)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("ALTER TABLE `{0}` MODIFY COLUMN `{1}` {2} {3} NULL;", table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
// int count = this.db.ExecuteNonQuery();
// return count > 0;
//}
//public override bool EqualColumn(ColumnInfoModel column1, ColumnInfoModel column2)
//{
// if (column1 != null && column2 != null)
// {
// int i = column1.DataType.IndexOf('(');
// string s1 = i > 0 ? column1.DataType.Substring(0, i).Trim() : column1.DataType;
// i = column2.DataType.IndexOf('(');
// string s2 = i > 0 ? column2.DataType.Substring(0, i).Trim() : column2.DataType;
// if (!s1.Equals(s2, StringComparison.OrdinalIgnoreCase))
// {
// return false;
// }
// if ((s1.Equals("char", StringComparison.OrdinalIgnoreCase)
// || s1.Equals("varchar", StringComparison.OrdinalIgnoreCase))
// && column1.MaxLength != column2.MaxLength)
// {
// return false;
// }
// if (s1.Equals("decimal", StringComparison.OrdinalIgnoreCase)
// && column1.MaxLength != column2.MaxLength
// && column1.MinLength != column2.MinLength)
// {
// return false;
// }
// }
// return true;
//}
/// <summary>
/// 获取表列信息
/// </summary>
/// <param name="table">表名</param>
/// <returns>列信息</returns>
public override List <ColumnInfoModel> GetTableColumns(string table)
{
List <ColumnInfoModel> list = new List <ColumnInfoModel>();
this.db.ClearParameters();
this.db.CommandText = SelectColumnSql;
this.db.AddParameter("?database", this.database);
this.db.AddParameter("?table", table);
using (DataTable dt = new DataTable())
{
db.Fill(dt);
this.db.ClearParameters();
this.db.CommandText = SelectTableIndexSql;
this.db.AddParameter("?database", this.database);
this.db.AddParameter("?table", table);
using (var index_dt = new DataTable())
{
db.Fill(index_dt);
foreach (DataRow row in dt.Rows)
{
ColumnInfoModel m = new ColumnInfoModel();
list.Add(m);
int len = 0;
if (int.TryParse(row["MaxLength"].ToString(), out len))
{
m.MaxLength = len;
}
if (int.TryParse(row["MinLength"].ToString(), out len))
{
m.MinLength = len;
}
m.Name = row["Name"].ToString();
m.DataType = row["DataType"].ToString();
m.IsAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
m.IsKey = Convert.ToBoolean(row["IsKey"]);
m.IsNonClustered = false;
m.IsNullable = Convert.ToBoolean(row["IsNullable"]);
m.Order = Convert.ToInt32(row["Order"]);
m.Comment = row["Comment"].ToString();
var index_row = index_dt.Select("Order = " + m.Order);
if (index_row != null && index_row.Length > 0)
{
m.Indexs = new List <IndexModel>(index_row.Length);
foreach (var r in index_row)
{
IndexModel index = new IndexModel();
m.Indexs.Add(index);
index.ColumnName = m.Name;
index.Name = r["IndexName"].ToString();
index.IsUnique = Convert.ToBoolean(r["IsUnique"]);
}
}
}
}
}
return(list);
}
public void AddColumn(IColumnInfo column)
{
var columnMeta = new ColumnInfoModel(column);
var collumnVm = new ColumnInfoViewModel(columnMeta);
ColumnInfoModels.Add(collumnVm);
}
//public override bool DeleteTable(string table)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP TABLE \"{0}\"", table);
// int count = this.db.ExecuteNonQuery();
// return count != 0;
//}
/// <summary>
/// 添加列
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列信息</param>
/// <returns>是否成功</returns>
public override bool AddColumn(string table, ColumnInfoModel column)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("table");
}
if (column == null)
{
throw new ArgumentNullException("column");
}
int count = 0;
using (this.db.BeginTransaction())
{
var sql = string.Format("ALTER TABLE \"{0}\" ADD (\"{1}\" {2} {3} NULL)",
table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
count = this.db.ExecuteNonQuery(sql);
if (column.IsAutoIncrement)
{
count += this.AddAutoIncrement(table, column);
}
this.db.Commit();
}
return(count != 0);
}
private int AddAutoIncrement(string table, ColumnInfoModel column)
{
int count = 0;
if (column.IsAutoIncrement)
{
string sq_name = this.GetSequenceName(table, column.Name);
object obj = this.db.ExecuteScalar <object>("SELECT COUNT(1) FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = @p_db AND SEQUENCE_NAME = @sq_name",
new { p_db = this.database, sq_name = sq_name });
if (Convert.ToInt32(obj) == 0)
{
var sql = string.Format("CREATE SEQUENCE \"{0}\" MINVALUE 1 MAXVALUE 9999999999999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE", sq_name);
count += this.db.ExecuteNonQuery(sql);
}
string tr_name = this.GetTriggerName(table, column.Name);
obj = this.db.ExecuteScalar <object>("SELECT COUNT(1) FROM ALL_TRIGGERS WHERE OWNER = @p_db AND TRIGGERING_EVENT = 'INSERT' AND TABLE_NAME = @p_tb AND TRIGGER_NAME = @tr_name",
new { p_db = this.database, p_tb = table, tr_name });
if (Convert.ToInt32(obj) == 0)
{
var sql = string.Format("CREATE TRIGGER \"{0}\" BEFORE INSERT ON \"{1}\" FOR EACH ROW BEGIN SELECT \"{2}\".NEXTVAL INTO :NEW.\"{3}\" FROM DUAL; END;", tr_name, table, sq_name, column.Name);
count += db.ExecuteNonQuery(sql);
}
}
return(count);
}
//public override bool DeleteTable(string table)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP TABLE [{0}]", table);
// int count = this.db.ExecuteNonQuery();
// return count > 0;
//}
/// <summary>
/// 添加列
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列信息</param>
/// <returns>是否成功</returns>
public override bool AddColumn(string table, ColumnInfoModel column)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("table");
}
if (column == null)
{
throw new ArgumentNullException("column");
}
this.db.ClearParameters();
this.db.CommandText = string.Format(AddColumnSql, table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
int count = this.db.ExecuteNonQuery();
if (!string.IsNullOrEmpty(column.Comment))
{
this.db.CommandText = @"EXEC sys.sp_addextendedproperty";
this.db.ClearParameters();
this.db.AddParameter("@name", "MS_Description");
this.db.AddParameter("@value", column.Comment);
this.db.AddParameter("@level0type", "SCHEMA");
this.db.AddParameter("@level0name", "dbo");
this.db.AddParameter("@level1type", "TABLE");
this.db.AddParameter("@level1name", table);
this.db.AddParameter("@level2type", "COLUMN");
this.db.AddParameter("@level2name", column.Comment);
this.db.ExecuteNonQuery();
}
return(count > 0);
}
//public override bool DeleteTable(string table)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP TABLE [{0}]", table);
// int count = this.db.ExecuteNonQuery();
// return count > 0;
//}
/// <summary>
/// 添加列
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列信息</param>
/// <returns>是否成功</returns>
public override bool AddColumn(string table, ColumnInfoModel column)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("table");
}
if (column == null)
{
throw new ArgumentNullException("column");
}
var sql = string.Format(AddColumnSql, table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
int count = this.db.ExecuteNonQuery(sql);
if (!string.IsNullOrEmpty(column.Comment))
{
this.db.ExecuteNonQuery("EXEC sys.sp_addextendedproperty", new {
name = "MS_Description",
value = column.Comment,
level0type = "SCHEMA",
level0name = "dbo",
level1type = "TABLE",
level1name = table,
level2type = "COLUMN",
level2name = column.Comment
}, CommandType.StoredProcedure);
}
return(count > 0);
}
//public override bool DeleteTable(string table)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP TABLE [{0}]", table);
// int count = this.db.ExecuteNonQuery();
// return count > 0;
//}
/// <summary>
/// 添加列
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列信息</param>
/// <returns>是否成功</returns>
public override bool AddColumn(string table, ColumnInfoModel column)
{
var sql = string.Format("ALTER TABLE [{0}] ADD COLUMN [{1}] {2} {3} NULL;",
table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
int count = this.db.ExecuteNonQuery(sql);
return(count > 0);
}
//public override bool DeleteTable(string table)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP TABLE `{0}`", table);
// int count = this.db.ExecuteNonQuery();
// return count > 0;
//}
/// <summary>
/// 添加列
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列信息</param>
/// <returns>是否成功</returns>
public override bool AddColumn(string table, ColumnInfoModel column)
{
this.db.ClearParameters();
this.db.CommandText = string.Format("ALTER TABLE `{0}` ADD COLUMN `{1}` {2} {3} NULL;",
table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
int count = this.db.ExecuteNonQuery();
return(count > 0);
}
/// <summary>
/// 获取表列信息
/// </summary>
/// <param name="table">表名</param>
/// <returns>列信息</returns>
public override List <ColumnInfoModel> GetTableColumns(string table)
{
List <ColumnInfoModel> list = new List <ColumnInfoModel>();
this.db.ClearParameters();
this.db.CommandText = string.Format(@"PRAGMA TABLE_INFO('{0}')", table);
using (var dt = new DataTable())
{
this.db.Fill(dt);
foreach (DataRow row in dt.Rows)
{
string typeString = row["type"].ToString();
ColumnInfoModel m = new ColumnInfoModel();
list.Add(m);
m.Name = row["name"].ToString();
m.IsKey = Convert.ToBoolean(row["pk"]);
m.IsNullable = !Convert.ToBoolean(row["notnull"]);
m.IsAutoIncrement = false;
m.DataType = typeString;
int index = typeString.IndexOf('(');
if (index > 0)
{
m.DataType = typeString.Substring(0, index);
string s = typeString.Substring(index + 1, typeString.Length - index - 2);
string[] sarr = s.Split(',');
int len = 0;
if (int.TryParse(sarr[0].Trim(), out len))
{
m.MaxLength = len;
}
if (sarr.Length > 1 && int.TryParse(sarr[1].Trim(), out len))
{
m.MinLength = len;
}
}
m.Order = Convert.ToInt32(row["cid"]);
m.IsUnique = false;
m.IndexName = null;
}
if (list.Count(q => q.IsKey) == 1)
{
this.db.ClearParameters();
this.db.CommandText = string.Format(@"SELECT [sql] FROM [sqlite_master] WHERE [type]='table' AND [name]='{0}'", table);
var sql = (db.ExecuteScalar() ?? "").ToString().ToUpper();
var m = list.Find(q => q.IsKey);
m.IsAutoIncrement = sql.Contains("PRIMARY KEY AUTOINCREMENT");
}
this.GetIndexName(table, list);
}
return(list);
}
/// <summary>
/// 获取model属性类型string
/// </summary>
/// <param name="column">列信息</param>
/// <returns>model属性类型string</returns>
public override string GetPropertyType(ColumnInfoModel column)
{
if (column == null) throw new ArgumentNullException("column");
string type = "";
DataTypeModel m = null;
dic.TryGetValue(column.DataType, out m);
if (m != null)
{
type = column.IsNullable ? m.NullableType : m.Type;
}
return type;
}
/// <summary>
/// 获取model属性类型string
/// </summary>
/// <param name="column">列信息</param>
/// <returns>model属性类型string</returns>
public override string GetPropertyType(ColumnInfoModel column)
{
string type = "";
DataTypeModel m = null;
dic.TryGetValue(column.DataType, out m);
if (m != null)
{
type = column.IsNullable ? m.NullableType : m.Type;
}
return(type);
}
/// <summary>
/// 添加索引
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">索引列信息</param>
/// <returns>是否成功</returns>
public override bool AddIndex(string table, ColumnInfoModel column)
{
int count = 0;
if (!column.IsKey && !string.IsNullOrEmpty(column.IndexName))
{
this.db.ClearParameters();
this.db.CommandText = string.Format("CREATE {0} INDEX [{1}] ON [{2}] ([{3}])",
column.IsUnique ? "UNIQUE" : "", column.IndexName, table, column.Name);
count = this.db.ExecuteNonQuery();
}
return(count > 0);
}
/// <summary>
/// 添加索引
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">索引列信息</param>
/// <returns>是否成功</returns>
public override bool AddIndex(string table, ColumnInfoModel column)
{
int count = 0;
if (!column.IsKey && !string.IsNullOrEmpty(column.IndexName))
{
this.db.ClearParameters();
this.db.CommandText = string.Format("ALTER TABLE `{0}` ADD {1} INDEX `{2}` (`{3}`);",
table, column.IsUnique ? "UNIQUE" : "", column.IndexName, column.Name);
count = this.db.ExecuteNonQuery();
}
return(count > 0);
}
//public override bool DeleteTable(string table)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP TABLE [{0}]", table);
// int count = this.db.ExecuteNonQuery();
// return count > 0;
//}
/// <summary>
/// 添加列
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列信息</param>
/// <returns>是否成功</returns>
public override bool AddColumn(string table, ColumnInfoModel column)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("table");
}
if (column == null)
{
throw new ArgumentNullException("column");
}
this.db.ClearParameters();
this.db.CommandText = string.Format(AddColumnSql, table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
int count = this.db.ExecuteNonQuery();
return(count > 0);
}
/// <summary>
/// 获取表列信息
/// </summary>
/// <param name="table">表名</param>
/// <returns>列信息</returns>
public override List <ColumnInfoModel> GetTableColumns(string table)
{
List <ColumnInfoModel> list = new List <ColumnInfoModel>();
var sql = string.Format(@"PRAGMA TABLE_INFO('{0}')", table);
var cols = db.Query <SqliteTableColumnInfoModel>(sql);
foreach (var row in cols)
{
string typeString = row.type;
ColumnInfoModel m = new ColumnInfoModel();
list.Add(m);
m.Name = row.name;
m.IsNonClustered = false;
m.IsKey = row.pk;
m.IsNullable = !row.notnull;
m.IsAutoIncrement = false;
m.DataType = typeString;
int index = typeString.IndexOf('(');
if (index > 0)
{
m.DataType = typeString.Substring(0, index);
string s = typeString.Substring(index + 1, typeString.Length - index - 2);
string[] sarr = s.Split(',');
int len = 0;
if (int.TryParse(sarr[0].Trim(), out len))
{
m.MaxLength = len;
}
if (sarr.Length > 1 && int.TryParse(sarr[1].Trim(), out len))
{
m.MinLength = len;
}
}
m.Order = row.cid;
}
if (list.Count(q => q.IsKey) == 1)
{
sql = string.Format(@"SELECT [sql] FROM [sqlite_master] WHERE [type]='table' AND [name]='{0}'", table);
var sqls = (db.ExecuteScalar <string>(sql) ?? "").ToUpper();
var m = list.Find(q => q.IsKey);
m.IsAutoIncrement = sqls.Contains("PRIMARY KEY AUTOINCREMENT");
}
this.GetIndexName(table, list);
return(list);
}
/// <summary>
/// 获取model属性类型string
/// </summary>
/// <param name="column">列信息</param>
/// <returns>model属性类型string</returns>
public override string GetPropertyType(ColumnInfoModel column)
{
if (column == null)
{
throw new ArgumentNullException("column");
}
string type = "";
DataTypeModel m = null;
dic.TryGetValue(column.DataType, out m);
if (m != null)
{
type = column.IsNullable ? m.NullableType : m.Type;
if (column.DataType.ToUpper() == "NUMBER")
{
if (column.MinLength == 0)
{
if (column.MaxLength <= 1)
{
type = "bool";
}
else if (column.MaxLength <= 3)
{
type = "byte";
}
else if (column.MaxLength <= 5)
{
type = "short";
}
else if (column.MaxLength <= 11)
{
type = "int";
}
else
{
type = "long";
}
}
else
{
type = "decimal";
}
}
}
return(type);
}
//public override bool DeleteTable(string table)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP TABLE `{0}`", table);
// int count = this.db.ExecuteNonQuery();
// return count > 0;
//}
/// <summary>
/// 添加列
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列信息</param>
/// <returns>是否成功</returns>
public override bool AddColumn(string table, ColumnInfoModel column)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("table");
}
if (column == null)
{
throw new ArgumentNullException("column");
}
this.db.ClearParameters();
this.db.CommandText = string.Format("ALTER TABLE `{0}` ADD COLUMN `{1}` {2} {3} NULL Comment '{4}';",
table, column.Name, column.DataType, column.IsNullable ? "" : "NOT", (column.Comment ?? "").Replace("'", ""));
int count = this.db.ExecuteNonQuery();
return(count > 0);
}
//public override bool AlterColumn(string table, ColumnInfoModel column)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format(AlterColumnSql, table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
// int count = this.db.ExecuteNonQuery();
// return count > 0;
//}
//public override bool EqualColumn(ColumnInfoModel column1, ColumnInfoModel column2)
//{
// if (column1 != null && column2 != null)
// {
// int i = column1.DataType.IndexOf('(');
// string s1 = i > 0 ? column1.DataType.Substring(0, i).Trim() : column1.DataType;
// i = column2.DataType.IndexOf('(');
// string s2 = i > 0 ? column2.DataType.Substring(0, i).Trim() : column2.DataType;
// if (!s1.Equals(s2, StringComparison.OrdinalIgnoreCase))
// {
// return false;
// }
// if ((s1.Equals("nchar", StringComparison.OrdinalIgnoreCase)
// || s1.Equals("nvarchar", StringComparison.OrdinalIgnoreCase))
// && column1.MaxLength != column2.MaxLength)
// {
// return false;
// }
// if (s1.Equals("decimal", StringComparison.OrdinalIgnoreCase)
// && column1.MaxLength != column2.MaxLength
// && column1.MinLength != column2.MinLength)
// {
// return false;
// }
// }
// return true;
//}
/// <summary>
/// 获取表列信息
/// </summary>
/// <param name="table">表名</param>
/// <returns>列信息</returns>
public override List <ColumnInfoModel> GetTableColumns(string table)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("table");
}
List <ColumnInfoModel> list = new List <ColumnInfoModel>();
this.db.ClearParameters();
this.db.CommandText = SelectColumnSql;
this.db.AddParameter("@table", table);
using (DataTable dt = new DataTable())
{
db.Fill(dt);
foreach (DataRow row in dt.Rows)
{
ColumnInfoModel m = new ColumnInfoModel();
list.Add(m);
int len = 0;
if (int.TryParse(row["MaxLength"].ToString(), out len))
{
m.MaxLength = len;
}
if (int.TryParse(row["MinLength"].ToString(), out len))
{
m.MinLength = len;
}
m.Name = row["Name"].ToString();
m.DataType = row["DataType"].ToString();
m.IsAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
m.IsKey = Convert.ToBoolean(row["IsKey"]);
m.IsNullable = Convert.ToBoolean(row["IsNullable"]);
m.Order = Convert.ToInt32(row["Order"]);
m.IsUnique = false;
if (!m.IsKey)
{
m.IndexName = row["IndexName"].ToString();
m.IsUnique = Convert.ToBoolean(row["IsUnique"]);
}
}
}
return(list);
}
//public override bool DeleteTable(string table)
//{
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP TABLE \"{0}\"", table);
// int count = this.db.ExecuteNonQuery();
// return count != 0;
//}
/// <summary>
/// 添加列
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列信息</param>
/// <returns>是否成功</returns>
public override bool AddColumn(string table, ColumnInfoModel column)
{
int count = 0;
using (this.db.BeginTransaction())
{
this.db.ClearParameters();
this.db.CommandText = string.Format("ALTER TABLE \"{0}\" ADD (\"{1}\" {2} {3} NULL)",
table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
count = this.db.ExecuteNonQuery();
if (column.IsAutoIncrement)
{
count += this.AddAutoIncrement(table, column);
}
this.AddIndex(table, column);
this.db.Commit();
}
return(count != 0);
}
/// <summary>
/// 添加索引
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">索引列信息</param>
/// <returns>是否成功</returns>
public override bool AddIndex(string table, ColumnInfoModel column)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("table");
}
if (column == null)
{
throw new ArgumentNullException("column");
}
int count = 0;
if (!column.IsKey && !string.IsNullOrEmpty(column.IndexName))
{
this.db.ClearParameters();
this.db.CommandText = string.Format(AddIndexSql, column.IsUnique ? "UNIQUE" : "", column.IndexName, table, "[" + column.Name + "]");
count = this.db.ExecuteNonQuery();
}
return(count > 0);
}
/// <summary>
/// 添加索引
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">索引列信息</param>
/// <returns>是否成功</returns>
public override bool AddIndex(string table, ColumnInfoModel column)
{
int count = 0;
if (!column.IsKey && !string.IsNullOrEmpty(column.IndexName))
{
this.db.ClearParameters();
this.db.CommandText = "SELECT COUNT(1) FROM ALL_INDEXES WHERE OWNER = :p_db AND TABLE_NAME = :p_tb AND TABLE_TYPE = 'TABLE' AND INDEX_NAME = :index_name";
this.db.AddParameter("p_db", this.database);
this.db.AddParameter("p_tb", table);
this.db.AddParameter("index_name", column.IndexName);
object obj = db.ExecuteScalar();
if (Convert.ToInt32(obj) == 0)
{
this.db.ClearParameters();
this.db.CommandText = string.Format("CREATE {0} INDEX \"{2}\" ON \"{3}\" (\"{4}\");",
column.IsUnique ? "UNIQUE" : "", column.IndexName, table, column.Name);
count = this.db.ExecuteNonQuery();
}
}
return(count != 0);
}
/// <summary>
/// 获取表列信息
/// </summary>
/// <param name="table">表名</param>
/// <returns>列信息</returns>
public override List <ColumnInfoModel> GetTableColumns(string table)
{
List <ColumnInfoModel> list = new List <ColumnInfoModel>();
var param = new { p_db = this.database, p_tb = table };
// column
var dt = db.Query <OracleTabColumnModel>("SELECT COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, CHAR_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE FROM ALL_TAB_COLUMNS WHERE OWNER = @p_db AND TABLE_NAME = @p_tb ORDER BY COLUMN_ID",
param);
// tr
var tr_dt = db.Query <OracleTriggerModel>("SELECT TRIGGER_NAME, TRIGGER_BODY FROM ALL_TRIGGERS WHERE OWNER = @p_db AND TRIGGERING_EVENT = 'INSERT' AND TABLE_NAME = @p_tb",
param);
// key
var key_dt = db.Query <string>("SELECT a.COLUMN_NAME FROM ALL_CONS_COLUMNS a INNER JOIN ALL_CONSTRAINTS b ON a.OWNER=b.OWNER AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND a.TABLE_NAME=b.TABLE_NAME WHERE b.OWNER = @p_db AND b.TABLE_NAME = @p_tb AND b.CONSTRAINT_TYPE = 'P'",
param);
// index
var in_dt = db.Query <OracleIndexModel>("SELECT a.INDEX_NAME, a.COLUMN_NAME, b.UNIQUENESS FROM ALL_IND_COLUMNS a INNER JOIN ALL_INDEXES b ON a.TABLE_OWNER = b.TABLE_OWNER AND a.TABLE_NAME = b.TABLE_NAME AND a.INDEX_NAME = b.INDEX_NAME WHERE a.TABLE_OWNER = @p_db AND a.TABLE_NAME = @p_tb AND b.TABLE_TYPE = 'TABLE' AND b.GENERATED = 'N' ORDER BY a.INDEX_NAME, a.COLUMN_POSITION",
param);
// sq
var sq_dt = db.Query <string>("SELECT SEQUENCE_NAME FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = @p_db",
new { p_db = this.database });
foreach (var row in dt)
{
ColumnInfoModel m = new ColumnInfoModel();
list.Add(m);
m.Name = row.COLUMN_NAME;
m.IsNullable = string.Compare(row.NULLABLE, "Y", true) == 0;
m.Order = row.COLUMN_ID;
m.IsKey = key_dt.Contains("m.Name", StringComparer.OrdinalIgnoreCase);
string tr = this.GetTriggerName(table, m.Name);
m.IsAutoIncrement = false;
var r = tr_dt.Find(q => tr.Equals(q.TRIGGER_NAME, StringComparison.OrdinalIgnoreCase));
if (r != null)
{
string tr_body = r.TRIGGER_BODY.ToLower();
string sq = this.GetSequenceName(table, m.Name);
if (sq_dt.Contains(sq, StringComparer.OrdinalIgnoreCase))
{
m.IsAutoIncrement = tr_body.Contains(sq.ToLower()) && tr_body.Contains(m.Name.ToLower());
break;
}
}
var index_row = in_dt.FindAll(q => m.Name.Equals(q.COLUMN_NAME, StringComparison.OrdinalIgnoreCase));
if (index_row != null && index_row.Count > 0)
{
m.Indexs = new List <IndexModel>(index_row.Count);
foreach (var ir in index_row)
{
IndexModel index = new IndexModel();
m.Indexs.Add(index);
index.ColumnName = m.Name;
index.Name = ir.INDEX_NAME;
index.IsUnique = string.Compare(ir.UNIQUENESS, "UNIQUE", true) == 0;
}
}
string type = row.DATA_TYPE;
if (string.Compare(type, "NUMBER", true) == 0)
{
m.MaxLength = row.DATA_PRECISION ?? 0;
m.MinLength = row.DATA_SCALE ?? 0;
}
else
{
if (string.Compare(type, "RAW", true) == 0)
{
m.MaxLength = row.DATA_LENGTH ?? 0;
}
else
{
m.MaxLength = row.CHAR_LENGTH ?? 0;
}
}
m.DataType = type;
}
return(list);
}
//public override bool DeleteColumn(string table, ColumnInfoModel column)
//{
// int count = 0;
// using (var tx = this.db.BeginTransaction())
// {
// if (!string.IsNullOrEmpty(column.IndexName))
// {
// this.db.ClearParameters();
// this.db.CommandText = "SELECT COUNT(1) FROM ALL_INDEXES WHERE OWNER = :p_db AND TABLE_NAME = :p_tb AND TABLE_TYPE = 'TABLE' AND INDEX_NAME = :index_name";
// this.db.AddParameter("p_db", this.database);
// this.db.AddParameter("p_tb", table);
// this.db.AddParameter("index_name", column.IndexName);
// object obj = db.ExecuteScalar();
// if (Convert.ToInt32(obj) > 0)
// {
// this.db.ClearParameters();
// this.db.CommandText = string.Format("DROP INDEX \"{1}\"", table, column.IndexName);
// count += this.db.ExecuteNonQuery();
// }
// }
// if (column.IsAutoIncrement)
// {
// string sq_name = this.GetSequenceName(table, column.Name);
// this.db.ClearParameters();
// this.db.CommandText = "SELECT COUNT(1) FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = :p_db AND SEQUENCE_NAME = :sq_name";
// this.db.AddParameter("p_db", this.database);
// this.db.AddParameter("sq_name", sq_name);
// object obj = db.ExecuteScalar();
// if (Convert.ToInt32(obj) > 0)
// {
// this.db.ClearParameters();
// db.CommandText = string.Format("DROP SEQUENCE \"{0}\"", sq_name);
// count += db.ExecuteNonQuery();
// }
// string tr_name = this.GetTriggerName(table, column.Name);
// this.db.ClearParameters();
// this.db.CommandText = "SELECT COUNT(1) FROM ALL_TRIGGERS WHERE OWNER = :p_db AND TRIGGERING_EVENT = 'INSERT'AND TABLE_NAME = :p_tb AND TRIGGER_NAME = :tr_name";
// this.db.AddParameter("p_db", this.database);
// this.db.AddParameter("p_tb", table);
// this.db.AddParameter("tr_name", tr_name);
// obj = db.ExecuteScalar();
// if (Convert.ToInt32(obj) > 0)
// {
// this.db.ClearParameters();
// db.CommandText = string.Format("DROP TRIGGER \"{0}\"", tr_name);
// count += db.ExecuteNonQuery();
// }
// }
// this.db.ClearParameters();
// this.db.CommandText = string.Format("ALTER TABLE \"{0}\" DROP COLUMN \"{1}\"", table, column.Name);
// count = this.db.ExecuteNonQuery();
// tx.Commit();
// }
// return count != 0;
//}
//public override bool AlterColumn(string table, ColumnInfoModel column)
//{
// //this.db.ClearParameters();
// //this.db.CommandText = string.Format("ALTER TABLE \"{0}\" MODIFY (\"{1}\" {2} {3} NULL)", table, column.Name, column.DataType, column.IsNullable ? "" : "NOT");
// //int count = this.db.ExecuteNonQuery();
// return false;// count > 0;
//}
//public override bool EqualColumn(ColumnInfoModel column1, ColumnInfoModel column2)
//{
// //if (column1 != null && column2 != null)
// //{
// // int i = column1.DataType.IndexOf('(');
// // string s1 = i > 0 ? column1.DataType.Substring(0, i).Trim() : column1.DataType;
// // i = column2.DataType.IndexOf('(');
// // string s2 = i > 0 ? column2.DataType.Substring(0, i).Trim() : column2.DataType;
// // if (!s1.Equals(s2, StringComparison.OrdinalIgnoreCase))
// // {
// // return false;
// // }
// // if ((s1.Equals("nchar", StringComparison.OrdinalIgnoreCase)
// // || s1.Equals("nvarchar", StringComparison.OrdinalIgnoreCase))
// // && column1.MaxLength != column2.MaxLength)
// // {
// // return false;
// // }
// // if (s1.Equals("decimal", StringComparison.OrdinalIgnoreCase)
// // && column1.MaxLength != column2.MaxLength
// // && column1.MinLength != column2.MinLength)
// // {
// // return false;
// // }
// //}
// return true;
//}
/// <summary>
/// 获取表列信息
/// </summary>
/// <param name="table">表名</param>
/// <returns>列信息</returns>
public override List <ColumnInfoModel> GetTableColumns(string table)
{
List <ColumnInfoModel> list = new List <ColumnInfoModel>();
this.db.ClearParameters();
// column
this.db.CommandText = "SELECT COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, CHAR_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE FROM ALL_TAB_COLUMNS WHERE OWNER = :p_db AND TABLE_NAME = :p_tb ORDER BY COLUMN_ID";
this.db.AddParameter("p_db", this.database);
this.db.AddParameter("p_tb", table);
using (DataTable dt = new DataTable())
{
this.db.Fill(dt);
// tr
this.db.CommandText = "SELECT TRIGGER_NAME, TRIGGER_BODY FROM ALL_TRIGGERS WHERE OWNER = :p_db AND TRIGGERING_EVENT = 'INSERT' AND TABLE_NAME = :p_tb";// AND TABLE_OWNER = :p_db
using (DataTable tr_dt = new DataTable())
{
this.db.Fill(tr_dt);
// key
this.db.CommandText = "SELECT a.COLUMN_NAME FROM ALL_CONS_COLUMNS a INNER JOIN ALL_CONSTRAINTS b ON a.OWNER=b.OWNER AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND a.TABLE_NAME=b.TABLE_NAME WHERE b.OWNER = :p_db AND b.TABLE_NAME = :p_tb AND b.CONSTRAINT_TYPE = 'P'";
using (DataTable key_dt = new DataTable())
{
this.db.Fill(key_dt);
// index
this.db.CommandText = "SELECT a.INDEX_NAME, a.COLUMN_NAME, b.UNIQUENESS FROM ALL_IND_COLUMNS a INNER JOIN ALL_INDEXES b ON a.TABLE_OWNER = b.TABLE_OWNER AND a.TABLE_NAME = b.TABLE_NAME AND a.INDEX_NAME = b.INDEX_NAME WHERE a.TABLE_OWNER = :p_db AND a.TABLE_NAME = :p_tb AND b.TABLE_TYPE = 'TABLE' ORDER BY a.INDEX_NAME, a.COLUMN_POSITION";
using (DataTable in_dt = new DataTable())
{
this.db.Fill(in_dt);
this.db.ClearParameters();
// sq
this.db.CommandText = "SELECT SEQUENCE_NAME FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = :p_db";
this.db.AddParameter("p_db", this.database);
using (DataTable sq_dt = new DataTable())
{
this.db.Fill(sq_dt);
foreach (DataRow row in dt.Rows)
{
ColumnInfoModel m = new ColumnInfoModel();
list.Add(m);
m.Name = row["COLUMN_NAME"].ToString();
m.IsNullable = string.Compare(row["NULLABLE"].ToString(), "Y", true) == 0;
m.Order = Convert.ToInt32(row["COLUMN_ID"]);
m.IsKey = false;
foreach (DataRow r in key_dt.Rows)
{
if (string.Compare(r["COLUMN_NAME"].ToString(), m.Name, true) == 0)
{
m.IsKey = true;
break;
}
}
m.IsAutoIncrement = false;
foreach (DataRow r in tr_dt.Rows)
{
string tr = this.GetTriggerName(table, m.Name);
if (string.Compare(r["TRIGGER_NAME"].ToString(), tr, true) == 0)
{
string tr_body = r["TRIGGER_BODY"].ToString().ToLower();
foreach (DataRow rq in sq_dt.Rows)
{
string sq = this.GetSequenceName(table, m.Name);
if (string.Compare(r["SEQUENCE_NAME"].ToString(), sq, true) == 0)
{
m.IsAutoIncrement = tr_body.Contains(sq.ToLower()) && tr_body.Contains(m.Name.ToLower());
break;
}
}
break;
}
}
m.IsUnique = false;
if (!m.IsKey)
{
foreach (DataRow r in in_dt.Rows)
{
if (string.Compare(r["COLUMN_NAME"].ToString(), m.Name, true) == 0)
{
m.IndexName = r["INDEX_NAME"].ToString();
m.IsUnique = string.Compare(r["INDEX_NAME"].ToString(), "UNIQUE", true) == 0;
break;
}
}
}
int len = 0;
string type = row["DATA_TYPE"].ToString();
if (string.Compare(type, "NUMBER", true) == 0)
{
int.TryParse(row["DATA_PRECISION"].ToString(), out len);
m.MaxLength = len;
int.TryParse(row["DATA_SCALE"].ToString(), out len);
m.MinLength = len;
}
else
{
string s = "CHAR_LENGTH";
if (string.Compare(type, "RAW", true) == 0)
{
s = "DATA_LENGTH";
}
int.TryParse(row[s].ToString(), out len);
m.MaxLength = len;
}
m.DataType = type;
}
}
}
}
}
}
return(list);
}
// GET: ColmunInfo
public ActionResult ColumnInfo(ColumnInfoModel model)
{
return(View(model));
}
