Page 1 of 1

ADO.NET Overview

#1 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3821
  • View blog
  • Posts: 13,539
  • Joined: 12-December 12

Posted 04 August 2014 - 06:59 PM

ADO.NET is a very large subject and this tutorial can only provide an introduction and overview. I introduce ADO.NET itself, its Classes, and discuss connecting to a database, executing commands, reading rows, and transactions (briefly).

There are many subjects not covered, including persisting data (although, see last topic), defining DataTables, Columns, DataRelations, working with XML, etc.. The main MSDN link to start pursuing these subjects is here:

ADO.NET :MSDN

The Overview

ADO.NET allows you to interact with a database directly using objects of the managed provider class. Objects like the DataReader allow you to read rows from a table. ADO.NET also allows you to work in a disconnected manner. That is, you work with in-memory data and a connection to the database is only made when necessary to persist changes to the database. A DataAdapter can handle this connecting, and disconnecting, for you - see the last topic.

Namespaces System.Data System.Data.SqlClient System.Data.OleDb System.Data.Odbc

Objects of the generic data classes are used to store a local copy of information received from the database. The main generic data class is the System.Data.DataSet class. These classes are not specific to any particular database. The generic data classes represent information retrieved from the database as XML.

There are managed provider classes for SQL Server, OLE DB (connecting to databases that support OLE DB such as Access or Oracle) and ODBC (connects to most databases), as well as for Oracle, EntityClient and SQL Server Compact Edition.

Connection Classes: SqlConnection OleDbConnection OdbcConnection
Command Classes: SqlCommand OleDbCommand OdbcCommand

There are also Parameter and ParameterCollection classes to pass and store parameters for a Command object. CommandBuilder classes generate single-table INSERT, UPDATE and DELETE commands that synchronize changes made to a DataSet object, via a DataAdapter object.

The DataReader classes can read rows (in a forward-only direction) retrieved using a Command object. A DataReader object is typically faster than reading from a DataSet. DataAdapter classes move rows between a DataSet and a database through a Connection object.

Transaction classes represent a database transaction. Here is a basic outline for transactions:
using System.Transactions;      // ADD A REFERENCE FIRST


    string source = "YOUR CONNECTION STRING";
    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)) {
        using (SqlConnection conn = new SqlConnection(source)) {
            // do SQL stuff..
            // then mark as complete..
            scope.Complete();
        }
    }


If the transaction is not explicitly marked as Complete then the transaction will be rolled-back and no changes will be committed to the database.

Attached Image




The following console example uses a SQL Server database, establishes a Connection and Command, then uses a DataAdapter to Fill a DataSet. It then iterates the Rows of the DataSet's DataTable ("dbo.Staff"). Notice that the Connection is closed before the rows of the (in-memory) DataTable are read.
using System.Data;
using System.Data.SqlClient;

string cnnString = "Server=ANDREW-PC\\SQLEXPRESS;Database=Staff Database;Trusted_Connection=Yes";
SqlConnection cnn = new SqlConnection(cnnString);
string selectStr = "SELECT * FROM dbo.Staff";
SqlCommand myCmd = cnn.CreateCommand();
myCmd.CommandText = selectStr;
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = myCmd;
DataSet myDS = new DataSet();           // to store the results
cnn.Open();
Console.WriteLine("Retrieving rows from the dbo.Staff table.");
myAdapter.Fill(myDS,"dbo.Staff");
cnn.Close();
DataTable myDT = myDS.Tables["dbo.Staff"];
foreach (DataRow myDR in myDT.Rows)
    Console.WriteLine(myDR["LastName"] + ",\t" + myDR["FirstName"]);


This example explicitly opens and closes the connection. This is not necessary as the Fill() method of the DataAdapter does this implicitly. However, for multiple-operations, performance can be improved by explicitly opening, and then closing, the connection. (It is not necessary in this example.)

connectionstrings.com is an important resource.

The older ADO technology has a Recordset object that can hold a single table that can come from a query, table or stored procedure, or any other source of a single table of information. An ADO.NET DataSet can contain multiple tables. The DataTable objects within the DataSet can store relations to create complex database structures within your application.

What happened to the Recordset? :MSDN

