O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required