Display Authors Table App

Hello,

I need an app made in Visual Studio Basic doing a Windows Form Application. In the code also needs to be basic comments on what certain parts of the code are doing. The instructions of the app needing to be created is below. This project is a little confusing. I supplied the 12.5 sample. As well as ll the data needed for the App in a word document.

Thank you in advance!

Fig. 12.3 Authors table of the Books database.
Column
Description
AuthorID
Author’s ID number in the database. In the Books database, this integer column is defined as an identity column, also known as
anautoincremented column— for each row inserted in the table, the AuthorID value is increased by 1 automatically to ensure that
each row has a unique AuthorID. This is the primary key.
FirstName
Author’s first name (a string).
LastName
Author’s last name (a string).
Fig. 12.4 Data from the Authors table of the Books database.
AuthorID
FirstName
LastName
1
Paul
Deitel
2
Harvey
Deitel
3
Abbey
Deitel
AuthorID
FirstName
LastName
4
Dan
Quirk
5
Michael
Morgano
Fig. 12.5 Titles table of the Books database.
Column
Description
ISBN
ISBN of the book (a string). The table’s primary key. ISBN is an abbreviation for “International Standard Book Number”—a
numbering scheme that publishers worldwide use to give every book a unique identification number.
Title
Title of thebook (a string).
EditionNumber
Edition number of the book (an integer).
Copyright
Copyright year of the book (a string).
Fig. 12.6 Data from the Titles table of the Books database.
ISBN
Title
EditionNumber
Copyright
0132151006
Internet &World Wide Web How to Program
5
2012
0132575663
Java How to Program
9
2012
013299044X
C How to Program
7
2013
0132990601
Simply Visual Basic 2010
4
2013
0133406954
Visual Basic 2012 How to Program
6
2014
0133379337
Visual C# 2012 How to Program
5
2014
0136151574
Visual C++ 2008 How to Program
2
2008
0133378713
C++ How to Program
9
2014
0132121360
Android for Programmers: An App-Driven Approach
1
2012
Fig. 12.7 AuthorISBN table of the Books database.
Column
Description
AuthorID
The author’s ID number, a foreign key to the Authors table.
ISBN
The ISBN for a book, a foreign key to the Titles table.
Fig. 12.8 Data from the AuthorISBN table of the Books database.
AuthorID
ISBN
AuthorID
ISBN
1
0132151006
(continued)
1
0132575663
1
0133379337
1
013299044X
1
0136151574
1
0132990601
1
0133378713
1
0133406954
1
0132121360
AuthorID
ISBN
(continued)
AuthorID
ISBN
2
0132151006
2
0132575663
(continued)
2
013299044X
2
0132121360
2
0132990601
3
0132151006
2
0133406954
3
0132990601
2
0133379337
3
0132121360
2
0136151574
3
0133406954
2
0133378713
4
0136151574
5
0132121360
(continued)
Entity-Relationship Diagram for the Books Database
Figure 12.9 is an entity-relationship (ER) diagram for the Books database. This diagram shows the tables in the database and the relationships
among them. The first compartment in each box contains the table’s name. The names in italic font are primary keys—AuthorID in
the Authors table, AuthorID and ISBN in the AuthorISBN table, and ISBN in the Titles table. Every row must have a value in the primary-key
column (or group of columns), and the value of the key must be unique in the table; otherwise, the DBMS will report an error. The
names AuthorID and ISBN in the AuthorISBN table are both italic—together these form a composite primary keyfor the AuthorISBN table.
Fig. 12.9 Entity-relationship diagram for the Books database.
The lines connecting the tables in Fig. 12.9 represent the relationships among the tables. Consider the line between
the Authorsand AuthorISBN tables. On the Authors end of the line, there’s a 1, and on the AuthorISBN end, an infinity symbol (∞). This indicates
a one-to-many relationship—for each author in the Authors table, there can be an arbitrary number of ISBNs for books writtenby that author in
the AuthorISBN table (that is, an author can write any number of books). Note that the relationship line links the AuthorIDcolumn in
the Authors table (where AuthorID is the primary key) to the AuthorID column in the AuthorISBN table (where AuthorID is a foreign key)—the
line between the tables links the primary key to the matching foreign key.
The line between the Titles and AuthorISBN tables illustrates a one-to-many relationship—one book can be written by many authors. Note that
the line between the tables links the primary key ISBN in table Titles to the corresponding foreign key in table AuthorISBN. The relationships
in Fig. 12.9 illustrate that the sole purpose of the Author-ISBN table is to provide a many-to-many relationshipbetween
the Authors and Titles tables—an author can write many books, and a book can have many authors.
12.5 Querying a Database with LINQ
In this section, we demonstrate howto connect to a database, query it and display the results of the
query. There is little code in this section—the IDE provides visual programming tools and wizards that
simplify accessing data in apps. These tools establish database connections and create the objects
necessary to view and manipulate the data through Windows Forms GUI controls—a technique known
as data binding.
For the examples in Sections 12.5–12.8, we’ll create one solution that contains severalprojects. One will
be a reusable class library containing the ADO.NET Entity Data Model for interacting with
the Books database. The other projects will be Windows Forms apps that use the ADO.NET Entity Data
Model in the class library to manipulate the database.
Our firstexample performs a simple query on the Books database from Section 12.3. We retrieve the
entire Authors table, ordered by the authors’ last name, then first name. We then use data binding to
display the data in a DataGridView—a control from namespace System.Windows.Forms that can display
data from a data source in tabular format. The basic steps we’ll perform are:
•
Create the ADO.NET entity data model classes for manipulating the database.
•
Add the entity data model object that represents 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 code to the Form’s code-behind file to allow the app to interact with the database.
The GUI for the program is shown in Fig. 12.10 . All of the controls in this GUI are automatically
generated when we drag a data source that represents the Authors table onto the Form in Design view.
The BindingNavigator toolbar at the top of the window is a collection of controls that allow you to
navigate through the records in the DataGridView that fills the rest of the window.
The BindingNavigator controls also allow you to add records, delete records, modify existing records and
save your changes to the database. You can add a new record by pressing the Add new ( ) button,
then entering the new author’s first and last name. You can delete an existing record by selecting an
author (either in the DataGridView or via the controls on the BindingNavigator) and pressing
the Delete ( ) button. You can edit an existing record by clicking the first name or last name field for
that record and typing the new value. To save your changes to the database, simply click the Save Data (
) button. Empty values are not allowed in the Authors table of the Books database, so if you attempt
to save a record that does not contain a value for both the first name and last name an exception
occurs.
FIG. 12.10 GUI the Display AUTHORS TABLE App.
12.5.1 Creating the ADO.NET Entity Data Model Class Library
This section presents the steps required to create the entity data model from an existing database.
A model describes the data that you’ll be manipulating—in our case, the data represented by the tables
in the Books database.
Step 1: Creating a Class Library Project for the ADO.NET Entity Data Model
Select FILE > New Project… to display the New Project dialog, then select Class Library from the Visual
Basic templates and name the project BooksExamples. Click OK to create the project, then delete
the Class1.vb file from the Solution Explorer.
Step 2: Adding the ADO.NET Entity Data Model to the Class Library
To interact with the database, you’ll add an ADO.NET entity data model to the class library project. This
will also configure the connection to the database.
1. Adding the ADO.NET Entity Data Model. Right click the BooksExamples project in the Solution
Explorer, then select Add > New Item… to display the Add New Item dialog (Fig. 12.11 ). From
the Data category select ADO.NET Entity Data Model and name the model BooksModel.edmx—
this file will contain the information about the entity data model you’re about to create.
Click Add to add the entity data model to the class library and display the EntityData Model
Wizard dialog.
1. Fig. 12.11 Selecting ADO.NET Entity Data Model in the Add New ItemDialog.
2. Choosing the Model Contents. The Choose Model Contents step in the Entity Data Model
Wizard dialog (Fig. 12.12 ) enables you to specify the entity data model’s contents. The model in
these examples will consist of data from the Books database, so select Generate from
Database and click Next > to display the Choose Your Data Connection step.
3. Choosing the Data Connection. In the Choose Your Data Connection step, click New
Connection… to display the Connection Properties dialog (Fig.12.13 ). (If the Choose Data
Source dialog box appears, select Microsoft SQL Server, then click OK.) The Data
source: TextBox should contain Microsoft SQL Server Database File (SqlClient). (If it does not,
click Change… to display a dialog where you can change the Data source.) Click Browse… to
locate and select the Books.mdf file in the Databases directory included with this chapter’s
examples. You can click Test Connection to verify that the IDE can connect to the database
through
Fig. 12.12 Entity Data Model Wizard dialog’s Choose Model Contentsstep
Fig. 12.13 Connection Properties dialog.
SQL Server Express. Click OK to create the connection. Figure 12.14 shows the Entity connection
string for the Books.mdf database. This string contains the information that the ADO.NET Entity
Framework requires to connect to the database at runtime. Click Next >. A dialog will appear asking if
you’d like to add the database file to your project. Click Yes to move to the next step.
Fig. 12.14 Choose Your Data Connection step after selecting Books.mdf.
1. Choosing the Database Objects to Include in the Model. In the Choose Your Database Objects
and Settings step, you’ll specify the database parts to include in the entity data model. Select
the Tables node as shown in Fig. 12.15, ensure that Pluralize or singularize generated object
names is checked, then click Finish.
2. Viewing the Entity Data Model Diagram in the Model Designer. At this point, the IDE creates
the entity data model and displays a diagram (Fig. 12.16 ) in the model designer. The diagram
contains Author and Title entities—these represent authors and titles in the database and the
properties of each. Notice that the IDE renamed the Title column of the Titles table as Title1 to
avoid a naming conflict with the class Title that represents a row in the table. The line between
the entities indicates a relationship between authors and titles—this relationship is
implemented in the Books database as the AuthorISBN table. The asterisk (*) at each end of the
line indicates a many-to-many relationship—an author can be an author for many titles and a
title can have many authors. The Navigation Properties section in the Author entity contains
the Titlesproperty, which connects an author to all titles written by that author. Similarly,
the Navigation Properties section in the Title entity contains the Authorsproperty, which
connects a title to all of its authors.
3. Building the Class Library. Select BUILD > Build Solution to build the class library that you’ll
reuse in the next several examples—this will compile the entity data model classes that were
generated by the IDE. When you build the class library, the IDE generates the classes that you
can use to interact with the database. These
Fig. 12.15 Selecting the database’s tables to include in the ADO.NET Entity Data Model.
Fig. 12.16 Entity data model diagram for the Author and Title entities.
include a class for each table you selected from the database and a derived class
of DbContext named BooksEntities that enables you to programmatically interact with the database.
[Note: Building the project causes the IDE to execute a script that creates and compiles the entity data
model classes. A security warning dialog appears indicating that this script could harm your computer.
Click OK to allow the script to execute. The warning is intended primarily for cases in which you
download from the Internet Visual Studio templates that execute scripts.]
12.5.2 Creating a Windows Forms Project and Configuring It to Use the Entity Data Model
Recall that the next several examples will all be part of one solution containing several projects—the
class library project with our reusable model and individual Windows Forms apps for each example. In
this section, you’ll create a new Windows Forms app and configure it to be able to use the entity data
model that you created in the preceding section.
Step 1: Creating the Project
To add a new Windows Forms project to the existing solution:
1. Right click the solution name in Solution Explorer and select Add > New Project… to display
the Add New Project dialog.
2. Select Windows Forms Application, name the project DisplayTable and click OK.
3. Change the name of the Form1.vb source file to DisplayAuthorsTable.vb. The IDE updates
the Form’s class name to match the source file. Set the Form’s Text property
to DisplayAuthorsTable.
4. Configure the solution so that this new project will execute when you select DEBUG > Start
Debugging (or press F5). To do so, right click the DisplayTableproject’s name in the Solution
Explorer, then select Set as Startup Project.
Step 2: Adding a Reference to the BooksExamples Class Library
To use the entity data model classes for data binding, you must first add a reference to the class library
you created in Section 12.5.1—this allows the new project to use that class library. Each project you
create typically contains references to several .NET class libraries (called assemblies) by default—for
example, a Windows Forms project contains a reference to the System.Windows.Forms library. When
you compile a class library, the IDE creates a .dll file (known as an assembly) containing the library’s
components. To add a reference to the class library containing the entity data model’s classes:
1. Right click the DisplayTable projectin the Solution Explorer and select Add Reference….
2. In the left column of the Reference Manager dialog that appears, select Solution to display the
other projects in this solution, then in center of the dialog select BooksExamples and
click OK. BooksExamples will be placed in the project’s References node (which you can see
when the Show All Filesbutton is selected).
Step 3: Adding References to System.Data.Entity and EntityFramework
You’ll also need references to the System.Data.Entity and EntityFrameworklibraries to use the ADO.NET
Entity Framework. To add a reference to System.Data.Entity, repeat Step 2 for adding a reference to
the BooksExampleslibrary, but in the left column of the Reference Manager dialog that appears,
select Assemblies then locate System.Data.Entity, ensure that its checkbox is checked and
click OK. System.Data.Entity should now appear in the projects Referencesnode.
The EntityFramework library was added by the IDE to the BooksExamples class library project when we
created the entity data model, but the EntityFramework library is also required in each app that will use
the entity data model. To add a reference to the EntityFramework library:
1. Right click the solution name in the Solution Explorer and select Manage NuGet Packages for
Solution… to display the Manage NuGet Packagesdialog.
2. In the dialog that appears, click Manage to display the Select Projects dialog, then select
the DisplayTable projectand click OK.
3. Click Close to close the Manage NuGet Packages dialog. EntityFrameworkshould now appear in
the projects References node.
Step 4: Adding the Connection String to the Windows Forms App
Each app that will use the entity data model also requires the connection string that tells the Entity
Framework how to connect to the database. The connection string is stored in the BooksExamples class
library’s App.Config file. In the Solution Explorer, open the Books-Examples class library’s App.Config file
then copy the three lines that represent the connection string, which have the format:
Connection string information appears here
Next, open the App.Config file in the DisplayTable project and paste the connection
string information after the line containing and before the line
containing . Save the App.Config file.
12.5.3 Data Bindings Between Controls and the Entity Data Model
You’ll now use the IDE’s drag-and-drop GUI design capabilities to create the GUI for interacting with
the Books database. You must write a small amount of code to enable the autogenerated GUI to interact
with the entity data model. You’ll now perform the steps to display the contents of the Authors table in
a GUI.
Step 1: Adding a Data Source for the Authors Table
To use the entity data model classes for data binding, you must first add them as a data source. To do
so:
1. Select VIEW > Other Windows >Data Sources to display the Data Sources window at the left side
of the IDE, then in that window click the Add New Data Source… link to display the Data Source
Configuration Wizard.
2. The Entity Data Model classes are used to create objects representing the tables in the
database, so we’ll use an Object data source. In the dialog, select Object and click Next >.
Expand the tree view as shown in Fig. 12.17 and ensure that Author is checked. An object of this
class will be used as the data source.
3. Click Finish.
The Authors table in the database is now a data source from which adata bound GUI control can obtain
data. In the Data Sources window (Fig. 12.18 ), you can see the Author class that you added in the
previous step. Properties representing columns of the database’s Authors table should appearbelow it,
as well as a Titles navigation property representing the relationship between the
database’s Authors and Titlestables.
Step 2: Creating GUI Elements
Next, you’ll use the Design view to create a DataGridView control that can display the Authors table’s
data. To do so:
1. Switch to Design view for the DisplayAuthorsTable class.
2. 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 and ensure that the DataGridView option (which is
the default) is selected—this is the GUI control that will be used to display and interact with the
data.
Fig. 12.17 Selecting the Author class as the data source.
Fig. 12.18 Data Sources window showing the Author class as a data source
1. Drag the Author node from the Data Sources window onto the Form in Design view. You’ll need
to resize the Form to fit the DataGridView.
The IDE creates a DataGridView (Fig. 12.19 ) with column names representing allthe properties for
an Author, including the Titles navigation property. The IDE also creates a BindingNavigator that
contains Buttons for moving between entries, adding entries, deleting entries and saving changes to the
database. The IDE also generates a BindingSource (AuthorBindingSource), which handles the transfer of
data between the data source and the data-bound controls on the Form. Nonvisual components such as
the BindingSource and the nonvisual aspects of the BindingNavigator appear in the component tray—
the gray region below the Form in Design view. The IDE names
the BindingNavigator and BindingSource(AuthorBindingNavigator and AuthorBindingSource,
respectively) based on the data source’s name (Author). We use the default names for automatically
generated components throughout this chapter to show exactly what the IDE creates.
Fig. 12.19 Component tray holds nonvisual components in Design view.
To make the DataGridView occupy the entire window below the BindingNavigator, select
the DataGridView, then use the Properties window to set the Dock property to Fill. You can stretch the
window horizontally to see all the DataGridView columns. We do not use the Titles column in this
example, so with the DataGridView selected, select the Columns property in the Properties window,
then click the ellipsis button ( ) to display the Edit Columns dialog. Select Titles in the Selected
Columnslist, then click Remove to remove that column.
Step 3: Connecting the Data Source to the AuthorBindingSource
The final step is to connect the data source to the AuthorBindingSource, so that the app can interact
with the database. Figure 12.20 shows the code needed to obtain data from the database and
to save any changes that the user makes to the data back into the database.
Fig. 12.20 Displaying data from a database table in a DataGridView.
Creating the DbContext Object
As mentioned in Section 12.4, a DbContext object interacts with the database on the app’s behalf.
The BooksEntities class (a derived class of DbContext) was automatically generated by the IDE when you
created the entity data model classes to access the Books database (Section 12.5.1). Line 8 creates an
object of this class named dbcontext.
DisplayAuthorsTable_Load Event Handler
You can create the Form’s Load event handler (lines 11–23) by double clicking the Form’s title bar
in Design view. In this app, we allow data to move between the DbContext and the database by using
LINQ to Entities extension methods to extract data from the Books-Entities’s Authors property (lines 15–
19), which corresponds to the Authors table in the database. The expression
dbcontext.Authors
indicates that we wish to get data from the Authors table.
OrderBy Extension Method
The OrderBy extension method call
indicates that the rows of the table should be retrieved in ascending order by the authors’ last names.
The argument to OrderBy is a lambda expression that defines a simple, anonymous method. Like other
Visual Basic methods, a lambda expression begins with Function if it will return a value and Sub if it will
not. This is followed by a parameter list in parentheses—author in this case, is an object of
the Author entity data model class. The lambda expression infers the lambda parameter’s type
from dbcontext.Authors, which contains Author objects. The parameter list is followed by the body of
the function. The value produced by the expression—a given author’s last name—is implicitly returned
by the lambda expression. The syntax shown here is for a single-line lambda expression. You do not
specify a return type for a single-line lambda expression—the return type is inferred from the return
value. You can also create multiline lambda expressions by placing the statements on separate lines
between the Function…End Function or Sub…End Sub keywords. Multiline lambdas can optionally use
the As clause to specify areturn type, just as in a typical method definition. As we encounter lambda
expressions in this chapter, we’ll discuss the syntax we use. You can learn more about lambda
expressions at
msdn.microsoft.com/en-us/library/bb531253.aspx
ThenBy Extension Method
When there are multiple authors with the same last name, we’d like them to be listed in ascending
order by first name as well. The ThenBy extension method call
enables you to order results by an additional column. This is applied to the Authorobjects that have
already been ordered by last name. We’ll show similar LINQ query syntax shortly.
Load Extension Method
Finally, line 18 calls the Load extension method (defined in class DbExtensions from the
namespace System.Data.Entity). This method executes the LINQ to Entities query and loads the results
into memory. This data is tracked by the BookEntities DbContext in local memory so that any changes
made to the data can eventually be saved into the database.
Setting the AuthorBindingSource’s DataSource
Line 22 sets the AuthorBindingSource’s DataSource property to the Localproperty of
the dbcontext.Authors object. In this case, the Local property is an ObservableCollection(Author) that
represents the query results that were loaded into memory by lines 15– 19. When
a BindingSource’s DataSource property is assigned an ObservableCollection(Of
T) (namespace System.Collections.ObjectModel), the GUI that’s bound to the BindingSourceis notified of
any changes to the data so the GUI can be updated accordingly. In addition, changes made by the user
to the data in the GUI will be tracked so the DbContext can eventually save those changes to the
database.
AuthorBindingNavigatorSaveItem_Click Event Handler: Saving Modifications 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 DataButton ( ) is disabled. To enable it, right click
this Button’s icon in the BindingNavigator and select Enabled. Then, double click the icon to create
its Click event handler (lines 27–40).
Saving the data entered in the DataGridView back to the database is a three-step process. First, all
controls on the form are validated by calling the DisplayTableForm’s inherited Validate method (line
30)—if any control has an event handler for the Validating event, it executes. You typically handle this
event to determine whether a control’s contents are valid. Next, line 31 calls EndEdit on
the AuthorBindingSource, which forces it to save any pending changes into the BooksEntities model in
memory. Finally, line 35 calls SaveChanges on the BooksEntities object (dbcontext) to store any changes
into the database. We placed this call in a Try statement, because the Authors table does not allow
empty values for the first name and last name—these rules were configured when we originally created
the database. When SaveChanges is called, any changes stored into the Authors table must satisfy the
table’s rules. If any of the changes do not, a DBEntityValidationException occurs.
12.3 (Display Authors Table App Modification) Modify the app in Section 12.5 to contain a Text-Box and a Button that allow the user to search for specific authors by last name.
Include a Label to identify the TextBox. Using the techniques presented in Section 12.99, create a LINQ query and change the DataSource property of AuthorBindingSource to contain
only the specified authors. Also, provide a Button that enables the user to return to browsing the complete set of authors.