Sams Teach Yourself C# 5.0 in 24 Hours (2013)

Part III: Working with Data

Hour 16. Working with Databases

What You’ll Learn in This Hour

• Prerequisites

• Understanding ADO.NET

• Understanding LINQ to ADO.NET

Interacting with a database is something that many applications need to do. A database is simply a repository of data, much like a text file or an XML file. As you might imagine, using files in this manner to store data isn’t always the best approach. As a result, most applications make use of a relational database, which organizes data into tables, rows, and records. Each table is organized into rows, and each row represents a single record. Rows are further organized into columns, and each row in a table has the same column structure.

The .NET Framework provides a rich set of classes for database interaction through ADO.NET, which is a library of objects specifically designed to simplify writing applications that use databases, and LINQ to ADO.NET.

In this hour, you briefly learn ADO.NET and LINQ to ADO.NET.

Prerequisites

All the examples and exercises in this hour require you to have SQL Server or SQL Server Express (2005 or higher) installed. If you don’t already have SQL Server or SQL Server Express installed, you can download it from http://www.microsoft.com/sqlserver.

You will also need to install the AdventureWorks sample database. These examples use the AdventureWorksLT 2012 version of the OLTP database, which can be directly downloaded from http://msftdbprodsamples.codeplex.com/downloads/get/354847. Once the data file has been downloaded, you will need to attach the database by following these steps:

1. Display the Database Explorer window, shown in Figure 16.1. This is available through the VIEW|Other Windows menu.

Figure 16.1. Database Explorer.

2. Add a new Data Connection by right-clicking Data Connections and selecting Add Connection.

3. When the Choose Data Source dialog box, shown in Figure 16.2, displays, select Microsoft SQL Server Database File as the data source.

Figure 16.2. Choose Data Source dialog box.

4. When you click Continue, the Add Connection dialog box is displayed, as shown in Figure 16.3. Browse to the AdventureWorksLT2012_Data file you previously downloaded and click the OK button.

Figure 16.3. Add Connection dialog box.

Note: Upgrading the Database File

At this point, you may see a dialog box, similar to the one shown in Figure 16.4, letting you know that the database file is not compatible with the current instance of SQL Server. If that’s the case, go ahead and click Yes to upgrade the file.

Figure 16.4. Database conversion required dialog box.

5. Expand the connection that was just added to view the available tables, as shown in Figure 16.5.

Figure 16.5. An expanded data connection.

6. At this point, the database name is AdventureWorksLT2012_Data.mdf. You should rename it to be just AdventureWorksLT2012 by right-clicking the database and selecting the Rename menu command, as shown in Figure 16.6.

Figure 16.6. Renaming the database.

Understanding ADO.NET

The ADO.NET library is a rich framework enabling you to build applications that can retrieve and update information in relational databases. In keeping with the ideals of language and platform independence, ADO.NET is built upon the idea of data providers. Each database system that ADO.NET supports (such as SQL Server, Oracle, and DB2) has a data provider that implements the mechanisms for connecting to a database, executing queries, and updating data. Because each provider implements the same interfaces, you can write code that is independent of the underlying database.

One of the primary classes in ADO.NET is the DataSet, which represents a portion of the database. A DataSet does not require a continuous connection to the database, enabling you to work in a disconnected manner. To update the database and the DataSet, you periodically reconnect theDataSet to its parent database and perform any updates required.

Note: ADO.NET Data Providers

An ADO.NET data provider encapsulates the logic needed for connecting to a database, executing commands and retrieving results. It provides a lightweight layer between the underlying data source and your application code.

The .NET Framework currently ships with five data providers, each in their own namespace:

• Data Provider for SQL Server (System.Data.SqlClient)

• Data Provider for OLE DB (System.Data.OleDb)

• Data Provider for ODBC (System.Data.Odbc)

• Data Provider for Oracle (System.Data.OracleClient)

• EntityClient Provider (System.Data.EntityClient)

Even though SQL Server and Oracle both support OLE DB, you should use the specific SQL Server or Oracle providers because they are optimized for those database systems.

To accomplish all this, the DataSet must represent database tables and the relationships between those tables. The collection of database tables is available through the Tables property, with each table represented as a DataTable instance. The Relations property is a collection of DataRelationinstances, with each DataRelation representing a relationship between two tables.

