Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 742
Part V Data Connectivity
It’s up to developers to instrument their application by adding trace points during development.
By default, every .NET application domain — the basic unit of isolation in the CLR — contains a
DefaultTraceListener, and can contain other listeners in its collection. Normally the role of
the trace listener is to direct trace output to a file or the event log. A .NET application developer
can define trace points at method calls or even metrics such as performance counters throughout the
application’s code.
Trace switches can also be defined by the developer to produce various levels of trace output
during runtime, depending on the value of the trace switch. Trace switches can be set at runtime
within the
app.config file. Under favorable run conditions, tracing can be disabled. If problems
occur, then tracing can be enabled at a level appropriate for capturing information for a given
problem.
Among the wealth of information available on the Internet about trace instrumenta-
tion with the .NET Framework is a very good MSDN magazine article by Jon Fancet,
‘‘Powerful Instrumentation Options in .NET Let You Build Manageable Apps with Confidence,’’ at
and an excellent MSDN white
paper by Bob Beauchemin, ‘‘Tracing Data Access,’’ at
/>library/ms971550.aspx
.
Beginning with ADO.NET 2.0, trace instrumentation is extended by Microsoft to include built-in
trace points in the ADO.NET assemblies, the SQL Native Client .
dll, and the other .NET providers.
ADO.NET 2.0 introduced built-in tracing functionality that is supported by all the .NET data providers
for SQL Server, Oracle, OLEDB, and ODBC, and even the ADO.NET DataSet and SQL Server 2005
network protocols.
Tracing is not an ADO.NET-specific feature, but Microsoft providers in ADO.NET 2.0 and later can take
advantage of generalized tracing and instrumentation APIs.
Tracing data access API calls can help diagnose problems such as schema mismatch between client and
database, incorrect SQL, invalid programming logic, and database availability.

Application Building Basics
This discussion of ADO.NET 3.5 would not be complete without a look at the code techniques that
have been covered thus far in this chapter. This section provides code that uses the
adodb.dll .NET
primary interop assembly to interact with SQL Server, code that uses the
SqlClient to perform
equivalent operations, and code that demonstrates access through the SQL Native Client using the
common base classes. As stated previously, the ADO and ADO.NET technologies are fundamentally
different yet do similar work. The code is presented to show how different interactions with the database
are completed by each technology.
Little space is given here to matters of graphical interface design, XML, or remoting in the .NET Frame-
work. Windows Forms design, ASP.NET, SOA, and remoting are extensive subjects that require more
space than the SQL Server 2008 Bible can allocate. Instead, shown here are the methods needed to move
data in and out of the data source and how to work with the ADO.NET components. The purpose is to
see how easily and elegantly data can be moved in and out of the data access layer. What is done with
that data above the data access layer is best left to the developer’s imagination.
742
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 743
Programming with ADO.NET 3.5 32
With that said, the coding style used here is intended only to permit a straightforward view of a particu-
lar method. How ADO.NET is used in .NET Framework programming depends largely on usage require-
ments and the established coding practices within a given development environment.
The first activity in any ADO.NET project is to connect to the data source(s) that will be used to
develop the project using the Server Explorer.
Sample code for this chapter is available on the
SQL Server 2008 Bible
website at
www.sqlserverbible.com. The code uses a console application to show the techniques
that follow.

Connecting to SQL Server
The easiest way to create a connection is to run the Data Source Configuration Wizard. From the Visual
Studio Data menu, launch the wizard by selecting Add New Data Source, or view the data sources asso-
ciated with an existing application project by selecting the Show Data Sources menu option.
It is easy to programmatically define a connection string. The connection string is a set of name-value
pairs. One slight confusion in defining connection strings is that they can be somewhat different for each
.NET provider, and the ADO connection is different still.
With .NET Framework 2.0 and later, connection strings are more flexible than in the past.
For example, an
ADODB.dll provider connection string or a SNAC connection string allow
the SQL Server to be called a ‘‘Data Source’’ or a ‘‘Server.’’
It is possible to define the connection string based on values stored in the file system or in the registry
or specified by a user. If the common base class is used, then it is possible to define both the provider
used and the connection string in the
app.config file.
What’s new in ADO.NET 3.5
This section covers what’s new in ADO.NET 3.5.
LINQ
One of the major new features to ADO.NET 3.5 is LINQ (Language Integrated Query). LINQ is a wel-
come new advancement in query technology that introduces query facilities and capabilities directly into
the .NET Framework 3.0 programming languages.
The goal of LINQ is to fill the gap between two completely different spheres, that of data and that of
objects. For example, developers up until now have had to learn completely different querying tech-
nologies or languages, depending on the source of data, such as SQL databases and XML documents.
LINQ bridges this gap by making a query a first-class construct in .NET. Instead of writing inline SQL
to query a SQL database or using XQuery or XPath to query an XML document, developers can now
write queries against strongly typed objects using standard query operators and keywords.
The additional benefit of this is that now there is compile-time syntax checking and IntelliSense,
providing improved developer productivity. Because of LINQ integration into Visual Studio, developers
can write queries in either C# or Visual Basic that can query SQL databases, ADO.NET DataSets, XML

