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