CREATE PROCEDURE AvgLast  
(@ag integer Output,
@dateA DateTime,
@dateB DateTime,
@prodSku varchar(6)
)
AS
 SELECT     AVG(Qty) AS @ag  FROM   dbo.SalesH
WHERE     (ProdSku = '@prodsku') AND (SDate BETWEEN @dateA and @dateB)
GO
 

 Public Function GetAvg(ByVal DateA As DateTime, ByVal DateB As DateTime, ByVal a_prodsku As String) As String

        Dim paramsIn As New DbParams
        Dim paramsOut As New DbParams
        Dim param1 As New DbParam("DateA", DbType.DateTime, False, DateA)
        Dim param2 As New DbParam("Dateb", DbType.DateTime, False, DateB)
        Dim param3 As New DbParam("prodsku", DbType.Int16, False, a_prodsku)
        Dim param As New DbParam("ag", DbType.AnsiString, False)

        paramsIn.Add("DateA", param1)
        paramsIn.Add("dateb", param2)
        paramsIn.Add("prodsku", param3)
        paramsOut.Add("ag", param)
        Dim ret As String = ""
        Dim cm As IDbCommand
        Dim rd As IDataReader
        cm = _DataAccess.getCommand(paramsIn, paramsOut, "AvgLast")
        Try
            rd = cm.ExecuteReader
            If rd.Read Then
                ret = rd.Item("")
            End If
        Catch ex As Exception
            ret = ex.Message
        End Try
        Return ret

    End Function
 

 
Friend Overloads Function getCommand(ByVal paramsIn As DbParams, ByVal paramsOut As DbParams, ByVal spName As String) As IDbCommand
            Dim cn As IDbConnection
            cn = m_connects.getConnection()
            Dim cmd As IDbCommand = cn.CreateCommand
            cmd.CommandText = spName
            cmd.CommandType = CommandType.StoredProcedure

            loadParams(cmd, paramsIn, ParameterDirection.Input)
            loadParams(cmd, ParamsOut, ParameterDirection.Output)
end Function

  Private Overloads Sub loadParams(ByVal cmd As IDbCommand, ByVal aParams As DbParams, ByVal pramDir As ParameterDirection)
            If aParams Is Nothing Then Exit Sub

            Dim x As Integer = 0
            Dim en As IEnumerator = aParams.Values.GetEnumerator


            While en.MoveNext

                Dim p As DbParam = DirectCast(en.Current, DbParam)
                Dim param As IDbDataParameter = cmd.CreateParameter()
                'Try
                '    param.DbType = Me.inferType(de.Value)
                'Catch ex As Exception
                '    Throw New Exception(de.Key & " value - unable to infer type.")
                'End Try
                param.DbType = p.ParamType
                param.Value = p.ParamValue
                param.Direction = pramDir
                param.ParameterName = p.ParamName
                cmd.Parameters.Insert(x, param)

            End While

        End Sub
 

quote:

---

Originally posted by alboi

why do i need the "(" in those declarations ??

---

Option Explicit On 
Option Strict On
Namespace DataAccessFramework
    Public Class DbParam
        Private _FieldName As String
        Private _ParamType As DbType
        Private _ParamValue As Object
        Private _IsPrimaryKey As Boolean

        Public Sub New(ByVal aFieldName As String, ByVal aParamType As DbType, ByVal aIsPrimaryKey As Boolean)
            MyBase.new()
            _FieldName = aFieldName
            _ParamType = aParamType
            _IsPrimaryKey = aIsPrimaryKey
        End Sub
        Public Sub New(ByVal aFieldName As String, ByVal aParamType As DbType, ByVal aIsPrimaryKey As Boolean, ByVal aParamValue As Object)
            MyBase.new()
            _FieldName = aFieldName
            _ParamType = aParamType
            _ParamValue = aParamValue
            _IsPrimaryKey = aIsPrimaryKey

            Select Case _ParamType

                Case Is = DbType.DateTime
                    Dim t1 As New DateTime(1753, 1, 1)
                    Dim t2 As New DateTime(9999, 12, 31)

                    Dim t As DateTime = DirectCast(_ParamValue, DateTime)

                    If (t < t1) Or (t > t2) Then
                        _ParamValue = DBNull.Value
                    End If
                Case Else
                    If _ParamValue Is Nothing Then
                        _ParamValue = DBNull.Value
                    End If

            End Select
        End Sub
        Public ReadOnly Property FieldName() As String
            Get
                Return _FieldName
            End Get
        End Property

        Public ReadOnly Property ParamType() As DbType
            Get
                Return _ParamType
            End Get
        End Property


        Public Property ParamValue() As Object
            Get
                Return _ParamValue
            End Get
            Set(ByVal Value As Object)
                _ParamValue = Value
            End Set
        End Property
        Public ReadOnly Property ParamName() As String
            Get
                Return "@" & _FieldName
            End Get
        End Property
        Public ReadOnly Property IsPrimaryKey() As Boolean
            Get
                Return _IsPrimaryKey
            End Get
        End Property

    End Class
End Namespace

Namespace DataAccessFramework
    Public Class DbParams
        Inherits Hashtable
        Public Shadows Sub Add(ByVal key As String, ByVal value As DbParam)
            MyBase.Add(key, value)
        End Sub
        Public Shadows Property Item(ByVal parameterName As String) As DbParam
            Get
                Return MyBase.Item(parameterName)
            End Get
            Set(ByVal Value As DbParam)
                MyBase.Item(parameterName) = Value
            End Set
        End Property

        Public Shadows Function Contains(ByVal parameterName As String) As Boolean
            Return MyBase.Contains(parameterName)
        End Function

        Public Shadows Function ContainsKey(ByVal parameterName As String) As Boolean
            Return MyBase.ContainsKey(parameterName)
        End Function

        Public Shadows Function ContainsValue(ByVal aParam As DbParam) As Boolean
            Return MyBase.ContainsValue(aParam)
        End Function

        Public Shadows Sub Remove(ByVal parameterName As String)
            MyBase.Remove(parameterName)
        End Sub
    End Class
End Namespace

CREATE PROCEDURE AvgLast
(
    @ag integer Output,
    @dateA DateTime,
    @dateB DateTime,
    @prodSku varchar(6)
)
AS
SELECT @ag = AVG(Qty)
FROM   dbo.SalesH
WHERE      ProdSku = @prodsku
       AND SDate BETWEEN @dateA and @dateB
       AND Qty IS NOT NULL
GO

DECLARE @ag int
EXEC AvgLast  
     @ag = @ag OUTPUT, 
     @dateA = '01Jan2005',
     @dateB = '31Dec2005',
     @prodSku = '123456'
SELECT [@ag OUTPUT] = @ag