ADO.NET Connections are one of the simplest ways to connect to a database from a C# application. They rely on the use of a provider and a connection string that points to your database to perform queries against.
Common Data Provider Classes
Many of the following are classes that are commonly used to query databases and their related namespaces :
All of these are commonly used to access data through C# and will be commonly encountered throughout building data-centric applications. Many other classes that are not mentioned that implement the same
FooDataReader classes can be expected to behave the same way.
Common Access Pattern for ADO.NET Connections
A common pattern that can be used when accessing your data through an ADO.NET connection might look as follows :
Or if you were just performing a simple update and didn't require a reader, the same basic concept would apply :
You can even program against a set of common interfaces and not have to worry about the provider specific classes. The core interfaces provided by ADO.NET are:
- IDbConnection - for managing database connections
- IDbCommand - for running SQL commands
- IDbTransaction - for managing transactions
- IDataReader - for reading data returned by a command
- IDataAdapter - for channeling data to and from datasets
A Connection String is a string that specifies information about a particular data source and how to go about connecting to it by storing credentials, locations, and other information.
Storing Your Connection String
Typically, a connection string will be stored within a configuration file (such as an
web.config within ASP.NET applications). The following is an example of what a local connection might look like within one of these files :
This will allow your application to access the connection string programatically through
WidgetsContext. Although both
Integrated Security=SSPI and
Integrated Security=True perform the same function;
Integrated Security=SSPI is preferred since works with both SQLClient & OleDB provider where as
Integrated Security=true throws an exception when used with the OleDb provider.
Different Connections for Different Providers
Each data provider (SQL Server, MySQL, Azure, etc.) all feature their own flavor of syntax for their connection strings and expose different available properties. ConnectionStrings.com is an incredibly useful resource if you are unsure about what yours should look like.
Entity Framework Connections
Entity Framework exposes abstraction classes that are used to interact with underlying databases in the form of classes like
DbContext. These contexts generally consist of
DbSet<T> properties that expose the available collections that can be queried :
DbContext itself will handle making the connections with the databases and will generally read the appropriate Connection String data from a configuration to determine how to establish the connections :
Executing Entity Framework Queries
Actually executing an Entity Framework query can be quite easy and simply requires you to create an instance of the context and then use the available properties on it to pull or access your data
Entity Framework also provides an extensive change-tracking system that can be used to handle updating entries within your database by simply calling the
SaveChanges() method to push changes to the database :