public void TestTransaction()
{
using (DbConnection connection = GetConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
const string newName = "changed";
StoredProcedure changeName = new StoredProcedure("ChangeName");
changeName.Command.AddParameter("@Id", 2, DbType.Int32, 4);
changeName.Command.AddParameter("@NewName", newName, DbType.String, 50);
changeName.Execute(transaction);
StoredProcedure getName = new StoredProcedure("GetName");
getName.Command.AddParameter("@Id", 2, DbType.Int32, 4);
Assert.AreEqual(newName, getName.ExecuteScalar(transaction), "The name has not been changed");
transaction.Rollback();
Assert.AreEqual("name2", getName.ExecuteScalar(), "The name has not been reverted");
connection.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
pagTitle.InnerText = ResourceManager.GetString("pagTitle");
if (!Page.IsPostBack)
{
if (UserContext.UserData.UserType == UserType.SaltAdmin || UserContext.UserData.UserType == UserType.OrgAdmin)
{
Int32.TryParse(Request.QueryString["timezone"], out this.tzId);
using (StoredProcedure sp = new StoredProcedure("prcTimeZone_GetTimeZoneWrittenName",
StoredProcedure.CreateInputParam("@TimeZoneID", SqlDbType.Int, this.tzId)))
{
this.tzWrittenName = sp.ExecuteScalar().ToString();
}
ViewState.Add("WrittenName", this.tzWrittenName);
this.isNew = false;
if (Request.QueryString["ruleid"].ToString().ToUpper() == "NEW")
{
this.isNew = true;
this.txtTimezone.Text = this.tzWrittenName;
}
else
{
Int32.TryParse(Request.QueryString["ruleid"], out this.ruleId);
this.LoadData();
}
ViewState.Add("RuleID", this.ruleId);
ViewState.Add("TimezoneID", this.tzId);
ViewState.Add("IsNew", this.isNew);
}
}
}
} // GetList
/// <summary>
/// Get the Version of the application
/// </summary>
/// <returns></returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Stephen Kennedy-Clark, 10/02/2004
/// Changes:
/// </remarks>
public string GetVersion()
{
using (StoredProcedure sp = new StoredProcedure("prcVersion_Get"))
{
return(Convert.ToString(sp.ExecuteScalar()));
}
} // GetVersion
/// <summary>
/// Gets time the Module Status Update job was last run
/// </summary>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Gavin Buddis, 24/03/2004
/// Changes:
/// #1 19/4/04 Returns DateTime.Minvalue if it has never been run before
/// </remarks>
public String GetSqlAgentRunningStatus()
{
using (StoredProcedure sp = new StoredProcedure("prcSqlAgentRunningStatus_Get"))
{
return((string)sp.ExecuteScalar());
}
}
} //GetUser
/// <summary>
/// prcQuiz_GetScore
/// </summary>
/// <returns>
/// Returns the user score based on their session id
/// </returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Peter Kneale, 24 / 2 / 05
/// Changes:
/// </remarks>
/// <param name="sessionID">String containg the session GUID</param>
public int GetQuizScore(SqlString sessionID)
{
using (StoredProcedure sp = new StoredProcedure("prcQuiz_GetScore",
StoredProcedure.CreateInputParam("@QuizSessionID", SqlDbType.VarChar, 50, sessionID)))
{
return((int)sp.ExecuteScalar());
}
} //GetUser
/// <summary>
/// Gets SQL Server version
/// </summary>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Jack Liu, 26/02/2004
/// Changes:
/// </remarks>
public string GetDatabaseVersion()
{
using (StoredProcedure sp = new StoredProcedure("prcVersion_Get")
)
{
return((string)sp.ExecuteScalar());
}
} // GetDatabaseVersion
} //RecordPageVisited
/// <summary>
/// GetBookmark
/// </summary>
/// <returns>Returns the ToolBookPageID for the page in the current toolbook that a user has set</returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Stephen Kennedy-Clark, 03/02/0/2004
/// Changes:
/// </remarks>
/// <param name="sessionID">String containg the session GUID</param>
public string GetBookmark(SqlString sessionID)
{
using (StoredProcedure sp = new StoredProcedure("prcBookMark_GetBookMarkBySessionID",
StoredProcedure.CreateInputParam("@lessonSessionID", SqlDbType.VarChar, 50, sessionID)))
{
return((string)sp.ExecuteScalar());
}
} //GetBookmark
} //EndLessonSession
/// <summary>
/// GetUser
/// </summary>
/// <returns>
/// Returns the user's first and last names concatenated with a space
/// </returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Stephen Kennedy-Clark, 03/02/0/2004
/// Changes:
/// </remarks>
/// <param name="sessionID">String containg the session GUID</param>
public string GetUser(SqlString sessionID)
{
using (StoredProcedure sp = new StoredProcedure("prcUser_GetNameBySessionID",
StoredProcedure.CreateInputParam("@sessionID", SqlDbType.VarChar, 50, sessionID)))
{
return((string)sp.ExecuteScalar());
}
} //GetUser
} //StartLesson
/// <summary>
/// SessionIsUnique
/// </summary>
/// <returns>Returns true if a session has not been used before</returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// this function is to ensure that the lesson onload event only fires once
/// for each toolbook - and that no attempt is made (ie hitting the
/// back / forward button) to restart a lesson
/// Author: Stephen Kennedy-Clark, 03/02/0/2004
/// Changes:
/// </remarks>
/// <param name="sessionID">String containg the session GUID</param>
public bool SessionIsUnique(SqlString sessionID)
{
using (StoredProcedure sp = new StoredProcedure("prcLessonQuizSession_CheckSessionUnique",
StoredProcedure.CreateInputParam("@sessionID", SqlDbType.VarChar, 50, sessionID)))
{
return(Convert.ToBoolean(sp.ExecuteScalar()));
}
} //SessionIsUnique
} //BeforeLessonStart
/// <summary>
/// StartLesson - updates a prepared lesson, starts it if it has not already been started and return true, else return false
/// </summary>
/// <returns>Returns true if a session has not been strated before</returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// this function is to ensure that the lesson onload event only fires once
/// for each toolbook - and that no attempt is made (ie hitting the
/// back / forward button) to restart a lesson
/// Author: Stephen Kennedy-Clark, 03/02/0/2004
/// Changes:
/// </remarks>
/// <param name="sessionID">String containg the session GUID</param>
public bool StartLesson(SqlString sessionID)
{
using (StoredProcedure sp = new StoredProcedure("prcLessonSession_StartLesson",
StoredProcedure.CreateInputParam("@lessonSessionID", SqlDbType.VarChar, 50, sessionID)))
{
return(Convert.ToBoolean(sp.ExecuteScalar()));
}
} //StartLesson
} //BeforeQuizStart
/// <summary>
/// Starts a quiz session for a particular user attempting a particular quiz.
/// </summary>
/// <returns> A boolean value indicating whether the session started successfully or not.
/// </returns>
/// <remarks>
/// Assumptions:None
/// Notes:
/// Author: Peter Kneale, 05/02/2004
/// Changes:
/// </remarks>
/// <param name="QuizSessionID">User ID of the user starting the quiz.</param>
public bool StartQuiz(string QuizSessionID)
{
using (StoredProcedure sp = new StoredProcedure("prcQuizSession_StartQuiz",
StoredProcedure.CreateInputParam("@QuizSessionID", SqlDbType.VarChar, 50, QuizSessionID)
))
{
return(Convert.ToBoolean(sp.ExecuteScalar()));
}
} //StartQuiz
public int GetAcceptedUsers(int OrganisationID, int PolicyID)
{
using (StoredProcedure spGetAcceptedUsers = new StoredProcedure("prcPolicy_GetAcceptedUsers",
StoredProcedure.CreateInputParam("@OrganisationID", SqlDbType.Int, OrganisationID),
StoredProcedure.CreateInputParam("@PolicyID", SqlDbType.Int, PolicyID)))
{
return(Int32.Parse(spGetAcceptedUsers.ExecuteScalar().ToString()));
}
}
public string GetTypeFromID(int reportId)
{
using (StoredProcedure sp = new StoredProcedure("prcReport_GetTypeFromID",
StoredProcedure.CreateInputParam("@ReportId", SqlDbType.Int, reportId)
))
{
return(sp.ExecuteScalar().ToString());
}
}
} //GetUserIDBySessionID
/// <summary>
/// This method returns a users Unit ID based on their UserID
/// </summary>
/// <remarks>
/// Assumptions:None
/// Notes:
/// Author: Peter Kneale, 05/02/2004
/// Changes:
/// </remarks>
/// <param name="UserID">UserID of desired user.</param>
/// <returns>UnitID of the Unit that the user belongs to.</returns>
public int GetUnitIDByUserID(int UserID)
{
using (StoredProcedure sp = new StoredProcedure("prcUser_GetUnitIDByUserID",
StoredProcedure.CreateInputParam("@UserId", SqlDbType.Int, UserID)
))
{
return(Convert.ToInt32(sp.ExecuteScalar()));
}
} //GetUnitIDByUserID
} //GetQuizPassMark
/// <summary>
/// This method returns the number of questions in a particular quiz.
/// </summary>
/// <remarks>
/// Assumptions:None
/// Notes:
/// Author: Peter Kneale, 05/02/2004
/// Changes:
/// </remarks>
/// <param name="QuizID">QuizID of the Quiz that we wish to could the questions in.</param>
/// <returns>The number of questions.</returns>
public int GetQuizQuestionsCountByQuizID(int QuizID)
{
using (StoredProcedure sp = new StoredProcedure("prcQuiz_CountQuestions",
StoredProcedure.CreateInputParam("@QuizID", SqlDbType.Int, QuizID)
))
{
return(Convert.ToInt32(sp.ExecuteScalar()));
}
} //GetQuizQuestionsCountByQuizID
public void Acc_SP_ScalarTest()
{
StoredProcedure sp = new StoredProcedure("CustOrderHist", DataService.GetInstance("NorthwindAccess"));
sp.Command.AddParameter("[CustomerID]", "ALFKI", DbType.AnsiString);
object result = sp.ExecuteScalar();
Assert.IsTrue(result != null);
}
public static string Changepassword(string UserId, string Password)
{
SubSonic.Schema.StoredProcedure sp = new StoredProcedure("changepassword");
sp.Command.AddParameter("@userId", UserId, DbType.String);
sp.Command.AddParameter("@password", Password, DbType.String);
return(sp.ExecuteScalar <String>());
}
} // GetCourse
/// <summary>
/// Gets the course ID given the session ID
/// </summary>
/// <returns>Returns the ID of the Course as an int, -1 if no session was found matching this session id</returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Stephen K-Clark, 9/02/04
/// Changes:
/// </remarks>
/// <param name="sessionID">ID of the session to get the course id for</param>
public int GetCourseBySessionID(SqlString sessionID)
{
using (StoredProcedure sp = new StoredProcedure("prcCourse_GetOneBySessionID",
StoredProcedure.CreateInputParam("@sessionID", SqlDbType.VarChar, 50, sessionID)
))
{
return(Convert.ToInt32(sp.ExecuteScalar()));
}
} // GetCourse
} //GetQuizIDBySessionID
/// <summary>
/// This method returns a users User ID based on a current Sesion Id.
/// </summary>
/// <remarks>
/// Assumptions:None
/// Notes:
/// Author: Peter Kneale, 05/02/2004
/// Changes:
/// </remarks>
/// <param name="SessionID">The session ID from which to get the User ID</param>
/// <returns>UserID of the user matching the sessionid </returns>
public int GetUserIDBySessionID(string SessionID)
{
using (StoredProcedure sp = new StoredProcedure("prcUser_GetUserIDBySessionID",
StoredProcedure.CreateInputParam("@SessionID", SqlDbType.VarChar, 50, SessionID)
))
{
return(Convert.ToInt32(sp.ExecuteScalar()));
}
} //GetUserIDBySessionID
public Boolean RequiresDateFromDateTo(int reportId)
{
using (StoredProcedure sp = new StoredProcedure("prcRequiresDateFromDateTo",
StoredProcedure.CreateInputParam("@ReportId", SqlDbType.Int, reportId)
))
{
return(Convert.ToBoolean(sp.ExecuteScalar()));
}
}
public string LMSgetValue(int StudentID, int LessonID, string DME)
{
using (StoredProcedure sp = new StoredProcedure("prcSCORMgetValue",
StoredProcedure.CreateInputParam("@StudentID", SqlDbType.Int, StudentID),
StoredProcedure.CreateInputParam("@LessonID", SqlDbType.Int, LessonID),
StoredProcedure.CreateInputParam("@DME", SqlDbType.VarChar, 50, DME)))
{
return((string)sp.ExecuteScalar());
}
}
} //GetPagesVisited
/// <summary>
/// RecordPageVisited
/// </summary>
/// <returns>Returns boolean true if the lesson was not already finished and the page visited exists in the toolbook</returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// This function records the fact that a student has visited a page in a toolbook
/// it requires the toolbook pageid and the session id
/// The underlying stored proc will not accept the page if the lesson has already been finished
/// or if salt cannot match the pageID to a page in the lesson
/// Author: Stephen Kennedy-Clark, 03/02/0/2004
/// Changes:
/// </remarks>
/// <param name="sessionID">String containg the session GUID</param>
/// <param name="pageID">Integer containg the page id</param>
public bool RecordPageVisited(SqlString sessionID, SqlString pageID)
{
using (StoredProcedure sp = new StoredProcedure("prcLessonPageAudit_InsertPageVisited",
StoredProcedure.CreateInputParam("@lessonSessionID", SqlDbType.VarChar, 50, sessionID),
StoredProcedure.CreateInputParam("@toolBookPageID", SqlDbType.VarChar, 50, pageID)
))
{
return(Convert.ToBoolean(sp.ExecuteScalar()));
}
} //RecordPageVisited
} //GetQuizQuestionsCountByQuizID
/// <summary>
/// ValidateAccess
/// </summary>
/// <returns>Returns true if a user can access a module.</returns>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Stephen Kennedy-Clark, 03/02/0/2004
/// Changes:
/// </remarks>
/// <param name="userID">integer containg the user id</param>
/// <param name="moduleID">integer containg the module id</param>
public bool ValidateAccess(int userID, int moduleID)
{
using (StoredProcedure sp = new StoredProcedure("prcUserModuleAccess_Validate",
StoredProcedure.CreateInputParam("@userID", SqlDbType.Int, userID),
StoredProcedure.CreateInputParam("@moduleID", SqlDbType.Int, moduleID)
))
{
return(Convert.ToBoolean(sp.ExecuteScalar()));
}
} //ValidateAccess
} //GetUnitIDByUserID
/// <summary>
/// Gets the pass mark of the quiz that belongs to the specified Unit and Module.
/// </summary>
/// <remarks>
/// Assumptions:None
/// Notes:
/// Author: Peter Kneale, 05/02/2004
/// Changes:
/// </remarks>
/// <param name="UnitID">UnitID that the quiz belongs to.</param>
/// <param name="ModuleID">ModuleID that the quiz belongs to.</param>
/// <returns></returns>
public int GetQuizPassMark(int UnitID, int ModuleID)
{
using (StoredProcedure sp = new StoredProcedure("prcQuiz_GetPassMark",
StoredProcedure.CreateInputParam("@UnitID", SqlDbType.Int, UnitID),
StoredProcedure.CreateInputParam("@ModuleID", SqlDbType.Int, ModuleID)
))
{
return(Convert.ToInt32(sp.ExecuteScalar()));
}
} //GetQuizPassMark
public static int Add(Seller item)
{
int newID = 0;
using (StoredProcedure sp = new StoredProcedure("Seller_AddItem"))
{
sp.Params.Add("@Name", System.Data.SqlDbType.DateTime).Value = item.Name;
newID = Convert.ToInt32(sp.ExecuteScalar());
}
return newID;
}
} //GetBookmark
/// <summary>
/// EndLessonSession
/// </summary>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Stephen Kennedy-Clark, 03/02/0/2004
/// Changes:
/// </remarks>
/// <param name="sessionID">String containg the session GUID</param>
/// <param name="duration">Integer - The number of seconds that the lesson was running</param>
/// <param name="bookmark">string containg teh toolbook page id to be bookmarked</param>
/// <returns>Return true if the lesson was ended sucessfully</returns>
public bool EndLessonSession(SqlString sessionID, SqlInt32 duration, SqlString bookmark)
{
using (StoredProcedure sp = new StoredProcedure("prcLessonSession_EndLesson",
StoredProcedure.CreateInputParam("@lessonSessionID", SqlDbType.VarChar, 50, sessionID),
StoredProcedure.CreateInputParam("@duration", SqlDbType.Int, duration),
StoredProcedure.CreateInputParam("@bookmark", SqlDbType.VarChar, 50, bookmark)
))
{
return(Convert.ToBoolean(sp.ExecuteScalar()));
}
} //EndLessonSession
public string SCORMpublishcontent(string LessonLaunchPoint, string QFS, string ModuleName, string CourseName, string QuizLaunchPoint)
{
using (StoredProcedure sp = new StoredProcedure("prcSCORMpublishcontent",
StoredProcedure.CreateInputParam("@LessonLaunchPoint", SqlDbType.VarChar, 100, LessonLaunchPoint),
StoredProcedure.CreateInputParam("@QFS", SqlDbType.VarChar, 100, QFS),
StoredProcedure.CreateInputParam("@QuizLaunchPoint", SqlDbType.VarChar, 100, QuizLaunchPoint),
StoredProcedure.CreateInputParam("@CourseName", SqlDbType.VarChar, 100, CourseName),
StoredProcedure.CreateInputParam("@ModuleName", SqlDbType.VarChar, 100, ModuleName)
))
{
return((string)sp.ExecuteScalar());
}
}
public long GetUsedPolicyDiskSpace(int OrganisationID)
{
using (StoredProcedure spGetUsedPolicyDiskSpace = new StoredProcedure("prcOrganisation_GetPolicyUsedSpace",
StoredProcedure.CreateInputParam("@OrganisationID", SqlDbType.Int, OrganisationID)))
{
string strUsedPolicyDiskSpace = spGetUsedPolicyDiskSpace.ExecuteScalar().ToString();
if (strUsedPolicyDiskSpace.Length == 0)
{
return(0);
}
else
{
return(long.Parse(strUsedPolicyDiskSpace));
}
}
}
/// <summary>
/// Attempts to connect to the database
/// </summary>
/// <returns>boolean value indicating if database is available</returns>
private bool VerifyDatabaseConnection()
{
try
{
string strTemp = "";
// Just call a stored procedure and discard its return value
using (StoredProcedure sp = new StoredProcedure("prcVersion_Get"))
{
strTemp = sp.ExecuteScalar().ToString();
}
return(true);
}
catch
{
return(false);
}
}
} // GetList
/// <summary>
/// Get a single Organisation configuration value
/// </summary>
/// <returns></returns>
/// <param name="organisationID">Organisation to return configuration values for</param>
/// <param name="name">Name of the configuration value</param>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Peter Kneale, 28/06/2004
/// Changes:
/// </remarks>
public string GetOne(int organisationID, string name)
{
// Collection to hold name value pairs
string strValue;
// Get datatable of results from database
using (StoredProcedure sp = new StoredProcedure("prcOrganisationConfig_GetOne",
StoredProcedure.CreateInputParam("@organisationID", SqlDbType.Int, 4, organisationID),
StoredProcedure.CreateInputParam("@name", SqlDbType.NVarChar, 255, name)
))
{
strValue = sp.ExecuteScalar().ToString();
}
// return result
return(strValue);
} // GetList
} // GetDatabaseVersion
/// <summary>
/// Gets time the Module Status Update job was last run
/// </summary>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Gavin Buddis, 24/03/2004
/// Changes:
/// #1 19/4/04 Returns DateTime.Minvalue if it has never been run before
/// </remarks>
public DateTime GetDateModuleStatusUpdateLastRun(int organisationID)
{
string strDateTime;
// Update User Quiz Status table
using (StoredProcedure sp = new StoredProcedure("prcModuleStatusUpdateHistory_GetLastRun",
StoredProcedure.CreateInputParam("@OrgID", SqlDbType.Int, 8000, organisationID)))
{
strDateTime = sp.ExecuteScalar().ToString();
if (strDateTime.Length == 0)
{
return(DateTime.MinValue);
}
else
{
return(DateTime.Parse(strDateTime));
}
}
}
public void SP_ScalarTest()
{
StoredProcedure sp = new StoredProcedure("CustOrderHist", DataService.GetInstance("Northwind"));
sp.Command.AddParameter("@CustomerID", "ALFKI", DbType.AnsiString);
object result = sp.ExecuteScalar();
Assert.IsTrue(result != null);
}
public void TestTransaction()
{
using (DbConnection connection = GetConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
const string newName = "changed";
StoredProcedure changeName = new StoredProcedure("ChangeName");
changeName.Command.AddParameter("@Id", 2, DbType.Int32, 4);
changeName.Command.AddParameter("@NewName", newName, DbType.String, 50);
changeName.Execute(transaction);
StoredProcedure getName = new StoredProcedure("GetName");
getName.Command.AddParameter("@Id", 2, DbType.Int32, 4);
Assert.AreEqual(newName, getName.ExecuteScalar(transaction), "The name has not been changed");
transaction.Rollback();
Assert.AreEqual("name2", getName.ExecuteScalar(), "The name has not been reverted");
connection.Close();
}
}
