SQL in a Nutshell, 2nd Edition

Retrieving Data Using ADO.NET

The following C# code fragment executes a SQL SELECT statement that returns author names from the authors table and then iterates through the results one row at a time, printing out each author’s name. The code is explained in the detailed steps that follow:

{Odbc|OleDb|Sql}Command statement = connection.CreateCommand( );
statement.CommandText = "SELECT au_fname, au_lname FROM authors";
{Odbc|OleDb|Sql}DataReader resultSet = statement.ExecuteReader( );
while( resultSet.Read( ) )
{
   String fname = "NULL";
   String lname = "NULL";
   if( !resultSet.IsDBNull( 0 ) ) fname = resultSet.GetString( 0 );
   if( !resultSet.IsDBNull( 1 ) ) lname = resultSet.GetString( 1 );
   System.Console.WriteLine( lname + ", " + fname );
}
resultSet.Close( );
statement.Close( );

Warning

Column ordinals are all zero-based (the first column is 0, the second is 1, etc.) in ADO.NET, which is different from the one-based ordinals used by JDBC.

To execute a SQL query and process the results using ADO.NET, take the following steps:

1. Create the Command object that will be used to execute the SELECT statement and attach a SELECT statement to it:

   {Odbc|OleDb|Sql}Command statement = connection.CreateCommand( );
   statement.CommandText = "SELECT au_fname, au_lname FROM AUTHORS";

2. Invoke the ExecuteReader method on the Command object, creating a new DataReader object.

   {Odbc|OleDb|Sql}DataReader resultSet = statement.ExecuteReader( );

3. Iterate through each row in the result set. After execution of the SELECT statement, ...