ADO.NET Fundamentals

By Herve Roggero
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
ADO.NET Overview 5m
Setting Up Your Environment
Introduction 1m Download Database 3m Create Project 1m
Connect to A Data Source Using ADO.NET
Introduction 5m Setup and Connecting with WinForm 4m Connecting with ASP.NET 2m SqlConnectionStringBuilder 4m Change ApplicationName 3m Conclusion 0m
Retrieving Data from A Data Source
Introduction 1m Build SQL Statement 3m Implement Data Layer 7m WinForm Application 5m ASP.NET Application 3m Stored Procedure 4m Summary 0m
Inserting, Deleting, Updating Data from A Data Source
Introduction 0m Prepare ExecuteNonQuery 2m Stored Procedure 1m Inserts With ExecuteNonQuery 3m Inserts With ExecuteScalar 2m Using Output Parameter 3m Using ReturnValue Parameter 2m Deletes With ExecuteNonQuery 3m Updates With ExecuteNonQuery 4m Apply Changes In ASP.NET 3m Summary 0m
Understanding Errors with ADO.NET
Introduction 6m Catch SqlException Errors 4m Use RAISEERROR 2m Change ASP.NET Application 2m Summary 0m
Using SqlDataAdapter
Introduction 2m Load A DataTable 3m Bind A DataTable 2m Update with SqlDataAdapter 7m Change ASP.NET Application 2m Summary 0m
Client Side Performance Tracking
Introduction 2m Setup 3m RetrieveStatistics 1m Display All Statistics 5m Roundtrips 1m Summary 0m
Managing Concurrency and Transactions
Introduction 5m Concurrency with WHERE clause 5m Concurrency with SqlDataAdapter 1m Concurrency with Timestamp 5m SqlTransaction 4m TransactionScope 4m Server Transactions 3m Summary 0m
Connection Pooling
Introduction 4m SQL Profiler and Pooling 2m Performance Monitor and Pooling 2m Impact of Connection String on Pooling 3m Resolve a Connection Pooling Bug 2m Summary 0m
Using XML with ADO.NET
Introduction 2m Writing XML 7m Reading XML 5m Summary 0m
Transient Fault Handling with ADO.NET
Introduction 4m Setup 3m Demo 9m Summary 0m
Performance Considerations with ADO.NET
Introduction 4m XML Input 12m MARS 6m Summary 0m
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
Herve Roggero
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.

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT