More on database handling

I. WorkShop 3-1
1. Keo DetailsView vao trang vao ket noi voi database

sqlconCelinaBatteries = new SqlConnection("Data Source=HOANFLONG-PC\\SQLEXPRESS;Initial Catalog=CelinaBatteries;Persist Security Info=True;User ID=gc0806;Password=gc123456");
        if (!Page.IsPostBack)
        {
            panProductDetails.Visible = false;
            lblStatus.Text = "";

            sqlcomProducts = new SqlCommand("SELECT * FROM Products", sqlconCelinaBatteries);
            if (sqlconCelinaBatteries.State == ConnectionState.Open)
                sqlconCelinaBatteries.Close();
            sqlconCelinaBatteries.Open();

            sqldaProducts = sqlcomProducts.ExecuteReader();
            ddlProductList.DataSource = sqldaProducts;
            ddlProductList.DataTextField = "ProductName";
            ddlProductList.DataBind();
            sqlconCelinaBatteries.Close();

        }

2. Lay du lieu tu database vao DetailsView: Keo 1 nut co title la show details roi tao event cho no:

event cho show details:

panProductDetails.Visible = true;
        lblStatus.Text = "";
        txtQuantity.Text = "0";

        sqlcomProducts = new SqlCommand("SELECT * FROM Products where ProductName='" + ddlProductList.Text + "'", sqlconCelinaBatteries);
        if (sqlconCelinaBatteries.State == ConnectionState.Open)
            sqlconCelinaBatteries.Close();
        sqlconCelinaBatteries.Open();

        sqldaProducts = sqlcomProducts.ExecuteReader();
        dvProducDetails.DataSource = sqldaProducts;
        dvProducDetails.DataBind();
        sqlconCelinaBatteries.Close();
        flag = true;
3. update database qua DetailsView bang cachs mua hang:

Event cho nut Buy

 if (flag == true)
        {
            string id = "";
            string name = "";
            double price = 0;
            int quantity = 0;

            sqlcomProducts = new SqlCommand("SELECT * FROM Products where ProductName='" + ddlProductList.Text + "'", sqlconCelinaBatteries);
            if (sqlconCelinaBatteries.State == ConnectionState.Open)
                sqlconCelinaBatteries.Close();
            sqlconCelinaBatteries.Open();

            sqldaProducts = sqlcomProducts.ExecuteReader();

            if (sqldaProducts.Read())
            {
                id = sqldaProducts.GetString(0);
                name = sqldaProducts.GetString(1);
                price = sqldaProducts.GetDouble(4);

                try
                {
                    quantity = Convert.ToInt32(txtQuantity.Text);
                    Response.Redirect("BillInfo.aspx?id=" + id + "&name=" + name + "&price=" + price + "&quantity=" + txtQuantity.Text);
                    flag = false;
                }
                catch (Exception ex)
                {
                    lblStatus.Text = "Please enter quantity.";
                    
                }
                sqldaProducts.Close();
            }
        }

5. Trang BillInfo dung de hien thi noi dung mua hang:

lblProducID.Text = Request.QueryString["id"].ToString();
        lblName.Text = Request.QueryString["name"].ToString();
        lblTotal.Text = "$ " + (Convert.ToInt32(Request.QueryString["quantity"].ToString()) * Convert.ToDouble(Request.QueryString["price"].ToString())).ToString();

Link download: https://www.mediafire.com/?jzz9i43iie3efnp

I. WorkShop 3-2

1. Ket noi voi database:

 sqlconKnightUniversity = new SqlConnection("Data Source=HOANGLONG-PC\\SQLEXPRESS;Initial Catalog=KnightUniversity;Persist Security Info=True;User ID=gc0806;Password=gc123456");
        if (!Page.IsPostBack)
        {
            sqlcomStudents = new SqlCommand("select * from Students", sqlconKnightUniversity);
            sqlconKnightUniversity.Open();
            SqlDataReader sqldaStudents = sqlcomStudents.ExecuteReader();

            while (sqldaStudents.Read())
            {
                ddlStudents.Items.Add(sqldaStudents.GetString(1));
                
            }
            sqlconKnightUniversity.Close();
2. Lay du lieu kieu XML tu database ra trang

sqlcomStudents = new SqlCommand("SELECT * FROM Students", sqlconKnightUniversity);
        sqlconKnightUniversity.Open();
        SqlDataReader sqldr = sqlcomStudents.ExecuteReader();

        while(sqldr.Read())
        {
            if (sqldr.GetString(1) == ddlStudents.Text)
            {
                SqlXml sx = sqldr.GetSqlXml(2);
                XmlReader xr = sx.CreateReader();

                lblID.Text = sqldr.GetString(0);
                lblName.Text = sqldr.GetString(1);

                while (xr.Read())
                {
                    if (xr.IsStartElement("Age"))
                    {
                        lblAge.Text = xr.ReadString();
                    }
                    if (xr.IsStartElement("City"))
                    {
                        lblCity.Text = xr.ReadString();
                    }
                    if (xr.IsStartElement("Picture"))
                    {
                        imgPhoto.ImageUrl = xr.ReadString();
                    }
                }
            }
        }
        sqlconKnightUniversity.Close();

Link: https://www.mediafire.com/?w4gaur68zr27qj0