C#Data Operations Series - 3. ADO.NET Offline Query

0. Preface

In the previous article, I deliberately left an example of a query unseen.Although you can get a DataReader from the following code:

IDataReader reader = command.ExecuteReader();

Then read the data line by line through the reader, but I don't recommend it.

C#does a lot of work and offers more choices for querying this high frequency requirement.Here's another way to write a query.

1. Offline Query

C#provides another mechanism for querying by reading the results from the database into the network cache at once and loading them into the program when they are used.

The three most critical interfaces or classes in offline queries are:

  • IDataAdapter An adapter used to obtain data and populate or update DataSet s
  • DataSet represents the cache of data in memory
  • DataTable represents a table in memory

IDataAdapter provides data, DataSet represents the result set read by adapter, and one DataTable collection represents the result of an executed SQL query.As for why collections are, IDataAdapter allows multiple query statements to run.

Okay, let's take a quick look at the properties and methods of these three key points:


public int Fill (System.Data.DataSet dataSet);//Fill the DataSet with the results of the query

Inside C#, direct inheritance of the interface is not actually recommended, and inheritance of the DataAdapter class is recommended, which specifies that the Database Adapter must provide an accessible database connection and command text to execute when it is initialized.

Of course, part of its implementation class allows assignment of these two key elements in the form of attributes.


public DataSet ();
public DataSet (string dataSetName);//Specify the name of the dataset
public System.Data.DataTableCollection Tables { get; }//Gets the collection of tables contained in the DataSet

DataSet has many useful methods, but today we can just use relationships.

Tables introduces a type not mentioned, DataTableCollection.So let's just follow the cue and see what's critical inside:

public System.Data.DataTable this[int index] { get; }// Gets the DataTable with the specified subscript
public System.Data.DataTable this[string name] { get; }//Gets the DataTable with the specified name

You can see that there is an index access to the DataTable elements that we can get inside.

DataTable :

public System.Data.DataSet DataSet { get; }//Gets the DataSet to which this table belongs.
public System.Data.DataColumnCollection Columns { get; }//Gets the collection of columns that belong to the table
public System.Data.DataRowCollection Rows { get; }//Gets the collection of rows belonging to the table

Two new classes have emerged: DataColumnCollection and DataRowCollection.This is an implementation class of an internal collection that functions like List but is not equivalent to List.

Let's take a look at some of the properties and methods that are useful to us:


public virtual int Count { get; }//Gets the total number of elements in the collection
public System.Data.DataColumn this[int index] { get; }//Gets the DataColumn from the collection at the specified index location
public System.Data.DataColumn this[string name] { get; }//Gets a DataColumn from a collection with the specified name.


public override int Count { get; }
public System.Data.DataRow this[int index] { get; }// Get rows at index

Well, that's it.Turn the direction back to the previous intersection and come back.Let's see what DataColumn and DataRow are worth noting now:


public string ColumnName { get; set; }//Gets or sets the name of the column in the DataColumnCollection
public Type DataType { get; set; }//Gets or sets the type of data stored in a column


public object this[System.Data.DataColumn column] { get; set; }//Gets or sets the data stored in the specified DataColumn
public object this[int columnIndex] { get; set; }//Gets or sets the data stored in the column specified by the index
public object this[string columnName] { get; set; }//Gets or sets the data stored in the column specified by the name
public object[] ItemArray { get; set; }//Gets or sets all the values of this row from an array

So far, the supporting classes and interfaces for offline queries have been outlined.So let's see how to do an offline query

2. Take a look at the practice

Take the SQL Server database as an example:

To get a SqlDataAdapter, C#provides four ways to get it:

public SqlDataAdapter ();//Construct an Adapter object without connections and commands
public SqlDataAdapter (System.Data.SqlClient.SqlCommand selectCommand);// Specify a query command
public SqlDataAdapter (string selectCommandText, System.Data.SqlClient.SqlConnection selectConnection);//Specify query commands, and connections
public SqlDataAdapter (string selectCommandText, string selectConnectionString);//Specify query commands and connection strings

Reference namespace:

using System.Data;
using System.Data.SqlClient;

So let's first construct an Adapter:

var connectStr = "Data Source=.;Initial Catalog=Old;Integrated Security=True";
var sql = "select * from Area_PostCode";
var adapter = new SqlDataAdapter(sql, connectStr);

Then create a DataSet to hold the data and populate it:

DataSet set = new DataSet();

Then you can see that the data in this set should look like this:

The above figure is in debug mode in VS, you can see

From the figure above, we can probably guess what the data structure inside the DataTable is, or what C#makes us understand.

The DataColumn corresponds to the column in the diagram, and ColumnName is the column name shown in the diagram.DataRow is a row, ItemArray is a row.

This makes it obviously much easier to access data than using IDataReader directly.

According to the above example:

Let's try to get the Province column value in the third row, and if you find this statement odd, take a look at my writing to see why I said that.

var table = set.Tables[0];// Get the first table first
var value = table.Rows[2]["Province"];

This is an ant-moving way of reading data.C#provides an extension method for DataTable:

public static EnumerableRowCollection<DataRow> AsEnumerable(this DataTable source);

Converts a table to an enumerable DataRow collection.

So we can use a foreach loop to iterate through the DataTable.

3. Not Continued

This section provides a brief introduction to offline query support for ADO.NET.When we get a DataTable from a database, we can do more with it.In the next chapter, I'll take you through a simple ORM tool class that combines the reflection described earlier.

Please pay attention to more content My blog, Mr. Gao's Cabin

Tags: C# Database SQL network

Posted on Mon, 11 May 2020 19:47:50 -0700 by hc