Tuesday 11.1.11

Module Module1

Sub Main()
Dim input() As String = {"Brachiosaurus", _
"Amargasaurus", _
"Mamenchispurus"}
Dim dinosaurs As New List(Of String)(input)

Console.WriteLine(vbLf & "Capacity: {0}", dinosaurs.Capacity)
Console.WriteLine()
For Each dinosaur As String In dinosaurs
Console.WriteLine(dinosaur)
Next

Console.WriteLine(vbLf & "AddRange(dinosaurs)")
dinosaurs.AddRange(dinosaurs)

Console.WriteLine()
For Each dinosaur As String In dinosaurs
Console.WriteLine(dinosaur)
Next

input = New String() {"Tyrannosaurus", _
"Doinonychus", _
"Velociraptor"}

Console.WriteLine(vbLf & "InsertRange(3, Input)")
dinosaurs.InsertRange(3, input)
Console.WriteLine()
For Each dinosaur As String In dinosaurs
Console.WriteLine(dinosaur)
Next

Console.WriteLine(vbLf & "output = dinosaurs.GetRange(2,3).ToArray")
Dim output() As String = dinosaurs.GetRange(2, 3).ToArray()

Console.WriteLine()
For Each dinosaur As String In output
Console.WriteLine(dinosaur)
Next
End Sub

End Module

---

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration


Public Class EmployeeDB
Dim connectionString As String

Public Sub New()
' get default connection string
connectionString = WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString

End Sub

Public Sub New(ByVal connectionStringName As String)
'get teh specified connection string.
connectionString = WebConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString
End Sub

Public Function InsertEmployee(ByVal emp As EmployeeDetails) As Integer
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("InsertEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar, 10))
cmd.Parameters("@FirstName").Value = emp.FirstName
cmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar, 20))
cmd.Parameters("@LastName").Value = emp.LastName
cmd.Parameters.Add(New SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25))
cmd.Parameters("@TitleOfCourtesy").Value = emp.TitleOfCourtesy
cmd.Parameters.Add(New SqlParameter("@EmployeeID", SqlDbType.Int, 4))
cmd.Parameters("@EmployeeID").Direction = ParameterDirection.Output

Try
con.Open()
cmd.ExecuteNonQuery()
Return CInt(cmd.Parameters("@EmployeeID").Value)
Catch err As SqlException
'replace the error with something less specific
'you could also log the error now
Throw New ApplicationException("Data Error.")
Finally
con.Close()
End Try

End Function

Public Sub DeleteEmployee(ByVal employeeID As Integer)
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("DeleteEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@EmployeeID", SqlDbType.Int, 4))
cmd.Parameters("@EmployeeID").Value = employeeID
Try
con.Open()
cmd.ExecuteNonQuery()
Catch err As SqlException
Throw New ApplicationException("Data error.")
Finally
con.Close()
End Try
End Sub

Public Function GetEmployee(ByVal employeeID As Integer) As EmployeeDetails
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("GetEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@EmployeeID", SqlDbType.Int, 4))
cmd.Parameters("@EmployeeID").Value = employeeID
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
'get the first row
reader.Read()
Dim emp As EmployeeDetails = New EmployeeDetails(CInt(reader("EmployeeID")), CStr(reader("FirstName")), CStr(reader("LastName")), CStr(reader("TitleOfCourtesy")))
reader.Close()
Return emp
Catch err As SqlException
Throw New ApplicationException("Data error.")
Finally
con.Close()
End Try
End Function

Public Function GetEmployees() As EmployeeDetails()
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("GetAllEmployees", con)
cmd.CommandType = CommandType.StoredProcedure

'create a collection for all the employee records
Dim Employees As New List(Of EmployeeDetails)
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
Do While reader.Read()
Dim emp As New EmployeeDetails(CInt(reader("EmployeeID")), CStr(reader("FirstName")), CStr(reader("LastName")), CStr(reader("TitleOfCourtesy")))
employees.Add(emp)
Loop
reader.Close()
Return employees.ToArray
Catch err As SqlException
Throw New ApplicationException("Data error.")
Finally
con.Close()
End Try
End Function

Public Function CountEmployees() As Integer
Dim con As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand("CountEmployees", con)
cmd.CommandType = CommandType.StoredProcedure
Try
con.Open()
Return CInt(cmd.ExecuteScalar())
Catch err As SqlException
Throw New ApplicationException("Data error.")
Finally
con.Close()

End Try
End Function

Public Sub UpdateEmployee(ByVal EmployeeID As Integer, ByVal firstName As String, ByVal lastName As String, ByVal titleOfCourtesy As String)
Dim con As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand("UpdateEmployee", con)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar, 10))
cmd.Parameters("@FirstName").Value = firstName
cmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar, 20))
cmd.Parameters("@LastName").Value = lastName
cmd.Parameters.Add(New SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25))
cmd.Parameters("@TitleOfCourtesy").Value = titleOfCourtesy
cmd.Parameters.Add(New SqlParameter("@EmployeeID", SqlDbType.Int, 4))
cmd.Parameters("@EmployeeID").Value = EmployeeID
Try
con.Open()
cmd.ExecuteNonQuery()
Catch err As SqlException
Throw New ApplicationException("Data error.")
Finally
con.Close()

End Try
End Sub



End Class

---

Partial Class ComponentTest
Inherits System.Web.UI.Page

Private db As EmployeeDB = New EmployeeDB()

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
WriteEmployeesList()
Dim empID As Integer = db.InsertEmployee(New EmployeeDetails(0, "Marco", "Bellinaso", "Mr"))
HtmlContent.Text &= "
Inserted 1 employee
"
WriteEmployeesList()
db.DeleteEmployee(empID)
HtmlContent.Text &= "
Deleted 1 employee
"
WriteEmployeesList()
End Sub

Private Sub WriteEmployeesList()
Dim htmlStr As StringBuilder = New StringBuilder("")
Dim numEmployees As Integer = db.CountEmployees()
htmlStr.Append("
Total employees: ")
htmlStr.Append(numEmployees.ToString())
htmlStr.Append("

")
Dim employees As Array = db.GetEmployees()
For Each emp As EmployeeDetails In employees
htmlStr.Append("

")
htmlStr.Append(emp.EmployeeID)
htmlStr.Append(" ")
htmlStr.Append(emp.TitleOfCourtesy)
htmlStr.Append(" ")
htmlStr.Append(emp.FirstName)
htmlStr.Append(", ")
htmlStr.Append(emp.LastName)
htmlStr.Append("

")
Next emp
htmlStr.Append("
")
HtmlContent.Text &= htmlStr.ToString()


End Sub

End Class