5.9 Examples

     

In the previous sections of this chapter, we examined the fundamental components of SQL statement processing found in most database programs, but didn't include any programs in their entirety. In the following section, we combine the necessary steps into a small program that executes a simple SELECT statement and prints out the results. The same example is provided for both of the APIs covered in this chapter.

The examples execute a SELECT statement and print out the results after establishing connectivity with the database. The SELECT statement is as follows :

 SELECT a.au_lname, a.au_fname, SUM(t.ytd_sales) FROM authors a, titleauthor, titles t  WHERE titleauthor.au_id = a.au_id and        titleauthor.title_id = t.title_id GROUP BY a.au_lname, a.au_fname ORDER BY 3 DESC 

The examples execute the statement and print out the three-column result set returned by the database server.

5.9.1 ADO.NET Example

The following C# ADO.NET database program connects to a database and prints a list of authors in the pubs database that includes their year-to-date sales. This program can be easily be adapted to meet other database processing needs by following the practices outlined in the earlier sections of this chapter.

 using System; using System.Data.SqlClient; class ExampleApplication {         static void Main(string[] args)         {         String connection_string =             "Server=(local);Trusted_Connection=true;DATABASE=pubs;";         String SQL =              "SELECT a.au_lname, a.au_fname, SUM(t.ytd_sales) " +             "FROM authors a, titleauthor, titles t " +             "WHERE titleauthor.au_id = a.au_id and " +             "      titleauthor.title_id = t.title_id " +             "GROUP BY a.au_lname, a.au_fname " +             "ORDER BY 3 DESC";         SqlConnection connection = null;         SqlCommand statement = null;         SqlDataReader resultSet = null;         try          {             // Create Connection and Connect to the Server             connection = new SqlConnection(connection_string);             connection.Open( );             // Create a Command object for the SQL statement             statement = connection.CreateCommand( );             statement.CommandText = SQL;             // Create a Reader for reading the result set             resultSet = statement.ExecuteReader( );             while( resultSet.Read( ) )             {                 // Extract the data from the server and display it                 String fname = "NULL";                 String lname = "NULL";                 String sales = "ZERO";                 if( !resultSet.IsDBNull( 0 ) )                     fname = resultSet.GetString( 0 );                 if( !resultSet.IsDBNull( 1 ) )                     lname = resultSet.GetString( 1 );                 if( !resultSet.IsDBNull( 2 ) )                     sales = resultSet.GetInt32( 2 ).ToString( );                 System.Console.WriteLine( lname + ", " +                                            fname + " has sales of " +                                           sales);             }         }          catch( SqlException e )          {             // Print out the error string, if any.             System.Console.WriteLine("Error:" + e.ToString( ) );         } finally {             // Free up resources             if( resultSet != null ) resultSet.Close( );             if( statement != null ) statement.Dispose( );             if( connection != null ) connection.Close( );         }         } } 

5.9.2 JDBC Example

The following Java JDBC database program connects to a database and prints out each of the authors in the pubs database, as well as their year-to-date sales.

 import java.sql.*; public class ExampleApplication {    public static void main(String[] args)    {       String connection_string =            "jdbc:microsoft:sqlserver://localhost:1433;" +           "User=montoyai;Password=12345;DatabaseName=pubs;";       String SQL =           "SELECT a.au_lname, a.au_fname, SUM(t.ytd_sales) " +          "FROM authors a, titleauthor, titles t " +          "WHERE titleauthor.au_id = a.au_id and " +          "      titleauthor.title_id = t.title_id " +          "GROUP BY a.au_lname, a.au_fname " +          "ORDER BY 3 DESC";       Connection connection = null;       Statement statement = null;       ResultSet resultSet = null;       try        {          Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");          // Create Connection and Connect to the Server          connection = DriverManager.getConnection( connection_string );          // Create a Command object for the SQL statement          statement = connection.createStatement( );          // Create a Reader for reading the result set          resultSet = statement.executeQuery( SQL );          while( resultSet.next( ) )          {             // Extract the data from the server and display it             String lname = resultSet.getString( 1 );             if( resultSet.wasNull( ) ) lname = "NULL";             String fname = resultSet.getString( 2 );             if( resultSet.wasNull( ) ) fname = "NULL";             String sales = resultSet.getString( 3 );             if( resultSet.wasNull( ) ) sales = "ZERO";             System.out.println( lname + ", " +                                  fname + " has sales of " + sales);          }       }        catch( Exception e )        {          // Print out the error string, if any.          System.out.println("Error:" + e.toString( ) );       } finally {          // Free up resources          if( resultSet != null )              try {resultSet.close( );} catch( Exception e ) {}          if( statement != null )              try {statement.close( );} catch( Exception e ) {}          if( connection != null )              try {connection.close( );} catch( Exception e ) {}             }    } }