Department of Computer Science

Event-Driven Programming by C#

Chapter Six:
GUI and Database Programming in C#
 Contents
2

 Introduction to GUI programming in C#

 The ADO.NET Architecture

 LINQ Architecture

 The .NET Data providers

 Working with the common .NET Data providers

 The Dataset Component

 Using the DataGridView for database access

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#
3

 A C# has all the features of any powerful, modern language.

 In C#, the most rapid and convenient way to create your user interface is to do so

visually, using the Windows Forms Designer and Toolbox.

 Windows Forms controls are reusable components that encapsulate user interface

functionality and are used in client side Windows based applications.

 A control is a component on a form used to display information or accept user input.

 The Control class provides the base functionality for all controls that are displayed on a

Form.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
4

How to create a new project in C# ?

From the following steps we can understand how to place a new control on windows Form.
Step 1) Open your Visual Studio Environment and Click File->New Project

Step 2) Then you will get a Create a New Project Dialogue Box asking in which language you want to
create a new project.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
5

How to create a new project in C# ?...

Step 3) Select “Windows Forms App (.NET Framework)” from the list of project templates, then
you will get the following screen.

Step 4) Then in this window, give name of

project and specify project location:

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
6

How to add controls to Form ?

Step 5) Now you can add controls in your Form Control.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
7

How to add controls to Form ?

 In the left side of the Visual Studio Environment, you can see the Toolbox. There are lots of

controls grouping there in the Toolbox according to their functionalities.

 Just click the expand sign before each group then you can see the controls inside the group.

You can select basic controls from Common Controls or All windows Forms group.

 You can place the control in your Form by drag and drop the control from your toolbox to Form

control.
.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
8

How to drag and drop controls ?

 In the slide 7 picture, we drag and drop the Button control from Toolbox - Common control to

Form control.
 Now you can start write codes on each control to create your programs.

 From the following lessons you can study how to use some of these Windows Forms

Controls in your C# applications.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
9

Windows Forms and Controls

 C# programmers have made extensive use of forms to build user interfaces. Each time you create
a Windows application, Visual Studio will display a default blank form, onto which you can drag
the controls onto your applications main form and adjust their size and position. See picture on
slide 7.
 The Windows Form you see in Designer view is a visual representation of the window that will

open when your application is opened.

 You can switch between this view and Code view at any time by right-clicking the design surface

or code window and then clicking View Code or View Designer.

C# Lecture Notes by Jerusalem F.
Introduction to GUI programming in C#...
10

 At the top of the form there is a title bar which displays the forms title. Form1 is the default

name, and you can change the name to your convenience.

 The title bar also includes the control box, which holds the minimize, maximize, and close

buttons.
 If you want to set any properties of the Form, you can use Visual Studio Property window to

change it.
 If you do not see the Properties window, on the View menu, click Properties window. This

window lists the properties of the currently selected Windows Form or control, and it's here that
you can change the existing values.
C# Lecture Notes by Jerusalem F.
Introduction to GUI programming in C#...
11

 For example , to change the forms title from Form1 to MyForm,

click on Form1 and move to the right side down Properties window,
set Text property to MyForm.
 Then you can see the Title of the form is changed. Likewise, you can

set any properties of Form through Properties window.

 You can also set the properties of the Form1 through coding.

 For coding, you should right-click the design surface or code

window and then clicking View Code.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
12

Windows Controls
1) C# Label Control
 Labels are one of the most frequently used C# control. We can use the Label control to

display text in a set location on the page.

 Label controls can also be used to add descriptive text to a Form to provide the user with

helpful information.
 The Label class is defined in the System.Windows.Forms namespace.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
13

2) C# Button Control
 A button is a control, which is an interactive component that enables users to communicate
with an application.
 The Button class inherits directly from the ButtonBase class.

 A Button can be clicked by using the mouse, ENTER key, or SPACEBAR if the button has

focus.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
14

How to Call a Button's Click Event Programmatically

The Click event is raised when the Button control is clicked. This event is commonly used

when no command name is associated with the Button control.

 Raising an event invokes the event handler through a delegate.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
15

3) C# TextBox Control
 A TextBox control is used to display, or accept as input, a single line of text. This

control has additional functionality that is not found in the standard Windows text box
control, including multiline editing and password character masking.
 A text box object is used to display text on a form or to get user input while a C#

program is running.
 In a text box, a user can type data or paste it into the control from the clipboard.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
16

 A TextBox control has many properties, from those let us see Keydown and TextChanged Events.

Keydown event
You can capture which key is pressed by the user using KeyDown event.

private void textBox1_KeyDown(object sender, KeyEventArgs e) {

if (e.KeyCode == Keys.Enter) {
MessageBox.Show("You press Enter Key");
}
if (e.KeyCode == Keys.CapsLock) {
MessageBox.Show("You press Caps Lock Key");
}
}
C# Lecture Notes by Jerusalem F.
Introduction to GUI programming in C#...
17

TextChanged Event
When user input or setting the Text property to a new value raises the TextChanged event.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
18

4) C# ComboBox Control
 C# controls are located in the Toolbox of the development environment, and you use them to
create objects on a form with a simple series of mouse clicks and dragging motions.
 A ComboBox displays a text box combined with a ListBox, which enables the user to select

