Tuesday 10.11.11

Imports System
Imports System.Data
Imports System.Data.SqlClient

Namespace Apress.VisualBasicRecipes.Chapter08

Public Class Recipe08_07

Public Shared Sub Main()
'create a new SqlConnection object
Using con As New SqlConnection

'configure the SqlConnection object's connection string.
con.ConnectionString = "Data Source=Alfred-PC\SQLExpress;Database=AdventureWorks;Integrated Security=SSPI"

'create and configure a new command
Using com As SqlCommand = con.CreateCommand
com.CommandType = CommandType.Text
com.CommandText = "SELECT e.BirthDate, c.FirstName, c.LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c ON e.EmployeeID = c.ContactID " & _
" ORDER BY e.BirthDate; SELECT * FROM humanResources.Employee"

'open the database connection and execute the example commands through the conneciton
con.Open()

'execute the command and obtain a DataReader
Using reader As SqlDataReader = com.ExecuteReader

Console.WriteLine("Employee Birthdays (By Age).")
While reader.Read
'process the first set of results and display the content of the result set
Console.WriteLine("{0,18:D} - {1} {2}", reader.GetDateTime(0), reader("FirstName"), reader(2))
End While
Console.WriteLine(Environment.NewLine)
'process the second set of results and display details
'abotu the columns and data types in the result set.


End Using
con.Close()
End Using
End Using
End Sub
End Class

End Namespace

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Data.SqlClient

Namespace Apress.VisualBasicRecipes.Chapter08

Public Class Recipe08_08

Public Shared Sub ConnectedExample()
'create a new SqlConnection object
Using con As New SqlConnection
'configure the SqlConnection object's connection strin.
con.ConnectionString = "Data Source=Alfred-PC\SQLExpress;Database=AdventureWorks;Integrated Security=SSPI;"
'create and configure a new command that includes the FOR XML AUTO clause.
Using com As SqlCommand = con.CreateCommand

com.CommandType = CommandType.Text
com.CommandText = "SELECT DepartmentID, [Name], " & _
"GroupName FROM HumanResources.Department FOR XML AUTO"
'open the database connection
con.Open()

'execute the command and retrieve and XmlReader to access the results
Using reader As XmlReader = com.ExecuteXmlReader
'loop through the reader
While reader.Read
'make sure we are dealing with an actual element of some type
If reader.NodeType = XmlNodeType.Element Then
'create an XElement object based on the current contents of the reader
Dim currentEle As XElement = XElement.ReadFrom(reader)
'display the anme of the current element and list any attributes that it may have
Console.WriteLine("Element: {0}", currentEle.Name)
If currentEle.HasAttributes Then
For i As Integer = 0 To currentEle.Attributes.Count - 1
Console.Write("{0}: {1}", currentEle.Attributes()(i).Name, currentEle.Attributes()(i).Value)
Next
End If
End If
End While
End Using
'close the database conneciton
con.Close()
End Using
End Using
End Sub

Public Shared Sub DisconnectedExample()

'thsi will be used to create the new XML document
Dim doc As New XDocument
'create a new SqlConnection object
Using con As New SqlConnection

'configure the SqlConnection object's connection string
con.ConnectionString = "Data Source=Alfred-PC\SQLExpress; Database=AdventureWorks;Integrated Security=SSPI;"

'create and configure a new command that includes the FOR XML AUTO clause
Using com As SqlCommand = con.CreateCommand


com.CommandType = CommandType.Text
com.CommandText = "SELECT DepartmentID, [Name], GroupName FROM HumanResources.Department FOR XML AUTO;"

'open the database connection
con.Open()

'Execute the command and retrieve and XmlReader to access the results
Using reader As XmlReader = com.ExecuteXmlReader
'create the parent element for the results
Dim root As XElement =
'loop through the reader and add each node as a child to the root
While reader.Read

'we need to make sur ewe are only dealing with some form of an element
If reader.NodeType = XmlNodeType.Element Then
Dim newChild As XNode = XElement.ReadFrom(reader)
root.Add(newChild)
End If

End While

'finally add the root element (and all of it its children
'to the new xml document.
doc.Add(root)
End Using


End Using
'close the database connection
con.Close()
End Using
End Sub

Public Shared Sub Main()
ConnectedExample()
Console.WriteLine(Environment.NewLine)
DisconnectedExample()
Console.WriteLine(Environment.NewLine)
End Sub
End Class
End Namespace

---