Siebel E-Mail Attachments Import - C# and MS Transact SQL

if exists (select * from dbo.sysobjects where id =
object_id(N"[dbo].[CrmAttachImporter]") and
OBJECTPROPERTY(id, N"IsUserTable") = 1)
drop table [dbo].[CrmAttachImporter]
GO

<config>
   <connectionString>provider=SQLOLEDB;
                     Initial Catalog=Albaspectrum;
                     Data Source=MSSQL1;
                     UserId=sa;Password=sa;</connectionString>   <msgFolder>data</msgFolder>   <tableName>CrmAttachImporter</tableName>   <activityGuidColumn>CrmActivityGuid</activityGuidColumn>   <msgFileNameColumn>MsgFileName</msgFileNameColumn>

</config>

Here, you described the MS SQL Server connection string, the path to messages-files in the file system, the name of the table that stores the relations Activity GUID and file names, and table column names. These are required for the import procedure.
Now, look at the method of potential attachments catalog scanning:
public void scanFolder() {
log = LogManager.GetLogger(typeof(AttachImporter));
DOMConfigurator.Configure(new FileInfo("log.config"));
try {
DirectoryInfo dirInfo = new DirectoryInfo(msgFolder);
FileInfo[] files = dirInfo.GetFiles();
Hashtable emails = new Hashtable();
foreach (FileInfo fileInfo in files) {
log.Debug("Analizing file: " + fileInfo.Name);
Guid activityId = GetActivityIdByFileName(fileInfo.Name);
if (! activityId.ToString().Equals(new Guid().ToString())) {
emails.Add(activityId, fileInfo.DirectoryName + @"\" +
fileInfo.Name);
Console.WriteLine("Marked for import: " + fileInfo.Name);
log.Debug("Marked for import: " + fileInfo.Name);
}
else {
Console.WriteLine("Not found in activity import list: " + fileInfo.Name);
log.Debug("Not found in activity import list: " + fileInfo.Name);
}
}
ProcessMessages(emails);
}
catch (Exception e) {
Console.WriteLine(e.Message + "\r\n" + e.StackTrace);
}
}
The central place in this method checks on the relationship existence in the import table for CRM Activity GUID and file name in the import directory:
private Guid GetActivityIdByFileName(string fileName) {
//create the database connection
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
//create the command object and store the sql query
OleDbCommand command = conn.CreateCommand();
command.CommandText = "SELECT " + activityGuidColumn + ", " +
msgFileNameColumn + " FROM " + tableName +
" WHERE UPPER(LTRIM(RTRIM(" +
msgFileNameColumn + "))) =
UPPER(LTRIM(RTRIM(?)))";
log.Debug("Preview checking SQL query: " + command.CommandText);
log.Debug("Using file name: " + fileName);
command.Parameters.Add(new OleDbParameter(msgFileNameColumn, fileName));
//create the datareader object to connect to table
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read()) {
Guid activityGuid = new Guid(reader.GetString(0));
reader.Close();
conn.Close();
return activityGuid;
}
else {
reader.Close();
conn.Close();
return new Guid();
}
}
Importing the messages cache is transferred as a parameter to the method, which does an attachment import into MS CRM:
private void ProcessMessages(Hashtable emails)
{
try