1.3. Connect to a Database and Browse Records


One great time-saving feature in VB 2005 is its new support for automatic data binding. The automatic data binding feature allows you to connect to a data source and then drag and drop the connection onto a Windows Forms application. A set of controls bound to the data source will then be automatically added to the form, and you can perform a variety of operations on the data source, such as navigating through records, searching for a specific record, and more, without having to write any code.

To see how automatic data binding works, you will now connect to a database and then drag and drop one of its tables onto your form so that you can view and work with its records. You will use SQL Server 2005 Express and the pubs database and then view and edit the records in the authors table.

  1. Select Data Show Data Sources to display the Data Sources window, as shown in Figure 1-10. The Data Sources window allows you to connect to your data sources (such as databases, web services, and business objects) and view their content. Click on the Add New Data Source… link to add a new data source to your project (see Figure 1-10).

    The Choose a Data Source dialog, which appears next, lets you choose between a database, a web service, or some other object as the source of your data. You'll be using a database, so click the Database icon and then click Next.

  2. Now you need to select a data connection to use to connect to your database. In the "Choose your data connection" dialog, click New Connection….

    Figure 1-10. The Data Sources window


  3. The Add Connection dialog will be shown (see Figure 1-11).

    For this step, I am assuming you have SQL Server 2005 Express installed on your computer. You can download SQL Server 2005 Express from: http://www.microsoft.com/sql/express/default.mspx.

    As SQL Server 2005 Express does not come with any sample databases, you need to install the sample database yourself. See the sidebar "SQL Server 2005 Express and the pubs and Northwind Databases" for more information.


    Enter the details of your database (see Figure 1-11). Type .\SQLEXPRESS for Server name if your database is installed locally on your machine. Select the database to use (in this example, select the sample pubs database in SQL Server 2005 Express). Click OK and Next in the next window.

  4. Visual Studio 2005 now has the information it needs to create the connection string that will let your application access the pubs database. The "Save the connection string to the application configuration file" step gives you the useful option of naming and saving the connection string in the application configuration file, as shown in Figure 1-12. Having the information in a configuration file lets you change database details without recompiling the application, even after it has been deployed. Leave the checkbox checked, give the connection string the name pubsConnectionString, and click Next to display the next part of the Data Source Configuration Wizard dialog.

  5. With a connection string in place, you're now in a position to select the table(s) you want to work with. The "Choose your database objects" step shown in Figure 1-13 displays the tables (and fields) of the pubs database. Your application will give users access to author information stored in the pubs database, so check the authors table to select all fields and then click Finish to move to the next step, which displays a completed Data Sources window for your application. You can return to the Data Sources window whenever you need to make changes by clicking on the Data Sources tab next to the Solution Explorer or by going to the Visual Studio menu and selecting Data Show Data Sources.

    Figure 1-11. Adding a new database connection


    SQL Server 2005 Express and the pubs and Northwind Databases

    SQL Server 2005 Express does not ship with the pubs and Northwind sample databases. But you can install them by downloading the pubs and Northwind database installation scripts at http://www.microsoft.com/downloads/details.aspx?familyid=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en.

    Once the scripts are installed on your system, go to the Visual Studio 2005 command prompt (Start Programs Microsoft Visual Studio 2005 Visual Studio Tools Visual Studio 2005 Command Prompt) and change to the directory containing your installation scripts (assuming your installation scripts are stored in pubs and Northwind databases:

     C:\>sqlcmd -S .\SQLEXPRESS -i instpubs.sql C:\>sqlcmd -S .\SQLEXPRESS -i instnwnd.sql 


    Figure 1-12. Saving the connection string in the application configuration file


    Figure 1-13. Selecting the table to work with


  6. One last step is required to make the authors table accessible to your users: you need to bind each field to a control that can be displayed in the application window. In the Data Sources window, you'll see that the authors table is displayed as a tree, as shown in Figure 1-14, and that each field has been bound to a specific type of control. For example, the au_lname field is bound to a text box (represented by the icon containing the letters "abl"). You can change the binding by clicking on the field name and then choosing another binding. For now, we'll make only one change. Click on the drop-down menu for the au_id field and then choose the Label control (as represented by the icon containing the letter A, as shown in Figure 1-14).

    Figure 1-14. Changing the bindings of the fields


    Also, you should change the authors table binding to Details (using the drop-down menu for the authors field shown in Figure 1-15) so that you can view the authors table one record at a time. The reason for doing this will become evident to you in the next step when we add the ability for users to navigate through the table.

    Now you're ready the drag and drop the authors item in the Data Sources window onto the default Windows Form and watch Visual Studio 2005 perform some real magic. For starters, Visual Studio automatically populates Form1 with the controls shown in Figure 1-16. Visual Studio 2005 also adds a new BindingNavigator control to the top of the form. The new BindingNavigator control lets users navigate through all the records in the table as displayed in the form.

  7. By default, the BindingNavigator control is docked (fixed) to the top of the form and is not moveable. Set the Dock property to None and rearrange the controls as shown in Figure 1-17.

    Figure 1-15. Changing the binding of the table


    Figure 1-16. The populated Windows Form


    You can rearrange the BindingNavigator control by increasing the size of the ToolStripContainer control. Simply click the arrow shown at the top of the ToolStripContainer control.


    Figure 1-17. Arranging the controls at the top of the Form


  8. You can now test the application by pressing F5. Form1, the main window of your application, will display, complete with menu, toolbar, and navigation control, as shown in Figure 1-18. You should be able to navigate the records in the authors table as well as move the various toolbars around. Try scrolling through the table by clicking the arrows on the BindingNavigator control.

    Figure 1-18. Navigating the records in the table


    You should also be able to edit individual records by modifying the values in the text box bound to each field. Try it. To save a modified record to the database, you need to click on the Save icon, which is represented by the diskette icon in the BindingNavigator control.

    Visual Studio 2005 automatically adds the relevant code to the code behind of the form to handle the retrieving and saving of data. To see the code added by Visual Studio 2005, double-click on the diskette icon to reveal the code behind. You will see the code shown in Example 1-1.

    Example 1-1. Save menu item code behind added by Visual Studio 2005
     Private Sub bindingNavigatorSaveItem_Click( _  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles bindingNavigatorSaveItem.Click  If Me.Validate Then  Me.AuthorsBindingSource.EndEdit()  Me.AuthorsTableAdapter.Update(Me.PubsDataSet.authors)      Else  System.Windows.Forms.MessageBox.Show( _ Me, "Validation errors occurred.", "Save", _ System.Windows.Forms.MessageBoxButtons.OK, _ System.Windows.Forms.MessageBoxIcon.Warning)  End If End Sub 



Visual Basic 2005 Jumpstart 2005
Visual Basic 2005 Jumpstart
ISBN: 059610071X
EAN: 2147483647
Year: 2005
Pages: 86
Authors: Wei-Meng Lee

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