The ADO Recordset object supports Move, MoveNext, etc., methods to move between records. ADO.NET uses collections within the DataSet. One advantage is that you can examine all records using a foreach structure. However, reading a single field item is more complex with ADO.NET, and you might need code like:
strSingleValue = myData.Tables[0].Rows[0].ItemArray.GetValue(1).ToString();


The following example uses SQL Server with the sample Northwind database. An SqlConnectionStringBuilder object is used to help build the connection string. It also uses an SqlParameter rather than embedding a variable within a SQL statement – which is a serious security risk and exposes an application to SQL injection attacks.
using System;
using System.Data;
using System.Data.SqlClient;

public class Sample {

    public static void Main(string[] args) {

        SqlConnection conn = new SqlConnection();
        try {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = ".\\SQLExpress";       // or "ANDREW-PC\\SQLExpress"
            builder.InitialCatalog = "northwind";
            builder.IntegratedSecurity = true;
            conn.ConnectionString = builder.ConnectionString;
            conn.Open();
            Console.Write("Enter a customer ID (5 characters): ");      //e.g. ALFKI
            string strCustID = Console.ReadLine();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT OrderID, OrderDate, ShippedDate, ShipName, " +
                "ShipAddress, ShipCity, ShipCountry " +
                "FROM Orders WHERE CustomerID = @CustomerIDParam";
            SqlParameter param = new SqlParameter("@CustomerIdParam", SqlDbType.Char, 5);
            param.Value = strCustID;
            cmd.Parameters.Add(param);
            Console.WriteLine("Finding orders for customer {0}\n\n", strCustID);
            SqlDataReader dReader = cmd.ExecuteReader();
            while (dReader.Read()) {
                int orderId = dReader.GetInt32(0);
                if (dReader.IsDBNull(2)) {          // OrderDate might be 'null'
                    Console.WriteLine("Order {0} not yet shipped\n\n", orderId);
                } else {
                    DateTime orderDate = dReader.GetDateTime(1);
                    string shipName = dReader.GetString(3);         // etc.
                    Console.WriteLine("{0}\nPlaced: {1}\nShip Name: {2}", orderId, orderDate, shipName);
                }
            }
            dReader.Close();
        } catch (SqlException e) {
            Console.WriteLine("Error accessing database: {0}", e.Message);
        } finally {
            conn.Close();
        }
        Console.ReadKey();
    }
}


Note that, without the IsDBNull check, the application would fail with an unhandled exception because some of the OrderDate's are null. This creates an SqlNullValueException, so it is not caught by the SqlException handler.

This example uses a DataReader object which is sometimes referred to as a firehose cursor, as it pours data out as quickly as possible. (Cursor is an acronym for "current set of rows".) It retrieves data one row at a time and does not retain any locks on a row after it has been retrieved.

An alternative to using finally to ensure that the connection is closed is to use a using statement:
using (SqlConnection conn = new SqlConnection()) {
    try { ... }
    catch (SqlException e) { ... }      // 'finally' not necessary
}       // or better yet,
using (SqlDataReader dReader = cmd.ExecuteReader(Commandbehavior.CloseConnection)) { ... }


Configuring Connection Strings

Saving, and reading, connection-strings from the configuration file is more flexible, making it possible to switch database providers with the help of the DbProviderFactory class. It is also an application-wide setting.

In the App.config file:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name="Northwind" providerName="System.Data.SqlClient" 
    connectionString="Data Source=.\SQLExpress;Initial Catalog=northwind;Integrated Security=true"/>
  </connectionStrings>
</configuration>


To use this connection-string first add a reference to System.Configuration, then you need using System.Configuration;. The following code revises the first part of the console application-code above:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;     // Add Reference..

public class Sample {

