Desktop Application Development 43

LAB 7
WINDOWS FORM PROGRAMMING DATABASE
CONNECTION
TARGET
✓ Instruct students to familiarize themselves with building Windows App
applications that connect to SQL Server databases.
✓ Design input forms for tables in the database.
✓ Execute basic data queries in the application
✓ In this part, we are going to learn how to perform CRUD operations in a Windows
Forms application using Entity Framework. - List of Entity Framework: EF
Basics, EF 6 DB-First, EF 6 Code-First, EF Core, EF Cheat Sheet, EF Quiz

PRACTICE CONTENT

1. Exercise 1

1.1. Follow the requirements below

Building a Windows Form application that illustrates basic student information

management, the information that needs to be managed includes:
 Student information
 City information
 Class information

1.2. Step-by-step instructions

First, we have to create database and table in SQL Server. Please find the below
scripts for creating the database and table respectively.
DB Script
1. CREATE DATABASE StudentInformation;
2. GO;
Table Script

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 44

Create a Windows application project in Visual Studio. Rename the project to

AppWDFConnectDMMSSqlServer.
After creating the project successfully, we proceed to design the interface. Here, they
have used labels, text-boxes, combo-boxes, Data GridView, and buttons, the ID label is
a hidden field

Figure 7.1 Sample interface

Connect Database
Add the ADO.NET Entity model in your project. To add, right click your solution
and select Add - > New Item -> Select "Data" in left pane and select "Ado.Net Entity

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 45

Model".

Figure 7.2 Add the ADO.NET Entity model in your project

Select ADO.NET Entity Data Model, then rename Model1 to Student

Figure 7.3 Add the ADO.NET Entity model in your project

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 46

After, Select EF Designer from Database in Windows “Entity Data Model

Wizard”, then click Next button. Then select “New Connection...”

At windows “Connection Properties”, We are choose the server name and select

Figure 7.4 Configure connection string

the name of the database defined earlier. In case we don't remember the server name we
can enter the Server name with "."
Please find the below image for your reference.

Figure 7.5 Configure connection string

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 47

They can test whether the connection is successful or not by clicking the "Test
Connection" button. Then click OK button, to continue. Continue to press the Next
button. Continue, we choose “Entity Framework 6.x” and click Next button

Figure 7.6 Select Entity Framework version

At Windows “Entity Data Model Wizard”,

We select all the defined tables or
some necessary ones. In the case where the
database has "View, Stored Procedures and
Function" then we choose "View" and “Stored
Procedures and Function”. After click
Finish button to Complete.
Now, you can see the added Entity Model
Figure 7.7 Choose table in database
in your Solution Explorer.

Figure 7.8 Entity Model in your Solution Explorer

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 48

The next, let us create our custom model class for binding and displaying the values.
Note: Don't forget to declare your class as "public". Then only you can access this class
outside of any other class.
First, add a new folder, then rename "Models". Next, we add a new class and
rename to "StudentInfo".
Code Example: class StudentInfo. Include properties to display:

Figure 7.9 class StudentInfo

Display value from Database into Data GridView

Bind the Student Details in Data GridView when form is loading and as well
as we have to bind the values for Gender combo-box and City combo-box values at
Event Form_Load.
First, we define a function that retrieves data from the database. Then pour the
data into Data GridView. Please find the below code for your reference.

Figure 7.10 Get data from the DB

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 49

After defining the "Display" method, at the "Form_Load" event, we call the "Display"
method to execute. Please find the below code for your reference.

Figure 7.11 Call Display method at the Form_Load event

Press the F5 key to see the result. Result:

Figure 7.12 Result

Now, let's write the code for "Save" button.

Click the "Save" button and write the below code. "SaveStudentDetails" is a method
to update the entity. Please find the below code for your reference.

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 50

Figure 7.13 Method Save Student Details

Here, we are binding our input values (Name, Age, City, Gender) into
StudentDetails class and passing this to "SaveStudentDetails" method and saving the
entity. Please find the below code for your reference:

Press the F5 key, then enter the information in the box then click the Save button

Figure 7.14 Save data to DB

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 51

Figure 7.15 Save data to DB

Update
Next, write the code for update and delete operations. To update the record, we
have to select the record from data GridView. Here, I have written datagridview cell
click event to get the values from datagridview to fields. Please find the below code for
your reference.
We have to select the record from datagridview for update and delete. The same
event will help us to get the records.

Figure 7.16 Assign data from GridView to the corresponding textbox

Please find the below code for "Update". You define a function that updates data,
which returns the correct value if the update is successful, otherwise false if the update
fails.

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 52

Figure 7.17 Update method

After defining the data update function, you will call it in the update button click
event.

Figure 7.18 Execute update method on Save button click event

Clear the fields after Insert or Update or Delete operation

Figure 7.19 Clear fields method

Delete
You define a function that delete data, which returns the correct value if the delete
is successful, otherwise false if the delete fails.

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 53

Figure 7.20 Delete method

After defining the data delete function, you will call it in the delete button click
event. Please find the below code for "Delete".

Figure 7.21 Execute delete method on Delete button click event

2. Exercise 2

Same as above, now we do: view, add, delete, edit data across multiple tables.
✓ Design the student information management interface (view, add, delete, edit)
✓ Design interface for managing the list of provinces / cities (view, add, delete,
edit).
✓ Design the class list management interface (view, add, delete, edit).

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 54

✓ Update student information into the database. Place of birth and class information
will be taken from two tables: Class and province / city. Show status (success /
failure).
✓ Delete student information by student ID.
✓ Search student information by.
Sample database

Figure 7.22 Sample database

Suggestions
Display list of province / city and class in database into combobox. To display data
into the combobox. You write the functions as follows: SetValueCity, SetValueClass.
Then, at the "Form_Load" event, call the two functions SetValueCity and
SetValueClass just defined earlier. Please find the below code for " SetValueCity ".

Figure 7.23 Method SetValueCity

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 55

Please find the below code for "SetValueClass".

Figure 7.24 Method SetValueClass

Please find the below code for " Form_Load".

Figure 7.25 Execute SetValueClass and SetValueCity method on Form_load event

Next, you create two forms to manage the list of classes and provinces / cities.
Form Manage classroom information

Figure 7.26 Manage classroom information

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 56

Form Manage cities information

Figure 7.27 Manage cities information

Form Main

Figure 7.28 Form main

To add data for the class table as well as the province/city. You need to open the
information management form of two tables through the corresponding "+" button next
to it. Please find the below code for "+", add Class.

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY

 Desktop Application Development 57

Figure 7.29 Add class Event

Please find the below code for "+", add City

Figure 7.30 Add city Event

Students continue to complete the remaining functions

---The end---

DESKTOP APPLICATION DEVELOPMENT FACULTY OF INFORMATION TECHNOLOGY