using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using log4net;
using Npgsql;
using NpgsqlTypes;
namespace SiteOffice.Data{
/// <summary>
/// Author: Joe Audette
/// Created: 2007-11-15
/// Last Modified: 2007-11-15
///
///
/// The use and distribution terms for this software are covered by the
/// Common Public License 1.0 (http://opensource.org/licenses/cpl.php)
/// which can be found in the file CPL.TXT at the root of this distribution.
/// By using this software in any fashion, you are agreeing to be bound by
/// the terms of this license.
///
/// You must not remove this notice, or any other, from this software.
///
/// </summary>
public static class DBUserEmailAccount
{
private static readonly ILog log = LogManager.GetLogger(typeof(DBUserEmailAccount));
public static String DBPlatform()
{
return "pgsql";
}
private static string GetConnectionString()
{
string connectionString = ConfigurationManager.AppSettings["PostgreSQLConnectionString"];
if (ConfigurationManager.AppSettings["SiteOfficePostgreSQLConnectionString"] != null)
{
connectionString = ConfigurationManager.AppSettings["SiteOfficePostgreSQLConnectionString"];
}
return connectionString;
}
public static int AddUserEmailAccount(
Guid id,
Guid userGuid,
string accountName,
string userName,
string email,
string password,
string pop3Server,
int pop3Port,
string smtpServer,
int smtpPort,
bool useSsl)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[11];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_useremailaccounts_insert(:id,:userguid,:accountname,:username,:email,:password,:pop3server,:pop3port,:smtpserver,:smtpport,:usessl)");
arParams[0].Value = id.ToString();
arParams[1].Value = userGuid.ToString();
arParams[2].Value = accountName;
arParams[3].Value = userName;
arParams[4].Value = email;
arParams[5].Value = password;
arParams[6].Value = pop3Server;
arParams[7].Value = pop3Port;
arParams[8].Value = smtpServer;
arParams[9].Value = smtpPort;
arParams[10].Value = useSsl;
}
else
{
arParams[0] = new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id.ToString();
arParams[1] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
arParams[2] = new NpgsqlParameter("accountname", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = accountName;
arParams[3] = new NpgsqlParameter("username", NpgsqlTypes.NpgsqlDbType.Varchar, 75);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userName;
arParams[4] = new NpgsqlParameter("email", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = email;
arParams[5] = new NpgsqlParameter("password", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = password;
arParams[6] = new NpgsqlParameter("pop3server", NpgsqlTypes.NpgsqlDbType.Varchar, 75);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = pop3Server;
arParams[7] = new NpgsqlParameter("pop3port", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = pop3Port;
arParams[8] = new NpgsqlParameter("smtpserver", NpgsqlTypes.NpgsqlDbType.Varchar, 75);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = smtpServer;
arParams[9] = new NpgsqlParameter("smtpport", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = smtpPort;
arParams[10] = new NpgsqlParameter("usessl", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = useSsl;
}
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
GetConnectionString(),
CommandType.StoredProcedure,
"mp_useremailaccounts_insert(:id,:userguid,:accountname,:username,:email,:password,:pop3server,:pop3port,:smtpserver,:smtpport,:usessl)",
arParams);
return rowsAffected;
}
public static bool UpdateUserEmailAccount(
Guid id,
string accountName,
string userName,
string email,
string password,
string pop3Server,
int pop3Port,
string smtpServer,
int smtpPort,
bool useSsl)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[10];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_useremailaccounts_update(:id,:accountname,:username,:email,:password,:pop3server,:pop3port,:smtpserver,:smtpport,:usessl)");
arParams[0].Value = id.ToString();
arParams[1].Value = accountName;
arParams[2].Value = userName;
arParams[3].Value = email;
arParams[4].Value = password;
arParams[5].Value = pop3Server;
arParams[6].Value = pop3Port;
arParams[7].Value = smtpServer;
arParams[8].Value = smtpPort;
arParams[9].Value = useSsl;
}
else
{
arParams[0] = new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id.ToString();
arParams[1] = new NpgsqlParameter("accountname", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = accountName;
arParams[2] = new NpgsqlParameter("username", NpgsqlTypes.NpgsqlDbType.Varchar, 75);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = userName;
arParams[3] = new NpgsqlParameter("email", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = email;
arParams[4] = new NpgsqlParameter("password", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = password;
arParams[5] = new NpgsqlParameter("pop3server", NpgsqlTypes.NpgsqlDbType.Varchar, 75);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = pop3Server;
arParams[6] = new NpgsqlParameter("pop3port", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = pop3Port;
arParams[7] = new NpgsqlParameter("smtpserver", NpgsqlTypes.NpgsqlDbType.Varchar, 75);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = smtpServer;
arParams[8] = new NpgsqlParameter("smtpport", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = smtpPort;
arParams[9] = new NpgsqlParameter("usessl", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = useSsl;
}
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(GetConnectionString(),
CommandType.StoredProcedure,
"mp_useremailaccounts_update(:id,:accountname,:username,:email,:password,:pop3server,:pop3port,:smtpserver,:smtpport,:usessl)",
arParams);
return (rowsAffected > -1);
}
public static bool DeleteUserEmailAccount(Guid id)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_useremailaccounts_delete(:id)");
arParams[0].Value = id.ToString();
}
else
{
arParams[0] = new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id.ToString();
}
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(GetConnectionString(),
CommandType.StoredProcedure,
"mp_useremailaccounts_delete(:id)",
arParams);
return (rowsAffected > -1);
}
public static IDataReader GetUserEmailAccount(Guid id)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_useremailaccounts_selectone(:id)");
arParams[0].Value = id.ToString();
}
else
{
arParams[0] = new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id.ToString();
}
return NpgsqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.StoredProcedure,
"mp_useremailaccounts_selectone(:id)",
arParams);
}
public static IDataReader GetUserEmailAccountByUser(Guid userGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_useremailaccounts_selectbyuser(:userguid)");
arParams[0].Value = userGuid.ToString();
}
else
{
arParams[0] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
}
return NpgsqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.StoredProcedure,
"mp_useremailaccounts_selectbyuser(:userguid)",
arParams);
}
}
}
|