Visual

Programming
Chapter 6: ADOT.NET in C#

11/27/2024 Compiled by Dagne Walle 1

ADO.NET Architecture
ADO.NET introduced data providers that are a
set of special classes to access a specific
database, execute SQL commands and retrieve
data.
 ADO.NET uses a multilayer architecture that
mainly has a few concepts, for instance
Connection, Reader, Command, Adapter and
Dataset objects.
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

11/27/2024 Compiled by Dagne Walle 2

 ADO.NET

11/27/2024 Compiled by Dagne Walle 3

 ADO.NET
• ADO.NET is designed to access relational
databases
• Example:
– Sales database with customers, orders, and
products

11/27/2024 Compiled by Dagne Walle 4

 ADO.NET
• ADO.NET architecture based on data providers
– data providers encapsulate DB-specific
details
.NET
Programmer

ADO.NET

Data Provider

DB

11/27/2024 Compiled by Dagne Walle 5

 ADO.NET object model
ADO.NET
• ADO.NET is an object-oriented approach
• Classes are spread across a number of FCL
namespaces
– some are provider-neutral, others are provider-
specific provider-
System.Data neutral

System.Data.Common
SQL Server
System.Data.SqlClient
other DBs, e.g. MS
Access
System.Data.OleDb
ODBC (Open Database Connectivity)
System.Data.Odbc

11/27/2024 Compiled by Dagne Walle 6

ADO.NET Namespaces

11/27/2024 Compiled by Dagne Walle 7

ADO.NET
In simple terms, you can think of ADO.NET, as a
set of classes(Framework), that can be used to
interact with data sources like Databases and
XML files. This data can, then be consumed in
any .NET application.
 ADO stands for Microsoft ActiveX Data Objects.
The following, a few of the different types of .NET
applications that use ADO.NET to connect to a
database, execute commands, and retrieve data.
Dot Net Data Providers:
Data Provider for SQL Server-
System.Data.SqlClient
Data Provider for Oracle-
System.Data.OracleClient
11/27/2024 Compiled by Dagne Walle 8
 ADO.NET Data Providers
The ADO.NET Framework comes with the following
providers:
OLEDB: The OLEDB provider, expressed through
the System.Data.OleDb namespace. You can use
this provider to access SQL Server 6.5 and
earlier, SyBase, DB2/400, and Microsoft Access.
ODBC: The ODBC provider, expressed through
the System.Data.Odbc namespace. This provider
is typically used when no newer provider is
available.
SQL Server: The Microsoft SQL Server provider,
expressed through the System.Data.SqlClient
namespace. It Contains classes that provide
functionality similar to the generic OleDb
11/27/2024 Compiled by Dagne Walle 9
 ADO.NET Data Providers
The ODBC .NET data provider provides access to
ODBC data sources with the help of native ODBC
drivers in the same way that the OleDb .Net data
provider accesses native OLE DB providers.
One of the best things about working with
ADO.NET data providers is all data providers
define the similar class hierarchy. The only things
you need to change are the classes and the
connection string.

11/27/2024 Compiled by Dagne Walle 10

ADO.NET Class
Connection : 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.
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,
11/27/2024 Compiled by Dagne Walle 11
ADO.NET Class
DataSet: is a disconnected architecture
technology. It contains zero or more tables and
relationships. When you work with a dataset, the
data in the data source is not touched at all.
Instead, all the changes are made locally to the
dataset in memory. In the following example, you
will see how to retrieve data from a SQL Server
table and use it to fill in a DataTable object in the
DataSet
DataAdapter : bridges the gap between the
disconnected DataTable objects and the physical
data source.
SqlDataAdapter is capable of executing a
SELECT, DELETE and UPDATE statement on a
data source Compiled
11/27/2024 as well asWalle
by Dagne extracting input from the12
ADO.NET Objects
Core objects that make up a .NET Framework data
provider:
Connection object: Establishes a connection to
a specific data source. The base class for all
Connection objects is the DbConnection class.
Command object: Executes a command against
a data source. Exposes Parameters and can
execute in the scope of a Transaction from a
Connection. The base class for all Command
objects is the DbCommand class.
DataReader object : Reads a forward-only, read-
only stream of data from a data source. The base
class for all DataReader objects is the
DbDataReader class.
11/27/2024 Compiled by Dagne Walle 13
ADO.NET Methods
 The ExecuteScalar() method returns the value stored in
the first field of the first row of a result set generated by
the command’s SELECT query. This method is usually used
to count the total number of rows in the table as in the
following.
 The ExecuteNonQuery() method executes commands
that don’t return a result set, for instance INSERT, UPDATE
and DELETE. Here in this example we made a modification
to a specific record in the Customer table of the Adventure
Works database
 ExecuteReader()-use when the T-SQL statement returns
