Chapter 13. Databases
Most applications deal with data in some manner. Often, such data resides in a relational database such as SQL Server, Oracle, or MySQL (among many others).
This chapter covers some of the basics of database access from .NET. If you do not have a database to play around with, I recommend either SQL Server Express Edition or MySql Community Edition, either of which can be found via your favorite search engine. Both databases have components that allow you create and edit databases directly from Visual Studio.
All the examples use a database called TestDB, with a single table called Books. The first section will walk through creating this in SQL Server.
Create a New Database from Visual Studio
Solution: First, make sure you have SQL Server (the Express Edition is fine) or another database server installed. Although SQL Server will automatically integrate with Visual Studio, you may have to download additional components to allow third-party databases to work inside Visual Studio. This section will assume you are using SQL Server.
1. Open Server Explorer (Ctrl+W, L or from the View menu).
2. Right-click Data Connections and choose Create New SQL Server Database.
3. Pick your server instance from the drop-down box and select the appropriate authentication method (usually chosen when you set up the server).
4. Type in a name—for example, “TestDB”—and hit OK.
5. Expand the new database that was created for you.
6. Right-click Tables and select Add New Table.
7. Configure the table similar to what is shown in Figure 13.1.
Figure 13.1 In order to run the examples in this chapter, you will need to create a test database and table similar to the one shown here.
8. Close the window and give the table the name Books.
9. To add data, right-click the created table and select Show Table Data. You can see the values I added in Figure 13.2.
Figure 13.2 You can insert whatever data you want into the table.
Connect and Retrieve Data
Solution: Most database access is based around the following set of steps:
1. Create a connection using a connection string.
2. Create a command using SQL syntax.
3. Execute the command upon connection and possibly get data back.
We’ll see specific examples of this in the following sections. Most code samples in this chapter require the System.Data.SqlClient namespace, even though it’s not always explicitly referenced in the code samples.
Connect to SQL Server
.NET ships with a connector for SQL Server, so we’ll start with an example of that:
Connect to MySQL
Sun (who owns MySQL at the time of this writing) provides .NET connectors for accessing MySQL databases. You have two options for installation:
• Use the installer, which will put the MySql.Data.dll into the Global Assembly Cache (GAC), from which all MySQL-enabled applications can access it.
• Put the MySql.Data.dll into your project and reference it directly from the file system.
In the accompanying source code, I chose the second option. Although this is a somewhat personal choice, I feel it’s best to have as few external dependencies for the project as possible.
Once you add a reference to the component, the code is nearly identical to the SQL Server version:
Structure Your Program to Be Database-Agnostic
Solution: Notice how similar the MySQL and SQL Server code is? That’s because all data connectors in .NET derive from a common set of interfaces and classes. If there is a possibility of switching database servers, it’s a good idea to abstract out as much as possible, as in the following simplistic example.
Note
When you use the base classes for database access, you are limited to the common functionality provided by all database systems. Even then, you may run into trouble where one database doesn’t support the entire interface. Even if you make your codebase largely database-agnostic, you may still have to provide some database-specific functionality.
Note
In a real system, you will likely need to do much more work to get truly useful database-agnostic code. Databases consist of many things, such as triggers, views, stored procedures, and much more, so obtaining complete agnosticism can be quite a bit of work.
Insert Data into a Database Table
Solution: Most applications need to write some kind of data to a database as well as read it.
Note
Most SQL objects wrap external resources, thus the extensive use of using statements in database code.
Note
You should always use the SqlCommand object, with parameters, to execute your code. Using these objects will prevent dreaded SQL injection attacks. If you execute raw SQL statements, especially with user input in them, it’s an invitation for your database to be stolen or destroyed.
Delete Data from a Table
Solution: Delete is a dangerous operation, but it is sometimes necessary. Executing it is just as simple in .NET.
Note
Do you really need to delete data? Removing data from a database can be difficult, especially when you have lots of tables referring to others. You might be surprised at how often it is easier to just leave data in the database and have a field called “IsDeleted” to mark whether the record represents active data.
However, do your due diligence: Sometimes there are laws that require certain types of data to be deleted after a certain amount of time.
Run a Stored Procedure
Solution: Stores procedures are something like methods stored on the server, and .NET supports them by settings the CommandType on the SqlCommand object. For this example, I created a simple stored procedure in my SQL Server database to return a list of books sorted by publishing year.
Executing a stored procedure is very similar to executing a query, as shown here:
Use Transactions
Solution: You can create a new transaction from a connection object. You then need to include that transaction with each command you execute.
The following output demonstrates that even though the first INSERT was perfectly fine, it never gets added because the second INSERT in the same transaction failed:
Bind Data to a Control Using a DataSet
Solution: This section introduces the powerful technique of binding data to onscreen controls and allowing .NET to manage the interaction between them.
All database binding functionality relies on data sets. Technically, a data set can be any IEnumerable collection, but often you can use the DataSet class, which allows you to store relational data in memory. DataSet objects can be populated from a database, after which the connection to the database can be safely closed.
Bind Data to a Control in Windows Forms
This code assumes the form contains a single DataGridView control: See the DataBindingWinForms project for this chapter for the complete source code, including all the UI stuff I neglected to copy here.
Figure 13.3 demonstrates the DataGridView control when it’s hooked up to our sample database.
Figure 13.3 The DataGridView control is an easy way to display table data directly from the database.
Update Database from DataSet
Solution: To do this, you need to make sure the DataSet knows the syntax of an UPDATE command. Then you need to call Update() on the adapter object. This code extends the previous example by adding the UpdateCommand to the DataSet and adding a button to trigger the update.
Bind Data to a Control in WPF
WPF offers much more flexible formatting capabilities than Windows Forms, but for this example, we’ll stick to the same type of display as before. See the DatabindingWPF sample project for the complete code.
Here’s the Extensible Application Markup Language (XAML) code for the window:
Here’s the code-behind for the window:
Figure 13.4 shows the WPF version of the DataGrid with our database’s data.
Figure 13.4 In WPF, any control can have its values bound to a data source.
Bind Data to a Control in ASP.NET
Data binding is similarly easy in ASP.NET. You can rely on the GridView control.
Here’s the HTML:
And here’s the code-behind:
Figure 13.5 shows our by-now familiar grid of data, this time in a web page.
Figure 13.5 Binding to ASP.NET’s GridView control is just as easy as in Windows Forms.
Detect if Database Connection Is Available
Solution: Ideally, you would want to ensure that databases can’t disappear from the network, but sometimes you need to be extra cautious.
Here’s a simple function that tries to open a connection and returns a Boolean value depending on whether it was successful or not:
Note
Using this won’t guarantee the database is available for you—after all, the server could fail right after this method returns true. Ultimately, your code must be very robust in the face of failure. Nevertheless, I have found this code useful on a network where there was a reliably inconsistent connection to the database.
Automatically Map Data to Objects with the Entity Framework
Solution: Use the Entity Framework to automatically map database tables and their relationships to objects you can use in C# code.
The Entity Framework is Microsoft’s Object-Relational Mapping library and is fully integrated with Visual Studio.
To create an object for the Books table introduced earlier in this chapter, follow these steps:
1. Right-click your project, select Add, New Item.
2. Select ADO.NET Entity Data Model and give it the name Books.edmx. Click Add.
3. In the Entity Data Model Wizard that comes up, select “Generate From Database” and click Next.
4. Select the database. from the drop-down list.
5. For the value under “Save entity connection settings in App.Config as:” type BookEntities.
6. Click Next.
7. Check the Books table.
8. Set the Model Namespace to “BookModel” and click Finish.
This will create the Book and BookEntities object for you. The following sections will demonstrate how to use them.
Get a List of All Entities
The BookEntities represents the interface to the collection. The following code prints out all values in the table via the entity collection:
Create a New Entity
To create a new Book, just declare the object and set its properties before adding it to the entity collection. In this case, since ID is an auto-incrementing property, you don’t need to set it.
Look Up an Entity
You can use the ObjectQuery<T> class to create queries for you, as in this sample:
However, this syntax becomes awkward very quickly. Instead, you should use LINQ, which is discussed in Chapter 21. The equivalent query would looks like this:
Delete an Entity
To delete an entity, pass it to the entity list’s DeleteObject method:
