ADO.NET in a Nutshell

.ADO.NET in a Nutshell


ADO.NET in a Nutshell


Web page by Kevin Harris of Homer IL

Please contact Kevin Harris of Homer IL concerning this web site

"ADO.NET is the suite of data access technologies in the .NET framework that developers use to build applications and services accessing relational data and XML."

This page is a review of the book, with additional notes, as I read through the chapters. I also used ADO.NET Programming by Arlen Feldman as a reference. Additionally I used the following MSD pages:

The main focus of this page is ADO.NET - SQL, although there are some reference to Entity Framework (EF). For more detailed information see the Entity Framework and Programming Entity Framework articles. ADO.NET - SQL has existed for over 15 years and is firmly established as a widely used data access technology that is fast, efficient, and highly stable. ADO.NET - SQL will be used in existing and new system for years to come, even though Microsoft is putting all of it innovation into Entity Framework.

Chapter 1: Introduction

Like any good book. chapter 1 contains the key concepts and terminology to lay the foundation for understanding the content in subsequent chapters. This is particularly important for ADO.NET since it is substantially different than its predecessor ADO. Two of these important concepts are Disconnected Data and Data Providers.

ADO.NET supports both the traditional Connected Model, where applications are continually connected to a database, and the newer Disconnected Model, which uses in-memory representations of the database tables. ADO.NET's Disconnected Data model is an In-Memory Data Base (IMDB) which obtains the desired data from a database and stores it in a DataSet structure in memory which can contain the same table relations and data constraints as the database. The database connection is then closed and the application works with the data in memory. Working with the data in memory is an order of magnitude faster than accessing the data from disk via a DBMS. Closing the database connection sooner reduces the contention and allows for greater scalabilty. For more information on the Connected and Disconnected Data models, see the IMDB article.

Chapter 2 goes into more details about ADO.NET data providers, but the general idea is that ADO.NET provides interfaces that database vendors use to create the classes for accessing their particular database. For example, the ADO.NET interfaces contain IDbConnection which requires an Open and Close method and a Connection String property. ADO.NET contains the SqlConnection class that provides direct access to Microsoft's SQL Server database. But Oracle had to create the OracleConnection class which implements the IDBConnection, to provide direct access to an Oracle database. ADO.NET also provided the OdbcConnection and OleDbConnection classes to provide indirect access to third-party database. More on Connection classes in chapter 3.

By having database vendors create the classes , which implement the ADO.NET interfaces, Microsoft created a way to interact with various databases in using a common interface. So migrating from one database vendor to another would have less impact on the application code. Aside: I have to wonder about the need for creating a Repository Pattern with ADO.NET. The common interfaces for ADO.NET lessens the effort required to switch from one database vendor to another, so is it worth the additional complexity and overhead of a Repository pattern for an infrequent occasion of changing database vendors?

Chapter 2: .NET Data Providers

ADO.NET uses a Data Provider model which supports a common interface for interacting with data stores. ADO.NET includes the classes for direct access to Microsoft SQL Server (System.Data.SqlClient) ,Oracle (System.Data.OracleClient) and indirect access for ODBC (System.Data.Odbc) and OLE DB(System.Data.OleDb) for other database systems. The direct access to other database systems is provided by third-party providers which adhere to the common interface classes. Each database vendor is associated with a particular implementation of the IDbProvider interface. For example ADO.NET contains the SqlConnection class for opening a connection to a SQL Server database, while MySQL provides the corresponding MySqlConnection class for connecting to a MySQL database.

ADO.NET Data Providers web page. For MySQL ADO.NET support see the Download Connector/Net for installing support through Windows, or see the Entity Framework with MySQL in Visual Studio 2015 article for installing support through the nuget package manager.

.ADO.NET Data Providers


ADO.NET Data Providers

DbProviderFactory base was introduced which contains a set of methods for creating instances of a custom data access classes. With DbProviderFactory, changing from one database vendor to another can be accomplished by changing the DbProviderFactory configuration, with minimal changes to the code.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>

  <system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>

Web.config Entity Framework and DbProviderFactory Configuration

