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); } }