A DataTable is most commonly created as a result of a query against the database and represents an actual table in that database. Each column in the database table is represented as a DataColumn, exposed through the Columns property of DataTable. The DataTable also contains a Rows property, which returns a DataRow object for each actual row of the database table.

If you think of a DataSet as an abstraction of the database, you need a way to bridge the gap between the DataSet and the underlying database. This is done with a DataAdapter, which is used to exchange data between a data source and a DataSet, primarily through the Fill and Update methods. The benefit this provides is that a DataSet can represent tables from multiple databases.

Note: Read-Only Access Using a Data Reader

Both a DataSet and a DataAdapter enable two-way interaction with the database, allowing you to both read and write data. If you only need to read data, which is common, you can create a DbDataReader, or one of its derived classes like SqlDataReader, instead. The simplest way to create a DataReader is to use the ExecuteReader method available from DbCommand.

A DbDataReader provides connected, forward-only, read-only access to a collection of tables, often referred to as a fire hose cursor. (The name “fire hose cursor” is due to the similarities of accessing data in a fast, forward-only, continuous stream of data and the fast, forward-only, continuous stream of water through a fire hose.)

As a result, they are lightweight objects and are ideally suited for filling controls with data and then breaking the database connection.

Finally, you need to represent a connection to the data source through a DbConnection and a database command (such as a SQL statement or stored procedure) through a DbCommand. After a DbConnection is created, it can be shared with many different DbCommand instances.

Each data provider typically provides customized subclasses of DbConnection and DbCommand specific to that database system. For example, the Data Provider for SQL Server provides the SqlConnection and SqlCommand classes.

Caution: Required References

To use ADO.NET, you need to ensure that the project has a reference to the System.Data assembly and that the correct namespace for the data provider you use is included.

Listing 16.1 shows how to execute a query using ADO.NET and the classes provided by the Data Provider for SQL Server.

Listing 16.1. A Simple ADO.NET Query

using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = @"Integrated Security=SSPI;
database=AdventureWorksLT2012;server=(local)\SQLEXPRESS";

try
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = @"SELECT *
FROM [AdventureWorksLT2012].[SalesLT].[Customer]
WHERE [CompanyName] = 'A Bike Store'";

connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// Retrieve the second, third, and fifth columns
// explicitly as a string value.
Console.WriteLine("{0} {1} {2}",
reader.GetString(2),
reader.GetString(3),
reader.GetString(5));
}

connection.Close();
}
}
catch (SqlException e)
{
Console.WriteLine("An error occurred: {0}", e.Message);
}
}

To ensure that the database command and connection instances are disposed of properly and that the connection is closed, it is best to place them in a using statement.

Note: Connection Pooling

Most of the data providers also provide support for connection pooling. You can think of the connection pool as a set of available database connections. When an application requires a connection, the provider extracts the next available connection from the pool. When the application closes the connection, it is returned to the pool, ready for the next application that needs a connection.

As a result, you should not keep a connection longer than you need to. Instead, open a connection when you need it and then close it as soon as you finish using it.

Try It Yourself: Working with ADO.NET

To use the ADO.NET Data Provider for SQL Server to insert a record and then query a table, use the following steps:

1. Create a new console application.

2. Include the System.Data.SqlClient namespace with a using directive.

3. In the Main method of the Program.cs file, implement the code shown in Listing 16.1.

4. Just before the second using statement (that declares a SqlCommand instance), write a using statement that creates a SqlCommand to insert a record into the database. Instead of calling the ExecuteReader method on command, use the ExecuteNonQuery method because the SQL statement does not return any data. Use the following text for the CommandText property:

@"INSERT INTO [AdventureWorksLT2012].[SalesLT].[Customer]
(NameStyle, Title, FirstName, LastName, CompanyName, PasswordHash,
PasswordSalt, ModifiedDate)
VALUES (0, 'Mr.', 'Scott', 'Dorman', 'A Bike Store', 'aaaaa', 'aaa',
'" + DateTime.Now.ToString("G") + "')";

5. Run the application using Ctrl+F5. The output should look like what is shown in Figure 16.7.

Figure 16.7. Results of working with ADO.NET.

Caution: SQL Injection Attacks

