Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
Option Strict On
Option Explicit On
Imports Microsoft.VisualBasic
Imports System.Data.OracleClient
Imports System.Data
Imports System.Configuration
Imports System
Imports System.Web.HttpContext
Imports System.Diagnostics
 
Public Class DataAccess
    Private Shared oConn As New OracleConnection()
    Private Shared sConnString As String
    Private _objCommand As OracleCommand
    Private _objAdaptor As New OracleDataAdapter
    Dim _strMsg As String = ""
    Dim _strMsgCode As String = ""
 
 
 
    Public Function GetDataset(ByVal sSQL As String) As DataSet
        Return GetDataset(sSQL, "")
    End Function
    Public Function GetDataset(ByVal sSQL As String, ByVal sDataTableName As String) As DataSet
 
        Dim ObjDs As New DataSet
        Try
            oConn = GetConnection()
 
            Dim ObjDa As New OracleDataAdapter(sSQL, oConn)
            If sDataTableName.Trim <> "" Then
                ObjDa.Fill(ObjDs, sDataTableName)
            Else
                ObjDa.Fill(ObjDs)
            End If
            oConn.Close()
            oConn = Nothing
 
        Catch ex As Exception
            Dim bDebuggingLocally As Boolean = (ConfigurationManager.AppSettings("bDebuggingLocally").ToString.ToLower.Trim = "true")
            CommonFunctions.LogEvent( _
                ex.ToString.ToString, _
                Diagnostics.EventLogEntryType.Error, _
                sSQL, _
                Current.Server.UrlEncode(Current.Request.ServerVariables("script_name")))
            Throw ex
        End Try
        Return ObjDs
    End Function
 
    Public Shared Function ExecuteQuery(ByVal sSQL As String) As Integer
        oConn = GetConnection()
        oConn.Open()
 
        Dim cmdExecute As New OracleCommand
 
        cmdExecute.Connection = oConn
        cmdExecute.CommandText = sSQL
 
        Dim iRows As Integer = cmdExecute.ExecuteNonQuery()
        ExecuteQuery = iRows
 
        oConn.Close()
        oConn = Nothing
 
    End Function
 
    Public Shared Function GetValue(ByVal sSQL As String) As String
 
        Dim myReturn As String = ""
        'used to retrieve one single value
        oConn = GetConnection()
 
        Dim cmdExecute As New OracleCommand
 
        cmdExecute.Connection = oConn
        cmdExecute.CommandType = CommandType.Text
        cmdExecute.CommandText = sSQL
 
        Dim dr As OracleDataReader
 
        oConn.Open()
 
        dr = cmdExecute.ExecuteReader()
 
        Dim returnStr As String = ""
        Dim x As Integer
 
        dr.Read()
        If dr.HasRows Then
            For x = 0 To dr.FieldCount - 1
                If IsDBNull(dr.Item(x)) Then
                    returnStr += " ~"
                Else
                    returnStr += dr.Item(x).ToString & "~"
                End If
            Next
 
            myReturn = Mid(returnStr, 1, returnStr.Length - 1)
 
        End If
 
 
        dr.Close()
        oConn.Close()
 
        dr = Nothing
        oConn = Nothing
 
        Return myReturn
 
    End Function
 
    Public Sub CleanUp(ByRef objCom As OracleCommand)
        Try
            If objCom IsNot Nothing Then
                objCom.Connection.Close()
                objCom.Connection.Dispose()
                objCom.Dispose()
            End If
        Catch ex As Exception
            Throw ex
        End Try
 
    End Sub
 
    Private Shared Function GetConnection() As OracleConnection
        Try
 
    <!--$debug - for dev/qa/prod, set to dev/qa/prod -->
    <addkey="DBConnection"value="dev"/>
 
            Select Case ConfigurationManager.AppSettings("DBConnection").ToLower()
                Case "dev"
                    sConnString = "data source=dev.COM;user id=devuser;password=devpassword "
                Case "qa"
                    sConnString = "data source=qa.COM;user id=qauser;password=qapassword "
                Case "prod"
                    sConnString = "data source=prod.COM;user id= prod user;password= prod password "
            End Select
 
        Catch ex As Exception
            Throw ex
        End Try
        Return New OracleConnection(sConnString)
 
    End Function
 
    Protected Friend Function GetCommand(ByVal sSQL As String) As OracleCommand
        Dim _objCommand As New OracleCommand
        Try
            Dim objConn As New OracleConnection
            objConn = GetConnection()
            _objCommand = New OracleCommand(sSQL, objConn)
        Catch ex As Exception
            CommonFunctions.LogEvent( _
                ex.ToString.ToString, _
                Diagnostics.EventLogEntryType.Error, _
                sSQL, _
                Current.Server.UrlEncode(Current.Request.ServerVariables("script_name")))
            Throw ex
        End Try
        Return _objCommand
    End Function
 
    Protected Friend Function addCursor(ByVal Cursorname As String) As Object
        Dim op As New OracleParameter(Cursorname, OracleType.Cursor)
        op.Direction = ParameterDirection.Output
        Return op
    End Function
 
    Public Function Select_Generic_Report( _
                           ByVal Start_DateString As String, _
                           ByVal End_DateString As String) As DataSet
 
        Dim returnDs As New DataSet
        Try
 
            Dim dateRangeString As String = portalDateRange.GetOracleDateRange(Start_DateString, End_DateString)
            Dim sSqlHeader As String = = "select yadda yadda from table 1 " + dateRangeString
            Dim sSql As String = = "select yadda yadda from table 2 " + dateRangeString
 
            Dim PkgName As String = "myPackage.fetchTwoCursorsViaTwoDynamicSQLs "
            _objCommand = GetCommand(PkgName)
            _objCommand.CommandType = CommandType.StoredProcedure
 
            With _objCommand
                .Parameters.Add(New OracleParameter("sSqlHeader", OracleType.VarChar, 8000, ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Current, sSqlHeader))
                .Parameters.Add(New OracleParameter("sSql", OracleType.VarChar, 8000, ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Current, sSql))
                .Parameters.Add(New OracleParameter("_strMsg", OracleType.VarChar, 50, ParameterDirection.Output, False, 0, 0, "", DataRowVersion.Current, ""))
                .Parameters.Add(New OracleParameter("_strMsgCode", OracleType.VarChar, 50, ParameterDirection.Output, False, 0, 0, "", DataRowVersion.Current, ""))
                .Parameters.Add(addCursor("rpt_header_cur"))
                .Parameters.Add(addCursor("rpt_data_cur"))
            End With
 
            _objAdaptor.SelectCommand = _objCommand
 
            _objAdaptor.Fill(returnDs)
            processMsgCode()
        Catch ex As Exception
            Throw ex
        Finally
            CleanUp(_objCommand)
        End Try
        Return returnDs
    End Function
 
