Connection
, Command
, and DataReader
objects.System.Data.SqlClient
package to use ADO.NET classes.SqlDataReader
is a forward-only stream of rows.
SqlDataReader
returned contains multiple result sets.NextResult
or NextResultAsync
on your SqlDataReader
object.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
Command.ExecuteReader
method has an overload that will take a CommandBehavior
argument to modify the default behavior of the DataReader
.**SequentialAccess**
to the ExecuteReader
method so that instead of loading rows of data, it will load data sequentially as it is received.
**GetSchemaTable**
To Get Schema InformationDataReader
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.
DataTable
contains one row for each column of 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]));
}