예제 #1
0
        //public static string StaticTicketDate1;

       // public MainViewModel bugForm = new MainViewModel();

        public void opens()
        {
            try
            {
                
                //Brows b = new Brows();
                //MessageBox.Show("Button works"+ sheet);
                OleDbConnection con = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + MainViewModel.staticFilePath + "; Extended Properties =\"Excel 8.0;  HDR=Yes; IMEX=1;\"");
                //string from = textBox_from.Text.ToString();

                //string to = textBox_to.Text.ToString();

                
                OleDbCommand oconn = new OleDbCommand
                    (
                    "SELECT * From [" + "sheet1" + "$]", con

                    );
                //DataSet ds = new DataSet();
                dataTable = new DataTable();
                OleDbDataAdapter adapter = new OleDbDataAdapter(oconn);
                oconn.Connection = con;
                int i;
                int j;
                adapter.Fill(dataTable);
                //MessageBox.Show(dataTable.Rows[0][0].ToString());
                //ds.Tables.Add(dataTable);


                ef = new ObservableCollection<Ticket>();


                for (i = 0; i <= dataTable.Rows.Count - 1; i++)
                {
                    Ticket r = new Ticket();
                    r.ticketNo = dataTable.Rows[i][0].ToString();
                    r.ticketDate = dataTable.Rows[i][1].ToString();
                    r.ticketReference = dataTable.Rows[i][2].ToString();
                    r.ticketCategory = dataTable.Rows[i][3].ToString();
                    r.ticketStatus = dataTable.Rows[i][4].ToString();
                    r.tickectReporter = dataTable.Rows[i][5].ToString();
                    r.ticketDescription = dataTable.Rows[i][6].ToString();
                    r.ticketAttachment = dataTable.Rows[i][7].ToString();
                    //MessageBox.Show("Number" + MainWindow.bugForm.Number);
                     if (MainWindow.bugForm.Number.Equals(r.ticketNo))
                    {
                        MainWindow.bugForm.Number = r.ticketNo;
                        MainWindow.bugForm.Date = r.ticketDate;
                        MainWindow.bugForm.Reference = r.ticketReference;
                        MainWindow.bugForm.Category = r.ticketCategory;
                        MainWindow.bugForm.Status = r.ticketStatus;
                        MainWindow.bugForm.Originator = r.tickectReporter;
                        MainWindow.bugForm.Description = r.ticketDescription;
                        MainWindow.bugForm.Attachment = r.ticketAttachment;
                    }
                    
                    ef.Add(r);
                }
                con.Close();
            }
            catch (Exception ButtonException)
            {
                MessageBox.Show(" an Exception happend, " + ButtonException);
                throw new MyException("Open Bug file Exception");
            }
            

        }
