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(); } }