public void Fetch() { if (Target == null) { return; } Db2SourceContext ctx = Target.Context; if (ctx == null) { return; } int?limit = null; if (IsChecked(checkBoxLimitRow) && !string.IsNullOrEmpty(textBoxLimitRow.Text)) { int l; if (!int.TryParse(textBoxLimitRow.Text, out l)) { MessageBox.Show("件数が数字ではありません", "エラー", MessageBoxButton.OK, MessageBoxImage.Error); textBoxLimitRow.Focus(); } limit = l; } int offset; string sql = Target.GetSelectSQL(null, textBoxCondition.Text, string.Empty, limit, HiddenLevel.Visible, out offset); try { using (IDbConnection conn = ctx.NewConnection(true)) { using (IDbCommand cmd = ctx.GetSqlCommand(sql, null, conn)) { UpdateDataGridResult(cmd); } } } catch (Exception t) { ctx.OnLog(ctx.GetExceptionMessage(t), LogStatus.Error, sql); Db2SrcDataSetController.ShowErrorPosition(t, textBoxCondition, ctx, offset); } finally { UpdateTextBlockWarningLimit(); } }
public void Execute() { if (Target == null) { return; } Db2SourceContext ctx = Target.Context; if (ctx == null) { return; } try { foreach (ParamEditor p in dataGridParameters.ItemsSource) { p.SetValue(); } using (IDbConnection conn = ctx.NewConnection(true)) { IDbCommand cmd = Target.DbCommand; try { cmd.Connection = conn; UpdateDataGridResult(cmd); } catch (Exception t) { ctx.OnLog(ctx.GetExceptionMessage(t), LogStatus.Error, Target.FullName); } finally { cmd.Connection = null; } } } catch (Exception t) { MessageBox.Show(ctx.GetExceptionMessage(t), "エラー", MessageBoxButton.OK, MessageBoxImage.Error); } }
private void AddReference(List <ColumnValue> list, ForeignKeyConstraint constraint) { Db2SourceContext dataSet = Table.Context; Table refTbl = dataSet.Tables[constraint.ReferenceSchemaName, constraint.ReferenceTableName]; if (refTbl == null) { return; } ColumnInfo[] cols = Row.GetForeignKeyColumns(constraint); //List<IDbDataParameter> prms = new List<IDbDataParameter>(cols.Length); StringBuilder buf = new StringBuilder(); string join = string.Empty; for (int i = 0, n = cols.Length; i < n; i++) { ColumnInfo col = cols[i]; buf.Append(join); buf.Append(constraint.RefColumns[i]); buf.Append(" = :"); buf.Append(col.Name); buf.Append("::"); buf.Append(Table.Columns[col.Name].DataType); //prms.Add(dataSet.CreateParameterByFieldInfo(col, Row[col.Index], false)); join = " and "; } using (IDbConnection conn = dataSet.NewConnection(true)) { using (IDbCommand cmd = dataSet.GetSqlCommand(refTbl.GetSelectSQL(string.Empty, buf.ToString(), string.Empty, null, HiddenLevel.Visible), null, conn)) { foreach (ColumnInfo col in cols) { dataSet.ApplyParameterByFieldInfo(cmd.Parameters, col, Row[col.Index], false); } IDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { return; } list.Add(new ColumnValue() { IsHeader = true, ColumnName = refTbl.Name, Value = null }); for (int i = 0, n = reader.FieldCount; i < n; i++) { object v = null; try { v = reader.GetValue(i); } catch (OverflowException) { v = "(OVERFLOW)"; } list.Add(new ColumnValue() { IsHeader = false, ColumnName = reader.GetName(i), Value = v }); } } } }
public void Fetch(bool force) { dataGridResult.CommitEdit(); _fetched = false; if (!force && DataGridControllerResult.IsModified) { MessageBoxResult ret = MessageBox.Show("変更が保存されていません。保存しますか?", "確認", MessageBoxButton.YesNoCancel, MessageBoxImage.Exclamation, MessageBoxResult.Yes); switch (ret) { case MessageBoxResult.Yes: DataGridControllerResult.Save(); break; case MessageBoxResult.No: break; case MessageBoxResult.Cancel: return; } } if (Target == null) { return; } Db2SourceContext ctx = Target.Context; if (ctx == null) { return; } int?limit = null; if (IsChecked(checkBoxLimitRow) && !string.IsNullOrEmpty(textBoxLimitRow.Text)) { int l; if (!int.TryParse(textBoxLimitRow.Text, out l)) { MessageBox.Show("件数が数字ではありません", "エラー", MessageBoxButton.OK, MessageBoxImage.Error); textBoxLimitRow.Focus(); } limit = l; } string orderby = sortFields.GetOrderBySql(string.Empty); int offset; string sql = Target.GetSelectSQL(null, textBoxCondition.Text, orderby, limit, VisibleLevel, out offset); try { using (IDbConnection conn = ctx.NewConnection(true)) { using (IDbCommand cmd = ctx.GetSqlCommand(sql, null, conn)) { UpdateDataGridResult(cmd); } } } catch (Exception t) { ctx.OnLog(ctx.GetExceptionMessage(t), LogStatus.Error, sql); Db2SrcDataSetController.ShowErrorPosition(t, textBoxCondition, ctx, offset); } finally { UpdateTextBlockWarningLimit(); GC.Collect(0); } _fetched = true; }
private async Task ExportConfigAsync(Db2SourceContext dataSet, List <string> schemas, List <string> excludedSchemas, string configFile, string ruleFile, Encoding encoding) { LoadFromRuleFile(ruleFile, encoding); await dataSet.LoadSchemaAsync(); Dictionary <string, bool> activeSchemas = GetActiveSchemaDict(dataSet, schemas, excludedSchemas); StringBuilder buf = new StringBuilder(); IDbConnection conn = dataSet.NewConnection(true); IDbCommand cmdTbl = dataSet.GetSqlCommand(_tableSql, null, conn); IDbCommand cmdCol = dataSet.GetSqlCommand(Properties.Resources.ColumnSQL, null, conn); IDbCommand cmdKey = dataSet.GetSqlCommand(Properties.Resources.KeyConsSQL, null, conn); try { IDbDataParameter pC = cmdCol.Parameters[0] as IDbDataParameter; //pC.ParameterName = "oid"; pC.DbType = DbType.Int64; //cmdCol.Parameters.Add(pC); IDbDataParameter pK = cmdKey.Parameters[0] as IDbDataParameter; //pK.ParameterName = "oid"; pK.DbType = DbType.Int64; //cmdKey.Parameters.Add(pK); List <TableInfo> l = new List <TableInfo>(); using (IDataReader reader = cmdTbl.ExecuteReader()) { while (reader.Read()) { uint id = (uint)reader.GetValue(0); string sch = reader.GetString(1); string tbl = reader.GetString(2); if (!activeSchemas.ContainsKey(sch.ToLower())) { continue; } l.Add(new TableInfo() { Oid = id, Schema = sch, TableName = tbl }); } } for (int i = 0; i < l.Count; i++) { TableInfo info = l[i]; SortedList <int, string> lCol = new SortedList <int, string>(); List <string> ignoreCols = new List <string>(); List <ValueInfo> fixedCols = new List <ValueInfo>(); pC.Value = (Int64)info.Oid; using (IDataReader readerC = cmdCol.ExecuteReader()) { while (readerC.Read()) { string col = readerC.GetString(0); string[] keys = new string[] { (info.Schema + "." + info.TableName + "." + col).ToLower(), (info.TableName + "." + col).ToLower(), col.ToLower() }; int idx = readerC.GetInt32(1); lCol.Add(idx, col); if (_ignoreFields.ContainsKey(keys[0]) || _ignoreFields.ContainsKey(keys[1]) || _ignoreFields.ContainsKey(keys[2])) { ignoreCols.Add(col); } foreach (string k in keys) { if (_fixedFields.ContainsKey(k)) { fixedCols.Add(new ValueInfo() { FieldName = col, Value = _fixedFields[k] }); break; } } } } info.IgnoreFields = ignoreCols.ToArray(); List <string> keyCols = new List <string>(); pK.Value = (Int64)info.Oid; using (IDataReader readerK = cmdKey.ExecuteReader()) { if (readerK.Read()) { Array a = readerK.GetValue(1) as Array; foreach (object o in a) { int p = Convert.ToInt32(o); keyCols.Add(lCol[p]); } } } info.OrderBy = keyCols.ToArray(); buf.AppendLine(string.Format("[{0}.{1}]", info.Schema, info.TableName)); buf.Append("ORDER="); buf.AppendLine(ToCsv(info.OrderBy)); buf.Append("WHERE="); buf.AppendLine(); buf.Append("IGNORE="); buf.AppendLine(ToCsv(info.IgnoreFields)); buf.AppendLine(string.Format("[{0}.{1}\\FIXED]", info.Schema, info.TableName)); List <string> fixVals = new List <string>(); foreach (ValueInfo f in fixedCols) { buf.Append(f.FieldName); buf.Append('='); buf.AppendLine(f.Value); } buf.AppendLine(); } } finally { cmdTbl.Dispose(); cmdCol.Dispose(); cmdKey.Dispose(); conn.Dispose(); } File.WriteAllText(_configFileName, buf.ToString(), encoding); }
private void UpdateDataGridResult(SQLParts sqls) { Db2SourceContext ctx = CurrentDataSet; using (IDbConnection conn = ctx.NewConnection(true)) { bool modified; Parameters = ParameterStore.GetParameterStores(sqls.ParameterNames, Parameters, out modified); if (modified) { return; } foreach (ParameterStore p in Parameters) { if (p.IsError) { dataGridParameters.Focus(); dataGridParameters.SelectedItem = p; MessageBox.Show(string.Format("パラメータ{0}の値が不正です", p.ParameterName), "エラー", MessageBoxButton.OK, MessageBoxImage.Error); return; } } foreach (SQLPart sql in sqls.Items) { IDbCommand cmd = ctx.GetSqlCommand(sql.SQL, Command_Log, conn); ParameterStoreCollection stores = ParameterStore.GetParameterStores(cmd, Parameters, out modified); DateTime start = DateTime.Now; try { using (IDataReader reader = cmd.ExecuteReader()) { DataGridControllerResult.Load(reader); if (0 <= reader.RecordsAffected) { AddLog(string.Format("{0}行反映しました。", reader.RecordsAffected), null, null, LogStatus.Normal, true); } else if (0 < reader.FieldCount) { tabControlResult.SelectedItem = tabItemDataGrid; } } AddToHistory(sql, stores); } catch (Exception t) { Tuple <int, int> errPos = CurrentDataSet.GetErrorPosition(t, sql.SQL, 0); AddLog(CurrentDataSet.GetExceptionMessage(t), sql.SQL, stores, LogStatus.Error, true, errPos); Db2SrcDataSetController.ShowErrorPosition(t, textBoxSql, CurrentDataSet, sql.Offset); return; } finally { ParameterStore.GetParameterStores(cmd, stores, out modified); UpdateDataGridParameters(); DateTime end = DateTime.Now; TimeSpan time = end - start; string s = string.Format("{0}:{1:00}:{2:00}.{3:000}", (int)time.TotalHours, time.Minutes, time.Seconds, time.Milliseconds); AddLog(string.Format("実行しました (所要時間 {0})", s), cmd.CommandText, stores, LogStatus.Aux, false); textBlockGridResult.Text = string.Format("{0}件見つかりました。 所要時間 {1}", DataGridControllerResult.Rows.Count, s); } } } }