DATABASE

Getting Comfortable with ADO.NET 3.5 and SQL Server 2008

4/8/2011 5:47:14 PM
You need to familiarize yourself with the following primary .NET Framework namespaces to be able to program against SQL Server 2008:
  • System.Data— This is the root namespace, which contains essential data access classes, such as DataSet, DataTable, and DataRow.

  • System.Data.SqlClient— This namespace contains classes specialized for SQL Server access, such as SqlConnection, SqlCommand, and SqlParameter.

  • System.Xml— This namespace holds most of the objects you need to be able to work with SQL Server XML.

  • System.Linq and System.Data.Linq— These namespaces hold classes essential for working with LINQ to SQL (stored in the new Sytem.Core assembly).

The easiest way to immerse yourself in the code is to walk through some typical usage scenarios, which we do in the following sections.

ADO.NET: Advanced Basics

To start coding with ADO.NET and SQL Server, you first need to connect to an instance of SQL Server. To do this, you need a connection string. A connection string is simply a string literal that contains all the parameters necessary to locate and log in to a server in a semicolon-delimited format. The following is an example:

"Data Source=(SQLServer001);Initial Catalog=AdventureWorks2008;Integrated Security=True"


This connection string tells ADO.NET to connect to a server called SQLServer001, change to the AdventureWorks2008 database context, and use integrated Windows security to connect, which means it should use the credentials of the currently authenticated user (in web applications, this is usually ASPNET, unless impersonation is used). You typically want to store this connection string in your application’s .config file, preferably encrypted.

There are too many different connection string parameters to list here; you can check the MSDN “Connection Strings” topic for full information.

The managed object that represents a SQL Server connection is System.Data.SqlClient.SqlConnection.

This article takes you through a simple C# Windows application built with Visual Studio 2008 (VS). It contains every manager’s dream: a form with a magic button on it that does everything with one click. To create this application, open Visual Studio and create a new C# Windows Forms application (illustrated in Figure 1). Right-click the file called Form1.cs in Solution Explorer (SE) and select Rename. Change the name to MainForm.cs and accept the rename warning. Next, right-click MainForm.cs again and select View Code. Then type the following namespaces into the using area at the top left:

using System.Data.SqlClient;
using System.Configuration;

Figure 1. Creating a new C# Windows Forms application with Visual Studio 2008.

Next, right-click the References folder in the Solution Explorer and choose Add Reference. On the ensuing Add Reference dialog, click on the .NET tab and then scroll down until you find System.Configuration. Select this entry and click OK. Next, right-click the project in the Solution Explorer and choose Add New Item; then scroll though the choices that appear in the Add New Item dialog until you find Application Configuration File. This file is the place where you typically store connection strings and other configurable parameters. Open your new App.Config file and enter the following elements, substituting your server name for (local) in the connection string itself:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add
key="SqlConn"
value="Data Source=(local);Initial Catalog=AdventureWorks2008;
Integrated Security=True"/>
</appSettings>
</configuration>

Switching back to MainForm.cs, right-click the file in Solution Explorer and then select View Designer, which launches the Visual Studio WinForms designer. Drag a Button control from the Toolbox onto the form and name it btnGo. Next, drag a DataGridView control onto the form and name it GridView (ignore the form designer’s SmartTag); this control will be used to display the data returned when you execute your SqlCommand. The SqlCommand object allows you to execute T-SQL, including stored procedures, functions, and other expressions from within the context of an active SQL Server connection.

SqlCommand has several execution methods, each of which behaves differently and returns a different type of object:

  • ExecuteNonQuery— Executes the T-SQL statements and returns an Int32 that indicates the number of rows affected. It also populates any output parameters. This capability is especially useful when you are executing INSERT and UPDATE queries.

  • ExecuteScalar— Executes the T-SQL statements and returns an object (of type Object) that contains the value of the first column of the first row returned. The object returned is castable to one of the native .NET types (for example, Int32, String [even for returned xml columns], Boolean).

  • ExecuteReader— Executes the T-SQL statements and returns a SqlDataReader object. SqlDataReader objects are useful when you want to perform some behavior on the returned result set on a per-row and/or per-column basis (usually using a looping construct).

  • ExecuteXmlReader— Executes the T-SQL statements and returns a System.Xml.XmlReader, which you can use to iterate through the nodes in selected XML (or to instantiate other System.Xml objects, such as System.Xml.XPath.XPathDocument), produced either via SELECT...FOR XML or from a column or variable of the new xml data type.

Tip

System.Data.SqlClient also provides asynchronous versions of the preceding method calls in begin-call/end-call pairs that take a handy callback method parameter, including BeginExecuteReader, EndExecuteReader, BeginExecuteNonQuery, and EndExecuteNonQuery. Note that there is no BeginExecuteScalar/EndExecuteScalar.


To wire up the data returned from the SqlCommand into a System.Data.DataSet (the storage object for returned results), you need to use an object of type System.Data.SqlClient.SqlDataAdapter. Developers frequently use SqlDataAdapter objects to map data coming from SQL Server into DataSet objects and back, using its Fill() and Update() methods, respectively.

You may also want to include in your code a try-catch exception-handling block that catches any SqlException objects thrown during your code’s execution.

To test the many classes we’ve just mentioned, add the code in Listing 1 to the btnGo_Click() event handler. To generate the event handler, simply view your form in the Visual Studio designer and then double-click your button.

