/// <summary>
/// 数据库名下拉框下拉时，远程获取所有数据库名
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DSDBCB_DropDownOpened(object sender, EventArgs e)
{
if (this.SelectedItem == null)
{
return;
}
if (!(this.SelectedItem is DesignerDataBaseDataSource))
{
return;
}
DesignerDataBaseDataSource ds = SelectedItem as DesignerDataBaseDataSource;
if (!string.IsNullOrEmpty(ds.Address) && !string.IsNullOrEmpty(ds.UserName) && !string.IsNullOrEmpty(ds.PassWord))
{
string constr = string.Format("Server={0};database={1};uid={2};pwd={3}", ds.Address, "master", ds.UserName, ds.PassWord);
using (SqlExcuter se = new SqlExcuter(DataBaseType.SqlServer, constr))
{
DataTable data = se.ExecuteSelectSql("select name from master..sysdatabases");
DSDBCB.Items.Clear();
foreach (DataRow r in data.Rows)
{
string n = r["name"].ToString();
DSDBCB.Items.Add(n);
}
}
}
else
{
MessageBox.Show("请输入完整信息");
}
}
protected override object DoUpdate(UpdateContext context, UpdateConfig config, IEnumerable <object> datas, object complexData)
{
var sql = config.Sql;
var cols = GetCols(config);
var key = GetKeyName(config, cols);
IValueSetter valueSetter = ValueSetterCreater.Create(datas.First());
//1.自定义SQl一条条插入
if (!string.IsNullOrEmpty(sql) || !string.IsNullOrEmpty(config.Filter))
{
if (string.IsNullOrEmpty(sql))
{
config.Sql = DBHelper.BuildUpdateSql(cols, config, key, SqlKeyWorld.ComplexDataObjectStart);
}
config.ReturnId = true;
var keys = valueSetter.GetFields(datas.First());
foreach (var data in datas)
{
context.ContentParams.ReplaceOrInsert(SqlKeyWorld.ComplexData, data);
UpdateTrigger.TriggeValuesChecked(context, data, config, cols, ActionType.Update, valueSetter, keys);
SqlExcuter.ExcuteTrann(context);
ExcuteSubUpdate(context, config, data);
}
return(datas.Count());
}
return(UpdateOnOneTime(context, config, cols, datas, valueSetter, key));
}
public void ShowTableData(DBSource db, string dbName, string tablename, string sql)
{
var title = $"[查询数据 {tablename} -{db.ServerName}]";
foreach (TabPage page in this.TabControl.TabPages)
{
if (page.Text == title)
{
TabControl.SelectedTab = page;
return;
}
}
var viewTb = new SqlExcuter(db, dbName, sql);
//ViewTBData viewTb = new ViewTBData();
viewTb.Text = title;
viewTb.BorderStyle = BorderStyle.None;
this.TabControl.TabPages.Add(viewTb);
TabControl.SelectedTab = viewTb;
tsb_Excute.Enabled = true;
this.TSCBServer.Text = db.ServerName;
this.TSCBServer.Visible = true;
//viewTb.DBSource = db;
//viewTb.DBName = dbName;
//viewTb.SQLString = sql;
}
protected override object DoQuery(Context context)
{
var queryConfig = context.Config;
var handlerConfig = context.HandlerConfig;
if (string.IsNullOrEmpty(queryConfig.Connection))
{
queryConfig.Connection = handlerConfig.Connection;
}
IValueSetter valueSetter = queryConfig.Create();
var datas = SqlExcuter.ExcuteQuery(context, valueSetter);
context.Data = datas;
ExcuteSubQueries(context, queryConfig, valueSetter, datas);
if (!queryConfig.OnlyOne)
{
return(datas);
}
if (datas.Any())
{
return(datas.First());
}
return(new object());
}
/// <summary>
/// 使用Indusfo.DAL 返回DataTable
/// </summary>
/// <param name="designerDataBaseDataSource"></param>
/// <returns></returns>
private static Task <DataTable> ExcuteDataBaseDataSource(DesignerDataBaseDataSource ds)
{
return(Task <DataTable> .Run(() =>
{
using (SqlExcuter se = new SqlExcuter(DataBaseType.SqlServer, ds.ConnectionString))
{
return se.ExecuteSelectSql(ds.SqlString);
}
}));
}
protected override object DoQuery(Context context)
{
var queryConfig = context.Config;
var handlerConfig = context.HandlerConfig;
if (string.IsNullOrEmpty(queryConfig.Connection))
{
queryConfig.Connection = handlerConfig.Connection;
}
return(SqlExcuter.ExcuteScalar(context));
}
protected override object DoQuery(Context context)
{
var queryConfig = context.Config;
var handlerConfig = context.HandlerConfig;
if (string.IsNullOrEmpty(queryConfig.Connection))
{
queryConfig.Connection = handlerConfig.Connection;
}
IValueSetter valueSetter = new ValueSetter.SimpleRecordQueryHandler();
var datas = SqlExcuter.ExcuteQuery(context, valueSetter);
context.Data = datas;
return(datas);
}
private void DataViewButton_Click(object sender, RoutedEventArgs e)
{
try
{
using (SqlExcuter se = new SqlExcuter(DataBaseType.SqlServer, (this.SelectedItem as DesignerDataBaseDataSource).ConnectionString))
{
DataTable data = se.ExecuteSelectSql((this.SelectedItem as DesignerDataBaseDataSource).SqlString);
DataTablePreViewWindow win = new DataTablePreViewWindow();
win.Grid.ItemsSource = data.DefaultView;
win.Show();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
protected override object DoUpdate(UpdateContext context)
{
var config = context.Config;
if (string.IsNullOrEmpty(config.Connection))
{
config.Connection = context.HandlerConfig.Connection;
}
var cols = GetCols(config);
ApplyRules(context, config, cols);
var sql = config.Sql;
if (!string.IsNullOrEmpty(sql))
{
if (config.InTran == false)
{
return(SqlExcuter.Excute(context));
}
else
{
return(SqlExcuter.ExcuteTrann(context));
}
}
if (string.IsNullOrEmpty(config.Table))
{
throw new ArgumentException(nameof(config.Table));
}
config.Sql = BuilderSql(context, config, cols);
object result;
if (config.InTran == false)
{
result = SqlExcuter.Excute(context);
}
else
{
result = SqlExcuter.ExcuteTrann(context);
}
return(result);
}
private object DeleteOnOneTime(UpdateContext context, UpdateConfig config, IDictionary <string, string> cols, IEnumerable <object> datas, IValueSetter valueSetter, string key)
{
ICollection <object> ids = new List <object>();
foreach (var data in datas)
{
var id = valueSetter.GetValue(data, key);
ids.Add(id);
ExcuteSubUpdate(context, config, data);
}
var sql = $"DELETE FROM {config.Table} where {key} in ({string.Join(SqlKeyWorld.Split1, ids)});";
config.Sql = sql;
object result = SqlExcuter.ExcuteTrann(context);
return(result);
}
protected override object DoUpdate(UpdateContext context, UpdateConfig config, IEnumerable <object> datas, object complexData)
{
var sql = config.Sql;
var ingoreKey = config.Config[IngoreKey].ToSingleData <bool>();
var cols = GetCols(config);
var key = GetKeyName(config, cols);
IValueSetter valueSetter = ValueSetterCreater.Create(datas.First());
//1.自定义SQl一条条插入
//2.未包含Key又没有忽略产生Key说明是要数据库自动生成
if (!string.IsNullOrEmpty(sql) || (!cols.ContainsKey(key) && !ingoreKey))
{
if (string.IsNullOrEmpty(sql))
{
config.Sql = DBHelper.BuildInsertSql(cols, config.Table, SqlKeyWorld.ComplexDataObjectStart);
}
config.ReturnId = true;
var keys = valueSetter.GetFields(datas.First());
foreach (var data in datas)
{
context.ContentParams.ReplaceOrInsert(SqlKeyWorld.ComplexData, data);
UpdateTrigger.TriggeDefaultValues(context, data, config, cols, valueSetter, keys);
UpdateTrigger.TriggeValuesChecked(context, data, config, cols, ActionType.Insert, valueSetter, keys);
var result = SqlExcuter.ExcuteTrann(context);
long id = 0;
if (result is long || result is int)
{
id = long.Parse(result.ToString());
valueSetter.SetField(data, id, key);
}
ExcuteSubUpdate(context, config, data);
}
return(datas.Count());
}
return(InsertOnOneTime(context, config, cols, datas, valueSetter));
}
private void ExecutSql(DBSource source, string db, string sql)
{
var tit = $"执行语句";
//foreach (TabPage tab in this.TabControl.TabPages)
//{
// if (tab.Text.Equals(tit))
// {
// (tab as UC.WebTab).SetHtml(html);
// TabControl.SelectedTab = tab;
// return;
// }
//}
SqlExcuter se = new SqlExcuter(source, db, sql);
se.Text = tit;
this.TabControl.TabPages.Add(se);
this.TabControl.SelectedTab = se;
tsb_Excute.Enabled = true;
this.TSCBServer.Text = source.ServerName;
this.TSCBServer.Visible = true;
}
private void TestConnectionButton_Click(object sender, RoutedEventArgs e)
{
if (this.SelectedItem == null)
{
return;
}
if (this.SelectedItem is DesignerDataBaseDataSource)
{
using (SqlExcuter se = new SqlExcuter(DataBaseType.SqlServer, (this.SelectedItem as DesignerDataBaseDataSource).ConnectionString))
{
DataTable data = se.ExecuteSelectSql("select 1");
if (data == null)
{
MessageBox.Show("连接失败");
}
else
{
MessageBox.Show("连接成功");
}
}
}
}
protected override object DoUpdate(UpdateContext context)
{
context.Submit();
var queryConfig = context.Config;
var handlerConfig = context.HandlerConfig;
if (string.IsNullOrEmpty(queryConfig.Connection))
{
queryConfig.Connection = handlerConfig.Connection;
}
IValueSetter valueSetter = queryConfig.Create();
var querySql = queryConfig.Config["query_sql"].ToSingleData <string>(string.Empty);
var datas = SqlExcuter.ExcuteQuery(context, valueSetter, querySql);
foreach (var data in datas)
{
context.ContentParams.ReplaceOrInsert(SqlKeyWorld.ComplexData, data);
SqlExcuter.ExcuteTrann(context);
}
return(true);
}
private object InsertOnOneTime(UpdateContext context, UpdateConfig config, IDictionary <string, string> cols, IEnumerable <object> datas, IValueSetter valueSetter)
{
StringBuilder sb = new StringBuilder();
var columnInfos = TableColumnQueryHandler.QueryColumns(config).Where(c => cols.Keys.Contains(c.ColName) && DBHelper.SpecailColumn(c)).ToArray();
sb.AppendLine($"insert into {config.Table} ({string.Join(SqlKeyWorld.Split1, cols.Keys)}) values ");
var len = datas.Count();
var keys = valueSetter.GetFields(datas.First());
for (var i = 0; i < len; i++)
{
var data = datas.ElementAt(i);
context.ContentParams.ReplaceOrInsert(SqlKeyWorld.ComplexData, data);
UpdateTrigger.TriggeDefaultValues(context, data, config, cols, valueSetter, keys);
UpdateTrigger.TriggeValuesChecked(context, data, config, cols, ActionType.Insert, valueSetter, keys);
sb.AppendLine(string.Intern("("));
var colVals = cols.Select(c => DBHelper.BuildColVal(c, valueSetter, data, columnInfos));
sb.Append(string.Join(SqlKeyWorld.Split1, colVals));
sb.Append(string.Intern(")"));
if (i != len - 1)
{
sb.Append(SqlKeyWorld.Split1);
}
}
config.Sql = sb.ToString();
object result = SqlExcuter.ExcuteTrann(context);
foreach (var data in datas)
{
ExcuteSubUpdate(context, config, data);
}
return(result);
}
private object UpdateOnOneTime(UpdateContext context, UpdateConfig config, IDictionary <string, string> cols, IEnumerable <object> datas, IValueSetter valueSetter, string key)
{
var columnInfos = TableColumnQueryHandler.QueryColumns(config).Where(c => cols.Keys.Contains(c.ColName) && DBHelper.SpecailColumn(c)).ToArray();
StringBuilder sb = new StringBuilder();
var len = datas.Count();
var keys = valueSetter.GetFields(datas.First());
var page = len / PerCount;
if (page % PerCount != 0)
{
page++;
}
if (page == 0 && len > 0)
{
page = 1;
}
var cCount = cols.Count();
for (var p = 0; p < page; p++)
{
var currentIndex = p * PerCount;
ICollection <object> ids = new List <object>();
IDictionary <string, StringBuilder> dictSbs = new Dictionary <string, StringBuilder>();
for (var i = currentIndex; i < currentIndex + PerCount; i++)
{
if (i >= len)
{
break;
}
var data = datas.ElementAt(i);
context.ContentParams.ReplaceOrInsert(SqlKeyWorld.ComplexData, data);
UpdateTrigger.TriggeValuesChecked(context, data, config, cols, ActionType.Update, valueSetter, keys);
var id = valueSetter.GetValue(data, key);
ids.Add(id);
for (var c = 0; c < cCount; c++)
{
var col = cols.ElementAt(c);
if (col.Key.Equals(key, StringComparison.OrdinalIgnoreCase))
{
continue;
}
StringBuilder colSb;
if (!dictSbs.TryGetValue(col.Key, out colSb))
{
colSb = new StringBuilder();
dictSbs.Add(col.Key, colSb);
}
colSb.Append($" WHEN '{id}' THEN {DBHelper.BuildColVal(col, valueSetter, data, columnInfos)} ");
}
}
if (ids.Any() == false)
{
break;
}
sb.AppendFormat(DBHelper.UpdateFormatter, config.Table);
var index = 0;
foreach (var colSb in dictSbs)
{
sb.Append(string.Concat("`", colSb.Key, "` = CASE `", key, "` "));
sb.Append(colSb.Value.ToString());
sb.Append(End);
index++;
if (index < cCount)
{
sb.Append(SqlKeyWorld.Split3);
}
}
sb.Append($"{DBHelper.Where}{key} {SqlKeyWorld.In} ({string.Join(SqlKeyWorld.Split1, ids)});");
}
config.Sql = sb.ToString();
object result = SqlExcuter.ExcuteTrann(context);
foreach (var data in datas)
{
ExcuteSubUpdate(context, config, data);
}
return(result);
}