The code you wrote in the “Working with ADO.NET” Try It Yourself explicitly passed text values to the insert statement. In production quality code, you should avoid this whenever possible because it introduces the possibility of someone injecting malicious SQL text as the values. Instead, you should use parameters, as shown in the following:

command.CommandText = @"INSERT INTO [AdventureWorksLT2012].[SalesLT].[Customer]
(NameStyle, Title, FirstName, LastName, CompanyName, PasswordHash,
PasswordSalt, ModifiedDate)
VALUES (@NameStyle, @Title, @FirstName, @LastName, @CompanyName, @PasswordHash,
@PasswordSalt, @ModifiedDate)";

command.Parameters.Add(new SqlParameter("@NameStyle", 0));
command.Parameters.Add(new SqlParameter("@Title", "Mr"));
command.Parameters.Add(new SqlParameter("@FirstName", "Scott"));
command.Parameters.Add(new SqlParameter("@LastName", "Dorman"));
command.Parameters.Add(new SqlParameter("@CompanyName", "A Bike Store"));
command.Parameters.Add(new SqlParameter("@PasswordHash", "aaaaa"));
command.Parameters.Add(new SqlParameter("@PasswordSalt", "aaa"));
command.Parameters.Add(new SqlParameter("@ModifiedDate",
DateTime.Now.ToString("G")));

Understanding LINQ to ADO.NET

LINQ to ADO.NET is actually three separate technologies that enable you to interact with relational databases. Figure 16.8 shows how the LINQ to ADO.NET technologies relate to each other, the other LINQ-enabled data sources, and the higher-level programming languages such as C#.

Figure 16.8. LINQ to ADO.NET.

Working with LINQ to DataSet

LINQ to DataSet builds upon the existing ADO.NET architecture and enables you to build queries over data stored in a DataSet easily and quickly. It is not meant to replace ADO.NET in an application but to enable you to write queries using the LINQ syntax and fills the void left by the limited query capabilities of the DataSet.

To use LINQ to DataSet, you must first populate the DataSet. After data has been loaded, you can begin querying it using the same techniques you have already learned with LINQ to Objects (Hour 13, “Understanding Query Expressions”) and LINQ to XML (Hour 15, “Working with XML”). These queries can be performed against a single table in the DataSet or against multiple tables using the Join and GroupJoin query operators.

Listing 16.2 is functionally equivalent to the query shown in Listing 16.1 using a LINQ to DataSet query instead. This code first creates a SqlDataAdapter and then fills the DataSet instance with data. This code is necessary with both ADO.NET and LINQ to DataSet. However, by using theAsEnumerable extension method on the DataTable, you can execute standard LINQ queries against the data.

Listing 16.2. A Simple LINQ to DataSet Query

string connectionString = @"Integrated Security=SSPI;
database= AdventureWorksLT2012;server=(local)\SQLEXPRESS";

string selectSQL = @"SELECT * FROM [AdventureWorksLT2012].[SalesLT].[Customer]";

SqlDataAdapter adapter = new SqlDataAdapter(selectSQL, connectionString);
DataSet ds = new DataSet();
adapter.Fill(ds);
foreach (var customer in ds.Tables[0].AsEnumerable().
Where(row => row.Field<string>("CompanyName") == "A Bike Store"))
{
Console.WriteLine("{0} {1} {2}",
customer.Field<string>("Title"),
customer.Field<string>("FirstName"),
customer.Field<string>("LastName"));
}

Just as LINQ to XML added XML-specific extensions, LINQ to DataSet also adds several DataSet specific extensions. These extensions make it easier to query over a set of DataRow objects, enabling you to compare sequences of rows or directly access the column values of a DataRow.

Caution: Required References

To use LINQ to DataSet, you need to ensure that the project has references to the following assemblies:

• System.Core

• System.Data

• System.Data.DataSetExtensions

• System.Data.Common or System.Data.SqlClient, depending on how you connect to the database

You also need to include the System.Linq and System.Data namespaces.

Working with LINQ to SQL

LINQ to SQL enables you to write queries directly against SQL databases using the same query syntax you use for an in-memory collection or any other LINQ data source. Although ADO.NET maps the database to a conceptual data model, represented by a DataSet, LINQ to SQL maps directly to an object model in your application code. When your application executes, the LINQ syntax queries are translated by LINQ to SQL into SQL language queries that are then sent to the database to be executed. When the results are returned, LINQ to SQL translates them back to data model objects.

