MasteringC#Database Programming

Overview

Enter a New World of Database Programming

C# and ADO.NET facilitate the development of a new generation of database applications, including remote applications that run on the Web. Mastering C# Database Programming is the resource you need to thrive in this new world. Assuming no prior experience with database programming, this book teaches you every aspect of the craft, from GUI design to server development to middle-tier implementation. If you're familiar with earlier versions...

See more details below
Other sellers (Paperback)
  • All (19) from $2.93   
  • New (7) from $37.27   
  • Used (12) from $2.93   
Sending request ...

Overview

Enter a New World of Database Programming

C# and ADO.NET facilitate the development of a new generation of database applications, including remote applications that run on the Web. Mastering C# Database Programming is the resource you need to thrive in this new world. Assuming no prior experience with database programming, this book teaches you every aspect of the craft, from GUI design to server development to middle-tier implementation. If you're familiar with earlier versions of ADO, you'll master the many new features of ADO.NET all the more quickly. You'll also learn the importance of XML within the new .NET paradigm. Coverage includes:

  • Accessing a database using C# and ADO.NET
  • Using SQL to access a database
  • Using Visual Studio .NET to build applications
  • Creating and modifying database tables
  • Understanding ADO.NET classes
  • Designing, building, and deploying Web applications that access a database
  • Designing, building, and deploying effective Web services
  • Using SQL Server's built-in XML capabilities
  • Working with a database in a disconnected manner
  • Using advanced transaction controls
  • Using Transact-SQL to create stored procedures and functions in a SQL Server database
Read More Show Less

Product Details

  • ISBN-13: 9780782141832
  • Publisher: Wiley
  • Publication date: 5/6/2003
  • Edition number: 1
  • Pages: 688
  • Sales rank: 1,305,492
  • Product dimensions: 9.25 (w) x 7.50 (h) x 1.38 (d)

Meet the Author

Jason Price has more than 10 years' experience in the software industry. He is an MCSE, OCP, and has extensive experience with C#, .NET, and Java. He is the author of Sybex's Mastering Visual C# .NET and two Oracle books for other publishers. All his books have received 5-star reviews on Amazon.

Read More Show Less

Read an Excerpt

Mastering C# Database Programming


By Jason Price

John Wiley & Sons

ISBN: 0-7821-4183-8


Chapter One

Introduction to Database Programming with ADO.NET

A DATABASE is an organized collection of information that is divided into tables. Each table is further divided into rows and columns; these columns store the actual information. You access a database using Structured Query Language (SQL), which is a standard language supported by most database software including SQL Server, Access, and Oracle.

In this chapter, you'll see a C# program that connects to a SQL Server database, retrieves and displays the contents stored in the columns of a row from a table, and then disconnects from the database. You'll also see programs that connect to Access and Oracle databases.

You'll also learn about Microsoft's rapid application development (RAD) tool, Visual Studio .NET (VS .NET). VS .NET enables you to develop, run, and debug programs in an integrated development environment. This environment uses all the great features ofWindows, such as the mouse and intuitive menus, and increases your productivity as a programmer.

In the final sections of this chapter, you'll see how to use the extensive Microsoft documentation that comes with the .NET Software Development Kit (SDK) and VS .NET. You'll find this documentation invaluable as you become an expert with ADO.NET and C#. You'll also learn how to use the SQL Server documentation.

Featured in this chapter:


* Obtaining the required software
* Developing your first ADO.NET program
* Connecting to Access and Oracle databases
* Introducing Visual Studio .NET
* Using the .NET documentation
* Using the SQL Server documentation

Obtaining the Required Software

Before you can develop C# programs, you'll need to install either the .NET Software Development Kit (SDK) or VS .NET. You can download the .NET SDK at msdn.microsoft.com/downloads (search for the Microsoft .NET Framework Software Development Kit). You can purchase a trial or full copy of VS .NET from Microsoft at msdn.microsoft.com/vstudio.

To install the .NET SDK, run the executable file you downloaded and follow the instructions on the screen to install it on your computer. To install VS .NET, run the setup.exe file on the disk and follow the instructions on the screen.

