SQL in a Nutshell, 2nd Edition [Electronic resources] نسخه متنی

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