[ADO.NET] Connected Classes

DB Access is all about the recent business applications. DB is regarded as a must component. EF (Entity Framework) and LINQ might be the choice of developers nowadays.

But the basics are the basics. Old ADO.NET classes are still used out there in legacy applications. Also learning old ADO.NET classes are really helpful to understand how applications interact with DB. When you work with EF, you are working with Model objects and the most of the DB interactions are encapsulated and hidden from you. It is a good thing in most cases, but I recommend you to learn at least how ADO.NET works directly with DB.  

 

1. Namespaces

Most ADO.NET classes are located in “System.Data” namespace and its sub-namespaces.

  • System.Data: for ADO.NET architecture classes such as DbSet, DbTable or DbRow
  • System.Data.Common: for generic classes such as DbConnection or DbCommand
  • System.Data.SqlClient: for SQL-Server specific classes such as SqlConnection or SqlCommand

 

2. Connected Classes

Connected classes, also known as provider classes, are responsible for movement of data between the data store and the disconnected classes. A valid “DbConnection object is required to use most of the primary provider classes.

To query information:

  • Create a Connection, Command, and a DataReader objects
  • Use the “DataReader” object to retrieve the data
  • Close the Connection

To add/delete/update information:

  • Create a Connection and Command objects
  • Execute the Command
  • Close the Connection

 

3. DbConnection

Before working with DB, you need to create a connection to the database. Usually, the connection string is stored in the “Web.config” or “App.config” file.

The “System.Data.Common.DbConnection” implements the “System.Data.IDbConnection” interface.

public interface IDbConnection
{
  string ConnectionString { get; set; }
  ConnectionState State { get; }
  void Open();
  void Close();
}

public abstract class DbConnection: Component, IDbConnection, IDisposable
{
}

public sealed class SqlConnection : DbConnection
{
}

 

4. DbCommand

The “System.Data.Common.DbCommand” class sends a SQL command to a data store.  You can also create parameters and pass them to the “DbCommand” object.

public interface IDbCommand
{
  string CommandText { get; set; }
  int CommandTimeout { get; set; }
  CommandType CommandType { get; set; } // Text (default), StoredProcedure, TableDirect
  IDataParameterCollection Parameters { get; }

  int ExecuteNonQuery();
  Object ExecuteScalar();
  IDataReader ExecuteReader();
  IDataReader ExecuteReader(CommandBehavior behavior);
}

public abstract class DbCommand : Component, IDbCommand, IDisposable
{
}

public sealed class SqlCommand : DbCommand
{
  public SqlParameterCollection Parameters { get; }

  public SqlDataReader ExecuteReader();
  public SqlDataReader ExecuteReader(CommandBehavior behavior);
}

SqlParameterCollection provides the “AddWithValue(string parameter, object value)” method to add a parameter to a collection.

 

5. DbDataReader

The “DbDataReader” provides a high-performance method of retrieving data from a data store by delivering a forward-only, read-only, server-side cursor (firehose cursor) .

public interface IDataRecord
{
  int FieldCount { get; }
  Object this[int i] { get; } // indexer
  Object this[string name] { get; } // indexer with a string key

  bool IsDBNull(int i);
  Object GetValue(int i);

  int GetInt32(int i);
  double GetDouble(int i);
  string GetString(int i);
  ...
}

public interface IDataReader : IDisposable, IDataRecord
{
  bool IsClosed { get; }

  bool Read(); // Advances the IDataReader to the next record
  bool NextResult(); // Advances the data reader to the next result (batch SQL statements)

  void Close();
}

public abstract class DbDataReader : IDataReader, IEnumerable
{
}

public class SqlDataReader : DbDataReader
{
}

In order to check the return value is null, you need to use “DBNull.Value“.

 

6. DbDataAdapter

The “DbDataAdapter” retrieves and updates data between  a DataTable and a data store.

  • can contains a single “SelectCommand” for read-only data
  • can contain a “SelectCommand“, “InsertCommand“, “UpdateCommand“, and “DeleteCommand” for fully updatable data.
