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.
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.