1、Chapter 10 Accessing Database Files,Programming In Visual Basic.NET, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 2,Visual Basic and Databases,VB projects can display and update the data from database files VB.NET uses ADO.NET for database access ADO.NET is the next generation of
2、 database technology, based on Microsofts previous version ActiveX Data Objects (ADO), 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 3,ADO.NET,Data is stored and transferred in Extensible Markup Language (XML) Allows access to database data in many formats Two types of Connections
3、 SQLClient for SQL Server OLEDB for all other database formats, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 4,Relational Database Terminology,Database includes Tables = collection of related data Queries = SQL designed to select data from tables Table Record = row of related dat
4、a for one instance Field = column of specific data element Keys Primary = uniquely identifies a record Foreign = links record to related record in related table Relationships, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 5,Using ADO.NET and VB,Can display data from a database on
5、Windows Form Web Form Add controls to form and bind data to them Normal Controls like label, textbox Special controls designed just for data like DataGrid, DataList, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 6,Steps for Data Access, 2001 by The McGraw-Hill Companies, Inc. All
6、rights reserved.,10- 7,Connection,Used to establish link form specific file or database to program Types SqlConnection Microsoft SQL Server databases only OleDBConnection All other databases, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 8,Setting Up a Connection,Can be accomplish
7、ed from Toolbox Server Explorer window Within Data Adapter Configuration Wizard using New Connection button No matter which method above is used to begin the connection, the Data Link Properties dialog appears for you to complete the task, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.
8、,10- 9,Creating a Connection from Server Explorer,Click Connect to Database buttonOR Right-click Data Connections, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 10,Data Link Properties Dialog,Used to define Connections OLE DB Provider Actual DB Filename Logon UserName and Password
9、 (optional) Used to Test Connection, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 11,Connections in Server Explorer,Shows all created Connections Same Connection can be use for multiple projects Expand Connection node to view tables and fields, 2001 by The McGraw-Hill Companies,
10、Inc. All rights reserved.,10- 12,Data Adapter,Does all the work of passing data back and for the between a data source and a program Does not have to be a database Can be text file, object, or even an array Types, must match Connection type OleDbDataAdapter SqlDataAdapter Use db prefix (e.g. dbRnR),
11、 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 13,Setting Up a Data Adapter from Server Explorer,Drag Table name to formOR CTRL-click Field names and drag to form, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 14,Data Adapter Configuration Wizard,Walks you throu
12、gh the steps for selecting fields in a database Only used if you create/add a Data Adapter from the Toolbox Can be used to modify existing Data Adapters also, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 15,Dataset,Temporary set of data stored in memoryIn ADO.NET datasets are dis
13、connected; the copy of data kept in memory does not keep an active connection to the data sourceDataset may contain multiple tables and relationships, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 16,Defining a Dataset,Drag Dataset control from the toolbox to form Select Data menu
14、, Generate Dataset Right-click on Data Adapter and select Generate Dataset In all cases, you see the Generate Dataset dialog box, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 17,Defining a Dataset (cont.),Name dataset with ds prefix in Generate Dataset dialog (e.g. dsBooks), 2001
15、 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 18,Data Passing from Data Source to Dataset to User Interface, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 19,XML Data,Industry-standard format for storing and transferring data Specifications at http:/www.w3.org/XML,
16、World Wide Web Consortium (W3C) The needed XML for accessing databases will be automatically generated for you in Visual Basic, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 20,XML,Data stored in XML is all text XML Data File Contains actual data XML Schema File Contains descripti
17、ons for Fields Data Types Constraints, such as required fields .xsd file in Solution Explorer, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 21,Fill Method,Method of the Data Adapter executed at run time to fill the dataset Usually coded in Form_Load (or Page_Load for Web Projects
18、) General Form DataAdapterName.Fill (DataSetName) ExampledbRnR. Fill (dsBooks1), 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 22,Creating Bound Controls: Data Grid,In Windows Form Set properties of the controls Use Fill method Example: Grid Control New control in VB.NET Displays
19、database records as a table Steps Set up/add Connection, DataAdapter, DataSet Add DataGrid control from toolbox (dbg prefix), 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 23,Creating Bound DataGrid,Steps continued Set properties of DataGrid to bind to data DataSource to DataSet n
20、ame DataMember to Table nameAdd code to populate control with data Windows Form, Form_Load = DataAdapterName.Fill (DataSetName), 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 24,Creating Simple Bound Controls,Labels and text boxes Same steps: Set up/add Connection, DataAdapter, Da
21、taSet Add text boxes or label control from toolbox Set properties to bind to data Data Binding properties menu Text option to be specifiedAdd code to populate control with data Windows Form, Form_Load = DataAdapterName.Fill (DataSetName), 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,
22、10- 25,Navigating Through DataSets,Refer to the Position and Count properties of the Windows Forms BindingContext, Move to next record Me.BindingContext(dsBooks1, “Books“).Position + = 1 Move to previous record Me.BindingContext(dsBooks1, “Books“).Position - = 1 Move to the first record Me.BindingCo
23、ntext(dsBooks1, “Books“).Position = 0 Move to the last record With Me.BindingContext(DsCustomer1, “Customer“).Position = .Count - 1 End With, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 26,Retrieving Record Count,Specify DataSet Table Name Rows Collection Count Property Example
24、intRecordCount=dsBooks1.Tables(“Books“).Rows.Count Ulternatively Me.BindingContext(DsCustomer1, “Customer“).Count, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 27,Updating a Database File,Types of updating Modify existing records Add records Delete records Must display the datase
25、t in bound controls that can be used for input (no labels) You must write code to actually update the database, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 28,Typical Form for Updating,Save Button is enabled only when an Add or Edit is in progress,Add caption changes to Cancel d
26、uring Add or Edit,All Navigation Buttons are disabled during Add or Edit, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 29,Update Method,Method of DataAdapter used to write changes to database General Form DataAdapterName.Update (DataSet, “Table“) Example dbRnR. Update (dsBooks1,
27、“Books“) Can be executed After every change, perform data validation, then execute Update (Save Record button) Once, just before program termination for all changes execute Update (Forms Closing event), 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 30,Update Method (cont.),Automat
28、ically loops through the records in a table to determine what type of update is needed and then performs the required SQL Update Insert DeleteExecute during Forms Closing event if running once to apply all changes, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 31,Example - Update
29、Forms Closing Event, mblnIsDirty was declared in Declarations section Save changes, Update Database If mblnIsDirty ThenIf MessageBox.Show(“Save Changes?“, “Books“ _ MessageBoxButtons.YesNo, _ MessageBoxIcon.Question ) = DialogResult.Yes ThenTrydbRnR.Update(dsBooks1, “Books“)CatchMessageBox.Show(“Err
30、or saving file“, “Books“)End If End If, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 32,Logic of Update Programs,Enclose all statements that access the database in Try/Catch blocks Display data in bound input controls Initially set ReadOnly property = True When user clicks Edit b
31、utton Set ReadOnly = False Disable Navigation buttons Provide Cancel and Save buttons for user, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 33,Saving Changes Logic - User Clicks Save Button,Perform Data Validation If valid data then Set ReadOnly = True Set mblnDirty = True Enabl
32、e Navigation buttons Disable Save button If invalid data then Notify user with MessageBox Set focus to control containing invalid data, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 34,Canceling Changes Logic - User Click Cancel Button,Replace changed values in controls with origi
33、nal values See RejectChanges procedure (p 441) Set ReadOnly = True Enable Navigation buttons Disable Save button, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 35,Deleting Records,Execute Delete Method of the tables Rows collection Example,Dim intCurrRecNum As Integer intCurrRecNu
34、m = Me.BindingContext(dsBooks1, _“Books“).Position dsBooks1.Books.Rows(intCurrRecNum).Delete ( ) mblnIsDirty = True, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 36,Adding Records,If user clicks Add button Clear bound input controls Set ReadOnly = False Set mblnAdding = True Disa
35、ble Navigation buttons Provide Cancel and Save buttons for user Logic for Save and Cancel are same as on previous slide except for resetting mblnAdding to False, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 37,Example - Saving an Added Record,Dim newRow As DataRow = dsBooks1.Book
36、s.NewRownewRow(“Author“) = txtAuthor.Text newRow(“ISBN“) = txtISBN.Text newRow(“Title“) = txtTitle.TextdsBooks1.Books.Row.Add(newRow), 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 38,Logic for Save Button,Must determine type of operation in progress since used for saving changes
37、made as Edits Adds Use mblnAdding variable to identify Add operations in progress, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 39,Bound Combo Boxes,Provides an easy user interface for selecting records to view or update Windows Forms Use ListBox or ComboBox Web Forms Use ListBox
38、 or DropDownList, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 40,Using Multiple Data Adapters,Use when more than 1 dataset or more than 1 table in a dataset is needed A single Connection object can supply the connection for more than 1 data adapter Useful when a list is availabl
39、e for the user to select the record(s) to display 1 dataset for the list itself 1 dataset for the selected record(s), 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 41,Parameterized Query,DataSet with special type of SQL SELECT statement WHERE clause is incomplete until user enters
40、 value for criteria at run time Uses question mark in place of criteria Example,SELECT Title, Author, ISBN FROM books WHERE Title = ?, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 42,Filling a List - Windows Forms,Set ListBox or ComboBox Properties DataSource to DataSet name Disp
41、layMember to table name.field nameUse Fill method to populate list with data,Tip: To eliminate duplicate entries in the list. Modify the SQL SELECT statement to include the DISTINCT keyword., 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 43,Displaying the Data for the Selected Ite
42、m,At run time the user may enter the value to be used in the Parameterized Query in TextBox Include an associated button for the user to click to use the value Be sure to check for possible empty dataset if the user enters a value not in the dataset Selection of a ListBox or ComboBox Write code in t
43、he SelectedIndexChanged Event, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 44,Example, Get record to match selected title in list dsBooks1.Clear( ) dbRnR.SelectCommand.Parameters(“Title“).Value =cboTitles.Text dbRnR.Fill(dsBooks1),Combo box SelectedInexChange Event,TextBox, Asso
44、ciated Buttons Click Event, Get record to match selected title in list dsBooks1.Clear( ) dbRnR.SelectCommand.Parameters(“Title“).Value = txtTitles.Text dbRnR.Fill(dsBooks1) If dsBooks1.Tables(“Books“).Rows.Count = 0 ThenMessageBox.Show(“Title not found.“), 2001 by The McGraw-Hill Companies, Inc. All
45、 rights reserved.,10- 45,Making Database Projects Portable,Connection information is directly tied to database file location You must move database AND Create Connection on new computer Configure DataAdapter For Web Projects, also create Virtual Directory Less problems if you store the database in t
46、he same folder as your project, 2001 by The McGraw-Hill Companies, Inc. All rights reserved.,10- 46,Command Summary, Data Adapter fills a Dataset. dbRnR. Fill (dsBooks1) Move to next, pervious, first and last record Me.BindingContext(dsBooks1, “Books“).Position + = 1, -=1, 0, .Count 1 Retrieving rec
47、ord count intRecordCount=dsBooks1.Tables(“Books“).Rows.Count Get record to match selected title in list dbRnR.SelectCommand.Parameters(“Title“).Value =cboTitles.Text Save the changes in the file dbRnR. Update (dsBooks1, “Books“)Add a new record Dim newRow as DataRow = DsCustomer1.Customer.NewRowdsBooks1.Books.Row.Add(newRow) Delete current record dsBooks1.Books.Rows(intCurrRecNum).Delete ( ),
