Ejemplo n.º 1
0
 private PDAView(string name)
 {
     Name = name;
     QuerySelectPDA query = new QuerySelectPDA();
     query.Select("select * from " + name);
     rows = query.GetRows();
 }
Ejemplo n.º 2
0
 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");
 }
Ejemplo n.º 3
0
 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 "";
 }
Ejemplo n.º 4
0
        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));
                }

            }
        }
Ejemplo n.º 5
0
 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);
         }
     }
 }
Ejemplo n.º 6
0
 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;
 }
Ejemplo n.º 7
0
        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;
             }
        }
Ejemplo n.º 8
0
 // --
 /// <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;
 }
Ejemplo n.º 9
0
 // ---
 /// <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;
 }
Ejemplo n.º 10
0
        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);

                }
            }
        }
Ejemplo n.º 11
0
 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);
     }
 }
Ejemplo n.º 12
0
        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"));
            }
        }
Ejemplo n.º 13
0
 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);
     }
 }
Ejemplo n.º 14
0
        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);
            }
        }
Ejemplo n.º 15
0
        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);
            }
        }
Ejemplo n.º 16
0
        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"));
            }
        }
Ejemplo n.º 17
0
        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")));

            }
        }