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