Custom ADO.NET provider classes can be implemented with the following ADO.NET interfaces:

  • IDbConnection - represents an open connection to a data source.
  • IDbTransaction - represents a transaction to be performed at a data source.
  • IDbCommand - represents an SQL statement that is executed while connected to a data source.
  • IDataParameter - represent a parameter to a Command object, and optionally, its mapping to DataSet columns.
  • IDataParameterCollection - collects all parameters relevant to a Command object and their mappings to DataSet columns.
  • IDataReader - provides a means of reading one or more forward-only streams of result sets.
  • IDataAdapter - represents a set of methods and mapping action-related properties that are used to fill and update a DataSet and update a data source.
  • IDbDataAdapter - represents a set of command-related properties that are used to fill the DataSet and update a data source.

More information about these interfaces is provided in the following chapters.

Chapter 3: Connections

You must first open a connection to an ADO.NET data source before any task can be performed with the data source. Opening a connection is performed with the Connection object which comes in versions specific for the data provider. Each ADO.NET Connection object contains low-level functionality for:

  • User Authentication
  • Network Access
  • Connection Pooling

Every Connection object that accesses relational databases implements the IDbConnection interface which the contains the following properties:

  • ConnectionString - contains a series of name/value pairs delimited by a semicolon. The pairs are case insensitive and the order is not significant. The pairs specify information needed to connect to the data source, such as the location of the database server, the name of the database, and authentication information. Connection strings are data source specific, although they tend to have similarities. Note: The ConnectionString is the only writable property on the Connection object. In order for connections to be considered compatible in the connection pool, their parameters must match exactly. This is another good reason to define your connection strings in a configuration file, instead of in code.
  • ConnectionTimeout - the time in seconds to wait for a connection to open before failing. The default is 15 seconds. 0 will wait indefinitely.
  • Database - the name of the database to use. Initially set in the connection string but can be changed with the ChangeDatabase() method.
  • State - Bitwise collection of values defining the database state. Currently only Open and Closed are supported.

The IDbConnection methods include:

  • BeginTransaction() - starts a database transaction. Transactions are data provider specific.
  • ChangeDatabase() - sets a database to be used for subsequent operations.
  • CreateCommand() - returns a provider specific IDbCommand object.
  • Open() - attempts to open a connection to a data source.
  • Close() - attempts to close a connection to a data source.
<add name="XXXXX"
connectionString="server=www.XXXXX.com; database=XXXXX; User Id=XXXXX; password=XXXXX;
Persist Security Info=True; Convert Zero Datetime=true; Allow Zero Datetime=true" 
providerName="MySql.Data.MySqlClient" />

ADO.NET Connection String

<add name="XXXXXEntities" 
connectionString="metadata=res://*/XXXXXModels.csdl|res://*/XXXXXModels.ssdl|res://*/NyseslatModels.msl; provider=MySql.Data.MySqlClient; provider connection string=&quot;server=www.XXXXX.com;user id=XXXXX; password=XXXXX; persistsecurityinfo=True;database=XXXXX&quot;" providerName="System.Data.EntityClient" />

Entity Framework Connection String

The connection string typically supports the Persist Security Info parameter which has a default value of False. When the value is False, the security-sensitive information (e.g. password) is removed from the ConnectionString property as soon as the connection is opened. This prevents the security-sensitive information from being retrieved from within the code. Also, be careful when dynamically creating a connection string dynamically. Never use untrusted data (e.g. user input or data from a database) to be used to form a connection string. If data needs to be stored to be used for dynamic creation of a connection string, consider storing the data in a configuration file.

The using statement can be used with anything that supports IDisposable, which includes the Connection object. The using statement ensures the Dispose() method will be called on the Connection when the code inside the block ends, even when an unhandled exception occurs. However, unlike the Close() method, Dispose() also removed the connection from the connection pool, making the using method unsuitable for large applications because it removed the benefit of connection pooling.

Connection pooling recycles a set of open connections. Opening and closing data source connections is resource intensive and time consuming. Connection pooling is a data provider specific feature. Connection pooling save time by looking for an already open connection, which is compatible, in the connection pool when on Open() method is called. When the Close() method is called, the open connection is returned to the data pool. When the Dispose() method is called, the open connection is closed and the connection is NOT returned to the data pool.

When deciding which open connections in the connection pool are compatible to fill a request, an algorithm looks at the connection string. The string must match exactly in order to be considered compatible. Some vendors compatiblity checks are case sensitive. So the best approach to ensure the connections strings are compatible is to define them in a configuration file, instead of coding them in individual programs.

Chapter 4: Commands

The Command object governs every interaction between a client and a data source. The Command object wraps an SQL statement or stored procedure call. The Command class is specific to the data provider, but must implement the System.Data.IDbCommand interface, which means it is required contain the following members:

