Visual Studio 2005 has some excellent features for working with data, but there are some things you need to do before you can use them. This article, the first of two parts, explains. It is excerpted from chapter 14 of the book Murach's Visual Basic 2005, written by Anne Boehm (Murach, 2006; ISBN: 1890774383).
Before you can take advantage of Visual Studio 2005’s new features for working with data, you must create a data source for the application. As its name implies, a data source specifies the source of the data for an application. Since most applications get their data from a database, the next five figures show how to create a data source that gets data from a database.
How to use the Data Sources window
The data sources that are available to a project are listed in the Data Sources window as shown in figure 14-1. Here, the second screen shows a data source for the Products table that’s available from the MMABooks database described in the previous chapter. As you can see, this data source includes three columns from the Products table named ProductCode, Description, and UnitPrice.
If no data sources are available to a project, the Data Sources window will display an Add New Data Source link as shown in the first screen. Then, you can click on this link to start the Data Source Configuration Wizard described in figures 14-2 through 14-4. This wizard lets you add a new data source to the project. When you’re done, you can drag the data source onto a form to create bound controls as described later in this chapter.
An empty Data Sources window
A Data Sources window after a data source has been added
Description
A data source shows all the tables and columns in the dataset that are available to your application.
You can display the Data Sources window by clicking on the Data Sources tab that’s usually grouped with the Solution Explorer at the right edge of the Visual Studio window or by selecting the Show Data Sources command from the Data menu.
To create a data source, you can click the Add New Data Source link. Then, you can drag the data source to a form to create controls that are bound to the data source.
You can use the Data Source Configuration Wizard to create a data source for an application. Figure 14-2 shows the first step of this wizard. Since the steps of this wizard are mostly self-explanatory, this chapter only shows the ones that need some additional explanation. In particular, figure 14-3 shows how to create a new connection to a database, and figure 14-4 shows how to select the database objects that will be included in the dataset for the data source.
If your project doesn’t already contain a data source, you can start the Data Source Configuration Wizard by clicking the Add New Data Source link that’s in the Data Sources window. However, if your project already contains a data source, the Data Sources window will display a data source and this link won’t be available. In that case, you can start the Data Source Configuration Wizard by selecting the Add New Data Source command from the Data menu.
You can also start the Data Source Configuration Wizard by adding a SQL Server or Access database file to the project. You may want to do that if the application is for a single user. That way, the database can easily be distributed with the application as described in chapter 25.
If you add a database file to your project, you should know that by default, that file is copied to the output directory for the project every time the project is built. (The output directory is the directory where the executable file for the application is stored.) Then, when you run the application, the application works with the copy of the database file in the output directory. That means that any changes that you make to the database aren’t applied to the database file in the project directory. And each time you rebuild the application, the database in the output directory is overwritten by the unchanged database in the project directory so you’re back to the original version of the database.
If you want to change the way this works, you can select the database file in the Solution Explorer and change its “Copy to Output Directory” property from “Copy always” to “Copy if newer.” Then, the database file in the output directory won’t be overwritten unless the database file in the project directory contains more current data.
The first step of the Data Source Configuration Wizard lets you specify the source from which your application will get its data. To work with data from a database as described in this chapter, you select the Database option. However, you can also select the Web Service option to work with data from a web service that’s available from the Internet or from an intranet. Or, you can select the Object option to work with data that’s stored in business objects. This option lets you take advantage of the objects that are available from the middle layer of an application as described in chapter 17.
The first step of the Data Source Configuration Wizard
How to start the Data Source Configuration Wizard
Click on the Add New Data Source link that’s available from the Data Sources window when a project doesn’t contain any data sources.
Select the Add New Data Source command from Visual Studio’s Data menu.
Add a SQL Server (.mdf) or Access (.mdb) data file to the project using the Project->Add-> Existing Item command. Then, the wizard will skip to the step shown in figure 14-4 that lets you choose the database objects you want to include.
How to choose a data source type
To get your data from a database, select the Database option. This option lets you create applications like the ones described in this chapter.
To get your data from a web service, select the Web Service option. This option lets you browse the web to select a web service that will supply data to your application.
To get your data from a business object, select the Object option. This option lets you create applications like the ones described in chapter 17.
Description
Before you start this procedure, you need to install your database server software on your own PC or on a network server, and you need to attach your database to it. For more information, please refer to appendix A.
When you click the Next button in the first step of the wizard above, the Choose Your Data Connection step is displayed. Then, if you’ve already established a connection to a database, you can choose that connection. Otherwise, you can click the New Connection button to display the Add Connection dialog box shown in the next figure.
Figure 14-2. How to start the Data Source Configuration Wizard and choose a data source type
After you choose the data source type, the Data Source Configuration Wizard displays a dialog box that lets you choose the data connection you want to use. From this dialog box, you can select an existing connection (one you’ve previously defined), or you can click the New Connection button to display the Add Connection dialog box. This dialog box helps you identify the database that you want to access and provides the information you need to access it. That includes specifying the name of the server that contains the database, entering the information that’s required to log on to the server, and specifying the name of the database. How you do that, though, varies depending on whether you’re running SQL Server Express on your own PC or whether you’re using a database server that’s running on a network server.
If you’re using SQL Server Express on your own PC and you’ve downloaded and installed it as described in appendix A, you can use the localhost keyword to specify that the database server is running on the same PC as the application. This keyword should be followed by a backslash and the name of the database server: SqlExpress.
For the logon information, you should select the Use Windows Authentication option. Then, SQL Server Express will use the login name and password that you use to log in to Windows as the name and password for the database server too. As a result, you won’t need to provide a separate user name and password in this dialog box.
Last, you enter or select the name of the database that you want to connect to. In this figure, for example, the connection is for the MMABooks database that’s used throughout the chapters in this section of the book. When you’re done supplying the information for the connection, you can click the Test Connection button to be sure that the connection works.
In contrast, if you need to connect to a database that’s running on a database server that’s available through a network, you need to get the connection information from the network or database administrator. This information will include the name of the database server, logon information, and the name of the database. Once you establish a connection to the database, you can use that connection for all of the other applications that use that database.
By default, Visual Studio assumes you want to access a SQL Server database as shown here. This works for SQL Server 7, 2000, and 2005 databases including SQL Server Express databases. If you want to access a different type of database, though, you can click the Change button to display the Change Data Source dialog box. Then, you can select the data source and the data provider you want to use to access that data source. If you want to access an Oracle database, for example, you can select the Oracle Database item in the Data Source list. Then, you can choose the data provider for Oracle or the data provider for OLE DB from the Data Provider drop-down list.
The Add Connection and Change Data Source dialog boxes Description
Description
By default, a connection uses the SQL Server data provider. If that isn’t what you want, you can click the Change button in the Add Connection dialog box to display the Change Data Source dialog box. Then, you can choose the right data source and data provider.
To be sure that the connection is configured properly, you can click the Test Connection button in the Add Connection dialog box.
The next dialog box (not shown) asks if you want to save the connection string to the application configuration file (app.config), and we recommend that you do that. Then, if the connection string changes later on, you can change the string in the app.config file rather than in each form that uses the connection string.
Express Edition differences
The Change Data Source dialog box provides only two options: Microsoft Access Database File and Microsoft SQL Server Database File.
The Add Connection dialog box is simpler, and it includes a Database File Name text box that you use to specify the database. To do that, you click the Browse button to the right of the text box and use the resulting dialog box to point to the data file for the database.
Figure 14-3. How to create a connection to a database
Figure 14-4 shows how you can use the last step of the Data Source Configuration Wizard to choose the database objects for a data source. This step lets you choose any tables, views, stored procedures, or functions that are available from the database. In some cases, you can just select the table you need from the list of tables that are available from the database. Then, all of the columns in the table are included in the dataset.
If you want to include selected columns from a table, you can expand the node for the table and select just the columns you want. In this figure, for example, the node for the Products table has been expanded and the three columns that will be used by the Product Maintenance applications in this chapter are selected. Note that although these applications will allow data to be added to the Products table, the OnHandQuantity column can be omitted because it’s defined with a default value in the database. So when a new row is added to the database, the database will set this column to its default value.
If you include a column with a default value in a dataset, you need to realize that this value isn’t assigned to the column in the dataset, even though the dataset enforces the constraints for that column. For instance, the OnHandQuantity column in the MMABooks database has a default value of zero and doesn’t allow nulls. But if you include this column in the dataset, its definition will have a default value of null and won’t allow nulls. As a result, an exception will be thrown whenever a new row is added to the dataset with a null value for the OnHandQuantity column.
This means that either the user or the application must provide an acceptable value for the OnHandQuantity column. One way to do that is to provide a way for the user to enter a value for the column. Another way is to use the Dataset Designer to set the DefaultValue property for this column as described in this figure. You’ll learn more about working with the Dataset Designer later in this chapter.
In a larger project, you might want to include several tables in the dataset. Then, the dataset will maintain the relationships between those tables whenever that’s appropriate. Or, you might want to use views, stored procedures, or functions to work with the data in the database. If you have experience working with views, stored procedures, and functions, you shouldn’t have any trouble understanding how this works. Otherwise, you can get another book such as Murach’s SQL for SQL Server to learn more about working with these types of objects.
The last step of the Data Source Configuration Wizard
Description
In the last step of the Data Source Configuration Wizard, you can choose the database objects that you want to include in the dataset for your project.
In this step, you can choose from any tables, views, stored procedures, or functions that are available from the database. In addition, you can expand the node for any table, view, stored procedure, or function and choose just the columns you want to include in the data source.
You can also enter the name you want to use for the dataset in this dialog box. By default, the name is the name of the database appended with “DataSet”.
How to work with columns that have default values
If a column in a database has a default value, that value isn’t included in the column definition in the dataset. Because of that, you may want to omit columns with default values from the dataset unless they’re needed by the application. Then, when a row is added to the table, the default value is taken from the database.
If you include a column that’s defined with a default value, you must provide a value for that column whenever a row is added to the dataset. One way to do that is to let the user enter a value. Another way is to display the Dataset Designer as described in figure 14-21, click on the column, and use the Properties window to set the DefaultValue property.
Figure 14-4. How to choose database objects for a data source
Please check back next week for the conclusion of this article.
Visual Basic.NET 
ASP.NET Export to Excel and Word using VB.NET and C#
