/// <summary>
/// 增加列多主键
/// </summary>
/// <returns></returns>
public static WriteReturn AddColumnMoreKey(DataContext db, Data_Business table, List <CacheColumn> list)
{
var filedName = "";
list.ForEach(a => { filedName += a.Name + ","; });
return(db.ExecuteSql(string.Format("alter table {0} add constraint {0}_key primary key ({1})", table.TableName, filedName.Substring(0, filedName.Length - 1)), null, false).writeReturn);
}
/// <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>
public static void ExpireData(DataContext db, Data_Business item)
{
var month = item.SaveDataMonth.ToStr().ToInt(0) * -1;
var sql = string.Format("delete from {0} where addtime<='{1}'", item.TableName, DateTime.Now.AddMonths(month));
db.ExecuteSql(sql, null, true);
}
/// <summary>
/// 修改表备注
/// </summary>
/// <returns></returns>
public static bool UpdateTableComment(DataContext db, Data_Business table)
{
var sql = "";
if (db.config.DbType == DataDbType.MySql)
{
sql = string.Format("alter table {0} comment '{1}'", table.TableName, table.Name);
}
if (db.config.DbType == DataDbType.Oracle)
{
sql = string.Format("Comment on table {0} is '{1}'", table.TableName, table.Name);
}
if (db.config.DbType == DataDbType.SqlServer)
{
sql = string.Format("select count(0) count from sys.extended_properties where object_id('{0}')=major_id and minor_id=0", table.TableName);
var count = db.ExecuteSql(sql, null, false).DicList[0]["count"].ToStr().ToInt(0);
if (count >= 1)
{
sql = string.Format("execute sp_updateextendedproperty N'MS_Description', '{0}', N'user', N'dbo', N'table', N'{1}', NULL, NULL", table.TableName, table.Name);
}
else
{
sql = string.Format("execute sp_addextendedproperty N'MS_Description', '{0}', N'user', N'dbo', N'table', N'{1}', NULL, NULL", table.TableName, table.Name);
}
}
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>
/// <param name="id"></param>
public void OnGet(string id)
{
if (!string.IsNullOrEmpty(id))
{
using (var db = new DataContext(AppEtl.Db))
{
info = IFast.Query <Data_Business>(a => a.Id == id).ToItem <Data_Business>(db);
}
}
}
/// <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>
public static void ExpireData(DataContext db, Data_Business item)
{
var month = item.SaveDataMonth.ToStr().ToInt(0) * -1;
var param = new List <OracleParameter>();
param.Add(new OracleParameter {
ParameterName = "etladdtime", Value = DateTime.Now.AddMonths(month)
});
var sql = string.Format("delete from {0} where etladdtime<=:etladdtime", item.TableName);
db.ExecuteSql(sql, param.ToArray(), true);
}
/// <summary>
/// 操作
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public IActionResult OnPostBusinessForm(Data_Business item)
{
var info = new WriteReturn();
using (var db = new DataContext(AppEtl.Db))
{
db.BeginTrans();
if (IFast.Query <Data_Business>(a => a.Id == item.Id).ToCount(db) == 0)
{
item.Id = Guid.NewGuid().ToString();
info = db.Add(item).writeReturn;
if (info.IsSuccess)
{
info = DataSchema.CreateTable(db, item);
}
}
else
{
var oldTableName = IFast.Query <Data_Business>(a => a.Id == item.Id, a => new { a.TableName }).ToDic(db).GetValue("TableName").ToStr();
info = db.Update <Data_Business>(item, a => a.Id == item.Id).writeReturn;
if (info.IsSuccess)
{
if (oldTableName.ToLower() != item.TableName.ToLower())
{
DataSchema.DropTable(db, oldTableName);
info = DataSchema.CreateTable(db, item);
}
else
{
DataSchema.UpdateTableComment(db, item);
}
}
}
if (info.IsSuccess)
{
db.SubmitTrans();
return(new JsonResult(new { success = true, msg = "操作成功" }));
}
else
{
db.RollbackTrans();
return(new JsonResult(new { success = false, msg = info.Message }));
}
}
}
/// <summary>
/// 数据策略
/// </summary>
public static bool DataPolicy(DataContext db, Data_Business item, object key, string columnName, object columnValue)
{
if (item.Policy == "1")
{
var sql = string.Format("delete from {0} where key='{1}'", item.TableName, key);
db.ExecuteSql(sql, null, true);
return(true);
}
if (item.Policy == "2")
{
var sql = string.Format("update {0} set {1}='{2}' where key='{3}'", item.TableName, columnName, columnValue, key);
db.ExecuteSql(sql, null, true);
return(false);
}
return(true);
}
/// <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 bool UpdateTableName(DataContext db, Data_Business table, string oldTableName)
{
var sql = "";
if (db.config.DbType == DataDbType.Oracle)
{
sql = string.Format("alter table {0} rename to {1}", oldTableName, table.TableName);
}
if (db.config.DbType == DataDbType.MySql)
{
sql = string.Format("alter table {0} rename{1}", oldTableName, table.TableName);
}
if (db.config.DbType == DataDbType.SqlServer)
{
sql = string.Format("exec sp_rename '{0}', '{1}' ", oldTableName, table.TableName);
}
return(db.ExecuteSql(sql, null, false).writeReturn.IsSuccess);
}
/// <summary>
/// 创建表
/// </summary>
/// <returns></returns>
public static bool CreateTable(DataContext db, Data_Business table)
{
var isSuccess = false;
if (db.config.DbType == DataDbType.Oracle)
{
isSuccess = db.ExecuteSql(string.Format("create table {0}(Id varchar2(64) primary key,AddTime date,key varchar2(255))", table.TableName), null, false).writeReturn.IsSuccess;
if (isSuccess)
{
db.ExecuteSql(string.Format("Comment on column {0}.Id is '主键'", table.TableName), null, false);
db.ExecuteSql(string.Format("Comment on column {0}.AddTime is '抽取时间'", table.TableName), null, false);
db.ExecuteSql(string.Format("Comment on column {0}.key is '关键主键'", table.TableName), null, false);
}
}
if (db.config.DbType == DataDbType.MySql)
{
isSuccess = db.ExecuteSql(string.Format("create table {0}(Id varchar(64) primary key,AddTime date,key varchar(255))", table.TableName), null, false).writeReturn.IsSuccess;
if (isSuccess)
{
db.ExecuteSql(string.Format("alter table {0} modify Id varchar2(64) comment '主键'", table.TableName), null, false);
db.ExecuteSql(string.Format("alter table {0} modify AddTime date comment '抽取时间'", table.TableName), null, false);
db.ExecuteSql(string.Format("alter table {0} modify key varchar2(255) comment '关键主键'", table.TableName), null, false);
}
}
if (db.config.DbType == DataDbType.SqlServer)
{
isSuccess = db.ExecuteSql(string.Format("create table {0}(Id varchar(64) primary key,AddTime date,key varchar(255))", table.TableName), null, false).writeReturn.IsSuccess;
if (isSuccess)
{
db.ExecuteSql(string.Format("exec sys.sp_addextendedproperty N'MS_Description',N'主键',N'SCHEMA', N'dbo', N'TABLE',N'{0}',N'column',N'Id'", table.TableName), null, false);
db.ExecuteSql(string.Format("exec sys.sp_addextendedproperty N'MS_Description',N'抽取时间',N'SCHEMA', N'dbo', N'TABLE',N'{0}',N'column',N'AddTime'", table.TableName), null, false);
db.ExecuteSql(string.Format("exec sys.sp_addextendedproperty N'MS_Description',N'关键主键',N'SCHEMA', N'dbo', N'TABLE',N'{0}',N'column',N'key'", table.TableName), null, false);
}
}
return(isSuccess);
}
/// <summary>
/// 创建表
/// </summary>
/// <returns></returns>
public static WriteReturn CreateTable(DataContext db, Data_Business table)
{
var result = new WriteReturn();
if (db.config.DbType.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
result = db.ExecuteSql(string.Format("create table {0}(EtlAddTime date,Etlkey varchar(255))", table.TableName), null, false).writeReturn;
if (result.IsSuccess)
{
db.ExecuteSql(string.Format("Comment on column {0}.EtlAddTime is '抽取时间'", table.TableName), null, false);
db.ExecuteSql(string.Format("Comment on column {0}.Etlkey is '关键主键'", table.TableName), null, false);
}
}
if (db.config.DbType.ToLower() == AppEtl.DataDbType.MySql.ToLower())
{
result = db.ExecuteSql(string.Format("create table {0}(AddTime date,key varchar(255))", table.TableName), null, false).writeReturn;
if (result.IsSuccess)
{
db.ExecuteSql(string.Format("alter table {0} modify EtlAddTime date comment '抽取时间'", table.TableName), null, false);
db.ExecuteSql(string.Format("alter table {0} modify Etlkey varchar2(255) comment '关键主键'", table.TableName), null, false);
}
}
if (db.config.DbType.ToLower() == AppEtl.DataDbType.SqlServer.ToLower())
{
result = db.ExecuteSql(string.Format("create table {0}(AddTime date,key varchar(255))", table.TableName), null, false).writeReturn;
if (result.IsSuccess)
{
db.ExecuteSql(string.Format("exec sys.sp_addextendedproperty N'MS_Description',N'抽取时间',N'SCHEMA', N'dbo', N'TABLE',N'{0}',N'column',N'EtlAddTime'", table.TableName), null, false);
db.ExecuteSql(string.Format("exec sys.sp_addextendedproperty N'MS_Description',N'关键主键',N'SCHEMA', N'dbo', N'TABLE',N'{0}',N'column',N'Etlkey'", table.TableName), null, false);
}
}
return(result);
}
/// <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>
/// <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);
}
