1 / 29

CIS162AD

CIS162AD. Databases 14_databases.ppt. Overview of Topics. Relational Database Terminology ADO.Net Instructions to complete CS13 Connecting to a Database Connection Data Adapter DataSet Binding data DataGridView Label and Textbox View. Relational Databases.

amaya-contreras
Download Presentation

CIS162AD

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. CIS162AD Databases 14_databases.ppt

  2. Overview of Topics • Relational Database Terminology • ADO.Net • Instructions to complete CS13 • Connecting to a Database • Connection • Data Adapter • DataSet • Binding data • DataGridView • Label and Textbox View

  3. Relational Databases • Most data is now stored in relational database management systems (DBMS or RDBMS). • There are various vendors to choose form:Oracle, MS SqlServer, Sybase, IBM DB2, etc. • Visual Studio is usually used to develop applications that store data in Microsoft SqlServer. • In this class we will use MS Access, due to SqlServer’s installation requirements. • Those of you familiar with Access know that it has a built-in form and report designer. However, keep in mind that we would normally be accessing a database stored in SqlServer, which does not have built-in tools.

  4. Database Terminology • A database is made up of one or more related tables. • Conceptually a table is made up of rows and columns (2D Array). • Each row represents the data for one record(a person, inventory item, course information). • Each column (field) is a data element (name, address, city, state, zip). • Tables have a Primary Key Field to uniquely identify each record(Id number, part number, account number). • Relationships between various tables can be defined. • A DBMS stores everything (tables, columns, relationships, etc.) about the database in system tables. • System and data tables are usually stored in one file (CSMail.mbd).

  5. SQL – Structured Query Language • SQL is pronounced sequel. • SQL – Structured Query Language – Basic set of commands that are common in all DBMS. • DBMS vendors may add additional commands. • SQL commands are usually processed against a record set. • Select name, address From customer where zip = “85202”; • Delete From customer where zip = “85202”All rows matching the criteria would be selected or deleted. • Record set commands are very powerful. • We’ll usually want to qualified a command by specifying a customer id or other unique identifier using the Where clause.

  6. An Access Table Columns Row

  7. C# Application Reading Database

  8. Accessing a Database – ADO.Net • Use ActiveX Data Objects (ADO) • An Access database uses the following objects: • OleDbConnection – connection tool establishes a link to a data source. • OleDbDataAdapter – data adapter handles retrieving and updating the data and creates a Dataset. • Dataset – bind columns in the Dataset to controls (textbox, listbox) by setting the DataBinding property. • Use the Fill method of the data adapter to load the data into the dataset, daCustomer.Fill(dsCustomer1);The Fill method is usually placed in the form load event.

  9. Web Form Data Source Connection Data Adapter Dataset Windows Form Specific data file Connects to data source Handles data transfer and provides data for dataset; uses SQL Actual data stored in memory; can contain multiple tables Bounded controls display data Accessing and Presenting Data

  10. Completing CS13 • Create CS13 project and download the database file (CSMail.mbd) into the /debug/bin/ folder of the project. • CSMail.mbd is available on the website. • Name the form CS13Form. • Change the Text property of the form to CS13 Your Name. • Display the Data Sources Panel in Visual Studio. • Menu Path: Data > Show Data Sources • The Data Source panel should be displayed over the Toolbox. • Click on Add New Data Source • The Data Source Configuration Wizard should launch.

  11. Data Source Panel

  12. Data Source Configuration Wizard • Click on Database as the data source type. • Click on Next >.

  13. Data Connection • Click on New Connection.

  14. Add Connection • Click on Change... • Select Microsoft Access Database File • Click on OK. • Click Browse… • In the open file dialog box, navigate to CS13\bin\Debug\CSmail.mbd • Click on Open. • Click Test Connection • Click OK. • Click Next on Choose Data Connection window.

  15. Copy File Prompt • Click on No.

  16. Save Connection String • Make sure Yes is selected. • Click Next >

  17. Choose Database Objects • Click on the plus sign in front of Tables to expand list. • Select Customer. • Click on Finish.

  18. Add DataGridView • Drag Customer table from Data Sources on to the form to add a DataGridView control.

  19. DataGridView Control • When a table from the Data Sources is dragged on to the form, a DataGridView control is created by default. • It allows users to browse all the rows in the table as well as add, update, and delete rows. • The navigation toolbar is also added at the top of the form. • The FormLoad method is also created, which contains a call to the Fill method to load the data into the DataSet from the source. • Four additional objects are also added to the Component tray. • Dataset provides access to the data • TableAdapter provides the commands to read and write to the table • BindingNavigator defines the toolbar used to navigate • BindingSource identifies the data source for the bounded controls • Next Slide - Binding

  20. Data Binding • Complex Binding • Connect more than one data element to a control. • DataGridView uses complex binding because several columns from the table are displayed in the same control. • Simple Binding • Connect one data element to a control. • Connect a textbox to the name column (etc.) • May use Data Bindings property and select the column from the database that should be displayed in control. • Later we’ll build a form using Labels and Textboxes.

  21. Connection String • When the database file is selected in the wizard, the directory path is included with the file name. For example: (E:\CS14\bin\Debug\CSMail.mdb). • If you use a different computer to run the program later, or rename a folder, or move the project, the connection will fail because the drive letter or directory may be different. (There also seems to be a bug…) • Set the connection string at runtime by adding the following command (all on one line) in the Form Load event procedure:customerTableAdapter.Connection.ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CSmail.mdb"; • Make sure to place the database file in the Debug folder.

  22. FormLoad Method Example private void CS13Form_Load(object sender, EventArgs e) { customerTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CSmail.mdb"; this.customerTableAdapter.Fill(this.cSmailDataSet.Customer); }

  23. Execute the Project • Test the form by navigating through the rows and using the scroll bars. • Feel free to make the form and DataGridView control bigger in the design form.

  24. Add Additional Form • To bind a data source to Labels and Textboxes, we’ll need to add another form to our project.Project > Add Windows Form > CS13Form2.cs • On the Data Sources panel, click on the Customers table once to select it. • Click on the drop down arrow, and select Details (see next slide).

  25. Select Details

  26. Create Labels and Textboxes • Click and drag the Customer table on to the form and the Labels and Textboxes are created. • As well as the required Component Tray controls. • Double click on the form and set the connection string.

  27. Change Startup Form • In the Solution Explore, double click on Program.cs (last file listed) and change the form that is displayed to CS13Form2 in the last line of code in the Main method.

  28. Execute the Project • Test the form by navigating through the rows. • That should complete the project .

  29. Summary • Relational Database Terminology • Connecting to a Database • Binding data • DataGridView • Label and Textbox View

More Related