US20-Retrieve Dynamic Content from Database

Description:

As a developer, I want to retreive Dynamic Content from the website’s database to improve the ability to display content to the customer which will improve customer’s website experience and will result in additional interest for our company’s services.

Acceptance Criteria:

The client reviews and approves Dynamic Content retrieval displayed through a demonstration to the client by website development team.

Tasks:

Develop connection and statements/query to facilitate Dynamic Content retrieval. 3 hrs –Tobin

/* Connect to Bucktown access database
string connStr = ConfigurationManager.ConnectionStrings["BucktownConnectionString"].ConnectionString;
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
   OleDbCommand command = new OleDbCommand(queryString, connection);
   connection.Open();
   OleDbDataReader reader = command.ExecuteReader();
}

/* Retrieve customer's address for a specific user */
string sql = "SELECT * FROM CUSTOMER WHERE CUSTOMERID =
  (SELECT USERID FROM USERINFO WHERE USERNAME='" + userName.Value + "')"

string sql = "SELECT FirstName, LastName, Address, City, Zip FROM CUSTOMER
  WHERE CUSTOMERID = (SELECT USERID FROM USERINFO WHERE USERNAME='" + userName.Value + "')";

using (OleDbConnection connection = new OleDbConnection(connStr))
using (OleDbCommand cmd = new OleDbCommand(sql, connection))
{
  connection.Open();
  OleDbDataReader reader = cmd.ExecuteReader();
  string fname, lname, address, city, zip, email;
  if (reader.Read())
  {
   fname = reader[0].ToString();
   lname = reader[1].ToString();
   address = reader[2].ToString();
   city = reader[3].ToString();
   zip = reader[4].ToString();
   custInfo.Text = fname + ' ' + lname + ' ' + address + ' ' + city + ' ' + zip;
   }
}

/* Insert a new customer information into the database */
string sql = "INSERT INTO Customer (FirstName,LastName,Address,City,State,Zip,Email,MembershipID)
  VALUES (firstName.Value,lastName.Value,address.Value,city.Vaule,state.Value,zip.Value,email.Value,membershipID.Value)"

using (OleDbConnection connection = new OleDbConnection(connStr))
using (OleDbCommand command = new OleDbCommand(sql, connection))
{
  connection.Open();
  cmd.ExecuteNonQuery();
  connection.Close();
}

/* Display classes for a specific user
string inputName = name.Value;
string sql = "SELECT CLASSNAME, DESCRIPTION, INSTRUCTOR, DAY, CLASSTIME FROM CLASS
  WHERE CLASSID IN (SELECT CLASSID FROM ENROLLMENT
   WHERE ENROLLMENT.CUSTOMERID = (SELECT CUSTOMERID FROM CUSTOMER
    WHERE CUSTOMERID = ( SELECT USERID FROM USERINFO WHERE USERNAME='" + inputName + "')))"
DataSet ds = new DataSet();

using (OleDbConnection connection = new OleDbConnection(connStr))
using (OleDbCommand command = new OleDbCommand(sql, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
   adapter.Fill(ds);
}
GridView1.DataSource = ds;
GridView1.DataBind();