private static void ReadStruct()
{
QuerySelectPDA query = new QuerySelectPDA();
query.Select(
"select TABLE_NAME, COLUMN_NAME, COLUMN_FLAGS, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME");
List<DataRows> rows = query.GetRows();
PDATable.Clear();
foreach (DataRows row in rows)
{
PDATable.Add(row);
}
// get constrains
query = new QuerySelectPDA();
query.Select(
"select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS order by TABLE_NAME");
rows = query.GetRows();
PDAConstr.Clear();
foreach (DataRows row in rows)
{
PDAConstr.Add(row);
}
// отображения
PDAView.Clear();
PDAView.Add("INFORMATION_SCHEMA.COLUMNS");
PDAView.Add("INFORMATION_SCHEMA.INDEXES");
PDAView.Add("INFORMATION_SCHEMA.KEY_COLUMN_USAGE");
PDAView.Add("INFORMATION_SCHEMA.PROVIDER_TYPES");
PDAView.Add("INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS");
PDAView.Add("INFORMATION_SCHEMA.TABLE_CONSTRAINTS");
PDAView.Add("INFORMATION_SCHEMA.TABLES");
}
private PDAView(string name)
{
Name = name;
QuerySelectPDA query = new QuerySelectPDA();
query.Select("select * from " + name);
rows = query.GetRows();
}
private void button10_Click(object sender, EventArgs e)
{
QuerySelectPDA q=new QuerySelectPDA();
QueryExecPDA qe=new QueryExecPDA();
string seltyp =
"select DATA_TYPE from information_schema.columns where TABLE_NAME='{0}' AND COLUMN_NAME='{1}'";
for (int i = 0; i < PDATables.Items.Count; i++)
{
PDATables.SelectedIndex = i;
for (int j = 0; j < PDAFields.Items.Count; j++)
{
PDAFields.SelectedIndex = j;
q.Select(string.Format(seltyp,PDATables.SelectedItem,PDAFields.SelectedItem));
List<DataRows> rowtype = q.GetRows();
string type = rowtype[0].FieldByName("DATA_TYPE");
string instype =
"update TransferFields set DataType={0} where fieldName='{1}' and idTransferTable={2}";
string value="";
switch(type)
{
case "numeric" : value = "1";
break;
case "nvarchar": value = "2";
break;
case "datetime": value = "3";
break;
}
qe.Execute(String.Format(instype, value, PDAFields.SelectedItem, textBox1.Text));
}
}
}
public static string GetParam(ParamName paramName)
{
QuerySelectPDA q=new QuerySelectPDA();
string sql = "select {0} from MainParams";
string name="*";
switch(paramName)
{
case ParamName.idGu: name="idGU";
break;
case ParamName.expDate:name="expDate";
break;
case ParamName.impDate: name="impDate";
break;
case ParamName.impState: name="impState";
break;
case ParamName.expState: name="expState";
break;
case ParamName.isLight: name="isLight";
break;
}
q.Select(string.Format(sql, name));
List<DataRows> rows = q.GetRows();
if (rows.Count > 0)
return rows[0].FieldByNameDef(name, "");
else
return "";
}
private void button1_Click(object sender, EventArgs e)
{
// make select
listView2.Items.Clear();
listView2.Columns.Clear();
if((textBox1.Text.TrimStart(' ').Length>6)&&( textBox1.Text.TrimStart(' ').Substring(0, 6).ToUpper() == "SELECT"))
{
QuerySelectPDA query = new QuerySelectPDA();
if(!query.Select(textBox1.Text))
{
listView2.Columns.Add("Результат", 300);
listView2.Items.Add(query.ErrorMsg);
}
else
{
List<DataRows> rows = query.GetRows();
// make colums
if(rows.Count>0)
{
foreach (DataField field in rows[0].fields)
{
listView2.Columns.Add(field.Field, 100);
}
}
// rows
foreach (DataRows row in rows)
{
bool first = true;
ListViewItem item = new ListViewItem();
foreach (DataField field in row.fields)
{
if(first)
{
first = false;
item = new ListViewItem(field.Value);
}
else
{
item.SubItems.Add(field.Value);
}
}
listView2.Items.Add(item);
}
}
}
else
{
// просто скрипт
listView2.Columns.Add("Результат", 300);
QueryExecPDA query = new QueryExecPDA();
if(query.Execute(textBox1.Text))
{
listView2.Items.Add("Выполнен");
}
else
{
listView2.Items.Add(query.ErrorMsg);
}
}
}
public void Exec()
{
count = 1;
foreach (TableInfo info in lst)
{
string ins = "";
if (Running)
{
QuerySelectPDA q = new QuerySelectPDA();
QueryExecOracle qu = new QueryExecOracle();
if (!q.Select("select * from " + info.tableName))
{
Loging.Loging.WriteLog("Error:select * from " + info.tableName, true, true);
}
else
{
Loging.Loging.WriteLog("OK:select * from " + info.tableName, false, false);
List<DataRows> dr = q.GetRows();
foreach (DataRows rows in dr)
{
string temp = "";
ins = "insert into " + info.tableName + "(";
foreach (FieldInfo field in info.fields)
{
ins += field.fieldName + ", ";
temp += "'" + rows.FieldByName(field.fieldName).Replace("'","''") + "', ";
}
ins = ins.Remove(ins.LastIndexOf(','), 1);
temp = temp.Remove(temp.LastIndexOf(','), 1);
ins += ") values (" + temp + ")";
if (!qu.Execute(ins))
{
Loging.Loging.WriteLog("Error:" + ins, true, true);
}
else Loging.Loging.WriteLog("OK:" + ins, false, false);
}
}
Coordinator.ExecuteDelegateArgs args = new Coordinator.ExecuteDelegateArgs();
args.Maximum = lst.Count;//передавать в args кол-во таблиц и номер текущей (для прогресс бара)
args.Pos = count;
args.runningAction = this;
args.Name = Name();
OnExecute(this, args);
count++;
}
else break;
}
}
// --
/// <summary>
/// Сохраняет запись в таблицу BrLogDet
/// </summary>
/// <param name="id">Идентификатор FK на BrLog</param>
public static List<LogRecordDetail> Load(string id)
{
QuerySelectPDA q=new QuerySelectPDA();
List<LogRecordDetail> list=new List<LogRecordDetail>();
q.Select("select * from LogRecordDetail where idlog=" + id);
List<DataRows> rows = q.GetRows();
foreach (DataRows row in rows)
{
LogRecordDetail lrd=new LogRecordDetail();
lrd.fieldName = row.FieldByName("fieldName");
lrd.fieldDescr = row.FieldByName("fieldDescr");
lrd.valueOld = row.FieldByName("valueOld");
lrd.valueNew = row.FieldByName("valueNew");
list.Add(lrd);
}
return list;
}
public static List<object> AddLog(string id, string Order, bool Dummy)
{
List<object> result = new List<object>();
QuerySelectPDA q = new QuerySelectPDA();
string BrLog = "Select a.* from BrLog a where a.idBr=" + id + " Order by " + Order;
string BrLogDet = "Select * from BrLogDet where idlog=" + id + " Order by IdLogDet";
if (Dummy)
{
q.Select(BrLog);
List<DataRows> lst = q.GetRows();
foreach (DataRows rows in lst)
{
BrLog log = new BrLog();
log.LogType = rows.FieldByName("LogType");
log.TableName = rows.FieldByName("TableName");
log.TableDescr = rows.FieldByName("TableDescr");
log.RunDate = rows.FieldByName("Rundate");
log.IdLog = rows.FieldByName("idLog");
//log.ValueOld = rows.FieldByName("ValueOld");
//log.ValueNew = rows.FieldByName("ValueNew");
//
//log.FieldName = rows.FieldByName("FieldName");
//log.FieldDescr = rows.FieldByName("FieldDescr");
//if (log.ValueOld.TrimEnd(',', '0') != log.ValueNew.TrimEnd(',', '0'))
result.Add(log);
}
}
else
{
q.Select(BrLogDet);
List<DataRows> lst = q.GetRows();
foreach (DataRows rows in lst)
{
BrLogDet log = new BrLogDet();
log.FieldName = rows.FieldByName("FieldName");
log.FieldDescr = rows.FieldByName("FieldDescr");
log.ValueOld = rows.FieldByName("ValueOld");
log.ValueNew = rows.FieldByName("ValueNew");
if (log.ValueOld.TrimEnd(',', '0') != log.ValueNew.TrimEnd(',', '0'))
{ result.Add(log); }
}
}
return result;
}
// ---
/// <summary>
/// Получает данные из базу.
/// </summary>
public List<LogRecord> Load()
{
List<LogRecord> list=new List<LogRecord>();
QuerySelectPDA q = new QuerySelectPDA();
q.Select("select * from Brlog");
List<DataRows> rows = q.GetRows();
foreach (DataRows row in rows)
{
LogRecord lr=new LogRecord();
lr.idBr = Int32.Parse(row.FieldByName("idbr"));
lr.sqlText = row.FieldByName("sqltext");
lr.runDate = row.FieldByName("rundate");
lr.logType = (LogType)Enum.Parse(typeof(LogType),row.FieldByName("logtype"));
lr.tableName = row.FieldByName("tablename");
lr.tableDescr = row.FieldByName("tableDescr");
string idlog = row.FieldByName("idlog");
lr.detailRecords = LogRecordDetail.Load(idlog);
list.Add(lr);
}
return list;
}
public override void Run()
{
/// Алгоритм
/// На основании данных таблицы КПК DisTriggers Включать/Выключать (зависит от On)
/// тригерра (учитвать порядок - ord)
/// - не забыть лог
/// - не забыть Running
if(Running)
{
string tr = "select * from DisTriggers order by ord";
string tri="Alter trigger {0} ";
if(On)
tri+= "Enable";
else tri += "Disable";
QuerySelectPDA q=new QuerySelectPDA();
QueryExecOracle qu = new QueryExecOracle();
if(!q.Select(tr))
{
Loging.Loging.WriteLog("Error: " + tr, true, true);
}
else
{
Loging.Loging.WriteLog("OK: " + tr, false, false);
}
List<DataRows> dr;
dr = q.GetRows();
foreach (DataRows rows in dr)
{
string TrName = rows.FieldByName("triggerName");
if(!qu.Execute(String.Format(tri,TrName)))
Loging.Loging.WriteLog("Error: " + String.Format(tri, TrName), true, true);
else
Loging.Loging.WriteLog("OK: " + String.Format(tri, TrName), false, false);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string SQL = "select tableName from Transfer2PDA";
QuerySelectPDA query=new QuerySelectPDA();
query.Select(SQL);
List<DataRows> rows = query.GetRows();
PDATables.Items.Clear();
foreach (DataRows row in rows)
{
PDATables.Items.Add(row.FieldByName("tableName"));
}
SQL = "select distinct Table_Name from user_tables";
QuerySelectOracle Oraquery=new QuerySelectOracle();
Oraquery.Select(SQL);
rows = Oraquery.GetRows();
OraTables.Items.Clear();
foreach (DataRows row in rows)
{
if (!PDATables.Items.Contains(row.FieldByName("Table_Name")))
OraTables.Items.Add(row.FieldByName("Table_Name"));
}
}
public void MakeSQLView(ref ListView sqlView)
{
sqlView.Items.Clear();
sqlView.Columns.Clear();
foreach (PDAField field in fields)
{
sqlView.Columns.Add(field.Name);
}
QuerySelectPDA query = new QuerySelectPDA();
query.Select("select * from " + Name);
List<DataRows> rows = query.GetRows();
foreach (DataRows row in rows)
{
ListViewItem item = null;
foreach (PDAField field in fields)
{
if (item == null)
item = new ListViewItem(row.FieldByNameDef(field.Name, ""));
else
item.SubItems.Add(row.FieldByNameDef(field.Name, ""));
}
sqlView.Items.Add(item);
}
}
private void button2_Click(object sender, EventArgs e)
{
if (OraTables.SelectedItem != null)
{
string idquery = "select MAX(idTransferTable) as ID from Transfer2PDA";
QuerySelectPDA query = new QuerySelectPDA();
query.Select(idquery);
List<DataRows> rows = query.GetRows();
int id;
try
{
id = Convert.ToInt32(rows[0].FieldByName("ID")) + 1;
}
catch(FormatException)
{
id = 1;
}
idquery = "select MAX(idQrySelect) as ID from QrySelect";
query.Select(idquery);
rows = query.GetRows();
int idSelectBM;
try
{
idSelectBM = Convert.ToInt32(rows[0].FieldByName("ID")) + 1;
}
catch(FormatException)
{
idSelectBM = 1;
}
int idSelectPDA = idSelectBM + 1;
idquery = "select MAX(idQryDelete) as ID from QryDelete";
query.Select(idquery);
rows = query.GetRows();
int idDelete;
try
{
idDelete = Convert.ToInt32(rows[0].FieldByName("ID")) + 1;
}
catch(FormatException)
{
idDelete = 1;
}
idquery = "select MAX(idQryClear) as ID from QryClear";
query.Select(idquery);
rows = query.GetRows();
int idClear;
try
{
idClear = Convert.ToInt32(rows[0].FieldByName("ID")) + 1;
}
catch(FormatException)
{
idClear = 1;
}
idquery = "select MAX(idQryInsert) as ID from QryInsert";
query.Select(idquery);
rows = query.GetRows();
int idInsert;
try
{
idInsert = Convert.ToInt32(rows[0].FieldByName("ID")) + 1;
}
catch(FormatException)
{
idInsert = 1;
}
QueryExecPDA qry = new QueryExecPDA();
string SQL = "insert into Transfer2PDA values ({0},'{1}',{2},{3},{4},{5},{6},null,null,null,null)";
qry.Execute(String.Format(SQL, id,OraTables.SelectedItem,idSelectBM,idSelectPDA,idDelete,idClear,idInsert));
SQL = "insert into QrySelect values ({0},null)";
qry.Execute(String.Format(SQL, idSelectBM));
SQL = "insert into QrySelect values ({0},null)";
qry.Execute(String.Format(SQL, idSelectPDA));
SQL = "insert into QryDelete values ({0},null)";
qry.Execute(String.Format(SQL, idDelete));
SQL = "insert into QryClear values ({0},null)";
qry.Execute(String.Format(SQL, idClear));
SQL = "insert into QryInsert values ({0},null)";
qry.Execute(String.Format(SQL, idInsert));
PDAFields.Items.Clear();
OraFields.Items.Clear();
button1_Click(sender, e);
}
}
private void PDATables_SelectedIndexChanged(object sender, EventArgs e)
{
string SQL = "select * from Transfer2PDA where tableName='{0}'";
QuerySelectPDA query = new QuerySelectPDA();
query.Select(String.Format(SQL,PDATables.SelectedItem));
List<DataRows> rows = query.GetRows();
if (rows.Count > 0)
{
if (rows[0].FieldByName("isLight") == "1") checkBox1.Checked = true;
else checkBox1.Checked = false;
if (rows[0].FieldByName("needExport") == "1") checkBox2.Checked = true;
else checkBox2.Checked = false;
textBox1.Text = rows[0].FieldByName("idTransferTable");
SQL = "select fieldname from TransferFields where idTransferTable={0}";
query.Select(String.Format(SQL, textBox1.Text));
List<DataRows> rows1 = query.GetRows();
PDAFields.Items.Clear();
foreach (DataRows row in rows1)
{
PDAFields.Items.Add(row.FieldByName("fieldname"));
}
/// запросы
///
SQL = "select text from QrySelect where idQrySelect={0}";
query.Select(String.Format(SQL, rows[0].FieldByName("idQrySelectBM")));
rows1 = query.GetRows();
SelectText.Text = rows1[0].FieldByName("text");
SQL = "select text from QrySelect where idQrySelect={0}";
query.Select(String.Format(SQL, rows[0].FieldByName("idQrySelectPDA")));
rows1 = query.GetRows();
SelectPDAText.Text = rows1[0].FieldByName("text");
SQL = "select text from QryDelete where idQryDelete={0}";
query.Select(String.Format(SQL, rows[0].FieldByName("idQryDelete")));
rows1 = query.GetRows();
DeleteText.Text = rows1[0].FieldByName("text");
SQL = "select text from QryClear where idQryClear={0}";
query.Select(String.Format(SQL, rows[0].FieldByName("idQryClear")));
rows1 = query.GetRows();
ClearText.Text = rows1[0].FieldByName("text");
SQL = "select text from QryInsert where idQryInsert={0}";
query.Select(String.Format(SQL, rows[0].FieldByName("idQryInsert")));
rows1 = query.GetRows();
InsertText.Text = rows1[0].FieldByName("text");
}
else
{
checkBox2.Checked = false;
checkBox1.Checked = false;
PDAFields.Items.Clear();
textBox1.Text = "";
}
SQL = "select Column_Name from user_tab_columns where Table_Name='{0}'";
QuerySelectOracle OraQuery=new QuerySelectOracle();
OraQuery.Select(String.Format(SQL, PDATables.SelectedItem));
rows = OraQuery.GetRows();
OraFields.Items.Clear();
foreach (DataRows row in rows)
{
if (!PDAFields.Items.Contains(row.FieldByName("Column_Name")))
OraFields.Items.Add(row.FieldByName("Column_Name"));
}
}
private void button5_Click(object sender, EventArgs e)
{
if (OraFields.SelectedItem != null)
{
string idquery = "select MAX(idTransferField) as ID from TransferFields";
QuerySelectPDA query = new QuerySelectPDA();
query.Select(idquery);
List<DataRows> rows = query.GetRows();
int id;
try
{
id = Convert.ToInt32(rows[0].FieldByName("ID")) + 1;
}
catch(FormatException)
{
id = 1;
}
QueryExecPDA qry = new QueryExecPDA();
string SQL = "insert into TransferFields values ({0},{1},'{2}')";
qry.Execute(String.Format(SQL, id, textBox1.Text,OraFields.SelectedItem));
PDATables_SelectedIndexChanged(sender,e);
}
}
private void button3_Click(object sender, EventArgs e)
{
if (PDATables.SelectedItem != null)
{
string SQL = "select * from Transfer2PDA where tableName='{0}'";
QuerySelectPDA query = new QuerySelectPDA();
query.Select(String.Format(SQL, PDATables.SelectedItem));
List<DataRows> rows = query.GetRows();
QueryExecPDA qry = new QueryExecPDA();
SQL = "delete from QrySelect where idQrySelect={1}";
qry.Execute(string.Format(SQL, SelectText.Text, rows[0].FieldByName("idQrySelectBM")));
SQL = "delete from QrySelect where idQrySelect={1}";
qry.Execute(string.Format(SQL, SelectPDAText.Text, rows[0].FieldByName("idQrySelectPDA")));
SQL = "delete from QryDelete where idQryDelete={1}";
qry.Execute(string.Format(SQL, DeleteText.Text, rows[0].FieldByName("idQryDelete")));
SQL = "delete from QryClear where idQryClear={1}";
qry.Execute(string.Format(SQL, ClearText.Text, rows[0].FieldByName("idQryClear")));
SQL = "delete from QryInsert where idQryinsert={1}";
qry.Execute(string.Format(SQL, ClearText.Text, rows[0].FieldByName("idQryInsert")));
SQL = "delete from Transfer2PDA where idTransferTable={0}";
qry.Execute(String.Format(SQL, textBox1.Text));
SQL = "delete from TransferFields where idTransferTable={0}";
qry.Execute(String.Format(SQL, textBox1.Text));
button1_Click(sender,e);
}
}
public void SetUp()
{
repository = new MockRepository();
query = repository.CreateMock<QuerySelectPDA>();
}
private void button6_Click(object sender, EventArgs e)
{
if(PDATables.SelectedItem!=null)
{
int isl=0, nex=0;
string SQL = "update Transfer2PDA set isLight={0}, needExport={1} where idTransferTable={2}";
if (checkBox1.Checked) isl = 1;
if (checkBox2.Checked) nex = 1;
QueryExecPDA qry = new QueryExecPDA();
qry.Execute(String.Format(SQL, isl, nex, textBox1.Text));
SQL = "select * from Transfer2PDA where tableName='{0}'";
QuerySelectPDA query = new QuerySelectPDA();
query.Select(String.Format(SQL, PDATables.SelectedItem));
List<DataRows> rows = query.GetRows();
SQL = "update QrySelect set text='{0}' where idQrySelect={1}";
qry.Execute(string.Format(SQL, SelectText.Text, rows[0].FieldByName("idQrySelectBM")));
SQL = "update QrySelect set text='{0}' where idQrySelect={1}";
qry.Execute(string.Format(SQL, SelectPDAText.Text, rows[0].FieldByName("idQrySelectPDA")));
SQL = "update QryDelete set text='{0}' where idQryDelete={1}";
qry.Execute(string.Format(SQL, DeleteText.Text, rows[0].FieldByName("idQryDelete")));
SQL = "update QryClear set text='{0}' where idQryClear={1}";
qry.Execute(string.Format(SQL, ClearText.Text, rows[0].FieldByName("idQryClear")));
SQL = "update QryInsert set text='{0}' where idQryInsert={1}";
qry.Execute(string.Format(SQL, InsertText.Text, rows[0].FieldByName("idQryInsert")));
}
}
