Beispiel #1
0
        public void buildAccessFile()
        {
            if (File.Exists(accessFilePath))
                File.Delete(accessFilePath);

            /* NOTE */
            //COM MS ADO EXT and disable embeded type
            /* NOTE */

            CatalogClass catalog = new CatalogClass();
            catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + accessFilePath);

            //Build tables
            library.oledbConn accessFile = new library.oledbConn(this.accessFilePath, "mdb");

            accessFile.execute("create table Sites "
            + " ( "
            + " [siteId]    integer,"
            + " [siteName]  TEXT"
            + " ) "
            );

            accessFile.execute("create table Data"
            + " ( "
            + " [dataId]    AUTOINCREMENT(1, 1) PRIMARY KEY,"
            + " [siteId]    integer,"
            + " [_weekNum]  integer,"
            + " [_year]     integer,"
            + " [_calValue] TEXT"
            + " ) "
            );

            accessFile.terminate();
        }
Beispiel #2
0
        public void process(DataTable dt)
        {
            library.oledbConn accessFile = new library.oledbConn(this.accessFilePath, "mdb");

            //Per each Column [1-~]
            for (int colIndex = 1; colIndex < dt.Columns.Count; colIndex++)
            {
                DataColumn curCol = dt.Columns[colIndex];

                string[] colNameCombo = curCol.ColumnName.ToString().Split(new char[] { '\\' });
                string siteId = colNameCombo[0].Trim();
                string siteName = colNameCombo[1].Trim();

                if (siteName == "Reach")
                    continue;

                siteName = this.applyFiltering(siteName);

                //if site Id repeated then override the record
                int exisingSiteId = accessFile.fetch("select 1 from sites where siteId=" + siteId).Tables[0].Rows.Count;
                if (exisingSiteId != 0)
                {
                    //if (MessageBox.Show("Want to Override ? siteId=" + siteId + " siteName=" + siteName, "", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    //{
                    //    accessFile.execute("update Sites set siteName='" + siteName + "' where siteId=" + siteId);
                    //}
                    //else
                    //{
                    //    //Do nothing and keep old record
                    //}
                }
                else
                {
                    accessFile.execute("insert into Sites (siteId,siteName) values"
                    + "( "
                    + "'" + siteId + "',"
                    + "'" + siteName + "'"
                    + ")"
                    );
                }

                //Per each Row[0-~]
                for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
                {
                    DataRow curRow = dt.Rows[rowIndex];
                    string[] dateCombo = curRow[0].ToString().Split(' ');

                    //check if got to the end of file
                    if (dateCombo.Length == 1)
                        break;

                    string week = dateCombo[1].Trim();
                    string year = dateCombo[2].Trim();
                    string value = dt.Rows[rowIndex][colIndex].ToString().Trim();

                    float calValue;
                    if (value == "")
                        calValue = 0;
                    else
                    {
                        //Apply Filter
                        calValue = float.Parse(this.applyFiltering(value.ToString()));

                        if (float.Parse(value) > 0)
                            calValue = float.Parse(value) * float.Parse("3.986") / float.Parse("2.1");
                        if (float.Parse(value) < 0)
                            calValue = float.Parse(value) / float.Parse("1.253");
                    }

                    accessFile.execute("insert into Data (siteId,_weekNum,_year,_calValue) values"
                    + "( "
                    + "'" + siteId + "',"
                    + "'" + week + "',"
                    + "'" + year + "',"
                    + "'" + calValue + "'"
                    + ")"
                    );
                }//column iteraton End
            }//Row Itteration End

            accessFile.terminate();

            //MessageBox.Show("'<>' # : " + this.firstMatchCounter.ToString());
            //MessageBox.Show("'summit' # : " + this.secondMatchCounter.ToString());

            //Load access File data
            library.oledbConn accessFile2 = new library.oledbConn(this.accessFilePath, "mdb");
            DataSet ds = accessFile2.fetch("SELECT * FROM Data d inner join Sites s on s.siteId=d.siteId");
            //dataGridView2.DataSource = ds.Tables[0].DefaultView;
            accessFile2.terminate();
        }
Beispiel #3
0
        private void Run()
        {
            //load Excel File
            library.oledbConn excelFile = new library.oledbConn(this.excelFilePath, "xls");
            DataSet ds = excelFile.fetch("SELECT * FROM Wk_1_1996");
            excelFile.terminate();

            //rebuild access File and tables
            this.buildAccessFile();

            //do Procesing based on logic
            this.process(ds.Tables[0]);
        }