using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using ASPNetPortal; namespace ASPNetPortal { //********************************************************************* // // EventDB Class // // Class that encapsulates all data logic necessary to add/query/delete // events within the Portal database. // //********************************************************************* public class EventsDB { //********************************************************************* // // GetEvents Method // // The GetEvents method returns a DataSet containing all of the // events for a specific portal module from the events // database. // // NOTE: A DataSet is returned from this method to allow this method to support // both desktop and mobile Web UI. // // Other relevant sources: // + GetEvents Stored Procedure // //********************************************************************* public DataSet GetEvents(int moduleId) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlDataAdapter myCommand = new SqlDataAdapter("GetEvents", myConnection); // Mark the Command as a SPROC myCommand.SelectCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4); parameterModuleId.Value = moduleId; myCommand.SelectCommand.Parameters.Add(parameterModuleId); // Create and Fill the DataSet DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet); // Return the DataSet return myDataSet; } //********************************************************************* // // GetSingleEvent Method // // The GetSingleEvent method returns a SqlDataReader containing details // about a specific event from the events database. // // Other relevant sources: // + GetSingleEvent Stored Procedure // //********************************************************************* public SqlDataReader GetSingleEvent(int itemId) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("GetSingleEvent", 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; } //********************************************************************* // // DeleteEvent Method // // The DeleteEvent method deletes a specified event from // the events database. // // Other relevant sources: // + DeleteEvent Stored Procedure // //********************************************************************* public void DeleteEvent(int itemID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("DeleteEvent", 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); // Open the database connection and execute SQL Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } //********************************************************************* // // AddEvent Method // // The AddEvent method adds a new event within the Events database table, // and returns the ItemID value as a result. // // Other relevant sources: // + AddEvent Stored Procedure // //********************************************************************* public int AddEvent(int moduleId, int itemId, String userName, String title, DateTime expireDate, String description, String wherewhen) { if (userName.Length < 1) { userName = "unknown"; } // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("AddEvent", 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 parameterModuleID = new SqlParameter("@ModuleID", SqlDbType.Int, 4); parameterModuleID.Value = moduleId; myCommand.Parameters.Add(parameterModuleID); SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 100); parameterUserName.Value = userName; myCommand.Parameters.Add(parameterUserName); SqlParameter parameterTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 100); parameterTitle.Value = title; myCommand.Parameters.Add(parameterTitle); SqlParameter parameterWhereWhen = new SqlParameter("@WhereWhen", SqlDbType.NVarChar, 100); parameterWhereWhen.Value = wherewhen; myCommand.Parameters.Add(parameterWhereWhen); SqlParameter parameterExpireDate = new SqlParameter("@ExpireDate", SqlDbType.DateTime, 8); parameterExpireDate.Value = expireDate; myCommand.Parameters.Add(parameterExpireDate); SqlParameter parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 2000); parameterDescription.Value = description; myCommand.Parameters.Add(parameterDescription); // Open the database connection and execute SQL Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); // Return the new Event ItemID return (int)parameterItemID.Value; } //********************************************************************* // // UpdateEvent Method // // The UpdateEvent method updates the specified event within // the Events database table. // // Other relevant sources: // + UpdateEvent Stored Procedure // //********************************************************************* public void UpdateEvent(int moduleId, int itemId, String userName, String title, DateTime expireDate, String description, String wherewhen) { if (userName.Length < 1) { userName = "unknown"; } // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("UpdateEvent", 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); SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 100); parameterUserName.Value = userName; myCommand.Parameters.Add(parameterUserName); SqlParameter parameterTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 100); parameterTitle.Value = title; myCommand.Parameters.Add(parameterTitle); SqlParameter parameterWhereWhen = new SqlParameter("@WhereWhen", SqlDbType.NVarChar, 100); parameterWhereWhen.Value = wherewhen; myCommand.Parameters.Add(parameterWhereWhen); SqlParameter parameterExpireDate = new SqlParameter("@ExpireDate", SqlDbType.DateTime, 8); parameterExpireDate.Value = expireDate; myCommand.Parameters.Add(parameterExpireDate); SqlParameter parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 2000); parameterDescription.Value = description; myCommand.Parameters.Add(parameterDescription); myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } } }