Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Imports Vishwa.Example.Business
Namespace Vishwa.Example.Data
Public Class DataAccess
' Author : Vishwa Mohan
' Date : 10/15/2006
' Class : Data Accesss Manager
‘ Design Pattern: Singleton
' Purpose: An Example to demonstrate Customer Data Access Management
Private Shared instance As New DataAccess
#Region "--Customer Functions--- "
Public Shared Function GetCustomers(ByVal custID As Integer) As Customer
Dim custRecord As New Customer
Using connection As New SqlConnection(GetConnectionString())
Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
command.Connection.Open()
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim tempCustomer As New Customer
custRecord.CustID = CInt(reader.Item("Cust_ID"))
custRecord.CustName = reader.Item("Cust_Name").ToString
custRecord.CustAddress = reader.Item("Cust_Address").ToString
custRecord.CustDOB = CDate(reader.Item("Cust_DOB"))
custRecord.DateCreated = CDate(reader.Item("Date_Created"))
custRecord.DateModified = CDate(reader.Item("Date_Modified"))
End Using
End Using
End Using
Return custRecord
End Function
Public Shared Function GetAllCustomers() As Generic.List(Of Customer)
Dim list As New Generic.List(Of Customer)()
Using connection As New SqlConnection(GetConnectionString())
Using command As New SqlCommand("dbo.Usp_GetCustomers", connection)
command.CommandType = CommandType.StoredProcedure
command.Connection.Open()
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
Do While (reader.Read())
Dim tempCustomer As New Customer
tempCustomer.CustID = CInt(reader.Item("Cust_ID"))
tempCustomer.CustName = reader.Item("Cust_Name").ToString
tempCustomer.CustAddress = reader.Item("Cust_Address").ToString
tempCustomer.CustDOB = CDate(reader.Item("Cust_DOB"))
tempCustomer.DateCreated = CDate(reader.Item("Date_Created"))
tempCustomer.DateModified = CDate(reader.Item("Date_Modified"))
list.Add(tempCustomer)
Loop
End Using
End Using
End Using
Return list
End Function
Public Shared Function InsertCustomer(ByVal custInfo As Customer) As Integer
Dim returnValue As Integer = -1
Using connection As New SqlConnection(GetConnectionString())
Using command As New SqlCommand("dbo.Usp_InsertCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custInfo.CustName
.Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custInfo.CustDOB
.Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custInfo.CustAddress
.Connection.Open()
.ExecuteNonQuery()
returnValue = CInt(command.Parameters("@ReturnID").Value)
End With
End Using
End Using
Return returnValue
End Function
Public Shared Function UpdateCustomer(ByVal custInfo As Customer) As Integer
Dim returnValue As Integer = -1
Using connection As New SqlConnection(GetConnectionString())
Using command As New SqlCommand("dbo.Usp_UpdateCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custInfo.CustID
.Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custInfo.CustName
.Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custInfo.CustDOB
.Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custInfo.CustAddress
.Connection.Open()
.ExecuteNonQuery()
returnValue = CInt(command.Parameters("@ReturnID").Value)
End With
End Using
End Using
Return returnValue
End Function
Public Shared Function DeleteCustomer(ByVal custInfo As Customer) As Integer
Dim returnValue As Integer = -1
Using connection As New SqlConnection(GetConnectionString())
Using command As New SqlCommand("dbo.Usp_DeleteCustomer", connection)
With command
.CommandText = "Usp_DeleteCustomer"
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@ReturnID", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custInfo.CustID
.Connection.Open()
.ExecuteNonQuery()
returnValue = CInt(command.Parameters("@ReturnID").Value)
End With
End Using
End Using
Return returnValue
End Function
#End Region
#Region "Common Connection Functions"
Private Shared Function GetConnectionString() As String
Try
Return ConfigurationManager.ConnectionStrings.Item("LocalSqlServer").ConnectionString
Catch exc As Exception
Return ""
End Try
End Function
#End Region
#Region "Private Constructor"
Private Sub New()
End Sub
#End Region
End Class
End Namespace