You'll also need a copy of the SQL Server database software. At time of writing, you can download a trial version of SQL Server from Microsoft at microsoft.com/sql. You can also purchase a trial or full copy of SQL Server from Microsoft's Web site.

This book uses the Developer Edition of the SQL Server 2000 software and uses a database named Northwind. This database contains the information for the fictitious Northwind Company, which sells food products to customers. Northwind is one of the example databases that you can install with SQL Server. Customer information in the Northwind database is stored in a table named Customers; you'll see the use of this table in the example program later in this chapter.

If you don't want to download or purchase a trial version of SQL Server, the .NET SDK (and VS .NET) comes with a stand-alone desktop database server known as the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). MSDE 2000 has a version of the Northwind database that you can use instead of the SQL Server Northwind database-although you won't get all of the graphical administration tools that come with SQL Server. If you're using the .NET SDK and want to install MSDE 2000, select Start ?? Microsoft .NET Framework SDK ?? Samples and QuickStart Tutorials. If you're using VS .NET and want to install MSDE 2000, run the setup.exe program that you use to install VS .NET and select MSDE 2000 as a new feature to install.

Note You can learn more about MSDE 2000 microsoft.com/sql/techinfo/development/ 2000/msde2000.asp.

Developing Your First ADO.NET Program

In this section you'll plunge into ADO.NET programming and see a C# program that performs the following tasks:


1. Connects to the SQL Server Northwind database
2. Retrieves a row from the Customers table
3. Displays the columns from the row
4. Closes the database connection

You'll be introduced to many concepts in this section that are fully explored in later chapters. Don't be too concerned about all the details of the concepts at this stage; you'll learn those details in the later chapters.

Listing 1.1 shows the example program, which is contained in the file FirstExample.cs.

NOTE You can download all the source files for the programs featured in this book from the Sybex Web site at sybex.com. You'll find instructions on downloading these files in the introduction of this book. Once you've downloaded the files, you can follow along with the examples without having to type in the program listings.

Let's go through the lines in FirstExample.cs. The first set of lines is a comment that indicates what the program does:

/* FirstExample.cs illustrates how to: 1. Connect to the SQL Server Northwind database. 2. Retrieve a row from the Customers table using a SQL SELECT statement. 3. Display the columns from the row. 4. Close the database connection.
*/

The next two lines indicate the namespaces being referenced in the program with the using statement:

using System; using System.Data.SqlClient;

The System namespace is the root namespace and is referenced so that we can simply use Console .WriteLine() calls in the program, rather than the fully qualified System.Console.WriteLine() call. The System.Data.SqlClient namespace contains the ADO.NET classes for use with SQL Server, including the SqlConnection, SqlCommand, and SqlDataReader classes that are used later in the program. You'll be introduced to these classes shortly, and you'll learn the full details of the ADO.NET classes as you progress through this book.

You handle exceptions that might be thrown in your code by placing the code within a try/catch block. You'll notice that the nine steps are placed within a try/catch block in the Main() method, with the catch block handling a SqlException object that might be thrown by the code in the try block. You'll learn more about this later in the section "Handling Exceptions" after I've discussed the nine steps in the following sections.

Step 1: Create a SqlConnection Object to Connect to the Database

You use an object of the SqlConnection class to connect to a SQL Server database. Step 1 in the Main() method creates a SqlConnection object named mySqlConnection to connect to the SQL Server Northwind database:

SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" );

The string passed to the SqlConnection constructor is known as the connection string and contains the following elements:

server Specifies the name of the computer on which SQL Server is running-localhost in this example; localhost is a common name that refers to the computer on which your program runs. If your database is running on a computer other than the one your program is running on, then you'll need to replace localhost with the name of that computer.

database Specifies the name of the database-Northwind in this example.

uid Specifies the name of the database user account-sa in this example; sa is a common database user account used by the database administrator (DBA). You can use any database user account as long as it has access to the Northwind database.

pwd Specifies the password for the user. The password for the sa user in my database is also sa. You'll need to change pwd to the password for your sa account, or whichever account you specified in uid.

You'll need to change the settings of some or all of the previous elements in your connection string. You might need to speak with your DBA to get the various elements that make up your connection string. Once you have the correct values, you should make the changes to the connection string in your copy of FirstExample.cs.

