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 ) {}      

      }



   }

}