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 DBPrivateMessage
{
private static readonly ILog log = LogManager.GetLogger(typeof(DBPrivateMessage));
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 AddPrivateMessage(
Guid messageId,
Guid fromUser,
Guid priorityId,
string subject,
string body,
string toCsvList,
string ccCsvList,
string bccCsvList,
string toCsvLabels,
string ccCsvLabels,
string bccCsvLabels,
DateTime createdDate,
DateTime sentDate)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[12];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_privatemessages_insert(:messageid,:fromuser,:priorityid,:subject,:body,:tocsvlist,:cccsvlist,:bcccsvlist,:tocsvlabels,:cccsvlabels,:bcccsvlabels,:createddate,:sentdate)");
arParams[0].Value = messageId.ToString();
arParams[1].Value = fromUser.ToString();
arParams[2].Value = priorityId.ToString();
arParams[3].Value = subject;
arParams[4].Value = body;
arParams[5].Value = toCsvList;
arParams[6].Value = ccCsvList;
arParams[7].Value = bccCsvList;
arParams[8].Value = toCsvLabels;
arParams[9].Value = ccCsvLabels;
arParams[10].Value = bccCsvLabels;
arParams[11].Value = createdDate;
arParams[12].Value = sentDate;
}
else
{
arParams[0] = new NpgsqlParameter("messageid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = messageId.ToString();
arParams[1] = new NpgsqlParameter("fromuser", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = fromUser.ToString();
arParams[2] = new NpgsqlParameter("priorityid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = priorityId.ToString();
arParams[3] = new NpgsqlParameter("subject", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = subject;
arParams[4] = new NpgsqlParameter("body", NpgsqlTypes.NpgsqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = body;
arParams[5] = new NpgsqlParameter("tocsvlist", NpgsqlTypes.NpgsqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = toCsvList;
arParams[6] = new NpgsqlParameter("cccsvlist", NpgsqlTypes.NpgsqlDbType.Text);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = ccCsvList;
arParams[7] = new NpgsqlParameter("bcccsvlist", NpgsqlTypes.NpgsqlDbType.Text);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = bccCsvList;
arParams[8] = new NpgsqlParameter("tocsvlabels", NpgsqlTypes.NpgsqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = toCsvLabels;
arParams[9] = new NpgsqlParameter("cccsvlabels", NpgsqlTypes.NpgsqlDbType.Text);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = ccCsvLabels;
arParams[10] = new NpgsqlParameter("bcccsvlabels", NpgsqlTypes.NpgsqlDbType.Text);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = bccCsvLabels;
arParams[11] = new NpgsqlParameter("createddate", NpgsqlTypes.NpgsqlDbType.Date);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = createdDate;
arParams[12] = new NpgsqlParameter("sentdate", NpgsqlTypes.NpgsqlDbType.Date);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = sentDate;
}
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(GetConnectionString(),
CommandType.StoredProcedure,
"mp_privatemessages_insert(:messageid,:fromuser,:priorityid,:subject,:body,:tocsvlist,:cccsvlist,:bcccsvlist,:tocsvlabels,:cccsvlabels,:bcccsvlabels,:createddate,:sentdate)",
arParams);
return rowsAffected;
}
public static bool UpdatePrivateMessage(
Guid messageId,
Guid fromUser,
Guid priorityId,
string subject,
string body,
string toCsvList,
string ccCsvList,
string bccCsvList,
string toCsvLabels,
string ccCsvLabels,
string bccCsvLabels,
DateTime createdDate,
DateTime sentDate)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[13];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_privatemessages_update(:messageid,:fromuser,:priorityid,:subject,:body,:tocsvlist,:cccsvlist,:bcccsvlist,:tocsvlabels,:cccsvlabels,:bcccsvlabels,:createddate,:sentdate)");
arParams[0].Value = messageId.ToString();
arParams[1].Value = fromUser.ToString();
arParams[2].Value = priorityId.ToString();
arParams[3].Value = subject;
arParams[4].Value = body;
arParams[5].Value = toCsvList;
arParams[6].Value = ccCsvList;
arParams[7].Value = bccCsvList;
arParams[8].Value = toCsvLabels;
arParams[9].Value = ccCsvLabels;
arParams[10].Value = bccCsvLabels;
arParams[11].Value = createdDate;
arParams[12].Value = sentDate;
}
else
{
arParams[0] = new NpgsqlParameter("messageid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = messageId.ToString();
arParams[1] = new NpgsqlParameter("fromuser", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = fromUser.ToString();
arParams[2] = new NpgsqlParameter("priorityid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = priorityId.ToString();
arParams[3] = new NpgsqlParameter("subject", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = subject;
arParams[4] = new NpgsqlParameter("body", NpgsqlTypes.NpgsqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = body;
arParams[5] = new NpgsqlParameter("tocsvlist", NpgsqlTypes.NpgsqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = toCsvList;
arParams[6] = new NpgsqlParameter("cccsvlist", NpgsqlTypes.NpgsqlDbType.Text);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = ccCsvList;
arParams[7] = new NpgsqlParameter("bcccsvlist", NpgsqlTypes.NpgsqlDbType.Text);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = bccCsvList;
arParams[8] = new NpgsqlParameter("tocsvlabels", NpgsqlTypes.NpgsqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = toCsvLabels;
arParams[9] = new NpgsqlParameter("cccsvlabels", NpgsqlTypes.NpgsqlDbType.Text);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = ccCsvLabels;
arParams[10] = new NpgsqlParameter("bcccsvlabels", NpgsqlTypes.NpgsqlDbType.Text);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = bccCsvLabels;
arParams[11] = new NpgsqlParameter("createddate", NpgsqlTypes.NpgsqlDbType.Date);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = createdDate;
arParams[12] = new NpgsqlParameter("sentdate", NpgsqlTypes.NpgsqlDbType.Date);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = sentDate;
}
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(GetConnectionString(),
CommandType.StoredProcedure,
"mp_privatemessages_update(:messageid,:fromuser,:priorityid,:subject,:body,:tocsvlist,:cccsvlist,:bcccsvlist,:tocsvlabels,:cccsvlabels,:bcccsvlabels,:createddate,:sentdate)",
arParams);
return (rowsAffected > -1);
}
public static bool DeletePrivateMessage(
Guid messageId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_privatemessages_delete(:messageid)");
arParams[0].Value = messageId.ToString();
}
else
{
arParams[0] = new NpgsqlParameter("messageid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = messageId.ToString();
}
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(GetConnectionString(),
CommandType.StoredProcedure,
"mp_privatemessages_delete(:messageid)",
arParams);
return (rowsAffected > -1);
}
public static IDataReader GetPrivateMessage(
Guid messageId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
if (ConfigurationManager.AppSettings["CachePostgreSQLParameters"].ToLower() == "true")
{
arParams = NpgsqlHelperParameterCache.GetParameterSet(GetConnectionString(),
"mp_privatemessages_selectone(:messageid)");
arParams[0].Value = messageId.ToString();
}
else
{
arParams[0] = new NpgsqlParameter("messageid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = messageId.ToString();
}
return NpgsqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.StoredProcedure,
"mp_privatemessages_selectone(:messageid)",
arParams);
}
}
}
|