Monday 10.10.11

Imports System
Imports System.Linq
Imports System.Diagnostics

Namespace Apress.VisualBasicRecipes.Chapter06
Public Class Recipe06_14
Public Shared Sub Main()

'array to hold a set of strings
Dim myWishList = New String() {"XBox 360", "Rolex", "Serenity", "iPod iTouch", "Season 3 of BSG", "Dell XPS", "Halo 3"}

'an array holding a second set of strings
Dim myShoppingCart = New String() {"Shrek", "Swatch (Green)", "Sony Walkman", "XBox 360", "Season 3 of the Golden Girls", "Serenity"}

' returns elemnts from myWishList that are NOT in myShoppingCart
Dim result1 = myWishList.Except(myShoppingCart)

Console.WriteLine("Items in the wish list that were not in the shopping cart:")
For Each item In result1
Console.WriteLine(item)
Next
Console.WriteLine()

'returns elements that are common in both myWishList and myShoppingCart
Dim result2 = myWishList.Intersect(myShoppingCart)

Console.WriteLine("Matching items from both lists:")
For Each Item In result2
Console.WriteLine(Item)
Next
Console.WriteLine()

'returns all elements from myWishList and myShoppingCart without duplicates
Dim result3 = myWishList.Union(myShoppingCart)

Console.WriteLine("All items from both lists (no duplicates):")
For Each item In result3
Console.WriteLine(item)
Next

Console.WriteLine()

'returns all elements from myWishList and myShoppingCart including duplicates
Dim result4 = myWishList.Concat(myShoppingCart)

Console.WriteLine("All items from both lists (with duplicates):")
For Each item In result4
Console.WriteLine(item)
Next

'wait to continue
Console.WriteLine()
Console.WriteLine("Main method complete. Press Enter")
Console.ReadLine()


End Sub

End Class
End Namespace

---

Imports System
Imports System.Linq
Imports System.Diagnostics

Namespace Apress.VisualBasicRecipes.Chapter06
Public Class Recipe06_15
Public Class Tool
Public Name As String
End Class

Public Class Clothes
Public Name As String
End Class

Public Shared Sub Main()
'from example - nongeneric collection

Dim employeeList As New ArrayList
employeeList.Add("Todd")
employeeList.Add("Alex")
employeeList.Add("Joe")
employeeList.Add("Todd")
employeeList.Add("Ed")
employeeList.Add("David")
employeeList.Add("Mark")

'you can't normall use standard query operators on an ArrayList (IEnumberable) unless you strongly type the From clause
'Strongly typeing the From clause creates a call to the Cast function, shown below.
Dim queryableList = employeeList.Cast(Of String)()
Dim query = From name In queryableList

For Each name In query
Console.WriteLine(name)
Next

Dim shoppingCart As New ArrayList

shoppingCart.Add(New Clothes With {.Name = "Shirt"})
shoppingCart.Add(New Clothes With {.Name = "Socks"})
shoppingCart.Add(New Tool With {.Name = "Hammer"})
shoppingCart.Add(New Clothes With {.Name = "Hat"})
shoppingCart.Add(New Tool With {.Name = "Screw Driver"})
shoppingCart.Add(New Clothes With {.Name = "Pants"})
shoppingCart.Add(New Tool With {.Name = "Drill"})

'attempting to iterate through the results would generate
' an InvalidCastException because some items cannot be cast
' to the appropriate type. However, some items maybe becast prior to hitting the exception.
Dim queryableList2 = shoppingCart.Cast(Of Clothes)()

Console.WriteLine("Cast (using Cast) all items to 'clothes':")
Try
For Each item In queryableList2
Console.WriteLine(item.Name)
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Console.WriteLine()

'oftype is similar to cast but wouldn't cause thet exception as shown in the previous example
'only the items that can be successfully cast will be returned
Dim queryableList3 = shoppingCart.OfType(Of Clothes)()

Console.WriteLine("Cast (using OfType) all items to 'Clothes':")
For Each item In queryableList3
Console.WriteLine(item.Name)
Next
End Sub



End Class
End Namespace

---

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

Namespace Apress.VisualBasicRecipes.Chapter08

Public Class Recipe08_01

Public Shared Sub SqlConnectionExample()

'Configure an empty SqlConnectino 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;"
'open the database connection
con.Open()

'display the information about the connection

If con.State = ConnectionState.Open Then
Console.WriteLine("SqlConnection Information:")
Console.WriteLine(" Connection State = " & con.State)
Console.WriteLine(" Connection String = " & con.ConnectionString)
Console.WriteLine(" Database Source = " & con.DataSource)
Console.WriteLine(" Database = " & con.Database)
Console.WriteLine(" Server Version = " & con.ServerVersion)
Console.WriteLine(" Workstation Id = " & con.WorkstationId)
Console.WriteLine(" Timeout = " & con.ConnectionTimeout)
Console.WriteLine(" Packet Size = " & con.PacketSize)
Else
Console.WriteLine("SqlConnection failed to open.")
Console.WriteLine("Conenction State = " & con.State)
End If

con.Close()
End Using
End Sub


Public Shared Sub OleDbConnectionExample()
Using con As New OleDbConnection
'configue the SqlConnection object's connection string.
con.ConnectionString = "Provider=SQLOLEDB; Data Source=Alfred-PC\SQLExpress;Initial Catalog=AdventureWorks; Integrated Security=SSPI;"
'open the database conncetion
con.Open()

