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.
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.
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.
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.
.NET ships with a connector for SQL Server, so we’ll start with an example of that:
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:
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.
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.
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.
Solution: Most applications need to write some kind of data to a database as well as read it.
Most SQL objects wrap external resources, thus the extensive use of using
statements in database code.
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.
Solution: Delete is a dangerous operation, but it is sometimes necessary. Executing it is just as simple in .NET.
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.
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:
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:
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.
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.
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.
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.
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.
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:
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.
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.
The BookEntities represents the interface to the collection. The following code prints out all values in the table via the entity collection:
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.
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:
To delete an entity, pass it to the entity list’s DeleteObject
method: