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