items from the list or enter a new value.

 The user can type a value in the text field or click the button to display a drop down list. You

can add individual objects with the Add method.

 You can delete items with the Remove method or clear the entire list with the Clear method.

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
19

4) C# ComboBox Control

C# Lecture Notes by Jerusalem F.

Introduction to GUI programming in C#...
20

How to Pass Data Between Forms

 Passing values between forms in C# is a common requirement when developing applications

with multiple forms or windows.

 There are several methods you can use to achieve this, including constructors, properties,

events, and static variables.

 Let us explore each method with detailed explanations and examples:

C# Lecture Notes by Jerusalem F.

Introduction (Revision)
21

 A Database is an organized collection of information that is divided into tables. Each

table is further divided into rows and columns; these columns store the actual
information.
 We can access a database using Structured Query Language (SQL), which is a standard

language supported by most database software including SQL Server, Access, and Oracle.
 In this chapter, you'll see a C# program that connects to a SQL Server database, retrieves

and displays the contents stored in the columns of a row from a table, and then
disconnects from the database.
C# Lecture Notes by Jerusalem F.
Introduction (Revision)…
22

 We shall also see programs that connect to Access and Oracle databases.

 We shall also learn about Microsoft's rapid application development (RAD) tool, Visual

Studio .NET (VS .NET). VS .NET enables you to develop, run, and debug programs in an
integrated development environment.
 This environment uses all the great features of Windows, such as the mouse and intuitive

menus, and increases your productivity as a programmer.

 In the final sections of this chapter, We shall see how to use the extensive Microsoft

documentation that comes with the .NET Software Development Kit (SDK) and VS .NET.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture
23

What is ADO.NET?
 ADO stands for Microsoft ActiveX Data Objects. ADO.NET is one of Microsoft’s data

access technologies, which we can use to communicate with different data sources.
 It is a part (module) of the .NET Framework, which connects the .NET Application

(Console, WCF, WPF, Windows, MVC, Web Form, etc.) and different data sources.
 The Data Sources can be SQL Server, Oracle, MySQL, XML, etc.

 ADO.NET consists of a set of predefined classes that can be used to connect, retrieve,

insert, update, and delete data (i.e., performing CRUD operation) from data sources.
C# Lecture Notes by Jerusalem F.
The ADO.NET Architecture…
24

What is ADO.NET?...
 All the ADO.NET classes are located into System.Data.dll and integrated with XML classes

located into System.Xml.dll.

 ADO.NET (ActiveX Data Objects .NET) is a data access technology in the
Microsoft .NET framework that provides a set of libraries and classes for working with
data from various sources, including databases, XML files, and more.
 It is part of the .NET framework’s base class library and interacts with data-centric

applications and databases

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
25

What is ADO.NET?...
 ADO.NET is designed to provide a bridge between your application code and the

underlying data sources.

 It offers a way to perform tasks like connecting to databases, executing queries,

retrieving and updating data, and managing connections and transactions.

 This is especially important for building data-driven applications and services.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
26

What Types of Applications Use ADO.NET?

 ADO.NET is used in various applications where data access and manipulation are

crucial. Here are some types of applications that commonly use ADO.NET:
 Desktop Applications: Traditional desktop applications like Windows Forms and WPF
applications often need to interact with databases or other data sources. ADO.NET provides
the necessary tools to connect to databases, retrieve data, and update records.
 Web Applications: Web applications, including ASP.NET Web Forms and ASP.NET MVC
applications, require data access to display, collect, and manage information. ADO.NET
enables these applications to connect to databases and present data to users.
C# Lecture Notes by Jerusalem F.
The ADO.NET Architecture…
27

What Types of Applications Use ADO.NET?

 Console Applications: Console applications might need to perform data-related tasks,

like importing/exporting data, data analysis, or reporting. ADO.NET can facilitate these
tasks by providing efficient data access.
 Service Applications: Background or Windows services that process data often rely on

ADO.NET to connect to databases and handle data-related operations.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
28

 So, ADO.NET is nothing but a component in .NET Framework that helps us to fetch data
from different data sources to our C#.NET or VB.NET Applications.
 As well as also used to send data from our C#.NET or VB.NET Application to different

data sources such as SQL Server, Oracle, MySQL, XML, etc.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
29

 The ADO.NET Architecture is comprised of 6 important components. They are as

follows:
• Connection: Establishes database connection
• Command: Executes SQL commands
• DataReader: Reads data in forward-only mode
• DataAdapter: Bridges DataSet and data source
• DataSet: In-memory cache of data
 From the above components, two components are compulsory. One is the command object and

the other one is the connection object. Irrespective of the operations like Insert, Update, Delete
and Select, the command and connection object you always need.
C# Lecture Notes by Jerusalem F.
The ADO.NET Architecture…
30

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
31

 ADO.NET uses a multilayer architecture that mainly has a few concepts, for instance

Connection, Reader, Command, Adapter and Dataset objects.

 ADO.NET introduced data providers that are a set of special classes to access a specific

database, execute SQL commands and retrieve data.

 The Data providers are extensible. Developers can create their own providers for a

proprietary data source.

 There are some examples of data providers such as SQL Server providers, OLE DB and

Oracle provider.
C# Lecture Notes by Jerusalem F.
The ADO.NET Architecture…
32

ADO.NET provides the following two types of classes’ objects:

 Connection-based: They are the data provider objects such as Connection, Command,

DataAdapter, and DataReader. They execute SQL statements and connect to a database.
 Content-based: They are found in the System.Data namespace and includes DataSet,

DataColumn, DataRow, and DataRelation. They are completely independent of the type
of data source.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
33

ADO.NET Namespaces
Namespaces Description
System.Data Contains the definition for columns,relations,tables,database,rows,views
and constraints.
System.Data.SqlClient Contains the classes to connect to a Microsoft SQL Server database such
as SqlCommand, SqlConnection, and SqlDataAdapter.
System.Data.Odbc Contains classes required to connect to most ODBC drivers. These classes
include OdbcCommand and OdbcConnection.
System.Data.OracleClient Contains classes such as OracleConnection and OracleCommand required
to connect to an Oracle database.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
34

Connection Class
You need to establish a connection class object for inserting, updating, deleting and

retrieving data from a database.

 The Connection class allows you to establish a connection to the data source.

 The Connection class object needs the necessary information to discover the data source

and this information is provided by a connection string.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
35

Connection Strings
You need to supply a connection string in the Connection class object. The connection

string is a series of name/value settings separated by semicolons (;).

A connection string requires a few pieces of information such as the location of the

database, the database name, and the database authentication mechanism.

This connection is used to connect to the Master database on the current computer using

integrated security (indicating the currently logged-in Windows user can access the
database).

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
36

1) Connection
 The first vital component of ADO.NET Architecture is the Connection Object.

 The Connection Object is required to connect with your backend database which can be SQL

Server, Oracle, MySQL, etc.

 To create a connection object, you need at least two things.

 The first one is where is your database located i.e. the Machine name or IP Address or
someplace where your database is located.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
37

1) Connection…
 And the second thing is the security credentials i.e. whether it is a Windows

authentication or SQL Authentication i.e. user name and password-based authentication.

 So, the first is to create the connection object and the connection object is required to

connect the front-end application with the backend data source.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
38

2) Command
 The Second vital component of ADO.NET Architecture is the Command Object.

 When we talk about databases like SQL Server, Oracle, MySQL, etc., we know one thing,

these databases only understand SQL.

 The Command Object is the component where you go and write your SQL queries.

 Later you take the command object and execute it over the connection.

 That means using the command object, you can fetch data or send data to the database i.e.

performing the Database CRUD Operations.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
39

2) Command
Note:
 From the Command object onwards, you can go in two different ways.
 One is you can go with the DataSet way and the other is, you can go with the DataReader
way.
 Which way you need to choose, basically will depend on the situation.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
40

3) DataReader
 DataReader is a read-only connection-oriented record set that helps us to read the records only in

the forward mode.

 Here, you need to understand three things i.e. read-only, connection-oriented, and forward mode.

 Read-Only means using DataReader, we cannot Insert, Update, and Delete the data.

 Connection-Oriented means, it always requires an active and open connection to fetch the data.

 Forward mode means you can always read the next record, there is no way that you can read the

previous record.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
41

4) DataSet
 It is a Disconnected record set that can be browsed in both i.e. forward and backward mode.

 It is not read-only i.e. you can update the data present in the data set.

 Actually, DataSet is a collection of DataTables that holds the data and we can add, update,

and delete data in a data table.

 DataSet gets filled by somebody called DataAdapter.

C# Lecture Notes by Jerusalem F.

The ADO.NET Architecture…
42

5) DataAdapter
The DataAdapter is one of the Components of ADO.NET which acts as a bridge between the

command object and the dataset.

 What the DataAdapter does is, it takes the data from the command object and fills the data

set.

C# Lecture Notes by Jerusalem F.

SQL database connection with C#
43

 Accessing Data from a database is one of the important aspects of any programming

language.
 It is an absolute necessity for any programming language to have the ability to work

with databases. C# is no different.

 It can work with different types of databases. It can work with the most common

databases such as Oracle and Microsoft SQL Server.

 It also can work with new forms of databases such as MongoDB and MySQL.

C# Lecture Notes by Jerusalem F.

SQL database connection with C#...
44

Fundamentals of Database connectivity

 C# and .Net can work with a majority of databases, the most common being Oracle and

Microsoft SQL Server.

 But with every database, the logic behind working with all of them is mostly the same.

 In our examples, we will look at working the Microsoft SQL Server as our database.

 For learning purposes, one can download and use the Microsoft SQL Server Express

Edition, which is a free database software provided by Microsoft.

 In working with databases, the following are the concepts which on to all databases.

C# Lecture Notes by Jerusalem F.

SQL database connection with C#...
45

1) Connection: To work with the data in a database, the first obvious step is the

connection. The connection to a database normally consists of the below-mentioned

parameters.
a) Database name or Data Source: The first important parameter is the database name to
which the connection needs to be established. Each connection can only work with one
database at a time.
b) Credentials: The next important aspect is the username and password which needs to be
used to establish a connection to the database. It ensures that the username and password
have the necessary privileges to connect to the database.
C# Lecture Notes by Jerusalem F.
SQL database connection with C#...
46