IDbCommand Properties:

  • CommandText - contains the SQL, stored procedure or table name. CommandText must be formatted as a string.
  • CommandTimeout - the number of seconds to wait for a command before it fails. Default is 30 seconds.
  • CommandType - indicates how the CommandText property is formatted. Values: Text, StoredProcedure, TableDirect.
  • Connection - references the IDbConnection object to use for the command. The connection must be open.
  • Parameters - a collection of input, output, or bidirectional parameter objects. Used for parameterized queries or stored procedure calls.
  • Transaction - gets or sets the transaction this command is a part of.
  • UpdatedRowSource - specifies how the command updated a data source when used with a DataSet.

IDbCommand Methods:

  • Cancel() - attempts to stop running a command. Method must be invoked on a separate thread.
  • CreateParameter() - creates a new Parameter object, which can be added to the Command.Parameters collection.
  • ExecuteReader() - executes the command and returns a forward-only read-only cursor in the form of a DataReader.
  • ExecuteNonQuery() - executes the command and returns the number of rows affected. Often used with Update, Delete, or Insert statements.
  • ExecuteScalar - executes the command and retrieves a single value.
  • Prepare - when CommandType is StoredProcedure, this method will precompile the command in the data source.

The three ways to execute a command are: ExecuteNonQuery(), ExecuteReader(), and ExecuteScalar(). Data Definition Language (DDL) commands can be executed with the ExecuteNonQuery() method, such as when creating a Table.

Each command can contain a collection of parameters. Parameters should be used when untrusted data (e.g. user input) is used to form part of the command text. When untrusted data is simply concatenated a part of the command text, it enables the possibility of SQL Injection attacks. Metacharacters within parameters are escaped automatically, thereby reducing the risk of some types of SQL injection attacks. Parameters can also prevent syntax errors associated with different data types, which must be escaped correctly when concatenating together a string. While more secure and less error-prone, a parameterized command does not improve performance.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
string returnValue;

string querySql = "Select field1 from table1 where keyField='Kevin';";

using (MySqlConnection conn = new MySqlConnection(connString))
{
    conn.Open();
    using (MySqlCommand cmdSql = new MySqlCommand(querySql, conn))
    {
        cmdSql.CommandText = querySql;
        cmdSql.Parameters.Clear();
        object result = cmdSql.ExecuteScalar();
        if ((result != null))
        {
            returnValue = result.ToString();
        }
    }
    conn.Close();
}

Get a Scalar Value

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
public static void GetValues(ref Dictionary<string, string> sqlColumns,
                                string sqlTable,
                                string sqlWhereClause,
                                string connectionString)
{
    // Create Select Clause
    string selectClause = null;
    int kvpCount = 1;
    foreach (KeyValuePair<string, string> kvpColumn in sqlColumns)
    {
        if (kvpCount == sqlColumns.Count)
        {
            selectClause += kvpColumn.Key;
        }
        else
        {
            selectClause += kvpColumn.Key + ", ";
        }
        kvpCount++;
    }

    // Create Select Statement
    string querySql = string.Format("Select {0} from {1} where {2};", selectClause, sqlTable, sqlWhereClause);

    // Perform SQL Query putting results in the Dictionary
    using (MySqlConnection conn = new MySqlConnection(connectionString))
    {
        conn.Open();
        using (MySqlCommand cmdSql = new MySqlCommand(querySql, conn))
        {
            cmdSql.CommandText = querySql;
            MySqlDataReader sqlDataReader = cmdSql.ExecuteReader();

            List<string> theKeys = new List<string>();
            foreach (KeyValuePair<string, string> kvpColumn in sqlColumns)
            {
                theKeys.Add(kvpColumn.Key);
            }

            kvpCount = 0;
            foreach (string theKey in theKeys)
            {
                sqlDataReader.Read();
                sqlColumns[theKey] = sqlDataReader.GetString(kvpCount);
                kvpCount++;
            }
        }
        conn.Close();
    }
}

Method to Get Multiple Values from Database

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
string strSQL = "insert into table1 (field1, field2, field3) " +
                "values(?parm1, ?parm2, ?parm2);";

