private void UpdateTechDataSheetInDb( TechData td ) { char[] sep = new char[] { ',' }; char[] trim = new char[] { '"' }; StringBuilder cmdStr = new StringBuilder("INSERT INTO VerizonTechData "); // make the connection SqlConnection dataConnection = null; OpenDataConn(ref dataConnection); // add code to check the data connection // add try catch block around setting up the command try { cmdStr.Append("(carrierName,sidBid,sidName,state,band,"); cmdStr.Append("mbiMin,lowLineRange,highLineRange,hlrPointCode, hlrClliCode, hlrMscid, hlrEsid, transmittedCdmaSid,"); cmdStr.Append("gteTsiCarrierCode, rateCenter, rateCenterState, county, mtasLikeCode, rbtEmrsGroup ) VALUES("); // add the data to the string cmdStr.Append("'" + td.CarrierName + "'"); cmdStr.Append(",'" + td.SidBid + "'"); cmdStr.Append(",'" + td.SidName + "'"); cmdStr.Append(",'" + td.State + "'"); cmdStr.Append(",'" + td.Band + "'"); cmdStr.Append(",'" + td.MbiMin + "'"); cmdStr.Append(",'" + td.LowLineRange + "'"); cmdStr.Append(",'" + td.HighLineRange + "'"); cmdStr.Append(",'" + td.HlrPointCode + "'"); cmdStr.Append(",'" + td.HlrClliCode + "'"); cmdStr.Append(",'" + td.HlrMscid + "'"); cmdStr.Append(",'" + td.HlrEsid + "'"); cmdStr.Append(",'" + td.TransmittedCdmaSid + "'"); cmdStr.Append(",'" + td.GteTsiCarrierCode + "'"); cmdStr.Append(",'" + td.RateCenter + "'"); cmdStr.Append(",'" + td.RateCenterState + "'"); if (td.County.Contains("'")) cmdStr.Append(",'" + td.County.Replace(Convert.ToChar("'"), Convert.ToChar(" ")) + "'"); else cmdStr.Append(",'" + td.County + "'"); cmdStr.Append(",'" + td.MtasLikeCode + "'"); // note special terminator here to end the sql command string cmdStr.Append(",'" + td.RbtEmrsGroup + "'" + ")"); } catch (Exception ex) { Console.WriteLine("ECaughtConfiguringSQLCommand::" + ex.Message + ex.StackTrace); } try { SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), dataConnection); sqlCommand.CommandType = CommandType.Text; sqlCommand.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine("ECaught::" + e.Message + e.StackTrace); } this.CloseDataConn(ref dataConnection); }
private TechData ParseTheLine(string[] data ) { TechData d = new TechData(data); return d; }
private TechData ParseTheLine(string[] data) { TechData d = new TechData(data); return(d); }
}// private void ProcessWCIFile() /// <summary> /// private method to parse the file and store the data in an array /// the array is used later to update the database /// </summary> /// <param name="fileName"></param> /// <returns></returns> private List <TechData> ParseTheFile(string fileName) { // make the array , let size grow as we add data to it System.Collections.ArrayList theData = new System.Collections.ArrayList(); // params to store // update data of the file and the data itself DateTime fileUpdateDate; List <TechData> techDataList = new List <TechData>(); char[] sep = new char[] { ',' }; char[] trim = new char[] { ' ' }; int lineNumber = 1; try { using (StreamReader sr = new StreamReader(fileName)) { String line; while ((line = sr.ReadLine()) != null) { try { // parse the line string[] data = line.Split(sep); if (data.GetLength(0) < 0) { // we have a non-data line -- header or footer... so skip it continue; } else if (data[2].Contains("Updated:")) { // grab the update date of this file int yy = Convert.ToInt32(data[2].Substring(16, 4)); int mm = Convert.ToInt32(data[2].Substring(13, 2)); int dd = Convert.ToInt32(data[2].Substring(10, 2)); fileUpdateDate = new DateTime(yy, mm, dd, DateTime.Now.Hour, DateTime.Now.Minute, 0); // update our date in the database as well // also figure out how to roll back if there is an error loading the data UpdateTechDataSheetDateInDb(fileUpdateDate); } else if (data[0].Contains("VERIZON")) { // process the data TechData td = ParseTheLine(data); // add the record to the list for now as well as update the database techDataList.Add(td); this.UpdateTechDataSheetInDb(td); } lineNumber++; } catch (System.Exception ex) { string errorMsg = "Error in File>" + fileName + " Line>" + lineNumber; if (line != null) {// add the line information if available errorMsg += "Line>" + line; } LogError(errorMsg + "\r\n" + ex.Message + "\r\n" + ex.StackTrace); } } } }// try catch (Exception e) { LogError(e.Message + "\r\n" + e.StackTrace); }// catch return(techDataList); }// private void ParseTheFile()
private void UpdateTechDataSheetInDb(TechData td) { char[] sep = new char[] { ',' }; char[] trim = new char[] { '"' }; StringBuilder cmdStr = new StringBuilder("INSERT INTO VerizonTechData "); // make the connection SqlConnection dataConnection = null; OpenDataConn(ref dataConnection); // add code to check the data connection // add try catch block around setting up the command try { cmdStr.Append("(carrierName,sidBid,sidName,state,band,"); cmdStr.Append("mbiMin,lowLineRange,highLineRange,hlrPointCode, hlrClliCode, hlrMscid, hlrEsid, transmittedCdmaSid,"); cmdStr.Append("gteTsiCarrierCode, rateCenter, rateCenterState, county, mtasLikeCode, rbtEmrsGroup ) VALUES("); // add the data to the string cmdStr.Append("'" + td.CarrierName + "'"); cmdStr.Append(",'" + td.SidBid + "'"); cmdStr.Append(",'" + td.SidName + "'"); cmdStr.Append(",'" + td.State + "'"); cmdStr.Append(",'" + td.Band + "'"); cmdStr.Append(",'" + td.MbiMin + "'"); cmdStr.Append(",'" + td.LowLineRange + "'"); cmdStr.Append(",'" + td.HighLineRange + "'"); cmdStr.Append(",'" + td.HlrPointCode + "'"); cmdStr.Append(",'" + td.HlrClliCode + "'"); cmdStr.Append(",'" + td.HlrMscid + "'"); cmdStr.Append(",'" + td.HlrEsid + "'"); cmdStr.Append(",'" + td.TransmittedCdmaSid + "'"); cmdStr.Append(",'" + td.GteTsiCarrierCode + "'"); cmdStr.Append(",'" + td.RateCenter + "'"); cmdStr.Append(",'" + td.RateCenterState + "'"); if (td.County.Contains("'")) { cmdStr.Append(",'" + td.County.Replace(Convert.ToChar("'"), Convert.ToChar(" ")) + "'"); } else { cmdStr.Append(",'" + td.County + "'"); } cmdStr.Append(",'" + td.MtasLikeCode + "'"); // note special terminator here to end the sql command string cmdStr.Append(",'" + td.RbtEmrsGroup + "'" + ")"); } catch (Exception ex) { Console.WriteLine("ECaughtConfiguringSQLCommand::" + ex.Message + ex.StackTrace); } try { SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), dataConnection); sqlCommand.CommandType = CommandType.Text; sqlCommand.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine("ECaught::" + e.Message + e.StackTrace); } this.CloseDataConn(ref dataConnection); }