Скачать или смотреть How to Retrieve the Correct DbConnection Object from a Connection String in C#

Learn how to dynamically create a `DbConnection` object in C# based on a connection string. This guide explains the steps involved, helping you avoid complex combinations of connection strings.
---
This video is based on the question https://stackoverflow.com/q/185474/ asked by the user 'johnc' ( https://stackoverflow.com/u/5302/ ) and on the answer https://stackoverflow.com/a/185571/ provided by the user 'Mark Cidade' ( https://stackoverflow.com/u/1659/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, comments, revision history etc. For example, the original title of the Question was: C# Retrieving correct DbConnection object by connection string

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 2.5' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Retrieving the Correct DbConnection Object by Connection String in C#

When developing applications in C#, you often need to connect to various databases using different connection strings. However, determining which DbConnection object to create can be quite challenging, especially if your application receives connection strings without any predefined context. This article will guide you through a solution that dynamically identifies the appropriate DbConnection type based on the provided connection string.

The Challenge

If you're faced with a situation where a connection string is passed into a function, how do you create the correct DbConnection object? For example:

You may have connection strings for SQL Server, Oracle, OLE DB, etc.

It's not always clear what type of connection string you're working with, so hardcoding logic based on string formats is not feasible.

You want to avoid writing extensive code to handle all possible variations of connection strings.

The Solution

Fortunately, there's a way to handle this using the DbConnectionStringBuilder and DbProviderFactories classes in ADO.NET. Here’s a breakdown of how to implement a method that retrieves the correct DbConnection object based on the provided connection string.

Step 1: Create the GetConnection Method

You can create a method called GetConnection that takes a connection string as an input parameter. The goal of this method is to return a DbConnection object.

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Identify the Provider

You'll need to check whether the connection string includes a specific key for the provider. If it doesn't, the code will look for the connection string in the application's configuration settings.

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Validate the Provider

Once you have the providerName, you need to check if it exists among the registered DbProviderFactories. If it does, you can create a connection using that factory.

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Set the Connection String and Return

Attach the original connection string to the created DbConnection object and return it. If no valid provider is found, return null.

[[See Video to Reveal this Text or Code Snippet]]

Complete Code Example

Here’s the complete code for the GetConnection function:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By implementing the GetConnection method, you can effectively create the appropriate DbConnection object at runtime based solely on the connection string input. This approach eliminates the need for manual parsing of the string or hardcoding connection types, making your code cleaner and more maintainable.

Now you're equipped with the knowledge to dynamically handle different database connections in your C# applications efficiently! Happy coding!