c) Optional parameters: For each database type, you can specify optional parameters to provide
more information on how .Net should handle the connection to the database. For example, one
can specify a parameter for how long the connection should stay active. If no operation is
performed for a specific period of time, then the parameter would determine if the connection
has to be closed.

C# Lecture Notes by Jerusalem F.

SQL database connection with C#...
47

2) Selecting data from the database – Once the connection has been established, the

next important aspect is to fetch the data from the database. C# can execute ‘SQL’
select command against the database. The ‘SQL’ statement can be used to fetch data
from a specific table in the database.
3) Inserting data into the database – C# can also be used to insert records into the

database. Values can be specified in C# for each row that needs to be inserted into the
database.

C# Lecture Notes by Jerusalem F.

SQL database connection with C#...
48

4) Updating data into the database – C# can also be used to update existing records into

the database. New values can be specified in C# for each row that needs to be updated
into the database.
5) Deleting data from a database – C# can also be used to delete records into the

database. Select commands to specify which rows need to be deleted can be specified
in C#.

C# Lecture Notes by Jerusalem F.

SQL database connection with C#...
49

 SqlCommand in C# allow the user to query and send the commands to the database.

SQL command is specified by the SQL connection object. Two methods are used,
ExecuteReader method for results of query and ExecuteNonQuery for insert, Update, and
delete commands. It is the method that is best for the different commands.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
50

How to connect C# to Database

 Let’s now look at the code, which needs to be kept in place to create a connection to a

database. In our example, we will connect to a database which has the name of StudentDB.
 The credentials used to connect to the database are given below:

Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=StudentDB;Integrated Security=True";

Server Name Database Name
 We will see a simple windows forms application to work with databases. We will have a

simple button called “Connect” which will be used to connect to the database and show a
dialogue to us which confirm successful database connection.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
51

So, let us follow the below steps to achieve this:

Step 1) The first step involves the

creation of a new project in Visual Studio
2022. After launching Visual Studio, you
need to choose the menu option New-
>Project.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
52

Step 2) The next step is to choose the

project type as a Windows Forms App
(.NET Framework). Here, we also need to
mention the name and location of our
project.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
53

Step 3) Here, we also need to mention the

name and location of our project.

C# Lecture Notes by Jerusalem F.

 …SQL database connection with C#...
54

1) In the create a new project dialog box, we can see various options for creating different
types of projects in Visual Studio. Customize the combo boxes for “Languages” as “C#”,
for “platforms” as “Windows” and for “Project Types” as “Desktop” for your simplicity to
get the desired project template as follows:

2) We then give a name for the application which in our case is “SQL_databse_in_csharp”.
We also need to provide a location to store our application.
3) Finally, we click the ‘OK’ button to let Visual Studio to create our project.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
55

Step 5) Now add a button from the toolbox

to the Windows form. Put the text property
of the Button as check connection. See the
figure.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
56

Step 4) Now double click the form so that an event handler is added to the code for the
button click event. In the event handler, add the below code.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
57

Slide 33’s code explanation:

1) The first step is to create variables, which will be used to create the connection string and the
connection to the SQL Server database. In this case connectionString and cnn are variables.
2) The next step is to create the connection string. The connecting string needs to be specified
correctly for C# to understand the connection string. The connection string consists of the
following parts
a) Data Source: This is the name of the server on which the database resides. In my case, it is
(localdb)\\MSSQLLocalDB.
b) The Initial Catalog is used to specify the name of the database. In my case, it is StudentDB.
c) The integrated security; in my case it is True.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
58

Slide 33’s code explanation:…

3) Next, we assign the connecting string to the variable cnn. The variable cnn, which is
of type SqlConnection() is used to establish the connection to the database.
4) Next, we use the Open() method of the cnn variable to open a connection to the

database. We then just display a message to the user that the connection is established.
5) Once the operation is completed successfully, we then close the connection to the

database. It is always a good practice to close the connection to the database if nothing
else is required to be done on the database.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
59

 When the above code is set, and the project is executed using Visual Studio, you will get

the below output. Once the form is displayed, click the ‘check connection’ button.
When you click on “check connection” button, from the output, you can see that the

database connection was established. Hence, the message box was displayed.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
60

Access data with the SqlDataReader

 To showcase how data can be accessed using C#, let us assume that we have the

following artifacts in our database.

1) A table called StudentDB. This table will be used to store the studID, studName,
deptName and age of students.
2) The table will have four columns, one called ‘studID’, ‘studName’, ‘studDept’ and
‘studAge’.
3) For this moment, the table will have 5 rows as shown in slide 38.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
61

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
62

C# Select from database table

 Let’s change the code in our form, so that we can query for this data and display the

information via a Messagebox. Note that all the code entered below is a continuation of
the code written for the data connection in the previous section.
Step 1) Let’s split the code into two parts so that it will be easy to understand for the user.
The first will be to construct our “select” statement, which will be used to read the
data from the database.
We will then execute the “select” statement against the database and fetch all the
table rows accordingly.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
63

C# Select from
database table…

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
64

C# Select from database table…

Code Explanation
1) The first step is to create the following variables:
 SQLCommand – The ‘SQLCommand’ is a class defined within C# namespace