    public static void Main(string[] args) {

        SqlConnection conn = new SqlConnection();
        try {
            string sConn = ConfigurationManager.ConnectionStrings["Northwind"].ToString();
            conn.ConnectionString = sConn;
            conn.Open();
            // remaining code as above


CommandTypes and Executing Commands

There are three different CommandTypes, demonstrated here using the Northwind database:
    // Text (the default)
    string selecting = "SELECT ContactName FROM Customers";
    SqlCommand cmd = new SqlCommand(selecting, conn);

    // StoredProcedure
    SqlCommand cmd = new SqlCommand("CustOrderHist", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@CustomerID", "QUICK");

    // TableDirect - single table, all columns returned
    // (only valid with OleDb provider)
    OleDbCommand cmd = new OleDbCommand("Categories", conn);
    cmd.CommandType = CommandType.TableDirect;


Execute Methods:

  • ExecuteNonQuery() Does not return any output, so typically for INSERT, UPDATE and DELETE statements.
  • ExecuteReader() Returns a typed IDataReader, a forward-only resultset.
  • ExecuteScalar() Returns the value of the first row, and first column, of the resultset.
  • ExecuteXmlReader() Returns an XmlReader object.

Executing a Command :MSDN

ExecuteNonQuery() helpfully returns an integer, the number of rows affected by the command.
int rowsAffected = cmd.ExecuteNonQuery();


The following console example uses SQL Server Compact Edition (CE) to demonstrate some of the topics discussed above. It also demonstrates inserting and deleting records.

Note: This example works in a connected manner. That is, it explicitly opens and closes the connection, rather than the preferred disconnected approach: using a DataAdapter to Fill a DataSet and Update the database. It is important to be aware of both approaches.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlServerCe;      // Add Reference

namespace SimpleADO_Console {
    class Program {
        static void Main(string[] args) {
            try {
                string sConn = GetConnectionString();
                using (SqlCeConnection conn = new SqlCeConnection(sConn)) {
                    conn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Books", conn))
                    using (SqlCeDataReader reader = cmd.ExecuteReader()) {
                        while (reader.Read()) {
                            Console.WriteLine("{0}\t{1}\t{2}", reader.GetInt32(0),
                                reader.GetString(1), reader.GetInt32(2));

                        }
                    }
                }
            } catch (SqlCeException ex) {
                Console.Write(ex);
            }

            // inserting and deleting data
            try {
                string sConn = GetConnectionString();
                using (SqlCeConnection conn = new SqlCeConnection(sConn)) {
                    conn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(
                        // insert data
                        "INSERT INTO Books (Title, PublishYear) VALUES (@Title, @PublishYear)", conn)) {
                        cmd.Parameters.Add(new SqlCeParameter("@Title", "Test Book"));
                        cmd.Parameters.Add(new SqlCeParameter("@PublishYear", 2010));
                        int iRowsAffected = cmd.ExecuteNonQuery();
                        Console.WriteLine("{0} rows affected by insert.", iRowsAffected);

                        // delete data
                        cmd.CommandText = "DELETE FROM Books WHERE Title LIKE 'Test%'";

                        iRowsAffected = cmd.ExecuteNonQuery();
                        Console.WriteLine("{0} rows affected by delete.", iRowsAffected);
                    }
                }
            } catch (SqlCeException ex) {
                Console.Write(ex);
            }

            Console.ReadKey();
        }

        static string GetConnectionString() {
            string sConn = String.Empty;
            // using SQL Server Compact 4.0
            sConn = @"Data Source=C:\Users\Andrew\Documents\Books.sdf;Password='Whatever1';";
            return sConn;
        }
    }
}


A Word About Persistence

When working with the disconnected layer - using a DataAdapter to Fill a DataSet - then, eventually, there is a need to persist changes to the DataSet back to the database (or data-source). This, essentially, requires a call of the DataAdapter's Update() method, on the DataSet:
adapter.Update(dataSet);


This will open the connection to the database, commit the changes, and close the connection.

Usually there is a DataGridView or BindingSource involved. Each of these (and a DataRow) has an EndEdits() method and one of these is typically called before the Update() call. (The DataSet also has an AcceptChanges() method, which is sometimes involved in the process.)

For the Update() to work there must be valid, and appropriate, INSERT, UPDATE and DELETE statements associated with the DataAdapter, although these are typically created for us.

Read more about this important subject here:
Saving Data in Datasets :MSDN

ADO.NET :MSDN
The main MSDN link and will lead you to many other topics.

This post has been edited by andrewsw: 05 August 2014 - 07:12 PM


Is This A Good Question/Topic? 2
  • +

Replies To: ADO.NET Overview

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5676
  • View blog
  • Posts: 12,194
  • Joined: 02-June 10

Posted 05 August 2014 - 05:35 AM

Great stuff Andrew! I have to work through this one myself.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3821
  • View blog
  • Posts: 13,539
  • Joined: 12-December 12

Posted 05 August 2014 - 08:35 AM

Thank you ;)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1