| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Get control of your email attachments. Connect all your Gmail accounts and in less than 2 minutes, Dokkio will automatically organize your file attachments. You can also connect Dokkio to Drive, Dropbox, and Slack. Sign up for free.

View
 

Lecture - Advanced - The MyDailyMath Equation Editor Part 2

Page history last edited by Dr. Ron Eaglin 8 years ago

 

The MyDailyMath Equation Editor Part 2

 

Prerequisites

 

Lecture - Advanced - The MyDailyMath Equation Editor

 

Case Study - My Daily Math

 

Summary

 

Demonstrates accessing properties on a master page, setting Session variables as a property, getting a return identity from a stored procedure,
using a dictionary to create stored procedure parameters

 

Video 

 

http://online1.daytonastate.edu/player2.php?id=75e33da9b103b7b91dcd8da0abe1354b 

 

Reference Materials

 

The ProblemStatement table - where the text of the question is stored.

 

 CREATE TABLE [dbo].[ProblemStatement](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [StatementTitle] [varchar](200) NULL,
    [StatementText] [nvarchar](max) NULL,
    [AssessmentLevelLow] [int] NULL,
    [AssessmentLevelHigh] [int] NULL,
    [ProblemType] [int] NULL,
    [EnteredByUserID] [int] NULL,
    [EnteredDate] [datetime] NULL)

GO

CREATE PROCEDURE [dbo].[sp_InsertProblemStatement] (
  @StatementTitle VARCHAR(200), 
  @StatementText NVARCHAR(MAX),
  @AssessmentLevelLow INT = 0,
  @AssessmentLevelHigh INT = 0,
  @ProblemType INT = 1,
  @EnteredByUserID INT)
  
AS
BEGIN

INSERT INTO ProblemStatement
 (StatementTitle, StatementText, AssessmentLevelLow, AssessmentLevelHigh, ProblemType, EnteredByUserID, EnteredDate)
VALUES
 (@StatementTitle, @StatementText, @AssessmentLevelLow, @AssessmentLevelHigh, @ProblemType, @EnteredByUserID, GETDATE())

RETURN @@IDENTITY
END
GO
CREATE PROCEDURE [dbo].[sp_UpdateProblemStatement] (
  @id INT,
  @StatementTitle VARCHAR(200), 
  @StatementText NVARCHAR(MAX))
  
AS
BEGIN

UPDATE ProblemStatement
 SET    StatementTitle = @StatementTitle,
        StatementText = @StatementText
 WHERE
        id = @id
END
GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

DatabaseHelper.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace MyDailyMath.Code
{
    public class DatabaseHelper
    {
        public string ConnectionString { get; set; }
        public DatabaseHelper()
        {
        }
        public DatabaseHelper(string connName)
        {
            ConnectionString = getConnectionString(connName);
        }
        public string getConnectionString(string connName)
        {
            string s = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
            System.Configuration.Configuration rootWeb = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(s);
            System.Configuration.ConnectionStringSettings connstr;
            if (rootWeb.ConnectionStrings.ConnectionStrings.Count > 0)
            {
                connstr = rootWeb.ConnectionStrings.ConnectionStrings[connName];
                return connstr.ConnectionString;
            }
            return String.Empty;
        }
        public string executeSqlReturnString(string sql)
        {
            // Should be the first element in the DataTable, row and column
            DataTable dt = executeSqlReturnDataTable(sql);
            if (dt.Rows.Count >= 1)
                return Convert.ToString(dt.Rows[0][0]);
            else
                return String.Empty;
        }
        public int executeSqlReturnInt(string sql)
        {
            string s = executeSqlReturnString(sql);
            try
            {
                return Convert.ToInt32(s);
            }
            catch
            {
                return 0;
            }
        }
        public DataTable executeSqlReturnDataTable(string sql)
        {
            // Create a Connection Object
            SqlConnection connection = new SqlConnection(ConnectionString);
            // Create the Command
            SqlCommand command = new SqlCommand(sql, connection);
            // Create an Adapter to execute the command
            SqlDataAdapter a = new SqlDataAdapter(command);
            // Create a DataTable to get the results
            DataTable dt = new DataTable();
            // The Adapter will then fill the DataTable
            a.Fill(dt);
            return dt;
        }
        public void ExecuteStoredProcedureNonQuery(String sp, Dictionary<string, object> Parameters)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand(sp, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (string Key in Parameters.Keys)
            {
                command.Parameters.AddWithValue(Key, Parameters[Key] == null ? DBNull.Value : Parameters[Key]);
            }
            connection.Open();
            command.ExecuteNonQuery();
            command.Dispose();
            connection.Close();
        }
        public DataSet ExecuteStoredProcedureReturnDataSet(String sp, Dictionary<string, object> Parameters)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand(sp, connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            foreach (string Key in Parameters.Keys)
            {
                command.Parameters.AddWithValue(Key, Parameters[Key] == null ? DBNull.Value : Parameters[Key]);
            }
            connection.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            command.Dispose();
            connection.Close();
            return ds;
        }
        public DataTable ExecuteStoredProcedureReturnDataTable(string sp, Dictionary<string, object> Parameters)
        {
            DataSet ds = ExecuteStoredProcedureReturnDataSet(sp, Parameters);
            if (ds.Tables.Count != 0)
                return ds.Tables[0];
            else
                return new DataTable();
        }
        public int ExecuteStoredProcedureReturnIdentity(string sp,  Dictionary<string, object> Parameters)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand(sp, connection);
            command.CommandType = CommandType.StoredProcedure;            
            foreach (string Key in Parameters.Keys)
            {
                command.Parameters.AddWithValue(Key, Parameters[Key] == null ? DBNull.Value : Parameters[Key]);
            }
            // Create the return parameter
            SqlParameter rParam = new SqlParameter("RETURN", SqlDbType.Int);
            rParam.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(rParam);
            try
            {
                connection.Open();
                command.ExecuteNonQuery();
                int val =  Convert.ToInt32(rParam.Value);
                command.Dispose();
                connection.Close();
                return val;
            }
            finally
            {
                command.Dispose();
                connection.Close();
            }
        }
        public string ExecuteStoredProcedureReturnString(string sp, Dictionary<string, object> Parameters)
        {
            DataTable dt = ExecuteStoredProcedureReturnDataTable(sp, Parameters);
            
            if (dt.Rows.Count >= 1)
                return Convert.ToString(dt.Rows[0][0]);
            else
                return String.Empty;
        }
        public int executeSqlNonQuery(string sql)
        {
            // Create a Connection Object
            SqlConnection connection = new SqlConnection(ConnectionString);
            // Create the Command
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandText = sql;
            connection.Open();
            int i = command.ExecuteNonQuery();
            connection.Close();
            return i;
        }
        public void deleteFromTable(int id, string table)
        {
            string sql = "DELETE FROM " + table + " WHERE id = " + id.ToString();
            executeSqlNonQuery(sql);
        }
        public static string Clean(string s)
        {
            // This cleans any input parameters to prevent injection attack
            return s.Replace("'", "''");
        }
    }
} 

 