'display the information about the connection
If con.State = ConnectionState.Open Then
Console.WriteLine("OleDbConnection Information:")
Console.WriteLine(" Connection State = " & con.State)
Console.WriteLine(" Connection String = " & con.ConnectionString)
Console.WriteLine(" Database Source = " & con.DataSource)
Console.WriteLine(" Database = " & con.Database)
Console.WriteLine(" Server Version = " & con.ServerVersion)
Console.WriteLine(" Timeout = " & con.ConnectionTimeout)
Else
Console.WriteLine("OleDbConnection failed to open.")
Console.WriteLine(" Connection State = " & con.State)
End If

con.Close()

End Using
End Sub
Public Shared Sub Main()
'open the connection using SqlConnection
SqlConnectionExample()
Console.WriteLine()
'open conneciton using OleDbConnection
OleDbConnectionExample()
Console.WriteLine(Environment.NewLine)



End Sub

End Class

End Namespace

---

Imports System
Imports System.Data.SqlClient

Namespace Apress.VisualBasicRecipes.Chapter08
Public Class Recipe08_02

Public Shared Sub Main()

'obtain a pooled connection
Using con As New SqlConnection
'configue the SqlConnection object's connection string.
con.ConnectionString = "Data Source=Alfred-PC\SQLExpress;Database=AdventureWorks;" & _
"Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=15;" & _
"Connection Reset=True;Connection Lifetime=600;Pooling=True;"

'open the database conenction.
con.Open()



'access the database

'close the database connection
'this returns the connection to the pool for reuse.
con.Close()

'at the end of the using block, the Dispose class Close
'which returns the connection back to the pool for reuse

End Using
'Obtain a nonpooled connection
Using con As New SqlConnection
'configure the SqlConnection object's connection string.
con.ConnectionString = "Data Source=Alfred-PC\SQLExpress;Database=AdventureWorks;Integrated Security=SSPI;Pooling=False;"
'open the database connection
con.Open()

'access the database
con.Close()
End Using


End Sub

End Class

End Namespace

---

Imports System
Imports System.Data.SqlClient

Namespace Apress.VisualBasicRecipes.Chapter08
Public Class Recipe08_03

Public Shared Sub Main()
'configure the SqlConnection object's connection string
Dim conString As String = "Data Source=Alfred-PC\SQLExpress;Database=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=15;" & _
"Connection Lifetime=600;"

'parse the SQL Server connection string an display the component configuration parameters
Dim sb1 As New SqlConnectionStringBuilder(conString)

Console.WriteLine("Parsed SQL Connection String Parameters:")
Console.WriteLine(" Database Source = " & sb1.DataSource)
Console.WriteLine(" Database = " & sb1.InitialCatalog)
Console.WriteLine(" Use Integrated Security = " & sb1.IntegratedSecurity)

Console.WriteLine(" Min Pool SIze = " & sb1.MinPoolSize)
Console.WriteLine(" Max Pool Size = " & sb1.MaxPoolSize)
Console.WriteLine(" LIfetime = " & sb1.LoadBalanceTimeout)

'build a connection string frmo component parameters and display it
Dim sb2 As New SqlConnectionStringBuilder(conString)

sb2.DataSource = "Alfred-PC\SQLExpress"
sb2.InitialCatalog = "AdventureWorks"
sb2.IntegratedSecurity = True
sb2.MinPoolSize = 5
sb2.MaxPoolSize = 15
sb2.LoadBalanceTimeout = 700

Console.WriteLine()
Console.WriteLine("Constructed connection string:")
Console.WriteLine(" " & sb2.ConnectionString)

'wait to continue
Console.WriteLine()
Console.ReadLine()


End Sub

End Class

End Namespace

---

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

Namespace Apress.VisualBasicRecipes.Chapter08

Public Class Recipe08_05

Public Shared Sub ExecuteNonQueryExample(ByVal con As IDbConnection)

'create and configure a new command.
Dim com As IDbCommand = con.CreateCommand
com.CommandType = CommandType.Text
com.CommandText = "UPDATE HumanResources.Employee SET Title = " & _
"'Production Supervisor' WHERE EmployeeID = 24;"

'execute the command and process the result.
Dim result As Integer = com.ExecuteNonQuery
If result = 1 Then
Console.WriteLine("Employee Title updated.")
ElseIf result > 1 Then
Console.WriteLine("{0} employee titles updated.", result)
Else
Console.WriteLine("Emploee title not updated.")
End If
End Sub


Public Shared Sub ExecuteReaderExample(ByVal con As IDbConnection)
'create and configure a new command
Dim com As IDbCommand = con.CreateCommand
com.CommandType = CommandType.Text
com.CommandText = "SET ROWCOUNT 10; SELECT " & _
"Production.Product.Name, Production.Product.ListPrice FROM " & _
"Production.Product ORDER BY Production.Product.ListPrice DESC; SET ROWCOUNT 0;"

'execute the command and process the results
Using reader As IDataReader = com.ExecuteReader

While reader.Read
'display the product details.
Console.WriteLine(" {0} = {1}", reader("Name"), reader("ListPrice"))
End While
End Using
End Sub

Public Shared Sub ExecuteScalarExample(ByVal con As IDbConnection)
'create and configure a new command
Dim com As IDbCommand = con.CreateCommand
com.CommandType = CommandType.Text
com.CommandText = "SELECT COUNT(*) FROM HumanResources.Employee;"

'execute the command and cast the result
Dim result As Integer = CInt(com.ExecuteScalar)

Console.WriteLine("Employee count = " & result)
End Sub

'the example in this recipe demonstrates hwo to use a command object to execute a few different
'sql statements against a database.

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;"

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

ExecuteNonQueryExample(con)
Console.WriteLine(Environment.NewLine)
ExecuteReaderExample(con)
Console.WriteLine(Environment.NewLine)
ExecuteScalarExample(con)
Console.WriteLine(Environment.NewLine)

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

End Class
End Namespace