System.Data.SqlClient;. This class is used to perform operations of reading and writing into the
database. Hence, the first step is to make sure that we create a variable type of this class. This
variable will then be used in subsequent steps of reading data from our database.
 The DataReader object is used to get all the data specified by the SQL query. We can then read
all the table rows one by one using the data reader.
 We then define 2 string variables, one is “SQL” to hold our SQL command string. The next is
the “Output” which will contain all the table values.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
65

C# Select from database table…

Code Explanation…
 We then define 2 string variables, one is “SQL” to hold our SQL command string. The next
is the “Output” which will contain all the table values.
2) The next step is to define the SQL statement, which will be used against our

database. In our case, it is “Select * from Student;”. This will fetch all the rows
from the table Student.
3) Next, we create the command object which is used to execute the SQL statement

against the database. In the SQL command, you have to pass the connection object
and the SQL string.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
66

C# Select from database table…

Code Explanation…

4) Next, we will execute the data reader command, which will fetch all the rows from the

Student table.
5) Now that we have all the rows of the table with us, we need a mechanism to access the

row one by one. For this, we will use the while statement. The while statement will be
used to access the rows from the data reader one at a time. We then use the GetValue()
method to get the value of columns of the Student table.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
67

Step 2) In the final step, we will just display the output to the user and close all the objects related
to the database operation. // close all objects
dataReader.Close();
command.Dispose();
cnn.Close();
Code Explanation:

1) We will continue our code by displaying the value of the Output variable using the
MessageBox. The Output variable will contain all the values from the demotb table.
2) We finally close all the objects related to our database operation. Remember this is always a
good practice.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
68

 When the above code is set, and the project is run using Visual Studio, you will get the below

output. Once the form is displayed, click the Connect button.

Figure 1: Record from the SQL database Figure 2: output from the select C# program
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
69

C# insert into database table

 Just like Accessing data, C# has the ability to insert records into the database as well. To

showcase how to insert records into our database, let’s take the same table structure
which was used above.
 Let’s change the code in our form, so that we can insert the following row into the table

to the Figure 1 record series using C# program instead of SQL editor.

No studID stduName studDept studAge
1 sweg2381 Senay Software 21
eng.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
70

C# insert into database table…

The below code snippet can be used to insert an existing record in our database.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
71

C# insert into database table…

Code Explanation
1) The first step is to create the following variables
a) SQLCommand: This data type is used to define objects which are used to perform SQL
operations against a database. This object will hold the SQL command which will run
against our SQL Server database.
b) The DataAdapter object is used to perform specific SQL operations such as insert, delete
and update commands.
c) We then define a string variable, which is “SQL” to hold our SQL command string.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
72

C# insert into database table…

Code Explanation…
2) The next step is to actually define the SQL statement which will be used against our database.
In my case, we are issuing an insert statement, which will insert the following record:

3) Next, we create the command object which is used to execute the SQL statement against the
database. In the SQL command, you have to pass the connection object and the SQL string

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
73

C# insert into database table…

Code Explanation…
4) In the data adapter command, we now associate the insert SQL command to the adapter. We
also then issue the ExecuteNonQuery method which is used to execute the Insert statement
against our database table. The ‘ExecuteNonQuery’ method is used in C# to issue any DML
statements against the database. By DML statements, we mean the insert, delete, and update
operation.
5) We finally close all the objects related to our database operation. Remember this is always a
good practice.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
74

C# insert into database table…

Code Explanation…
When the above code is set, and the project is executed using Visual Studio, you will get the

below output. Once the form is displayed, click the Connect button.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
75

C# Update Database
 Just like accessing data, C# has the ability to update existing records from the database as well.
 To showcase how to update records into our database, suppose the same table structure which

was used above.

 Let us change the code in our form, so that we can update the following row. The old row value
is studID as “sweg2395”. Which we will update it to studDept “CS eng.” and studAge “19” while
the row value for studID and studName will remain same.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
76

C# Update Database…
Old row
studID studName studDept studAge
sweg2395 Hirit Software eng. 18

New row
studID studName studDept studAge
sweg2395 Hirit CS eng. 19

 The next slide code snippet will be used to update an existing record in our database.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
77

C# Update Database…

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
78

C# Update Database…

Code Explanation
1) The first step is to create the following variables
a) SQLCommand: This data type is used to define objects which are used to perform SQL
operations against a database. This object will hold the SQL command which will run
against our SQL Server database.
b) The dataadapter object is used to perform specific SQL operations such as insert, delete
and update commands.
c) We then define a string variable, which is SQL to hold our SQL command string.
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
79

C# Update Database…

Code Explanation…
2) The next step is to define the SQL statement which will be used against our database. In our
case we are issuing an update statement, this will update the studDept to “CS eng.” while the
studID and studName are unchanged.
3) Next, we will create the command object, which is used to execute the SQL statement against
the database. In the SQL command, you have passed the connection object and the SQL
string.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
80

C# Update Database…

Code Explanation…
4) In our data adapter command, we now associate the insert SQL command to our adapter. We
also then issue the ExecuteNonQuery method which is used to execute the Update statement
against our database.
5) We finally close all the objects related to our database operation.
 Remember this is always a good practice.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
81

C# Update Database…
 When the above code is set, and the project is executed using Visual Studio, you will get the

below output. Once the form is displayed, click the Connect button.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
82

C# delete records from database table

 Just like Accessing data, C# has the ability to delete existing records from the database as well.

To showcase how to delete records into our database, let’s take the same table structure which
was used above.
 Let’s change the code in our form, so that we can delete the following row

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
83

C# delete records from database table

 So, let’s add the following code to our program. The below code snippet will be used to delete an

existing record in our database.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
84

C# delete records from database table

Code Explanation
1) The Key difference in this code is that we are now issuing the delete SQL statement. The delete
statement is used to delete the row in the student table in which the studID has a value of
sweg2381.
2) In our data adapter command, we now associate the insert SQL command to our adapter. We
also then issue the ExecuteNonQuery method which is used to execute the Delete statement
against our database.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
85

C# delete records from database table

 When the above code is set, and the project is executed using Visual Studio, you will get the

below output. Once the form is displayed, click the Connect button.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
86

Connecting Controls to Data

 In the earlier sections, we have seen how to we can use C# commands such as SQLCommand

and SQLReader to fetch data from a database. We also saw how we read each row of the table
and use a messagebox to display the contents of a table to the user.
 But obviously, users don’t want to see data sent via message boxes and would want better

controls to display the data. Let’s take the below data structure in a table

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
87

Connecting Controls to Data…

 From the above data structure, the user would ideally want to see the studID, studName,

studDept and studAge displayed in a textbox.

 Secondly, they might want to have some sort of button control which could allow them to go to

the next record or to the previous record in the table.

 C# can reduce the additional coding effort by allowing binding of controls to data.

 What this means is that C# can automatically populate the value of the textbox as per a particular

field of the table.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
88

Connecting Controls to Data…

 So, you can have four textboxes in a windows form. You can then link each text box to the the

respective columns of the database table.

 This linking is done in the Visual Studio designer itself, and you don’t need to write extra code

for this.
Visual Studio will ensure that it writes the code for you to ensure the linkage works.

 Then when you run your application, the textbox controls will automatically connect to the

database, fetch the data and display it in the textbox controls. No coding is required from the
developer’s end to achieve this.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
89

Connecting Controls to Data…

 So, Let’s look at a code example of how we can achieve binding of controls.

 In our example, we are going to create four textboxes on the windows form. They are going to

represent the studID, studName, studDept and studAge respectively. They will be bound to the
studID, studName, studDept and stuAge fields of the database accordingly.
 Let’s follow the below-mentioned steps to achieve this.

Step 1) Construct the basic form. In the form drag and drop 2 components- labels and textboxes.
Then carry out the following substeps:

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
90

Connecting Controls to Data…

a) Put the text value of the labels as studID, studName, studDept and studAge.
b) Put the name property of the first textbox as txtID, txtName, txtDept, and numbAge.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
91

Connecting Controls to Data…

Step 2) The next step is to add a binding Navigator to the form.
• The binding Navigator control can automatically navigate through each row of the table.
• To add the binding navigator, just go to the toolbox and drag it to the form.
 see Figure 3
Step 3) The next step is to add a binding to our database.
• This can be done by going to any of the Textbox control and clicking on the DataBindings-
>Text property.
• The Binding Navigator is used to establish a link from your application to a database.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
92

Connecting Controls to Data…

Figure 3

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
93

Connecting Controls to Data…

 When you perform this step, Visual Studio will automatically add the required code to the
application to make sure the application is linked to the database.
 Normally the database in Visual Studio is referred to as a “Project Data Source”. So, to ensure
the connection is established between the application and the database, the first step is to create
a project data source.
 The following screen will show up. Click on the link- “Add Project Data Source”. When you
click on the project data source, you will be presented with a wizard; this will allow you to
define the database connection.
 See Figure 4
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
94

Connecting Controls to Data…

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
95

Connecting Controls to Data… a) In the screen which pops up , choose the

Step 4) Once you click on the Add Project Data Source type as Database and then
Data Source link, you will be presented with click on next button.
a wizard which will be used to create a
4
connection to the Student database. The
1
following steps show in detail what needs to
be configured during each step of the Choose the database option
wizard. 5

3 2

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
96

Connecting Controls to Data… c) In the screen which pops up , choose the

b) In the next screen, you need to start the Data Source type as Database and then
creation click on next button.

6 7

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
97

Connecting Controls to Data…

d) In the next screen, you need to start the
creation 9

1
0

1
1
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
98

Connecting Controls to Data…

e) In the next screen, click on the “Next”
button to confirm the creation of the
connection string.

Click Next

1
2
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
99

Connecting Controls to Data…

step 5) In this step,
a) Choose the tables of Student, which will
be shown in the next screen.
b) This table will now become an available
data source in the C# project

Click Finish

1
3
C# Lecture Notes by Jerusalem F.
…SQL database connection with C#...
100

Connecting Controls to Data…

 When you click the Finish button, visual studio will now ensure that the application can query

all the rows in the table Student.

Step 6) Now that the data source is defined, we now need to connect the txtID, txtName, txtDept,
and numbAge textbox to the Student table. When you click on the Text property of the textboxes,
then you will now see that the binding source to Student is available.
• For the first text box choose the studID and repeat this step for the other textboxes.
• The next slide steps show how we can navigate to each control and change the binding
accordingly.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
101

