    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using ASPNET.StarterKit.Portal;

    namespace ASPNET.StarterKit.Portal {

        //*********************************************************************
        //
        // LinkDB Class
        //
        // Class that encapsulates all data logic necessary to add/query/delete
        // links within the Portal database.
        //
        //*********************************************************************

        public class LinkDB {

            //*********************************************************************
            //
            // GetLinks Method
            //
            // The GetLinks method returns a SqlDataReader containing all of the
            // links for a specific portal module from the announcements
            // database.
            //
            // Other relevant sources:
            //     + GetLinks Stored Procedure
            //
            //*********************************************************************

            public SqlDataReader GetLinks(int moduleId) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_GetLinks", 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;
            }

            //*********************************************************************
            //
            // GetSingleLink Method
            //
            // The GetSingleLink method returns a SqlDataReader containing details
            // about a specific link from the Links database table.
            //
            // Other relevant sources:
            //     + GetSingleLink Stored Procedure
            //
            //*********************************************************************

            public SqlDataReader GetSingleLink(int itemId) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_GetSingleLink", 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;
            }

            //*********************************************************************
            //
            // DeleteLink Method
            //
            // The DeleteLink method deletes a specified link from
            // the Links database table.
            //
            // Other relevant sources:
            //     + DeleteLink Stored Procedure
            //
            //*********************************************************************

            public void DeleteLink(int itemID) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_DeleteLink", 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);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }

            //*********************************************************************
            //
            // AddLink Method
            //
            // The AddLink method adds a new link within the
            // links database table, and returns ItemID value as a result.
            //
            // Other relevant sources:
            //     + AddLink Stored Procedure
            //
            //*********************************************************************

            public int AddLink(int moduleId, int itemId, String userName, String title, String url, String mobileUrl, int viewOrder, String description) {

                if (userName.Length < 1) {
                    userName = "unknown";
                }

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_AddLink", 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 parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 100);
                parameterDescription.Value = description;
                myCommand.Parameters.Add(parameterDescription);

                SqlParameter parameterUrl = new SqlParameter("@Url", SqlDbType.NVarChar, 100);
                parameterUrl.Value = url;
                myCommand.Parameters.Add(parameterUrl);

                SqlParameter parameterMobileUrl = new SqlParameter("@MobileUrl", SqlDbType.NVarChar, 100);
                parameterMobileUrl.Value = mobileUrl;
                myCommand.Parameters.Add(parameterMobileUrl);

                SqlParameter parameterViewOrder = new SqlParameter("@ViewOrder", SqlDbType.Int, 4);
                parameterViewOrder.Value = viewOrder;
                myCommand.Parameters.Add(parameterViewOrder);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();

                return (int)parameterItemID.Value;
            }

            //*********************************************************************
            //
            // UpdateLink Method
            //
            // The UpdateLink method updates a specified link within
            // the Links database table.
            //
            // Other relevant sources:
            //     + UpdateLink Stored Procedure
            //
            //*********************************************************************

            public void UpdateLink(int moduleId, int itemId, String userName, String title, String url, String mobileUrl, int viewOrder, String description) {

                if (userName.Length < 1) {
                    userName = "unknown";
                }

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_UpdateLink", 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 parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 100);
                parameterDescription.Value = description;
                myCommand.Parameters.Add(parameterDescription);

                SqlParameter parameterUrl = new SqlParameter("@Url", SqlDbType.NVarChar, 100);
                parameterUrl.Value = url;
                myCommand.Parameters.Add(parameterUrl);

                SqlParameter parameterMobileUrl = new SqlParameter("@MobileUrl", SqlDbType.NVarChar, 100);
                parameterMobileUrl.Value = mobileUrl;
                myCommand.Parameters.Add(parameterMobileUrl);

                SqlParameter parameterViewOrder = new SqlParameter("@ViewOrder", SqlDbType.Int, 4);
                parameterViewOrder.Value = viewOrder;
                myCommand.Parameters.Add(parameterViewOrder);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }
    }