PACKAGE myPackage
 IS
   type Select_Cursor is ref cursor;
 PROCEDURE fetchTwoCursorsViaTwoDynamicSQLs
     (sSqlHeader     in VARCHAR2,
      sSql           in VARCHAR2,
      oStrMsg        OUT varchar2,
      oStrMsgCode    OUT varchar2,
      rpt_header_cur OUT Select_Cursor,
      rpt_data_cur   OUT Select_Cursor);
 
END; -- Package spec
 
PACKAGE BODY myPackage
IS
 
 PROCEDURE fetchTwoCursorsViaTwoDynamicSQLs
     (sSqlHeader     in VARCHAR2,
      sSql       in VARCHAR2,
      oStrMsg        OUT varchar2,
      oStrMsgCode    OUT varchar2,
      rpt_header_cur OUT Select_Cursor,
      rpt_data_cur   OUT Select_Cursor)
 IS
     
 
   BEGIN
        OPEN rpt_header_cur FOR sSqlHeader;
        OPEN rpt_data_cur FOR sSql;
 
        oStrMsgCode := '0';
                   
   EXCEPTION
     WHEN others THEN
         oStrMsg := sqlerrm ;
         oStrMsgCode := sqlcode;
   END;
END;
 
    Private Sub processMsgCode()
        Try
            _strMsgCode = _objCommand.Parameters("_strMsgcode").Value.ToString
            _strMsg = _objCommand.Parameters("_strMsg").Value.ToString
            If _strMsgCode <> "0" Then
                Throw New Exception("Data Error Code: " + _strMsgCode + ", error message: " + _strMsg)
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CleanUp(_objCommand)
        End Try
    End Sub
 
    Public Function Call_Stored_Proc_To_Return_Dataset() As DataSet
        Dim objDs As New DataSet
        Dim PkgName As String
        Try
            PkgName = "myPackage.Stored_Proc_To_Return_Dataset"
            _objCommand = GetCommand(PkgName)
            _objCommand.CommandType = CommandType.StoredProcedure
            With _objCommand
                .Parameters.Add(New OracleParameter("parm1", OracleType.VarChar, 100, ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Current, Campaign_Id))
                .Parameters.Add(New OracleParameter("_strMsg", OracleType.VarChar, 50, ParameterDirection.Output, False, 0, 0, "", DataRowVersion.Current, ""))
                .Parameters.Add(New OracleParameter("_strMsgCode", OracleType.VarChar, 50, ParameterDirection.Output, False, 0, 0, "", DataRowVersion.Current, ""))
                .Parameters.Add(addCursor("my_cur"))
            End With
            _objAdaptor.SelectCommand = _objCommand
 
            _objAdaptor.Fill(objDs)
            processMsgCode()
        Catch ex As Exception
            Throw ex
        Finally
            CleanUp(_objCommand)
        End Try
        Return objDs
    End Function
 
 PROCEDURE Stored_Proc_To_Return_Dataset
     (parm1 IN Number,
      oStrMsg OUT varchar2,
      oStrMsgCode OUT varchar2,
      my_cur OUT Select_Cursor)
 IS
     
   BEGIN
        OPEN my_cur FOR
            SELECT yaddaYadda as blahBlah
            FROM table T
            where T.id = iCampaignId
                          
         oStrMsgCode := '0';
                   
   EXCEPTION
     WHEN others THEN
         oStrMsg := sqlerrm ;
         oStrMsgCode := sqlcode;
   END;  
 
Posted on Friday, January 4, 2008 3:27 AM Oracle | Back to top


Comments on this post: Oracle Framework for .NET

# re: Oracle Framework for .NET
Requesting Gravatar...
Thanks for the post, was very helpful and I've only just started Oracle so it was a bit hard to understand but I got it in the end.
Left by abstract canvas art on Sep 21, 2011 4:56 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net