documents, and even an in-memory collection of objects. Anything that implements and supports the
IEnumerable or IEnumerable<T> interfaces can be queried using LINQ.
Chapter 34, ‘‘LINQ,’’ is completely dedicated to LINQ and LINQ technologies.
743
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 744
Part V Data Connectivity
Table-valued parameters
Table-valued parameters are a new parameter type in SQL Server 2008 that enable developers to send
multiple rows of data to a stored procedure or function. They are declared by using user-defined table
types and have the benefit of not requiring the creation of temporary tables or passing in a bunch of
parameters.
In essence, table-valued parameters are an easy way to funnel multiple rows of data from the application
to SQL Server in a single trip. An added value is that table-valued parameters are strongly typed, and
structure validation is automatic. Moreover, the only limitation to the size of a table-valued parameter
is the amount of server memory.
For example, the following illustrates how to add rows of data to a table-valued parameter from a data
reader:
Dim cmd As New SqlCommand("usp_AddEmployees", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim tvp As SqlParameter = cmd.Parameters.AddWithValue( _
"@tvpNewEmployees", dataReader)
tvp.SqlDbType = SqlDbType.Structured
Likewise, a SQL parameter can be configured to insert data using a table-valued parameter as follows:
Dim cmd AS New SqlCommand(strInsert, conn)
Dim tvp As SqlParameter = cmd.Parameters.AddWithValue( _
"@tvpNewEmployees", addedEmps)
tvp.SqlDbType = SqlDbType.Structured
tvp.TypeName = "@EmployeeTableType"
From the SQL side, a table type needs to be created that defines the table structure. That new table type

can then be used in a routine (stored procedure or function, for example).
Keep in mind that table-valued parameters are for input. Using the
OUTPUT keyword won’t work; it is
not supported and data cannot be returned. In addition, a name type for the table-value parameter must
be specified as part of the
SqlParameter.ThisisdoneusingtheTypeName property.
From an application perspective, the SqlClient provider supports populating table-valued param-
eters via several options. A developer can use a
DataTable,aDbDataReader, or a generic list
(
System.Collections.Generic.IList<SqlDataRecord>).
Stored procedures vs. parameterized/ad-hoc queries
‘‘Is it better to use stored procedures or parameterized/ad-hoc queries?’’ is an age-old debate. Everyone
has an opinion on this topic — a topic that has caused watercoolers everywhere to boil over from the
heated discussions among developers gathered around them. First, you need to understand the differ-
ence between the two options.
Ad-hoc SQL is any query sent to SQL Server as opposed to calling stored procedures. Not all ad-hoc
queries are parameterized. This differs from dynamic SQL in that dynamic SQL is any code-generated
SQL regardless of whether it is generated in the client or in a stored procedure.
744
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 745
Programming with ADO.NET 3.5 32
What I want to stress here is that when dealing with SQL on the client side, a developer has several
options, and some are better than others. First and foremost, use stored procedures whenever possible.
The following .NET code calls a stored procedure, which also utilizes a parameter:
Dim tConnStr = "connectioninfo"
Using conn As New SqlConnection(tConnStr)
Dim cmd As New SqlCommand("uspGetEmployeeInfo", conn)
cmd.CommandType = CommandType.StoredProcedure

conn.Open()
Dim param As New SqlParameter("@EmployeeID", SqlDbType.Int)
With param
.IsNullable = True
.Direction = ParameterDirection.Input
.Value = 5
End With
cmd.Parameters.Add(param)
Dim rdr As SqlDataReader = cmd.ExecuteReader
If rdr.HasRows Then
rdr.Read()
End If
End Using
Why use stored procedures? There are several reasons. First, they create an abstraction layer between the
database and the application. By creating this layer, changes to the database (schema changes, new DB
technology, tuning) have much less chance of breaking the application. The application should have no
knowledge of the physical shape of the database at all. This enables the database experts to work their
tuning/indexing/relationship magic without breaking the application.
The second reason is security. SQL injection attacks are much less common and easier to prevent when
using stored procedures. This is because, as stated earlier, ad-hoc queries require that the underlying
database objects be exposed.
However, if you need to use ad-hoc queries, make sure that the SQL is used with parameters, as shown
in the example that follows, as this helps lessen the likelihood of an injection attack:
Dim tConnStr = "connectioninfo"
Using conn As New SqlConnection(tConnStr)
Dim cmd As New SqlCommand("SELECT EmployeeID, ManagerID, _ Title,
HireDate, BirthDate FROM HumanResources.Employee WHERE _ EmployeeID =
@EmployeeID", conn)
cmd.CommandType = CommandType.Text
conn.Open()

745
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 746
Part V Data Connectivity
Dim param As New SqlParameter("@EmployeeID", SqlDbType.Int)
With param
.IsNullable = True
.Direction = ParameterDirection.Input
.Value = 5
End With
cmd.Parameters.Add(param)
Dim rdr As SqlDataReader = cmd.ExecuteReader
If rdr.HasRows Then
rdr.Read()
End If
End Using
I cannot stress enough the importance of using stored procedures and parameterized queries (whether
it’s with stored procedures or ad-hoc queries). However, by using stored procedures, the likelihood of
breaking the application when the database schema changes is drastically reduced; and it is hard to tune
a database when the SQL is embedded in the application.
There must be a layer of abstraction between the application and the database. When designing a
database, many developers forget that there are two layers to the process — physical and logical. Many
developers create the database and then begin to code directly against the physical layer. Because there is
now no layer of abstraction, they lose many of the benefits described earlier.
For more information on SQL generation and dynamic SQL, see Chapter 29 ‘‘Dynamic
SQL and Code Generation,’’ as well as Chapter 63, ‘‘Interpreting Query Execution
Plans.’’
Data adapters
The Data Source Configuration Wizard offers opportunities to specify tables, views, stored procedures,
and functions that are to be used to create the

DataSet and to create a typed DataSet. Alternately,
the
DataSet can be added and specified by adding a data set to the project in Solution Explorer. Using
this method, the Dataset Designer will be opened for the project.
The Dataset Designer enables either the toolbox orServerExplorertobeusedtosourceandidentify
the application’s
DataSet. The Dataset Designer tool is another great way to ensure that only typed
data sets are created. The programming purist can even type out the definition for a typed data set if so
desired, although there seems to be scant justification for this technique.
Recall that a typed
DataSet can prevent runtime data conversion errors from sneaking into the code. If
the intention is to use an untyped
DataSet, then there is no reason to use the Dataset Designer. Sim-
ply declare a new, empty
DataSet in-line, before it is filled from the specified DataAdapter:
‘populate an untyped dataset from a SqlDataAdapter
Dim daScrapReasons As SqlDataAdapter = _
New SqlDataAdapter(sSQLScrapReasons, cnADONET2)
746
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 747
Programming with ADO.NET 3.5 32
‘create an untyped dataset
Dim dsWOWithScrap As New DataSet
‘fill the DataSet from the Adapter
daScrapReasons.Fill(dsWOWithScrap, "ScrapReason")
If the developer opts for the typed data set, then the Table Adapter Configuration Wizard can be
used to create a
TableAdapter for each table in the specified DataSet. The wizard is launched
from the context menu of the Dataset Designer design surface. Within this wizard, the programmer

can select database tables or specify queries and stored procedures from which to populate the table
adapter(s). Once the table population is defined, the wizard willingly creates the
INSERT, UPDATE,
and
DELETE SQL commands that will be executed when the DataAdapter.Update method is
called on the data set. These commands can be viewed and edited as desired in the Properties pane
of the adapter. The code generated by the design surface can also be viewed. Editing this code is
not recommended, as changes may cause incorrect behavior. Any custom changes will be lost if the
DataSet or TableAdapter is regenerated by the wizard.
DataReaders and Recordsets
ADO Recordsets can be processed as Server API cursors or as client cursors. On the client side, the
Recordsets can be used after the database connection is closed, but it is completely up to the devel-
oper to determine if and how the data can be modified and propagated back to the database:
‘A record set from an ADO server side cursor
Dim rsADOWOWithScrap As New ADODB.Recordset
rsADOWOWithScrap.CursorLocation = CursorLocationEnum.adUseServer
rsADOWOWithScrap.Open(sSQLWOWithScrap, cnADO,_
CursorTypeEnum.adOpenForwardOnly, _
LockTypeEnum.adLockReadOnly)
‘A disconnected recordset from an ADO client cursor
Dim cmdADOWOWithScrap1 As New ADODB.Command
Dim rsADOWOWithScrap1 As New ADODB.Recordset
cmdADOWOWithScrap1.CommandText = sSQLWOWithScrap
cmdADOWOWithScrap1.CommandType = _
CommandTypeEnum.adCmdText
rsADOWOWithScrap1.CursorType = _
CursorTypeEnum.adOpenStatic
rsADOWOWithScrap1.LockType = _
LockTypeEnum.adLockBatchOptimistic
rsADOWOWithScrap1.CursorLocation = _

CursorLocationEnum.adUseClient
cmdADOWOWithScrap1.ActiveConnection = cnADO
rsADOWOWithScrap1 = cmdADOWOWithScrap1.Execute
The ADO.NET DataReader is a forward-only client cursor. It can be loaded into a DataTable if
needed. The
DataAdapter includes methods to insert, update, and delete data from the DataSet’s
DataTable back to the database.
747
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 748
Part V Data Connectivity
Streams
Data can be streamed in and out of the database as XML or in binary format. Streams can be somewhat
difficult to work with because of their transient nature. Generally, a stream can be consumed once and
then it disappears:
Dim bfBINWOWithScrap As New Binary.BinaryFormatter
Dim msXMLWOWithScrap As New MemoryStream()
Dim msBINWOWithScrap As New MemoryStream()
‘get XML streamfrom dataset
bfBINWOWithScrap.Serialize(msXMLWOWithScrap, _
dsBINWOWithScrap)
Typically, XML streams are used in .NET Framework AppDomains and to move data in service-oriented
settings. Binary streams are most frequently used in scenarios where remoting between AppDomains is
appropriate. Intimate knowledge of the data is necessary to serialize (that is, meaningfully write to disk)
a binary stream. The XML stream includes metadata information and can be serialized by any receiver
that is able to manipulate XML.
Asynchronous execution
In some cases, a query or procedure needs time to execute at the database, and the application can do
other useful work while the query completes. In spite of the somewhat restrictive rules regarding when
asynchronous execution can be used, the capability provides increased flexibility, as shown in the fol-

lowing code:
Dim rdrAsyncScrapCountInit As IAsyncResult = _
cmdScrapCount.BeginExecuteReader
‘do some other work
Dim rdrAsyncScrapCount As SqlDataReader = _
cmdScrapCount.EndExecuteReader(rdrAsyncScrapCountInit)
The database connection will support only one asynchronous query at a time. This means that from the
application or mid-tier, multiple connections might be required. From server-side CLR, asynchronous
queries are not supported in ADO.NET 3.5.
Using a single database value
An application is often interested in a single column or perhaps a few columns from a single row. With
ADO, the options are to execute a query and return the value into a
Recordset that is one-column
wide and one-row long, or to execute a command with output parameters defined in the command’s
parameters collection. The single value or single row into a
Recordset is well known to be the least
scalable because of the overhead required to build and tear down the
Recordset repeatedly. Therefore,
in ADO, the preference should always go to using the parameters collection:
paramscrapWOCount = _
cmdScrapWOByProduct.CreateParameter("ScrapWOCount", _
748
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 749
Programming with ADO.NET 3.5 32
DataTypeEnum.adInteger, _
ParameterDirectionEnum.adParamOutput)
cmdScrapWOByProduct.Parameters.Append(paramscrapWOCount)
cmdScrapWOByProduct.Parameters.Item("ProductName").Value =
sProductName

With ADO.NET, the options are greater and the preference less obvious. The value can be returned
into a
DataTable of one row in length. This method could be advantageous in some scenarios
where the data point is used in concert with other
DataTables in the DataSet. In addition, the
ExecuteWithNoQuery method could be used to populate variables via the parameters collection when
multiple columns from a single row are of interest; or the
ExecuteScalar method could be used for
cases when only a single value will be needed:
Dim iScrapWOCount As Integer = _
cmdScrapCount.ExecuteScalar()
or
cmdScrapCountByProduct.Parameters.Add("@ScrapWOCount", _
SqlDbType.Int).Direction = _
ParameterDirection.Output
cmdScrapCountByProduct.ExecuteNonQuery()
Data modification
When a change is made to data in the .NET application, the change must be moved to the database.
This can be done within a pessimistic concurrency model or an optimistic concurrency model. The con-
currency model is actually a mechanism to aid the developer in deciding whether to use a disconnected
ADO.NET
DataClass (under the optimistic concurrency model) or a connected method (under the
pessimistic model) to read and write to the database.
The pessimistic concurrency model is useful when data is bulk loaded or when DML
queries (
INSERT, UPDATE,orDELETE SQL statements) or input parameters are used within
an
ExecuteNonQuery statement. The optimistic concurrency model is useful when it is desirable for the
application to take specific steps such as retrying or writing to a failure log when a data modification
operation fails.

Updating a DataSet requires nothing more than value assignment within .NET. Any or all rows in a
DataSet can be inserted, deleted, or updated. When a data row is changed within a DataSet,the
row is marked as changed. Running the
Update method of the DataAdapter will cause the INSERT,
UPDATE,andDELETE commands defined in the DataAdapter to be executed on the database using
the
DataAdapter’s data. It is possible that another user has changed a data point between the time
it was read to a
DataAdapter and the time that data was written back to the database. In such a
case, if the insert fails, then the code must determine whether a concurrency error has occurred. This
is typically done with a timestamp column or by comparing the original values in the DataSet with the
current values in the database when an
UPDATE method is executed. It is the developer’s responsibility
to determine the correct resolution for such concurrency collisions.
In ADO.NET 1.1, the
INSERT, UPDATE,andDELETE commands processed by the Update method
could be automatically generated by the Table Adapter Configuration Wizard. The generated statements
749
www.getcoolebook.com
Nielsen c32.tex V4 - 07/23/2009 2:20pm Page 750
Part V Data Connectivity
would compare every column of the table in the DataSet with the corresponding database column
using a .NET concurrency option named
CompareAllSearchableValues. The resulting statements
were unruly, performed poorly, and made it difficult to detect and resolve collisions.
The preferred method is to use stored procedures even for the commands in the
DataAdapter.With
ADO.NET 3.5, stored procedures can be created using the wizard, although they are not particularly
good procedures. Different concurrency options other than
CompareAllSearchableValues can

also be specified. Alternatives include
CompareRowVersion, which checks for changes in the primary
key and the row version, and
OverwriteChanges, which checks only the primary key to determine
whether a collision has occurred. The options are getting better, but the preferred method is still to
write custom SQL commands for
DataAdapters and to use stored procedures, rather than embed SQL
statements in the application code.
Binding to controls
In the true spirit of reducing lines of code, the only thing necessary to bind data to a control in Visual
Studio 2008 is to drag the data component onto the control and voil
`
a! A bound data control is created.
To make things look nice, it is almost a certainty that more layout design will be needed, but that takes
us out of ADO.NET programming and into the business of form design.
Summary
This chapter compared ADO and ADO.NET and discussed the advantages and limitations of one over
the other at some length. Admittedly, the bias has been one in favor of ADO.NET. While it was perhaps
less compelling to use ADO.NET 1.x than good old ADO in some scenarios, this chapter tried to show
that, in fact, ADO.NET 3.5 offers the most complete high-level data access technology from Microsoft to
date. This chapter discussed the following:
■ The differences between ADO and ADO.NET
■ Usability features in Visual Studio 2008
■ New ADO.NET 3.5 features
■ SQL Server 2008 SQL Native Client features
With an understanding of Visual Studio 2008 and the new ADO.NET 3.5 features, you are ready for the
next chapter, which discusses the Sync Framework.
750
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 751

Sync Framework
IN THIS CHAPTER
Sync Framework example
Sync Framework overview
Sync Services for ADO.NET 2.0
T
he Microsoft Sync Framework is a robust, comprehensive yet user-friendly
synchronization platform providing synchronization capabilities between
two distinct data sources. It also enables developers to create and include
sync capabilities in their applications.
The overall goal of the Sync Framework is to enable synchronization between
two peers such as two SQL Server databases or a Microsoft SQL Server Compact
database client and another data source. It was created when Microsoft perceived
the need among developers to synchronize data sources without having to require
a DBA to create SQL Server publishers, publications, and subscriptions using
replication.
One of the components of the Sync Framework is Sync Services, which provides
a powerful yet flexible API that enables developers to create applications that pro-
vide offline and collaboration capabilities, which can enhance their applications,
as well as services or mobile devices.
Because Sync Services is part of the Microsoft Sync Framework, any database
that uses Sync Services can also participate in the exchange of information with
other data sources supported by the Sync Framework, including file systems and
Web Services or even custom data stores. While it is not integrated into SQL
Server Management Studio, the Sync Framework and Sync Services is part of the
SQL Server 2008 installation and can be used with all SQL Server versions and
editions.
This chapter begins by providing an overview of the Microsoft Sync Framework
and discussing some of its fundamentals, and then dives into the core functional-
ity of Microsoft Sync Services.

751
www.getcoolebook.com