' ************************************************** **********************************
' In the name of god
' This Class Help you to use ADO.Net 2.0
' Provider: Niloufar
' ************************************************** **********************************
Imports System
Imports System.Data
Public Class clsADO
'Public part
''' <summary>
''' This Constractor is for MsAccess DataBases
''' </summary>
''' <param name="strPathAndFileName">MsAccess DataBase full file name (Path\FileName)</param>
''' <remarks></remarks>
Public Sub New(ByVal strPathAndFileName As String)
DataBaseType = DataBaseTypes.MsAccess
strlocalPathAndFileName = strPathAndFileName
End Sub
''' <summary>
''' This constractor is for MsSqlServer DataBases
''' </summary>
''' <param name="strServerName">MsSqlServer Server Name</param>
''' <param name="strDataBaseName">DataBase Name</param>
''' <param name="strUserId">User Id</param>
''' <param name="strPassword">Password</param>
''' <remarks></remarks>
Public Sub New(ByVal strServerName As String, ByVal strDataBaseName As String, ByVal strUserId As String, ByVal strPassword As String)
DataBaseType = DataBaseTypes.MsSqlServer
strlocalServerName = strServerName
strlocalDataBaseName = strDataBaseName
strlocalUserId = strUserId
strlocalPassword = strPassword
End Sub
''' <summary>
''' This constractor is for Oracle DataBases
''' </summary>
''' <param name="strSID">Oracle System Identifier</param>
''' <param name="strUserId">User Id</param>
''' <param name="strPassword">Password</param>
''' <remarks></remarks>
Public Sub New(ByVal strSID As String, ByVal strUserId As String, ByVal strPassword As String)
DataBaseType = DataBaseTypes.Oracle
strlocalSID = strSID
strlocalUserId = strUserId
strlocalPassword = strPassword
End Sub
''' <summary>
''' Dispose this class
''' </summary>
''' <param name="Disposing">Set to TRUE if you want to dispose any objects used in this class and set them to nothing</param>
''' <remarks></remarks>
Public Overloads Sub Dispose(ByVal Disposing As Boolean)
If Not Me.Disposed Then
If Disposing Then
If Not objConnection Is Nothing Then
If objConnection.State <> ConnectionState.Closed Then
objConnection.Close()
End If
objConnection.Dispose()
objConnection = Nothing
End If
If Not objCommand Is Nothing Then
objCommand.Dispose()
objCommand = Nothing
End If
If Not objDataAdapter Is Nothing Then
objDataAdapter.Dispose()
objDataAdapter = Nothing
End If
Me.Disposed = True
End If
End If
End Sub
''' <summary>
''' Get a DataTable object by a text query
''' </summary>
''' <param name="strQuery">SELECT query that you want to fill DataTable by this query</param>
''' <returns>DataTable object filled by entered query</returns>
''' <remarks></remarks>
Public Function GetDataTableByQuery(ByVal strQuery As String) As DataTable
Select Case DataBaseType
Case DataBaseTypes.MsAccess
Call OpenMsAccessConnection(strlocalPathAndFileName)
Case DataBaseTypes.MsSqlServer
Call OpenMsSqlServerConnection(strlocalServerName, strlocalDataBaseName, strlocalUserId, strlocalPassword)
Case DataBaseTypes.Oracle
Call OpenOracleConnection(strlocalSID, strlocalUserId, strlocalPassword)
End Select
CommandType = Data.CommandType.Text
InitializeCommand(strQuery)
Try
objDataAdapter = New OleDb.OleDbDataAdapter(objCommand)
Dim objDataTable As New DataTable
objDataAdapter.Fill(objDataTable)
Return (objDataTable)
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
''' <summary>
''' Execute any text query (Select, Insert, Update, Delete) and return the number of rows affected by this query
''' </summary>
''' <param name="strQuery">Text query that you want to execute it</param>
''' <returns>The number of rows affected by entered query</returns>
''' <remarks></remarks>
Public Function ExecuteQuery(ByVal strQuery As String) As Integer
Select Case DataBaseType
Case DataBaseTypes.MsAccess
Call OpenMsAccessConnection(strlocalPathAndFileName)
Case DataBaseTypes.MsSqlServer
Call OpenMsSqlServerConnection(strlocalServerName, strlocalDataBaseName, strlocalUserId, strlocalPassword)
Case DataBaseTypes.Oracle
Call OpenOracleConnection(strlocalSID, strlocalUserId, strlocalPassword)
End Select
CommandType = Data.CommandType.Text
InitializeCommand(strQuery)
Try
ExecuteQuery = objCommand.ExecuteNonQuery
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
''' <summary>
''' You should call this sub befor getting DataTable or execute StoredProecedure for adding parameters of StoredProcedure
''' </summary>
''' <param name="SPName">StoredProcedure Name that you want Execute it</param>
''' <remarks></remarks>
Public Sub InitializeStoredProcedure(ByVal SPName As String)
Select Case DataBaseType
Case DataBaseTypes.MsAccess
Call OpenMsAccessConnection(strlocalPathAndFileName)
Case DataBaseTypes.MsSqlServer
Call OpenMsSqlServerConnection(strlocalServerName, strlocalDataBaseName, strlocalUserId, strlocalPassword)
Case DataBaseTypes.Oracle
Call OpenOracleConnection(strlocalSID, strlocalUserId, strlocalPassword)
End Select
CommandType = Data.CommandType.StoredProcedure
InitializeCommand(SPName)
End Sub
''' <summary>
''' Add a Parameter of StoredProcedure. Note: you should call the InitializeStoredProcedure sub befor using this sub
''' </summary>
''' <param name="Name">Parameter name</param>
''' <param name="Type">Parameter type</param>
''' <param name="Size">Parameter size</param>
''' <param name="Value">Parameter value</param>
''' <remarks></remarks>
Public Sub AddParameter(ByVal Name As String, ByVal Type As OleDb.OleDbType, ByVal Size As Integer, ByVal Value As Object)
Try
objCommand.Parameters.Add(Name, Type, Size).Value = Value
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Sub
''' <summary>
''' Get DataTable Object by Execute the StoredProcedure that you enter it in the InitializeStoredProcedure sub. Note: you should call the InitializeStoredProcedure sub befor using this function
''' </summary>
''' <returns>DataTable object filled by entered StoredProcedure</returns>
''' <remarks></remarks>
Public Function GetDataTableByStoredProcedure() As DataTable
Try
objDataAdapter = New OleDb.OleDbDataAdapter(objCommand)
Dim objDataTable As New DataTable
objDataAdapter.Fill(objDataTable)
Return (objDataTable)
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
''' <summary>
''' Execute the StoredProcedure that you enter it in the InitializeStoredProcedure sub. Note: you should call the InitializeStoredProcedure sub befor using this function
''' </summary>
''' <returns>The number of rows affected by entered StoredProcedure</returns>
''' <remarks></remarks>
Public Function ExecuteStoredProcedure() As Integer
Try
ExecuteStoredProcedure = objCommand.ExecuteNonQuery
Catch ex As OleDb.OleDbException
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Function
'Private part
Private Enum DataBaseTypes As Byte
MsAccess = 1
MsSqlServer = 2
Oracle = 3
End Enum
Private CommandType As CommandType = CommandType.Text
Private Disposed As Boolean
Private DataBaseType As DataBaseTypes
Private strlocalPathAndFileName As String
Private strlocalServerName As String
Private strlocalDataBaseName As String
Private strlocalSID As String
Private strlocalUserId As String
Private strlocalPassword As String
Private objConnection As OleDb.OleDbConnection
Private objCommand As OleDb.OleDbCommand
Private objDataAdapter As OleDb.OleDbDataAdapter
Private Sub OpenMsAccessConnection(ByVal strPathAndFileName As String)
objConnection = New OleDb.OleDbConnection
objConnection.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;" & _
"Data Source=" & strPathAndFileName & ";"
Try
objConnection.Open()
Catch ex As OleDb.OleDbException 'Connection-Level Exceptions
Throw New Exception(ex.Message, ex.InnerException)
Catch ex As InvalidOperationException 'Trying to open a connection that is already opened
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
Private Sub OpenMsSqlServerConnection(ByVal strServerName As String, ByVal strDataBaseName As String, ByVal strUserId As String, ByVal strPassword As String)
objConnection = New OleDb.OleDbConnection
objConnection.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=" & strServerName & ";" & _
"DataBase=" & strDataBaseName & ";" & _
"User ID=" & strUserId & ";" & _
"Password=" & strPassword & ";"
Try
objConnection.Open()
Catch ex As OleDb.OleDbException 'Connection-Level Exceptions
Throw New Exception(ex.Message, ex.InnerException)
Catch ex As InvalidOperationException 'Trying to open a connection that is already opened
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
Private Sub OpenOracleConnection(ByVal strSID As String, ByVal strUserId As String, ByVal strPassword As String)
objConnection = New OleDb.OleDbConnection
objConnection.ConnectionString = "Provider=MSDAORA;" & _
"Data Source=" & strSID & ";" & _
"User ID=" & strUserId & ";" & _
"Password=" & strPassword & ";"
Try
objConnection.Open()
Catch ex As OleDb.OleDbException 'Connection-Level Exceptions
Throw New Exception(ex.Message, ex.InnerException)
Catch ex As InvalidOperationException 'Trying to open a connection that is already opened
Throw New Exception(ex.Message, ex.InnerException)
End Try
End Sub
Private Sub InitializeCommand(ByVal strQueryOrSPName As String)
Try
objCommand = New OleDb.OleDbCommand(strQueryOrSPName, objConnection)
objCommand.CommandType = CommandType
Catch ex As Exception
Throw New System.Exception(ex.Message, ex.InnerException)
End Try
End Sub
End Class