To use LINQ to SQL, you must first create the object model that represents the database. There are two ways to do this, as follows:

• The Object Relational Designer (O/R Designer), which is part of Visual Studio 2012, provides a rich user interface for creating the object model from an existing database. The O/R Designer supports only SQL Server Express or SQL Server 2000 (and higher) databases.

• The SQLMetal command-line tool, which should be used for large databases, or databases that the O/R Designer does not support.

When you decide how you will generate the object model, you need to decide what type of code you want to generate. Using the O/R Designer, you can generate C# source code that provides attribute-based mapping. If you use the SQLMetal command-line tool, you can also generate an external XML file containing the mapping metadata.

Note: Creating the Object Model

There is actually a third way to create the object model, in which you use the code editor to write the object model by hand. This is not recommended because it can be error-prone, especially when creating the object model to represent an existing database.

You can use the code editor to modify or refine the code generated by the Object Relational Designer or the SQLMetal command-line tool.

When you have the object model created, you can then use it in your applications by writing LINQ queries. The code in Listing 16.3 shows a complete example from defining the DataContext to executing the query and observing the results, and produces the same result as the code shown inListing 16.1 and Listing 16.2.

Listing 16.3. A Simple LINQ to SQL Query

DataContext dataContext = new DataContext(@"Integrated Security=SSPI;
database=AdventureWorksLT2012;server=(local)\SQLEXPRESS");

Table<Customer> customers = dataContext.GetTable<Customer>();
IQueryable<Customer> query =
from customer in customers
where customer.CompanyName == "A Bike Store"
select customer;

foreach(Customer customer in query)
{
Console.WriteLine("{0} {1} {2}",
customer.Title,
customer.FirstName,
customer.LastName);
}

Caution: Required Namespaces

To use LINQ to SQL, you need to include the System.Linq and System.Data.Linq namespaces.

You must first establish the connection between your object model and the database, done through a DataContext. You then create a Table<T> class that acts as the table you will query. Although not actually the case, you can think of the DataContext as being similar to a DBConnection andTable<T> as being similar to a DataTable. You then define and subsequently execute your query.

Try It Yourself: Working with LINQ to SQL

By following these steps, you use the O/R Designer and LINQ to SQL to insert a record and then query a table:

1. Create a new console application.

2. Include the System.Data.Linq namespace with a using directive.

3. Use the O/R Designer to create the object model. To do this, add a new item to your project by selecting LINQ to SQL Classes, as shown in Figure 16.9. Name the file AdventureWorksLT.

Figure 16.9. Add New Item dialog box.

4. In the resulting editor, a portion of which is shown in Figure 16.10, click the Database Explorer link to display the Visual Studio Database Explorer tool window.

Figure 16.10. O/R Designer.

5. Expand the AdventureWorksLT2012 connection to view the available tables, select the Customer table and drag it on to the AdventureWorksLT.dbml editor window, as shown in Figure 16.11. This creates the entity class that represents the Customer table.

Figure 16.11. O/R Designer displaying a table.

Note: Copying the Database File to Your Project

If you see a dialog box, similar to the one shown in Figure 16.12, saying that the connection is using a local data file that isn’t in the project and asking if you want to copy the file to your project, be sure to say No.

Figure 16.12. Copy database file to your project dialog box.

6. In the Main method of the Program.cs file, implement the code shown in Listing 16.3.

7. Run the application using Ctrl+F5. The output should look like what is shown in Figure 16.13.

Figure 16.13. Results of running a query.

Selecting data, also known as projection, is accomplished simply by writing a LINQ query and then executing it. LINQ to SQL also enables you to add, modify, or delete data in the database. Whenever you make changes using LINQ to SQL, you are modifying only the local cache. The changes you have made are not sent to the database until you call the SubmitChanges method on the DataContext instance.

To add a new record to the database, you simply create a new instance of the appropriate data model object, calling the InsertOnSubmit method of the Table<T> instance. The code in Listing 16.4 shows an example of adding a new Customer record to the Customers table, and is equivalent to the code you wrote earlier to insert a new record using ADO.NET.

Listing 16.4. Adding a New Record

DataContext dataContext = new DataContext(@"Integrated Security=SSPI;
database= AdventureWorksLT2012;server=(local)\SQLEXPRESS");

Table<Customer> customers = dataContext.GetTable<Customer>();

Customer customer = new Customer();
customer.NameStyle = true;
customer.Title = "Mr."
customer.FirstName = "Scott";
customer.LastName = "Dorman";
customer.PasswordHash = "aaaaa";
customer.PasswordSalt = "aaaa";
customer.ModifiedDate = DateTime.Now;
customers.InsertOnSubmit(customer);

dataContext.SubmitChanges();

Updating an existing entity simply requires you to retrieve the specific object from the database using a LINQ to SQL query and then modifying its properties, as shown in Listing 16.5.

Listing 16.5. Updating a Record

DataContext dataContext = new DataContext(@"Integrated Security=SSPI;
database= AdventureWorksLT2012;server=(local)\SQLEXPRESS");

Table<Customer> customers = dataContext.GetTable<Customer>();

Customer customer =
(from customer in customers
where customer.LastName == "Dorman"
select customer).First();

customer.Title = "Mr.";

dataContext.SubmitChanges();

Deleting an item is also just as simple because you remove the item from its containing collection using the DeleteOnSubmit method and then call SubmitChanges. Listing 16.6 shows an example of deleting a record from the database.

Listing 16.6. Deleting a Record

DataContext dataContext = new DataContext(@"Integrated Security=SSPI;
database= AdventureWorksLT2012;server=(local)\SQLEXPRESS");

Table<Customer> customers = dataContext.GetTable<Customer>();

IQueryable<Customer> query =
from customer in customers
where customer.LastName == "Dorman"
select customer;

if (query.Count() > 0)
{
customers.DeleteOnSubmit(query.First());
}

dataContext.SubmitChanges();

Defining a Custom DataContext

In all the preceding examples, you used the DataContext class provided by LINQ to SQL. The GetTable method enables you to instantiate any entity class even if that table is not contained in the underlying database the DataContext is connected to. It is only while your program is executing that you can discover this problem. As a result, it is recommended that you create specialized DataContext classes specific to the database you will use.

To create a derived DataContext class, you simply inherit from DataContext and then provide the various Table<T> collections as public members. Listing 16.7 shows a strongly typed DataContext class for the AdventureWorksLT database and how it would be used in a simple query.

Listing 16.7. A Custom DataContext

public partial class AdventureWorksLT : DataContext
{
public Table<Customer> Customers;
public Table<Order> Orders;

public AdventureWorksLT (string connection) : base(connection) { }
}

AdventureWorksLT dataContext = new AdventureWorksLT(@"Integrated Security=SSPI;
database= AdventureWorksLT2012;server=(local)\SQLEXPRESS");

IQueryable<Customer> query =
from customer in dataContext.Customers
customer.CompanyName == "A Bike Store"
select customer;

foreach(Customer customer in query)
{
Console.WriteLine("{0} {1} {2}",
customer.Title,
customer.FirstName,
customer.LastName);
}

Working with LINQ to Entities

Earlier you learned about ADO.NET and how LINQ to SQL can be used to develop applications that interact with databases. The Entity Framework extends the basic capabilities of ADO.NET to support developing data-centric applications.

Traditionally, when developing data-centric applications, not only must you model the database tables, relations, and business logic, but also you must work directly with the database to store and retrieve data. Although LINQ to SQL helps solve some of these problems, the Entity Framework enables you to work at a higher level of abstraction by creating a domain model that defines the entities and relationship being modeled.

The Entity Framework then translates this domain model, commonly called a conceptual model, to data source-specific commands. This approach enables you to develop your application without being concerned about dependencies on a particular data source.

When the domain model has been defined, the Entity Framework then maps relational tables, columns, and foreign key constraints to classes in the logical model. This approach enables a great deal of flexibility to define and optimize the logical model. The classes generated are partial classes, enabling you to extend them easily.

To access or modify data through the Entity Framework, you execute queries against the conceptual model to return objects through one of the following methods:

• LINQ to Entities provides LINQ support for querying directly against the conceptual model. Both the Entity Framework and LINQ can then use the objects returned from a LINQ to Entities query.

• Entity SQL, which is a data source-independent version of SQL that works directly with the conceptual model.

• Query builder methods, which enable you to create Entity SQL queries using a similar syntax to the LINQ extension methods.

To make creating entity models easier, Visual Studio 2012 includes the Entity Data Model Designer, shown in Figure 16.14.

Figure 16.14. Entity data model designer. (Image not meant to be read in detail.)

Note: EntityClient Data Provider

Because the Entity Framework is built on top of the traditional ADO.NET services, it includes the EntityClient data provider that is responsible for translating the entity queries into their data source-specific versions and translating entity data into their object equivalents.

It is also possible to use the EntityClient provider like any other ADO.NET data provider, enabling you to execute Entity SQL queries that return the data using a data reader.

This designer enables you to visually create and modify the entities, mappings, associations, and inheritance of your models.

It is divided into the following components:

• The visual design surface

• The mapping details window, which enables you to view and edit mappings

• The model browser window, which displays the conceptual and logical models in a tree view

The easiest way to get started using the Entity Framework is to add a new ADO.NET Entity Data Model item to your project. This starts the Entity Data Model Wizard, which enables you to generate an entity data model from an existing database or by creating an empty data model.

When the data model has been created, you can then easily perform queries against the data context using LINQ to Entities, as shown in Listing 16.8. This query selects the first 10 records from the Products table and displays the product name. The Entity Data Model Wizard created theAdventureWorksLT2012_DataEntities and Product classes.

Listing 16.8. A Custom DataContext

using (AdventureWorksLT2012_DataEntities context =
new AdventureWorksLT2012_DataEntities())
{
IQueryable<Product> productsQuery = (from product in context.Products
select product).Take(10);

Console.WriteLine("Product Names:");
foreach (var prod in productsQuery)
{
Console.WriteLine(prod.Name);
}
}

The results of running this query are shown in Figure 16.15.

Figure 16.15. Selecting the top 10 products.

Summary

In this hour, you learned how ADO.NET and the LINQ to ADO.NET technologies make it easy to work with databases. You learned the basics of working with ADO.NET and then built upon that to see how LINQ to DataSets enable you to add complex queries over the data in a DataSet. You then learned how LINQ to SQL enables you to create an object model that represents your database, enabling you to easily query, add, update, and remove data from the database. Finally, you saw how easy it is to create custom data contexts for use with LINQ to SQL.

Q&A

Q. What is a relational database?

A. A relational database is simply a repository of data that is organized into tables, rows, and records.

Q. What is ADO.NET?

A. The ADO.NET library is a rich framework enabling you to easily build applications that can retrieve and update information in relational databases.

Q. Does ADO.NET require a constant connection to the database?

A. No, most of the ADO.NET classes are designed so that they do not require a continuous connection to the database, enabling you to work in a disconnected manner. The DataReader class and any derived classes do require a constant connection to the database.

Q. What is LINQ to ADO.NET?

A. LINQ to ADO.NET is actually three separate technologies that enable you to interact with relational databases: LINQ to DataSet, LINQ to SQL, and LINQ to Entities.

Q. What is a LINQ to SQL object model?

A. A LINQ to SQL object model directly represents, or maps, objects in your application to objects (tables) in the underlying database.

Q. Can LINQ to SQL be used to update data?

A. Yes, LINQ to SQL can be used to update data. It can also be used to add new data or delete existing data.

Workshop

Quiz

1. What are the ADO.NET data providers that ship with the .NET Framework?

2. What references are required to use LINQ to DataSet?

3. What references are required to use LINQ to SQL?

4. What is the benefit to creating a custom DataContext class?

Answers

1. The .NET Framework currently ships with five data providers, which support SQL Server, Oracle, any OLE DB-compliant database, any ODBC-compliant database, and the Entity Data Model (which is part of the Entity Framework).

2. LINQ to DataSet requires references to the following assemblies:

• System.Core

• System.Data

• System.Data.DataSetExtensions

• System.Data.Common or System.Data.SqlClient

3. LINQ to DataSet requires references to the following assemblies:

• System.Core

• System.Data.Linq

4. Creating a custom DataContext helps to ensure that you can only access tables defined by the underlying database by providing the various Table<T> collections as public members.

Exercises

There are no exercises for this hour.