Enterprise Library包装类,无需配置文件即可实现运行时动态连接字符串





4.00/5 (3投票s)
Enterprise Library包装类,无需配置文件即可实现运行时动态连接字符串
- 下载 EntLibWrapper 类 (VB 文件) - 3.84 KB
- 下载 EntLibWrapper 类帮助 (CHM 文件) - 18.43 KB
- 下载加密/解密函数 (VB 文件) - 718 B
- 加密/解密的示例 web.config (XML 文件) - 738 B
引言
这是一个 Enterprise Library 包装器类,用于在不使用配置文件的情况下实现运行时动态连接字符串。
背景
Enterprise Library 的开箱即用功能建议将连接字符串和提供程序信息存储在配置文件中。我们的应用程序在运行时动态生成连接字符串。因此,我开发了这个 enterprise library 包装器类,以便我们可以使用 enterprise library 和运行时动态连接字符串。
Using the Code
下载 EntLibWrapper 类和 通用函数。
下载的 VB 文件可以包含在 .NET 项目中以使用 EntLibWrapper
类。下面我将详细介绍如何使用 enterprise library 包装器类和运行时动态连接字符串。
我使用以下 **Enterprise Library 包装器类** 来使用该库和运行时生成的连接字符串。
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports system.Data.Common
Imports system.Data.Odbc
Imports System.Data.OleDb
Imports System.Data.OracleClient
Imports System.Data.SqlClient
''' <summary>
''' This is a wrapper class for enterprise library.
''' </summary>
''' <remarks>
''' This is a wrapper class for enterprise library to avoid web.config
''' and to make connectionStrings runtime configurable using Global.asax
''' application variables which have the connectionStrings.
''' In Global.asax you can retrieve any passwords stored in web.config
''' and assemble the connectionString
''' and store in application variables when the application starts.
''' These connection strings can then be passed in when this class
''' is instantiated along with the required provider factory to use.
''' appSetting in web.config and connection strings stored in
''' Application variables can be encrypted for security purposes.
''' </remarks>
Public Class EntLibWrapper
Private db As Database
Private dbCommand As DbCommand
Private dbConn As DbConnection
Private dbread As IDataReader
Private conString As String
Private dbProvider As enumProviderFactory
Private dbRowsAffected As Integer
Private dbIdentity As Integer
''' <summary>
''' Enumeration of the providers supported by the Enterprise Library wrapper class.
''' </summary>
''' <remarks>
''' This class currently supports only the following providers
''' i.e. all the providers supported by Enterprise Library -
''' January 2006 and installed in our systems.
''' Others can be added on later as required.
''' </remarks>
Public Enum enumProviderFactory
''' <summary>
''' System.Data.OleDb
''' </summary>
''' <remarks>Use for a OleDb connection.</remarks>
OleDb = 1
''' <summary>
''' System.Data.SqlClient
''' </summary>
''' <remarks>Use for a SQL Server connection.</remarks>
Sql = 2
''' <summary>
''' System.Data.Odbc
''' </summary>
''' <remarks>Use for an ODBC connection.</remarks>
Odbc = 3
''' <summary>
''' System.Data.OracleClient
''' </summary>
''' <remarks>Use for an Oracle connection.</remarks>
Oracle = 4
End Enum
''' <summary>
''' Instantiate a new Enterprise Library wrapper.
''' </summary>
''' <param name="connectionString">
''' Pass in the connection string at run time.
''' The connection string can be stored by the user in
''' Application / Session variables in an encrypted
''' format for security purposes. When calling this procedure pass
''' in the decrypted connection string.
''' </param>
''' <param name="Provider">
''' Using the public enum enumProviderFactory choose which
''' ProviderFactory you want to use.
''' </param>
''' <remarks>
''' User can store the password in appSettings in an
''' encrypted format or retrieve the same
''' from a database to format the connection string at run-time dynamically.
''' </remarks>
Public Sub New(ByVal connectionString As String, _
ByVal Provider As enumProviderFactory)
Select Case Provider
Case enumProviderFactory.OleDb
db = GetDatabase(connectionString, "System.Data.OleDb")
Case enumProviderFactory.Sql
db = GetDatabase(connectionString, "System.Data.SqlClient")
Case enumProviderFactory.Odbc
db = GetDatabase(connectionString, "System.Data.Odbc")
Case enumProviderFactory.Oracle
db = GetDatabase(connectionString, "System.Data.OracleClient")
End Select
conString = connectionString
dbProvider = Provider
End Sub
''' <summary>
''' To close the instance of this class.
''' </summary>
''' <remarks>
''' Always call the Close method after you are finished to free up all used resources.
''' </remarks>
Public Sub Close()
db = Nothing
dbCommand = Nothing
If Not dbread Is Nothing Then
dbread.Close()
End If
If Not dbConn Is Nothing Then
dbConn.Close()
End If
End Sub
''' <summary>
''' Gives access to all the other overloads in the
''' Enterprise Library not exposed by this class.
''' </summary>
''' <value>
''' Database
''' </value>
''' <returns>
''' Returns a database.
''' </returns>
''' <remarks>
''' This will enable the user to use all the other methods and
''' properties of the generic database which are not handled in this class
''' - this class handles only the most frequently used
''' methods to make it simpler for use.
''' </remarks>
Public ReadOnly Property Database() As Database
Get
Return db
End Get
End Property
''' <summary>
''' Retrieves the rows affected by a DoActionQuery.
''' </summary>
''' <value>Integer</value>
''' <returns>Returns an integer representing the rows affected
''' after DoActionQuery.</returns>
''' <remarks>
''' You can retrieve the rows affected after a DoActionQuery.
''' </remarks>
Public ReadOnly Property RowsAffected() As Integer
Get
Return dbRowsAffected
End Get
End Property
''' <summary>
''' Use to retrieve the id of the last row inserted after a DoActionQuery.
''' </summary>
''' <value>Integer</value>
''' <returns>Returns an integer representing the id of the
''' last row inserted after a DoActionQuery.</returns>
''' <remarks>
''' You can retrieve the id of the last row inserted after a DoActionQuery.
''' <param></param>
''' The Identity property returns the @@IDENTITY for OleDb,
''' the SCOPE_IDENTITY() for Sql and zero for the
''' other two providers, ODBC and Oracle.
''' <param></param>
''' For ODBC, setting this to zero as the ODBC connection string
''' may be pointing to a text file for example.
''' <param></param>
''' For Oracle, there is no such thing as scope identity -
''' have to use sequences, so returning zero for this too.
''' </remarks>
Public ReadOnly Property Identity() As Integer
Get
Return dbIdentity
End Get
End Property
''' <summary>
''' This is a private function of this class.
''' Provides the hook into the Enterprise Library.
''' </summary>
''' <param name="connectionString">The connection string to use
''' for connecting to the database.</param>
''' <param name="dbProviderFactoryString">Based on the provider selected
''' by the user the dbProviderFactoryString to use for the connection.</param>
''' <returns>A generic database</returns>
''' <remarks>
''' This is a private function of this class. Provides the hook
''' into the Enterprise Library.
''' </remarks>
Private Function GetDatabase(ByVal connectionString As String, _
ByVal dbProviderFactoryString As String) As Database
Return New GenericDatabase(connectionString, _
System.Data.Common.DbProviderFactories.GetFactory(dbProviderFactoryString))
End Function
''' <summary>
''' Pass in a sql string to get an IDataReader.
''' </summary>
''' <param name="sql">The sql string to use.</param>
''' <returns>An IDataReader</returns>
''' <remarks>
''' Depending on the provider factory chosen when the class was instantiated,
''' the returned data reader is converted to the appropriate type
''' - this is required for example to ensure that the HasRows method can be
''' used in OleDbDataReaders.
''' </remarks>
Public Function GetDataReader(ByVal sql As String) As IDataReader
dbCommand = db.GetSqlStringCommand(sql)
Select Case dbProvider
Case enumProviderFactory.OleDb
dbread = CType(db.ExecuteReader(dbCommand), OleDbDataReader)
Case enumProviderFactory.Sql
dbread = CType(db.ExecuteReader(dbCommand), SqlDataReader)
Case enumProviderFactory.Odbc
dbread = CType(db.ExecuteReader(dbCommand), OdbcDataReader)
Case enumProviderFactory.Oracle
dbread = CType(db.ExecuteReader(dbCommand), OracleDataReader)
End Select
Return dbread
End Function
''' <summary>
''' Pass in a sql string and tablename to get a dataset.
''' </summary>
''' <param name="sql">The sql string to use for getting the data.</param>
''' <param name="TableName">The tablename to assign for the table in the dataset.
''' </param>
''' <returns>A dataset</returns>
''' <remarks>Use when you need a new dataset; Use LoadDataSet
''' if you want to load a new table
''' in an existing dataset.
''' </remarks>
Public Function GetDataSet(ByVal sql As String, ByVal TableName As String) As DataSet
Dim ds As DataSet = New DataSet
ds = db.ExecuteDataSet(CommandType.Text, sql)
ds.Tables(0).TableName = TableName
Return ds
End Function
''' <summary>
''' Pass in a sql string, an existing dataset (ByRef)
''' and the name of the table to be added to the dataset.
''' </summary>
''' <param name="sql">The sql string to use for getting the data.</param>
''' <param name="ds">An existing database to which the data
''' is to be added as a table.</param>
''' <param name="TableName">The table name to use when adding
''' the data to the dataset.</param>
''' <remarks>
''' Use when you want to load a new table to an existing dataset.
''' Use GetDataSet when you need a new dataset.
''' </remarks>
Public Sub LoadDataSet(ByVal sql As String, ByRef ds As DataSet, _
ByVal TableName As String)
db.LoadDataSet(CommandType.Text, sql, ds, New String() {TableName})
End Sub
''' <summary>
''' Pass in a sql string and tablename to get a datatable.
''' </summary>
''' <param name="sql">The sql string to use for getting the data.</param>
''' <param name="TableName">The tablename to assign for the returned datatable.
''' </param>
''' <returns>A DataTable</returns>
''' <remarks>Use to get a DataTable. See all GetTableWithZero method.</remarks>
Public Function GetDataTable(ByVal sql As String, _
ByVal TableName As String) As DataTable
Dim ds As DataSet = New DataSet
ds = db.ExecuteDataSet(CommandType.Text, sql)
ds.Tables(0).TableName = TableName
Return ds.Tables(TableName)
End Function
''' <summary>
''' Use to get an IDbDataAdapter.
''' </summary>
''' <returns>Returns an IDbDataAdapter</returns>
''' <remarks>When you instantiated the class
''' you have already passed in the connection string and
''' which provider to use - so you don't need to pass this info. again!
''' </remarks>
Public Function GetDataAdapter() As IDbDataAdapter
Return db.GetDataAdapter()
End Function
''' <summary>
''' Pass in sql string containing an action query.
''' </summary>
''' <param name="sql">Sql string containing action query.</param>
''' <returns>Returns the word "Success" if the action query completed successfully;
''' If the action query did not complete successfully
''' the return string will contain the error message.
''' </returns>
''' <remarks>
''' User to check return value and handle any errors as appropriate.
''' <param>
''' A template as follows is suggested for handling errors within a Try Catch block:
''' </param>
''' <param>
''' <code>
''' retval = ELW.DoActionQuery(sql)
''' If Not retval = "Success" Then
''' Err.Raise(513, , retval)
''' Exit Try
''' End If
'''
''' </code>
''' </param>
''' Use the RowsAffected property to retrieve the number of rows
''' affected after a DoActionQuery.
''' <param></param>
''' Use the Identity property to retrieve the id of the last row
''' inserted after an insert query is executed.
''' <param></param>
''' The Identity property returns the @@IDENTITY for OleDb,
''' the SCOPE_IDENTITY() for Sql and zero for the
''' other two providers, ODBC and Oracle.
''' <param></param>
''' For ODBC, setting this to zero as the ODBC connection string
''' may be pointing to a text file for example.
''' <param></param>
''' For Oracle, there is no such thing as scope identity -
''' have to use sequences, so returning zero for this too.
''' </remarks>
Public Function DoActionQuery(ByVal sql As String) As String
Dim retval As String
Dim cmdGetIdentity As DbCommand
Select Case dbProvider
Case enumProviderFactory.OleDb
dbConn = New OleDbConnection(conString)
dbConn.Open()
dbCommand = New OleDbCommand(sql, dbConn)
cmdGetIdentity = New OleDbCommand()
cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
cmdGetIdentity.Connection = dbConn
Case enumProviderFactory.Sql
dbConn = New SqlConnection(conString)
dbConn.Open()
dbCommand = New SqlCommand(sql, dbConn)
cmdGetIdentity = New SqlCommand()
cmdGetIdentity.CommandText = _
"SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]"
cmdGetIdentity.Connection = dbConn
Case enumProviderFactory.Odbc
dbConn = New OdbcConnection(conString)
dbConn.Open()
dbCommand = New OdbcCommand(sql, dbConn)
Case enumProviderFactory.Oracle
dbConn = New OracleConnection(conString)
dbConn.Open()
dbCommand = New OracleCommand(sql, dbConn)
End Select
dbRowsAffected = 0
Try
dbRowsAffected = dbCommand.ExecuteNonQuery()
Select Case dbProvider
Case enumProviderFactory.OleDb, enumProviderFactory.Sql
dbIdentity = cmdGetIdentity.ExecuteScalar().ToString()
Case enumProviderFactory.Odbc, enumProviderFactory.Oracle
dbIdentity = 0
' For ODBC setting this to zero as the ODBC connection string may be
' pointing to a text file for example
' For Oracle there is not such thing as scope identity -
' have to use sequences
' so returning o for this too
End Select
retval = "Success" ' you can check for this in the string and do whatever
Catch exc As Exception
retval = "Error: " & exc.Message & "<br><br>" & _
exc.InnerException.Message & "."
Finally
dbCommand = Nothing
cmdGetIdentity = Nothing
dbConn.Close()
End Try
Return retval
End Function
''' <summary>
''' Pass in the SQLString, ValueField (first column) and TextField
''' (second column) to get a DataTable with an additional zero record
''' containing "FirstDummyRecord" for the first column and an empty string
''' for the second column.
''' </summary>
''' <param name="SQLString">The sql string to use for getting the data.</param>
''' <param name="ValueField">The name of the value field
''' which will be assigned to the first column.</param>
''' <param name="TextField">The name of the text field which will be
''' assigned to the second column.</param>
''' <returns>A DataTable</returns>
''' <remarks>
''' This function returns a data table including a first dummy record which is empty.
''' This data table can then be bound to a dropdown list to enable
''' the user to unselect by choosing the first empty record;
''' Then in code we can check for "FirstDummyRecord"
''' and act appropriately. See all GetDataTable method.
''' </remarks>
Public Function GetTableWithZero(ByVal SQLString As String, _
ByVal ValueField As String, ByVal TextField As String) As DataTable
Dim dtTable As DataTable = New DataTable
Dim dr As DataRow
Dim dcDescription As New DataColumn("description")
Dim dcID As New DataColumn("id")
dtTable.Columns.Add(dcID)
dtTable.Columns.Add(dcDescription)
' Add zero record
dr = dtTable.NewRow()
dr(0) = "FirstDummyRecord"
dr(1) = ""
dtTable.Rows.Add(dr)
dbread = db.ExecuteReader(CommandType.Text, SQLString)
While (dbread.Read())
dr = dtTable.NewRow()
dr(0) = dbread(ValueField)
dr(1) = dbread(TextField)
dtTable.Rows.Add(dr)
End While
dbread.Close()
Return dtTable
End Function
End Class
以下辅助函数用于加密/解密web.config 中的 appSettings
以及用于在运行时动态地组装和存储连接字符串的应用程序变量。
Imports System.Configuration
Imports system.Web.Configuration
Imports Microsoft.Practices.EnterpriseLibrary.Security.Cryptography
Module Common
Private Const symmProvider As String = "SelfServiceCryptoProvider"
Public Sub ProtectSection(ByVal sectionName As String, _
ByVal provider As String, ByVal VirtualPath As String)
Dim config As Configuration = _
WebConfigurationManager.OpenWebConfiguration(VirtualPath)
Dim section As ConfigurationSection = config.GetSection(sectionName)
If ((section.SectionInformation.IsProtected = False) AndAlso _
(section.ElementInformation.IsLocked = False)) Then
' Protect (encrypt) the section.
section.SectionInformation.ProtectSection(provider)
' Save the encrypted section.
section.SectionInformation.ForceSave = True
config.Save(ConfigurationSaveMode.Full)
'config.Save()
End If
End Sub
Public Sub UnProtectSection(ByVal sectionName As String, _
ByVal VirtualPath As String)
Dim config As Configuration = _
WebConfigurationManager.OpenWebConfiguration(VirtualPath)
Dim section As ConfigurationSection = config.GetSection(sectionName)
'If ((section.SectionInformation.IsProtected = False) AndAlso _
' (section.ElementInformation.IsLocked = False)) Then
' UnProtect (decrypt) the section.
section.SectionInformation.UnprotectSection()
' Save the decrypted section.
section.SectionInformation.ForceSave = True
config.Save(ConfigurationSaveMode.Full)
'config.Save()
'End If
End Sub
Public Function EncryptValue(ByVal UnEncryptedValue As String) As String
Dim RetVal As String
If (Not (UnEncryptedValue Is Nothing) AndAlso _
UnEncryptedValue.Trim().Length > 0) Then
RetVal = Cryptographer.EncryptSymmetric(symmProvider, UnEncryptedValue & "")
End If
Return RetVal
End Function
Public Function DecryptValue(ByVal EncryptedValue As String) As String
Dim RetVal As String
If (Not (EncryptedValue Is Nothing) _
AndAlso EncryptedValue.Trim().Length > 0) Then
RetVal = Cryptographer.DecryptSymmetric(symmProvider, EncryptedValue & "")
End If
Return RetVal
End Function
End Module
我在 Global.asax 中应用程序启动时生成我的连接字符串,如下所示 - 密码从web.config中检索。请注意,连接字符串以加密形式存储在应用程序变量中,并在实例化 EntLibWrapper
类时解密。另请注意,web.config 中的 appSettings
根据从数据库检索到的参数进行加密或解密 - 这使开发人员能够在解密该节后稍后添加新的 appSettings
。
Const UserId As String = "YourUserNameGoesHere"
Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
' Fires when the application is started
Dim Password As String = ConfigurationManager.AppSettings("SYSPWD")
Application("News") = EncryptValue_
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("../fpdb/News.mdb") & ";Jet OLEDB:System Database=" _
& Server.MapPath("../fpdb/SelfService.mdw") & ";User ID=" & _
UserId & ";Password=" & Password & ";")
Dim ELW As New EntLibWrapper(DecryptValue(Application("News")), _
EntLibWrapper.enumProviderFactory.OleDb)
Dim dbread As OleDbDataReader
Dim sql As String
Dim EncryptAppSettings As Boolean
' GET PARAMETER - EncryptAppSettings
sql = "SELECT tblParameters.*"
sql = sql & " FROM [tblParameters]"
sql = sql & " WHERE (((tblParameters.RecordId)='EncryptAppSettings'));"
dbread = ELW.GetDataReader(sql)
If dbread.HasRows Then
Do While dbread.Read()
EncryptAppSettings = dbread.Item("MyBoolean")
Loop
Else
EncryptAppSettings = True
End If
dbread.Close()
ELW.Close()
If EncryptAppSettings = True Then
'Call ProtectSection("appSettings", _
"DataProtectionConfigurationProvider", "/timetable/Documentation")
'Call ProtectSection("appSettings", _
"RSAProtectedConfigurationProvider", "/timetable/Documentation")
Call ProtectSection("appSettings", _
"SelfServiceConfigurationProvider", "/timetable/Documentation")
Else
Call UnProtectSection("appSettings", "/timetable/Documentation")
End If
End Sub
然后,在我需要获取数据的地方,获取数据读取器并将其绑定到 dropdownlist
的**代码**如下:
Dim ELW As New EntLibWrapper(DecryptValue(Application("News")), _
EntLibWrapper.enumProviderFactory.OleDb)
Dim dbread As OleDbDataReader
Dim sql As String
' LOAD ddCountry
sql = "SELECT qryNewsCountry.*"
sql = sql & " FROM qryNewsCountry;"
dbread = ELW.GetDataReader(sql)
ddCountry.DataSource = dbread
ddCountry.DataTextField = "CountryName"
ddCountry.DataValueField = "Country"
If Session("SelectedCountryIndex") Is Nothing Then
ddCountry.SelectedIndex = 0
Else
ddCountry.SelectedIndex = Session("SelectedCountryIndex")
End If
ddCountry.DataBind()
dbread.Close()
' Do other things with the ELW object and then finally close it
ELW.Close()
为了使加密/解密工作,我们需要 web.config 条目。您下载的 示例 web.config 包含使用 Enterprise Library 配置工具创建的条目。
以下链接有助于您全面了解有关加密/解密的问题。这也有助于您创建加密密钥。
请注意,即使您不关心加密/解密,也可以使用 EntLibWrapper
类。该类不会强制您加密 web.config 中的 appSettings
或存储在应用程序变量中的连接字符串。您也不必将连接字符串存储在应用程序变量中 - 如果更适合您的需求,您也可以从数据库中检索它。
EntLibWrapper
类具有以下方法/属性/枚举/引用:
方法
Close
LoadDataSet
新建
DoActionQuery
GetDataAdapter
GetDatabase
GetDataReader
GetDataSet
GetDataTable
GetTableWithZero
属性
Database
- 这使用户能够访问Enterprise Library
中的GenericDatabase
中的其他方法和属性。Identity
- 在调用DoActionQuery
方法插入记录后,用户可以使用此属性检索最后插入记录的 id。RowsAffected
- 在调用DoActionQuery
方法后,用户可以从此属性检索受影响的行数。
枚举
enumProviderFactory
参考文献
Microsoft.Practices.EnterpriseLibrary.Common
Microsoft.Practices.EnterpriseLibrary.Data
Microsoft.Practices.EnterpriseLibrary.Security.Cryptography
System.Configuration
System.Data.Common
System.Data.Odbc
System.Data.OleDb
System.Data.OracleClient
System.Data.SqlClient
System.Web.Configuration
下载的 EntLibWrapper.CHM 文件应为您提供有关 EntLibWrapper
类的更多详细信息。当您在项目中类时,您会注意到它与 Visual Studio 中的 Intellisense 和对象浏览器集成得很好。我也能够使用 nDocs 轻松生成 CHM 文件。如果您想知道这是如何实现的,可以看看以下链接:
感谢那些根据我的反馈改进了该类的朋友。希望这个 enterprise library 包装器类也能对他人有所帮助。
干杯
Raja Lakshman
历史
- 2009 年 6 月 11 日
- 初次发布
- 2009 年 6 月 16 日
- 添加了
RowsAffected
和Identity
属性 - 2009 年 6 月 26 日
- 添加了
LoadDataSet
方法 - 添加了
Encryption
/Decryption
函数 - 使用代码中的 XML 注释编译了帮助文件。