ADO.NET components access the database in two ways: in a connected way and in a disconnected way. In the connected access method, we deal with synchronous communication with a database instance, sending any instances of data definition language (DDL) or data manipulation language (DML). In the disconnected access method, we store a pseudo-copy of data and schema of any table locally, with the ability to save changes (if any) asynchronously.
In the end, any time we deal with ADO.NET, as with low level classes, or with high level OR/M classes, we always produce some SQL statement. This statement may be the result of an OR/M data provider that translate object-oriented code in SQL code or may be the result of our direct text entry like when we write the SQL by ourselves or like when we simply invoke a stored procedure (an SQL statement stored on the database itself). Eventual parameters may be used to parameterize the SQL execution. Never concatenate strings to produce an SQL statement because of poor performance execution of the string-format-like value formatting, heavily increased attack surface for SQL-injection exploits, and bad testability.
A simplified view of ADO.NET components
The simplest database access is when we make a simple SELECT statement against our persistence storage and try to read its result, later. If the result is in a tabular form, we must use a DataReader object, which is an enumerator-like class that uses a client-side cursor to stream data rows, giving us the ability to read columns data as we wish, by column index or name. As a simplified option, we can read the first row's first column data of any SELECT with a direct request, without having to stream the result-set with a cursor. Here is an example:
//a connection with specific connection-string
using (var cn = new SqlConnection("data source=(local);initial catalog=TestDB;integrated security=true;"))
//a command with custom SQL statement
using (var cm = new SqlCommand("SELECT [Number],[Date] from [Invoice]", cn))
{
//open the connection
cn.Open();
//execute the command statement and catch the result-set into a data reader
using (var dr = cm.ExecuteReader())
//read until cursor signal new rows availability
while (dr.Read())
//reads columns data
Console.WriteLine("Number:{0} Date:{1:d}", dr[0], dr["Date"]);
}Because of the extremely high customizability of the ADO.NET connected classes architecture, it could easily run into performance issues. A poor-quality SQL statement made by the developer could also create another performance issue.
If the using keyword is not used properly, the preceding code can open several database connections, wasting client and server resources and limiting future usage of the database server itself because of the limited number of connections available. This is another example showing a scalar request made against the database:
//a connection with specific connection-string
using (var cn = new SqlConnection("data source=(local);initial catalog=TestDB;integrated security=true;"))
//a command with custom SQL statement
using (var cm = new SqlCommand("SELECT @a+@b+@c", cn))
{
//add parameters value to the command
//the usage of the "@" is not mandatory
cm.Parameters.AddWithValue("a", 10);
cm.Parameters.AddWithValue("b", 10);
cm.Parameters.AddWithValue("@c", 10);
//open the connection always at the last time
cn.Open();
var result = (int)cm.ExecuteScalar();
}In this case, a direct binary value flows to the client from the database. The usage of an input parameter is actually straightforward.
Another specific feature of the Command class is the ability to return an XML value made in the database. Even if such a feature has bad performance for both XML encoding and decoding, sometimes it is useful. Moreover, if applied together with XML serialization from .NET, this could make a (very) basic O/RM tool. Here is an example:
//an XML serializer/deserializer for Invoice class
var serializer = new XmlSerializer(typeof(Invoice));
using (var cn = new SqlConnection("data source=(local);initial catalog=TestDB;integrated security=true;"))
//a command with XML result
using (var cm = new SqlCommand("SELECT [Number], [Date] from [Invoice] FOR XML AUTO", cn))
{
cn.Open();
//instead of returning a binary result-set this returns an XmlReader
using (var reader = cm.ExecuteXmlReader())
//read until an Invoice is available
while (!reader.EOF && reader.ReadToFollowing("Invoice"))
{
//the single row as xml string
string xml = reader.ReadOuterXml();
//another reader to read the single row
using (var subReader = reader.ReadSubtree())
{
//the deserializer can parse the xml row and retrieve needed values as defined by the decorator pattern (attributes) against the Invoice class
var invoice = serializer.Deserialize(subReader);
}
}
}The following example shows the Invoice class decorated to supply XmlSerializer needs:
[XmlRoot("Invoice")]
public class Invoice
{
[XmlAttribute]
public string Number { get; set; }
[XmlAttribute]
public DateTime Date { get; set; }
}When executing such an example in SQL Management Studio, the rows are returned as an XML instance, shown as follows:
<Invoice Number="001/2015/GR" Date="2015-01-01T00:00:00" />
Any other operation that does not need data retrieval will return the affected row count, as shown in the following example:
using (var cn = new SqlConnection("data source=(local);initial catalog=TestDB;integrated security=true;"))
//a command with a insert-select using parameters
using (var cm = new SqlCommand("INSERT INTO [Invoice](CustomerCustomerID,Number,Date) SELECT @customerid, @number, @date", cn))
{
//parameter definition and population
cm.Parameters.AddWithValue("customerid", 1);
cm.Parameters.AddWithValue("number", "2015/test/test");
cm.Parameters.AddWithValue("date", DateTime.Now.Date);
//open the connection
cn.Open();
//checks for the right insert result
if (cm.ExecuteNonQuery() != 1)
throw new ArgumentException("No insert made within database server. Check values");
}The other way of dealing with ADO.NET classes is by accessing the database in disconnected mode, in which we bring the data and schema locally in our application state, without having to maintain a connection open to the database server. The following code shows an example:
//a temporary datatable to contains
//read data and schema
using (var dt = new DataTable())
{
//a connection with specific connection-string
using (var cn = new SqlConnection("data source=(local);initial catalog=TestDB;integrated security=true;"))
//a command with custom SQL statement
using (var cm = new SqlCommand("SELECT [Number], [Date] from [Invoice]", cn))
//a data adapter to fill a datatable and
//handle eventual change tracking
using (var da = new SqlDataAdapter(cm))
//the connection this time is handled by the adapter
da.Fill(dt);
//thanks to the using keyword
//at this row anything related
//to the database connection and
//command execution is being
//disposed
//do some work on disconnected data
//the DataRow usage is similar to a DataReader
foreach (DataRow dr in dt.Rows)
Console.WriteLine("Number:{0} Date:{1:d}", dr[0], dr["Date"]);
}Although such usage is actually substituted by O/RM data access techniques, the ability to execute SQL code without the need to know the shape of returned data during design is still a killer feature of such legacy (2001) data-access framework. An example is reading a pivot table from a database view or a stored procedure.