more than a single value. For example, if the query returns
rows of data.
 Open() to opens a database connection with the property
settings specified by the ConnectionString.
 • Close() to closes the connection to the database. This is
the preferred way
11/27/2024
to close any opened connections.
Compiled by Dagne Walle 14
ADO. Net class for SQL Server
Database
SqlConnection: It is used to create SQL
Server connection. This class cannot be
inherited.
SqlCommand: It is used to execute the SQL
queries. This class cannot be inherited.
SqlDataAdapter: It represents a set of data
commands and a database connection that
are used to fill the DataSet. This class cannot
be inherited.
SqlDataReader: It is used to read rows from a
SQL Server database. This class cannot be
inherited.
error is
11/27/2024 Compiled by Dagne Walle 15
ADO. Net class for OleDb (Microsoft Access
Database)
Connection string for access database
Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccessFile.accdb;
Persist Security Info=False;
OleDbConnection: It is used to create OleDb
Server connection. This class cannot be inherited.
OleDbCommand: It is used to execute database
queries. This class cannot be inherited.
OleDbDataAdapter: It represents a set of data
commands and a database connection that are
used to fill the DataSet. This class cannot be
inherited.
OleDbDataReader:It is used to read rows from a
OleDb database. error is
11/27/2024 Compiled by Dagne Walle 16
ADO. Net class for MySQL Server
Database
MySqlConnection: It is used to create MySQL
Server connection. This class cannot be inherited.
MySqlCommand: It is used to execute database
queries. This class cannot be inherited.
MySqlDataAdapter: It represents a set of data
commands and a database connection that are
used to fill the DataSet. This class cannot be
inherited.
MySqlDataReader: It is used to read rows from a
MySQL Server database. This class cannot be
inherited

11/27/2024 Compiled by Dagne Walle 17

 Overview
Basic Steps toof database
Access access
the Database
• Three steps:
1. open connection to database
2. execute SQL to retrieve records / update
DB
3. close connection

11/27/2024 Compiled by Dagne Walle 18

