private void Deleteolddate(object sender, ElapsedEventArgs e) { clsBLMain _main = new clsBLMain(); if (_main.Deleteolddata()) { eventLog1.WriteEntry("4 days old data successfully deleted"); } else { eventLog1.WriteEntry("Error while deleting old data: " + _main.Error); } }
private DataTable getDatatable() { // eventLog1.WriteEntry("Now making data table"); DataTable table = new DataTable(); try { clsBLMain main = new clsBLMain(); DataView d = main.GetAll(4); if (d.Table.Rows.Count > 0) { main.MAxID = d[0]["maxresultid"].ToString(); } else { return(null); } DataView dv = main.GetAll(2); if (dv.Table.Rows.Count > 0) { table = dv.Table; } else { return(null); } DataView dv1 = main.GetAll(3); if (dv1.Table.Rows.Count > 0) { maxid = dv1[0]["maxid"].ToString(); } } catch (Exception ee) { eventLog1.WriteEntry("Error while creating DataTable:" + ee.ToString()); } return(table); // return table; }
public void insertRemoteData(DataTable dt) { //eventLog1.WriteEntry("In Remote Insertion method"); try { eventLog1.WriteEntry("Asynchronous call successful"); #region "Remote Server DB Login" int count = 0; string StrConnection = null; AppSettingsReader con = new AppSettingsReader(); // string connectionString = ConfigurationSettings.AppSettings.Get("ConnectionString"); StrConnection = Rot13.Transform(con.GetValue("RemoteServerConnectionString", "".GetType()).ToString()); // StrConnection = "dsn=saps"; try { Conn = new MySqlConnection(StrConnection); Conn.Open(); eventLog1.WriteEntry("Remote DB Connection Established"); // return Conn; } catch (Exception ex) { eventLog1.WriteEntry("Remote DB login failed: " + ex.Message); //return; } #endregion #region "Remote Server DB Insertion" if (dt.Rows.Count > 0) { ClsBLInterface blinterface = new ClsBLInterface(); QueryBuilder qb = new QueryBuilder(); // StringBuilder sb = new StringBuilder(); for (int i = 0; i < dt.Rows.Count; i++) { //eventLog1.WriteEntry("Reading records "); blinterface.Mserialno = dt.Rows[i]["BookingId"].ToString(); // sb.Append("\t"); blinterface.AttributeID = dt.Rows[i]["AttributeID"].ToString(); // sb.Append("\t"); blinterface.Value = dt.Rows[i]["Result"].ToString(); // sb.Append("\t"); blinterface.Enteredon = System.DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss");//enteredon // sb.Append("\t"); blinterface.ClientID = dt.Rows[i]["ClientID"].ToString().Substring(0, 3); // sb.Append("\r\n"); blinterface.EquipmentCode = dt.Rows[i]["MachineName"].ToString(); DataView dv = null; try { DataSet DS = null; try { string query = "select * from ls_tinterface where MserialNo ='" + blinterface.Mserialno + "' and AttributeID='" + blinterface.AttributeID + "'"; MySqlCommand ObjCmd = new MySqlCommand(query); // dv = blinterface.GetAll(1); ObjCmd.CommandText = query; ObjCmd.Connection = Conn; MySqlDataAdapter da = new MySqlDataAdapter(ObjCmd); DS = new DataSet(); da.Fill(DS); //Conn.Close(); } catch (Exception ee) { eventLog1.WriteEntry("Remote DB login off failed: " + ee.Message); //return; } finally { //Conn.Close(); //Conn.Dispose(); } dv = new DataView(DS.Tables[0]); } catch (Exception ee) { eventLog1.WriteEntry("Check Entry already exist at RDB exp:" + ee.Message); } if (dv.Table.Rows.Count > 0) { //blinterface.Update(); try { try { // Conn.Open(); } catch (Exception ee) { eventLog1.WriteEntry("Remote DB login failed: " + ee.Message); return; } int updaterw = 0; try { string updateq = qb.QBUpdate2(blinterface.MakeArray(), "ls_tinterface"); //string query = "select * from ls_tinterface where MserialNo ='" + blinterface.Mserialno + "' and AttributeID=" + blinterface.AttributeID + ""; MySqlCommand ObjCmd = new MySqlCommand(); // dv = blinterface.GetAll(1); ObjCmd.CommandText = updateq; ObjCmd.Connection = Conn; updaterw = ObjCmd.ExecuteNonQuery(); //Conn.Close(); } catch (Exception ee) { eventLog1.WriteEntry("Remote DB login off failed: " + ee.Message); //return; } finally { //Conn.Close(); //Conn.Dispose(); } if (updaterw > 0) { //eventLog1.WriteEntry("Remote DB row update and local log entry "); clsBLMain main = new clsBLMain(); maxid = dt.Rows[i]["ResultID"].ToString(); main.Senton = DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss"); main.Sentto = rmserver; main.status = "Y"; main.MAxID = maxid; try { if (main.Insert()) { eventLog1.WriteEntry("Audit Table insertion successful"); } else { eventLog1.WriteEntry("Following Error Occured while updating Audit Table: " + main.Error); } } catch (Exception ee) { eventLog1.WriteEntry("Following Error occured while updating Audit Table: " + ee.ToString()); } } } catch (Exception ee) { eventLog1.WriteEntry("Entry Update at RDB exp:" + ee.Message); } } else { //blinterface.Insert(); try { try { //Conn.Open(); } catch (Exception ee) { eventLog1.WriteEntry("Remote DB login failed: " + ee.Message); return; } int insertcheck = 0; try { string insertq = qb.QBInsert(blinterface.MakeArray(), "ls_tinterface"); //string query = "select * from ls_tinterface where MserialNo ='" + blinterface.Mserialno + "' and AttributeID=" + blinterface.AttributeID + ""; MySqlCommand ObjCmd = new MySqlCommand(); // dv = blinterface.GetAll(1); ObjCmd.CommandText = insertq; ObjCmd.Connection = Conn; insertcheck = ObjCmd.ExecuteNonQuery(); //Conn.Close(); } catch (Exception ee) { eventLog1.WriteEntry("Remote DB login off failed: " + ee.Message); //return; } finally { // Conn.Close(); // Conn.Dispose(); } if (insertcheck > 0) { clsBLMain main = new clsBLMain(); maxid = dt.Rows[i]["ResultID"].ToString(); main.Senton = DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss"); main.Sentto = rmserver; main.status = "Y"; main.MAxID = maxid; main.Insert(); } } catch (Exception ee) { eventLog1.WriteEntry("Entry Update at RDB exp:" + ee.Message); } } count++; } eventLog1.WriteEntry(count.ToString() + " Records Sucessfully inserted "); } try { Conn.Close(); Conn.Dispose(); eventLog1.WriteEntry("Connection Closed SUccessfully"); } catch (Exception ee) { eventLog1.WriteEntry("RDB Connection closing :" + ee.Message); } //finally //{ // Conn.Close(); // Conn.Dispose(); //} //return count; #endregion } catch (Exception ee) { eventLog1.WriteEntry("Exception in file operation method. " + ee.Message); } }