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;