/// <summary>
/// 保存业务配置
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Btn_SaveData(object sender, RoutedEventArgs e)
{
using (var db = new DataContext())
{
var isUpdateTable = false;
var isSuccess = true;
db.BeginTrans();
var main = Bussiness.SelectedItem as Data_Business;
if (main == null || string.IsNullOrEmpty(main.Name))
{
CodeBox.Show("业务名称不能为空", this);
return;
}
if (string.IsNullOrEmpty(main.TableName))
{
CodeBox.Show("业务表名不能为空", this);
return;
}
#region 业务
if (string.IsNullOrEmpty(main.Id))
{
//默认每天更新
main.UpdateDay = 1;
//默认晚上2点更新
main.UpdateTime = 2;
//默认更新条数1万
main.UpdateCount = 1;
//默认上次更新时间
main.LastUpdateTime = DateTime.Now;
//关联主键策略(1=重复删除,0=重复保留，2=重复更新)
main.Policy = "1";
//增加业务
main.Id = Guid.NewGuid().ToString();
isSuccess = db.Add(main).writeReturn.IsSuccess;
//创建表
if (isSuccess)
{
isSuccess = DataSchema.CreateTable(db, main);
}
//表备注
if (isSuccess)
{
isSuccess = DataSchema.UpdateTableComment(db, main);
}
}
else
{
//修改业务
if (isSuccess)
{
var oldTableName = FastRead.Query <Data_Business>(a => a.Id == main.Id, a => new { a.Name }).ToDic(db).GetValue("name").ToString();
isSuccess = db.Update <Data_Business>(main, a => a.Id == main.Id, a => new { a.Name }).writeReturn.IsSuccess;
if (oldTableName != main.TableName)
{
if (DataSchema.IsExistsTable(db, oldTableName))
{
isUpdateTable = true;
//修改表名
if (isSuccess)
{
isSuccess = DataSchema.UpdateTableName(db, main, oldTableName);
}
//修改表备注
if (isSuccess)
{
isSuccess = DataSchema.UpdateTableComment(db, main);
}
}
else
{
DataSchema.CreateTable(db, main);
}
}
}
}
#endregion
#region 业务明细
foreach (var temp in BussinessDetails.Items)
{
var leaf = temp as Data_Business_Details;
if (leaf == null)
{
continue;
}
if (string.IsNullOrEmpty(leaf.FieldName))
{
continue;
}
//数据源id
if (Common.GetTemplateColumn <ComboBox>(BussinessDetails, 1, "DataSourceBox", temp).SelectedItem == null)
{
continue;
}
var dataSource = (Common.GetTemplateColumn <ComboBox>(BussinessDetails, 1, "DataSourceBox", temp).SelectedItem as Data_Source);
leaf.DataSourceId = dataSource.Id;
//表名
if (Common.GetTemplateColumn <ComboBox>(BussinessDetails, 2, "TabelBox", temp).SelectedItem != null)
{
leaf.TableName = (Common.GetTemplateColumn <ComboBox>(BussinessDetails, 2, "TabelBox", temp).SelectedItem as Cache_Table).Name;
}
//源列名
var columnInfo = new Cache_Column();
if (Common.GetTemplateColumn <ComboBox>(BussinessDetails, 3, "ColumnBox", temp).SelectedItem != null)
{
columnInfo = Common.GetTemplateColumn <ComboBox>(BussinessDetails, 3, "ColumnBox", temp).SelectedItem as Cache_Column;
leaf.ColumnName = columnInfo.Name;
}
//主键
if (Common.GetTemplateColumn <ComboBox>(BussinessDetails, 4, "KeyBox", temp).SelectedItem != null)
{
columnInfo = Common.GetTemplateColumn <ComboBox>(BussinessDetails, 4, "KeyBox", temp).SelectedItem as Cache_Column;
leaf.Key = columnInfo.Name;
}
//排序
if (Common.GetTemplateColumn <ComboBox>(BussinessDetails, 5, "OrderByBox", temp).SelectedItem != null)
{
columnInfo = Common.GetTemplateColumn <ComboBox>(BussinessDetails, 5, "OrderByBox", temp).SelectedItem as Cache_Column;
leaf.OrderBy = columnInfo.Name;
}
//字典
if (Common.GetTemplateColumn <ComboBox>(BussinessDetails, 6, "DicBox", temp).SelectedItem != null)
{
var dic = Common.GetTemplateColumn <ComboBox>(BussinessDetails, 6, "DicBox", temp).SelectedItem as Data_Dic;
if (dic != null && !string.IsNullOrEmpty(dic.Id))
{
leaf.Dic = dic.Id;
}
}
//sql
leaf.Sql = (temp as Data_Business_Details).Sql;
if (string.IsNullOrEmpty(leaf.FieldId))
{
leaf.FieldId = Guid.NewGuid().ToString();
leaf.Id = main.Id;
//增加业务明细
if (isSuccess)
{
isSuccess = db.Add(leaf).writeReturn.IsSuccess;
}
//增加列
columnInfo = Common.GetTemplateColumn <ComboBox>(BussinessDetails, 3, "ColumnBox", temp).SelectedItem as Cache_Column;
if (isSuccess)
{
isSuccess = DataSchema.AddColumn(db, main, leaf, columnInfo, dataSource);
}
//修改备注
if (isSuccess)
{
isSuccess = DataSchema.UpdateColumnComment(db, main, leaf, columnInfo, dataSource);
}
}
else
{
//修改业务明细
if (isSuccess)
{
isSuccess = db.Update <Data_Business_Details>(leaf, a => a.FieldId == leaf.FieldId, a => new { a.FieldName, a.DataSourceId, a.ColumnName, a.Key, a.OrderBy, a.TableName, a.Dic, a.Sql }).writeReturn.IsSuccess;
}
//列增加修改
if (isSuccess)
{
if (DataSchema.IsExistsColumn(db, main.Name, leaf.FieldName))
{
isSuccess = DataSchema.UpdateColumn(db, main, leaf, columnInfo, dataSource);
}
else
{
isSuccess = DataSchema.AddColumn(db, main, leaf, columnInfo, dataSource);
}
}
}
}
#endregion
if (isSuccess)
{
if (isUpdateTable)
{
Bussiness.ItemsSource = AppCache.GetAllBusiness;
Common.UpdateWindow();
}
db.SubmitTrans();
CodeBox.Show("保存业务成功", this);
}
else
{
db.RollbackTrans();
CodeBox.Show("保存业务失败", this);
}
}
}
/// <summary>
/// 获取字段类型
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private static string GetFieldType(Cache_Column item, ConfigModel config, Data_Source source)
{
if (config.DbType.ToLower() == DataDbType.Oracle.ToLower() && source.Type.ToLower() == DataDbType.SqlServer.ToLower())
{
#region sqlserver to oracle
if (string.IsNullOrEmpty(item.Type))
{
return("nvarchar2(255)");
}
switch (item.Type.ToLower())
{
case "bit":
case "int":
case "smallint":
case "tinyint":
return("integer");
case "datetime":
case "smalldatetime":
return("date");
case "decimal":
case "numeric":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
case "money":
case "smallmoney":
case "real":
return("real");
case "uniqueidentifier":
return(string.Format("char({0})", item.Length));
case "nchar":
case "nvarchar":
{
if (item.Length > 4000)
{
return("clob");
}
else
{
return(string.Format("nvarchar2({0})", item.Precision / 2));
}
}
case "varchar":
case "char":
{
if (item.Length > 4000)
{
return("clob");
}
else
{
return(string.Format("varchar2({0})", item.Precision));
}
}
case "text":
case "ntext":
return("clob");
case "binary":
case "varbinary":
case "image":
return("blob");
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == DataDbType.Oracle.ToLower() && source.Type.ToLower() == DataDbType.MySql.ToLower())
{
#region mysql to oracle
if (string.IsNullOrEmpty(item.Type))
{
return("nvarchar2(255)");
}
switch (item.Type.ToLower())
{
case "bigint":
return("number(19,0)");
case "bit":
case "tinyblob":
return("raw");
case "blob":
return("blob");
case "char":
return(string.Format("char{0}", item.Length));
case "date":
case "datetime":
case "time":
case "timestamp":
return("date");
case "deciaml":
case "double":
case "real":
case "double precision":
return("float(24)");
case "float":
return("float");
case "int":
case "integer":
return("number(10, 0)");
case "longblob":
case "mediumblog":
return("blob");
case "longtext":
case "mediumtext":
case "text":
case "tinytext":
case "varchar":
return("clob");
case "mediumint":
return("number(7,0)");
case "numeric":
case "year":
return("number");
case "smallint":
return("number(5,0)");
case "tinyint":
return("number(3,0)");
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == DataDbType.SqlServer.ToLower() && source.Type.ToLower() == DataDbType.Oracle.ToLower())
{
#region oracle to sqlserver
if (string.IsNullOrEmpty(item.Type))
{
return("varchar(255)");
}
switch (item.Type.ToLower())
{
case "char":
case "varchar2":
return(string.Format("varchar({0})", item.Length));
case "nchar":
case "nvarchar2":
return(string.Format("nvarchar({0})", item.Length / 2));
case "date":
return("datetime");
case "long":
return("text");
case "bfile":
case "blob":
case "long raw":
case "raw":
case "nrowid":
case "binary":
return("image");
case "rowid":
return("uniqueidentifier");
case "number":
case "decimal":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
case "integer":
return("int");
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == DataDbType.SqlServer.ToLower() && source.Type.ToLower() == DataDbType.MySql.ToLower())
{
#region MySql to sqlserver
if (string.IsNullOrEmpty(item.Type))
{
return("varchar(255)");
}
switch (item.Type.ToLower())
{
case "tinyint":
return("bit");
case "text":
return("ntext");
case "varchar":
return(string.Format("nvarchar({0})", item.Length));
case "char":
return(string.Format("nchar({0})", item.Length));
case "decimal":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == DataDbType.MySql.ToLower() && source.Type.ToLower() == DataDbType.Oracle.ToLower())
{
#region Oracle to MySql
if (string.IsNullOrEmpty(item.Type))
{
return("varchar(255)");
}
switch (item.Type.ToLower())
{
case "colb":
return("text");
case "date":
return("datetime");
case "varchar2":
return(string.Format("varchar({0})", item.Length));
case "number":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == DataDbType.MySql.ToLower() && source.Type.ToLower() == DataDbType.SqlServer.ToLower())
{
#region SqlServer to MySql
if (string.IsNullOrEmpty(item.Type))
{
return("varchar(255)");
}
switch (item.Type.ToLower())
{
case "datetime2":
case "datetimeoffset":
case "smalldatetime":
return("datetime");
case "uniqueidentifier":
return("varchar(40)");
case "bit":
return("bigint");
case "money":
case "real":
case "smallmoney":
return("float");
case "xml":
case "ntext":
return("text");
case "decimal":
case "numeric":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
case "char":
return(string.Format("char({0})", item.Length));
case "nchar":
return(string.Format("char({0})", item.Length / 2));
case "varchar":
return(string.Format("varchar({0})", item.Length));
case "nvarchar":
return(string.Format("varchar({0})", item.Length / 2));
default:
return(item.Type);
}
#endregion
}
else
{
#region default
switch (item.Type.ToLower())
{
case "char":
case "varchar":
case "varchar2":
return(string.Format("{0}({1})", item.Type, item.Length == -1 ? "max" : item.Length.ToString()));
case "nchar":
case "nvarchar":
case "nvarchar2":
return(string.Format("{0}({1})", item.Type, item.Length == -1 ? "max" : (item.Length / 2).ToString()));
case "decimal":
case "numeric":
case "number":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("{0}({1},{2})", item.Type, item.Precision, item.Scale));
}
default:
return(item.Type);
}
#endregion
}
}
/// <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 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>
/// <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>
private static void InitColumn(Data_Source link, bool IsLoad, string tableName)
{
if (AppCache.ExistsColumn(link, tableName) && IsLoad)
{
return;
}
var list = new List <Cache_Column>();
var dt = new DataTable();
//oracle 列信息
if (link.Type == DataDbType.Oracle)
{
#region oracle
using (var conn = new OracleConnection(BaseLink.GetConnStr(link)))
{
tableName = tableName.ToUpper();
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select a.column_name,data_type,data_length,b.comments,
(select count(0) from all_cons_columns aa, all_constraints bb
where aa.constraint_name = bb.constraint_name and bb.constraint_type = 'P' and bb.table_name = '"
+ tableName + @"' and aa.column_name=a.column_name),(select count(0) from all_ind_columns t,all_indexes i
where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = '"
+ tableName + @"' and t.column_name=a.column_name),nullable,data_precision,data_scale
from all_tab_columns a inner join all_col_comments b
on a.table_name='" + tableName +
"' and a.table_name=b.table_name and a.column_name=b.column_name order by a.column_id asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
if (link.Type == DataDbType.SqlServer)
{
#region sql server
using (var conn = new SqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select a.name,(select top 1 name from sys.systypes c where a.xtype=c.xtype) as type ,
length,b.value,(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='"
+ tableName + @"' and COLUMN_NAME=a.name),
(SELECT count(0) FROM sysindexes aa JOIN sysindexkeys bb ON aa.id=bb.id AND aa.indid=bb.indid
JOIN sysobjects cc ON bb.id=cc.id JOIN syscolumns dd ON bb.id=dd.id AND bb.colid=dd.colid
WHERE aa.indid NOT IN(0,255) AND cc.name='" + tableName + @"' and dd.name=a.name),isnullable,prec,scale
from syscolumns a left join sys.extended_properties b
on major_id = id and minor_id = colid and b.name ='MS_Description'
where a.id=object_id('" + tableName + "') order by a.colid asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
if (link.Type == DataDbType.MySql)
{
#region mysql
using (var conn = new MySqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select column_name,data_type,character_maximum_length,column_comment,
(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a where TABLE_SCHEMA='" + link.ServerName
+ "' and TABLE_NAME='" + tableName + @"' and constraint_name='PRIMARY' and c.column_name=a.column_name),
(SELECT count(0) from information_schema.statistics a where table_schema = '"
+ link.ServerName + "' and table_name = '" + tableName + @"' and c.column_name=a.column_name),
is_nullable,numeric_precision,numeric_scale,column_type from information_schema.columns c where table_name='"
+ tableName + "' order by ordinal_position asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
foreach (DataRow item in dt.Rows)
{
var column = new Cache_Column();
column.Name = (item.ItemArray[0] == DBNull.Value ? "" : item.ItemArray[0].ToString());
column.Type = item.ItemArray[1] == DBNull.Value ? "" : item.ItemArray[1].ToString();
column.Length = item.ItemArray[2] == DBNull.Value ? 0 : decimal.Parse(item.ItemArray[2].ToString());
column.Comments = item.ItemArray[3] == DBNull.Value ? "" : item.ItemArray[3].ToString();
column.Precision = item.ItemArray[7] == DBNull.Value ? 0 : int.Parse(item.ItemArray[7].ToString());
column.ShowName = string.Format("{0}({1})", column.Name, column.Comments);
list.Add(column);
}
AppCache.SetColumnList(list, link, tableName);
}
