Introduction to LINQ
Download
Report
Transcript Introduction to LINQ
Database
Applications
Visual Basic and Database Files
•VB projects can display and update the data from database files.
•VB .NET uses ADO.NET for database access.
•ADO.NET is the next generation of database technology, based on
Microsoft's previous version, ActiveX Data Objects (ADO).
•An advantage of ADO.Net is that information is stored and transferred
in Extensible Markup Language (XML).
Database Terminology
•Databases
•Tables—can be viewed like a spreadsheet
• Records (rows) — data for one item, person, transaction
• Fields (columns) — store a different element of data
• Key field (unique to each record)
•Relational database
• Multiple tables
• Relationships between the tables
Database Table Example
HTML Example
<html>
<head>
<title>Hello World!</title>
</head>
<body>
<h1>Hello world!</h1>
<p>This is an HTML document</p>
</body>
</html>
5
XML Data (1 of 2)
•Industry-standard format for storing and transferring data
•The XML needed for accessing databases will be automatically
generated in Visual Basic.
•Data stored in XML is text, identified by tags — similar to
HTML tags that can be edited by any text editor program.
•Tags in XML are not predefined as they are in HTML.
•Tags can identify fields by name.
XML Example
<notes>
<note>
<to>Students</to>
<from>Instructor</from>
<heading>Reminder</heading>
<body>Don't forget the exam next week!</body>
</note>
<note></note>
</notes>
7
XML Data (2 of 2)
•In addition to an XML data file, there is normally an XML schema
file.
• Schema describes the fields, data types, and any constraints,
such as required fields.
•ADO.NET validates the data against the schema and checks for
constraint violations.
• Schema is defined with XML tags and can be viewed/edited
in a text editor.
•ADO.NET can treat the XML data as objects, allowing the
IntelliSense feature of the VS.NET environment to provide
information to the programmer.
• Data executes faster.
Using ADO.NET and Visual Basic
•Can display data from a database on
• Windows Form
• Web Form
•Add controls to form and bind data to them.
• Label, TextBox
• Special controls designed just for data such as DataGrid, DataList
The DataGrid View Control
The DataGridView control is bound to a table
in a dataset. The data fields display
automatically in the cells of the grid.
Data Access in Visual Studio
•Allows an easy way to create data-bound controls on a form
•Can drag tables and fields from the window onto a form to
automatically create controls that are bound to the data
•Data can be displayed in grids or individual fields, referred to as
details.
•Can drag a field from the Data Sources window and drop it on
an existing control — causes data binding to be set up
automatically.
•An .xsd file and the schema is added to the Server Explorer
window; Binding Source, Table Adapter, and Data Set objects are
added to the form’s component tray.
Overview of Database Objects
•Binding Source
• Establishes a link to the actual data; a specific file
and/or server
•Table Adapter
• Handles retrieving and updating the data
• Generates SQL statements that are used to access
or update data
•Dataset
•
Contains actual data — may come from multiple
connections and/or multiple data adapters
Steps to Display Data in Bound Controls on a
Form
Binding Sources
•Object establishes a link from a specific file or database to the
program.
•Use a wizard to automatically create Binding Source objects –
OR—
•Add new Binding Source objects using the Data Sources window
or the Data menu.
Table Adapters
•Does all the work of passing data back and forth between a
data source (the binding source) and a program (the
dataset)
•Data does not have to be from a database.
•Data can be text file, object, or an array.
•Transfers data to and from via XML
Datasets
•Temporary set of data stored in memory
•In ADO.NET datasets are disconnected; the copy of data kept in
memory does not keep an active connection to the data source.
•Dataset may contain multiple tables and relationships.
•Any controls bound to the dataset will automatically fill with
data.
The Smart Tag
•Smart Tags are used for many functions including:
• Selecting a data source
• Editing properties of grid columns
• AutoFormating the grid
• Previewing data
•Preview helps users do a better job of designing output.
•Selecting AutoFormat allows the user to choose from many
predefined formats.
Grid with Pre-Defined AutoFormat
Predefined formats can be applied from the
AutoFormat item on the smart tag.
The Database Schema File
•When a new data source is added to a project, a file .xsd is
added to the Solution Explorer — the XML schema definition.
•XML file contains the description and properties of the data.
•The schema shows the names of the table(s) and fields, the
primary keys for each table, and the relationships among the
tables.
•The TableAdapter for the table, which handles different methods
for the table, appears at the bottom of the schema.
.XSD (Schema) File
The .xsd file holds the
schema of the
database, where table
elements, relationships,
and keys can be viewed
and modified.
Binding Individual Data Fields
•Table fields from the dataset can be bound to many types of
controls such as labels, text boxes, combo boxes, and check boxes.
•Controls that are connected to fields are bound controls or databound controls.
•Easiest way to create bound controls is to use the automatic
binding feature of the Data Source window.
• Creates individual text box controls for each field of
data and navigation control allowing the user to more
from one record to another
Data-Bound Text Boxes — Example
Each text box or check
box is bound to one
field from the table. As
the user clicks the
navigation buttons, all
controls change to
display the data for
the next record.
The Data Sources Window
This window is used to add a
new data source by using the
Data Source Configuration
Wizard.
Click the table name to make a
drop-down list available to view
Details.
The table’s icon changes to
match the view.
Selecting Records from a List
•Allows user to select records to display from a list
• Fill a ListBox or ComboBox with values from the database
–OR—
• Display the information in a drop-down list and allow the
user to make a selection; the corresponding data
elements fill remaining fields.
Selecting Fields from the Table
•Select individual fields when creating the new data source –OR– after
creating the data source.
•When creating the data source, choose the Add New Data Source
option and follow the Configuration Wizard; expand the Tables node
and place a check mark on the desired fields.
•To modify after creating, select the dataset and use the Configuration
Wizard and make the field selections from the Wizard.
Data Source Configuration Wizard
Sorting the List Data
•Sort records in a query that selects data for the dataset.
•SQL SELECT statement is generated automatically by the
designer; can be modified.
•Use the Solutions Explorer and the schema file (.xsd
extension) and the Property button (…).
•Choose “Ascending” in the Sort Type cell of the Query Builder.
•Execute the query to preview the output.
Sorting Data
Click the Fill,GetData() entry
in the dataset schema to
display the properties of the
Fill command in the Properties
window.
Select Ascending for the Sort Type
to sort the data by the Title field.
The SQL statement changes to
include an ORDER BY clause.
Choosing the Control Type for Fields
•Dragging a Details view to a form
displays text boxes for the default text
fields.
•Click a field name and choose a control
type in the Data Sources window.
Introduction to
LINQ
Introduction
Large amounts of data are often stored in a database—an
organized collection of data.
A database management system (DBMS) provides mechanisms
for storing, organizing, retrieving and modifying data contained
in the database.
Today’s most popular database systems are relational
databases.
A language called Structured Query Language (SQL)—
pronounced “sequel”—is an international standard used with
relational databases to perform queries (that is, to request
information that satisfies given criteria) and to manipulate data.
Introduction
For years, programs that accessed a relational database passed
SQL queries as Strings to the database management system
then processed the results.
A logical extension of querying and manipulating data in
databases is to perform similar operations on any sources of
data, such as arrays, collections (like the Items collection of a
ListBox) and files.
Microsoft developed LINQ (Language Integrated Query) to
enable you to write query expressions similar to SQL queries
that retrieve information from a wide variety of data sources—
not just relational databases—using a common syntax that is
built into Visual Basic.
Introduction
This enables you to take advantage of the IDE’s IntelliSense feature
when writing LINQ queries—IntelliSense is not provided for SQL
queries.
We use LINQ to Objects in this chapter to query the contents of arrays,
selecting elements that satisfy a set of conditions—this is known as
filtering.
We also use LINQ to Objects to perform common array manipulations
such as sorting an array.
Querying an Array of Reference-Type
Elements Using LINQ
LINQ is not limited to querying arrays of primitive types.
It can be used with most data types.
It cannot be used when a query does not have a defined meaning—for
example, you cannot use Order By on objects that cannot be compared
to one another to determine sorting order.