ASP.NET：如何从web.config ConnectionString创建连接？

How do you construct a DbConnection based on a provider name?

如何根据提供者名称构建DbConnection?

Sample provider names

样本提供者名称

• System.Data.SqlClient
• System.Data.OleDb
• System.Data.Odbc
• FirebirdSql.Data.FirebirdClient

i have connection strings stored in my IIS server's web.config file:

我有连接字符串存储在我的IIS服务器的web.config文件中:

<connectionStrings>
  <add name="development"
        connectionString="Provider = IBMDA400; Data Source = MY_SYSTEM_NAME; User Id = myUsername; Password = myPassword;" 
        providerName="System.Data.OleDb" />
  <add name="live" 
        connectionString="usd=sa;pwd=password;server=deathstar;" 
        providerName="System.Data.Odbc" />
  <add name="testing" 
        connectionString="usd=sa;pwd=password;server=deathstar;" 
        providerName="System.Data.SqlClient" />
  <add name="offline"
        connectionString="Server=localhost;User=SYSDBA;Password=masterkey;Charser=NONE;Database=c:\data\mydb.fdb"
        providerName="FirebirdSql.Data.FirebirdClient"/>

You can see they all use different providers. When it comes time for me to create a connection, i have to know what kind of DbConnection to create, e.g.:

您可以看到他们都使用不同的提供商。当我需要创建连接时,我必须知道要创建什么样的DbConnection,例如:

• SqlConnection
• OleDbConnection
• OdbcConnection
• FbConnection

The connectionStrings entries contains a providerName, but these aren't the names of DbConnection descendant classes, but appear to be a namespace

connectionStrings条目包含providerName,但这些不是DbConnection后代类的名称,但似乎是名称空间

How do i turn construct a DbConnection based on a string providerName?

如何根据字符串providerName转换构造DbConnection?

---

public DbConnection GetConnection(String connectionName)
{
    //Get the connectionString infomation
    ConnectionStringSettings cs = 
          ConfigurationManager.ConnectionStrings[connectionName];
    if (cs == null)
       throw new ConfigurationException("Invalid connection name \""+connectionName+"\");

    //Create a connection based on the provider
    DbConnection conn = new DbConnection();

}

3 个解决方案

#1

If you go this route, I think you'll want to use the DbProviderFactories class to get a DbProviderFactory that you can use to construct the connection. I haven't tried this code out, but I think it will work. It's possible that you may need to look up the provider name using the GetFactoryClasses method on the DbProviderFactories class and use the InvariantName.

如果你走这条路线,我想你会想要使用DbProviderFactories类来获得可用于构建连接的DbProviderFactory。我没有尝试过这个代码,但我认为它会起作用。您可能需要使用DbProviderFactories类上的GetFactoryClasses方法查找提供程序名称,并使用InvariantName。

public DbConnection GetConnection(String connectionName)
{
   //Get the connection string info from web.config
   ConnectionStringSettings cs= 
         ConfigurationManager.ConnectionStrings[connectionName];

   //documented to return null if it couldn't be found
   if (cs == null)
      throw new ConfigurationErrorsException("Invalid connection name \""+connectionName+"\"");

   //Get the factory for the given provider (e.g. "System.Data.SqlClient")
   DbProviderFactory factory = 
         DbProviderFactories.GetFactory(cs.ProviderName);

   //Undefined behaviour if GetFactory couldn't find a provider.
   //Defensive test for null factory anyway
   if (factory == null)
      throw new Exception("Could not obtain factory for provider \""+cs.ProviderName+"\"");

   //Have the factory give us the right connection object      
   DbConnection conn = factory.CreateConnection();

   //Undefined behaviour if CreateConnection failed
   //Defensive test for null connection anyway
   if (conn == null)
      throw new Exception("Could not obtain connection from factory");

   //Knowing the connection string, open the connection
   conn.ConnectionString = cs.ConnectionString;
   conn.Open()

   return conn;
}

#2

Check out this Hanselman blog on adding custom build types for different connection strings names, it looks like it may fit what you want to accomplish in a different way than using the provider types.

查看这个Hanselman博客,为不同的连接字符串名称添加自定义构建类型,看起来它可能适合您想要以与使用提供程序类型不同的方式完成的任务。

#3

If the providerName for the particular connection name (dev, test, prod) never changes why cant you do a switch on the connectionName param for your method and set the providerName instance that way?

如果特定连接名称(dev,test,prod)的providerName永远不会更改,为什么不能为您的方法切换connectionName参数并以这种方式设置providerName实例?