}// AddFileInfo()
/// <summary>
/// method to update the database with the user provided fileinfo
/// </summary>
/// <param name="fileName">user populated file information </param>
public void UpdateFileInfo( OmcCdrFileInfo i )
{
// add code to make sure the fields are populated
// make the connection
SqlConnection dataConnection = null;
OpenDataConn(ref dataConnection);
try
{
StringBuilder cmdStr = new StringBuilder( "UPDATE FilesDownloaded SET downloaded = " + i.Downloaded + ",");
cmdStr.Append( " dateDownloaded = '" + i.DateDownloaded.ToString() + "'," );
cmdStr.Append( " storedInDb = " + i.StoredInDb + "," );
cmdStr.Append( " dateStoredInDb = '" + i.DateStoredInDb.ToString() + "'," );
cmdStr.Append(" ciberCreated = " + i.CiberCreated + ",");
cmdStr.Append(" dateCiberCreated = '" + i.DateCiberCreated.ToString() + "',");
cmdStr.Append(" fileMerged = " + i.FileMerged + ",");
cmdStr.Append(" dateFileMerged = '" + i.DateFileMerged.ToString() + "'");
cmdStr.Append( " WHERE fileName = '" );
cmdStr.Append( i.FileName + "'" );
SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), dataConnection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
catch (System.Exception e)
{
OmcCdrFileWriter.Instance.WriteToLogFile("OmcCdrDb::UpdateFileInfo():FailedTryingToUpdateTheFileInfoInTheDB:" + i.FileName + e.Message + "\r\n" + e.StackTrace);
}
CloseDataConn(ref dataConnection);
}// UpdateDateDownloaded()
/// <summary>
/// method to add the fully populated fileinfo to the database to prevent:
/// the file from being dowloaded twice
/// the CDRs from being duplicated into the database
/// the CIBER records being created twice
/// </summary>
/// <param name="fileName">name of the file that has been downloaded from the remote server</param>
public void AddFileInfo(OmcCdrFileInfo fileInfo )
{
// add logic to verify all fields in the fileInfo are set before trying to use them
// make the connection
SqlConnection dataConnection = null;
OpenDataConn(ref dataConnection);
try
{
StringBuilder cmdStr = new StringBuilder("INSERT INTO FilesDownloaded ");
cmdStr.Append("(fileName, downloaded, dateDownloaded, storedInDb, dateStoredInDb, ciberCreated, dateCiberCreated ) VALUES(");
cmdStr.Append("'" + fileInfo.FileName + "', "+fileInfo.StoredInDb+ ", '" + fileInfo.DateDownloaded.ToString() + "'," + fileInfo.StoredInDb);
cmdStr.Append(",'" + fileInfo.DateStoredInDb.ToString() + "',"+ fileInfo.CiberCreated + ",'" + fileInfo.DateCiberCreated.ToString() + "' )" );
SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), dataConnection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
catch (System.Exception e)
{
OmcCdrFileWriter.Instance.WriteToLogFile("OmcCdrDb::AddFileInfo():FailedTryingToAddTheFileNameInTheDB:" + e.Message + "\r\n" + e.StackTrace);
}
CloseDataConn(ref dataConnection);
}
}// AddFileInfo()
/// <summary>
/// method to add the fully populated fileinfo to the database to prevent:
/// the file from being dowloaded twice
/// the CDRs from being duplicated into the database
/// the CIBER records being created twice
/// </summary>
/// <param name="fileName">name of the file that has been downloaded from the remote server</param>
public void AddFileInfo(OmcCdrFileInfo fileInfo )
{
// add logic to verify all fields in the fileInfo are set before trying to use them
// make the connection
SqlConnection dataConnection = null;
OpenDataConn(ref dataConnection);
try
{
StringBuilder cmdStr = new StringBuilder("INSERT INTO FilesDownloaded ");
cmdStr.Append("(fileName, downloaded, dateDownloaded, storedInDb, dateStoredInDb, ciberCreated, dateCiberCreated ) VALUES(");
cmdStr.Append("'" + fileInfo.FileName + "', "+fileInfo.StoredInDb+ ", '" + fileInfo.DateDownloaded.ToString() + "'," + fileInfo.StoredInDb);
cmdStr.Append(",'" + fileInfo.DateStoredInDb.ToString() + "',"+ fileInfo.CiberCreated + ",'" + fileInfo.DateCiberCreated.ToString() + "' )" );
SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), dataConnection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
catch (System.Exception e)
{
OmcCdrFileWriter.Instance.WriteToLogFile("OmcCdrDb::AddFileInfo():FailedTryingToAddTheFileNameInTheDB:" + e.Message + "\r\n" + e.StackTrace);
}
CloseDataConn(ref dataConnection);
}// AddFileInfo()
}// public void WriteToFile(string msg)
private bool CheckDbBeforeProcessingFile(string fileName)
{
OmcCdrFileInfo info = m_db.GetOmcCdrInfo(fileName);
if (info.StoredInDb == 1)
{
return(true);
}
return(false);
}//CheckDbBeforeDownloadingFile
}// MoveTheNewCdrFileForCiberProcessing()
/// <summary>
/// method to make sure that we have not created CIBER records based on
/// this file
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
private bool CheckDbBeforeProcessingFile(string fileName)
{
OmcCdrFileInfo info = m_db.GetOmcCdrInfo(fileName);
// if we have already merged this file
if (info.FileMerged == 1)
{
return(true);
}
// otherwise we need to merge the file
return(false);
}//CheckDbBeforeDownloadingFile
}// UpdateDateDownloaded()
/// <summary>
/// method to add the fileinfo to the database to prevent it from being downloaded twice
/// </summary>
/// <param name="fileName">fileinfo object</param>
public void AddDateDownloaded( string fileName )
{
// make the connection
SqlConnection dataConnection = null;
OpenDataConn(ref dataConnection);
try
{
StringBuilder cmdStr = new StringBuilder("INSERT INTO FilesDownloaded ");
cmdStr.Append("(fileName, downloaded, dateDownloaded, storedInDb, ciberCreated, fileMerged) VALUES(");
cmdStr.Append("'" + fileName + "', 1 , '" + DateTime.Now.ToString() + "',");
cmdStr.Append("0,0,0 )");
SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), dataConnection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException se)
{
if (se.Message.Contains("PRIMARY KEY"))
{
// then we update the info and not add a new one
try
{
OmcCdrFileInfo ri = GetOmcCdrInfo(fileName);
ri.Downloaded = 1;
ri.DateDownloaded = DateTime.Now;
UpdateFileInfo(ri);
}
catch (System.Exception e)
{
OmcCdrFileWriter.Instance.WriteToLogFile("OmcCdrDb::UpdateFileInfo():FailedTryingToUPDATETheFileInfoInTheDB:" + fileName + e.Message + "\r\n" + e.StackTrace);
}
}// if
}
catch (System.Exception e)
{
OmcCdrFileWriter.Instance.WriteToLogFile("OmcCdrDb::AddDateDownloaded():FailedTryingToAddTheFileNameInTheDB:" + e.Message + "\r\n" + e.StackTrace);
}
CloseDataConn(ref dataConnection);
}// AddFileInfo()
public bool CheckDbForFileDownloaded(string fileName)
{
bool fileDownloaded = false;
try
{
OmcCdrFileInfo ri = GetOmcCdrInfo(fileName);
if ( ri.Downloaded.Equals(0) )
fileDownloaded = false;
else
fileDownloaded = true;
}
catch (System.Exception e)
{
OmcCdrFileWriter.Instance.WriteToLogFile("OmcCdrDb::CheckDbForFileDownloaded():FailedTryingToGetTheFileNameInTheDB:" + fileName + e.Message + "\r\n" + e.StackTrace);
}
return fileDownloaded;
}//CheckDbBeforeDownloadingFile
/// <summary>
/// method to update the database with the user provided fileinfo
/// </summary>
/// <param name="fileName">user populated file information </param>
public void UpdateFileInfo( OmcCdrFileInfo i )
{
// add code to make sure the fields are populated
// make the connection
SqlConnection dataConnection = null;
OpenDataConn(ref dataConnection);
try
{
StringBuilder cmdStr = new StringBuilder( "UPDATE FilesDownloaded SET downloaded = " + i.Downloaded + ",");
cmdStr.Append( " dateDownloaded = '" + i.DateDownloaded.ToString() + "'," );
cmdStr.Append( " storedInDb = " + i.StoredInDb + "," );
cmdStr.Append( " dateStoredInDb = '" + i.DateStoredInDb.ToString() + "'," );
cmdStr.Append(" ciberCreated = " + i.CiberCreated + ",");
cmdStr.Append(" dateCiberCreated = '" + i.DateCiberCreated.ToString() + "',");
cmdStr.Append(" fileMerged = " + i.FileMerged + ",");
cmdStr.Append(" dateFileMerged = '" + i.DateFileMerged.ToString() + "'");
cmdStr.Append( " WHERE fileName = '" );
cmdStr.Append( i.FileName + "'" );
SqlCommand sqlCommand = new SqlCommand(cmdStr.ToString(), dataConnection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
catch (System.Exception e)
{
OmcCdrFileWriter.Instance.WriteToLogFile("OmcCdrDb::UpdateFileInfo():FailedTryingToUpdateTheFileInfoInTheDB:" + i.FileName + e.Message + "\r\n" + e.StackTrace);
}
CloseDataConn(ref dataConnection);
}
public OmcCdrFileInfo GetOmcCdrInfo(string fileName)
{
StringBuilder cmdStr = new StringBuilder("SELECT * from FilesDownloaded where fileName =");
DataSet ds = new DataSet();
SqlDataAdapter rDapter = null;
SqlConnection dataConnection = null;
OmcCdrFileInfo rInfo = new OmcCdrFileInfo();
try
{
// make the connection
OpenDataConn(ref dataConnection);
cmdStr.Append("'" + fileName + "'");
// execute and fill
rDapter = new SqlDataAdapter(cmdStr.ToString(), dataConnection);
rDapter.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)
{
rInfo.FileName = myRow.ItemArray[0].ToString();
rInfo.Downloaded = (byte) myRow.ItemArray[1];
if (!myRow.ItemArray[2].Equals(System.DBNull.Value))
rInfo.DateDownloaded = (DateTime)myRow.ItemArray[2];
else
rInfo.DateDownloaded = NullDateTime;
rInfo.StoredInDb = (byte) myRow.ItemArray[3];
if (!myRow.ItemArray[4].Equals(System.DBNull.Value))
rInfo.DateStoredInDb = (DateTime)myRow.ItemArray[4];
else
rInfo.DateStoredInDb = NullDateTime;
rInfo.CiberCreated = (byte) myRow.ItemArray[5];
if ( !myRow.ItemArray[6].Equals(System.DBNull.Value ) )
rInfo.DateCiberCreated = (DateTime) myRow.ItemArray[6];
else
rInfo.DateCiberCreated = NullDateTime;
rInfo.FileMerged = (byte)myRow.ItemArray[7];
if (!myRow.ItemArray[8].Equals(System.DBNull.Value))
rInfo.DateFileMerged = (DateTime)myRow.ItemArray[8];
else
rInfo.DateFileMerged = NullDateTime;
}
}
// shut it down
CloseDataConn( ref dataConnection);
}
catch (Exception ex)
{
OmcCdrFileWriter.Instance.WriteToLogFile( "OmcCdrDb::GetOmcCdrInfo():ECaught:" + ex.Message + "\r\n" + ex.StackTrace );
}
return rInfo;
}
public OmcCdrFileInfo GetOmcCdrInfo(string fileName)
{
StringBuilder cmdStr = new StringBuilder("SELECT * from FilesDownloaded where fileName =");
DataSet ds = new DataSet();
SqlDataAdapter rDapter = null;
SqlConnection dataConnection = null;
OmcCdrFileInfo rInfo = new OmcCdrFileInfo();
try
{
// make the connection
OpenDataConn(ref dataConnection);
cmdStr.Append("'" + fileName + "'");
// execute and fill
rDapter = new SqlDataAdapter(cmdStr.ToString(), dataConnection);
rDapter.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)
{
rInfo.FileName = myRow.ItemArray[0].ToString();
rInfo.Downloaded = (byte) myRow.ItemArray[1];
if (!myRow.ItemArray[2].Equals(System.DBNull.Value))
rInfo.DateDownloaded = (DateTime)myRow.ItemArray[2];
else
rInfo.DateDownloaded = NullDateTime;
rInfo.StoredInDb = (byte) myRow.ItemArray[3];
if (!myRow.ItemArray[4].Equals(System.DBNull.Value))
rInfo.DateStoredInDb = (DateTime)myRow.ItemArray[4];
else
rInfo.DateStoredInDb = NullDateTime;
rInfo.CiberCreated = (byte) myRow.ItemArray[5];
if ( !myRow.ItemArray[6].Equals(System.DBNull.Value ) )
rInfo.DateCiberCreated = (DateTime) myRow.ItemArray[6];
else
rInfo.DateCiberCreated = NullDateTime;
rInfo.FileMerged = (byte)myRow.ItemArray[7];
if (!myRow.ItemArray[8].Equals(System.DBNull.Value))
rInfo.DateFileMerged = (DateTime)myRow.ItemArray[8];
else
rInfo.DateFileMerged = NullDateTime;
}
}
// shut it down
CloseDataConn( ref dataConnection);
}
catch (Exception ex)
{
OmcCdrFileWriter.Instance.WriteToLogFile( "OmcCdrDb::GetOmcCdrInfo():ECaught:" + ex.Message + "\r\n" + ex.StackTrace );
}
return rInfo;
}
