1 / 26

Chapter 18 - Data sources and datasets

Chapter 18 - Data sources and datasets. Outline How to create a data source How to use a data source How to use Query Builder to build a simple query How to use the Dataset Designer How to generate detail controls from a data source How to use Query Builder to build more complicated

gaille
Download Presentation

Chapter 18 - Data sources and datasets

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 18 - Data sources and datasets OutlineHow to create a data source How to use a data source How to use Query Builder to build a simple query How to use the Dataset Designer How to generate detail controls from a data source How to use Query Builder to build more complicated parameterized queries

  2. How to create a data source • Data Source • specifies the source of the data for the application • most apps get their data from a database • Data source window shows all tables and columns in the dataset that are available to the application. • display by clicking on Data Sources tab at left edge • display by selecting Data > Show Data Sources • If no data sources available, click on Add New Data Source link. • Starts the Data Source Configuration Wizard • Actually 4 different ways to start this Wizard (pg. 544)

  3. display by selecting • Data > Show Data Sources

  4. If you add a database file to your project • By default, that file is copied to the output directory for the project every time the project is built • When you run the app, it works with the copy of the database file in the output directory • Any changes made to the database aren’t applied to the database file in the project directory • Each time you rebuild, database in output directory is overwritten • Change this , by selecting database file in Solution Explorer and change its “Copy to Output Directory” property to “Copy if newer”

  5. Choosing the connection for the data source • Can choose from a previously defined connection • Or click New Connection • Click the Connection string button to make sure you have the right connection

  6. Creating a connection to a database • How you do this differs for • SQL Server Express (default) • Microsoft Access • using a database server running on a network server • etc. • WE ARE USING MICROSOFT ACCESS – NO WHINING!!! • Change the data source to Microsoft Access Database File • Enter the database filename • Click Test Connection to be sure connection is configured properly • Save the Connection string in the app configuration file • Now table adapters that use the connection can refer to the connection string by name

  7. Choosing database objects for a data source • Last step of the Data Source Configuration Wizard • Choose any tables, views, stored procedures, functions available from database • Can expand nodes and just choose columns • Can also enter the name you want to use for the dataset • Default: databasename + “DataSet” • NOTE: selection of several tables in the dataset, maintains the relationships between those tables

  8. Schemafile • After completing Data Source Configuration Wizard • new data source is displayed in Data Sources Window • generates a file that contains the schema for the DataSet class • defines the structure of the dataset, incl. tables and columns it contains, data types, and constraints • listed in Solution Explorer Window with extension .xsd • double- click it to view graphic representation • generated code is below it • when you create bound controls from the data source, the code in this class is used to define the DataSet object that the controls are bound to – DO NOT CHANGE THIS CODE!

  9. Schema file, generated code file for DataSet

  10. How to use a data source • Bind controls to the data source and then use the bound controls to add, update, and delete the data in the datasource. • We’ll use DataGridViewand TextBoxcontrols in our examples. • Steps • Drag a table from the Data Sources Window onto a form • adds a DataGridViewcontrol to the form and binds it to the table - allows browsing of rows, as well as add, update and delete rows (complex data binding) • adds five more objects to the Component Designer tray

  11. Objects in Component Designer Tray • DataSet– defines the dataset that contains the Authors table • TableAdapter– provides command that can be used to work with the Authors table • TableAdapterManager– provides for writing the data in two or more related tables to the database, so referential integrity is maintained • BindingSource - specifies the data source (the Authors table) that the controls are bound to and provides functionality for working with the data source • BindingNavigator – defines the toolbar that contains the controls for working with the data structure

  12. Generated code • When application starts, the first event handler is executed • it uses the Fill method of the TableAdapter object to load data into the DataSet object • because the DataGridView control is bound to this table, the data is displayed in this control private void Form1_Load(object sender, EventArgs e) { this.authorsTableAdapter.Fill(this.booksDataSet.Authors); }

  13. Generated code • When user changes the data in the DataGridView control • changes are saved back to the dataset • changes are not saved to database until user clicks the Save button • calls Validate method • calls EndEdit method of the BindingSource – apply any pending changes to the dataset (added/updated rows are not saved until you move to another row • calls UpdateAll of the TableAdapterManager – saves the data in the DataSet object to the database (only updates rows that need updating maintaining referential integrity)

  14. Generated code user clicks the Save button private void authorsBindingNavigatorSaveItem_Click(object sender, EventArgs e) { this.Validate(); this.authorsBindingSource.EndEdit(); this.tableAdapterManager.UpdateAll(this.booksDataSet); }

  15. How to use Query Builder • DataSourceConfigurationWizarddoesn’t give you enough flexibility, e.g., can’t join data or sort • QueryBuilderprovides a graphical interface that you can use to modify a Select statement without knowing proper SQLsyntax • Steps: • Select the table adapter that contains the query • Right click Add query, • Change query name if a new query • Click Query Builder

  16. Type new name for query Click Query Builder

  17. Diagram Pane right click to add tables to pane Grid Pane shows all columns selected SQL Pane Results Pane

  18. How to use the Dataset Designer • Lets you work with a dataset schema using a graphic interface • To view the schema – double-click on the schema file for the dataset (.xsd file) • Properties you can view • table adapter, query, columns in a table • Example: Fix the autoincrement of the authorID column in the Authors table

  19. Generating detail controls from a data source • DataGridViewmay not be appropriate for app • Can bind columns of data source to individual controls (simple data binding) • Select Details option from drop-down list when you select the table in Data Sources Window • Drag the table onto the form • Generates a label and a bound control for each column • most string/numeric columns  TextBox • Change to other appropriate types by selecting column in Data Sources Window and the select a different type of control from drop-down list

More Related