NOTE A database administrator (DBA) is responsible for performing tasks such as installing the database software, backing up the databases, and so on.

Step 2: Create a SqlCommand Object

Step 2 creates a SqlCommand object named mySqlCommand that is used later to send a SELECT statement to the database for execution.

SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

Step 3: Set the CommandText Property of the SqlCommand Object

You use SQL to work with the information stored in a database. SQL is an industry standard language supported by SQL Server, Access, and Oracle. You use the SQL SELECT statement for retrieving information from a database. You'll learn the basics of SQL in Chapter 3, "Introduction to the Structured Query Language."

Step 3 sets the CommandText property of mySqlCommand created in the previous step to a SELECT statement. This statement will retrieve the CustomerID, CompanyName, ContactName, and Address columns from the row in the Customers table whose CustomerID is ALFKI:

mySqlCommand.CommandText = "SELECT CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "WHERE CustomerID = 'ALFKI'";

Step 4: Open the SqlConnection Object

Step 4 opens the database connection using the Open() method of the SqlConnection object created in step 1:

mySqlConnection.Open();

Once the connection to the database is open, you can send commands to the database for execution.

Step 5: Run the SELECT Statement

You run the SELECT statement previously set in mySqlCommand by calling the ExecuteReader() method. This method returns a SqlDataReader object that you then use to read the row data returned by the SELECT statement.

Step 5 creates a SqlDataReader object and calls the ExecuteReader() method of mySqlCommand object to run the SELECT statement:

SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

Step 6: Read the Row Using the SqlDataReader Object

Step 6 reads the row in mySqlDataReader using the Read() method:

mySqlDataReader.Read();

Step 7: Display the Column Values from the SqlDataReader Object

You can read the value for a column from mySqlDataReader by passing the name of the column in square brackets. For example, mySqlDataReader["CustomerID"] returns the value of the CustomerID column.

Step 7 displays the column values for the CustomerID, CompanyName, ContactName, and Address column values:

Console.WriteLine("mySqlDataReader[\"CustomerID\"] = " + mySqlDataReader["CustomerID"]); Console.WriteLine("mySqlDataReader[\"CompanyName\"] = " + mySqlDataReader["CompanyName"]); Console.WriteLine("mySqlDataReader[\"ContactName\"] = " + mySqlDataReader["ContactName"]); Console.WriteLine("mySqlDataReader[\"Address\"] = " + mySqlDataReader["Address"]);

Step 8: Close the SqlDataReader Object

When you're finished reading rows from a SqlDataReader object, close it using the Close() method. Step 8 calls the Close() method for mySqlDataReader:

mySqlDataReader.Close();

Step 9: Close the SqlConnection Object

When you're finished accessing the database, close your SqlConnection object using the Close() method. Step 9 calls the Close() method for mySqlConnection:

mySqlConnection.Close();

Handling Exceptions

You handle exceptions that might be thrown in your code by placing the code within a try/catch block. You'll notice that the nine steps are placed within a try/catch block, with the catch block handling a SqlException object that might be thrown by the code in the try block. The SqlException class is specifically for use with code that accesses a SQL Server database. The following example shows how to structure a try/catch block:

try { /* code that might throw a SqlException */ } catch (SqlException e) { Console.WriteLine("A SqlException was thrown"); Console.WriteLine("Number = " + e.Number); Console.WriteLine("Message = " + e.Message); Console.WriteLine("StackTrace:\n" + e.StackTrace); }

The properties displayed in the catch block are as follows:

Number The error number

Message A string containing a description of the error

StackTrace A string containing the name of the class and the method from which the exception was thrown

The two most common examples of when a SqlException object is thrown are as follows:

* Your SqlConnection object is unable to connect to the database. If this happens, you should check the connection string that specifies how to connect to your database.

* Your SELECT statement contains a mistake in the spelling of a table or column.

The following example output shows what happens when the SqlConnection object in First- Example.cs is unable to connect to the database because the database is currently down:

A SqlException was thrown Number = -2 Message = Timeout expired. Possible reasons: the timeout period elapsed prior to completion of the operation, the server is not responding, or the maximum pool size was exceeded. Please see the documentation for further details. StackTrace: at System.Data.SqlClient.SqlConnection.Open() at FirstExample.Main()

You can use the output from your catch block to determine the problem. If the database is down, contact your DBA.

NOTE For brevity, the only program to use a try/catch block in this book is FirstExample.cs. You should use try/catch blocks in your own programs to catch exceptions. For more details on handling exceptions, I recommend the book Mastering Visual C# .NET from Sybex (2002).

In the next section you'll see how to compile FirstExample.cs and run it.

Compiling and Running FirstExample.cs

You can compile the FirstExample.cs program using either the command-line tool that comes with the .NET SDK or VS .NET. In this section, you'll see how to use the command-line version of the compiler for FirstExample.cs program. Later in this chapter, in the section "Introducing Visual Studio .NET," you'll see how to use VS .NET to compile and run a program.

You run the command-line version of the compiler by entering csc in the Command Prompt tool, followed by the name of your program source file. For example, to compile FirstExample.cs, you would enter the following command in the Command Prompt tool:

csc FirstExample.cs

If you want to follow along with the examples, start the Command Prompt tool by selecting Start > Programs > Accessories > Command Prompt.

NOTE If you're using Windows XP rather than Windows 2000, start the Command Prompt tool by selecting Start > All Programs > Accessories > Command Prompt.

Next, you need to change directories to where you copied the FirstExample.cs file. To do this, you first enter the partition on your hard disk where you saved the file. For example, let's say you saved the file in the ADO.NET\book\ch01\programs directory of the C partition of your hard disk. To access the C partition, you enter the following line into the Command Prompt tool and then you press the Enter key:

C:

Next, to move to the ADO.NET\book\ch01\programs directory, you enter cd followed by ADO.NET\ book\ch01\programs:

cd ADO.NET\book\ch01\programs

To compile FirstExample.cs using csc, you enter the following command:

csc FirstExample.cs

Notice that the name of the program source file follows csc; in this case, it's FirstExample.cs.

If you get an error when running csc, you'll need to add the directory where you installed the SDK to your Path environment variable. The Path environment variable specifies a list of directories that contain executable programs. Whenever you run a program from the command prompt, the directories in the Path variable are searched for the program you want to run. Your current directory is also searched. To set your Path environment variable, do the following:


1. Select Start > Settings > Control Panel. Then double-click System and select the Advanced tab.
2. Click the Environment Variables button and double-click Path from the system variables area at the bottom.
3. Add the directory where you installed the SDK to your Path environment variable.
4. Click OK to save your change, and then click OK again on the next dialog.
5. Restart Command Prompt so that your change is picked up. You should then be able to run csc successfully.

The compiler takes the FirstExample.cs file and compiles it into an executable file named First- Example.exe. The .exe file contains instructions that a computer can run, and the .exe file extension indicates the file is an executable file.

(Continues...)



Excerpted from Mastering C# Database Programming by Jason Price Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Read More Show Less

Table of Contents

Introduction.

Part 1: Introduction to ADO.NET and Databases.

Chapter 1: Introduction to Database Programming with ADO.NET.

Chapter 2: Introduction to Databases.

Chapter 3: Introduction to Structured Query Language (SQL).

Chapter 4: Introduction to Transact-SQL Programming.

Chapter 5: Overview of the ADO.NET Classes.

Chapter 6: Introducing Windows Applications and ADO.NET.

Part 2: Fundamental Database Programming with ADO.NET.

Chapter 7: Connecting to a Database.

Chapter 8: Executing Database Commands.

Chapter 9: Using DataReader Objects to Read Results.

Chapter 10: Using DataSet Objects to Store Data.

Chapter 11: Using DataSet Objects to Modify Data.

Chapter 12: Navigating and Modifying Related Data.

Chapter 13: Using DataView Objects.

Part 3: Advanced Database Programming with ADO.NET.

Chapter 14: Advanced Transaction Control.

Chapter 15: Introducing Web Applications'ASP.NET.

Chapter 16: Using SQL Server's XML Support.

Chapter 17: Web Services.

Index.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)