CIS407a Web Application Development with Lab

CIS407_Lab7_2016/Week7Lab/App_Code/clsDataLayer.cs

// Namespaces to be used in the class using System.Data.OleDb; using System.Net; using System.Data; using System.Web; using System; /// <summary> /// This class handles all the database operations for the website /// </summary> public class clsDataLayer { // This function gets the user activity from the tblUserActivity public static dsUserActivity GetUserActivity(string Database) { // Declare data set, connection and data adapter objects dsUserActivity DS; OleDbConnection sqlConn; OleDbDataAdapter sqlDA; // create a connection to the access database sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); // set the dataadapter with the query sqlDA = new OleDbDataAdapter("select * from tblUserActivity", sqlConn); // initialize the dataset DS = new dsUserActivity(); // Fill the dataset sqlDA.Fill(DS.tblUserActivity); // return the dataset return DS; } // This function saves the user activity public static void SaveUserActivity(string Database, string FormAccessed) { // create a connection to the database and inserts user activity record to the table OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); conn.Open(); OleDbCommand command = conn.CreateCommand(); string strSQL; strSQL = "Insert into tblUserActivity (UserIP, FormAccessed) values ('" + GetIP4Address() + "', '" + FormAccessed + "')"; command.CommandType = CommandType.Text; command.CommandText = strSQL; command.ExecuteNonQuery(); conn.Close(); } // This function gets the IP Address public static string GetIP4Address() { string IP4Address = string.Empty; foreach (IPAddress IPA in Dns.GetHostAddresses(HttpContext.Current.Request.UserHostAddress)) { if (IPA.AddressFamily.ToString() == "InterNetwork") { IP4Address = IPA.ToString(); break; } } if (IP4Address != string.Empty) { return IP4Address; } foreach (IPAddress IPA in Dns.GetHostAddresses(Dns.GetHostName())) { if (IPA.AddressFamily.ToString() == "InterNetwork") { IP4Address = IPA.ToString(); break; } } return IP4Address; } // This function saves the personnel data public static bool SavePersonnel(string Database, string FirstName, string LastName, string PayRate, string StartDate, string EndDate) { bool recordSaved; // Declare a transaction variable OleDbTransaction myTransaction = null; try { // create a connection to the database OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); conn.Open(); OleDbCommand command = conn.CreateCommand(); string strSQL; // Start the transaction and set the transaction to the command object myTransaction = conn.BeginTransaction(); command.Transaction = myTransaction; // query to insert record in the tblPersonnel table strSQL = "Insert into tblPersonnel " + "(FirstName, LastName) values ('" + FirstName + "', '" + LastName + "')"; // Set command object with the query command.CommandType = CommandType.Text; command.CommandText = strSQL; // execute the insert query command.ExecuteNonQuery(); // update query strSQL = "Update tblPersonnel " + "Set PayRate=" + PayRate + ", " + "StartDate='" + StartDate + "', " + "EndDate='" + EndDate + "' " + "Where ID=(Select Max(ID) From tblPersonnel)"; // Set the command object with the update query command.CommandType = CommandType.Text; command.CommandText = strSQL; // Execute the query command.ExecuteNonQuery(); // Commit the transaction myTransaction.Commit(); // Close connection to the database conn.Close(); recordSaved = true; } catch (Exception ex) { //Incase of exception rollback the transaction myTransaction.Rollback(); recordSaved = false; } return recordSaved; } // This function gets the user activity from the tblPersonnel public static dsPersonnel GetPersonnel(string Database, string strSearch) { // Create the dataset, connection and data adapter objects dsPersonnel DS; OleDbConnection sqlConn; OleDbDataAdapter sqlDA; // Create connection the database sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); // if strsearch is empty or null select all records from tblPersonnel //otherwise select only those records matching with strsearch in lastname if (strSearch == null || strSearch.Trim() == "") { sqlDA = new OleDbDataAdapter("select * from tblPersonnel", sqlConn); } else { sqlDA = new OleDbDataAdapter("select * from tblPersonnel where LastName = '" + strSearch + "'", sqlConn); } // initialize the dataset DS = new dsPersonnel(); // populate the dataset with the data returned from the query sqlDA.Fill(DS.tblPersonnel); // return the dataset return DS; } // This function verifies a user in the tblUser table public static dsUser VerifyUser(string Database, string UserName, string UserPassword) { // Declare the Dataset, sqlConnection and SQLDataadapter objects dsUser DS; OleDbConnection sqlConn; OleDbDataAdapter sqlDA; // set the connection to the sql connection object sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); // set the sql adapter with the query to get the record for the user sqlDA = new OleDbDataAdapter("Select SecurityLevel from tblUserLogin " + "where UserName like '" + UserName + "' " + "and UserPassword like '" + UserPassword + "'", sqlConn); // set the dataset object DS = new dsUser(); // fill the dataset with the results returned from the query sqlDA.Fill(DS.tblUserLogin); // return the dataset return DS; } public static bool SaveUser(string Database, string UserName, string Password,string SecurityLevel) { bool recordSaved; // Declare a transaction variable OleDbTransaction myTransaction = null; try { // create a connection to the database OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); conn.Open(); OleDbCommand command = conn.CreateCommand(); string strSQL; // query to insert record in the tblUserLogin table strSQL = "Insert into tblUserLogin " + "(UserName, UserPassword,SecurityLevel) values ('" + UserName + "', '" + Password + "', '" + SecurityLevel + "')"; // Set command object with the query command.CommandType = CommandType.Text; command.CommandText = strSQL; // execute the insert query command.ExecuteNonQuery(); // Close connection to the database conn.Close(); recordSaved = true; } catch (Exception ex) { recordSaved = false; } return recordSaved; } }