Data access component
Description
Dans ce code vous allez trouver une classe générique d'accès aux données.
le but de cette classe c'est : "l?indépendance à une base de données spécifique"
le but de cette classe c'est : "l?indépendance à une base de données spécifique"
Source / Exemple :
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.Common
Imports System.Configuration
Imports System.Data
Imports System.Collections
Imports System.Reflection
Imports System.Transactions
Imports System.ComponentModel
Namespace mongeneric
Public Class mondata
Private m_connectionStringName As String = ""
Private connectionStringSettings As ConnectionStringSettings
Private connectionStringsCollection As ConnectionStringSettingsCollection
Private m_dbProviderFactory As DbProviderFactory
Private m_cacheEnabled As Boolean = True
Private transactionScope As TransactionScope
Private cache As Hashtable
Private cacheGeneric As Hashtable
Private Shared instance As mondata
Shared Sub New()
instance = New mondata()
' Récupération de toutes les chaines de connexion
instance.connectionStringsCollection = ConfigurationManager.ConnectionStrings
If instance.m_connectionStringName = String.Empty Then
instance.m_connectionStringName = instance.connectionStringsCollection(1).Name
End If
' Récupération de la classe fabrique suivant le provider
instance.m_dbProviderFactory = DbProviderFactories.GetFactory(instance.connectionStringsCollection(1).ProviderName)
instance.cache = New Hashtable()
instance.cacheGeneric = New Hashtable()
End Sub
Public Shared ReadOnly Property Current() As mondata
Get
Return instance
End Get
End Property
' Crée une connexion depuis le fichier de configuration
'Objet implementant DbConnection
Public Function GetConnection() As DbConnection
' Création de la connection
Dim con As DbConnection = Me.m_dbProviderFactory.CreateConnection
' Récupération de la chaine de connexion
connectionStringSettings = connectionStringsCollection(1)
' Affectation de la chaine de connexion
con.ConnectionString = connectionStringSettings.ConnectionString
' retour de la connexion
Return con
End Function
' Créer un objet Command
Public Function CreateDbCommand(ByVal commandText As String, ByVal connection As DbConnection, ByVal commandType As CommandType) As DbCommand
Dim command As DbCommand = Me.m_dbProviderFactory.CreateCommand()
command.CommandType = commandType
command.CommandText = commandText
command.Connection = connection
Return command
End Function
' Créer un objet Command : Surcharge
Public Function CreateDbCommand(ByVal commandText As String, ByVal commandType As CommandType) As DbCommand
Dim command As DbCommand = Me.m_dbProviderFactory.CreateCommand()
command.CommandType = commandType
command.CommandText = commandText
command.Connection = Me.GetConnection()
Return command
End Function
' Récupère un jeu d'enregistrement d'une base de donnée depuis un order T-SQL
Public Function GetReader(ByVal commandText As String) As DbDataReader
Dim dataReader As DbDataReader = Nothing
Dim conn As DbConnection = Nothing
Try
conn = Me.GetConnection()
Dim command As DbCommand = CreateDbCommand(commandText, conn, CommandType.Text)
conn.Open()
dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Catch
If conn.State <> System.Data.ConnectionState.Closed Then conn.Close()
End Try
' Retour du dataReader;
Return dataReader
End Function
' Récupère un jeu d'enregistrement d'une base de donnée en éxécutant une procédure stockée
Public Function GetReader(ByVal storedProcedure As String, ByVal ParamArray parameters As Object()) As DbDataReader
Dim dataReader As DbDataReader = Nothing
Dim conn As DbConnection = Nothing
Try
conn = Me.GetConnection()
Dim command As DbCommand = Me.CreateDbCommand(storedProcedure, conn, CommandType.StoredProcedure)
Me.PrepareCommand(command, parameters)
conn.Open()
dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Catch
If conn.State <> System.Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
' Retour du dataReader;
Return dataReader
End Function
' Exécute un ordre T-SQL et renvoit le nombre de lignes affectées
Public Function ExecuteNonQuery(ByVal commandText As String) As Integer
Dim resultat As Integer
Dim conn As DbConnection = Nothing
Try
conn = Me.GetConnection()
Dim command As DbCommand = Me.CreateDbCommand(commandText, conn, CommandType.Text)
conn.Open()
resultat = command.ExecuteNonQuery()
Finally
If conn.State <> System.Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
' Retour du résultat;
Return resultat
End Function
' Exécute une procédure stockée et renvoit le nombre de lignes affectées
Public Function ExecuteNonQuery(ByVal storedProcedure As String, ByVal ParamArray parameters As Object()) As Integer
Dim intResult As Integer
Dim conn As DbConnection = Nothing
Try
conn = Me.GetConnection()
Dim command As DbCommand = Me.CreateDbCommand(storedProcedure, conn, CommandType.StoredProcedure)
Me.PrepareCommand(command, parameters)
conn.Open()
intResult = command.ExecuteNonQuery()
Finally
If conn.State <> System.Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
' Retour du résultat;
Return intResult
End Function
' Exécute une procédure stockée et renvoit le nombre de lignes affectées.
' Prend en paramètre un objet command pouvant être récupéré et utilisé
Public Function ExecuteNonQuery(ByVal storedProcedure As String, ByVal command As DbCommand, ByVal ParamArray parameters As Object()) As Integer
Dim intResult As Integer
Dim conn As DbConnection = Nothing
Try
conn = Me.GetConnection()
command = Me.CreateDbCommand(storedProcedure, conn, CommandType.StoredProcedure)
Me.PrepareCommand(command, parameters)
conn.Open()
intResult = command.ExecuteNonQuery()
Finally
If conn.State <> System.Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
' Retour du résultat;
Return intResult
End Function
' Exécute un ordre T-SQL et renvoit une valeur
Public Function ExecuteScalar(ByVal commandText As String) As Object
Dim resultat As Object = Nothing
Dim conn As DbConnection = Nothing
Try
conn = Me.GetConnection()
Dim command As DbCommand = Me.CreateDbCommand(commandText, conn, CommandType.Text)
conn.Open()
resultat = command.ExecuteScalar()
Finally
If conn.State <> System.Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
' Retour du résultat;
Return resultat
End Function
' Exécute une procédure stockée et renvoit une valeur
Public Function ExecuteScalar(ByVal storedProcedure As String, ByVal ParamArray parameters As Object()) As Object
Dim resultat As Object = Nothing
Dim conn As DbConnection = Nothing
Try
conn = Me.GetConnection()
Dim command As DbCommand = Me.CreateDbCommand(storedProcedure, conn, CommandType.StoredProcedure)
Me.PrepareCommand(command, parameters)
conn.Open()
resultat = command.ExecuteScalar()
Finally
If conn.State <> System.Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
' Retour du résultat;
Return resultat
End Function
' Création d'un dataAdapter
Public Function CreateDataAdapter(ByVal commandType__1 As CommandType, ByVal selectStoredProcedure As String, ByVal insertStoredProcedure As String, ByVal updateStoredProcedure As String, ByVal deleteStoredProcedure As String) As DbDataAdapter
'Déclarations
Dim dbSelectCommand As DbCommand = Nothing
Dim dbInsertCommand As DbCommand = Nothing
Dim dbUpdateCommand As DbCommand = Nothing
Dim dbDeleteCommand As DbCommand = Nothing
Dim dbDataAdapter As DbDataAdapter = Nothing
Dim dbConnection As DbConnection = Me.GetConnection()
'Paramétrage de la commande de sélection
dbSelectCommand = m_dbProviderFactory.CreateCommand()
dbSelectCommand.CommandType = commandType__1
dbSelectCommand.Connection = dbConnection
If selectStoredProcedure IsNot Nothing Then
dbSelectCommand.CommandText = selectStoredProcedure
If commandType__1 = CommandType.StoredProcedure Then
Me.PrepareCommand(dbSelectCommand)
End If
Else
dbSelectCommand.CommandText = ""
End If
'Paramétrage de la commande d'insertion
dbInsertCommand = m_dbProviderFactory.CreateCommand()
dbInsertCommand.CommandType = commandType__1
dbInsertCommand.Connection = dbConnection
If insertStoredProcedure IsNot Nothing Then
dbInsertCommand.CommandText = insertStoredProcedure
If commandType__1 = CommandType.StoredProcedure Then
Me.PrepareCommand(dbInsertCommand)
End If
Else
dbInsertCommand.CommandText = ""
End If
'Paramétrage de la commande de mise à jour
dbUpdateCommand = m_dbProviderFactory.CreateCommand()
dbUpdateCommand.CommandType = commandType__1
dbUpdateCommand.Connection = dbConnection
If updateStoredProcedure IsNot Nothing Then
dbUpdateCommand.CommandText = updateStoredProcedure
If commandType__1 = CommandType.StoredProcedure Then
Me.PrepareCommand(dbUpdateCommand)
End If
Else
dbUpdateCommand.CommandText = ""
End If
'Paramétrage de la commande de suppression
dbDeleteCommand = m_dbProviderFactory.CreateCommand()
dbDeleteCommand.CommandType = commandType__1
dbDeleteCommand.Connection = dbConnection
If deleteStoredProcedure IsNot Nothing Then
dbDeleteCommand.CommandText = deleteStoredProcedure
If commandType__1 = CommandType.StoredProcedure Then
Me.PrepareCommand(dbDeleteCommand)
End If
Else
dbDeleteCommand.CommandText = ""
End If
'Paramétrage de l'adaptateur
dbDataAdapter = m_dbProviderFactory.CreateDataAdapter()
dbDataAdapter.InsertCommand = dbInsertCommand
dbDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
dbDataAdapter.UpdateCommand = dbUpdateCommand
dbDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
dbDataAdapter.DeleteCommand = dbDeleteCommand
dbDataAdapter.SelectCommand = dbSelectCommand
'Retour
Return dbDataAdapter
End Function
' Récupère un DataSet de la base de donnée depuis un ordre T-SQL
Public Sub GetDataSet(ByVal dataSet As DataSet, ByVal tableName As String, ByVal commandText As String)
Dim conn As DbConnection = Nothing
Dim dbreader As DbDataReader = Nothing
Try
conn = Me.GetConnection()
Dim command As DbCommand = Me.CreateDbCommand(commandText, conn, CommandType.Text)
conn.Open()
dbreader = command.ExecuteReader(CommandBehavior.CloseConnection)
dataSet.Load(dbreader, LoadOption.PreserveChanges, New String() {tableName})
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If conn.State <> System.Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
End Sub
' Récupère un DataSet de la base de donnée depuis une procédure stockée
Public Sub GetDataSet(ByVal dataSet As DataSet, ByVal tableName As String, ByVal storedProcedure As String, ByVal ParamArray parameters As DbParameter())
Dim conn As DbConnection = Nothing
Dim dbreader As DbDataReader = Nothing
Try
conn = Me.GetConnection()
Dim command As DbCommand = Me.CreateDbCommand(storedProcedure, conn, CommandType.StoredProcedure)
Me.DeriveParameters(command, conn)
conn.Open()
dbreader = command.ExecuteReader(CommandBehavior.CloseConnection)
dataSet.Load(dbreader, LoadOption.PreserveChanges, New String() {tableName})
Finally
If conn.State <> System.Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
End Sub
' Nettoyage du cache de procédures stockées
Public Sub ClearCache()
SyncLock cache
cache.Clear()
End SyncLock
SyncLock cacheGeneric
cacheGeneric.Clear()
End SyncLock
End Sub
' Préparation d'une commande avec une procédure stockée
' Pattern Double Check pour démonstration
Private Sub PrepareCommand(ByVal command As DbCommand, ByVal ParamArray parameters As Object())
Dim index As Int32 = 1
Dim connexionLocale As DbConnection = Me.GetConnection()
Dim commandeLocale As DbCommand = m_dbProviderFactory.CreateCommand()
commandeLocale.CommandText = command.CommandText
commandeLocale.Connection = connexionLocale
commandeLocale.CommandType = CommandType.StoredProcedure
Dim parametresLocaux As DbParameterCollection = Nothing
If Not cache.ContainsKey(command.CommandText) Then
SyncLock cache
If Not cache.ContainsKey(command.CommandText) Then
Me.DeriveParameters(commandeLocale, connexionLocale)
If Me.m_cacheEnabled Then
cache.Add(command.CommandText, commandeLocale.Parameters)
Else
parametresLocaux = commandeLocale.Parameters
End If
End If
End SyncLock
End If
'Transfert de la structure
If Me.m_cacheEnabled Then
parametresLocaux = DirectCast(cache(command.CommandText), DbParameterCollection)
End If
For Each param As DbParameter In parametresLocaux
Dim newParam As DbParameter = m_dbProviderFactory.CreateParameter()
newParam.ParameterName = param.ParameterName
newParam.DbType = param.DbType
newParam.Size = param.Size
newParam.Direction = param.Direction
command.Parameters.Add(newParam)
Next
'Transfert des valeurs de paramètres
If parameters IsNot Nothing Then
For Each o As Object In parameters
command.Parameters(index).Value = o
index += 1
Next
End If
End Sub
Public Sub commandbuilder(ByVal dataAdapter As DbDataAdapter)
Dim commandBuilder As DbCommandBuilder = m_dbProviderFactory.CreateCommandBuilder()
End Sub
' Remplit un Objet DbCommand les Paramètres de la procédure stockée contenue dans la command
Private Sub DeriveParameters(ByVal command As DbCommand, ByVal connexion As DbConnection)
If TypeOf command Is System.Data.SqlClient.SqlCommand Then
connexion.Open()
System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(DirectCast(command, System.Data.SqlClient.SqlCommand))
connexion.Close()
ElseIf TypeOf command Is System.Data.Odbc.OdbcCommand Then
connexion.Open()
System.Data.Odbc.OdbcCommandBuilder.DeriveParameters(DirectCast(command, System.Data.Odbc.OdbcCommand))
connexion.Close()
ElseIf TypeOf command Is System.Data.OleDb.OleDbCommand Then
connexion.Open()
System.Data.OleDb.OleDbCommandBuilder.DeriveParameters(DirectCast(command, System.Data.OleDb.OleDbCommand))
connexion.Close()
Else
' ---------------------------------------
' Tentative de réflexion
' ---------------------------------------
Try
Dim commandBuilder As DbCommandBuilder = m_dbProviderFactory.CreateCommandBuilder()
' Récupération du Type du commandBuilder crée par mon Provider
Dim commandBuilderType As Type = commandBuilder.[GetType]()
' Tentative de récupération de la méthode DeriveParameters
Dim methodInfo As MethodInfo = commandBuilderType.GetMethod("DeriveParameters")
' Si la méthode n'existe pas, exeception levée
If methodInfo Is Nothing Then
Throw (New Exception("le provider ne suppoet pas la method DeriveParameters"))
End If
' Invocation de la méthode DeriveParameters
methodInfo.Invoke(Nothing, New Object(0) {command})
Catch ex As Exception
Throw ex
End Try
End If
End Sub
Public Sub BeginTransaction()
transactionScope = New TransactionScope()
End Sub
Public Sub EndTransaction(ByVal Completed As Boolean)
If Completed Then
transactionScope.Complete()
End If
transactionScope.Dispose()
End Sub
' pour permettre un mappage Objet - Proc Stock
Private Sub CaptureAttributes(ByVal typeGenericObject As Type, ByRef refAttributeSetExecuteOrder As AttributeSetExecuteOrder, ByRef refMembers As ArrayList)
refAttributeSetExecuteOrder = Nothing
refMembers = Nothing
' Collection des Descriptions de Propriétés
Dim pdColl As PropertyDescriptorCollection
' Récupération du nom du type de T
Dim tName As String = typeGenericObject.Name
' Récupération des AttributeSetExecuteOrder et de toutes les propriétés mappées
' Pattern double lock
If Not cacheGeneric.ContainsKey(tName) Then
SyncLock cacheGeneric
If Not cacheGeneric.ContainsKey(tName) Then
' Instanciation du tableau qui va contenir les Membres
refMembers = New ArrayList()
' Récupération des attributs d'entête
refAttributeSetExecuteOrder = DirectCast(Attribute.GetCustomAttribute(typeGenericObject, GetType(AttributeSetExecuteOrder)), AttributeSetExecuteOrder)
If refAttributeSetExecuteOrder Is Nothing Then
refAttributeSetExecuteOrder = New AttributeSetExecuteOrder(String.Format("{0}_Select", typeGenericObject.Name), True)
End If
' Récupération des membres marqués
Dim members As System.Reflection.MemberInfo() = typeGenericObject.GetMembers(BindingFlags.Instance Or BindingFlags.[Public])
pdColl = TypeDescriptor.GetProperties(typeGenericObject)
For Each propertyDescriptor As PropertyDescriptor In pdColl
Dim attribute1 As Attribute = propertyDescriptor.Attributes(GetType(AttributeColumnName))
If attribute1 Is Nothing Then
attribute1 = New AttributeColumnName(propertyDescriptor.Name)
End If
DirectCast(attribute1, AttributeColumnName).SchemaMemberName = propertyDescriptor.Name
refMembers.Add(attribute1)
Next
' Mise en cache si nécessaire
If Me.m_cacheEnabled Then
cacheGeneric.Add(tName, refAttributeSetExecuteOrder)
cacheGeneric.Add(String.Format("{0}_Members", tName), refMembers)
End If
End If
End SyncLock
End If
' Récupération depuis la la table static cacheGeneric si le cache est activé
If m_cacheEnabled Then
refAttributeSetExecuteOrder = DirectCast(cacheGeneric(tName), AttributeSetExecuteOrder)
refMembers = DirectCast(cacheGeneric(String.Format("{0}_Members", tName)), ArrayList)
End If
End Sub
' Remplit une collection générique de type T
Public Function GetList(Of T)(ByVal ParamArray parameters As Object()) As System.Collections.Generic.List(Of T)
' DbDataReader
Dim dr As System.Data.Common.DbDataReader = Nothing
' Déclaration d'une liste de T
Dim list As System.Collections.Generic.List(Of T) = New List(Of T)()
Try
' Attributs de T
Dim attribLocaux As AttributeSetExecuteOrder = Nothing
' Attributs des membres de T
Dim lstMembers As ArrayList = Nothing
' Récupération des attributs
Me.CaptureAttributes(GetType(T), attribLocaux, lstMembers)
If attribLocaux IsNot Nothing AndAlso attribLocaux.SqlOrder <> String.Empty Then
' Récupération du DataReader
If attribLocaux.IsStoredProcedure Then
dr = Me.GetReader(attribLocaux.SqlOrder, parameters)
Else
dr = Me.GetReader(attribLocaux.SqlOrder)
End If
If dr.HasRows Then
While dr.Read()
list.Add(Me.PopulateObject(Of T)(dr, lstMembers))
End While
End If
End If
dr.Close()
Return list
Finally
If Not dr.IsClosed Then
dr.Close()
End If
End Try
End Function
' Renvoit un objet unitaire de type T, remplit depuis un DataReader
Private Function PopulateObject(Of T)(ByVal dr As DbDataReader, ByVal lstMembers As ArrayList) As T
' Instanciation de T
Dim item As T = DirectCast(Activator.CreateInstance(GetType(T)), T)
' Parcours de tous les membres marqués et remplissage de la collection
For Each attribMember As AttributeColumnName In lstMembers
Try
If dr(attribMember.SchemaTableColumnName) IsNot System.DBNull.Value Then
item.[GetType]().GetProperty(attribMember.SchemaMemberName).SetValue(item, dr(attribMember.SchemaTableColumnName), Nothing)
End If
Catch
End Try
Next
Return item
End Function
End Class
' Attribut personnalisé.
' Il permet de récupérer une association Objet <-> Champ Base de donnée
Public Class AttributeColumnName
Inherits Attribute
Private m_schemaTableColumnName As String
Public Property SchemaTableColumnName() As String
Get
Return m_schemaTableColumnName
End Get
Set(ByVal value As String)
m_schemaTableColumnName = value
End Set
End Property
Private m_schemaMemberName As String
Public Property SchemaMemberName() As String
Get
Return m_schemaMemberName
End Get
Set(ByVal value As String)
m_schemaMemberName = value
End Set
End Property
Public Sub New(ByVal schemaTableColumnName As String)
Me.m_schemaTableColumnName = schemaTableColumnName
End Sub
End Class
' Marquage de la classe comme étant une classe à renvoyer avec DataAccess
Public Class AttributeSetExecuteOrder
Inherits Attribute
Public Sub New(ByVal sqlOrder As String, ByVal isStoredProcedure As Boolean)
Me.IsStoredProcedure = isStoredProcedure
Me.SqlOrder = sqlOrder
End Sub
Private m_isStoredProcedure As Boolean
Public Property IsStoredProcedure() As Boolean
Get
Return m_isStoredProcedure
End Get
Set(ByVal value As Boolean)
m_isStoredProcedure = value
End Set
End Property
Private m_sqlOrder As String
Public Property SqlOrder() As String
Get
Return m_sqlOrder
End Get
Set(ByVal value As String)
m_sqlOrder = value
End Set
End Property
End Class
End Namespace
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.