예제 #2
0
        public void AddReport()
        {
            try {
                MessageBox.Show("Ticket Saved??");
                OleDbConnection con1 = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + MainViewModel.staticFilePath + "; Extended Properties =\"Excel 8.0; HDR=Yes; typeGuessRows=0; ImportMixedType=test; IMEX=1;\"");
                connStr2 = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + MainViewModel.staticFilePath + "; Extended Properties =\"Excel 12.0 Macro;  HDR=Yes; typeGuessRows=1; ImportMixedType=text; IMEX=3; \"";
                OleDbCommand oconn1 = new OleDbCommand
                    (
                    "SELECT * From [" + "sheet1" + "$]", con1

                    );
                dataTable1 = new DataTable();
                OleDbDataAdapter adapter = new OleDbDataAdapter(oconn1);
                OleDbConnection conn2 = new OleDbConnection(connStr2);
                oconn1.Connection = con1;
                int i;
                int j;
                adapter.Fill(dataTable1);
                //MessageBox.Show(dataTable.Rows[0][0].ToString());
                //ds.Tables.Add(dataTable);


                ef1 = new ObservableCollection<Ticket>();


                for (i = 0; i <= dataTable1.Rows.Count - 1; i++)
                {
                    r = new Ticket();
                    r.ticketNo = dataTable1.Rows[i][0].ToString();
                    r.ticketDate = dataTable1.Rows[i][1].ToString();
                    r.ticketReference = dataTable1.Rows[i][2].ToString();
                    r.ticketCategory = dataTable1.Rows[i][3].ToString();
                    r.ticketStatus = dataTable1.Rows[i][4].ToString();
                    r.tickectReporter = dataTable1.Rows[i][5].ToString();
                    r.ticketDescription = dataTable1.Rows[i][6].ToString();
                    r.ticketAttachment = dataTable1.Rows[i][7].ToString();
                    //MessageBox.Show("Number" + MainWindow.bugForm.Number);

                    if (MainWindow.bugForm.Number.Equals(r.ticketNo))
                    {
                        switchOld = false;
                        switchFind = true;
                        r.ticketNo = MainWindow.bugForm.Number.ToString();
                    r.ticketDate = MainWindow.bugForm.Date.ToString();
                    r.ticketReference = MainWindow.bugForm.Reference.ToString();
                    r.ticketCategory = MainWindow.bugForm.Category.ToString();
                    r.ticketStatus = MainWindow.bugForm.Status.ToString();
                    r.tickectReporter = MainWindow.bugForm.Originator.ToString();
                    r.ticketDescription = MainWindow.bugForm.Description.ToString();
                    r.ticketAttachment = MainWindow.bugForm.Attachment.ToString();

                        one = r.ticketNo;
                        sql = @"Update [Sheet1$] set [Date Created] = '"+r.ticketDate+"', [ApplicationName] = '"+r.ticketReference+"', [Category]='"+r.ticketCategory+"', [Status]='"+r.ticketStatus+"', [Originator]='"+r.tickectReporter+"', [Description]='"+r.ticketDescription+"', [AttachedFile]='"+r.ticketAttachment+"' WHERE [ReportNo] = "+r.ticketNo+";";
                        OleDbCommand cmd2 = new OleDbCommand(sql, conn2);
                        conn2.Open();
                        //OleDbCommand cmd2 = new OleDbCommand(@"Update [Sheet1$] set [Date Created] = "+r.ticketDate+" where [ReportNo] = "+r.ticketNo +", conn2");
                        
                        cmd2.ExecuteNonQuery();
                        //oconn1.CommandText = sql;
                        //oconn1.ExecuteNonQuery();
                        conn2.Close();
                        
                    }

                    ef1.Add(r);

                }
                if (switchFind != true)
                {
                    MessageBox.Show("Check point new");
                    r = new Ticket();
                    r.ticketNo = MainWindow.bugForm.Number.ToString();
                    r.ticketDate = MainWindow.bugForm.Date.ToString();
                    r.ticketReference = MainWindow.bugForm.Reference.ToString();
                    r.ticketCategory = MainWindow.bugForm.Category.ToString();
                    r.ticketStatus = MainWindow.bugForm.Status.ToString();
                    r.tickectReporter = MainWindow.bugForm.Originator.ToString();
                    r.ticketDescription = MainWindow.bugForm.Description.ToString();
                    r.ticketAttachment = MainWindow.bugForm.Attachment.ToString();
                    ef1.Add(r);
                    //switchFind = false;
                    //con1.Open();
                    //sql = "INSERT INTO [Sheet1$](ReportNo,Date,ApplicationName,Category,Status,Originator,Description,Attachedfile)VALUES(r.ticketNo,r.ticketDate,r.ticketReference,r.ticketCategory,r.ticketStatus,r.tickectReporter,r.ticketDescription,r.ticketAttachment)";
                    //sql = "INSERT INTO [Sheet2$](1,2)VALUES('2','3');";
                    
                    one = r.ticketNo;
                    sql = "INSERT INTO [Sheet1$]([ReportNo],[Date Created],[ApplicationName],[Category],[Status],[Originator],[Description],[Attachedfile])VALUES('"+r.ticketNo+"','"+ r.ticketDate+"','"+ r.ticketReference+"','"+ r.ticketCategory+"','"+r.ticketStatus+"','"+ r.tickectReporter+"','"+ r.ticketDescription+"','"+ r.ticketAttachment+"'); ";
                    //sql = "INSERT INTO TABLE[Sheet1$](ReportNo)VALUES('3e3e33e')";
                    OleDbCommand cmd2 = new OleDbCommand(sql, conn2);
                    conn2.Open();
                    cmd2.ExecuteNonQuery();
                    //oconn1.CommandText = sql;
                    //oconn1.ExecuteNonQuery();
                    conn2.Close();
                    switchFind = true;
                }
                switchFind = false;


            }
            catch (Exception ButtonException2)
            {
                MessageBox.Show(" an Exception happend. AddTicket Class Data did not add to file, " + ButtonException2);
                throw new MyException("Add Ticket Exception");
            }

        }