So far, we have stored connection strings in the app.config file, in the <appSettings> and <connectionStrings> sections:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="SqlConnStr" value="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=School"/>
</appSettings>
<connectionStrings>
<add name="OleDbConnStr" connectionString="Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=School"/>
</connectionStrings>
</configuration>
Different lookup procedures are required:
string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
versus
string connStr = ConfigurationManager.ConnectionStrings["OleDbConnStr"].ConnectionString;
Looking up with ConfigurationManager.ConnectionStrings[] gives a ConnectionStringSettings object. Other than the ProviderName and ConnectionString properties, this object is pretty boring.
OLE DB Provider values:
| Provider=value | Provider |
|---|---|
| Microsoft.Jet.OLEDB.4.0 | OLE DB Provider for SQL Server |
| SQLOLEDB | Microsoft OLE DB Provider for SQL Server |
| MSDAORA | Microsoft OLE DB Provider for Oracle |
Using Windows authentication:
| Property=value | Works for |
|---|---|
| Integrated Security=true | SQL Server |
| Integrated Security=SSPI | SQL Server or OLE DB |
| Trusted_Connection=yes | ODBC only |
Here are some majorly over-specified connection strings. These were generated in an earlier version of VS. Later VS generates much smaller strings:
"Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.
"In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.
"Connection pooling reduces the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks to see if there is an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of actually closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.
"Only connections with the same configuration can be pooled. ADO.NET keeps several pools concurrently, one for each configuration. Connections are separated into pools by connection string, as well as by Windows identity when integrated security is used.
"Pooling connections can significantly enhance the performance and scalability of your application. Connection pooling is enabled by default in ADO.NET. Unless you explicitly disable it, the pooler will optimize the connections as they are opened and closed in your application. You can also supply several connection string modifiers to control connection pooling behavior."
| Method/Property | Description |
|---|---|
| ConnectionString | The connection string connect with. |
| ConnectionTimeout | How long to wait. |
| Database | The name of the database. |
| DataSource | The location of the database. |
| GetSchema() | Returns a DataSet that contains schema information from the data source. |
| Open() | Open. |
| Close() | Close. |
| CreateCommand() | Create an IDbCommand object tied to this connection. |
| BeginTransaction() | Starts a transaction on the connection. Returns a transaction object. You must Commit() or Rollback() to finish the transaction. |
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(); // you can supply a connection string to the c-tor to initialize csb.DataSource = "localhost"; csb.InitialCatalog = "School"; csb.IntegratedSecurity = true;
New in 2.0.
public abstract class DbProviderFactory
{
public virtual DbCommand CreateCommand();
public virtual DbCommandBuilder CreateCommandBuilder();
public virtual DbConnection CreateConnection();
public virtual DbConnectionStringBuilder CreateConnectionStringBuilder();
public virtual DbDataAdapter CreateDataAdapter();
public virtual DbDataSourceEnumerator CreateDataSourceEnumerator();
public virtual DbParameter CreateParameter();
public virtual Db Create();
...
}
The following is from the Connected\Connections solution:
Some line breaks added:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="provider" value="System.Data.SqlClient" />
</appSettings>
<connectionStrings>
<add name ="SqlConnStr"
connectionString = "Data Source=localhost;Initial Catalog=School; Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
Code:
private static void WithConnectionFactory()
{
string provider = ConfigurationManager.AppSettings["provider"];
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
string connStr = ConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = connStr;
DbCommand cmd = factory.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from Student";
conn.Open();
DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
Console.WriteLine("{0}\t{1}\t{2}", reader["Id"], reader["FirstName"], reader["LastName"]);
reader.Close();
}
Note that the value, System.Data.SqlClient, of the provider property is not random. (The property name, provider, however, is arbitrary.) This value must match the invariant attribute of one of the entries in the DbProviderFactories section:
<system.data>
<DbProviderFactories>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient"
support="FF" description=".Net Framework Data Provider for SqlServer"
type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.3600.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089" />
...
</DbProviderFactories>
</system.data>