"select *from Bazis_pomel where '" & kod_pomel & "' "

Option Strict On
Option Explicit On 

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.VisualBasic

Namespace MyDataAccess

    Public Class DataLayer
        Implements IDisposable

        Private adtCommand As SqlDataAdapter     'sql data adapter
        Private cmdCommand As SqlCommand     'sql data adapter

        '----------------------------------------------------------------
        ' Sub New:
        '   Конструктор. 
        '----------------------------------------------------------------
        Public Sub New()
            MyBase.New()
            '
            ' Создаем датаадаптер
            '
            adtCommand = New SqlDataAdapter
            '
            ' создаем команду
            '
            cmdCommand = New SqlCommand
            cmdCommand.Connection = New SqlConnection(ConnectionString)
        End Sub

        '----------------------------------------------------------------
        ' Sub Dispose:
        '     Особождаем все ресурсы.
        '----------------------------------------------------------------
        Public Sub Dispose() Implements IDisposable.Dispose
            Dispose(True)
            GC.SuppressFinalize(True) ' as a service to those who might inherit from us
        End Sub

        '----------------------------------------------------------------
        ' Sub Dispose:
        '     Освобождаем память, занятую переменными класса.
        '----------------------------------------------------------------
        Protected Overridable Sub Dispose(ByVal disposing As Boolean)
            If Not disposing Then
                Exit Sub ' we're being collected, so let the GC take care of this object
            End If

            If Not cmdCommand Is Nothing Then
                If Not cmdCommand.Connection Is Nothing Then
                    cmdCommand.Connection.Dispose()
                End If
                cmdCommand.Dispose()
            End If

            If Not adtCommand Is Nothing Then
                adtCommand.Dispose()
                adtCommand = Nothing
            End If
        End Sub

        '----------------------------------------------------------------
        ' Sub PopulateCommandParam:
        '     Параметры для запроса
        '----------------------------------------------------------------
        Private Sub PopulateCommandParam(ByVal arParams() As SqlParameter, ByVal cmd As SqlCommand)
            Dim i As Integer

            For i = LBound(arParams) To UBound(arParams)
                cmd.Parameters.Add(arParams(i))
            Next
        End Sub

        '----------------------------------------------------------------
        ' Sub PopulateOutputParam:
        '     Возвращает результаты запроса
        '----------------------------------------------------------------
        Private Sub PopulateOutputParam(ByVal arParams() As SqlParameter, ByVal cmd As SqlCommand)
            Dim i As Integer

            For i = LBound(arParams) To UBound(arParams)
                If arParams(i).Direction = ParameterDirection.Output Then
                    arParams(i).Value = cmd.Parameters(arParams(i).ParameterName).Value
                End If
            Next
        End Sub

        Public Sub ExecSP(ByVal _proc As String, _
            ByRef intReturnVal As Integer, _
            Optional ByRef arParams() As SqlParameter = Nothing, _
            Optional ByRef dsResult As DataSet = Nothing)

            Try
                cmdCommand.CommandType = CommandType.StoredProcedure
                cmdCommand.CommandText = _proc
                If Not (arParams Is Nothing) Then
                    PopulateCommandParam(arParams, cmdCommand)
                End If

                'выполняем команду
                cmdCommand.Connection.Open()
                If dsResult Is Nothing Then
                    intReturnVal = cmdCommand.ExecuteNonQuery
                Else
                    adtCommand.SelectCommand = cmdCommand
                    intReturnVal = adtCommand.Fill(dsResult)
                End If

                If Not (arParams Is Nothing) Then
                    PopulateOutputParam(arParams, cmdCommand)
                End If
            Finally
                If Not cmdCommand Is Nothing Then
                    If Not cmdCommand.Connection Is Nothing Then
                        cmdCommand.Connection.Dispose()
                    End If
                    cmdCommand.Dispose()
                End If
                adtCommand.Dispose()
            End Try

        End Sub


        Public Sub ExecSQL(ByVal _sql As String, _
            ByRef intReturnVal As Integer, _
            Optional ByRef dsResult As DataSet = Nothing)

            Try
                cmdCommand.CommandType = CommandType.Text
                cmdCommand.CommandText = _sql

                'выполняем команду
                If dsResult Is Nothing Then
                    intReturnVal = cmdCommand.ExecuteNonQuery
                Else
                    adtCommand.SelectCommand = cmdCommand
                    intReturnVal = adtCommand.Fill(dsResult)
                End If
            Finally
                If Not cmdCommand Is Nothing Then
                    If Not cmdCommand.Connection Is Nothing Then
                        cmdCommand.Connection.Dispose()
                    End If
                    cmdCommand.Dispose()
                End If
                adtCommand.Dispose()
            End Try

        End Sub

    End Class
End Namespace

            Dim arParams( 0 ) As SqlParameter
            Dim intReturnVal As Integer

            Try
                arParams( 0 ) = New SqlParameter("@ID", SqlDbType.VarChar)
                arParams( 0 ).Direction = ParameterDirection.Input
                arParams( 0 ).Value = id

                With New DataLayer
                    .ExecSP("dbo.MyStoredProcedure", intReturnVal, arParams)
                End With

            Catch ex As Exception
                'обработчик ошибки
                Dim i As Integer
                i =  1 
            End Try