Introduction to data handling in ASP.NET

1. Tao Ket noi voi database:

SqlConnection sqlconStudentData;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            sqlconStudentData = new SqlConnection(WebConfigurationManager.ConnectionStrings["StudentDataConnectionString"].ConnectionString);
            sqlconStudentData.Open();
        }
    }
2. Thu xem ket noi co thanh cong khong:

protected void btnConnect_Click(object sender, EventArgs e)
    {
        Controls.Clear();
        Response.Write("The database connection is successfully established.<br />");

        Response.Write("<strong>ConnectionString Name: </strong>" + WebConfigurationManager.
            ConnectionStrings["StudentDataConnectionString"].Name + "<br />");

        Response.Write("<strong>Provider Name: </strong>" + WebConfigurationManager.
            ConnectionStrings["StudentDataConnectionString"].ProviderName + "<br />");

        Response.Write("<strong>Total ConnectionString: </strong>" + WebConfigurationManager.
            ConnectionStrings.Count + "<br />");
    }
Link download : https://www.mediafire.com/?kuu8x2giksco31a

Data Source Controls In ASP.Net

I. Workshop 4-1
1. Ket noi voi du lieu XML:
Them moi 1 file XML vao trong website nhu sau:

<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies, 
      an evil sorceress, and her own childhood to become queen 
      of the world.</description>
   </book>
   <book id="bk103">
      <author>Corets, Eva</author>
      <title>Maeve Ascendant</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-11-17</publish_date>
      <description>After the collapse of a nanotechnology 
      society in England, the young survivors lay the 
      foundation for a new society.</description>
   </book>
   <book id="bk104">
      <author>Corets, Eva</author>
      <title>Oberon's Legacy</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2001-03-10</publish_date>
      <description>In post-apocalypse England, the mysterious 
      agent known only as Oberon helps to create a new life 
      for the inhabitants of London. Sequel to Maeve 
      Ascendant.</description>
   </book>
</catalog>

2. Keo TreeView va ket noi voi XML file qua XMLDataSource

Keo XMLDataSource va configure lai nhu sau:

3. Configure lai TreeView de no ket noi voi XMLDataSource

Add nhung dong muon hien thi bang cach configure lai column cua Tree View nhu sau:

sua lai cot TextField  thanh id cho book. 3 cai con lai configre TextField thanh innertext

Chay Trang:

II. Workshop 4-2

1. Tao 1 file sitemap nhu sau

<siteMap>
  <siteMapNode title="Home" description="Home" url="~/default.aspx">
    <siteMapNode title="Products" description="Our products"
      url="~/Products.aspx">
      <siteMapNode title="Hardware" description="Hardware choices"
        url="~/Hardware.aspx" />
      <siteMapNode title="Software" description="Software choices"
        url="~/Software.aspx" />
    </siteMapNode>
    <siteMapNode title="Services" description="Services we offer"
        url="~/Services.aspx">
        <siteMapNode title="Training" description="Training classes"
          url="~/Training.aspx" />
        <siteMapNode title="Consulting" description="Consulting services" 
          url="~/Consulting.aspx" />
        <siteMapNode title="Support" description="Supports plans" 
          url="~/Support.aspx" />
    </siteMapNode>
  </siteMapNode>
</siteMap>

2. Keo SiteMapDataSource va Menu vao roi configure Menu nhu sau:

Sua StaticDisplayLevels property cua Menu thanh 3 va chay trang



 Link download 2 bai: https://www.mediafire.com/?kyy5107791o9wtk

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