Open connection on oleDb Database
• Connections are opened based on connection string
info
– here we open a connection to a MS Access 2000
database("Sales.mdb“)
import System.Data.*;
import System.Data.OleDb.*;

String sConnection;
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=Sales.mdb";
connection
OleDbConnection dbConn;
dbConn = new OleDbConnection(sConnection);
dbConn.Open();

MessageBox.Show(dbConn.get_State().toString());

11/27/2024 Compiled by Dagne Walle 19

Retrieve Records from oleDb
Database
• Retrieve records via SQL Select query
– read-only access via DataReader & field names

String sql, fn, ln; Customer c;

sql = "Select * From Customers Order By LastName Asc, FirstName Asc;";

OleDbCommand dbCmd; command connection

OleDbDataReader dbReader;
record
dbCmd = new OleDbCommand(sql, dbConn); record
record
data reader
dbReader = dbCmd.ExecuteReader();

while ( dbReader.Read() ) // retrieve records one-by-one…

{
fn = String.valueOf(dbReader.get_Item("FirstName"));
ln = String.valueOf(dbReader.get_Item("LastName"));
c = new Customer(fn, ln);
this.listBox1.get_Items().Add(c);
field name of data value in current
} record

11/27/2024 Compiled by Dagne Walle 20

DataSets

DataSet

DataTable

DataColumn

DataRow

DataRelation

11/27/2024 Compiled by Dagne Walle 21

 DataSets
DataSets
• DataSets are an in-memory data structure
– easily filled with data from a database
– easily passed around
– easily displayed in a GUI app

DataAdapter Command Connection

DataSet DB

Name Price Stock

Ants $ 0.49 5000

Birds $ 4.49 500

Cats $29.95 100

Dogs $79.95 20
• DataSet mirrors the database
"Table" – data forms a temporary table called
"Table" within DataSet

11/27/2024 Compiled by Dagne Walle 22

Filling a DataSet

• DataAdapter object is used to fill a DataSet…

Name Price Stock
• Example: Ants $ 0.49 5000

– fill DataSet with all product data Birds $ 4.49 500

Cats $29.95 100

Dogs $79.95 20

"Table"
sql = "Select * From Products Order By Name Asc;";
.
.
.
DataSet ds;
OleDbDataAdapter adapter;
ds = new DataSet();
adapter = new OleDbDataAdapter(dbCmd);

dbConn.Open();
adapter.Fill(ds);
dbConn.Close();

11/27/2024 Compiled by Dagne Walle 23

DataSets on Sql Server Database

DataSet Example

string sConnString = “Persist Security Info=False;” +

“User ID=sa;Initial Catalog=Northwind;” +
“Data Source=MYSERVER”;
SqlConnection conn = new SqlConnection(sConnString);
conn.Open();
string sQueryString = “SELECT CompanyName FROM Customers”;
SqlDataAdapter myDSAdapter = new SqlDataAdapter();
DataSet myDataSet = new DataSet();
myDSAdapter.SelectCommand = new SqlCommand(sQueryString, conn);
myDSAdapter.Fill(myDataSet);
conn.Close();

11/27/2024 Compiled by Dagne Walle 24

DataGrid Display

• DataSet can be bound to DataGrid control

for easy display
.
.
– one line of code!
.
this.dataGrid1.SetDataBinding(ds, "Table");

11/27/2024 Compiled by Dagne Walle 25

DataReader

DataReader Example
string sConnString = “Provider=SQLOLEDB.1;” +
“User ID=sa;Initial Catalog=Northwind;” +
“Data Source=MYSERVER”;
OleDbConnection conn = new OleDbConnection(sConnString);
conn.Open();
string sQueryString = “SELECT CompanyName FROM Customers”;
OleDbCommand myCommand = new OleDbCommand(sQueryString, conn);
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read()) {
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
conn.Close();

11/27/2024 Compiled by Dagne Walle 26

Data Inserting in MySql Database
using MySql.Data.MySqlClient;
string constring =
"server=localhost;port=3306;username=root;passwo
rd=;database=student";
MySqlConnection con = new
MySqlConnection(constring);
con.Open();
MessageBox.Show("connection esstablished");
string query = "insert into student
values('"+id.Text+"','"+name.Text+"','"+age.Text+"','
"+dep.Text+"')";
MySqlCommand com = new MySqlCommand(query,
con);
int insert= com.ExecuteNonQuery();
11/27/2024 Compiled by Dagne Walle 27
Data Updating in MySql Database
using MySql.Data.MySqlClient;
string constring =
"server=localhost;port=3306;username=root;password
=;database=student";
MySqlConnection con = new
MySqlConnection(constring);
con.Open();
string query = "update student set Name='" +
name.Text + "',Age='" + age.Text + "',Department='" +
dep.Text + "' where ID='" + id.Text + "'";
MySqlCommand com = new
MySqlCommand(query, con);
int update=com.ExecuteNonQuery();
11/27/2024
MessageBox.Show("update"
Compiled by Dagne Walle
+ 28
Data Deleting form MySql Database
using MySql.Data.MySqlClient;
string constring =
"server=localhost;port=3306;username=root;password
=;database=student";
MySqlConnection con = new
MySqlConnection(constring);
con.Open();
string query = "delete from student where
ID='" + id.Text + "'";
MySqlCommand com = new
MySqlCommand(query, con);
int delete = com.ExecuteNonQuery();
MessageBox.Show("delete" + delete.ToString());
11/27/2024 Compiled by Dagne Walle 29
Loading all Data from MySql Database
string constring =
"server=localhost;port=3306;username=root;password
=;database=student";
MySqlConnection con = new
MySqlConnection(constring);
con.Open();
MessageBox.Show("connection esstablished");
string query = "select *from student";
MySqlCommand com = new
MySqlCommand(query, con);
MySqlDataReader read = com.ExecuteReader();
BindingSource source = new BindingSource();
source.DataSource = read;
11/27/2024 dataGridView1.DataSource
Compiled by Dagne Walle = source; 30
Searching Data from MySql Database
string constring =
"server=localhost;port=3306;username=root;password
=;database=student";
MySqlConnection con = new
MySqlConnection(constring);
con.Open();
MessageBox.Show("connection esstablished");

string query = "select *from student where

ID='"+st.Text+"'";
MySqlCommand com = new
MySqlCommand(query, con);
MySqlDataReader read = com.ExecuteReader();
11/27/2024 Compiled by Dagne Walle 31
Searching Data from MySql Database
while(read.Read())
{
string ID = read.GetInt32("ID").ToString();
id.Text = ID;
string Name = read.GetString("Name");
name.Text = Name;
string Age = read.GetInt32("Age").ToString();
age.Text = Age.ToString();
string Department =
read.GetString("Department");
dep.Text = Department;
MessageBox.Show("success");
}
11/27/2024 Compiled by Dagne Walle 32
Injection Prevention
SQL Injection Prevention: Preventing SQL
injection using Parameterized queries and Stored
procedures.
 How to execute stored procedures and
parameterized queries using ADO.NET command
object. Using Parameterize queries:

11/27/2024 Compiled by Dagne Walle 33

.

Question??

Thank you!!!

11/27/2024 Compiled by Dagne Walle 34