How to work with data sources and datasets

How to work with data sources and datasets

How to work with data sources and datasets Based on Murach (ch 17, 18) 1 Applied Use a data source to get the data that an application requires.

Use a DataGridView control to present the data thats retrieved by a data source. Use other controls like text boxes to present the data thats retrieved by a data source. Write the code for handling any data errors that result from the use of the data source or the controls that are bound to it.

Use the Dataset Designer to (1) view the schema for the dataset of a data source (2) modify a query using the Query Builder (3) preview the data for a query or (4) review the SQL statements that are generated for a data source. 2 Objectives

Knowledge Describe the use of a connection string in an app.config file. Describe the use of the Fill method of the TableAdapter object

and the UpdateAll method of the TableAdapterManager object. Describe the use of the EndEdit method of the BindingSource object. Describe the two categories of data errors that can occur when you run an application that uses a data source. Describe the use of the DataError event for a DataGridView control. In general terms, describe the way the SQL statements that are generated for a data source (1) prevent concurrency errors (2) refresh a dataset when the database generates the keys for new rows.

3 Visual Studio CE 2015 Database (When the Book published) MS SQL Server 2014 Express LocalDB compatible with SQL Server 2014 Editions: https://

docs.microsoft.com/en-us/sql/sql-server/editions-and-com ponents-of-sql-server-2017?view=sql-server-2017 http://www.sqlservercentral.com/blogs/waterox-sql/2014/0 4/22/2014-sql-server-editions-overview/ https:// docs.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-a 4 Slides are Almost Recorded snapshots

Starting point. Where is the stuff? C:\Murach\C#2015 C:\Murach\C#2015\Database\MMABooks.mdf copied from ch. 23 5 First DB Configuration 6

Basic ActiveX Data Object (ADO.NET ) components (ch 17) A P P L I C A T I O N

Data used by apps MS SQL Oracle DB2 Excel 7 Different Presentation

From http://www.siue.edu/ ~dbock/cmis142/WebNotes/Ch10Notes/10-databases_files/image006.jpg From https://i.ytimg.com/vi/ifvxhQy13u8/maxresdefault.jpg 8 An empty Data Sources window View->Other Windows Data Sources No DataSources If you are lost navigating + W, R

9 Data Source (DS) The source of the data for an application. Mostly database origin (Express LocalDB) IF you are using full version Express, see appendix A (not for this class)*

Add the DataSource 1. Add New Data Source link (when no DS available) 2. 3. 10 Step 1. The Data Source Configuration Wizard Depending on installed components

Click Next 11 Step 2. Select Dataset (only available option) VS generates dataset from the database Could be entity data model (not included) 12 Choosing a Data Connection New Connection

13 MMABooks.mdf Database file name: C:\Murach\C#2015\Database\MMABooks.mdf 14 Upgrade DB if asked 15 Step 2. Choose data connection

Data Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Murach\ C#2015\Database\MMABooks.mdf;Integrated Security=True;Connect Timeout=30 16 Copy to Output Directory Property https://code.msdn.microsoft.com/windowsdesktop/Working-with-Copy-to-11fbc251

DataDirectory refers to the output directory for the project. The output directory is the directory where the executable le for the application is stored. By default, a database le thats included in your project is copied to the output directory for the project every time the project is built. Then, when you run the application, the application works with the copy of

the database le in the output directory. That means that any changes you make to the database arent applied to the database le in the project directory. And each time you build the application, the database in the output directory is overwritten by the unchanged database in the project directory so youre back to the original version of the database. If you want to Change the way this works, you can select the database le in the Solution Explorer and change its Copy to Output Directory property from Copy always to Copy if newer. Then, the database le in the output directory wont be overwritten unless the database le in the project directory contains more current data. 17

Data Connection 18 Connection String 19 Step 2. Choose data connection (cont.) 20 Step 3. Choose data connection (cont.)

Test Connection 21 Step 3. Choose data connection (cont.) Include (Yes) Recommended for a single user 22 Where is database? 23

Where is database? g o L if les Copy if newer 24

App.config When included

connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=| DataDirectory|\MMABooks.mdf;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" /> Can be higher 25 Step 4. Choose data connection (cont.)

Connection String 26 App.config 27 App.config attributes

