1 / 49

Lesson 6 — Database Programming

Lesson 6 — Database Programming. Microsoft Visual Basic .NET, Introduction to Programming. Objectives. Explain what a database is and how to use it. Build an ODBC Data Source Name from an Access database. Link a database to a Visual Basic application.

ellard
Download Presentation

Lesson 6 — Database Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lesson 6 — Database Programming Microsoft Visual Basic .NET, Introduction to Programming

  2. Objectives • Explain what a database is and how to use it. • Build an ODBC Data Source Name from an Access database. • Link a database to a Visual Basic application. • Design an application to display records of a database using the DataGrid control. • Design an application to display a single record of a database by binding the fields of the database to text boxes. • Apply user-defined data types (UDTs) in an application. • Use a sequential file to store data from an array of a user-defined data type.

  3. Application programming interface (API) Data Source Name (DSN) Database DataBindings property DataGrid DataSet DataSource property Fields Open Database Connectivity (ODBC) Record Structured query language (SQL) Structures User-defined data type (UDT) Vocabulary

  4. Databases • A database is an organized collection of related data. Data are facts, names, dates, and values. Once data is organized, it becomes information. An example of data that is not information is a list of telephone numbers that isn't alphabetized. The lack of organization makes it difficult to find a number. • Databases are organized in tables. A database containing information about running a school might have individual tables containing data on teachers, course offerings, classrooms, class lists, and students enrolled. • Within each table, data is organized into records. Each record is one complete entry. In the teacher table, for example, a record exists for each teacher employed by the school. • Records are further subdivided intofields.

  5. Note As a programmer, you might know little or nothing about the information for which you are designing a database. You might be asked to design a database about experimental cancer drugs or performance camshafts. If you know nothing about the information, you will be dependent on the users to help you determine what is important and what is not. This is one reason that communication with the users is so important.

  6. Preparing the Database You must prepare a database before accessing it with a Visual Basic program. An application programming interface (API) called Open Database Connectivity (ODBC) is commonly used to prepare a database for use in a program. APIs provide ways for programmers and users to utilize functions built into the operating system. ODBC makes it possible for applications to access the information stored in a database. To use ODBC, you must create a Data Source Name (DSN) for the database.

  7. The ODBC Data Source Administrator

  8. Datasets A dataset is the storage of copies of some or all of the records of the database. It may combine records or fields from various tables in a single database or information from several databases. Why not access a database directly from an application? Today, databases may be stored on network servers or on the Internet. To maintain a continuous connection from the user's computer to the database could consume a great many network resources. It is also likely that others will need access to the same database at the same time. To facilitate this sharing of databases and to limit the use of network resources, a portion of the data is read from the database and stored in a dataset.

  9. Data Adapter Configuration Wizard

  10. Choose Your Data Connection Dialog Box

  11. Data Link Properties Dialog Box Set for SQL Server

  12. Data Link Dialog Box Set for an Access Database

  13. Choosing a Query Type

  14. Generating the SQL Statements

  15. Query Builder Dialog Box

  16. View Wizard Results Pane of the Data Adapter Configuration Wizard

  17. Generate Dataset Class Dialog Box

  18. Step-by-Step 6.1 OleDbDataAdapter1.Fill(DsPortableCD1)

  19. The Choosing a CD Player Program

  20. DataGridView Control The DataGridView control is a wonder of automation. Once linked to a properly configured dataset, it automatically identifies the fields and creates a column for each field. If the dataset contains more than one table, the data grid automatically changes to allow the display of each table.The tables then have a hierarchical arrangement, in which one table is the parent to the other tables. A number of the properties of the DataGrid control relate to the parent table.

  21. DataGrid Control The columns created by the DataGrid control are called DataGridColumns. As each column is created, it is added to the GridColumns collection. The data grid creates a new row for each record and automatically provides a header for each column that contains the name of the field. You can modify the properties of a column in the DataGridColumn Collection Editor. Likewise, you can add or delete columns in the DataGridColumn Collection Editor.

  22. Displaying a Single Record on a Form It is possible to bind the fields of a table of a database to text boxes. The text boxes then provide a place in which to display and edit the values in the fields. To make a connection between a particular field of a dataset and a text box, you use the text box’s DataBindingsproperty.

  23. DataBindings Property When you expand the DataBindings property and click the Advanced button, the Advanced Data Binding dialog box opens. The Advanced Data Binding dialog box lists many of the properties of the text box. Each property may be bound to a field of the dataset. Typically, to display the information in a database, you bind a field of the dataset to the Text property of the text box. It is a simple matter to envision a one-to-one correspondence between the fields of a dataset and text boxes on the form.

  24. Displaying Data • Setting up an application to display data in text boxes instead of a data grid requires a little more work. • First, you populate the form with text boxes and corresponding labels. • Next, you bind each text box to a field of the dataset.

  25. CurrencyManager Object Each field of the dataset represents a column of data. Multiple textboxes linked to the same data source and each linked to a particular field of the dataset must be managed so the data they display is coordinated. For instance, if one text box is bound to the last name and one to the first name, it would not do to display the first name of one record with the last name of a different record. To coordinate the display of information, each data source has a CurrencyManager object. The CurrencyManager object coordinates the display of multiple text boxes linked to different fields of the same dataset. If there is more than one data source bound to a form, each has its own CurrencyManager.

  26. BindingContext Object To manage multiple CurrencyManagers, each form has a BindingContext object. The BindingContext object uses two parameters: the first is the name of the dataset, and the second is the name of the table within the dataset to which the data belongs. In the simplest case, the form’s BindingContext object has a single CurrencyManager object that coordinates the display of the data on the form.

  27. Data Adapter Preview Window

  28. The SingleRecord Application

  29. Binding the Manufacturer Field of the Dataset to the Text Property

  30. Advanced Data Binding Dialog Box Binding the Model field of the dataset to the Text property

  31. Step-by-Step 6.3 With Me.BindingContext(dsPortableCD1, "Portable CD Players") If .Position > 0 Then .Position -= 1 End If End With

  32. Step-by-Step 6.3 Dim iCnt As Integer With Me.BindingContext(dsPortableCD1, "Portable CD Players") iCnt = .Count - 1 If .Position < iCnt Then .Position += 1 End If End With

  33. User-defined Data Types As versatile as the various data types are, it is nevertheless often useful to design your own data types from other built-in types. In Visual Basic, these data types are called structures or user-defined data types (UDTs). Structures are useful for representing data that is too complex to represent with a single simple data type.

  34. Structure Statement You define structures by using a Structure statement. A simple example follows: Structure Automobile Public Make As String Public Model As String Public Price As Decimal End Structure The name of the data type, Automobile, follows the keyword Structure. Declarations for the fields of the structure appear between the Structure and End Structure statements. The fields are the individual parts that make up the structure. Each field, in turn, has its own data type.

  35. Layout of Controls

  36. Step-by-Step 6.4 Structure Automobile Public Make As String Public Model As String Public Price As Decimal End Structure Const Capacity As Integer = 50 Public CarLot(Capacity) As Automobile Public LotNumber As Integer = 0

  37. Step-by-Step 6.4 If LotNumber = 0 Then Exit Sub End If Dim Index As Integer For Index = 0 To LotNumber - 1 With CarLot(Index) .Make = "" .Model = "" .Price = 0 End With Next LotNumber = 0 lstCarLot.Items.Clear()

  38. Step-by-Step 6.4 txtMake.ResetText() txtModel.ResetText() txtPrice.ResetText() txtMake.Focus()

  39. Step-by-Step 6.4 If txtMake.Text.ToString = "" Or txtModel.Text.ToString = "" Or _ CDec(txtPrice.Text) = 0 Then MessageBox.Show("Please fill all the fields.") Exit Sub End If If LotNumber = Capacity Then MessageBox.Show("The lot is full.") Exit Sub End If

  40. Step-by-Step 6.4 With CarLot(LotNumber) .Make = txtMake.Text.ToString .Model = txtModel.Text.ToString .Price = CDec(txtPrice.Text) End With LotNumber += 1 miClear_Click(sender, e)

  41. Step-by-Step 6.4 Dim Index As Integer lstCarLot.Items.Clear() For Index = 0 To LotNumber - 1 With CarLot(Index) lstCarLot.items.add(.Make & " " & .Model & " " & .Price.ToString) End With Next

  42. Step-by-Step 6.4 Dim PathName As String With OpenFileDialog1 .DefaultExt = "txt" .InitialDirectory = "c:\My Documents" .Filter = "Text files | *.txt" .ShowDialog() PathName = .FileName End With

  43. Step-by-Step 6.4 Dim fs As StreamReader fs = File.OpenText(PathName) LotNumber = CInt(fs.ReadLine) Dim Index As Integer For Index = 0 To LotNumber - 1 With CarLot(Index) .Make = fs.ReadLine .Model = fs.ReadLine .Price = CDec(fs.ReadLine) End With Next fs.close() miDisplay_Click(sender, e)

  44. Step-by-Step 6.4 Dim PathName As String With SaveFileDialog1 .DefaultExt = "txt" .InitialDirectory = "c:\My Documents" .Filter = "Text files | *.txt" .ShowDialog() PathName = .FileName End With

  45. Step-by-Step 6.4 Dim fs As StreamWriter fs = File.CreateText(PathName) Dim strContent As String = LotNumber.ToString fs.WriteLine(strContent) Dim Index As Integer For Index = 0 To LotNumber - 1 With CarLot(Index) fs.WriteLine(.Make) fs.WriteLine(.Model) fs.WriteLine(.Price.ToString) End With Next fs.Close()

  46. Imports System.IO Enable the file statements used in the earlier steps to work by inserting the following statement above Public Class Form1. It brings the File I/O (Input/Output) objects into the class definition of the form. It should be the first line in the Code window.

  47. Summary • A database is an organized collection of related data. It is divided into tables, records, and fields. Each field holds an individual piece of data, such as a name or a value. The design of the database is important because an appropriate design makes it easier to access the information contained in the database. • An application programming interface (API) called Open Database Connectivity (ODBC) is used to make a database available for use in an application. To use ODBC, you create a Data Source Name (DSN) for the database through the ODBC Data Source Administrator in the Control Panel. • A dataset is a collection of copies of some or all of the records of a database.

  48. Summary • You use structured query language (SQL) to write statements that select records from a database. • You use a DataGrid control to display the records of a dataset. Once connected to the dataset, the data grid configures itself to display each field of each record in the dataset. • To establish a connection between an application and a local database, put an OleDbDataAdapter on the form. The Data Adapter Wizard guides you through the process of establishing a connection and creating the commands to create a dataset. • You use the DataBindings property to link a control to a field of a dataset.

  49. Summary • A structure, or user-defined data type (UDT), provides a custom data type created from other built-in types or other structures. The parts of a structure are called fields. Variables of the data type defined by the structure can be declared in procedures or at the module level. Fields are accessed with the dot notation Variablename.fieldname. • Variables declared with the Public keyword in the Declarations section (the top of the Form class definition) are visible throughout the project.

More Related