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
Video for this lecture has not been produced yet.
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
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("'", "''");
}
}
}
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;
}
}
}
}
<%@ 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>
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");
}
}
}
}
<%@ 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>
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