MyDailyMathDB.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.SessionState;
using System.Data;
namespace MyDailyMath.Code
{
    public class MyDailyMathDB
    {
        public static int createQuestion(string QuestionTitle, string QuestionText, int UserID)
        {
            // Answers the Primary Key created
            DatabaseHelper dh = new DatabaseHelper(UserManagement.ConnectionName);
            Dictionary<string, object> Parameters = new Dictionary<string, object>();
            Parameters.Add("@StatementTitle", QuestionTitle);
            Parameters.Add("@StatementText", QuestionText);
            Parameters.Add("@EnteredByUserID", UserID);
            return dh.ExecuteStoredProcedureReturnIdentity("sp_InsertProblemStatement", Parameters);
        }
        public static void updateQuestion(int id, string QuestionTitle, string QuestionText)
        {
            // Answers the Primary Key created
            DatabaseHelper dh = new DatabaseHelper(UserManagement.ConnectionName);
            Dictionary<string, object> Parameters = new Dictionary<string, object>();
            Parameters.Add("@id", id);
            Parameters.Add("@StatementTitle", QuestionTitle);
            Parameters.Add("@StatementText", QuestionText);
        
            dh.ExecuteStoredProcedureNonQuery("sp_UpdateProblemStatement", Parameters);
        }
        public static DataTable getMyQuestions(int uid)
        {
            string sql = "SELECT * FROM ProblemStatement WHERE EnteredByUserID = " + Convert.ToString(uid);
            DatabaseHelper dh = new DatabaseHelper(UserManagement.ConnectionName);
            return dh.executeSqlReturnDataTable(sql);
        }
        public static string getQuestionTitle(int id)
        {
            return getQuestionField(id, "StatementTitle");
        }
        public static string getQuestionText(int id)
        {
            return getQuestionField(id, "StatementText");
        }
        private static string getQuestionField(int id, string field)
        {
            string sql = "SELECT " + field + " FROM ProblemStatement WHERE id = " + Convert.ToString(id);
            DatabaseHelper dh = new DatabaseHelper(UserManagement.ConnectionName);
            return dh.executeSqlReturnString(sql);
        }
        public static int getQuestionID(HttpSessionState s)
        {
            if (s[Global.QuestionID] != null)
            {
                int uid = Convert.ToInt32(s[Global.QuestionID]);
                return uid;
            }
            else
            {
                return 0;
            }
        }
    }
} 
 
