ADO.NET Fundamentals

Overview of ADO.NET including SqlConnection, SqlCommand, SqlDataAdapter.
Course info
Rating
(451)
Level
Intermediate
Updated
Oct 22, 2013
Duration
4h 6m
Table of contents
ADO.NET Overview
Setting Up Your Environment
Connect to A Data Source Using ADO.NET
Retrieving Data from A Data Source
Inserting, Deleting, Updating Data from A Data Source
Understanding Errors with ADO.NET
Using SqlDataAdapter
Client Side Performance Tracking
Managing Concurrency and Transactions
Connection Pooling
Using XML with ADO.NET
Transient Fault Handling with ADO.NET
Performance Considerations with ADO.NET
Description
Course info
Rating
(451)
Level
Intermediate
Updated
Oct 22, 2013
Duration
4h 6m
Description

Introduction to ADO.NET with an overview of many SqlClient objects, concurrency and transaction management overview, Transient Fault Handling Application Block, performance considerations and dealing with XML data.

About the author
About the author

Herve Roggero, Windows Azure MVP and co-author of PRO SQL Server 2012 Practices, is the founder of Blue Syntax Consulting (http://www.bluesyntax.net/). Herve is the president of the Azure Florida Association.

Section Introduction Transcripts
Section Introduction Transcripts

Setting Up Your Environment
This module focuses on setting up your environment for this training. We will configure a Visual Studio application and download a database for you to work with. In order to download the AdventureWorks database, you will need to follow the link at the bottom, http:/msftdbprodsamples. codeplex. com. We will review this step a little bit later. And then we will create three projects in Visual Studio: A Windows Form Application called WinDemo, an ASP. NET Web Forms Application called ASPDemo, and a Class Library, the DataLayer. The WinForm application will be used throughout the courses, and the ASP. NET web application will be used in the first half of the training. The DataLayer will be using ADO. NET and communicate to the database layer. The reason we have two projects consuming the DataLayer is that I wanted to show you how similar it was to consume data both in WinForm and the ASP. NET web applications.

Retrieving Data from A Data Source
In this module, we are going to fetch data from the database. We are going to leverage the project that we created in the previous module that is able to connect to the database. We will take a look at the SqlDataReader, which allows you to read data from a connected data source. It's a forward and read-only mechanism of fetching data, which is highly efficient. We are also going to use the SqlCommand class, which makes the actual calls to the database. SqlCommand can execute stored procedures and inline SQL statements as we're going to see in the code. We are also going to use the using command in C#, which allows you to surround your database calls and your database connections so that when the section of code is completed surrounded by the using command, all resources are freed properly against your database. We will use both inline SQL statements and stored procedures to fetch data from our database. Normally the use of inline SQL is discouraged because of security concerns. There are also performance concerns when using inline SQL, so we are going to start our application by using inline SQL for demonstration purposes, but then we will switch to using stored procedures going forward.

Inserting, Deleting, Updating Data from A Data Source
The previous module focused on how to read data from a database. In this module, we're going to take a look at how to make changes to our data source. First, we're going to take a look at how to insert data. There are many ways we can do this, and we'll take a look at as many of them as we can. We'll also do a delete and update operation using the SqlClient class. To do this, we're going to use the ExecuteNonQuery command and execute statements and stored procedures directly in our database to make data changes. We're also going to take a look at the ExecuteScalar, which returns a single value back to the client, and then we'll take a look at various ways to return data from stored procedures to our client code.