Connecting Controls to Data…

a) Click on the studID control.

Click the studID textBox

b) In the Properties window, you will see the properties of the TutorialID textbox. Go to the text property and click on the down arrow button.

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
102

Connecting Controls to Data…

b) When you click the down arrow button, you will see the studentBinding Source option. And
under this, you will see the options of studID, studName, studDept and studAge.
Click the studID textBox

Now choose the

field name

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
103

Connecting Controls to Data…

Repeat the above 3 steps for the other textboxes.
1) So, click on the studID text box
2) Go to the properties window
3) Choose the Text property
4) Choose the studID option under StudentBindingSource

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
104

Connecting Controls to Data…

Step 7) Next, we need to change the Binding Source property of the BindingNavigator to point to
our Student data source. The reason we do this is that the Binding Navigator also needs to know
which table it needs to refer to.
• The Binding Navigator is used to select the next or previous record in the table. So even though

the data source is added to the project as a whole and to the text box control, we still need to
ensure the Binding Navigator also has a link to our data source. In order to do this, we need to
click the Binding navigator object, go to the Binding Source property and choose the one that is
available

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
105

Connecting Controls to Data…

• Next, we need to go to the Properties window so that we can make the change to Binding
Source property.

Choose binding resource

C# Lecture Notes by Jerusalem F.

…SQL database connection with C#...
106

Connecting Controls to Data…

When all of the above steps are executed successfully, you will get the below-mentioned output.

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
107

 Language-Integrated Query (LINQ) is a powerful set of technologies based on the integration of

query capabilities directly into the C# language.

 LINQ Queries are the first-class language construct in C# .NET, just like classes, methods,

events.
 The LINQ provides a consistent query experience to query objects (LINQ to Objects), relational

databases (LINQ to SQL), and XML (LINQ to XML).

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
108

 LINQ (Language Integrated Query) is uniform query syntax in C# to retrieve data from different

sources and formats.

 It is integrated in C#, thereby eliminating the mismatch between programming languages and

databases, as well as providing a single querying interface for different types of data sources.
 For example, SQL is a Structured Query Language used to save and retrieve data from a

database.
 In the same way, LINQ is a structured query syntax built in C# and VB.NET to retrieve data

from different types of data sources such as collections, ADO.Net DataSet, XML Docs, web
service and MS SQL Server and other databases.
C# Lecture Notes by Jerusalem F.
LINQ Architecture…
109

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
110

 LINQ queries return results as objects.

 It enables you to uses object-oriented approach on the result set and not to worry about

transforming different formats of results into objects.

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
111
//C# program to demonstrate LING query in Array
using System; // Query execution
using System.Linq; foreach (var name in myLinqQuery)
namespace LINQ_Query_to_Array {
Console.Write(name + " ");
internal class Program {
}
static void Main(string[] args) {
// Data source
}
string[] courses = { "Testing", "Design", }
"Programming", "Embedded" };

// LINQ Query
var myLinqQuery = from name in courses
where name.Contains('m')
select name;

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
112

Advantages of LINQ
Familiar language: no need to learn a new query language for each type of data source or data format.

Less coding: It reduces the amount of code to be written as compared with a more traditional approach.

Readable code: LINQ makes the code more readable it can be easily understood.

Standardized way of querying multiple data sources: The same LINQ syntax can be used to query

multiple data sources.

Compile time safety of queries: It provides type checking of objects at compile time.

IntelliSense Support: LINQ provides IntelliSense for generic collections.

Shaping data: You can retrieve data in different shapes.

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
113

LINQ API in .NET

 We can write LINQ queries

for the classes that

implement IEnumerable<T>
or Iqueryable<T> interface.
 The System.Linq namespace

includes the following classes

and interfaces require for
LINQ queries. See the figure

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
114

LINQ Query Syntax

There are two basic ways to write a LINQ query to IEnumerable collection or IQueryable data

sources.
1) Query Syntax or Query Expression Syntax
2) Method Syntax or Method Extension Syntax or Fluent

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
115

1) LINQ Query Syntax

 Query syntax is similar to SQL (Structured Query Language) for the database.

 It is defined within the C# code.

from <range variable> in Collection

LINQ query syntax:
<Standard Query Operators> <lambda expression>
<select or groupBy operator> <result formation>

Structure of LINQ query syntax:

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
116
1) LINQ Query Syntax
"Software Verification",
// C# program to demonstrate LINQ Query "Software Security" ,
Syntax
"Web Programming"
using System;
};
using System.Linq;
// LINQ Query Syntax
namespace LINQ_Query_Syntax {
var result = from s in stringList
internal class Program {
where s.Contains("Java")
static void Main(string[] args) {
select s;
// string collection
// Output the results
IList<string> stringList = new List<string>() {
foreach (string str in result) {
"Window Programming (C#)",
Console.WriteLine(str); }
"Advanced Programming (Java)",
} } }

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
117

2) LINQ Method Syntax

 Method syntax (fluent syntax) uses extension methods included in the Enumerable or Queryable

static class, similar to how you would call the extension method of any class.
The compiler converts query syntax into method syntax at compile time.