public interface IDataAdapter
{
  int Fill(DataSet dataSet);
  DataTable[] FillSchema(DataSet dataSet, SchemaType schemaType);
}

public interface IDbDataAdapter : IDataAdapter
{
  IDbCommand DeleteCommand { get; set; }
  IDbCommand InsertCommand { get; set; }
  IDbCommand UpdateCommand { get; set; }
  IDbCommand SelectCommand { get; set; }
}

public abstract class DbDataAdapter : DataAdapter, IDbDataAdapter
{
}

public sealed class SqlDataAdapter : DbDataAdapter
{
}

Generally, DataAdapter classes are used with ADO.NET disconnected classes.

 

7. DbProviderFactory

The “DbProviderFactory” create provider-independent code, which might be necessary when the data store needs to be quickly changeable.

The “DbProviderFactories” obtains a list of the provider factories that are available on a computer.

public abstract class DbProviderFactory
{
  public virtual DbConnection CreateConnection();
  public virtual DbCommand CreateCommand();
  public virtual DbDataAdapter CreateDataAdapter();
  public virtual DbParameter CreateParameter();
}

public sealed class SqlClientFactory : DbProviderFactory
{
}

public static class DbProviderFactories
{
  public static DbProviderFactory GetFactory(DbConnection connection);
  public static DbProviderFactory GetFactory(DataRow providerRow);
  public static DbProviderFactory GetFactory(string providerInvariantName);

  public static DataTable GetFactoryClasses();
}

 

8. Guidelines

  • Use the “using” statement to ensure that the “Dispose()” method is called on the connection and command objects to avoid connection leaks.
  • How to work with BLOBs: Use the same techniques you use for smaller data types, unless the objects are too large to fit into the memory. When a BLOB is to large to fit into memory, you must use streaming techniques to move the data.

 

9. Example

The example is using the “AdventureWorks” sample database for SQL Server.

  • Web.config
<connectionStrings>
  <add name="AdventureWorks"
    connectionString="Data Source=localhostSQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True"
    providerName="System.Data.SqlClient" />
</connectionStrings>
  • Product.aspx
<div>
Product Category:
<asp:DropDownList ID="categoryDDL" runat="server" AutoPostBack="True"
 onselectedindexchanged="categoryDDL_SelectedIndexChanged">
</asp:DropDownList>
<br /><br />
Product Sub Categories:
<asp:BulletedList ID="subCategiryList" runat="server"
 EnableViewState="False">
</asp:BulletedList>
</div>
  • Product.aspx.cs
private string conStr =
  WebConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    FillCategories();
    PopulateSubCategories();
  }
}

protected void categoryDDL_SelectedIndexChanged(object sender, EventArgs e)
{
  PopulateSubCategories();
}

private void FillCategories()
{
  categoryDDL.Items.Clear();

  using (SqlConnection con = new SqlConnection(conStr))
  {
    string sqlCmd = "SELECT ProductCategoryID, Name FROM Production.ProductCategory";
    using (SqlCommand cmd = new SqlCommand(sqlCmd, con))
    {
      con.Open();

      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        ListItem item = new ListItem();
        item.Text = reader["Name"].ToString();
        item.Value = reader["ProductCategoryID"].ToString();
        categoryDDL.Items.Add(item);
      }
    }
  }
  categoryDDL.SelectedIndex = 0;
}

private void PopulateSubCategories()
{
  subCategiryList.Items.Clear();

  int categoryID = Int32.Parse(categoryDDL.SelectedValue);

  using (SqlConnection con = new SqlConnection(conStr))
  {
    string sqlCmd = "SELECT Name FROM Production.ProductSubCategory WHERE ProductCategoryID = @cid";
    using (SqlCommand cmd = new SqlCommand(sqlCmd, con))
    {
      cmd.Parameters.AddWithValue("@cid", categoryID);

      con.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        ListItem item = new ListItem();
        item.Text = reader["Name"].ToString();
        item.Value = reader["Name"].ToString();
        subCategiryList.Items.Add(item);
      }
    }
  }
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s