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