CIS407 Week 5 Lab Touch-Up

PayrollSystem (1)/App_Code/clsDataLayer.cs

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.OleDb; using System.Net; using System.Data; public class clsDataLayer { // This function gets the user activity from the tblUserActivity public static dsUserActivity GetUserActivity(string Database) { // Declare DataSet, connection, and data adapter object dsUserActivity DS; OleDbConnection sqlConn; OleDbDataAdapter sqlDA; // Inintialize connection using the connection string to the database sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); // Initialize the data adapter using SQL sqlDA = new OleDbDataAdapter("select * from tblUserActivity", sqlConn); // Create and empty data set DS = new dsUserActivity(); // It fills the data set from the data adapter sqlDA.Fill(DS.tblUserActivity); // returns the retrieved data to the caller return DS; } // This function saves the user activity public static void SaveUserActivity(string Database, string FormAccessed) { // It saves the user information by connecting to the database and saving it to the dataset 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; // Declares the transaction variable. OleDbTransaction myTransaction = null; try { // Opens the 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; // Begin transaction as new transaction myTransaction = conn.BeginTransaction(); command.Transaction = myTransaction; // It perform an insert interaction with the database to insert the data below; FirstName and LastName //from the form filled by the user. strSQL = "Insert into tblPersonnel " + "(FirstName, LastName) values ('" + FirstName + "', '" + LastName + "')"; // Indicate the type of command being executed, in this case is .Text; Second command is to identify // the command to execute which is strSQL that insert data into the datasources in the data set. command.CommandType = CommandType.Text; command.CommandText = strSQL; // perform a query without returning any value since we are performing an Insert operation for the database. command.ExecuteNonQuery(); // perform and UPDATE to save the PayRate, StartDate, and EndDate into the new record. strSQL = "Update tblPersonnel " + "Set PayRate=" + PayRate + ", " + "StartDate='" + StartDate + "', " + "EndDate='" + EndDate + "' " + "Where ID=(Select Max(ID) From tblPersonnel)"; // Indicate the type of command being executed, in this case is .Text; Second command is to identify // the command to execute which is strSQL that insert data into the datasources in the data set. command.CommandType = CommandType.Text; command.CommandText = strSQL; // perform a query without returning any value since we are performing an Insert operation for the database. command.ExecuteNonQuery(); // Commits the transaction saving changes and competing the current transaction. myTransaction.Commit(); // Close the connection to the database. conn.Close(); recordSaved = true; } catch (Exception ex) { // Rollsback the transaction canceling any changes during the current transaction and ends the transaction. myTransaction.Rollback(); recordSaved = false; } return recordSaved; } public static dsPersonnel GetPersonnel(string Database, string strSearch) { // Declare DataSet, connection, and data adapter object dsPersonnel DS; OleDbConnection sqlConn; OleDbDataAdapter sqlDA; // Inintialize connection using the connection string to the database sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); // Will check if the text box for search is empty or the user hit the search button with nothing on the text box // then it will return all the personnel. If a last name is filled up it will return the table with the last names on the table. if (strSearch == null || strSearch.Trim() == "") { sqlDA = new OleDbDataAdapter("select * from tblPersonnel", sqlConn); } else { sqlDA = new OleDbDataAdapter("select * from tblPersonnel where LastName = '" + strSearch + "'", sqlConn); } // Create and empty data set DS = new dsPersonnel(); // It fills the data set from the data adapter sqlDA.Fill(DS.tblPersonnel); // returns the retrieved data to the caller return DS; } }