using System;
using System.Data;
using System.Data.Common;
using SiberTek.Xenta.Data.Entities;
using SiberTek.Xenta.Data.Entities.Collections;
using SiberTek.Xenta.Data.Utils;
using SiberTek.Xenta.Enums;
namespace SiberTek.Xenta.Data.Providers
{
public class PublicationDataProvider : DataProviderBase, IPublicationDataProvider
{
#region Methods
public bool InsertPublication(int languageID, int authorID, PublicationType type, string title, string description, string text, bool isActive, DateTime createdOn, DateTime? publishedOn, DateTime updatedOn, out int publicationID)
{
bool res = false;
publicationID = 0;
using(DbCommand cmd = DataBase.GetStoredProcCommand("Publications_Insert"))
{
DbCommandHelper.AddInt32(DataBase, cmd, "LanguageID", languageID);
DbCommandHelper.AddInt32(DataBase, cmd, "AuthorID", authorID);
DbCommandHelper.AddInt32(DataBase, cmd, "Type", (int)type);
DbCommandHelper.AddString(DataBase, cmd, "Title", title);
DbCommandHelper.AddString(DataBase, cmd, "Description", description);
DbCommandHelper.AddString(DataBase, cmd, "Text", text);
DbCommandHelper.AddBoolean(DataBase, cmd, "IsActive", isActive);
DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
if(publishedOn.HasValue)
{
DbCommandHelper.AddDateTime(DataBase, cmd, "PublishedOn", publishedOn.Value);
}
DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);
DbCommandHelper.AddOutInt32(DataBase, cmd, "PublicationID");
res = DataBase.ExecuteNonQuery(cmd) > 0;
if(res)
{
publicationID = DbCommandHelper.GeInt32(DataBase, cmd, "@PublicationID");
}
}
return res;
}
public PublicationData GetPublication(int publicationID)
{
PublicationData data = null;
if(publicationID > 0)
{
using(DbCommand cmd = DataBase.GetStoredProcCommand("Publications_Select"))
{
DbCommandHelper.AddInt32(DataBase, cmd, "PublicationID", publicationID);
using(IDataReader reader = DataBase.ExecuteReader(cmd))
{
if(reader.Read())
{
data = ReadData(reader);
}
}
}
}
return data;
}
public PublicationDataCollection GetAllPublications(string searchTerm, int? languageID, int? authorID, PublicationType? type, int? tagID, DateTime? createdOnStart, DateTime? createdOnEnd, DateTime? publishedOnStart, DateTime? publishedOnEnd, bool showHidden, int startIndex, int count, out int totalCount)
{
PublicationDataCollection dataCollection = new PublicationDataCollection();
using(DbCommand cmd = DataBase.GetStoredProcCommand("Publications_SelectAll"))
{
DbCommandHelper.AddString(DataBase, cmd, "SearchTerm", searchTerm);
if(languageID.HasValue)
{
DbCommandHelper.AddInt32(DataBase, cmd, "LanguageID", languageID.Value);
}
if(authorID.HasValue)
{
DbCommandHelper.AddInt32(DataBase, cmd, "AuthorID", authorID.Value);
}
if(type.HasValue)
{
DbCommandHelper.AddInt32(DataBase, cmd, "Type", (int)type.Value);
}
if(tagID.HasValue)
{
DbCommandHelper.AddInt32(DataBase, cmd, "TagID", tagID.Value);
}
if(createdOnStart.HasValue)
{
DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOnStart", createdOnStart.Value);
}
if(createdOnEnd.HasValue)
{
DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOnEnd", createdOnEnd.Value);
}
if(publishedOnStart.HasValue)
{
DbCommandHelper.AddDateTime(DataBase, cmd, "PublishedOnStart", publishedOnStart.Value);
}
if(publishedOnEnd.HasValue)
{
DbCommandHelper.AddDateTime(DataBase, cmd, "PublishedOnEnd", publishedOnEnd.Value);
}
DbCommandHelper.AddBoolean(DataBase, cmd, "ShowHidden", showHidden);
DbCommandHelper.AddInt32(DataBase, cmd, "StartIndex", startIndex);
DbCommandHelper.AddInt32(DataBase, cmd, "Count", count);
DbCommandHelper.AddOutInt32(DataBase, cmd, "TotalCount");
using(IDataReader reader = DataBase.ExecuteReader(cmd))
{
while(reader.Read())
{
dataCollection.Add(ReadData(reader));
}
}
totalCount = DbCommandHelper.GeInt32(DataBase, cmd, "@TotalCount");
}
return dataCollection;
}
public bool UpdatePublication(int publicationID, int languageID, int authorID, PublicationType type, string title, string description, string text, bool isActive, DateTime createdOn, DateTime? publishedOn, DateTime updatedOn)
{
bool res = false;
if(publicationID > 0)
{
using(DbCommand cmd = DataBase.GetStoredProcCommand("Publications_Update"))
{
DbCommandHelper.AddInt32(DataBase, cmd, "PublicationID", publicationID);
DbCommandHelper.AddInt32(DataBase, cmd, "LanguageID", languageID);
DbCommandHelper.AddInt32(DataBase, cmd, "AuthorID", authorID);
DbCommandHelper.AddInt32(DataBase, cmd, "Type", (int)type);
DbCommandHelper.AddString(DataBase, cmd, "Title", title);
DbCommandHelper.AddString(DataBase, cmd, "Description", description);
DbCommandHelper.AddString(DataBase, cmd, "Text", text);
DbCommandHelper.AddBoolean(DataBase, cmd, "IsActive", isActive);
DbCommandHelper.AddDateTime(DataBase, cmd, "CreatedOn", createdOn);
if(publishedOn.HasValue)
{
DbCommandHelper.AddDateTime(DataBase, cmd, "PublishedOn", publishedOn.Value);
}
DbCommandHelper.AddDateTime(DataBase, cmd, "UpdatedOn", updatedOn);
res = DataBase.ExecuteNonQuery(cmd) > 0;
}
}
return res;
}
public bool DeletePublication(int publicationID)
{
bool res = false;
if(publicationID > 0)
{
using(DbCommand cmd = DataBase.GetStoredProcCommand("Publications_Delete"))
{
DbCommandHelper.AddInt32(DataBase, cmd, "PublicationID", publicationID);
res = DataBase.ExecuteNonQuery(cmd) > 0;
}
}
return res;
}
#endregion
#region Utilities
private static PublicationData ReadData(IDataReader reader)
{
PublicationData data = new PublicationData();
data.PublicationID = DataReaderHelper.ReadInt32(reader, "PublicationID");
data.LanguageID = DataReaderHelper.ReadInt32(reader, "LanguageID");
data.AuthorID = DataReaderHelper.ReadInt32(reader, "AuthorID");
data.Type = (PublicationType)DataReaderHelper.ReadInt32(reader, "Type");
data.Title = DataReaderHelper.ReadString(reader, "Title");
data.Description = DataReaderHelper.ReadString(reader, "Description");
data.Text = DataReaderHelper.ReadString(reader, "Text");
data.IsActive = DataReaderHelper.ReadBoolean(reader, "IsActive");
data.CreatedOn = DataReaderHelper.ReadDateTime(reader, "CreatedOn");
data.PublishedOn = DataReaderHelper.ReadNullableDateTime(reader, "PublishedOn");
data.UpdatedOn = DataReaderHelper.ReadDateTime(reader, "UpdatedOn");
return data;
}
#endregion
}
}