1 / 40

Chapter 10 – Database Management

Chapter 10 – Database Management. 10.1 An Introduction to Databases 10.2 Relational Databases and SQL. 10.1 An Introduction to Databases. The Server Explorer Accessing a Database with a Data Table. Database Terminology. A table is a rectangular array of data.

rhona
Download Presentation

Chapter 10 – Database Management

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. Chapter 10 – Database Management 10.1 An Introduction to Databases 10.2 Relational Databases and SQL Chapter 10 - VB.Net by Schneider

  2. 10.1 An Introduction to Databases • The Server Explorer • Accessing a Database with a Data Table Chapter 10 - VB.Net by Schneider

  3. Database Terminology • A table is a rectangular array of data. • Each column of the table, called a field, contains the same type of information. • Each row, called a record, contains all the information about one entry in the database. • In a phone book, there are fields for name, address, and phone number. Your entry in the phone book is a record. Chapter 10 - VB.Net by Schneider

  4. Database Management Software (DBMS) • Used to create databases • Databases can contain one or more related tables • Examples of DBMS include Access and Oracle • You should already know all of this from your database course Chapter 10 - VB.Net by Schneider

  5. The Server Explorer • Allows the programmer to view information located on other computers • Can also be used to view a database Chapter 10 - VB.Net by Schneider

  6. Using the Server Explorer • Place your mouse over the “Server Explorer” tab on the left side of Visual Studio. • Right-click on “Data Connections”, and select “Add Connection”. • In the Data Link Properties window that appears, click on the “Provider” tab at the top. Select the item “Microsoft Jet 4.0 OLE DB Provider” from the OLE DB Providers List box, and then press the next button. • Click on the “...” button to the right of the first text box. This will open up a file,browser that allows you to locate any file then press Open. • Clear the contents of the “User name” text box. Chapter 10 - VB.Net by Schneider

  7. Server Explorer continued • Press the Test Connection button. The message box stating “Test Connection Succeeded” will appear. Press the OK button on that message box, and then press the OK button on the Data Link Properties box. • An icon should appear in Server Explorer. Click on the + sign to the left of the icon to expand this entry. Three subentries will appear: Tables, Views, and Stored Procedures. • Expand the Tables entry to reveal the subentries. • Expand an entry to reveal the fields of the table. • Double-click on a table to show the table in a grid. Chapter 10 - VB.Net by Schneider

  8. Demo – vbBooks.mdb • Don’t really need this method for MS Access databases as Access is on most computers • But remember that you can access just about any type of database using the Server Explorer! Chapter 10 - VB.Net by Schneider

  9. Accessing a Database with a Data Table • A DataTable object holds the contents of a database table as a rectangular array. • A data table is similar to a two-dimensional array; it has rows and columns. • The following declares a DataTable variable Dim dt As New DataTable() Chapter 10 - VB.Net by Schneider

  10. Connecting with a DataTable Dim dt As New DataTable() Dim connStr As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = vbBooks.mdb " Dim sqlStr As String = "SELECT * FROM Titles" Dim dataAdapter As New _ OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() Chapter 10 - VB.Net by Schneider

  11. Connection String Dim connStr As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = MEGACITIES.MDB " • This string must be exact! • The VB editor can help you! (if you know what to look for (output box) • Cut and paste if possible Chapter 10 - VB.Net by Schneider

  12. Properties of the DataTable • After these six lines of code are executed, the number of records in the table is given by dt.Rows.Count • The number of fields in the table is given by dt.Columns.Count Chapter 10 - VB.Net by Schneider

  13. Properties of the DataTable • The records are numbered 0 through dt.Rows.Count – 1 • The fields are numbered 0 through dt.Columns.Count – 1 (just like arrays – numbering starts at 0) Chapter 10 - VB.Net by Schneider

  14. More Properties • The name of the jth field is given by dt.Columns(j) • The entry in the jth field of the ith record is dt.Rows(i)(j) • The string containing the entry in the specified field of the ith record is dt.Rows(i)(“fieldName”) Chapter 10 - VB.Net by Schneider

  15. Bound Controls • A data table bound to a list box can transfer information automatically into the list box. • The following statement binds a list box to a data table: lstBox.DataSource = dt • The contents of a specified table can be displayed in the list box by: lstBox.DisplayMember = “Author" Chapter 10 - VB.Net by Schneider

  16. Demo – DBtextbooks Chapter 10 - VB.Net by Schneider

  17. Exercises p. 502 Chapter 10 - VB.Net by Schneider

  18. 10.2 Relational Databases and SQL • Primary and Foreign Keys (quick review) • SQL Requests • The DataGrid Control Chapter 10 - VB.Net by Schneider

  19. Primary Keys • A primary key is used to uniquely identify each record. • Databases of student enrollments in a college usually use a field of Student Number as the primary key. Chapter 10 - VB.Net by Schneider

  20. Primary Key Fields • VB.NET will insist that every record have an entry in the primary-key field and that the same entry does not appear in two different records. Chapter 10 - VB.Net by Schneider

  21. Related Tables • Two or more tables are usually related. • vbBooks.mdb – the two tables Titles and Publishers are related by their PubID field. • Publishers.PubID is a primary key. • We say that Titles.PubID is a foreign key of Publishers. PubID Chapter 10 - VB.Net by Schneider

  22. Foreign Keys • Foreign keys can be specified when a table is first created. VB.NET will insist on the Rule of Referential Integrity. • This Rule says that each value in the foreign key must also appear in the primary key of the other table. Chapter 10 - VB.Net by Schneider

  23. Join • A foreign key allows VB.NET to link (or join) together two tables from a relational database Chapter 10 - VB.Net by Schneider

  24. SQL • Structured Query Language developed for use with relational databases • Very powerful language • Allows for the request and display of specified information from a database Chapter 10 - VB.Net by Schneider

  25. Four SQL Requests • Show the records of a table in a specified order SELECT * FROM Table1 ORDER BY field1 ASC • or SELECT * FROM Table1 ORDER BY field1 DESC Specifies ASCending Or DESCending Chapter 10 - VB.Net by Schneider

  26. Show just the records that meet certain criteria SELECT * FROM Table1 WHERE criteria * means "all the fields" Specified Criteria Name of the Table where the Records may be found Chapter 10 - VB.Net by Schneider

  27. Join the tables together • connected by a foreign key, and present the records as in Requests I and II. SELECT * FROM Table1 INNER JOIN Table2 ON foreign field = primary fieldWHERE criteria Chapter 10 - VB.Net by Schneider

  28. Make available just some of the fields • of either the basic tables or the joined table. SELECT field1, field2, . . ., fieldN FROM Table1 WHERE criteria Chapter 10 - VB.Net by Schneider

  29. Criteria Clause • A string containing a condition of the type used with If blocks. • Uses the standard operators <, >, and = • Also can use the operator Like. • Like uses the wildcard characters “_” and “%” to compare a string to a pattern. • Note: Similar to windows wildcards ? and * Chapter 10 - VB.Net by Schneider

  30. Like Examples • An underscore character stands for a single character in the same position as the underscore character. • The pattern “B_d” is matched by “Bid”, “Bud”, and “Bad”. • A percent sign stands for any number of characters in the same position as the asterisk. • The pattern “C%r” is matched by “Computer”, “Chair”, and “Car”. Chapter 10 - VB.Net by Schneider

  31. SELECT clause • SELECT fields FROM clause • fields is either * (to indicate all fields) or a sequence of the fields to be available (separated by commas) • clause is either a single table or a join of two tables Chapter 10 - VB.Net by Schneider

  32. Join clause • A join of two tables is indicated by a clause of the form table1 INNER JOIN table2 ON foreign key of table1=primary key of table2 • Appending WHERE criteriato the end of the sentence restricts the records to those satisfying criteria. • Appending ORDER BY field(s) ASC (or DESC) presents the records ordered by the specified field or fields. Chapter 10 - VB.Net by Schneider

  33. General SQL statements • SELECT www FROM xxx WHERE yyy ORDER BY zzz • where SELECT www FROM xxx is always present • May be accompanied by one or both ofWHERE yyy and ORDER BY zzz. • The xxx portion might contain an INNER JOIN phrase. Chapter 10 - VB.Net by Schneider

  34. More on SQL statements • The single quote, rather than the normal double quote, is used to surround strings. • Fields may be specified with the table they come from by tableName.FieldName Chapter 10 - VB.Net by Schneider

  35. Virtual Tables • SQL statements create a new “virtual” table from existing tables. • “Virtual” tables don’t exist physically. • For all practical purposes, VB.NET acts as if they did. • You may also see a “virtual” table called a view. Chapter 10 - VB.Net by Schneider

  36. The DataGrid Control • Displays the values for an entire view in table format (demo toolbox) • The prefix for the name of a DataGrid control is dg. dgDisplay.DataSource = dt • displays the contents of the data table dt in the data grid. Demo – 10-2-1 Chapter 10 - VB.Net by Schneider

  37. Changing the Contents of a Database • Data grids can also be used to add, modify, and delete records from a database. • After a DataAdapter has been created, the statement Dim commandBuilder As New OleDbCommandBuilder(dataAdapter) will automatically generate the commands used for the Insert, Update, and Delete operations. Chapter 10 - VB.Net by Schneider

  38. Using the DataAdapter to Change a Database • If changes is an Integer variable, then the statement changes = dataAdapter.Update(dt) will store all of the insertions, updates, and deletions made in the data table to the database and assign the number of records changed to the variable changes. Demo – 10-2-3 Chapter 10 - VB.Net by Schneider

  39. Comments • SQL statements are case insensitive. Chapter 10 - VB.Net by Schneider

  40. Exercises p. 516 Chapter 10 - VB.Net by Schneider

More Related