1 / 36

Chapter 11

Chapter 11. Introduction to Database Processing. Class 11: Database Processing. Use a Visual Studio Wizard to establish a database connection used to load database data into the project, and edit that data using control instances created on a form

austin-mercado
Download Presentation

Chapter 11

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 11 Introduction to Database Processing

  2. Class 11: Database Processing • Use a Visual Studio Wizard to establish a database connection used to load database data into the project, and edit that data using control instances created on a form • Perform specialized database processing tasks beyond those performed by the Data Source Configuration Wizard • Work with database data programmatically

  3. Naming Database Tables and Fields • Standard (Hungarian) prefixes are commonly used to name database tables and fields • The prefix "tbl" denotes a table • The prefix "fld" denotes a field

  4. ADO.NET (Introduction) • Database management in .NET is performed through ActiveX Data Objects (ADO.NET) • The System.Data and System.Data.OleDb namespaces make up ADO.NET

  5. Steps to Working with ADO.NET • First, a database connection is established • Second, an SQL command is sent over the open connection • Third, ADO.NET builds an in-memory representation of the returned data • Fourth, the database connection is closed • Optionally, changes can be made to the in-memory representation of the data • Finally, changes can be propagated back to the database

  6. Visual Studio Database Wizards (Introduction) • Database processing can get complex • Visual Studio supplies the Data Source Configuration Wizard to perform the following tasks: • Create a database connection based on information you specify • Select the tables and fields that will be included in the data source

  7. Understanding the Concept of a Data Source • A datasource is a connection between a Visual Studio project and a database • A data source can be configured to connect to different types of databases • The Data Sources window is used to manage an application's data sources

  8. Figure 11-2:Connecting to a Database

  9. Project Data Sources • A project can have one or many data sources • Each data source appears in the Data Sources window • Use the Data Sources window to create new data sources and modify existing ones

  10. Figure 11-3:Data Sources Window

  11. Creating and Configuring a Database Connection • Use the Data Source Configuration Wizard to create a new data source • The Data Source Configuration Wizard creates a connection string • A connection string is used by ADO.NET to establish the database connection • The Data Sources window displays the tables and fields in a data source

  12. Figure 11-4: Data Sources Window with a Table and Fields

  13. Creating Bound Control Instances • Drag fields from the Data Sources window to the Windows Forms Designer • The control instances are configured and bound automatically • The control type is based on the data type of the database field • TextBox control instances are created for String fields • CheckBox control instances are created for Boolean fields • The default control type can be changed

  14. Figure 11-5: Control Instances Bound to a DataSet

  15. Figure 11-16: Windows Forms Designer Displaying Bound Control Instances at Design Time

  16. Figure 11-17: Windows Form Displaying Bound Control Instances at Run Time

  17. Populating and Updating a TableAdapter • Calling the Fill method populates a DataSet and DataTable • Calling the Update method saves changes made to the DataSet back to the database

  18. Figure 11-19: Using the TableAdapter to Select and Update Database Data

  19. Filling A DataSet(Example) • The Wizard adds the following statement to the form's Load event handler to populate a DataSet: Private Sub frmMain_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load Me.TblEmployeesTableAdapter.Fill( _ Me.EmployeesDataSet.tblEmployees) End Sub

  20. Updating a DataSet(Example) • The Update method is called on the TableAdapter to update a DataSet as follows: Me.TblEmployeesTableAdapter.Update( _ Me.EmployeesDataSet.tblEmployees)

  21. Introduction to the BindingSource Class • Navigation from record to record is accomplished using the BindingSource class • The BindingSource class is new to Visual Studio 2005 • Two properties are used to bind data • The DataSource property is set to a DataSet • The DataMember stores a string containing a table in the DataSet

  22. The BindingSourceClass (Members) • The Count property stores the number of records contained in the data source • The Current property gets the current record • The Position property gets the index of the current list item • The methods named MoveFirst, MoveNext, MovePrevious, and MoveLast perform navigation

  23. Introduction to the Untyped DataSet and DataTable Classes • The System.Data.DataSet class stores an in-memory representation of one or more database tables • Each table is represented as a DataTable object • The Tables property of the DataSet stores a reference to a collection of DataTable objects • One DataTable object exists for each table in the DataSet • Reference a DataTable using a 0-based index value or a string key

  24. Referencing a DataTable (Example) • Reference the first DataTable in the DataSet named EmployeesDataSet using a numeric index and a string key Dim CurrentTable As _ System.Data.DataTable CurrentTable = _ EmployeesDataSet.Tables(0) CurrentTable = _ EmployeesDataSet.Tables("tblEmployees")

  25. Introduction to the Untyped DataTable Class • The DataTable class supports properties to get information about the current table • The TableName property gets the name of the table • The Rows property gets a collection of rows (DataRow objects)

  26. Introduction To the Untyped DataRow Class • The Rows property of the DataTable class references a collection of rows • The Count property of the Rows collection returns the number of rows • Each item in the collection has a data type of DataRow • Each item in the collection represents a row in the table

  27. The DataRow Class (Examples) • Get the number of rows in the DataTable named CurrentTable Dim RowCount As Integer RowCount = CurrentTable.Rows.Count • Get the first row from the table named tblEmployees Dim CurrentRow As System.Data.DataRow CurrentRow = _ EmployeesDataSet.Tables("tblEmployees"). _ Rows(0)

  28. Referencing a Field in a DataRow • Use the Item member of the DataRow class to reference a field • The Item member accepts an Integer index or string key containing the field name • Example to reference the field named fldEmployeeID: Dim CurrentID As String CurrentID = _ CurrentRow.Item("fldEmployeeID").ToString

  29. Introduction to the DataColumn Class • A database table contains one or more columns • The Columns collection of the DataTable class stores a reference to the columns • The DataColumn class of the Columns collection stores a reference to an individual column • The ColumnName property stores the name of the column • The Caption property stores a descriptive caption • The MaxLength property stores the maximum length (number of characters) that can be stored in the column • This value is inferred for numeric data types

  30. The DataColumnClass (Example) • Examine the first column in the DataTable named CurrentTable Dim CurrentTable As DataTable Dim CurrentColumn As DataColumn CurrentTable = EmployeesDataSet.tblEmployees CurrentColumn = CurrentTable.Columns(0) txtColumnName.Text = _ CurrentColumn.ColumnName txtUntypedOutput.Text = _ CurrentColumn.DataType.ToString

  31. Introduction to Strongly Typed DataSets • Strongly typed DataSets are generated by a Wizard • The Wizard creates properties and methods corresponding to the underlying tables in the database • Strongly typed DataSets eliminate type conversion errors • Strongly typed DataSets support Intellisense technology

  32. Figure 11-22:DataSet Files Appearing in the Solution Explorer

  33. Implementation of a Strongly Typed DataSet • A strongly typed DataSet is just a class that inherits from the base System.Data.DataSet class • Example: Partial Public Class EmployeesDataSet Inherits System.Data.DataSet End Class • Its members correspond to the underlying tables and fields in those tables

  34. Implementation of a Strongly Typed DataTable • The strongly typed DataSet class contains a strongly typed DataTable class • It inherits from the base System.Data.DataTable class • Example: Partial Public Class tblEmployeesDataTable Inherits System.Data.DataTable End Class

  35. Strongly Typed DataRows • A strongly typed DataRow class is just a class that inherits from the base System.Data.DataRow class • Example: Partial Public Class tblEmployeesRow Inherits System.Data.DataRow End Class

  36. Using a Strongly Typed DataRow • Using a strongly typed DataRow, it's possible to reference the fields directly • Example: Dim CurrentID As String CurrentID = CurrentRowTyped.fldEmployeeID.ToString

More Related