Wednesday 11.2.11

Imports System.Web.Configuration
Imports System.Data.SqlClient
Imports System.Data


Partial Class DataReader1
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString
Dim con As New SqlConnection(connectionString)
Dim sql As String = "SELECT * FROM Employees"

'create a new instance of the SqlDataAdapter class that will retrieve the employee list
Dim da As New SqlDataAdapter(sql, con)
Dim ds As New DataSet()
da.Fill(ds, "Employees")

Dim htmlStr As New StringBuilder("")

'loop through the category records and build the HTML string
For Each dr As DataRow In ds.Tables("Employees").Rows
htmlStr.Append("

")
htmlStr.Append(dr("TitleOfCourtesy").ToString())
htmlStr.Append(" ")
htmlStr.Append(dr("LastName").ToString())
htmlStr.Append(", ")
htmlStr.Append(dr("FirstName").ToString())
htmlStr.Append("

")
Next

Response.Write(htmlStr.ToString())
End Sub
End Class

---

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Partial Class MultipleTablesAndRelationships
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString
Dim con As New SqlConnection(connectionString)

Dim sqlCat As String = "SELECT CategoryID, CategoryName FROM Categories"
Dim sqlProd As String = "SELECT ProductName, CategoryID FROM Products"

Dim da As New SqlDataAdapter(sqlCat, con)
Dim ds As New DataSet()

Try
con.Open()
'fill the dataSet with the Categories table.
da.Fill(ds, "Categories")

'change the command text, and retrieve the Products table
'You could also use another DataAdapter object for this task
da.SelectCommand.CommandText = sqlProd
da.Fill(ds, "Products")

Catch ex As Exception

Finally
con.Close()
End Try

'define the relationship between Categories and Products
Dim relat As New DataRelation( _
"CatsProds",
ds.Tables("Categories").Columns("CategoryID"),
ds.Tables("Products").Columns("CategoryID"))

'add the relationship to the DataSet
ds.Relations.Add(relat)

Dim htmlStr As New StringBuilder("")
'loop through the category records, and build the HTML string
For Each row As DataRow In ds.Tables("Categories").Rows
htmlStr.Append("")
htmlStr.Append(row("CategoryName").ToString())
htmlStr.Append("

")
'get the children (products) for this parent (Category)
Dim childRows As DataRow() = row.GetChildRows(relat)

'loop through all the products in this category
For Each childRow As DataRow In childRows
htmlStr.Append("
• ")
  htmlStr.Append(childRow("ProductName").ToString())
  htmlStr.Append("
")
Next
htmlStr.Append("

")
Next

Response.Write(htmlStr.ToString())

End Sub
End Class

---

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration


Partial Class DataGridView
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString
Dim con As New SqlConnection(connectionString)

Dim sql As String = "SELECT TOP 5 EmployeeID, TitleOfCourtesy, LastName, FirstName FROM Employees"

Dim da As New SqlDataAdapter(sql, con)
Dim ds As New DataSet()

'Fill the dataset
da.Fill(ds, "Employees")

'bind the original data to #1
gv1.DataSource = ds.Tables("Employees")
gv1.DataBind()

'sort by last name, and bind it to #2
Dim view2 As New DataView(ds.Tables("Employees"))
view2.Sort = "LastName"
gv2.DataSource = view2
gv2.DataBind()


'sort by first name and bind it to #3
Dim view3 As New DataView(ds.Tables("Employees"))
view3.Sort = "FirstName"
gv3.DataSource = view3
gv3.DataBind()

End Sub
End Class