Chapter 2
Accessing Data
IN THIS CHAPTER
Understanding the System.Data namespace
Connecting to a data source
Working with data from databases
Not to predispose you to the contents of this chapter, but you'll probably find that data access is the most important part of your use of the .NET Framework. You’ll likely use the various features of the System.Data namespace more than any other namespace.
Unquestionably, one of the most common uses of Visual Studio is the creation of business applications. Business applications are about data. This is the black and white of development with Visual Studio. Understanding a little of everything is important, but complete understanding of the System.Data namespace is essential when you're building business applications.
Until the .NET Framework became popular in the 2003 time frame, most business applications built using Microsoft products used FoxPro or Visual Basic. C# has unquestionably replaced those languages as the business programmer’s language of choice over the years. You can look at the data tools in C# in three ways:
- Database connectivity: Getting information out of and into a database is a primary part of the
System.Datanamespace. Holding data in containers within your programs: The
DataSet,DataView, andDataTablecontainers are useful mechanisms for accomplishing the holding of data.
Language Integrated Query (LINQ) enables you to get the data out of the data containers using a Structured Query Language (SQL)-like methodology rather than complicated object-oriented language (OOL). LINQ isn't actually based on SQL — it’s a separate language, but if you know SQL, then you have a leg up on using LINQ.- Integration with data controls: The
System.WebandSystem.Windowsnamespaces function to integrate with the data controls. Data control integration uses database connectivity and data containers extensively. This makes data controls a great target for your reading in this chapter.
You don't have to type the source code for this chapter manually. In fact, using the downloadable source is a lot easier. You can find the source for this chapter in the CSAIO4D2EBK03CH02 folder of the downloadable source. See the Introduction for details on how to find these source files.
Getting to Know System.Data
Data in .NET is different from data in any other Microsoft platform. Microsoft has and continues to change the way data is manipulated in the .NET Framework. ADO.NET, whose implementation is contained in the System.Data library, provides the common way to think about data from a development perspective:
- Disconnected: After you get data from a data source, your program is no longer connected to that data source. You have a copy of the data. This cures one problem and causes another:
- You no longer have a row-locking problem. Because you have a copy of the data, you don't have to constrain the database from making changes.
- You have the last in wins problem. If two instances of a program get the same data, and they both update it, the last one back to the database overwrites the changes made by the first program.
- XML driven: The data copy that’s collected from the data source is XML under the hood. It might be moved around in a custom format when Microsoft deems it necessary for performance, but it is just XML either way, making movement between platforms, applications, or databases much easier.
- Database-generic containers: The containers don’t depend on the type of database at all — they can be used to store data from anywhere.
- Database-specific adapters: Connections to the database are specific to the database platform, so if you want to connect to a specific database, you need the components that work with that database.
The process for getting data has changed a little, too. You used to have a connection and a command, which returned a Recordset. Now you have an adapter, which uses a connection and a command to fill a DataSet container. What has changed is the way the user interface helps you get the job done.
System.Data has the classes to help you connect to a lot of different databases and other types of data. These classes are broken up into the namespaces in Table 2-1.
TABLE 2-1 The System.Data Namespaces
Namespace | Documentation URL | Purpose | Most Used Classes |
|---|---|---|---|
System.Data | Classes common to all of ADO.NET | The containers DataSet, DataView, DataTable, DataRow | |
System.Data.Common | Utility classes used by database-specific classes | DbCommand, DbConnection | |
System.Data.ODBC | Classes for connections to ODBC databases such as dBASE | OdbcCommand, OdbcAdapter | |
System.Data.OleDb | Classes for connections to OleDb databases such as Access | OleDbCommand, OleDbAdapter | |
System.Data.OracleClient |
| Classes for connections to Oracle | OracleCommand, OracleAdapter |
System.Data.SqlClient | Classes for connections to Microsoft SQL Server | SqlCommand, SqlDataAdapter | |
System.Data.SqlTypes | For referencing the native types common to SQL Server | SqlDateTime |
Though there is a lot to the System.Data namespace and related tools, this chapter focuses on the way Visual Studio implements these tools. In previous versions of the development software of all makes and models, the visual tools just made things harder because of the black box problem.
The black box problem is that of having a development environment do some things for you over which you have no control. Sometimes it's nice to have things done for you, but when the development environment doesn’t build them exactly how you need them, code is generated that isn’t useful.
Fortunately, that isn’t the case anymore. Visual Studio now generates completely open and sensible C# code when you use the visual data tools. You should be pleased with the results.
How the Data Classes Fit into the Framework
The data classes are all about information storage. Book 1 talks about collections, which are for storage of information while an application is running. Hashtables are another example of storing information. Collections hold lists of objects, and hashtables hold name and value pairs. The data containers hold data in larger amounts and help you manipulate that data. Here are the data containers:
DataSet: Kind of the granddaddy of them all, theDataSetcontainer is an in-memory representation of an entire database.DataTable: A single table of data stored in memory.DataSetcontainers are made up ofDataTablecontainers.DataRow: Unsurprisingly, a row in aDataTablecontainer.DataView: A copy of aDataTablethat you can use to sort and filter data for viewing purposes.DataReader: A read-only, forward-only stream of data used for one-time processes, such as filling up list boxes. Usually called a fire hose.
Getting to Your Data
Everything in the System.Data namespace revolves around getting data from a database such as Microsoft SQL Server and filling these data containers. You can get to this data manually. Generally speaking, the process goes something like this:
- You create an adapter.
- You tell the adapter how to get information from the database (the connection).
- The adapter connects to the database.
- You tell the adapter which information to get from the database (the command).
- The adapter fills the
DataSetcontainer with data. - The connection between the adapter and the database is closed.
- You now have a disconnected copy of the data in your program.
Not to put too fine a point on it, but you shouldn't have to go through that process at all. Visual Studio does a lot of the data management for you if you let it. Best practice is to use as much automation as possible.
Using the System.Data Namespace
The System.Data namespace is another namespace that gets mixed up between the code world and the visual tools world. Though it is more of a relationship between the form controls and the Data namespace, it often seems that the data lives right inside the controls, especially when you're dealing with Windows Forms.
In the following sections, you deal primarily with the visual tools, which are as much a part of the C# experience as the code. First, you discover how to connect to data sources, and then you see how to write a quick application using one of those connections. Finally, you uncover a little of the code side.
To make all this work, you need to have some kind of schema set up in your database. It can be a local project of your own creation or a sample schema. The next section tells you how.
Setting up a sample database schema
To get started, direct your browser to https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks2012. If this URL doesn’t work, search the web for SQL Server 2012 samples and find the nearest link. (If you want to use a newer version of the AdventureWorks database, you need to install a copy of SQL Server because these newer versions all rely on SQL Server backup (.bak) files. You can find these files at https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks.
Any of the sample schemas will work. If you want exactly the same one used in the examples here, choose the AdventureWorks2012 Online Transaction Processing (OLTP) Data File (adventure-works-2012-oltp-data-file.mdf). Other options may be a better fit for the work you're doing.
To install, download the MDF file and put it somewhere that makes sense to you. You’ll eventually reference it in your project, so a local location like C:Databases might be good if you have root directory access (or add the Databases folder to your user folder). If you're familiar with SQL Server, you can add a database to your local install and point to it there. In case you aren’t a DBA, you can also point a data provider directly to a file. That’s the approach used for the rest of this chapter.
Creating the data access project
Before you can do anything, you need to create your data access application. Of course, there will be a lot of configuring to do before you complete the task, but just start with the basics of the AccessData example using the following steps:
Click Create a New Project.
You see the template selection page.
Choose C#, and then Windows, and then Desktop from the three drop-down list boxes at the top.
The template list changes to show the specific templates that you selected.
Highlight the Windows Forms App (.NET Framework) option; then click Next.
The Configure Your New Project page appears.
- Type AccessData in the Project Name field.
- In the Location field, choose or type a place to put your projects (the example uses
C:CSAIO4D2EBK03CH02). Select Place Solution and Project in the Same Directory and then click Create.
Visual Studio creates the AccessData solution for you.
Connecting to a data source
There is more to connecting to a database than establishing a simple connection to a SQL Server dataset these days. C# developers have to connect to mainframes, text files, unusual databases, web services, and other programs. All these disparate systems get integrated into windows and web screens, with create, read, update, and delete (CRUD) functionality to boot.
Getting to these data sources is mostly dependent on the Adapter classes of the individualized database namespaces. Oracle has its own, as does SQL Server. Databases that are ODBC (Open Database Connectivity) compliant (such as Microsoft Access) have their own Adapter classes; the newer OLEDB (Object Linking and Embedding Database) protocol has one, too.
Fortunately, a wizard handles most of this. The Data Source Configuration Wizard is accessible from the Data Sources panel, where you spend much of your time when working with data. To get started with the Data Source Configuration Wizard, follow these steps:
Choose View ⇒ Other Windows ⇒ Data Sources, or press Shift+Alt+D.
The Data Sources panel tells you that you have no data sources.
Click the Add New Data Source link in the Data Sources panel.
You see the Data Source Configuration Wizard, shown in Figure 2-1. The wizard has a variety of data source types to choose from. The most interesting of these is the Object source, which gives you access to an object in an assembly to bind your controls to.
FIGURE 2-1: Choose a source type for the application data.
Select the Database data source type and click Next.
You see the database model selections shown in Figure 2-2 (the figure shows a bare metal setup in which you don't have any database managers, including SQL Server, installed on your system, so you may see more options). As a minimum, you have access to the Dataset model.
FIGURE 2-2: Choose a database model to use to model the data.
Select the Dataset model and click Next.
You see the option to choose a data connection, as shown in Figure 2-3. Because this is a new application, you shouldn’t see any connections.
Click the New Connection button.
Visual Studio asks you to create a new connection by using the Choose Data Source dialog box, shown in Figure 2-4. This example relies on a direct connection to a Microsoft SQL Server Database File, which is the easiest kind of connection to create. Note that you can create direct connections to Microsoft Access database files as well, and can create connections to an assortment of other databases using a database adapter.
The Data Provider field may provide more than one data provider. The wizard normally chooses the most efficient data provider for you. However, other data providers may have functionality you require for a specific application type. Always verify that you select the best data provider for your particular application needs.The steps that follow are specific to using a Microsoft SQL Server database file. Other types of data sources may require that you perform other steps to create a connection.
FIGURE 2-3: Choosing your data connection.
FIGURE 2-4: The Choose Data Source dialog box.
Select Microsoft SQL Server Database File and click Continue.
You see the Add Connection dialog box, shown in Figure 2-5.
Click Browse to display the Select SQL Server Database File dialog box, highlight the
adventure-works-2012-oltp-data-file.mdffile that you downloaded earlier, and click Open.It’s important to note that this technique only works with
.mdffiles, not the.bakfiles you find in other locations. The wizard adds the location to the Database File Name field.
FIGURE 2-5: Specify the location of the database file used for this example.
Click OK.
You may be asked by Visual Studio to upgrade the database file, which is totally fine. Simply click Yes to complete the process. After a few moments, you see the connection added to the Data Source Configuration Wizard dialog box, shown previously in Figure 2-5.
Click Next.
The wizard may ask whether you want to copy the data file to your current project. If you're working through this book in an isolated project, that’s fine. If you’re on a development effort with others, check to make sure that it’s appropriate to your life cycle methodology. For this example, click No because you’re the only one using this data source and there isn’t a good reason to create another copy. The wizard displays the connection string filename, such as adventure_works_2012_oltp_data_fileConnectionString, and asks whether you want to save it to the application.
Click Next.
You see the Choose Your Database Objects and Settings dialog box. You can choose the tables, views, or stored procedures that you want to use.
Under Tables, select Product and ProductCategory.
The Choose Your Database Objects and Settings dialog box should look similar to the one shown in Figure 2-6.
Click Finish.
You’re done! If you look at the Data Sources pane, you can see that a DataSet was added to your project with the two tables you requested.
FIGURE 2-6: Selecting data objects.
By following the preceding steps, you create two significant entities in Visual Studio:
- You create a connection to the database, shown in the Server Explorer. You find that it sticks around — it’s specific to this installation of Visual Studio.
- You also create a dataset, which is specific to this project and won’t be there if you start another project.
Both of them are important, and they provide different functionality. In this chapter, you focus on the project-specific data source displayed in the dataset.
Working with the visual tools
The Rapid Application Development (RAD) data tools for C# in Visual Studio are usable and do what you need, and they write decent code for you. Select the Data Sources panel (View ⇒ Other Windows ⇒ Data Sources) and click a table in the panel; a drop-down arrow appears, as shown in Figure 2-7. Click it, and you see something interesting: A drop-down list appears, and you can then choose how that table is integrated into Windows Forms.
FIGURE 2-7: Table Options drop-down list.
Change the Product table to Details View. It’s used to create a detail type form — one that easily enables users to view and change data. Then drag the table to the form, and Details View is created for you, as shown in Figure 2-8. (The screenshot doesn’t show the entire form because it’s too long.)
FIGURE 2-8: Creating a Parts Detail data form.
A whole lot of things happened when you dropped the table on your form:
- The fields and the field names were added.
- The fields are in the most appropriate format.
- The field name is a label.
- Visual Studio automatically adds a space where the case changes.
Note that each field gets a SmartTag that enables you to specify a query for the values in the text box. You can also preset the control that’s used by changing the values in the Data Sources pane. Five completely code-based objects are added in the Component Tray at the bottom of the page:
- DataSet:
_adventure_works_2012_oltp_data_fileDataSet - BindingSource:
productBindingSource - TableAdapter:
productTableAdapter - TableAdapterManager:
tableAdapterManager - BindingNavigator:
productBindingNavigator
The VCR Bar (technically called the BindingNavigator) is added to the top of the page. When you run the application, you can use the VCR Bar to cycle among the records in the table. Click the Start button to see the VCR Bar work. You can walk through the items in the database with no problems.
Writing data code
In most enterprise development environments, however, you won't be using the visual tools to build data access software. Generally, an infrastructure is already in place because enterprise software often has specific requirements, and the easiest way to manage those specifications is with unique and customized code. In short, some organizations don’t want things done the way Microsoft does them.
Output of the visual tools
Visual tools often aren’t used in enterprise environments because the code the tools put out is rather complicated. Double-click Form1.Designer.cs in Solution Explorer to see the code-behind for the form controls. Figure 2-9 shows what you see when you first get in there. The box marking the region near the top of the code window is marked as Windows Form Designer generated code, and you can't help but notice that the line number before that section is in the twenties and the number after that is in the seven hundreds. That’s a lot of generated code.
FIGURE 2-9: Generated code. Huh?
Nothing is wrong with this code, but it is purposely generic to support anything that anyone might want to do with it. Enterprise customers often want to make sure that everything is done the same way. For this reason, they often define a specific data code format and expect their software developers to use that, rather than the visual tools.
Basic data code
The code of the sample project is simple (you don’t type this code, it was automatically generated for you):
using System;
using System.Windows.Forms;
namespace AccessData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void productBindingNavigatorSaveItem:Click(object sender,
EventArgs e)
{
this.Validate();
this.productBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(
this._adventure_works_2012_oltp_data_fileDataSet);
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the
// '_adventure_works_2012_oltp_data_fileDataSet.Product'
// table. You can move, or remove it, as needed.
this.productTableAdapter.Fill(
this._adventure_works_2012_oltp_data_fileDataSet.Product);
}
}
}
Although this code is fairly straightforward, it obviously isn’t everything that you need. The rest of the code is in the file that generates the visual form itself, supporting the visual components.
The time may come when you want to connect to a database without using visual tools. The “How the Data Classes Fit into the Framework” section, earlier in this chapter, discusses the required steps and here is the code to go with it:
1. SqlConnection mainConnection = new SqlConnection();
2. mainConnection.ConnectionString = "server=(local);database=Assets_Maintenance;Trusted_Connection=True"
3. SqlDataAdapter partsAdapter = new SqlDataAdapter("SELECT * FROM Parts", mainConnection)
4. DataSet partsDataSet = new DataSet();
5. mainConnection.Open();
6. partsAdapter.Fill(partsDataSet);
7. mainConnection.Close();
This approach becomes useful especially when you want to build a web service or a class library — though you should note that you can still use the visual tools in those project types. The following paragraphs discuss this code a line at a time.
Line 1 sets up a new data connection, and line 2 populates it with the connection string. You can get this from your Database Administrator (DBA) or from the Properties panel for the data connection.
Line 3 has a SQL query in it. A Stored Procedure is a database artifact that allows you to use a parameterized query from ADO.NET, rather than dynamically generated SQL Strings. Don’t use inline SQL for production systems.
Line 4 builds a new dataset. This is where the schema of the returned data is held and what you use to navigate the data.
Lines 5, 6, and 7 perform the magic: Open the connection, contact the database, fill the dataset using the adapter, and then close the database. It’s all straightforward in this simple example. More complex examples make for more complex code.
After running this code, you would have the Products table in a DataSet container, just as you did in the visual tools in the earlier section, “How the Data Classes Fit into the Framework.” To access the information, you set the value of a text box to the value of a cell in the DataSet container, like this:
TextBox1.Text = myDataSet.Tables[0].Rows[0]["name"]
To change to the next record, you need to write code that changes the Rows[0] to Rows[1]. As you can see, it would be a fair amount of code. That’s why few people use the basic data code to get the databases. Either you use the visual tools or you use an Object Relationship Model of some sort, such as Entity Framework.