The following is a sample LINQ method syntax query that returns a collection of strings which

contains a word “Java".

Structure of LINQ method syntax:

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
118
// C# program to demonstrate LINQ Method Syntax
using System; new Course() { courseCode = 3024, courseName =
"Machine Learning", courseStatus = "minor"} ,
using System.Linq;
new Course() { courseCode = 3083, courseName =
using static LINQ_Query_Syntax.Program; "Adaptive Web Systems", courseStatus = "major" } ,
namespace LINQ_Method_Syntax { new Course() { courseCode = 4028, courseName =
internal class Program { "Discrete Mathematics", courseStatus = "minor" } ,
public class Course { new Course() { courseCode = 4113, courseName =
"Human Computer Interaction" , courseStatus = "minor"} ,
public int courseCode { get; set; }
new Course() { courseCode = 3122, courseName =
public string courseName { get; set; } "Network Security" , courseStatus = "minor" }
public string courseStatus { get; set; } };
}
static void Main(string[] args) { // LINQ Method Syntax to find out major courses
// Course collection var majorCourses = courseList.Where(s => s.courseStatus ==
"major" ).ToList<Course>();
IList<Course> courseList = new List<Course>()
{

C# Lecture Notes by Jerusalem F.

LINQ Architecture…
119

// You can now work with the 'majorCourses' list

foreach (var course in majorCourses)
{
Console.WriteLine($"courseCode:{course.courseCode}, courseName: {course.courseName}, courseStatus:
{course.courseStatus}");
Console.ReadKey(true);
}
}
}
}

C# Lecture Notes by Jerusalem F.

The .NET Data providers
120

Some common data providers in .net are:

 System.Data.SqlClient: SQL Server

 System.Data.OleDb: OLE DB
 System.Data.Odbc: ODBC
 MySql.Data.MySqlClient: MySQL
Example: SQL server (built in visual studio) provider

C# Lecture Notes by Jerusalem F.

Working with the common .NET Data providers
121

Connection creation Operations

C# Lecture Notes by Jerusalem F.

Working with the common .NET Data providers…
122

Insert Operations

C# Lecture Notes by Jerusalem F.

Working with the common .NET Data providers…
Select Operations 123

C# Lecture Notes by Jerusalem F.

Working with the common .NET Data providers…
124

Update Operations

C# Lecture Notes by Jerusalem F.

Working with the common .NET Data providers…
125

Delete Operations

C# Lecture Notes by Jerusalem F.

The Dataset Component
126

 Dataset Features:

 In-memory cache of database data

 Multiple tables and relationships
 Disconnected data access
 XML integration

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access
127

C# DataGridView
 Data Grids are used to display data from a table in a grid-like format. When a user sees
table data, they normally prefer seeing all the table rows in one shot.
 This can be achieved if we can display the data in a grid on the form.

 C# and Visual Studio have inbuilt data grids; this can be used to display data.

 In our example, we will have a data grid, which will be used to display the studID,

studNAme, studDept and studAge values from the Student table.

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access…
128

 Displaying data in a tabular format is a common and recurring task, and the DataGridView

control serves as a comprehensive solution for achieving this with Windows Forms.
 It offers a high degree of customization and extensibility, boasting numerous properties,

methods, and events to tailor its appearance and behavior to your requirements.
The DataGridView control simplifies the process of defining the visual aspects of cells and

formatting the display of cell values.

 Cells serve as the fundamental units of interaction within the DataGridView, all deriving

from the base class DataGridViewCell.

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access…
129

 Each cell within the control can possess its own unique style, encompassing elements such

as text format, background color, foreground color, and font.

 Nevertheless, it is common for multiple cells to share certain style characteristics.

 By default, the data type for the cell's Value property is Object.

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access…
130

C# DataGridView Binding - SQL Server dataset

 The DataGridView offers the capability to present data in three distinct modes: Bound

mode, unbound mode, and Virtual mode.

 Bound mode, an immensely practical approach, facilitates efficient data management

through automatic interaction with the underlying data store.

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access…
131

Binding the DataGridView

 Particularly, binding the DataGridView to a database table stands out as an exceedingly

prevalent usage scenario, enabling seamless integration with the existing data source.
 On the other hand, unbound mode is a viable option when the objective involves displaying

relatively smaller data sets that can be programmatically handled.

 This mode allows for more hands-on control over the data presentation, empowering

developers to manipulate and manage the information within the DataGridView as needed.

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access…
132

Step 1) Drag the DataGridView control from the toolbox to the Form in Visual Studio. The
DataGridView control is used in Visual Studio to display the rows of a table in a grid-like format.

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access…
133

Step 2) In the next step, we need to connect our data grid to the database.
a) First, you need to choose the grid and click on the arrow in the grid. This will bring up the grid
configuration options.
b) In the configuration options, just choose the data source as StudentBindingSource which was
the data source created in the earlier section.

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access…
134

If all the above steps are executed as shown, you will get the below-mentioned output.

C# Lecture Notes by Jerusalem F.

Using the DataGridView for database access…
135

 DataGridView Features:

 Automatic column generation

 Sorting and filtering
 Custom formatting
 Edit capabilities

C# Lecture Notes by Jerusalem F.