Save HTML & Images in a Database with C#.NET

I have developed an automated emailing service that requires to send out HTML and text mails of which the content is retrieved from a SQL database.
What you can do is the following:
Create a preview page that will allow users to preview the HTML that they type in to RichTextBox. If they are happy with the HTML allow them to save the content of the rich text box. In the background you save the content as type text to your database(on SQL Server, I am not sure what the equivalant is on Oracle ). We do not save the images to the database as that was not a practical solution. Our images are published to website. In the HTML for the images your image source will point to where your images are published.
Now you can create a stored procedure to retrieve your email content assign the content to the body of the email and send it.
To give you an idea of the efficiency of this system take the following
figures into consideration. We send an average of 5 emails per second 24/7 wich meams that we send 157248000 emails in a year. The system has not broken down once.
Keep in mind that we do not save every email but only the content of a specific mail that needs to be sent to x amount of people.
Here is the function that retrieves the mail content:
private string getMailContent(int mailId)
{
DataSet dts=new DataSet();
string strMail="";
m_sqlDataAdapter=new SqlDataAdapter();
m_sqlDataAdapter.SelectCommand=m_sqlComGetMailContentCommand;
try
{
//open the connection if the connection
is closed
if(m_sqlConThisContentConnection.StateConnectionState.Closed)
{
m_sqlConThisContentConnection.Open();
}
m_sqlComGetMailContentSubjectCommand.Parameters["@MailId"].Value=mailId;
m_sqlDataAdapter.Fill(dts);
//check to see if there was content
retrieved
if(dts.Tables[0].Rows.Count>0)
{
strMail=dts.Tables[0].Rows[0]["ContentText"].ToString();
}
else
{
throw new Exception("No Mail
Content retrieved");
}
}
catch(Exception ex)
{
strError=ex.Message.ToString();
Utility.WriteLogFile(SERVICE_NAME +
"Unable to retrieve Mail content: " + strError,
LogFile.ErrorLevel.CriticalError);
}
//close connection if open and cleanup
finally
{
if(m_sqlConThisContentConnection.StateConnectionState.Open)
{
m_sqlConThisContentConnection.Close();
}
m_sqlDataAdapter.Dispose();
dts=null;
}
return strMail;
}
Here is the function that sends the mail:
private void SendWelcomeMailer(string strFirstName,string
strRealAccountNumber,string strGuestAccountNumber,string
strEmailAddress,
int intCasinoID,int intSession,string
Body,string Subject,string FromAddress,string ReplyTo, int
MailIdentifier, string strHTML)
{
//replace tags with player data
try
{
try
{
//check for mailformat
if
(strHTML.ToLower().Equals("false") )
{
SendMail(FromAddress,strEmailAddress,ReplyTo,strEmailAddress,Subject,Bod
y,System.Web.Mail.MailFormat.Text);
}
else
{
SendMail(FromAddress,strEmailAddress,ReplyTo,strEmailAddress,Subject,Bod
y,System.Web.Mail.MailFormat.Html);
}
WriteLogFile(SERVICE_NAME + " Email Sent to " +
strEmailAddress,LogFile.ErrorLevel.Good);
MarkAsSent(strEmailAddress,intCasinoID,true,MailIdentifier,intSession);/
/disable for debugging
}
catch(Exception ex)
{
WriteLogFile(SERVICE_NAME +
"Email not sent to" + strEmailAddress + ". " + ex.Message.ToString()
,LogFile.ErrorLevel.NormalError);
}
}
catch(Exception ex)
{
we do some error handling over here
}
finally
{
FromAddress=null;
ReplyTo=null;
Subject=null;
Body=null;
}