Listing 1. A Button Event Handler That Illustrates the Use of Several ADO.NET Objects
private void btnGo_Click(object sender, EventArgs e)
{
using (SqlConnection Connection =
new SqlConnection(ConfigurationManager.AppSettings["SqlConn"]))
{
using (SqlCommand Command =
new SqlCommand(
@"SELECT TOP 10 * FROM Person.Person"
, Connection))
{
try
{
using (SqlDataAdapter Adapter =
new SqlDataAdapter(Command))
{
using (DataSet Set = new DataSet())
{
Connection.Open();
Adapter.Fill(Set);
GridView.DataSource = Set.Tables[0];
}
}
}
catch (SqlException SqlEx)
{
foreach (SqlError SqlErr in SqlEx.Errors)
{
MessageBox.Show(
"The following SQL Error occurred: " + SqlErr.Message,
"SqlError");
}
}
}
}
}

Next, run your Windows Forms application (press F5) and then click btnGo. Your form should look something like the one in Figure 2.

Figure 2. Using ADO.NET in a simple Windows Forms application.

This code in your form executes as follows: a connection to SQL Server is made via SqlConnection and the subsequent call to Connection.Open() (which, by the way, is unnecessary because SqlDataAdapter.Fill() implicitly opens the closed connection). The SqlCommand object Command is set to use this connection via its constructor. The constructor also takes a string parameter that contains the text of the query. It can also take the name of a stored procedure, for example. When using a stored procedure name, you change its CommandType property from the default of CommandType.Text to CommandType.StoredProcedure.

Next, you instantiate a SqlDataAdapter object that registers the Command object as what it will execute on the call to Fill(). You also create a DataSet object to hold the returned data. In the simplest respect, DataSet objects are collections of DataTable objects, which map directly to SQL Server query results. Each DataTableDataRow objects, each of which in turn holds an array of DataColumn values accessible by indexers. object, as you may guess, holds an array of

You bind the DataGridView object to the filled DataSet object’s first table (Tables[0]), and you can rest assured that the catch block will notify you with a message box about each SqlError contained in the Errors collection of any raised SqlException.


Many of the database classes provided in ADO.NET have a Dispose() method because, under the covers, they utilize unmanaged (COM) resources. The objects you most commonly use for database applications that provide Dispose() are SqlConnection, SqlCommand, SqlDataAdapter, SqlDataReader, and DataSet.

Now that you’ve had a taste of working with some of the traditional ADO.NET objects, it’s time to forge ahead into the new world of LINQ.

 
Other  
  •  SQL Server System and Database Administration : System Views
  •  SQL Server System and Database Administration : System Tables & System Stored Procedures
  •  SQL Server System and Database Administration : System Databases
  •  SQL Server 2008 : Monitoring Your Server - Monitoring Your CPU
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 3) - XML DML & Converting a Column to XML
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 2) - SQL Server 2005 XQuery in Action
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 1) - XQuery Defined
  •  SQL Server 2008 : Monitoring Your Server - Familiarizing Yourself with the Performance Monitor
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 2) - FOR XML EXPLICIT
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 1) - FOR XML RAW & FOR XML AUTO
  •  SQL Server 2008 : Audit-Related Startup and Shutdown Problems
  •  SQL Server 2008 : Creating SQL Server Audits Using the GUI
  •  SQL Server 2008 : Creating Database Audit Specifications
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 3) - XML Indexes
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 2) - XML Schemas
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 1) - Working with the XML Data Type as a Variable & Working with XML in Tables
  •  SQL Server 2008 : Auditing SQL Server - Creating Server Audit Specifications
  •  SQL Server 2008 : Auditing SQL Server - Creating SQL Server Audits with T-SQL
  •  Programming Microsoft SQL Serve 2005 : An Overview of SQL CLR - Security
  •  
    Top 10
    Exchange Server 2010 : Active Manager - Automatic database transitions & Best copy selection
    Exchange Server 2010 : Breaking the link between database and server
    iPhone 3D Programming : Drawing an FPS Counter (part 2) - Rendering the FPS Text
    iPhone 3D Programming : Drawing an FPS Counter (part 1) - Generating a Glyphs Texture with Python
    Mobile Application Security : Mobile Geolocation - Geolocation Methods & Geolocation Implementation
    Mobile Application Security : SMS Security - Application Attacks & Walkthroughs
    Transact-SQL in SQL Server 2008 : Table-Valued Parameters
    Transact-SQL in SQL Server 2008 : New date and time Data Types and Functions
    Windows 7 : Working with User Accounts (part 2)
    Windows 7 : Working with User Accounts (part 1)
    Most View
    Creating Link-Worthy Content and Link Marketing : Choosing the Right Link-Building Strategy
    Working with Access and Connectivity Policies in Vista
    Wireless Security in Windows Vista
    New Features of Windows Vista's Firewall
    Mobile Application Security : Windows Mobile Security - Development and Security Testing (part 3)
    SQL Server 2008: Managing Resources with the Resource Governor (part 1) - Resource Pools
    Windows Server 2008: IPv6 Introduction (part 2) - IPv6 Transition Technologies & The ISATAP Tunneling Protocol
    Transact-SQL in SQL Server 2008 : Row Constructors
    Performing Granular Backup Using the SharePoint Central Administration
    Exchange Server 2010 : Monitoring and Troubleshooting Unified Messaging
    Exchange Server 2010 : Unified Messaging Shell Commands
    Building Android Apps: Web SQL Database (part 1) - Creating a Database
    Windows Server 2008: Active Directory Infrastructure - Deploying Read-Only Domain Controllers (RODCs)
    Managing Cached Content
    Algorithms for Compiler Design: USING DAG FOR CODE GENERATION
    Programming Microsoft SQL Server 2005: Using Data Mining Extensions (part 1) - Data Mining Modeling Using DMX
    Optimizing an Exchange Server 2010 Environment : Analyzing Capacity and Performance
    Using Windows Phone 7 Technologies : Understanding Orientation and Movement
    Building LOB Applications : Data Validation through Data Annotation
    Beginning Android 3 : Working with Containers - Tabula Rasa