/// <summary>
/// Get the RSS cache.
/// </summary>
/// <param name="catalogCode">Catalog code representing the RSS</param>
public static FoeServerRssFeed GetRssFeedCache(string catalogCode)
{
FoeServerRssFeed feed = null;
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from RssFeeds where Code=@code";
cmd.Parameters.Add("@code", SqlDbType.NVarChar, 10);
cmd.Prepare();
cmd.Parameters["@code"].Value = catalogCode;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
feed = new FoeServerRssFeed();
feed.Id = (int)FoeServerDb.GetInt32(reader, "Id");
feed.Rss = FoeServerDb.GetString(reader, "Rss");
feed.Code = FoeServerDb.GetString(reader, "Code");
feed.DtLastUpdated = (DateTime)FoeServerDb.GetDateTime(reader, "DtLastUpdated");
}
reader.Close();
conn.Close();
return(feed);
}
public static void Set(string name, string value)
{
SqlConnection conn = FoeServerDb.OpenDb();
// Set the server registry
SqlCommand cmd = conn.CreateCommand();
// check if the registry name exists
string regValue = Get(name);
if (regValue != null)
{
// Registry exists, we'll update the value
cmd.CommandText = "update Registry set Value=@value where Name=@name";
}
else
{
// Registry doesn't exist, we'll create one
cmd.CommandText = "insert into Registry (Name, Value) values (@name, @value)";
}
cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@value", System.Data.SqlDbType.NVarChar, 512);
cmd.Prepare();
cmd.Parameters["@name"].Value = name;
cmd.Parameters["@value"].Value = value;
cmd.ExecuteNonQuery();
conn.Close();
}
private static void SaveCatalogRequest(string requestId, string userEmail, string processorEmail)
{
// Connect to DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
// Prepare and run query
// Default status to 'P' (Pending)
cmd.CommandText =
"insert into Requests (RequestType, UserEmail, RequestId, ProcessorEmail, DtReceived, Status) " +
"values (@requestType, @userEmail, @requestId, @processorEmail, @dtReceived, 'P')";
cmd.Parameters.Add("@requestType", System.Data.SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@requestId", System.Data.SqlDbType.NVarChar, 128);
cmd.Parameters.Add("@processorEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@dtReceived", System.Data.SqlDbType.DateTime);
cmd.Prepare();
cmd.Parameters["@requestType"].Value = FoeServerRequest.RequestTypeToString(RequestType.Catalog);
cmd.Parameters["@userEmail"].Value = userEmail;
cmd.Parameters["@requestId"].Value = requestId;
cmd.Parameters["@processorEmail"].Value = processorEmail;
cmd.Parameters["@dtReceived"].Value = DateTime.Now;
cmd.ExecuteNonQuery();
conn.Close();
}
private static void SaveContentRequest(string requestId, string userEmail, string[] catalogs, string processorEmail)
{
// Connect to DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
// Prepare and run query
// Default status to 'P' (Pending)
cmd.CommandText =
"insert into Requests (RequestType, UserEmail, RequestId, ProcessorEmail, RequestMessage, DtReceived, Status) " +
"values (@requestType, @userEmail, @requestId, @processorEmail, @requestMessage, @dtReceived, 'P')";
cmd.Parameters.Add("@requestType", System.Data.SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@requestId", System.Data.SqlDbType.NVarChar, 128);
cmd.Parameters.Add("@processorEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@requestMessage", System.Data.SqlDbType.NVarChar, -1);
cmd.Parameters.Add("@dtReceived", System.Data.SqlDbType.DateTime);
cmd.Prepare();
//message is {abc,def,ghi,}, so the last element of catalogs is ""
for (int i = 0; i < catalogs.Length - 1; i++)
{
cmd.Parameters["@requestType"].Value = FoeServerRequest.RequestTypeToString(RequestType.Content);
cmd.Parameters["@userEmail"].Value = userEmail;
cmd.Parameters["@requestId"].Value = requestId;
cmd.Parameters["@processorEmail"].Value = processorEmail;
cmd.Parameters["@requestMessage"].Value = catalogs[i];
cmd.Parameters["@dtReceived"].Value = DateTime.Now;
cmd.ExecuteNonQuery();
}
conn.Close();
}
/// <summary>
/// Get catalog item that matches the catalog code.
/// </summary>
/// <param name="code">Catalog code</param>
/// <returns>CatalogItem object that contains the details of a catalog item. If no matching catalog code is found, null will be returned.</returns>
public static CatalogItem GetCatalogItem(string code)
{
CatalogItem item = null;
// get catalog items from DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from CatalogRss where Code=@code";
cmd.Parameters.Add("@code", SqlDbType.NVarChar, 10);
cmd.Prepare();
cmd.Parameters["@code"].Value = code;
// get item
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
// populate the data
item = new CatalogItem();
item.Code = FoeServerDb.GetString(reader, "Code");
item.ContentType = FoeServerDb.GetString(reader, "ContentType");
item.Description = FoeServerDb.GetString(reader, "Description");
item.Location = FoeServerDb.GetString(reader, "Location");
}
reader.Close();
conn.Close();
return(item);
}
public static void UpdateCatalogItem(CatalogItem item)
{
// get catalog items from DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
"update CatalogRss set " +
"ContentType=@contentType, " +
"Description=@description, " +
"Location=@location " +
"where Code=@code";
cmd.Parameters.Add("@code", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@contentType", SqlDbType.NVarChar, 32);
cmd.Parameters.Add("@description", SqlDbType.NVarChar, 512);
cmd.Parameters.Add("@location", SqlDbType.NVarChar, 512);
cmd.Prepare();
// populate data
cmd.Parameters["@code"].Value = item.Code;
cmd.Parameters["@contentType"].Value = item.ContentType;
cmd.Parameters["@description"].Value = item.Description;
cmd.Parameters["@location"].Value = item.Location;
// get item
cmd.ExecuteNonQuery();
conn.Close();
}
/// <summary>
/// Get all catalog items available.
/// </summary>
/// <returns>A list of catalog items that are available.</returns>
public static List <CatalogItem> GetCatalog()
{
List <CatalogItem> items = new List <CatalogItem>();
// get catalog items from DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from CatalogRss order by Name";
// get items
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// create a new item and populate the data
CatalogItem item = new CatalogItem();
item.Code = FoeServerDb.GetString(reader, "Code");
item.ContentType = FoeServerDb.GetString(reader, "ContentType");
item.Description = FoeServerDb.GetString(reader, "Description");
item.Location = FoeServerDb.GetString(reader, "Location");
// add to list
items.Add(item);
}
reader.Close();
conn.Close();
return(items);
}
public static void AddCatalogItem(CatalogItem item)
{
// get catalog items from DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
"insert into CatalogRss (Code, ContentType, Description, Location) " +
"values (@code, @contentType, @description, @location)";
cmd.Parameters.Add("@code", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@contentType", SqlDbType.NVarChar, 32);
cmd.Parameters.Add("@description", SqlDbType.NVarChar, 512);
cmd.Parameters.Add("@location", SqlDbType.NVarChar, 512);
cmd.Prepare();
// populate data
cmd.Parameters["@code"].Value = item.Code;
cmd.Parameters["@contentType"].Value = item.ContentType;
cmd.Parameters["@description"].Value = item.Description;
cmd.Parameters["@location"].Value = item.Location;
// get item
cmd.ExecuteNonQuery();
conn.Close();
}
public static void UpdateUser(FoeUser user)
{
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
"update Users " +
"set " +
"Email=@email, " +
"UserId=@userId, " +
"DtCreated=@dtCreated, " +
"ProcessorEmail=@processorEmail " +
"where Id=@id";
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@userId", SqlDbType.NVarChar, 128);
cmd.Parameters.Add("@dtCreated", SqlDbType.DateTime);
cmd.Parameters.Add("@processorEmail", SqlDbType.NVarChar, 256);
cmd.Prepare();
// add user to db
cmd.Parameters["@id"].Value = user.Id;
cmd.Parameters["@email"].Value = user.Email;
cmd.Parameters["@userId"].Value = user.UserId;
cmd.Parameters["@dtCreated"].Value = user.DtCreated;
cmd.Parameters["@processorEmail"].Value = user.ProcessorEmail;
// execute command
cmd.ExecuteNonQuery();
conn.Close();
}
/// <summary>
/// Add user to db
/// </summary>
/// <param name="user">The user object containing the user info.</param>
public static void AddUser(FoeUser user)
{
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
"insert into Users (Email, UserId, DtCreated, ProcessorEmail) " +
"values (@email, @userId, @dtCreated, @processorEmail)";
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@userId", SqlDbType.NVarChar, 128);
cmd.Parameters.Add("@dtCreated", SqlDbType.DateTime);
cmd.Parameters.Add("@processorEmail", SqlDbType.NVarChar, 256);
cmd.Prepare();
// add user to db
cmd.Parameters["@email"].Value = user.Email;
cmd.Parameters["@userId"].Value = user.UserId;
cmd.Parameters["@dtCreated"].Value = user.DtCreated;
cmd.Parameters["@processorEmail"].Value = user.ProcessorEmail;
// execute command
cmd.ExecuteNonQuery();
conn.Close();
}
public static void DeleteCatalogItem(string code)
{
// get catalog items from DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from CatalogRss where Code=@code";
cmd.Parameters.Add("@code", SqlDbType.NVarChar, 10);
cmd.Prepare();
cmd.Parameters["@code"].Value = code;
// delete item
cmd.ExecuteNonQuery();
conn.Close();
}
private static void SaveFeedRequest(string requestId, string userEmail, string[] array, string processorEmail)
{
// Connect to DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
string feedName = array[0];
string location = array[1];
// insert the feed to CatalogRss
cmd.CommandText =
"insert into CatalogRss (Code,Name,ContentType,Description,Location) " +
"values (@code,@name,'RSS',@description,@location)";
cmd.Parameters.Add("@code", System.Data.SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 32);
cmd.Parameters.Add("@description", System.Data.SqlDbType.NVarChar, 512);
cmd.Parameters.Add("@location", System.Data.SqlDbType.NVarChar, 512);
cmd.Prepare();
cmd.Parameters["@code"].Value = feedName;
cmd.Parameters["@name"].Value = feedName;
cmd.Parameters["@description"].Value = feedName;
cmd.Parameters["@location"].Value = location;
cmd.ExecuteNonQuery();
// Prepare and run query
// Default status to 'P' (Pending)
cmd.CommandText =
"insert into Requests (RequestType, UserEmail, RequestId, ProcessorEmail, DtReceived, Status) " +
"values (@requestType, @userEmail, @requestId, @processorEmail, @dtReceived, 'P')";
cmd.Parameters.Add("@requestType", System.Data.SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@requestId", System.Data.SqlDbType.NVarChar, 128);
cmd.Parameters.Add("@processorEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@dtReceived", System.Data.SqlDbType.DateTime);
cmd.Prepare();
cmd.Parameters["@requestType"].Value = FoeServerRequest.RequestTypeToString(RequestType.Feed);
cmd.Parameters["@userEmail"].Value = userEmail;
cmd.Parameters["@requestId"].Value = requestId;
cmd.Parameters["@processorEmail"].Value = processorEmail;
cmd.Parameters["@dtReceived"].Value = DateTime.Now;
cmd.ExecuteNonQuery();
conn.Close();
}
public static void Update(string userEmail, string catalogCode, string requestId)
{
SqlConnection conn = FoeServerDb.OpenDb();
// Update existing subsubscription by the same user for the same catalog code
SqlCommand cmd = conn.CreateCommand(); // Update command
cmd.CommandText =
"update AutoSubscriptions set dtLastUpdated=@dtLastUpdated " +
"where UserEmail=@userEmail and CatalogCode=@catalogCode";
cmd.Parameters.Add("@dtLastUpdated", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@catalogCode", System.Data.SqlDbType.NVarChar, 10);
cmd.Prepare();
cmd.Parameters["@dtLastUpdated"].Value = DateTime.Now;
cmd.Parameters["@userEmail"].Value = userEmail;
cmd.Parameters["@catalogCode"].Value = catalogCode;
cmd.ExecuteNonQuery();
}
/// <summary>
/// Update a RSS cache.
/// </summary>
/// <param name="catalogCode">The catalog code representing the RSS feed.</param>
/// <param name="rss">The actual RSS to be cached.</param>
public static void UpdateRssCache(string catalogCode, string rss)
{
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "update RssFeeds set Rss=@rss,DtLastUpdated=@dtLastUpdated where Code=@code";
cmd.Parameters.Add("@code", SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@rss", SqlDbType.NVarChar, -1);
cmd.Parameters.Add("@dtLastUpdated", SqlDbType.DateTime);
cmd.Prepare();
cmd.Parameters["@code"].Value = catalogCode;
cmd.Parameters["@rss"].Value = rss;
cmd.Parameters["@dtLastUpdated"].Value = DateTime.Now;
cmd.ExecuteNonQuery();
conn.Close();
}
private void LoadRequests()
{
// Close the connection if for whatever reason it's not null
if (_conn != null)
{
_conn.Close();
}
// Get all pending requests from the DB
_conn = FoeServerDb.OpenDb();
SqlCommand cmd = _conn.CreateCommand();
cmd.CommandText = "select * from Requests where RequestType=@requestType and Status='P' and ProcessorEmail=@processorEmail";
cmd.Parameters.Add("@processorEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@requestType", System.Data.SqlDbType.NVarChar, 10);
cmd.Prepare();
cmd.Parameters["@processorEmail"].Value = _processorEmail;
cmd.Parameters["@requestType"].Value = RequestTypeToString(_requestType);
cmd.Prepare();
_reader = cmd.ExecuteReader();
}
/// <summary>
/// Get user information in an FoeUser object.
/// </summary>
/// <param name="userEmail">user's email address</param>
/// <returns>A FoeUser object populated with user info. If user is not found, then null is returned.</returns>
public static FoeUser GetUser(string userEmail)
{
FoeUser user = null;
// open connection to FOE DB
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
// get user
string sql = "select * from Users where Email=@email";
cmd.CommandText = sql;
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 256);
cmd.Prepare();
cmd.Parameters["@email"].Value = userEmail.Trim().ToLower();
SqlDataReader reader = cmd.ExecuteReader();
// see if user exists
if (reader.HasRows)
{
reader.Read();
// create a new user object;
user = new FoeUser();
// populate user information
user.Id = FoeServerDb.GetInt32(reader, "Id");
user.Email = FoeServerDb.GetString(reader, "Email");
user.UserId = FoeServerDb.GetString(reader, "UserId");
user.DtCreated = FoeServerDb.GetDateTime(reader, "DtCreated");
user.ProcessorEmail = FoeServerDb.GetString(reader, "ProcessorEmail");
}
reader.Close();
conn.Close();
return(user);
}
public static string Get(string name)
{
string value = null;
SqlConnection conn = FoeServerDb.OpenDb();
// Get registry value
SqlCommand checkCmd = conn.CreateCommand();
checkCmd.CommandText = "select Value from Registry where Name=@name";
checkCmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 50);
checkCmd.Prepare();
checkCmd.Parameters["@name"].Value = name;
SqlDataReader reader = checkCmd.ExecuteReader();
if (reader.Read())
{
value = reader.GetString(reader.GetOrdinal("Value"));
}
reader.Close();
conn.Close();
return(value);
}
public static void Add(string processName, LogType type, string detail)
{
// Check if DB write connection is alive
if ((_writeConn == null) || (_writeConn.State != System.Data.ConnectionState.Open))
{
_writeConn = FoeServerDb.OpenDb();
}
// Add log to DB
SqlCommand cmd = _writeConn.CreateCommand();
cmd.CommandText = "insert into Logs (DtCreated, Process, Type, Detail) values (@dtCreated, @process, @type, @detail)";
cmd.Parameters.Add("@dtCreated", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@process", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@type", System.Data.SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@detail", System.Data.SqlDbType.NVarChar, -1);
cmd.Prepare();
cmd.Parameters["@dtCreated"].Value = DateTime.Now;
cmd.Parameters["@process"].Value = processName;
cmd.Parameters["@type"].Value = LogTypeToString(type);
cmd.Parameters["@detail"].Value = detail;
cmd.ExecuteNonQuery();
}
/// <summary>
/// Get the RSS cache.
/// </summary>
/// <param name="catalogCode">Catalog code representing the RSS</param>
public static string GetRssCache(string catalogCode)
{
string rss = null;
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select Rss from RssFeeds where Code=@code";
cmd.Parameters.Add("@code", SqlDbType.NVarChar, 10);
cmd.Prepare();
cmd.Parameters["@code"].Value = catalogCode;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
rss = FoeServerDb.GetString(reader, "Rss");
}
reader.Close();
conn.Close();
return(rss);
}
public void UpdateRequestStatus(FoeRequester req, string status)
{
try
{
// Update the status of a request
SqlConnection conn = FoeServerDb.OpenDb();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "update Requests set Status=@status, DtProcessed=@dtProcessed where Id=@id";
cmd.Parameters.Add("@status", System.Data.SqlDbType.NChar, 1);
cmd.Parameters.Add("@dtProcessed", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int);
cmd.Prepare();
cmd.Parameters["@status"].Value = status;
cmd.Parameters["@dtProcessed"].Value = DateTime.Now;
cmd.Parameters["@id"].Value = req.Id;
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception except)
{
throw except;
}
}
public static void Add(string userEmail, string catalogCode, string requestId)
{
SqlConnection conn = FoeServerDb.OpenDb();
// First delete any existing subsubscription by the same user for the same catalog code
SqlCommand delCmd = conn.CreateCommand(); // Deletion command
delCmd.CommandText = "delete from AutoSubscriptions where UserEmail=@userEmail and CatalogCode=@catalogCode";
delCmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 256);
delCmd.Parameters.Add("@catalogCode", System.Data.SqlDbType.NVarChar, 10);
delCmd.Prepare();
delCmd.Parameters["@userEmail"].Value = userEmail;
delCmd.Parameters["@catalogCode"].Value = catalogCode;
delCmd.ExecuteNonQuery();
DateTime now = DateTime.Now;
// Add the new subscription
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
"insert into AutoSubscriptions (UserEmail, CatalogCode, RequestId, DtSubscribed, DtLastUpdated) " +
"values (@userEmail, @catalogCode, @requestId, @dtSubscribed, @dtLastUpdated)";
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 256);
cmd.Parameters.Add("@catalogCode", System.Data.SqlDbType.NVarChar, 10);
cmd.Parameters.Add("@requestId", System.Data.SqlDbType.NVarChar, 128);
cmd.Parameters.Add("@dtSubscribed", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@dtLastUpdated", System.Data.SqlDbType.DateTime);
cmd.Prepare();
cmd.Parameters["@userEmail"].Value = userEmail;
cmd.Parameters["@catalogCode"].Value = catalogCode;
cmd.Parameters["@requestId"].Value = requestId;
cmd.Parameters["@dtSubscribed"].Value = now;
cmd.Parameters["@dtLastUpdated"].Value = now;
cmd.ExecuteNonQuery();
}