int sqlReturn = 0;
using (MySqlConnection conn = new MySqlConnection(conString))
{
    conn.Open();
    using (MySqlCommand cmdSQL = new MySqlCommand(strSQL, conn))
    {
        cmdSQL.CommandText = strSQL;
        cmdSQL.Parameters.Add(new MySqlParameter("?parm1", DateTime.Now));
        cmdSQL.Parameters.Add(new MySqlParameter("?parm2", "ZIP"));
        cmdSQL.Parameters.Add(new MySqlParameter("?parm3", 'Z' + Guid.NewGuid().ToString("N").Substring(0, 9)));
        sqlReturn = cmdSQL.ExecuteNonQuery();
    }

    if (sqlReturn != 1)
    {
        ErrorRoutine(string.Format("Invalid. SQL Return: {0} ", sqlReturn));
    }
    else
    {
        string strUpdateSQL2 = null;
        strUpdateSQL2 = "update table1 set field3 = LAST_INSERT_ID() where autoKeyField = LAST_INSERT_ID()";

        int sqlReturn2 = 0;
        using (MySqlCommand cmdSQL = new MySqlCommand(strUpdateSQL2, conn))
        {
            cmdSQL.CommandText = strUpdateSQL2;
            sqlReturn2 = cmdSQL.ExecuteNonQuery();
        }
        conn.Close();
    }
}

Insert into table1 then update table1 field with autogenerated key value



Chapter 5: DataReaders

A DataReader is a wrapper over a cursor that retrieves query results in a read-only, forward-only stream of information. The DataReader is a performance-optimal way to retrieve data as it consumes few server resources and requires relatively little locking. The DataReader is data provider specific implementing the IDataReader interface which contains the following methods.

  • Close() - Closes the DataReader but not the underlying connection.
  • GetSchemaTable() - Retrieves a DataTable object with information about the schema for the current result set.
  • NextResult() - Moves from one result set to another when executing a Command that returns multiple result sets.
  • Read() - Loads the next row into the DataReader. Returns True if there are more rows left to read.

A DataReader loads only a single row into memory at a time, keeping memory use to a minimum. A DataReader can not be directly created. Instead it must be generated the ExecuteReader method of a Command object. A DataReader does not need to be manually opened as it will be initialized as you execute the Command. The Command object used with a DataReader contains a Select SQL statement. The only way to retrieve data from multiple tables using a DataReader, is to include Joins in the Select statement.

When a column name is used to access data from a DataReader, a Hashtable collection is used behind the scenes to determine the appropriate ordinal value. A fairly modest performance increase can be obtained by using the ordinal values directly. It is possible to execute a query that returns multiple result sets. This can improve performance as it only needs to contact the database once. This is done by setting up a batch query containing multiple Select statements separated by a semicolon.

Below is a project which compares the load time of a GridView using a DataReader vs using a DataSet.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="cathy_db" connectionString="server=xxxxxx; database=xxxxxxxxx; uid=xxxxx; pwd=xxxxx" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5.2" />
    <httpRuntime targetFramework="4.5.2" />
  </system.web>
<system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-1.5.2.14234" newVersion="1.5.2.14234" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>


Web.config

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DRvsDS._Default" %>

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
    <%--First GridView--%>
    <asp:GridView ID="gvGroceries" runat="server" CellPadding="4"
        GridLines="Vertical" AutoGenerateColumns="False" Width="100%" Height="250px"
        ForeColor="Black" Style="border: 1px solid black">
        <Columns>
            <asp:BoundField DataField="Quantity" HeaderStyle-BorderColor="Black" HeaderStyle-ForeColor="White" HeaderText="Quantity"
                SortExpression="Quantity"></asp:BoundField>
            <asp:BoundField DataField="Item" HeaderText="Item" HeaderStyle-BorderColor="Black" HeaderStyle-ForeColor="White" SortExpression="Item"
                ItemStyle-Wrap="False" />
            <asp:BoundField DataField="Brand" HeaderText="Brand" HeaderStyle-BorderColor="Black" HeaderStyle-ForeColor="White" SortExpression="Brand" />
        </Columns>
        <%-- Mandatory--%>
        <RowStyle CssClass="rowStyle" />
        <%-- Mandatory--%>
        <HeaderStyle CssClass="headerStyle" BackColor="#6B696B" Font-Bold="True" />
        <%-- Optional--%>
        <FooterStyle CssClass="footerStyle" />
        <AlternatingRowStyle BackColor="#EEE3EE" />
        <EmptyDataTemplate>
            No groceries on list.
        </EmptyDataTemplate>
    </asp:GridView>
    <asp:Label ID="lblTime" runat="server" Text=""></asp:Label>
    <br /><br />
    <%--Second GridView--%>
    <asp:GridView ID="gvGroceries2" runat="server" CellPadding="4"
        GridLines="Vertical" AutoGenerateColumns="False" Width="100%" Height="250px"
        ForeColor="Black" Style="border: 1px solid black">
        <Columns>
            <asp:BoundField DataField="Quantity" HeaderStyle-BorderColor="Black" HeaderStyle-ForeColor="White" HeaderText="Quantity"
                SortExpression="Quantity"></asp:BoundField>
            <asp:BoundField DataField="Item" HeaderText="Item" HeaderStyle-BorderColor="Black" HeaderStyle-ForeColor="White" SortExpression="Item"
                ItemStyle-Wrap="False" />
            <asp:BoundField DataField="Brand" HeaderText="Brand" HeaderStyle-BorderColor="Black" HeaderStyle-ForeColor="White" SortExpression="Brand" />
        </Columns>
        <%-- Mandatory--%>
        <RowStyle CssClass="rowStyle" />
        <%-- Mandatory--%>
        <HeaderStyle CssClass="headerStyle" BackColor="#6B696B" Font-Bold="True" />
        <%-- Optional--%>
        <FooterStyle CssClass="footerStyle" />
        <AlternatingRowStyle BackColor="#EEE3EE" />
        <EmptyDataTemplate>
            No groceries on list.
        </EmptyDataTemplate>
    </asp:GridView>
    <asp:Label ID="lblTime2" runat="server" Text=""></asp:Label>
