Creating Commands and Retrieving Data 

The DbProviderFactory provides methods for connecting to data and for creating parameterized commands.

Connecting to the Data Source

The CreateConnection method of the DbProviderFactory creates a DbConnection object that connects to the data provider. In the code fragment shown here, the connection string is retrieved from the configuration file by using the ConfigurationSettings.AppSettings property. The DbConnection is opened based on the provider that is specified in the DbProviderFactory object.

using(DbConnection conn = dataFactory.CreateConnection()) 
{
   conn.ConnectionString = 
     ConfigurationSettings.AppSettings["connectionString"];
   conn.Open();
...
}

Using conn As New DbConnection = dataFactory.CreateConnection()
    conn.ConnectionString = _
      ConfigurationSettings.AppSettings("connectionString")
...
End Using

Creating Commands

The CreateCommand method of the DbConnection object creates a DbCommand object. In the following example, the DbCommand object creates a strongly-typed SqlCommand object because the DbProviderFactory was created with the provider name "System.Data.SqlClient". The following code fragment creates a DbCommand and a DbParameter object to retrieve a store name in the AdventureWorks database for a valid CustomerID.

DbProviderFactory dataFactory = 
 DbProviderFactories.GetFactory("System.Data.SqlClient");
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = 
  "SELECT Name FROM Sales.Store WHERE CustomerID = @CustomerID";
DbParameter param = dataFactory.CreateParameter();
param.ParameterName = "@CustomerID";
param.Value = txtCustomerID.Text;
cmd.Parameters.Add(param);
string customerName = cmd.ExecuteScalar();

Dim dataFactory As DbProviderFactory = _
 DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim cmd As DbCommand = conn.CreateCommand()
cmd.CommandText = _
  "SELECT Name FROM Sales.Store WHERE CustomerID = @CustomerID"
Dim param As DbParameter = dataFactory.CreateParameter()
param.ParameterName = "@CustomerID"
param.Value = txtCustomerID.Text
cmd.Parameters.Add(param)
Dim customerName As String = cmd.ExecuteScalar()

Handling Parameters

One drawback to using a parameterized DbCommand is that code requiring parameters will work only with providers that support identical syntax. The OLEDB, SqlClient, and Oracle providers all use different syntax. For example, SQL syntax for naming and specifying parameters uses the @ symbol, OLEDB parameter syntax requires a question mark (?) as a parameter placeholder, and the Oracle provider uses a colon (:).

You will need to use basic ANSI SQL syntax and to avoid parameters as well as provider-specific language features to create a solution that will work across different providers without creating custom code blocks to accommodate provider-specific SQL syntax. You can create a DbCommand object and use string concatenation to dynamically construct valid SQL statements that will work across multiple providers. You will also need to work around the problem that different providers also support different data type delimiters for date/time data types. The following code fragment illustrates how you can concatenate a value for a WHERE clause retrieved from a variable to the SELECT statement.

DbCommand cmd = conn.CreateCommand();
cmd.CommandText = 
  "SELECT Name FROM Sales.Store WHERE CustomerID=" + CustID.ToString();
string customerName = cmd.ExecuteScalar();

Dim cmd As DbCommand = conn.CreateCommand()
cmd.CommandText = _
  "SELECT Name FROM Sales.Store WHERE CustomerID=" & CustID.ToString()
Dim customerName As String = cmd.ExecuteScalar()

If you use string concatenation for building dynamic SQL in your application, you must carefully validate all user input. Failure to do so could leave your application vulnerable to SQL Injection attack. See Validating User Input for additional information about input validation.

See Also

Concepts

Working with Factories
Provider Independent Model Overview
Obtaining the DbProviderFactory
Retrieving Data with a DbDataAdapter