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(); }
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]); }
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(); }
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(); }
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(); }
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]); }