Automatically Connecting to Data


Visual Studio 2005 provides new tools that make getting started with databases easier than ever. Although the process is relatively straightforward, it does involve a lot of steps. The steps also allow several variations, so describing every possible way to build a database connection takes a long time. To make the process more manageable, the following two sections group the steps in two pieces: connecting to the data source and adding data controls to the form.

Connecting to the Data Source

To build a simple database program, start a new application and select the Data menu’s Add New Data Source command to display the Data Source Configuration Wizard shown in Figure 11-2.

image from book
Figure 11-2: The Data Source Configuration Wizard lets you add or configure data sources.

Visual Studio 2005 allows you to use databases, web services, and objects in your application. The most straightforward choice is Database.

Select the type of data source you want add and click Next to select a data connection on the page shown in Figure 11-3.

image from book
Figure 11-3: Pick the data connection or click New Connection to create a new one.

If you have previously created data connections, you can select one from the drop-down list. If you have not created any data connections, click the New Connection button to open the dialog box shown in Figure 11-4.

image from book
Figure 11-4: Use the Add Connection dialog box to create a data connection.

If you don’t like the default data source type selected by the dialog box (in this example, Microsoft Access Database File), click the Change button to display the dialog box shown in Figure 11-5. This dialog box lets you select different kinds of databases such as Microsoft Access, ODBC data sources, SQL Server databases, and Oracle databases.

image from book
Figure 11-5: Use the Change Data Source dialog box to select a new kind of database.

After you have selected the database type, close the Change Data Source dialog box and return to the Add Connection dialog box. Depending on the type of database you selected in Figure 11-5, this dialog box may no longer look like Figure 11-4. For example, Figure 11-6 shows what the Add Connection dialog box looks like if you select an SQL Server database.

image from book
Figure 11-6: The Add Connection dialog box looks like this for SQL Server databases.

Enter the necessary data in the Add Connection dialog box. For a SQL Server database, select the server name, authentication method, database name, and other information.

For a Microsoft Access database, enter the file name or click the Browse button shown in Figure 11-4 and find the database file. Enter a user name and password if necessary and click OK.

When you return to the Data Source Configuration Wizard, select the new connection as shown in Figure 11-7. When you click the plus sign next to the Connection string label, the wizard shows the connection information it will use to connect the data source to the database.

image from book
Figure 11-7: Select the new connection in the Data Source Configuration Wizard.

When you click Next, the wizard tells you that you have selected a local database file that is not part of the project and it asks if you want to add it to the project. If you click Yes, the wizard adds the database to the project so it shows up in Project Explorer. If you plan to distribute the database with the application, you may want to do this to make it easier to manage the database and the Visual Basic source code together.

Next the wizard displays the dialog box shown in Figure 11-8, asking whether you want to save the connection string in the project’s configuration file. If you leave this check box selected, the wizard adds the configuration string to the project’s app.config file.

image from book
Figure 11-8: Decide whether you want to include the connection string in the configuration file.

The following shows the part of the configuration file containing the connection string:

  <connectionStrings>     <add name="StudentTest.My.MySettings.ClassRecordsConnectionString"         connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\ClassRecords.mdb; Persist Security Info=True;Jet OLEDB:Database Password=MyPassword"         providerName="System.Data.OleDb" /> </connectionStrings>  

Later, the program uses the Settings.Default.ClassRecordsConnectionString values to get this value and connect to the database. You can easily make the program connect to another data source by changing this configuration setting and then restarting the application.

Tip 

You should never save database passwords in the configuration file. The file is stored in plain text and anyone can read it. If you need to use a password, store a connection string that contains a placeholder for the real password. Then at runtime, load the connection string and replace the placeholder with a real password entered by the user.

Adding Data Controls to the Form

At this point you have defined the basic connection to the database. Visual Studo knows where the database is and how to build an appropriate connection string to open it. Now you must decide what data to pull out of the database and how to display it on the form.

