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")); } } }
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); } }
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 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); } } }
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 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 PDAView(string name) { Name = name; QuerySelectPDA query = new QuerySelectPDA(); query.Select("select * from " + name); rows = query.GetRows(); }
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; } } }
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); }
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); } } } }
// -- /// <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); }
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"))); } }
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 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); } }
public List <LogRecordDetail> detailRecords;// = new List<LogRecordDetail>(); // --- /// <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); }
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 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); } }
public void SetUp() { repository = new MockRepository(); query = repository.CreateMock <QuerySelectPDA>(); }