Sunday, April 29, 2012

VB.NET Oracle Call Function with Parameters

WHEN we need to call Oracle Database Function from vb.net Code Using System.Data.Oracle Client we will need to Pass Function Parameters and receive Function Return Value

so this action Can be Handled by the following Code Sample
NOTE: I have db function called ENCRYPT_STR S have two input arguments and one output return value so , i declared Oracle Command and define it's type as Stored Procedure and pass command text as SchemaName.FunctionName
then deal with command to add parameters and receive Return value and return it after .
    Public Function EncriptLoginData(ByVal username As String, ByVal EncKey As String) As String
        Dim Result_Encripted As String = String.Empty
                    'define Oracle Command
        Dim cmd As New Data.OracleClient.OracleCommand
        cmd.CommandType = Data.CommandType.StoredProcedure
        cmd.CommandText = "MAIN.ENCRYPT_STR"
        cmd.Connection = OConn
        OConn.ConnectionString = ConnString
        If OConn.State <> ConnectionState.Open Then OConn.Open()
        With cmd
            .Parameters.Add("var1", OracleType.VarChar, 100).Direction = ParameterDirection.Input
            .Parameters.Add("p_key", OracleType.VarChar, 100).Direction = ParameterDirection.Input
            .Parameters.Add("encUserName", OracleType.VarChar, 4000).Direction = ParameterDirection.ReturnValue
            .Parameters("var1").Value = username
            .Parameters("p_key").Value = EncKey

        End With
        cmd.ExecuteScalar()
        Result_Encripted = cmd.Parameters("encUserName").Value.ToString()
        cmd.Dispose()
        OConn.Close()
        OConn.Dispose()
        Return Result_Encripted
    End Function

No comments:

Post a Comment