Click Next to display the dialog box shown in Figure 11-9. This page shows the objects available in the database. In this example, the database contains two tables named Students and TestScores. By clicking the plus signs next to the objects, you can expand them to see what they contain. In Figure 11-9, the tables are expanded so you can see the fields they contain.

image from book
Figure 11-9: Select the database objects that you want included in the data source.

Select the database objects that you want to include in the data source. In Figure 11-9, both of the tables are selected.

When you click Finish, the wizard adds a couple objects to the application. The Solution Explorer shown in Figure 11-10 lists the new file ClassRecordsDataSet.xsd. This is a schema definition file that describes the data source.

image from book
Figure 11-10: The Data Source Configuration Wizard adds a database schema to Solution Explorer.

When you double-click the schema file, Visual Basic opens it in the editor shown in Figure 11-11. This display shows the tables defined by the schema and their fields.

image from book
Figure 11-11: The Schema Editor shows the tables defined by the schema and their relationships.

The line between the files with the little key on the left and the infinity symbol on the right indicates that the tables are joined by a one-to-many relationship. In this example, the Students.StudentId field and TestScores.StudentId field form a foreign key relationship. That means every StudentId value in the TestScores table must correspond to some StudentId value in the Students table. When you double-click the relationship link or right-click it and select Edit Relation, the editor displays the dialog box shown in Figure 11-12.

image from book
Figure 11-12: Use this dialog box to edit relationships among data source tables.

At the bottom of the tables shown in Figure 11-11, you can see two data adapter objects containing the labels Fill,GetData(). These represent data adapter objects that the program will later use to move data from and to the data source.

In addition to adding the schema file to Solution Explorer, the wizard also added a new DataSet object to the Data Sources window shown in Figure 11-13. (If this window is not visible, select the Data menu’s Show Data Sources command.)

image from book
Figure 11-13: The Data Sources window lists the new data source.

You can use the plus signs next to expand the objects contained within the DataSet. In Figure 11-13, the DataSet is expanded to show its tables, and the tables are expanded to show their fields. Notice that the TestScores table is listed below the Students table because it has a parent/child relationship with that table.

It takes a lot of words and pictures to describe this process, but using the wizard to build the data source is actually quite fast. After you have created the data source, you can build a simple user interface with no extra work. Simply drag objects from the Data Sources window onto the form.

When you click and drag a table from the Data Sources window onto the form, Visual Basic automatically creates BindingNavigator and DataGridView controls, and other components to display the data from the table. Figure 11-14 shows the result at runtime.

image from book
Figure 11-14: Drag and drop a table from the Data Sources window onto the form to create a simple DataGridView.

Instead of dragging an entire table onto the form, you can drag individual database columns. In that case, Visual Basic adds controls to the form to represent the column. Figure 11-15 shows the columns from the Students table dragged onto a form.

image from book
Figure 11-15: Drag and drop table columns onto a form to create a record-oriented view instead of a grid.

If you select a table in the Data Sources window, a drop-down arrow appears on the right. Open the drop-down to give the table a different display style, as shown in Figure 11-16. For example, if you set a table’s style to Details and drag the table onto a form, Visual Basic displays the table’s data using a record detail view similar to the one shown in Figure 11-15 instead of the grid shown in Figure 11-14.

image from book
Figure 11-16: Use the drop-down in the Data Sources window to give a table a different display style.

Similarly, you can change the display styles for specific database columns. Select a column in the Data Sources window and click its drop-down arrow to make it display in a text box, label, link label, combo box, or other control. Now, when you drag the column onto a form, or when you drag the table onto the form to build a record view, Visual Basic uses this type of control to display the column’s values.




Visual Basic 2005 with  .NET 3.0 Programmer's Reference
Visual Basic 2005 with .NET 3.0 Programmer's Reference
ISBN: 470137053
EAN: N/A
Year: 2007
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net