28 Step 5. Last Step. Choose Your Database Objects: tables, views, stored procedures, functions etc. Defined with default value (0), omit it. 29

How to work with columns that have default values Omit columns with default values from the dataset unless theyre needed by the application. Provide values for those columns whenever a row is added to the

dataset. Note that although this application will allow data to be added to the Products table, the OnHandQuantity column can be omitted because its dened with a default value in the database. So when a new row is added to the database, the database will set this column to its default value. If you include a column with a default value in a dataset, you need to realize hat this value isnt assigned to the column in the dataset, even though the dataset enforces the constraints for that column. For instance, the OnHandQuantity column in the MMABooks database has a default value of zero and doesnt allow nulls. But if you include this column in the dataset, its denition will have a default value of null and wont allow nulls. As a result, an exception will be thrown whenever a new row is added to the dataset with a null value for the OnHandQuantity column.

30 Done Dataset schema file (xsd) Dataset class 31 Generated MMABooksDataSet.Designer.cs (top) : tables, columns, constraints //-----------------------------------------------------------------------------//

// This code was generated by a tool. // Runtime Version:4.0.30319.42000 // // Changes to this file may cause incorrect behavior and will be lost if

// the code is regenerated. Do not change it // //-----------------------------------------------------------------------------#pragma warning disable 1591 namespace ProductMaintenance { ///

///Represents a strongly typed in-memory cache of data. ///

