csci 3328 object oriented programming in c# chapter 12: databases and linq 1 xiang lian the...
TRANSCRIPT
![Page 1: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/1.jpg)
CSCI 3328 Object Oriented CSCI 3328 Object Oriented Programming in C# Programming in C#
Chapter 12: Databases and LINQChapter 12: Databases and LINQ
1
Xiang Lian
The University of Texas – Pan American
Edinburg, TX 78539
![Page 2: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/2.jpg)
Objectives
• In this chapter, you will– Learn the relational database model– Get familiar with using LINQ to retrieve and
manipulate data from a database– Know how to add data sources to project, create
LINQ to SQL classes, and use data binding to move data between GUI controls and databases
– Learn how to create Master/Detail views to select a record and display its details
2
![Page 3: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/3.jpg)
Introduction
• A database is an organized collection of data• A database management system (DBMS)
provides mechanisms for storing, organizing, retrieving and modifying data
• Existing DBMSs– Microsoft SQL Server– Oracle– Sybase– IBM DB2
3
![Page 4: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/4.jpg)
Introduction (cont'd)
• PostgreSQL and MySQL are popular open-source DBMSs that can be downloaded and used freely by anyone
• We use Microsoft’s free SQL Server Express, which is installed with Visual Studio
• It can also be downloaded separately from Microsoft (www.microsoft.com/express/sql)
4
![Page 5: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/5.jpg)
Relational Database
• A relational database organizes data simply in tables– rows (also called records)– columns (also called fields, attributes)
• Primary key: a column (or group of columns) requiring a unique value that cannot be duplicated in other rows
• A primary key composed of two or more columns is known as a composite key
• Foreign key—a column in this table that matches the primary-key column in another table
5
![Page 6: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/6.jpg)
Example of Table: Employees
6
![Page 7: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/7.jpg)
SQL
• A program might select data from the table to create a query result – E.g., to retrieve the location of each department, in
increasing order by Department number– SQL:
• SELECT DISTINCT Department, Location
FROM Employees
ORDER BY Department
7
![Page 8: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/8.jpg)
SQL Results
8
![Page 9: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/9.jpg)
Schema
• A database may contain one or multiple tables• A database’s tables, their fields and the
relationships among them are collectively known as a database schema
• In SQL Server, database files have the *.mdf (“master data file”) file-name extension– Books.mdf– http://media.pearsoncmg.com/ph/esm/deitel/vcs2008ht
p3e/examples/
– Chapter 219
![Page 10: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/10.jpg)
Entity-Relationship Model
• Entity-Relationship (ER) model– Entity
• Authors
• Titles
– Relationship• There is a one-to-many relationship between a primary
key and a corresponding foreign key– E.g., one author can write many books and one book can be
written by many authors
• Others: many-to-many or one-to-one relationship
• E.g., AuthorISBN10
![Page 11: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/11.jpg)
Books Database
• Books database consists of 3 tables– Authors– Titles– AuthorISBN
11
![Page 12: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/12.jpg)
Example of ER Diagram
12
Books database
![Page 13: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/13.jpg)
Authors
13
![Page 14: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/14.jpg)
Titles
14
![Page 15: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/15.jpg)
AuthorISBN
15
![Page 16: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/16.jpg)
SQL on Books Database
• SELECT * FROM tableName– SELECT * FROM Authors
– SELECT AuthorID, LastName FROM Authors
• SELECT columnName1, columnName2, … FROM tableName WHERE criteria– SELECT BookTitle, EditionNumber, Copyright
FROM Titles
WHERE Copyright > '2011'
16
![Page 17: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/17.jpg)
SQL on Books Database (cont'd)
• Operator LIKE is used for pattern matching– Wildcard character
• Percent (%): zero or more characters
• Underscore (_): a single wildcard character
– SELECT AuthorID, FirstName, LastName
FROM Authors
WHERE LastName LIKE 'D%'
– SELECT AuthorID, FirstName, LastName
FROM Authors
WHERE LastName LIKE '_y%'
17
Deitel
Ayer
![Page 18: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/18.jpg)
SQL on Books Database (cont'd)
• SELECT columnName1, columnName2, … FROM tableName ORDER BY column ASC– ASC– DESC– SELECT AuthorID, FirstName, LastName FROM Authors ORDER BY LastName DESC
• Other statements in SQL– table1 INNER JOIN table2 ON table1.columnName=table2.columnName– INSERT INTO tableName (columnName1, columnName2, … ) VALUES (value1,
value2, …)– UPDATE tableName SET columnName1 = value1, columnName2 = value2, …
WHERE criteria– DELETE FROM tableName WHERE criteria
18
![Page 19: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/19.jpg)
Querying a Database with LINQ
• Connect to the Books database• Create the LINQ to SQL classes required to
use the database• Add the Authors table as a data source• Drag the Authors table data source onto the
Design view to create a GUI for displaying the table’s data
• Add a few statements to the program to allow it to interact with the database
19
![Page 20: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/20.jpg)
GUI To Display Authors Table
20
![Page 21: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/21.jpg)
Creating LINQ to SQL Classes
• Step 1: Creating the project– Create a new Windows Forms Application named
DisplayTable– Change the name of the source file to
DisplayAuthorsTable.cs– The IDE updates the Form’s class name to match
the source file– Set the Form’s Text property to "Display Table"
21
![Page 22: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/22.jpg)
Creating LINQ to SQL Classes (cont'd)
• Step 2: Adding a database to the project and connecting to the database– To interact with a database, you must create a
connection to the database in the Visual Studio• Select View > Server Explorer to display the Server
Explorer, and click Connect to Database icon button• Or, select Tools > Connect to Database…• In Choose Data Source dialog, select Microsoft SQL
Server Database File from the Data source• If you check Always use this selection CheckBox, the
IDE will use this type of database file by default when you connect to databases
• Click Continue to display the Add Connection dialog22
![Page 23: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/23.jpg)
Choose Data Source Dialog
23
![Page 24: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/24.jpg)
Creating LINQ to SQL Classes (cont'd)
• In the Add Connection dialog– click the Change… Button to select a database– click Browse… Button to locate and select the
Books.mdf file in the Databases directory– click Test Connection Button to verify that the
IDE can connect to the database through SQL Server Express
– click OK to create the connection
24
![Page 25: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/25.jpg)
Add Connection Dialog
25
![Page 26: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/26.jpg)
Creating LINQ to SQL Classes (cont'd)
• Step 3: Generating the LINQ to SQL classes– Select the database from which the LINQ to SQL
classes will be created– LINQ to SQL uses the database’s schema to help
define the classes– Right click the project name in the Solution
Explorer and select Add > New Item… to display the Add New Item dialog
• Select the LINQ to SQL Classes template, name the new item Books.dbml and click the Add button
– The Object Relational Designer window will appear• Double click the Books.dbml file in the Solution
Explorer to open the Object Relational Designer26
![Page 27: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/27.jpg)
Object Relational Designer
27
![Page 28: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/28.jpg)
Creating LINQ to SQL Classes (cont'd)
• Expand the Books.mdf database node in the Server Explorer
• Expand the Tables node• Drag the Authors, Titles and AuthorISBN tables onto
the Object Relational Designer• The IDE prompts whether you want to copy the
database to the project directory• Select Yes
– The Object Relational Designer will display the tables that you dragged from the Server Explorer
• Save the Books.dbml file28
![Page 29: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/29.jpg)
Object Relational Designer (cont'd)
29
![Page 30: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/30.jpg)
Recall: Querying a Database with LINQ
• Connect to the Books database• Create the LINQ to SQL classes required to
use the database• Add the Authors table as a data source• Drag the Authors table data source onto the
Design view to create a GUI for displaying the table’s data
• Add a few statements to the program to allow it to interact with the database
30
![Page 31: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/31.jpg)
Data Bindings Between Controls and the LINQ to SQL Classes
• Step 1: Adding the Authors LINQ to SQL class as a data source– To use the LINQ to SQL classes for data binding, you
must first add them as a data source• Select Data > Add New Data Source… to display the Data
Source Configuration Wizard• The LINQ to SQL classes are used to create objects
representing the tables in the database, so we will use an Object data source. In the dialog, select Object and click Next >
• Expand the tree view and ensure that Author is checked. An object of this class will be used as the data source
• Click Finish31
![Page 32: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/32.jpg)
32
![Page 33: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/33.jpg)
Data Bindings Between Controls and the LINQ to SQL Classes
(cont'd)• The Authors table in the database is now a
data source that can be used by the bindings
• Open the Data Sources window by selecting Data > Show Data Sources
33
![Page 34: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/34.jpg)
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)
• Step 2: Creating GUI elements– Use the Design view to create a GUI control that can
display the Author table’s data• Switch to Design view for the DisplayAuthorsTable class
• Click the Author node in the Data Sources window—it should change to a drop-down list.
– Open the drop-down by clicking the down arrow – Select DataGridView option—this is the GUI control that will be
used to display and interact with the data.
• Drag the Author node from the Data Sources window onto the Form in Design view
34
![Page 35: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/35.jpg)
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)
• To make the DataGridView occupy the entire window– select the DataGridView
– use the Properties window to set the Dock property to Fill
35
![Page 36: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/36.jpg)
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)
• Step 3: Connecting the BooksDataContext to the AuthorBindingSource– The final step is to connect the
BooksDataContext to the AuthorBindingSource, so that the application can interact with the database
– Obtain data from the database and to save any changes that the user makes to the data back into the database (see next slide)
36
![Page 37: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/37.jpg)
37
![Page 38: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/38.jpg)
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)
• Creating BooksDataContext– private BooksDataContext database = new
BooksDataContext();
• Connection between BooksDataContext and AuthorBindingSource– authorBindingSource.DataSource = from author in
database.Authors orderby author.AuthorID select author;
38
![Page 39: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/39.jpg)
Data Bindings Between Controls and the LINQ to SQL Classes (cont'd)
• Step 4: Saving Modifications Back to the Database– If the user modifies the data in the DataGridView,
we’d also like to save the modifications in the database– By default, the BindingNavigator’s Save Data Button
is disabled– To enable it, right click this Button’s icon and select
Enabled to true– Then, double click the icon to create its Click event
handler– Saving the data entered into the DataGridView back to
the database is a three-step process
39
![Page 40: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/40.jpg)
• Validate();• authorBindingSource.EndEdit();• database.SubmitChanges();
40
click event
![Page 41: CSCI 3328 Object Oriented Programming in C# Chapter 12: Databases and LINQ 1 Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa.edu](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d8a5503460f94a71568/html5/thumbnails/41.jpg)
41