/// <summary> /// 取读取列数据第一条数据 /// </summary> /// <returns></returns> public static object GetColumnData(Dictionary <string, object> link, Data_Business_Details columnInfo, object key) { object result = DBNull.Value; var type = link.GetValue("type").ToStr(); var conn = link.GetValue("conn") as DbConnection; var cmd = conn.CreateCommand(); if (type == AppEtl.DataDbType.Oracle.ToLower()) { cmd.CommandText = string.Format("select {0} from {1} where rownum =1 and {2}='{3}' order by {4} desc" , columnInfo.ColumnName, columnInfo.TableName, columnInfo.Key, key, string.IsNullOrEmpty(columnInfo.OrderBy) ? columnInfo.Key : columnInfo.OrderBy); } if (type == AppEtl.DataDbType.SqlServer.ToLower()) { cmd.CommandText = string.Format("select top 1 {0} from {1} where {2}='{3}' order by {4} desc" , columnInfo.ColumnName, columnInfo.TableName, columnInfo.Key, key, string.IsNullOrEmpty(columnInfo.OrderBy) ? columnInfo.Key : columnInfo.OrderBy); } if (type == AppEtl.DataDbType.MySql.ToLower()) { cmd.CommandText = string.Format("select {0} from {1} where limit 1 and {2}={3} order by {4} desc" , columnInfo.ColumnName, columnInfo.TableName, columnInfo.Key, key, string.IsNullOrEmpty(columnInfo.OrderBy) ? columnInfo.Key : columnInfo.OrderBy); } var dr = cmd.ExecuteReader(); while (dr.Read()) { result = dr[0]; } dr.Close(); return(result); }
/// <summary> /// 修改列备注 /// </summary> /// <returns></returns> public static bool UpdateColumnComment(DataContext db, Data_Business table, Data_Business_Details column, Cache_Column columnInfo, Data_Source dataSource) { var sql = ""; if (db.config.DbType == DataDbType.MySql) { sql = string.Format("alter table {0} modify {1} {2} comment '{3}'", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource), columnInfo.Comments); } if (db.config.DbType == DataDbType.Oracle) { sql = string.Format("Comment on column {0}.{1} is '{2}'", table.TableName, column.FieldName, columnInfo.Comments); } if (db.config.DbType == DataDbType.SqlServer) { sql = string.Format("select count(0) from syscolumns where id = object_id('{0}') and name='{1}' and exists(select 1 from sys.extended_properties where object_id('{0}') = major_id and colid = minor_id", table.TableName, column.FieldName); var count = db.ExecuteSql(sql, null, false).DicList[0]["count"].ToStr().ToInt(0); if (count >= 1) { sql = string.Format("exec sys.sp_updateextendedproperty N'MS_Description',N'{0}',N'SCHEMA', N'dbo', N'TABLE',N'{1}',N'column',N'{2}'", columnInfo.Comments, table.TableName, column.FieldName); } else { sql = string.Format("exec sys.sp_addextendedproperty N'MS_Description',N'{0}',N'SCHEMA', N'dbo', N'TABLE',N'{1}',N'column',N'{2}'", columnInfo.Comments, table.TableName, column.FieldName); } } return(db.ExecuteSql(sql, null, false).writeReturn.IsSuccess); }
/// <summary> /// 删除列 /// </summary> /// <returns></returns> public static bool DropColumn(DataContext db, Data_Business table, Data_Business_Details column) { if (IsExistsColumn(db, table.TableName, column.FieldName)) { var sql = string.Format("alter table {0} drop column {1}", table.TableName, column.FieldName); return(db.ExecuteSql(sql, null, false).writeReturn.IsSuccess); } return(true); }
/// <summary> /// 增加列 /// </summary> /// <returns></returns> public static WriteReturn AddColumn(DataContext db, Data_Business table, Data_Business_Details column, CacheColumn columnInfo, Data_Source dataSource, bool IsCheckKey = true) { if (columnInfo.IsKey && IsCheckKey) { return(db.ExecuteSql(string.Format("alter table {0} add {1} {2} primary key", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)), null, false).writeReturn); } else { return(db.ExecuteSql(string.Format("alter table {0} add {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)), null, false).writeReturn); } }
/// <summary> /// 操作 /// </summary> /// <param name="item"></param> /// <returns></returns> public IActionResult OnPostBusinessDetailsForm(Data_Business_Details item) { var info = new WriteReturn(); using (var db = new DataContext(AppEtl.Db)) { var table = IFast.Query <Data_Business>(a => a.Id == item.Id).ToItem <Data_Business>(db); var source = IFast.Query <Data_Source>(a => a.Id == item.DataSourceId).ToItem <Data_Source>(db); var key = string.Format(AppEtl.CacheKey.Column, source.Host, item.TableName); var colunm = RedisInfo.Get <List <CacheColumn> >(key, AppEtl.CacheDb).Find(a => a.Name == item.ColumnName); db.BeginTrans(); if (IFast.Query <Data_Business_Details>(a => a.FieldId == item.FieldId).ToCount(db) == 0) { item.FieldId = Guid.NewGuid().ToStr(); info = IFast.Add(item); if (info.IsSuccess) { info = DataSchema.AddColumn(db, table, item, colunm, source); if (info.IsSuccess) { DataSchema.UpdateColumnComment(db, table, item, colunm, source); } } } else { info = IFast.Update <Data_Business_Details>(item, a => a.FieldId == item.FieldId); if (info.IsSuccess) { info.IsSuccess = DataSchema.UpdateColumn(db, table, item, colunm, source); if (info.IsSuccess) { DataSchema.UpdateColumnComment(db, table, item, colunm, source); } } } if (info.IsSuccess) { db.SubmitTrans(); return(new JsonResult(new { success = true, msg = "操作成功" })); } else { db.RollbackTrans(); return(new JsonResult(new { success = false, msg = "操作失败" })); } } }
/// <summary> /// 获取条数 /// </summary> /// <param name="link"></param> /// <param name="tableInfo"></param> /// <returns></returns> public static PageModel GetTableCount(Dictionary <string, object> link, Data_Business_Details columnInfo, Data_Business tableInfo) { var page = new PageModel(); var conn = link.GetValue("conn") as DbConnection; var cmd = conn.CreateCommand(); if (!IsFirtExtract(cmd, tableInfo.TableName)) { if (string.IsNullOrEmpty(columnInfo.TableName)) { cmd.CommandText = string.Format("select count(*) from ({0})", columnInfo.Sql); } else { cmd.CommandText = string.Format("select count(*) from {0}", columnInfo.TableName); } var dr = cmd.ExecuteReader(); while (dr.Read()) { page.total = dr[0].ToStr().ToLong(0); } dr.Close(); } else { page.total = (long)tableInfo.UpdateCount; } page.pageId = 1; page.pageSize = 1000; if ((page.total % page.pageSize) == 0) { page.pageCount = long.Parse((page.total / page.pageSize).ToString()); } else { page.pageCount = long.Parse(((page.total / page.pageSize) + 1).ToString()); } return(page); }
/// <summary> /// 获取条数 /// </summary> /// <param name="link"></param> /// <param name="tableInfo"></param> /// <returns></returns> public static PageModel GetTableCount(Data_Business_Details columnInfo, Data_Business tableInfo) { var page = new PageModel(); page.total = tableInfo.UpdateCount.ToStr().ToLong(0) * 10000; page.pageId = 1; page.pageSize = tableInfo.UpdateCount.ToStr().ToLong(0) * 10000; if ((page.total % page.pageSize) == 0) { page.pageCount = long.Parse((page.total / page.pageSize).ToString()); } else { page.pageCount = long.Parse(((page.total / page.pageSize) + 1).ToString()); } return(page); }
/// <summary> /// 修改列 /// </summary> /// <returns></returns> public static bool UpdateColumn(DataContext db, Data_Business table, Data_Business_Details column, Cache_Column columnInfo, Data_Source dataSource) { var sql = ""; if (db.config.DbType == DataDbType.Oracle) { sql = string.Format("alter table {0} modify {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)); } if (db.config.DbType == DataDbType.MySql) { sql = string.Format("alter table {0} modify {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)); } if (db.config.DbType == DataDbType.SqlServer) { sql = string.Format("alter table {0} alter column {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)); } return(db.ExecuteSql(sql, null, false).writeReturn.IsSuccess); }
/// <summary> /// 取读取第一列数据 /// </summary> /// <returns></returns> public static PageData GetFirstColumnData(Dictionary <string, object> link, Data_Business_Details columnInfo, Data_Business tableInfo, PageModel page) { var data = new PageData(); var type = link.GetValue("type").ToStr(); var conn = link.GetValue("conn") as DbConnection; var sql = ""; if (type == FastApp.DataDbType.Oracle.ToLower()) { var cmd = conn.CreateCommand(); if (IsFirtExtract(cmd, tableInfo.TableName)) { sql = string.Format("select * from(select field.a,field.b,ROWNUM RN from(select {0} a,{1} b from {2}) field where rownum<={3}) where rn>={4}" , columnInfo.Key, columnInfo.ColumnName, columnInfo.TableName, page.pageId * page.pageSize, (page.pageId - 1) * page.pageSize + 1); } else { if (string.IsNullOrEmpty(columnInfo.TableName)) { sql = string.Format("select * from ({0}) where rownum <={1}", columnInfo.Sql, tableInfo.UpdateCount); } else { sql = string.Format("select {0} a,{1} b from {2} where rownum <={3} order by {4} desc" , columnInfo.Key, columnInfo.ColumnName, columnInfo.TableName, tableInfo.UpdateCount, columnInfo.OrderBy); } } cmd.CommandText = sql; var dr = cmd.ExecuteReader(); while (dr.Read()) { var dic = new Dictionary <string, object>(); dic.Add("key", dr[0]); dic.Add("data", dr[1]); data.list.Add(dic); } dr.Close(); } if (type == FastApp.DataDbType.SqlServer.ToLower()) { var cmd = conn.CreateCommand(); if (IsFirtExtract(cmd, tableInfo.TableName)) { sql = string.Format("select top {0} * from (select row_number()over({1})temprownumber,* from (select tempcolumn=0,{1} a,{2} b from {3})t)tt where temprownumber>={4}" , page.pageSize, columnInfo.Key, columnInfo.ColumnName, columnInfo.TableName, page.pageId * page.pageSize - 1); } else { if (string.IsNullOrEmpty(columnInfo.TableName)) { sql = string.Format("select top {1} * from ({0})a", columnInfo.Sql, tableInfo.UpdateCount); } else { sql = string.Format("select top {0} {1} a,{2} b from {3} order by {4} desc" , tableInfo.UpdateCount, columnInfo.Key, columnInfo.ColumnName, columnInfo.TableName, columnInfo.OrderBy); } } cmd.CommandText = sql; var dr = cmd.ExecuteReader(); while (dr.Read()) { var dic = new Dictionary <string, object>(); dic.Add("key", dr[0]); dic.Add("data", dr[1]); data.list.Add(dic); } dr.Close(); } if (type == FastApp.DataDbType.MySql.ToLower()) { var cmd = conn.CreateCommand(); if (IsFirtExtract(cmd, tableInfo.TableName)) { sql = string.Format("select {0} a,{1} b from {2} where limit {3},{4}" , columnInfo.Key, columnInfo.ColumnName, columnInfo.TableName, (page.pageId - 1) * page.pageSize + 1, page.pageId * page.pageSize); } else { if (string.IsNullOrEmpty(columnInfo.TableName)) { sql = string.Format("select * from ({0}) limit {1}", columnInfo.Sql, tableInfo.UpdateCount); } else { sql = string.Format("select {0} a,{1} b from {2} where limit {3} order by {4} desc" , columnInfo.Key, columnInfo.ColumnName, columnInfo.TableName, tableInfo.UpdateCount, columnInfo.OrderBy); } } cmd.CommandText = sql; var dr = cmd.ExecuteReader(); while (dr.Read()) { var dic = new Dictionary <string, object>(); dic.Add("key", dr[0]); dic.Add("data", dr[1]); data.list.Add(dic); } dr.Close(); } return(data); }
/// <summary> /// 增加列 /// </summary> /// <returns></returns> public static bool AddColumn(DataContext db, Data_Business table, Data_Business_Details column, Cache_Column columnInfo, Data_Source dataSource) { return(db.ExecuteSql(string.Format("alter table {0} add {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)), null, false).writeReturn.IsSuccess); }
/// <summary> /// 操作 /// </summary> /// <param name="item"></param> /// <returns></returns> public IActionResult OnPostBusinessFormList(Data_Business_List item) { var result = new WriteReturn(); result.IsSuccess = true; using (var db = new DataContext(AppEtl.Db)) { if (IFast.Query <Data_Source>(a => a.Id == item.DataId).ToCount(db) == 0) { return(new JsonResult(new { success = false, msg = "数据源不存在" })); } var data = IFast.Query <Data_Source>(a => a.Id == item.DataId).ToItem <Data_Source>(db); var tableKey = string.Format(AppEtl.CacheKey.Table, data.Host); if (!RedisInfo.Exists(tableKey, AppEtl.CacheDb)) { DataSchema.InitTable(data, false); } var tableList = RedisInfo.Get <List <CacheTable> >(tableKey, AppEtl.CacheDb); foreach (var table in tableList) { var columnKey = string.Format(AppEtl.CacheKey.Column, data.Host, table.Name); if (!RedisInfo.Exists(columnKey, AppEtl.CacheDb)) { DataSchema.InitColumn(data, false, table.Name); } var tableModel = BaseMap.CopyModel <Data_Business, Data_Business_List>(item); tableModel.Id = Guid.NewGuid().ToStr(); tableModel.Name = string.IsNullOrEmpty(table.Comments) ? table.Name : table.Comments; tableModel.TableName = table.Name; if (result.IsSuccess) { result = db.Add(tableModel).writeReturn; } if (result.IsSuccess) { result = DataSchema.CreateTable(db, tableModel); } var columnList = RedisInfo.Get <List <CacheColumn> >(columnKey, AppEtl.CacheDb); var keyName = columnList.Find(a => a.IsKey == true)?.Name; var keyList = columnList.FindAll(a => a.IsKey == true); columnList.ForEach(column => { var columnModel = new Data_Business_Details(); columnModel.FieldId = Guid.NewGuid().ToStr(); columnModel.Id = tableModel.Id; columnModel.DataSourceId = data.Id; columnModel.TableName = table.Name; columnModel.ColumnName = column.Name; columnModel.FieldName = column.Name; columnModel.Key = keyName; if (result.IsSuccess) { result = db.Add(columnModel).writeReturn; } else { BaseLog.SaveLog(string.Format("tableName:{0},error:", table.Name, result.Message), "Error_CreateTable"); } if (result.IsSuccess) { if ((keyList.Count > 1 && keyList.Exists(a => a.Name == columnModel.FieldName))) { result = DataSchema.AddColumn(db, tableModel, columnModel, column, data, false); } else { result = DataSchema.AddColumn(db, tableModel, columnModel, column, data); } if (result.IsSuccess) { DataSchema.UpdateColumnComment(db, tableModel, columnModel, column, data); } } result.IsSuccess = true; }); if (keyList.Count > 1) { DataSchema.AddColumnMoreKey(db, tableModel, keyList); } } if (result.IsSuccess) { return(new JsonResult(new { success = true, msg = "操作成功" })); } else { return(new JsonResult(new { success = false, msg = result.Message })); } } }