}// private void ParseTheFile()
private void UpdateTechDataSheetInDb(NpaNxxData n)
{
char[] sep = new char[] { ',' };
char[] trim = new char[] { '"' };
StringBuilder cmdStr = new StringBuilder("INSERT INTO NpaNxx ");
try
{
cmdStr.Append("( npanxx,lata,city,state ) VALUES( ");
// add the data to the string
cmdStr.Append("'" + n.NpaNxx + "'");
cmdStr.Append(",'" + n.Lata + "'");
cmdStr.Append(",'" + n.City + "'");
// note special terminator here to end the sql command string
cmdStr.Append(",'" + n.State + "'" + ")");
using (SqlConnection connection = new SqlConnection(m_connectionString))
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), connection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine("ECaughtConfiguringSQLCommand::" + ex.Message + ex.StackTrace);
}
} // UpdateTechDataSheetInDb
}// 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 <NpaNxxData> 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
List <NpaNxxData> npaDataList = new List <NpaNxxData>();
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[0].Contains("npanxx"))
{
// we have a non-data line -- header or footer... so skip it
continue;
}
else
{
// process the data
NpaNxxData n = ParseTheLine(data);
// add the record to the list for now as well as update the database
npaDataList.Add(n);
this.UpdateTechDataSheetInDb(n);
}
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;
}
LogMsg(errorMsg + "\r\n" + ex.Message + "\r\n" + ex.StackTrace);
}
}
}
}// try
catch (Exception e)
{
LogMsg(e.Message + "\r\n" + e.StackTrace);
}// catch
return(npaDataList);
}// private void ParseTheFile()
}// private void LogFileMsg(string msg)
/// <summary>
/// method to get the SID/BID for the subscriber based on the subscribers MSID
///
/// </summary>
/// <param name="msid">The MSID of the subscriber</param>
/// <returns></returns>
public NpaNxxData GetNpaNxxInfo(string npanxx)
{
NpaNxxData n = new NpaNxxData();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * from NpaNxx where npanxx = '" + npanxx + "' ");
DataSet ds = new DataSet();
try
{
using (SqlConnection connection = new SqlConnection(m_connectionString))
{
connection.Open();
// execute and fill
SqlDataAdapter myDaptor = new SqlDataAdapter(sb.ToString(), connection);
myDaptor.Fill(ds);
// make sure there is only one SID/BID returned here
foreach (DataTable myTable in ds.Tables)
{
//r.count = myTable.Rows.Count.ToString();
// only one row with the sidbid
foreach (DataRow myRow in myTable.Rows)
{
n.NpaNxx = myRow.ItemArray[1].ToString();
n.Lata = myRow.ItemArray[2].ToString();
n.City = myRow.ItemArray[3].ToString();
n.State = myRow.ItemArray[4].ToString();
}
}
}
}
catch (System.Exception ex)
{
// log the error, what is our logging policy?
}
return(n);
}
/// <summary>
/// method to get the SID/BID for the subscriber based on the subscribers MSID
///
/// </summary>
/// <param name="msid">The MSID of the subscriber</param>
/// <returns></returns>
public NpaNxxData GetNpaNxxInfo(string npanxx)
{
NpaNxxData n = new NpaNxxData();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * from NpaNxx where npanxx = '" + npanxx + "' ");
DataSet ds = new DataSet();
try
{
using (SqlConnection connection = new SqlConnection(m_connectionString))
{
connection.Open();
// execute and fill
SqlDataAdapter myDaptor = new SqlDataAdapter(sb.ToString(), connection);
myDaptor.Fill(ds);
// make sure there is only one SID/BID returned here
foreach (DataTable myTable in ds.Tables)
{
//r.count = myTable.Rows.Count.ToString();
// only one row with the sidbid
foreach (DataRow myRow in myTable.Rows)
{
n.NpaNxx = myRow.ItemArray[1].ToString();
n.Lata = myRow.ItemArray[2].ToString();
n.City = myRow.ItemArray[3].ToString();
n.State = myRow.ItemArray[4].ToString();
}
}
}
}
catch (System.Exception ex)
{
// log the error, what is our logging policy?
}
return n;
}
} // UpdateTechDataSheetInDb
private NpaNxxData ParseTheLine(string[] data)
{
NpaNxxData d = new NpaNxxData(data);
return(d);
}
private void UpdateTechDataSheetInDb(NpaNxxData n)
{
char[] sep = new char[] { ',' };
char[] trim = new char[] { '"' };
StringBuilder cmdStr = new StringBuilder("INSERT INTO NpaNxx ");
try
{
cmdStr.Append("( npanxx,lata,city,state ) VALUES( ");
// add the data to the string
cmdStr.Append("'" + n.NpaNxx + "'");
cmdStr.Append(",'" + n.Lata + "'");
cmdStr.Append(",'" + n.City + "'");
// note special terminator here to end the sql command string
cmdStr.Append(",'" + n.State + "'" + ")");
using (SqlConnection connection = new SqlConnection(m_connectionString))
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), connection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine("ECaughtConfiguringSQLCommand::" + ex.Message + ex.StackTrace);
}
}
private NpaNxxData ParseTheLine(string[] data)
{
NpaNxxData d = new NpaNxxData(data);
return d;
}