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