CREATE Procedure CMRC_ProductDetail


(


    @ProductID    int,


    @ModelNumber  nvarchar(50) OUTPUT,


    @ModelName    nvarchar(50) OUTPUT,


    @ProductImage nvarchar(50) OUTPUT,


    @UnitCost     money OUTPUT,


    @Description  ntext


)


AS





SELECT 


    @ProductID    = ProductID,


    @ModelNumber  = ModelNumber,


    @ModelName    = ModelName,


    @ProductImage = ProductImage,


    @UnitCost     = UnitCost,


    @Description  = Description





FROM 


    CMRC_Products





WHERE 


    ProductID = @ProductID





GO

Public Function GetProductDetails(ByVal productID As Integer) As ProductDetails





            ' Create Instance of Connection and Command Object


            Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))


            Dim myCommand As SqlCommand = New SqlCommand("ProductDetail", myConnection)





            ' Mark the Command as a SPROC


            myCommand.CommandType = CommandType.StoredProcedure





            ' Add Parameters to SPROC


            Dim parameterProductID As SqlParameter = New SqlParameter("@ProductID", SqlDbType.Int, 4)


            parameterProductID.Value = productID


            myCommand.Parameters.Add(parameterProductID)





            Dim parameterUnitCost As SqlParameter = New SqlParameter("@UnitCost", SqlDbType.Money, 8)


            parameterUnitCost.Direction = ParameterDirection.Output


            myCommand.Parameters.Add(parameterUnitCost)





            Dim parameterModelNumber As SqlParameter = New SqlParameter("@ModelNumber", SqlDbType.NVarChar, 50)


            parameterModelNumber.Direction = ParameterDirection.Output


            myCommand.Parameters.Add(parameterModelNumber)





            Dim parameterModelName As SqlParameter = New SqlParameter("@ModelName", SqlDbType.NVarChar, 50)


            parameterModelName.Direction = ParameterDirection.Output


            myCommand.Parameters.Add(parameterModelName)





            Dim parameterProductImage As SqlParameter = New SqlParameter("@ProductImage", SqlDbType.NVarChar, 50)


            parameterProductImage.Direction = ParameterDirection.Output


            myCommand.Parameters.Add(parameterProductImage)





            Dim parameterDescription As SqlParameter = New SqlParameter("@Description", SqlDbType.NText)


            'parameterDescription.Direction = ParameterDirection.Output


            myCommand.Parameters.Add(parameterDescription)





            ' Open the connection and execute the Command


            myConnection.Open()


            myCommand.ExecuteNonQuery()


            myConnection.Close()





            ' Create and Populate ProductDetails Struct using


            ' Output Params from the SPROC


            Dim myProductDetails As ProductDetails = New ProductDetails()





            myProductDetails.ModelNumber = CStr(parameterModelNumber.Value)


            myProductDetails.ModelName = CStr(parameterModelName.Value)


            myProductDetails.ProductImage = CStr(parameterProductImage.Value).Trim()


            myProductDetails.UnitCost = CType(parameterUnitCost.Value, Decimal)


            myProductDetails.Description = CStr(parameterDescription.Value).Trim()





            Return myProductDetails





        End Function