SQL in a Nutshell, 2nd Edition

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