</asp:Content>


Default.aspx

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
using System;
using System.Data;
using System.Web.UI;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Diagnostics;

namespace DRvsDS
{
    public partial class _Default : Page
    {
        private static string conString = ConfigurationManager.ConnectionStrings["cathy_db"].ConnectionString;

        protected void Page_Load(object sender, EventArgs e)
        {
            long elapsedMs;
            long elapsedMs2;
            string sql = "SELECT Quantity, Item, Brand FROM grocery;";

            // Bind GridView with DataSet
            DataSet ds = new DataSet();
            using (MySqlConnection conn = new MySqlConnection(conString))
            {
                using (MySqlDataAdapter da = new MySqlDataAdapter(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        var watch = Stopwatch.StartNew();
                        da.Fill(ds, "grocery");
                        gvGroceries.DataSource = ds.Tables["grocery"];
                        gvGroceries.DataBind();
                        elapsedMs = watch.ElapsedMilliseconds;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            // Bind GridView with DataReader
            using (MySqlConnection conn = new MySqlConnection(conString))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        var watch = Stopwatch.StartNew();
                        MySqlDataReader r = cmd.ExecuteReader();
                        gvGroceries2.DataSource = r;
                        gvGroceries2.DataBind();
                        elapsedMs2 = watch.ElapsedMilliseconds;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            lblTime.Text = "Load time is: " + elapsedMs.ToString() + " ms";
            lblTime2.Text = "Load time is: " + elapsedMs2.ToString() + " ms";
        }
    }
}


Default.aspx.cs

.DataSet vs DataReader Bind to GridView


DataSet vs DataReader Bind to GridView

Chapter 6: DataSets

A DataSet approximates a relational database in memory. A DataSet includes tables, relationships between tables, and unique and foreign key constraints. DataSets can be either typed or untyped. Typed datasets provide additional properties, methods, and events based on the DataSet schema. Strongly typed datasets allow for intelli-sense and compile time type checking. DataSets can be populated from DataAdapters or from XML sources. The DataSet can be easily serialized for marshalling between processes or for persistent storage. DataSets contain four characteristics:

  • Is not provider-specific. - A DataSet is universally structured regardless of the data source.
  • Is always disconnected. - Once populated, a DataSet does not maintain a connection the data source.
  • Can track changes made to its data. - Changes to the data in the DataSet can be updated back to the data source.
  • Can contain multiple tables. - A DataSet approximates a relational database in memory, with tables, columns, and rows.

The DataTableCollection object stores tables within a DataSet. The tables are accessed through the Tables property. Methods and properties for working with DataSet data include:

  • Add() - Add a table to the DataSet.
  • AddRange() - Allows multiple tables to be added to the DataSet using one statement.
  • Fill() - Create table and fill with data.
  • FillSchema() - Create table and fill with schema.
  • Count - Property returns the number of tables in the DataSet.
  • Contains() - Determines if a table with a specified name exists within a DataSet.
  • IndexOf() - Returns the index of the table within the DataSet table collection.
  • Remove() - Removes a table specified by a table name or reference.
  • RemoveAt() - Removes a table specified by an index value.
  • Clear() - Removes all tables from the DataSet.
  • Clone() - Creates a new DataSet with the same structure, including the table schema and relations, as the original but with none of the data.
  • Copy() - Creates a new DataSet with the same structure, including the table schema and relations, including all the data from the original DataSet.
  • Merge() - Combines the data and structure of two DataSets. The MissingSchemaAction property determines how schema mismatches are handled.
  • Reset() - Returns the DataSet to its original state. Can be used to discard the existing DataSet and start working with a new DataSet rather than instantiating a new DataSet object.
  • AcceptChanges() - Commits all pending changes back to the datasource. RowState values are set back to unchanged. Deleted rows are removed. Implicitly called on a row when the DataAdapter updates the row back to the data source when the Update() method is called.
  • RejectChanges() - Cancels any pending changes within the DataSet. Added rows are removed. Modified and deleted rows are returned to their original state.
  • HasChanges() - Indicates if the DataSet has any added, deleted, or modified rows.
  • GetChanges() - Creates a copy of the DataSet containing all the changes that have been made since it was last loaded or since AcceptChanges() was called. Takes an optional DataRowState argument which specifies the type of changes to be included.

DataSets can contains relationships (parent/child) between its tables. DataRelation objects store the table relations within a DataRelationCollection object. The DataRelationCollection object has Add(), Remove(), and Contains() methods. The relations are accessed using the Relations property of the DataSet. Chapter 11 discusses data relations in details.

DataSets contain a PropertyCollection that allows for custom information to be added to a DataSet. An example is adding a custom property which indicates the DataSet should be reset at specified time intervals. DataTable, DataColumn, DataRelation, and Constraint objects also have a similar collection of custom properties.

Below is an example of creating, populating, and accessing data within a DataSet:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
            DataSet ds = new DataSet();
            string sql = string.Format("SELECT * FROM table1 WHERE keyfield = '{0}'", myString);

            using (SqlConnection conn = new SqlConnection(conString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        da.Fill(ds, "MyTable");
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            DataRow dr = ds.Tables["MyTable"].Rows[0];
            string value1 = dr["field1"].ToString();
            string value2 = dr["field2"].ToString();

Use DataSet/DataRow to Hold Data



Chapter 7: DataTables

A DataTable is a disconnected version of a database table containing DataColumn and Constraint objects. Data is stored as a collection of DataRow objects within the DataTable. When accessing DataTable objects, they are conditionally case sensitive. When a DataTable is filled using a DataAdapter with a query, the DataTable columns use the column names from the query and framework determines the appropriate .NET datatype for each column (so it correctly corresponds with the database datatype). Alternatively the DataColumns names and data types can be specified programatically when the DataTable is created.

DataTable objects can be assigned UniqueConstraint objects which define a column, or group of columns, for which the value(s) must be unique for every row in the database. DataTable objects can also be assigned ForeignKeyConstraint objects which are similar to database foreign keys. Constraints are stored in a ConstraintCollection object and are accessed through the Constraints property of the DataTable.

DataTables have a PrimaryKey property which is a column, or collection of columns, which uniquely identify each row in the DataTable. The PrimaryKey acts as a unique constraint and is used by the Find() method to retrieve rows from the DataTable. The Select() method can be used to retrieve a subset of DataRows from within the DataTable. The Compute() method can be used to calculate aggregate values for a DataColumn. Compute() contains the ability to specify filter criteria to select specific DataRows.

ADO.NET tracks the changes on each DataRow within a DataTable. The RowState is set to Added, Modified, or Deleted corresponding to the action taken on the DataRow. The DataTable contains three methods: GetChanges(), AcceptChanges(), and RejectChanges() which will retrieve, commit, or discard the changes made to a DataTable since it was loaded or since the last AcceptChanges() was called. These methods function similarly to the DataSet methods of the same name, except when they are used on the DataSet they are applied to all the DataTables within the DataSet.

ADO.NET also tracks errors on each DataRow. Errors may be constraint violations are failed update attempts. The HasErrors() method can be called to determine if a DataTable contains errors. Then the GetErrors() method returns a collection of DataRows in an error state. This collection can be iterated through to process the error rows. The processing may include calling the ClearErrors() method on each row to reset the error state.

DataTables contain events which are triggered by actions against a DataColumn or DataRow. The ColumnChanging and ColumnChanged events can be used to validate data or control user interface elements. The RowChanging, RowChanged, RowDeleting, and RowDeleted events respond to actions performed on DataRows.

Methods and properties for working with DataTables include:

  • Clone() - Creates a new DataTable with the same structure, including the table schema and relations, as the original but with none of the data.
  • Column - Property which allows access to the DataColumnCollection of DataColumn objects.
  • Copy() - Creates a new DataTable with the same structure, including the table schema and relations, including all the data from the original DataTable.
  • Fill() - Create table and fill with data.
  • FillSchema() - Create table and fill with schema.
  • GetErrors() - Returns an array of DataRows which contain errors.
  • PrimaryKey - Property referencing one or more DataColumn objects which make up the primary key for the DataTable. The primary key acts as a unique data constraint and is used to locate records with the Find() DataRow method.
  • Reset() - Reset the state of the DataTable to be equivalent to a newly instantiated DataTable.
  • Row - Property which allows access to the DataRowCollection of DataRow objects.

DataTables consist of DataColumn and DataRow objects. DataColumns, and constraints on those columns define the schema for the DataTable. DataRows store the data within the DataTable. DataRow properties and methods allow you to add, remove, find, and examine data stored in a DataTable. More on DataColumns and DataRows in the next two chapters.



Chapter 8: DataColumns

The schema for a DataTable is defined by the DataColumns in the DataTable and the constraints on those DataColumns. The DataColumn defines the type of data which can be stored a particular column in a DataTable and the data type is a .NET datatype which is matched to the data type of the data source. The DataColumn also defines:

  • The length of the column for text-based data types.
  • If the data in the column can be modified.
  • If the column value must be unique for every row in the table.
  • If the column can contain null values.
  • If the column values are automatically generated and the rules for generating the values.
  • If the column value is calculated based on an expression.

The DataColumn has the following methods and properties:

  • AllowDBNull - specifies if columns allows null values. Default is True. DBNull class must be used to set a column to null. The null keyword causes an exception. DBNull represents no value in a database column. The null keyword represents an object which has not been instantiated.
  • AutoIncrement - specifies if the field will automatically increment its value (T/F).
  • AutoIncrementSeed - sets the starting value for the autoincrement.
  • AutoIncrementStep - specifies the value to be added during each increment.
  • Caption - sets a column title used by certain bound controls.
  • ColumnName - sets the name for the column.
  • DataType - specifies the .NET data type for the column values.
  • DefaultValue - sets a default value for the column.
  • ReadOnly - the column value can not be changed after the row has been added to the table (T/F).
  • Unique - creates a unique constraint. The column value must be unique for each row in the table (T/F).

AutoIncrementing Columns

The DataColumnAutoIncrement property can ensure that values are unique when new rows are added. These are frequently used for primary key values. The autoincrement value is not specified during the database Insert. ADO.NET determines the value based which can be configured with the starting point (AutoIncrementSeed) and increment value (AutoIncrementStep).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
private void AddAutoIncrementColumn()
{
    DataColumn column = new DataColumn();
    column.DataType = System.Type.GetType("System.Int32");
    column.AutoIncrement = true;
    column.AutoIncrementSeed = 1000;
    column.AutoIncrementStep = 10;

    // Add the column to a new DataTable.
    DataTable table = new DataTable("table");
    table.Columns.Add(column);
}

DataColumn AutoIncrement

Expression Columns

The DataColumnExpression property set the expression used to filter rows, calculate the values in a column, or create an aggregate column. Setting the Expression property to anything other than an empty string set the ReadOnly property to true. Invalid expressions raise an EvaluateException.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
private void CalcColumns()
{
    DataTable table = new DataTable ();

    // Create the first column.
    DataColumn priceColumn = new DataColumn();
    priceColumn.DataType = System.Type.GetType("System.Decimal");
    priceColumn.ColumnName = "price";
    priceColumn.DefaultValue = 50;

    // Create the second, calculated, column.
    DataColumn taxColumn = new DataColumn();
    taxColumn.DataType = System.Type.GetType("System.Decimal");
    taxColumn.ColumnName = "tax";
    taxColumn.Expression = "price * 0.0862";

    // Create third column.
    DataColumn totalColumn = new DataColumn();
    totalColumn.DataType = System.Type.GetType("System.Decimal");
    totalColumn.ColumnName = "total";
    totalColumn.Expression = "price + tax";

    // Add columns to DataTable.
    table.Columns.Add(priceColumn);
    table.Columns.Add(taxColumn);
    table.Columns.Add(totalColumn);

    DataRow row = table.NewRow();
    table.Rows.Add(row);
    DataView view = new DataView(table);
    dataGrid1.DataSource = view;
}

Expression Columns

Null Values

DBNull is a singleton class which represents a nonexistent value in a DataColumn or database column. A DBNull value brings with it the concepts of a null value in a database, such as equality does not exist with null values (i.e. null = null returns null AND null null also returns null) and requires the use of special operators (i.e. IsNull and IsNotNull). Using comparison operators on database fields which contains nulls is a common cause of getting unexpected results for uninitiated DB programmers.

A null value in a database is often confused the concept of a null in object-oriented programming where a null means the absence to an objected reference. For example you get a Null Value Exception when you try to reference an object which has not been instantiated. In .NET you will get a run-time exception if you assign a Null value to a table column. Some programmers perform a quick fix to this exception by using the ToString() function, but this converts the DBNull values to empty strings, which may not be the desired value when the row is updated in the database.



Chapter 9: DataRows

As with the DataColumn, the DataRow is a primary component of a DataTable. DataRow objects, with their properties and methods, can insert, retrieve, update, and delete values in a DataTable. The DataColumn defines the schema (data type, name, etc) for a column and the DataRow object manipulates the data within. Data can be accessed by specifying either the column name or the column's ordinal position within the row.

You can buffer updates to a row by calling the BeginEdit() method which turns off all constraints. When the EndEdit() method is called the data is validated against all constraints and appropriate events are raised. You can also get or set all the values for a row by using an array with the ItemArray property.

Changes to DataRow values will only occur when the AcceptChanges() method is called explicitly or implicitly. Each DataRow has a RowState property which can be set to Added, Modified, or Deleted. ADO.NET tracks the original and current value in each row which allows the AcceptChanges() method to commit the data or the RejectChanges() method to discard the data changes. These methods function the same as the DataSet methods of the same name. When they are used by the DataSet, they are implicitly called on all DataTables within the DataSet, and on all DataRows within each DataTable.

The HasErrors property can be checked to determine if there are any rows which contain errors (such as a failed constraint error). The GetErrors() method can then be called to retrieve an array of DataRows which contain errors. The GetColumnError() and SetColumnError() methods can be used to set the error description for a particular column. The ClearErrors() method clears all error information for the row and for all columns in the row.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
using System;
using System.Data;

class Program
{
    static void Main()
    {
	DataTable table = CreateTable();
	object[] array = new object[5];
	array[0] = 3;
	array[1] = "Sherlock";
	array[2] = "Holmes";
	array[3] = 3.4;
	array[4] = DateTime.Now;
	table.Rows.Add(array);
    }


   static DataTable CreateTable()
   {
       DataTable table = new DataTable();
       table.Columns.Add("Size", typeof(int));
       table.Columns.Add("LastName", typeof(string));
       table.Columns.Add("FirstName", typeof(string));
       table.Columns.Add("Amount", typeof(double));
       table.Columns.Add("Date", typeof(DateTime));
       return table;
   }
}

Add DataRow to Table Using an Object Array



Error | ASP.NET Developer

Error

Error message

  • Warning: Cannot modify header information - headers already sent by (output started at /srv/disk9/1218369/www/kcshadow.net/aspnet/includes/common.inc:2748) in drupal_send_headers() (line 1232 of /srv/disk9/1218369/www/kcshadow.net/aspnet/includes/bootstrap.inc).
  • PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user '1218369_b2cf'@'185.176.40.58' for table 'watchdog': INSERT INTO {watchdog} (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array ( [:db_insert_placeholder_0] => 0 [:db_insert_placeholder_1] => cron [:db_insert_placeholder_2] => Attempting to re-run cron while it is already running. [:db_insert_placeholder_3] => a:0:{} [:db_insert_placeholder_4] => 4 [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => http://www.kcshadow.net/aspnet/?q=ADO.NETNutshell [:db_insert_placeholder_7] => [:db_insert_placeholder_8] => 54.198.41.76 [:db_insert_placeholder_9] => 1534839492 ) in dblog_watchdog() (line 160 of /srv/disk9/1218369/www/kcshadow.net/aspnet/modules/dblog/dblog.module).
The website encountered an unexpected error. Please try again later.