• When using the connected parts of ADO.NET, you explicitly connect to a database and use that as the underlying data store.
  • When working in the connected fashion, you use Connection, Command, and DataReader objects.
• In .NET Core, you must reference System.Data.SqlClient package to use ADO.NET classes.
• A SqlDataReader is a forward-only stream of rows.
  • You can’t go back while you’re reading; only forward.
  • You can access the columns of a resulting row both by index and by name.
• When executing a query, you can also batch multiple SQL statements together.
  • The SqlDataReader returned contains multiple result sets.
  • You can advance to the next result set by calling NextResult or NextResultAsync on your SqlDataReader object.
• You should always call the Close method when you have finished using the DataReader object.
  • If your Command contains output parameters or return values, they will not be available until the DataReader is closed.
  • Note that while a DataReader is open, the Connection is in use exclusively by that DataReader.
  • You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

using (var connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();

    var command1 = new SqlCommand("SELECT * FROM [People]", connection);
    using (var dataReader = await command1.ExecuteReaderAsync())
    {
				if (dataReader.HasRows)
        {
					while (await dataReader.ReadAsync())
	        {
							// Evaluate fields in the current row...
	            if (dataReader["TargetField"] == null)
	                Console.WriteLine(dataReader["TargetField"]);
							var field1 = dataReader.GetInt32(0);
							var field2 = dataReader.GetInt32(1);
	        }
				}
    }

    var command2 = new SqlCommand("UPDATE [People] SET FirstName='John'", connection);
    var numberOfUpdatedRows1 = await command2.ExecuteNonQueryAsync();

    var command3 = new SqlCommand("INSERT INTO [People] ([FirstName], [LastName]) VALUES (@firstName, @lastName)", connection);

    command3.Parameters.AddWithValue("@firstName", "John");
    command3.Parameters.AddWithValue("@lastName", "Doe");
    var numberOfUpdatedRows2 = await command3.ExecuteNonQueryAsync();
}

Retrieving Identity or Autonumber Values

Retrieving Binary Data

• By default, the DataReader loads incoming data as a row as soon as an entire row of data is available.
• Binary large objects (BLOBs) need different treatment, however, because they can contain gigabytes of data that cannot be contained in a single row.
• The Command.ExecuteReader method has an overload that will take a CommandBehavior argument to modify the default behavior of the DataReader.
• You can pass SequentialAccess to the ExecuteReader method so that instead of loading rows of data, it will load data sequentially as it is received.
  • Note that this behavior may depend on your data source.
• Template

Use GetSchemaTable To Get Schema Information

• While a DataReader is open, you can retrieve schema information about the current result set using the GetSchemaTable method.
• GetSchemaTable returns a DataTable object populated with rows and columns that contain the schema information for the current result set.
  • The DataTable contains one row for each column of the result set.
  • Each column of the schema table row maps to a property of the column returned in the result set.

using (var dataReader = await command.ExecuteReaderAsync())
{
	DataTable schemaTable = dataReader.GetSchemaTable();
	foreach (DataRow row in schemaTable.Rows)
		foreach (DataColumn column in schemaTable.Columns)
			Console.WriteLine(String.Format("{0} = {1}", column.ColumnName, row[column]));
}

Transactions

• A local transaction is a single-phase transaction and is handled by the database directly.
• A distributed transaction when it is coordinated by a transaction monitor and uses fail-safe mechanisms (such as two-phase commit) for transaction resolution.