Transcript Photo Album

Visual C# 2012 How to Program
©1992-2014 by Pearson Education, Inc. All Rights Reserved.



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
2
 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
 To change this, select database file in Solution Explorer and change
its “Copy to Output Directory” property to “Copy if newer”
3

ADO.NET Entity Framework allows interaction with the
DB via classes that the IDE generates from the DB
schema

Two classes generated for each table (Authors and Titles)
 A row class
o name: Author for the Authors Table, Title for Titles Table
o contains properties for each column
 A table class

Relationships between tables (AuthorISBN) are taken into account
when generating classes.
o Author and Title row classes contain navigation properties to access
this data.
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Also generates
 A derived class DbContext (from System.Data.Entity)
 manages data flow between program and DB
 has properties for all the tables to manipulate data in LINQ queries
and in GUIs.
 Any changes made by DbContext can be saved back to the DB
using SaveChanges method
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Works through IQueryable <T> interface
 Which inherits from IEnumerable <T>
 When query executes against DB, results loaded into objects of
the entity data model classes to access in code

Extension methods to IQueryable <T> objects
 First, Any, Count, Distinct, ToArray and ToList (static)
 Can be applied to any object that implements IEnumerable
<T>, such as arrays, collections and results of LINQ to Objects
queries
 This chapter uses LINQ query syntax and LINQ extension
methods
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

This chapter creates one solution
 Several projects
 Reusable class library containing the ADO.NET entity model
 Windows Form app to use it

Little code, IDE provides visual programming
tools and wizards
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Steps to perform a simple query on Books DB.
1. Create ADO.NET entity data model classes to use DB
2. Add the EDM object for Authors table as a data source
3. Drag the Authors table data source onto the Design view to
create a GUI to display the table’s data
4. Add code to the Form’s code-behind file to allow app to
interact with the DB
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
Created when data source representing Authors table is dragged onto the form
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.






Create a class library project named BooksExamples
Delete the Class1.cs file
Right-click BooksExamples project in Solution
Explorer, Add New Item
From Data category, select ADO.NET EDM and name
it BooksModel.edmx
Click Add
A Wizard displays
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.


Select Generate from Database
Click Next
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
Click New Connection
◦ If Choose Data Source Dialog appears, select Microsoft SQL
Server, click OK (works for SQL Server Express, too)
◦ If it doesn’t have in the Data source, Click Change…

Click browse
◦ Locate the Books.mdf file


Test Connection
Click OK
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

The Entity connection string is displayed
◦ Information that ADO requires to connect to the DB at runtime



Click Next
Another dialog will appear
Click Yes
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Select the parts of the DB you want to use
◦ Select Tables node
◦ Click Finish
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

IDE creates the EDM and displays diagram in model
designer
◦ Notice IDE rename Title column  Title1 to avoid naming
conflict with class Title that represents a row.
◦ Notice the Navigation Properties
 Connects an author to all titles written by that author
 Connects a title to all authors its authors

Build the class library
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Add a Windows Forms application to this project
◦
◦
◦
◦


Add > New Project > Windows Forms application
Name it DisplayTable
Click OK
Rename Form1.cs to DisplayAuthorsTable.cs
Set Form’s Text property to Display Authors Table
Configure solution
◦ Right click DisplayTable, Set as Startup Project



Right click DisplayTable project’s References node
Select Add Reference
In reference Manager, select Solution to display other
projects, select BooksExamples and click OK
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Add reference to System.Data.Entity
◦
◦
◦
◦
Right click DisplayTable project’s References node to project
Select Add Reference
In reference Manager, select Assemblies
Locate System.Data.Entity
 Ensure checkbox is checked
 Click ok

Add reference to EntityFramework to Solution
◦
◦
◦
◦
◦
Right click Solution name
Select Mange NuGetPackages for Solution
Click Manage
Select DisplayTable project, click OK
Click Close
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Add a Windows Forms application to this project
◦
◦
◦
◦


Add > New Project > Windows Forms application
Name it DisplayTable
Click OK
Rename Form1.cs to DisplayAuthorsTable.cs
Set Form’s Text property to Display Authors Table
Configure solution
◦ Right click DisplayTable, Set as Startup Project
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Add the Connection String to the Window Forms App
◦ The connection string is stored in the BooksExamples class
library’s App.Config file
◦ Open that file
◦ Copy lines
 <connectionStrings>
……
</connectionStrings>
◦ Now open theApp.Config file in the DisplayTable project and
paste lines after the line containing </entityFramework>
◦ Save the file
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.






Select VIEW>Other Windows>Data Sources
Click Add New Data Source and Wizard comes up
Select Object data source
Click Next
Ensure Author is
checked
Click finish
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.



In the Data Sources Window, now see Author class
Also see Properties (Columns for the row class)
Also see Titles navigation property
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.


Switch to Design view for DisplayAuthorsTable class
Click the Author node in Data Sources window
◦ Changes to a drop-down list, ensure DataGridView option
(default) is selected
◦ Drag the Author node onto the Form
◦ Resize the Form
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
Handles transfer of data between data source and GUI
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.


Select the DataGridView control, change its Dock
property to Fill
Click Edit Columns… in property window
◦ Select Titles, click Remove (not used in this project example)

Final step, connect data source to authorBindingSource
◦ The code is shown next
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
Class BooksEntities derived from
DbContext – accesses DB
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
Double click Form
Data moves between DbContext
and database using LINQ to Entities
Extension methods
Changes made to data in GUI
tracked by DbContext to save
later to DB
Right click Save Data Button, Select Enabled, Double click it
Determine if data entered in GUI is valid
Save pending changes to BooksEntities model in memory
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
Store changes to DB
If data is empty, throws exception
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.

Add a new Windows Forms Application project named
DisplayQueryResult
 This app performs queries and displays results in DataGridView
 Only reads data from the EDM, so buttons to add and delete
records are disabled.
 User selects a query from a ComboBox



Rename the Form1.cs file to TitleQueries.cs
Set Form’s Text property to Display Query Results
Set the DisplayQueryResult project as the startup project
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.


Create the DataSource
Create DataGridView
 Set Dock property to Fill




Select the Title class (rather than Author)
Drag Title node from the Data Sources Window to the form.
Remove the Authors column from the DataGridView
Add a ComboBox name queriesComboBox below the
DataGridView on the form
 Set its Dock property to Bottom
 Open ComboBox’s String Collection Editor
add All titles, Titles with 2014 copyright, Titles ending with “How to
Program”
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
Create Load Handler
Programmatically select
query
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.
©1992-2014 by Pearson Education, Inc. All
Rights Reserved.