/// <summary>
/// Gets a specific resource mailbox
/// </summary>
/// <param name="userPrincipalName"></param>
/// <returns></returns>
public static ResourceMailbox GetResourceMailbox(string userPrincipalName)
{
SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM ResourceMailboxes WHERE UserPrincipalName=@UserPrincipalName", sql);
ResourceMailbox mailbox = new ResourceMailbox();
try
{
// Add our parameter
cmd.Parameters.AddWithValue("@UserPrincipalName", userPrincipalName);
// Open connection
sql.Open();
// Read data
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
mailbox.DisplayName = r["DisplayName"].ToString();
mailbox.CompanyCode = r["CompanyCode"].ToString();
mailbox.UserPrincipalName = r["UserPrincipalName"].ToString();
mailbox.PrimarySmtpAddress = r["PrimarySmtpAddress"].ToString();
mailbox.ResourceType = r["ResourceType"].ToString();
mailbox.MailboxPlanID = int.Parse(r["MailboxPlan"].ToString());
mailbox.AdditionalMB = int.Parse(r["AdditionalMB"].ToString());
}
// Dispose
r.Close();
r.Dispose();
return mailbox;
}
catch (Exception)
{
throw;
}
finally
{
cmd.Dispose();
sql.Dispose();
}
}
/// <summary>
/// Updates a resource mailbox
/// </summary>
/// <param name="mbx"></param>
/// <param name="mailboxPlanID"></param>
/// <param name="additionalMB"></param>
public static void UpdateResourceMailbox(ResourceMailbox mbx, int mailboxPlanID, int additionalMB)
{
SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
SqlCommand cmd = new SqlCommand(@"UPDATE ResourceMailboxes SET DisplayName=@DisplayName, PrimarySmtpAddress=@PrimarySmtpAddress, MailboxPlan=@MailboxPlan, AdditionalMB=@AdditionalMB WHERE UserPrincipalName=@UserPrincipalName", sql);
try
{
logger.Debug("Updating resource mailbox in SQL for " + mbx.UserPrincipalName);
// Add our parameter
cmd.Parameters.AddWithValue("@DisplayName", mbx.DisplayName);
cmd.Parameters.AddWithValue("@UserPrincipalName", mbx.UserPrincipalName);
cmd.Parameters.AddWithValue("@PrimarySmtpAddress", mbx.PrimarySmtpAddress);
cmd.Parameters.AddWithValue("@MailboxPlan", mbx.MailboxPlanID);
cmd.Parameters.AddWithValue("@AdditionalMB", additionalMB);
// Open connection
sql.Open();
// Update data
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cmd.Dispose();
sql.Dispose();
}
}
/// <summary>
/// Gets a list of resource mailboxes
/// </summary>
/// <param name="companyCode"></param>
/// <returns></returns>
public static List<ResourceMailbox> GetResourceMailboxes(string companyCode)
{
SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM ResourceMailboxes WHERE CompanyCode=@CompanyCode ORDER BY ResourceType,DisplayName", sql);
// Create our collection
List<ResourceMailbox> mailboxes = new List<ResourceMailbox>();
try
{
// Add our parameter
cmd.Parameters.AddWithValue("@CompanyCode", companyCode);
// Open connection
sql.Open();
// Read data
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
ResourceMailbox tmp = new ResourceMailbox();
tmp.DisplayName = r["DisplayName"].ToString();
tmp.CompanyCode = r["CompanyCode"].ToString();
tmp.UserPrincipalName = r["UserPrincipalName"].ToString();
tmp.PrimarySmtpAddress = r["PrimarySmtpAddress"].ToString();
tmp.ResourceType = r["ResourceType"].ToString();
mailboxes.Add(tmp);
}
// Dispose
r.Close();
r.Dispose();
return mailboxes;
}
catch (Exception)
{
throw;
}
finally
{
cmd.Dispose();
sql.Dispose();
}
}
/// <summary>
/// Adds a resource mailbox to the database
/// </summary>
/// <param name="mbx"></param>
public static void AddResourceMailbox(ResourceMailbox mbx, int mailboxPlanID, int additionalMB)
{
SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
SqlCommand cmd = new SqlCommand(@"INSERT INTO ResourceMailboxes
(DisplayName, CompanyCode, UserPrincipalName, PrimarySmtpAddress, ResourceType, MailboxPlan, AdditionalMB)
VALUES
(@DisplayName, @CompanyCode, @UserPrincipalName, @PrimarySmtpAddress, @ResourceType, @MailboxPlan, @AdditionalMB)", sql);
try
{
// Add our parameter
cmd.Parameters.AddWithValue("@DisplayName", mbx.DisplayName);
cmd.Parameters.AddWithValue("@CompanyCode", mbx.CompanyCode);
cmd.Parameters.AddWithValue("@UserPrincipalName", mbx.UserPrincipalName);
cmd.Parameters.AddWithValue("@PrimarySmtpAddress", mbx.PrimarySmtpAddress);
cmd.Parameters.AddWithValue("@ResourceType", mbx.ResourceType);
cmd.Parameters.AddWithValue("@MailboxPlan", mbx.MailboxPlanID);
cmd.Parameters.AddWithValue("@AdditionalMB", additionalMB);
// Open connection
sql.Open();
// Insert data
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cmd.Dispose();
sql.Dispose();
}
}