MyQuestions.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/LoggedIn.Master" AutoEventWireup="true" CodeBehind="MyQuestions.aspx.cs" Inherits="MyDailyMath.Users.MyQuestions" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
    <script type="text/javascript" src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
    </script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<table><tr><td>
    <asp:GridView ID="gvMyQuestions" runat="server" CellPadding="4" ForeColor="#333333" 
        GridLines="None" OnRowCommand="gvMyQuestions_OnRowCommand" AutoGenerateColumns="false">
        <AlternatingRowStyle BackColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#F5F7FB" />
        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
        <SortedDescendingCellStyle BackColor="#E9EBEF" />
        <SortedDescendingHeaderStyle BackColor="#4870BE" />
        <Columns>
        <asp:BoundField DataField="id" HeaderText="ID" />
        <asp:BoundField DataField="StatementTitle" HeaderText="Title" />
        <asp:TemplateField>
        <ItemTemplate>
            <asp:LinkButton ID="lbView" runat="server" CommandName="view" CommandArgument='<%#Eval("id") %>'>View</asp:LinkButton> |
            <asp:LinkButton ID="lbEdit" runat="server" CommandName="editq" CommandArgument='<%#Eval("id") %>'>Edit</asp:LinkButton>
        </ItemTemplate>
        </asp:TemplateField>            
        </Columns>
    </asp:GridView>
    </td>
    <td>
    <asp:Panel ID="Panel1" runat="server">
        <asp:Literal ID="litQuestion" runat="server"></asp:Literal>
    </asp:Panel>
    </td>
    </tr></table>
</asp:Content>
 
 
MyQuestions.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MyDailyMath.Code;
namespace MyDailyMath.Users
{
    public partial class MyQuestions : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                gvMyQuestions.DataSource = MyDailyMathDB.getMyQuestions(UserManagement.getUserID(Session));
                gvMyQuestions.DataBind();
            }
        }
        protected void gvMyQuestions_OnRowCommand(object sender, GridViewCommandEventArgs e)
        {
            int qid = Convert.ToInt32(e.CommandArgument);
            if (e.CommandName == "view")
            {
                litQuestion.Text = MyDailyMathDB.getQuestionText(qid);
            }
            if (e.CommandName == "editq")
            {
                Session.Add(Global.QuestionID, qid);
                Response.Redirect("~/Users/CreateMCQuestion.aspx");
            }
        }
    }
} 
 
CreateMCQuestion.aspx
<%@ Page Title="Create a Multiple Choice Question" Language="C#" MasterPageFile="~/LoggedIn.Master" AutoEventWireup="true" CodeBehind="CreateMCQuestion.aspx.cs" Inherits="MyDailyMath.Users.CreateMCQuestion" ValidateRequest="false" %>
<%@ Register Src="~/UserControls/ucQuestionEditor.ascx" TagPrefix="uc" TagName="QuestionEditor" %>
<%@ MasterType virtualpath="~/LoggedIn.Master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
    <script type="text/javascript" src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
    </script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <asp:Panel ID="Panel2" runat="server" CssClass="QuestionTitleTextBox">
    Question Title:  
    <asp:TextBox ID="tbTitle" runat="server" Width="200" BorderWidth="4" BorderStyle="Double" BorderColor="ActiveBorder" Font-Size="Large" ></asp:TextBox>
    <asp:RequiredFieldValidator
        ID="RequiredFieldValidator1" runat="server" ErrorMessage="A Question Title is required" ControlToValidate="tbTitle"></asp:RequiredFieldValidator>
        <asp:Button ID="btnSubmit" runat="server" Text="Submit Question to Database and Enter Answers" 
            CssClass="SubmitButton" onclick="btnSubmit_Click" />
    </asp:Panel>
    <br />
    <asp:Panel ID="Panel1" runat="server" ScrollBars="Auto" BorderStyle="Groove" BorderWidth="2" BorderColor="Aquamarine">
        <uc:QuestionEditor id="qeQuestion" runat="server" />
    </asp:Panel>
    <ajaxToolkit:RoundedCornersExtender ID="rce1" runat="server" TargetControlID="Panel1" Corners="All" Radius="8">
    </ajaxToolkit:RoundedCornersExtender>
</asp:Content> 
 
CreateMCQuestion.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MyDailyMath.Code;
namespace MyDailyMath.Users
{
    public partial class CreateMCQuestion : System.Web.UI.Page
    {
        public int questionID
        {
            get { return MyDailyMathDB.getQuestionID(Session); }
            set { Session.Add(Global.QuestionID, value); }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            btnSubmit.Visible = (qeQuestion.Text != String.Empty);
            if (!IsPostBack)
            {
                if (questionID != 0)
                {
                    tbTitle.Text = MyDailyMathDB.getQuestionTitle(questionID);
                    qeQuestion.Text = MyDailyMathDB.getQuestionText(questionID);
                }
            }
        }
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            if (questionID == 0)
                questionID = MyDailyMathDB.createQuestion(tbTitle.Text, qeQuestion.Text, Master.userID);
            else
                MyDailyMathDB.updateQuestion(questionID, tbTitle.Text, qeQuestion.Text);
                
        }
    }
} 

 

 

 

 

Additional Information

 

 

COP 4834 Lectures Page

Comments (0)

You don't have permission to comment on this page.