US20-Retrieve Dynamic Content from Database.
Description
As a developer, I want to display dynamic content on our site based on users action and record real-time data so that I can reduce the redundant data, determine the way to connect database and when or where is needed to update the data.
Acceptance Criteria
- Team reviewed and approved the architectural design.
- Team reviewed and approved the ERD design.
- After testing the version of .Net supported by ECTWEB2 server, we decided to use ASP.NET 3.5 Framework.
Tasks
- Create a new solution in Visual Studio 2010 with ASP.NET 3.5. -- 10min -- Weisi
- Connect to Team Fundation Service -- 10min -- Matt
- Design and develop the DAL project. --1.5 hours -- Weisi
- Design and develop the Entities class library project. -- 3 hours -- Weisi
- Design and develop the basic controllers in BLL project. -- 5 hours -- Weisi
- Test the connection and the correctness of the data. -- 1.5 hours -- Weisi
- Report some small modifications on the database design. -- 20min -- Weisi
- Code verification -- 1 hour -- Matt Drew Wei
Notes
Code example of database connection
<![CDATA[
using System.Data;
using System.Data.OleDb;
public static string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:/ectserver/silver481/Database/SilverDB.accdb";
/// <summary>
/// Execute SQL command, returning DataSet
/// </summary>
/// <param name="SQLString">SQL command</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
]]>
2 Code examples of DML implementation
<1> Search products by type
<![CDATA[
/// <summary>
/// Search Product by Type
/// </summary>
/// <param name="productTypeId">producttype_id in table producttype</param>
/// <returns>the products in this type. return a DataSet</returns>
public DataSet SearchProductByType(int productTypeId)
{
string sqlStr = @"SELECT p.prod_name, p.prod_model, p.unit_price, p.prod_desc, pt.producttype_name, pt.producttype_desc, b.brand_name, v.vendor_name, pv.review_rating
FROM product p, producttype pt, brand b, vendor v, productreview pv
WHERE (p.producttype_id = pt.producttype_id
AND p.brand_id = b.brand_id
AND p.vendor_id = v.vendor_id
AND p.product_id = pv.product_id)
AND p.producttype_id = " + productTypeId.ToString();
return DatabaseConnection.Query(sqlStr, null);
}
]]>
<2> Place a order. Add records in customerorder table and customerorderitem table
<![CDATA[
/// <summary>
/// Place order
/// </summary>
/// <param name="customerId">the customer id</param>
/// <param name="products_quantity">the list of products and quantities, it's a Dictionary</param>
public void PlaceOrder(int customerId, Dictionary<int, int> products_quantity)
{
//add a record of customerorder
string sqlStr = @"INSERT INTO customerorder (customer_id, order_date) VALUES ("+ customerId + @", "+ System.DateTime.Now.ToOADate() +@")";
DAL.DatabaseConnection.ExecuteSql(sqlStr, null);
int id_num = DAL.DatabaseConnection.GetMaxID("customer_id", "customerorder");
//add the records of customerorderitem
foreach (KeyValuePair<int, int> pq in products_quantity)
{
sqlStr = @"(SELECT unit_price FROM product WHERE product_id=" + pq.Key + ")";
OleDbDataReader dr = DAL.DatabaseConnection.ExecuteReader(sqlStr, null);
double price;
if (dr.Read())
{
price = dr.GetDouble(0);
}
else
{
price = 0;
}
sqlStr = @"INSERT INTO customerorderitem (customerorder_id, product_id, quantity, item_price) VALUES (" + id_num + ", " + pq.Key + ", " + pq.Value + ", " + price.ToString() + ")";
DAL.DatabaseConnection.ExecuteSql(sqlStr, null);
}
}
]]>
ResultSet example - Display the detailed product information (The result of above function SearchProductByType() )
These information will show in the detailed page. First line are columns, second line are data queried from different tables. At this time we have little data.
<![CDATA[
product_name product_model unit_price product_desc producttype_name producttype_desc brand_name vendor_name review_rating
White Dobby Border Bath Towel 0010087 101.99 White border bath towel high quality Sheets Sheets 1888 Mills Vendor A 5
]]>
