ConnectionManager.vb

Option Explicit On
Option Strict On

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


Public Class ConnectionManager
	Const connectionString As String = "Data Source=TJPAVC087;Initial Catalog=SIGADB;User Id=siga_user;Password=siga_pass;Max Pool Size=80;Min Pool Size=20;Pooling=true;"
	
	Private connection As SqlConnection
	Private Shared connectionManager As ConnectionManager
	
	Public Sub New()
		MyBase.New
	End Sub
	
	Public ReadOnly Property CurrentConnection() As SqlConnection
		Get
			If connection Is Nothing Then
				connection = New SqlConnection(connectionString)
				connection.Open()
			End If
			
			Return connection
		End Get
	End Property
	
	Public Sub Close()
		If connection IsNot Nothing Then
			connection.Close()
		End If
	End Sub
	
	Public Shared Function GetConnection() As SqlConnection
		If connectionManager Is Nothing
			connectionManager = New ConnectionManager()
		End If
		
		Return connectionManager.CurrentConnection()
	End Function
	
	Public Shared Sub Dispose()
		If connectionManager IsNot Nothing Then
			connectionManager.Close()
		End If
	End Sub
	
End Class

DataAccess.vb

Option Explicit On
Option Strict On

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

Public Class DataAccess
	
	Private connection As SqlConnection
	
	Public Sub New(ByVal connection As SqlConnection)
		Me.connection = connection
		MyBase.New
	End Sub
	
	Public Function LeeModelos(ByVal modelId As String) As DataSet
		Dim query As String = "SELECT ModelId, Model From Cat_Modelos Where ModelId = @modelId"
		
		Dim command As SqlCommand = New SqlCommand(query, connection)
		command.Parameters.Add(BuildParameter(modelId, "@modelId", SqlDbType.Int))

		Return ExecuteDataSet(command, "Modelos")
	End Function
	
	Public Sub InsertaSerie(ByVal modelId As String, ByVal serialNumber As String)
		Dim query As String = "INSERT INTO SerialNumber VALUES(@modelId, @serialNumber)"
		
		Dim command As SqlCommand = New SqlCommand(query, connection)
		command.Parameters.Add(BuildParameter(modelId, "@modelId", SqlDbType.Int))
		command.Parameters.Add(BuildParameter(modelId, "@serialNumber", SqlDbType.VarChar))
		
		ExecuteCommand(command)
	End Sub
	
	Private Sub ExecuteCommand(ByVal command As SqlCommand)
		command.ExecuteNonQuery()
	End Sub
	
	Private Function ExecuteDataSet(ByVal command As SqlCommand, ByVal tableName as String) As DataSet
		Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter()
		dataAdapter.TableMappings.Add(tableName)
		dataAdapter.SelectCommand = command
		
		Dim dataSet As DataSet = New DataSet()
		dataAdapter.Fill(dataSet)
		
		Return dataSet
	End Function
	
	Private Function BuildParameter(ByVal value As Object, ByVal name As String, ByVal type As SqlDbType) As SqlParameter
		Dim parameter As SqlParameter = New SqlParameter(name, type)
		parameter.Value = value
		Return parameter
	End Function
End Class

Ejemplo.vb

		Dim dataAcess As DataAccess = New DataAccess(ConnectionManager.GetConnection())
		Dim dataSet As DataSet = dataAccess.LeeModelos(5)
		
		dataAccess.InsertaSerie(2, "AABB0011")
		
		ConnectionManager.Close()