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