using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Web; using ASPNetPortal; namespace ASPNetPortal { //********************************************************************* // // DiscussionDB Class // // Class that encapsulates all data logic necessary to add/query/delete // discussions within the Portal database. // //********************************************************************* public class DiscussionDB { //******************************************************* // // GetTopLevelMessages Method // // Returns details for all of the messages in the discussion specified by ModuleID. // // Other relevant sources: // + GetTopLevelMessages Stored Procedure // //******************************************************* public SqlDataReader GetTopLevelMessages(int moduleId) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("GetTopLevelMessages", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4); parameterModuleId.Value = moduleId; myCommand.Parameters.Add(parameterModuleId); // Execute the command myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader return result; } //******************************************************* // // GetThreadMessages Method // // Returns details for all of the messages the thread, as identified by the Parent id string. // // Other relevant sources: // + GetThreadMessages Stored Procedure // //******************************************************* public SqlDataReader GetThreadMessages(String parent) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("GetThreadMessages", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterParent = new SqlParameter("@Parent", SqlDbType.NVarChar, 750); parameterParent.Value = parent; myCommand.Parameters.Add(parameterParent); // Execute the command myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader return result; } //******************************************************* // // GetSingleMessage Method // // The GetSingleMessage method returns the details for the message // specified by the itemId parameter. // // Other relevant sources: // + GetSingleMessage Stored Procedure // //******************************************************* public SqlDataReader GetSingleMessage(int itemId) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("GetSingleMessage", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterItemId = new SqlParameter("@ItemId", SqlDbType.Int, 4); parameterItemId.Value = itemId; myCommand.Parameters.Add(parameterItemId); // Execute the command myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader return result; } //********************************************************************* // // AddMessage Method // // The AddMessage method adds a new message within the // Discussions database table, and returns ItemID value as a result. // // Other relevant sources: // + AddMessage Stored Procedure // //********************************************************************* public int AddMessage(int moduleId, int parentId, String userName, String title, String body) { if (userName.Length < 1) { userName = "unknown"; } // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("AddMessage", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterItemID = new SqlParameter("@ItemID", SqlDbType.Int, 4); parameterItemID.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterItemID); SqlParameter parameterTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 100); parameterTitle.Value = title; myCommand.Parameters.Add(parameterTitle); SqlParameter parameterBody = new SqlParameter("@Body", SqlDbType.NVarChar, 3000); parameterBody.Value = body; myCommand.Parameters.Add(parameterBody); SqlParameter parameterParentID = new SqlParameter("@ParentID", SqlDbType.Int, 4); parameterParentID.Value = parentId; myCommand.Parameters.Add(parameterParentID); SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 100); parameterUserName.Value = userName; myCommand.Parameters.Add(parameterUserName); SqlParameter parameterModuleID = new SqlParameter("@ModuleID", SqlDbType.Int, 4); parameterModuleID.Value = moduleId; myCommand.Parameters.Add(parameterModuleID); myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); return (int) parameterItemID.Value; } } }