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