The data component of the .NET Framework (namely ADO.NET) works with two layers. These layers are called the connected and the disconnected layer. As you can guess, their names refers to the fact whether they are actually connected to a data source.
But before we dig into their details, let’s see what do they have in common – the fundamentals of ADO.NET.
To connect to a data source, your very first step will be to chose a provider. There are four providers built in the .NET Framework 3.5, these are the OleDbProvider, the OdbcProvider, the SqlProvider and the OracleProvider. You typically set the provider in the connectionString section of the web.config. Each provider declares four classes to manipulate data, these are:
- Command: represents a database command (stored procedure or ad hoc).
- Connection: represents a database connection. You typically refer to the web.config connectionStrings section to retrieve one.
- DataReader: provides a read-only, forward-only way to get data from a database.
- DataAdapter: lets you fill data objects of the disconnected layer, and write back the changes to the database.
Each of these classes gets the prefix of the provider name. So for example, when you want to connect to a SQL database, you will use a SqlConnection class to do so. Let’s take a closer look to these classes:
When you create a connection class, you should provide a connection string for it. A connection string stores the following information:
- Where is the server you’d like to connect?
- Which database you’d like to use?
- How should that authenticate you?
For example, you’d provide a connection string like this:
String ConnectionString =”Data Source=127.0.0.1;Initial Catalog=Northwind;Integrated Security=true;”
This string would connect to your local instance of the Northwind database, with Windows Authentication. For maintenance and caching reasons, you should store your connection strings in your web.config file. Connection classes must perform two tasks: opening and closing the connection to the database. As database connections are one of the most expensive resources you can deal with, it’s a rule of thumb to let a connection open for the shortest possible time.
As database connections are very expensive, you have the ability to pool them. You can specify pooling options, such as Max and Min Pool Size, Connection Lifetime, etc. in the connection string. Be aware of that only connection strings that are exactly the same can be pooled. A whitespace here, a semicolon there, and you have two pools. So place your connection strings in the web.config, where they belong.
A Command object represents a database query. It can be a stored procedure or a static ad hoc SQL query. You set it via the CommandType property. To use a command, you need three things at least: the command text, a connection object where the command belongs, and an open connection. Fail to provide one, and you will face exceptions.
Commands can even have parameters. Assign them to the Parameters collection (a good choice is the AddWithValue method) to prevent SQL Injection Attacks.
There are three ways to execute a command:
- ExecuteNonQuery: you’ll typically use this method when dealing with inserts, updates or deletes. There are two possible return values: -1, if the command specified was a select command, or the number of rows affected by the query.
- ExecuteScalar: returns the first row of the first column of the result set. Typically used in cases of COUNT or SUM queries.
- ExecuteReader: returns a DataReader object which lets you iterate over the result set.
DataReaders are read-only, forward-only cursors. They hold only one row of the result set at a given time. They provide methods to iterate over the data (only forward!) and to get the value at the specified position of the current row. The main methods are:
- Read: advances the rows cursor to the next row. Returns true until there is a next row.
- GetValue: returns the stored value at the specified index of the currently selected row.
- GetValues: saves the values of the current row into an array.
- GetInt32, GetDateTime, Get…: returns the value of the specified index within the current row, casted into the variable type defined in the method name, or an InvalidCastException.
- NextResult: moves the cursor to the next result set (if any).
- Close: closes the reader. If there are any return values, they will only be accessible after this method.
Transactions are procedures with multiple commands which fail or succeed together. A common example of transactions is money transfer. If either of the two steps of withdraw and deposit fails, the whole procedure must be rolled back. Transactions are ACID: Atomic, Consistent, Isolated and Durable.
There are three types of transactions you can work with:
- Stored Procedure Transactions
- Client-initiated transactions
- COM+ transactions
Stored procedure transactions are simply transactions defined in the database as a stored procedure. You’ll use the BEGIN TRAN(SACTION) and the END TRAN(SACTION) commands with COMMIT or ROLLBACK depended whether an error occurred. COM+ transactions aren’t covered in 70-562, so let’s take a look at the Client-initiated transactions.
To use one, you’d need a Transaction class (for example SqlTransaction). After the opening of the connection, you’d need to call the connection’s BeginTransaction method, and set the Transaction property of the commands you’d like to treat as a transaction. Before closing the connection, you should call the transaction’s Commit or RollBack method. A simple example:
SqlConnection connection = new SqlConnection(“ConnectionString”);
SqlCommand cmd1 = new SqlCommand(“INSERT INTO Employes (FirstName) VALUES (‘Ted’)”, connection);
SqlCommand cmd2 = new SqlCommand(“INSERT INTO Employes (FirstName) VALUES(‘Josh’), connection);
SqlTransaction tran = null;
tran = connection.BeginTransaction();
cmd1.Transaction = tran;
cmd2.Transaction = tran;