public static bool UpdateLastEmailReceived(DateTime newTime)
{
bool result = false;
var sqlEngine = new SqlEngine.Server(Constants.ConnectionString);
using (var command = sqlEngine.Connection.CreateCommand())
{
string queryString = @"UPDATE options SET option_value='" + newTime.ToString() +
"' WHERE option_key='email_last_received'";
command.CommandType = CommandType.Text;
command.CommandText = queryString;
using (var transaction = sqlEngine.Connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
command.Transaction = transaction;
int rowAffected = command.ExecuteNonQuery();
transaction.Commit();
result = rowAffected > 0;
}
catch (SqlException ex)
{
transaction.Rollback();
throw ex;
}
}
}
return(result);
}
public static bool Save(string subject, string senderEmail, string senderName, string content, DateTime receivedTime, bool isRead)
{
bool result = false;
var sqlEngine = new SqlEngine.Server(Constants.ConnectionString);
using (var command = sqlEngine.Connection.CreateCommand())
{
int read = 0;
if (isRead == true)
{
read = 1;
}
string queryString = @"INSERT INTO emails";
queryString += @" (subject, sender_mail, sender_name, message, received_at, is_read, created_at)";
queryString += @" VALUES('" + subject + @"','" + senderEmail + @"','" + senderName + @"','" +
content + @"','" + sqlEngine.ConvertToSqlDateTimeFormat(receivedTime) + @"','" +
read + @"',GETDATE())";
command.CommandType = CommandType.Text;
command.CommandText = queryString;
using (var transaction = sqlEngine.Connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
command.Transaction = transaction;
int rowAffected = command.ExecuteNonQuery();
transaction.Commit();
result = rowAffected > 0;
}
catch (SqlException ex)
{
transaction.Rollback();
throw ex;
}
}
}
return(result);
}
public static bool Exists(string subject, string senderEmail, DateTime receivedTime)
{
var result = false;
var sqlEngine = new SqlEngine.Server(Constants.ConnectionString);
using (var command = sqlEngine.Connection.CreateCommand())
{
string queryString = @"SELECT TOP 1 id FROM emails";
queryString += @" WHERE sender_mail='" + senderEmail + "'";
queryString += @" AND subject='" + subject + "'";
queryString += @" AND received_at='" + sqlEngine.ConvertToSqlDateTimeFormat(receivedTime) + "'";
command.CommandType = CommandType.Text;
command.CommandText = queryString;
try
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var value = reader.GetValue(0);
if (value != DBNull.Value)
{
result = true;
}
}
}
}
catch (SqlException ex)
{
throw ex;
}
}
sqlEngine.Dispose();
return(result);
}
public static DateTime LastEmailReceived()
{
var result = DateTime.MinValue;
var sqlEngine = new SqlEngine.Server(Constants.ConnectionString);
using (var command = sqlEngine.Connection.CreateCommand())
{
string queryString = @"SELECT TOP 1 option_value FROM options WHERE option_key='email_last_received'";
command.CommandType = CommandType.Text;
command.CommandText = queryString;
try
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var value = reader.GetValue(0);
if (value != DBNull.Value)
{
result = DateTime.Parse(value.ToString());
}
}
}
}
catch (SqlException ex)
{
throw ex;
}
}
sqlEngine.Dispose();
return(result);
}