/// find user method // used to find the user in the database public dsProgramaholics FindUser(string LastName) { // sql statements to find user in datbase /* * string sqlStmt = "SELECT *" + * "FROM tblUserDetails " + * "LEFT JOIN tblProgram ON (tblUserDetails.id = tblProgram.id)" + * "LEFT JOIN tblCompleted ON (tblUserDetails.id = tblCompleted.id)" + * " WHERE tblUserDetails.LastName like '" + LastName + "';"; */ string sqlStmt = "SELECT * FROM tblUserDetails, tblProgram, tblCompleted WHERE tblUseDetails.id = tblProgram.id and tblUseDetails.id = tblCompleted.id and tblUserDetails.LasName like ‘“ + LastName + “‘"; OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter(sqlStmt, dbConnection); // new dataset to store dsProgramaholics myStoreData = new dsProgramaholics(); sqlDataAdapter.Fill(myStoreData.tblUserDetails); sqlDataAdapter.Fill(myStoreData.tblPrograms); sqlDataAdapter.Fill(myStoreData.tblCompleted); // return dataset return(myStoreData); }
}// end delete /// FILL DATASET // GetUser method public dsProgramaholics GetUser() { OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter("SELECT * FROM tblProgramaholics;", dbConnection); dsProgramaholics userDataSet = new dsProgramaholics(); sqlDataAdapter.Fill(userDataSet.tblProgramaholics); return(userDataSet); }// end getUser
}// end getUser /// get the personal stats public dsProgramaholics GetPersonalStats() { OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter("SELECT * FROM tblProgramaholics;", dbConnection); // fill the data set dsProgramaholics myDataSet = new dsProgramaholics(); sqlDataAdapter.Fill(myDataSet.tblProgramaholics); return(myDataSet); }
/// FIND USER BY LAST NAME // FindUser method that accepts 'LastName' as param, returns instances public dsProgramaholics FindUser(string LastName) { // new sql statement string sqlStmt = "SELECT * FROM tblProgramaholics WHERE LName = '" + LastName + "'"; OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter(sqlStmt, dbConnection); // fill data set dsProgramaholics myDataSet = new dsProgramaholics(); sqlDataAdapter.Fill(myDataSet.tblProgramaholics); return(myDataSet); }// end find
/// gridview // get all users & fill public dsProgramaholics GetAllUsers() { // new adapter OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter("SELECT * FROM tblUserDetails;", dbConnection); // store data & fill dsProgramaholics myStoreData = new dsProgramaholics(); sqlDataAdapter.Fill(myStoreData.tblUserDetails); // return return(myStoreData); }
// get user file public DataSet GetPersonalStats() { DataSet xmlDataSet = new DataSet(); try { xmlDataSet.ReadXml(dataPath + "personalReports.xml"); }// end try catch (System.IO.FileNotFoundException error) { dsProgramaholics userListing = myDataLayer.GetPersonalStats(); userListing.tblProgramaholics.WriteXml(dataPath + "personalReports.xml"); xmlDataSet.ReadXml(dataPath + "personalReports.xml"); }//end catch return(xmlDataSet); } // end get stats
/// BindUserGrid method // binds gridview to data private dsProgramaholics BindUserGrid() { // path string tempPath = Server.MapPath("~/App_Data/Programaholics.mdb"); clsDataLayer myData = new clsDataLayer(tempPath); // get users dsProgramaholics userList = myData.GetAllUsers(); // fill gridview gvUsers.DataSource = userList.tblUserDetails; // bind gridview gvUsers.DataBind(); Cache.Insert("UserDataSet", userList); return(userList); }
/// get user XML file method // gets all info for XML gridview public DataSet GetUserXMLFile() { // new dataset DataSet xmlDataSet = new DataSet(); try { // path for XML xmlDataSet.ReadXml(dataPath + "user.xml"); } catch (System.IO.FileNotFoundException error) { // get users info & write XML dsProgramaholics userList = myDataLayer.GetAllUsers(); userList.tblUserDetails.WriteXml(dataPath + "user.xml"); xmlDataSet.ReadXml(dataPath + "user.xml"); } // return data return(xmlDataSet); }
/// findUser method // find user in database public dsProgramaholics FindUser(string LastName) { // find user dsProgramaholics dsFoundUser = myDataLayer.FindUser(LastName); // go through database fields if (dsFoundUser.tblUserDetails.Rows.Count > 0) { // go through records System.Data.DataRow userRecord = dsFoundUser.tblUserDetails.Rows[0]; if (userRecord["FirstName"] == DBNull.Value) { userRecord["FirstName"] = string.Empty; } if (userRecord["LastName"] == DBNull.Value) { userRecord["LastName"] = string.Empty; } if (userRecord["Username"] == DBNull.Value) { userRecord["Username"] = string.Empty; } if (userRecord["Password"] == DBNull.Value) { userRecord["Password"] = string.Empty; } if (userRecord["Street"] == DBNull.Value) { userRecord["Street"] = string.Empty; } if (userRecord["City"] == DBNull.Value) { userRecord["City"] = string.Empty; } if (userRecord["State"] == DBNull.Value) { userRecord["State"] = string.Empty; } } return(dsFoundUser); }
/// find user method // finds the user by their last name. protected void btnFind_Click(object sender, EventArgs e) { dsProgramaholics dsFindLastName = myBusinessLayer.FindUser(txtLastName.Text); // go through records if (dsFindLastName.tblUserDetails.Rows.Count > 0) { txtFirstName.Text = dsFindLastName.tblUserDetails[0].FName; txtLastName.Text = dsFindLastName.tblUserDetails[0].LName; txtEmail.Text = dsFindLastName.tblUserDetails[0].Email; txtPassword.Text = dsFindLastName.tblUserDetails[0].Pwd; txtStreet.Text = dsFindLastName.tblUserDetails[0].Street; txtCity.Text = dsFindLastName.tblUserDetails[0].City; txtState.Text = dsFindLastName.tblUserDetails[0].State; } else { // no user found Master.UserFeedBack.Text = "No records found!"; } }
/// BindCustomerGridView method // calls data layer to fetch customers & binds that to GridView private dsProgramaholics BindUserGridView() { // new path string tempPath = Server.MapPath("~/Programaholics.mdb"); clsDataLayer myDataLayer = new clsDataLayer(tempPath); // where the database is myBusinessLayer = new clsBusinessLayer(Server.MapPath("~/App_Data/")); // get all customers dsProgramaholics userListing = (dsProgramaholics)myBusinessLayer.GetPersonalStats(); // customer list's source is the table from the database gvStats.DataSource = userListing.tblUsers; // bind data to gridView & cache gvStats.DataBind(); Cache.Insert("UserDataSet", userListing); // return cache return(userListing); }