Event Driven Programming

Chapter: 5

Database Programming
 Outlines
• The ADO.NET Architecture
• LINQ Architecture
• The .NET Data providers
• Working with the common .NET Data
providers
• The Dataset Component

2
 Introduction
⏵Database programming in C# involves using
various technologies and techniques to interact
with databases.
⏵One of the most commonly used technologies is
ADO.NET, which provides a set of classes for
working with databases.
⏵Which is used to connecting a project to
Database system and access database objects.
⏵That means it is used to connect to Oracle, SQL
server, Ms Access.

3
 ADO.NET
 ADO.NET -stands for ActiveX Data Object.
 It is a database access technology created by Microsoft as
apart of its.NET framework that can access any kind of data
source.
 It is a part of the .NET Framework which is used to establish
a connection between the .NET Application and different
data sources.
 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.

4
 …Continued
 It is one of the Microsoft’s data access
technology which is used to communicate
between the .NET application(console,
windows, web forms etc) and data sources
such as sql server, oracle, Mysql etc.
 It provides a bridge between the front-end
controls and the back-end database.

5
 …Continued
⏵ADO.NET is a set of classes that can be used to
connect, retrieve, insert, update and delete data(i.e
performing CRUD operation) from a data sources.
⏵It has two main components that are used for
accessing and manipulating data are the .NET
Framework data provider and the DataSet

6
 ADO.NET Framework Data Providers
• Data provider is used to connect to the database,
execute commands and retrieve the record.
• It is lightweight component with better
performance.
Common Providers:
- SQL Server: System.Data.SqlClient
- OLE DB: System.Data.OleDb
- ODBC: System.Data.Odbc
- Oracle: System.Data.OracleClient
7
…Continued

8
 ADO.NET methods
⏵ADO.NET provides a rich set of classes for data
access, and it includes several important methods
across different classes.
⏵ExecuteScalar(): Executes the command and returns a
single value (e.g., an aggregate value).
⏵ExecuteNonQuery(): Executes a command that changes
data but does not return any data (e.g., INSERT, UPDATE,
DELETE).
⏵ExecuteReader(): Executes the command and returns a
SqlDataReader for reading the data. for example if the
query returns a row data.
⏵Open(): Opens a database connection.
⏵Close(): Closes the database connection.

9
 .NET Framework Data Providers Objects

• Following are the core object of Data

10
 ADO.NET Architecture
• The most important components of ADO.NET
Architecture .
– Connection
– Command
– DataReader
– DataAdapter
– DataSet
– DataView
• The 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.
11
…Continued

12
 LINQ Architecture
Language Integrated Query (LINQ) provides a unified
model for querying data from different data sources
using a consistent syntax.
Key Components:
- Standard Query Operators: Methods like Select,
Where, OrderBy, GroupBy, Join, Aggregate.
- LINQ Providers: Translate LINQ queries into a format
suitable for the data source.

13
 ADO.NET DataSet in C#
The DataSet represents a subset of the database
in memory. That means the ADO.NET DataSet is
a collection of tables containing relational data
in memory in tabular format.
Note: The ADO.NET DataSet class belongs to
the System.Data namespace

14
 …Continued
• Data sets are an in-memory data structure.
– Easily filled with data from a data base
– Easily passed around
– Easily displayed in a GUI app

15
 Open Connection on SQL Database
The steps to connect to Microsoft SQL server
database:
 First you have to create a database in SQL Server; the
server can be any version.
 Let’s say you have a database created in SQL server
which is called crudb.
 Under this database you have a table called stud.
 Now let us connect to this database.

16
 …Continued
First select TOOLS and select “Connect to Database”

17
 …Continued
 Select a server name and the database name in the add
connection dialog box.

18
 …Continued
 Next click test connection button to check if the connection is
succeeded

19
 …Continued
 Now let us create Form in visual studio using c#, which performs
CRUD operations.

20
 C# sample codes
 To insert values for the above window form as well as
the database.

21
 …Continued
✓ cmd: This is the SqlCommand object representing the SQL command you
want to execute.
✓ Parameters: This is the collection of parameters associated with the
SqlCommand.
✓ AddWithValue: This method adds a parameter and its value to the
SqlCommand's Parameters collection.
✓ @Id: This is the name of the parameter in the SQL command. The @
symbol indicates that it is a parameter placeholder.
✓ It is part of a parameterized query, which is a way to safely pass user
input to a database query without exposing it to SQL injection attacks.
✓ int.Parse(textBox1.Text): This converts the text from textBox1 to an
integer. The value is then assigned to the @Id parameter.

22
 …Continued
 To update values for the above window form as
well as the database.

23
 …Continued
 To delete values for the above window form as well
as the database.

24
 …Continued
 To search values for the above window form

 DataTable is a class in ADO.NET that represents a single table of in-memory

• Read more about what ASP.NETframework is

26
27