public void UpdateDataFromInternalDatabase(string hostname, MailServerInfo mailServer)
{
DemandPermission();
using var transaction = MailDbContext.Database.BeginTransaction();
var mailboxProvider = new MailboxProvider
{
Id = 0,
Name = hostname
};
var pReq = MailDbContext.MailboxProvider.Add(mailboxProvider);
MailDbContext.SaveChanges();
mailboxProvider = pReq.Entity;
var providerId = mailboxProvider.Id;
var mailboxServer = new MailboxServer
{
Id = 0,
IdProvider = providerId,
Type = "smtp",
Hostname = hostname,
Port = 587,
SocketType = "STARTTLS",
UserName = "******",
Authentication = "",
IsUserData = false
};
var req = MailDbContext.MailboxServer.Add(mailboxServer);
MailDbContext.SaveChanges();
mailboxServer = req.Entity;
var smtpServerId = mailboxServer.Id;
mailboxServer = new MailboxServer
{
Id = 0,
IdProvider = providerId,
Type = "imap",
Hostname = hostname,
Port = 143,
SocketType = "STARTTLS",
UserName = "******",
Authentication = "",
IsUserData = false
};
req = MailDbContext.MailboxServer.Add(mailboxServer);
MailDbContext.SaveChanges();
mailboxServer = req.Entity;
var imapServerId = mailboxServer.Id;
var mailServerData = MailDbContext.ServerServer.FirstOrDefault();
var connectionString = Newtonsoft.Json.JsonConvert.SerializeObject(mailServer);
var server = new ServerServer
{
Id = 0,
MxRecord = hostname,
ConnectionString = connectionString,
ServerType = 2,
SmtpSettingsId = smtpServerId,
ImapSettingsId = imapServerId
};
MailDbContext.ServerServer.Add(server);
MailDbContext.SaveChanges();
if (mailServerData != null)
{
server = MailDbContext.ServerServer.Where(r => r.Id == mailServerData.Id).FirstOrDefault();
MailDbContext.ServerServer.Remove(server);
MailDbContext.SaveChanges();
providerId = MailDbContext.MailboxServer
.Where(r => r.Id == mailServerData.SmtpSettingsId)
.Select(r => r.IdProvider)
.FirstOrDefault();
var providers = MailDbContext.MailboxProvider.Where(r => r.Id == providerId).ToList();
MailDbContext.MailboxProvider.RemoveRange(providers);
MailDbContext.SaveChanges();
var servers = MailDbContext.MailboxServer
.Where(r => new[] { mailServerData.SmtpSettingsId, mailServerData.ImapSettingsId }.Any(a => a == r.Id))
.ToList();
MailDbContext.MailboxServer.RemoveRange(servers);
MailDbContext.SaveChanges();
var mailboxId = MailDbContext.Mailbox
.Where(r => r.IdSmtpServer == mailServerData.SmtpSettingsId)
.Where(r => r.IdInServer == mailServerData.ImapSettingsId)
.ToArray();
foreach (var m in mailboxId)
{
m.IdSmtpServer = smtpServerId;
m.IdInServer = imapServerId;
}
MailDbContext.SaveChanges();
}
transaction.Commit();
MailServiceHelperStorage.Remove();
}
public static void UpdateInternalDatabase(string hostname, MailServerInfo mailServer)
{
DemandPermission();
using (var dbManager = GetDb())
using (var transaction = dbManager.BeginTransaction())
{
var insertQuery = new SqlInsert("mail_mailbox_provider")
.InColumnValue("id", 0)
.InColumnValue("name", hostname)
.Identity(0, 0, true);
var providerId = dbManager.ExecuteScalar <int>(insertQuery);
insertQuery = new SqlInsert("mail_mailbox_server")
.InColumnValue("id", 0)
.InColumnValue("id_provider", providerId)
.InColumnValue("type", "smtp")
.InColumnValue("hostname", hostname)
.InColumnValue("port", 587)
.InColumnValue("socket_type", "STARTTLS")
.InColumnValue("username", "%EMAILADDRESS%")
.InColumnValue("authentication", "")
.InColumnValue("is_user_data", false)
.Identity(0, 0, true);
var smtpServerId = dbManager.ExecuteScalar <int>(insertQuery);
insertQuery = new SqlInsert("mail_mailbox_server")
.InColumnValue("id", 0)
.InColumnValue("id_provider", providerId)
.InColumnValue("type", "imap")
.InColumnValue("hostname", hostname)
.InColumnValue("port", 143)
.InColumnValue("socket_type", "STARTTLS")
.InColumnValue("username", "%EMAILADDRESS%")
.InColumnValue("authentication", "")
.InColumnValue("is_user_data", false)
.Identity(0, 0, true);
var imapServerId = dbManager.ExecuteScalar <int>(insertQuery);
var selectQuery = new SqlQuery("mail_server_server")
.Select("id, smtp_settings_id, imap_settings_id")
.SetMaxResults(1);
var mailServerData = dbManager
.ExecuteList(selectQuery)
.Select(r => new[] { Convert.ToInt32(r[0]), Convert.ToInt32(r[1]), Convert.ToInt32(r[2]) })
.FirstOrDefault();
var connectionString = Newtonsoft.Json.JsonConvert.SerializeObject(mailServer);
insertQuery = new SqlInsert("mail_server_server")
.InColumnValue("id", 0)
.InColumnValue("mx_record", hostname)
.InColumnValue("connection_string", connectionString)
.InColumnValue("server_type", 2)
.InColumnValue("smtp_settings_id", smtpServerId)
.InColumnValue("imap_settings_id", imapServerId);
dbManager.ExecuteNonQuery(insertQuery);
if (mailServerData != null)
{
var deleteQuery = new SqlDelete("mail_server_server")
.Where(Exp.Eq("id", mailServerData[0]));
dbManager.ExecuteNonQuery(deleteQuery);
selectQuery = new SqlQuery("mail_mailbox_server")
.Select("id_provider")
.Where(Exp.Eq("id", mailServerData[1]))
.SetMaxResults(1);
providerId = dbManager
.ExecuteList(selectQuery)
.Select(r => Convert.ToInt32(r[0]))
.FirstOrDefault();
deleteQuery = new SqlDelete("mail_mailbox_provider")
.Where(Exp.Eq("id", providerId));
dbManager.ExecuteNonQuery(deleteQuery);
deleteQuery = new SqlDelete("mail_mailbox_server")
.Where(Exp.In("id", new[] { mailServerData[1], mailServerData[2] }));
dbManager.ExecuteNonQuery(deleteQuery);
selectQuery = new SqlQuery("mail_mailbox")
.Select("id")
.Where(Exp.Eq("id_smtp_server", mailServerData[1]))
.Where(Exp.Eq("id_in_server", mailServerData[2]));
var mailboxId = dbManager
.ExecuteList(selectQuery)
.Select(r => Convert.ToInt32(r[0]))
.ToArray();
var updateQuery = new SqlUpdate("mail_mailbox")
.Set("id_smtp_server", smtpServerId)
.Set("id_in_server", imapServerId)
.Where(Exp.In("id", mailboxId));
dbManager.ExecuteNonQuery(updateQuery);
}
transaction.Commit();
Cache.Remove(CacheKey);
}
}