[global::System.Serializable()] [global::System.ComponentModel.DesignerCategoryAttribute("code")] [global::System.ComponentModel.ToolboxItem(true)] [global::System.Xml.Serialization.XmlSchemaProviderAttribute("GetTypedDataSetSchema")] [global::System.Xml.Serialization.XmlRootAttribute("MMABooksDataSet")] [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.DataSet")] public partial class MMABooksDataSet : global::System.Data.DataSet { private ProductsDataTable tableProducts; private global::System.Data.SchemaSerializationMode _schemaSerializationMode = global::System.Data.SchemaSerializationMode.IncludeSchema; [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")] public MMABooksDataSet() {

32 Visual (double click) Generated MMABooksDataSet.xsd (top) : tables, columns 33 More details

In addition to this data source, Visual Studio generates a le that contains the schema for the DataSet class. This le denes the structure of the dataset, including the tables it contains, the columns that are included in each table, the data types of each column, and the constraints that are dened for each table. It is listed in the Solution Explorer window and is given the same name you specied for the dataset in the last step of the Data Source Conguration Wizard with a le extension of xsd. You can view a graphic representation of this schema by double-clicking on this le. Beneath the schema le, the Solution Explorer displays the le that contains the

generated code for the DataSet class. In this gure, this code is stored in the MMABooksDataSet.Designer.cs le. When you create bound controls from the data source, the code in this class is used to dene the DataSet object that the controls are bound to. Although you may want to View this code to see how it works, you shouldnt change it. If you do, the dataset may not work correctly. By the way, you should know that a dataset thats created from a dataset class like the one shown here is called a typed dataset. The code in the dataset class makes it possible for you to refer to the tables, rows, and columns in the typed dataset using the simplied syntax. In contrast, when you use an untyped dataset, you have to refer to the tables, columns, and rows through the collections that contain them. 34

Typed and untyped datasets Our case is typed only https://msdn.microsoft.com/enus/library/mt710792.aspx 35 Next Step is Binding: How To Use a Data

Source Once a data source is created, you can bind controls to the data source and then use the bound controls to add, update, delete data. Ex.: DataGridView TextBox

36 Binding a DataGridView using Drag-and-Drop Need Form Designer View DataGridView default 37

Drag and Drop Products 38 The controls and objects that are created when you drag a data source to a form Complex data binding 39 Drag-and-drop: Behind the curtain

VS adds a DataGridView control to the form and binds it to the table. This creates a DataGridView control that lets you browse all the rows in the table as well as add, update, and delete rows in the table. For that, VS adds a toolbar to that provides navigation buttons along with Add, Delete, and Save buttons. To bind a DataGridView control to a table, VS uses complex data binding. This means that

the bound control is bound to more than one data element. The DataGridView control, is bound to all the rows and columns in the Products table. When you generate a DataGridView control from a data source, VS also adds 5 additional objects to the Component Designer tray at the bottom of the Form Designer. 1: DataSet object denes the dataset that contains the Products table. 2: TableAdapter object provides commands that can be used to work with the Products table in the database. TableAdapter object is similar to the DataAdapter object. However, It can only be created by a designer.

3: TableAdapterManager provides for writing the data in two or more related tables to the database so that referential integrity is maintained. 4: BindingSource object species the data source (the Products table) that the controls are bound to, and it provides functionality for working with the data source. 5: BindingNavigator denes the toolbar that contains the controls for working with the data source. 40 Run it. No single line of code! Sortable

41 The code thats generated by Visual Studio namespace ProductMaintenance { public partial class Form1 : Form { . . . private void productsBindingNavigatorSaveItem_Click(object sender, EventArgs e) { this.Validate(); this.productsBindingSource.EndEdit();

this.tableAdapterManager.UpdateAll(this.mMABooksDataSet); } private void Form1_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the 'mMABooksDataSet.Products' table. //You can move, or remove it, as needed. this.productsTableAdapter.Fill(this.mMABooksDataSet.Products); } } } Generated Fill method of TableAdabter Reads Data from the database42

The syntax of the Fill and UpdateAll methods tableAdapter.Fill(dataSet.TableName) tableAdapterManager.UpdateAll(dataSet) When the user changes the data in DataGridView control, those changes saved in dataset automatically, but not in database.

On Save (button click) the data is saved in database. Generated UpdateAll method of TableAdabterManager Writes Data to the database 43 The syntax of the Fill and UpdateAll methods cont. (code w/o exception handling) private void productsBindingNavigatorSaveItem_Click(object sender, EventArgs e) { this.Validate(); //not used in Marach examples this.productsBindingSource.EndEdit(); //applies any pending

//changes //saved data in the database this.tableAdapterManager.UpdateAll(this.mMABooksDataSet); } 44 How to change the default control for a data table Remove Products Control and DataSource (via Solution Explorer)

Right Click on Solution Explorer and select DataSource and Delete Add another data source control (Customers) As it was before: Next->Next->Next 45 Select Customers Table 46

Select Details (DataGridView is Default) 47 Mapping Columns to Controls (most strings -> TextBox) How to change the default control for a column in a data table 48 DetailsDragand-drop Customer Table

49 Run it (one record at a time) 50 Customize it Read-only 51 Form Code (almost identical To DataGridView)

private void customersBindingNavigatorSaveItem_Click(object sender, EventArgs e) { this.Validate(); this.customersBindingSource.EndEdit() this.tableAdapterManager.UpdateAll(this.mMABooksDataSet); } private void Form1_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the 'mMABooksDataSet.Customers' table. // You can move, or remove it, as needed. this.customersTableAdapter.Fill(this.mMABooksDataSet.Customers);

} 52 Error Handling Many things can go wrong: Database shutdown Network is broken Database Table is not accessible 53

54 Code that catches a SQL exception using System.Data.SqlException private void Form1_Load(object sender, EventArgs e) { try { this.customersTableAdapter.Fill( this.mMABooksDataSet.Customers); } catch (SqlException ex)

{ MessageBox.Show("Database error # " + ex.Number + ": " + ex.Message, ex.GetType().ToString()); } } 55 ADO.NET Errors 56 Concurrency Error Details

Update and Delete statements that are generated for a table adapter contain code that checks that a row hasnt changed since it was retrieved. But if the row has changed, the row with the specied criteria wont be found and the SQL statement wont be executed. When the table adapter discovers that the row wasnt updated or deleted, however, it realizes there was a concurrency error and throws an exception.

57 Code that handles ADO.NET errors try { this.customersBindingSource.EndEdit(); this.tableAdapterManager.UpdateAll(this.mMABooksDataSet); } catch (DBConcurrencyException) { MessageBox.Show("A concurrency error occurred. " + "Some rows were not updated.", "Concurrency Exception");

this.customersTableAdapter.Fill(this.mMABooksDataSet.Customers); } catch (DataException ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); customersBindingSource.CancelEdit(); } catch (SqlException ex) { MessageBox.Show("Database error # " + ex.Number + ": " + ex.Message, ex.GetType().ToString()); } 58

DataGridControl Errors can detect some error BEFORE the data is saved. DataGridControl Ex.: required column data is not provided 59 Code that handles a data error

for a DataGridView control private void productsDataGridView_DataError( object sender, DataGridViewDataErrorEventArgs e) { int row = e.RowIndex + 1; string errorMessage = "A data error occurred.\n" + "Row: " + row + "\n" + "Error: " + e.Exception.Message; MessageBox.Show(errorMessage, "Data Error"); } 60

The schema displayed in the Dataset Designer Double click 61 The schema displayed in the Dataset Designer 62 Query Builder Custom Query (sorting, joins)

63 64 65 Diagram Pane Grid Pane SQL Pane Result Pane

66 Preview Data Right click 67 Preview Data cont click

68 Preview Data Preview 69 SQL that retrieves customer rows SELECT FROM

CustomerID, Name, Address, City, State, ZipCode Customers SQL that inserts a customer row and refreshes the dataset INSERT INTO Customers (Name, Address, City, State, ZipCode) VALUES(@Name, @Address, @City, @State, @ZipCode); SELECT ZipCode FROM WHERE

CustomerID, Name, Address, City, State, Customers (CustomerID = SCOPE_IDENTITY()) 70 SQL that updates a customer row and refreshes the dataset UPDATE Customers SET Name = @Name, Address = @Address, City = @City,

State = @State, ZipCode = @ZipCode WHERE ( (CustomerID = @Original_CustomerID) AND (Name = @Original_Name) AND (Address = @Original_Address) AND (City = @Original_City) AND (State = @Original_State) AND (ZipCode = @Original_ZipCode) ); SELECT CustomerID, Name, Address, City, State, ZipCode FROM Customers

WHERE (CustomerID = @CustomerID) 71 SQL that deletes a customer row DELETE FROM Customers WHERE (CustomerID = @Original_CustomerID) AND (Name = @Original_Name) AND (Address = @Original_Address) AND (City = @Original_City) AND (State = @Original_State) AND (ZipCode = @Original_ZipCode)

72

Recently Viewed Presentations

  • Security - nytaxaide.org

    Security - nytaxaide.org

    Security. This year in the AARP Foundation Tax-Aide program there were: Two(2)confirmed laptops reported stolen/lost. No data was on the computers. TaxWise Online no data stored on computers. Many state laws do not require notification when computers and/or devices are...
  • Well Behaved Women Rarely Make History Colonial women

    Well Behaved Women Rarely Make History Colonial women

    One of her parish elders noted Anne Richards was a "woman of an honest life and conversation . . . and so is and ever hathebynne esteemed," Allice Burges, at 28, was one of the oldest and said to be...
  • West Midlands Information Literacy Passport: mapped to Knowledge

    West Midlands Information Literacy Passport: mapped to Knowledge

    West Midlands NHS Improving patient care: meeting the NHS challenge to improve quality and productivity, through innovation in Library and Knowledge Services. A West Midlands Framework for Library and Knowledge Services 2010-2015. 2010 Department of Health National Service Framework of...
  • Chapter 1 - Intro to Matter

    Chapter 1 - Intro to Matter

    III. Key Concepts. Pure substances, such as pure water and table salt, are always the same - regardless of where they come from. Physical properties can be observed without changing the substance; to find chemical properties, you must try to...
  • 48x36 Poster Template - City University of New York

    48x36 Poster Template - City University of New York

    The Brooklyn College Freshman Health Initiative is the first step in a comprehensive assessment of the health of CUNY students, and the first to objectively assess the health status of students from a diverse, urban commuter college.
  • Agriculture Scientist - Sam Houston State University

    Agriculture Scientist - Sam Houston State University

    The cotton gin was a groundbreaking invention in the southern United States, and it had an enormous positive impact on the economies of the southern states in the U.S. The cotton gin also had an equally important impact on slavery...
  • Presentación de PowerPoint

    Presentación de PowerPoint

    These improvements in development and adult success have implications for public expenditures resulting in cost savings in education, social services, the criminal justice system, and health care. Of course, it is not just the government cost savings that are important,...
  • Friction, Conduction and Induction

    Friction, Conduction and Induction

    You can charge an object through: Friction - the transfer of electrons from one object to the other by rubbing them together. Conduction- by having two objects TOUCH each other and transfer electrons from one object to the next. Induction-...