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.
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.
18.188.211.106