DCIT 318

PROGRAMMING II

Session 8 – Database Programming Using

ADO.NET Part I
Lecturer: Mr. Paul Ammah, CSD
Contact Information: pammah@ug.edu.gh

Department of Computer Science

School of Physical and Mathematical Sciences
2023/2024
 ADO.NET
• ADO.NET is a set of classes for database access.
• It is a specification that unifies access to relational
databases, XML files, and other application data.
• The ADO.NET classes are found in System.Data
namespace.
• The full form of ADO.Net is ActiveX® Data Objects
• ADO.Net can be used by any .Net Language

Slide 2
 ADO.NET Architecture
• The two main components of ADO.NET for accessing and
manipulating data are
– the .NET Framework data providers
– the DataSet
• A .NET Framework data provider is used for connecting to
a database, executing commands, and retrieving result
• Those results are either:
– processed directly
– placed in a DataSet in order to be exposed to the user as needed
– combined with data from multiple sources
– remoted between tiers.

Slide 3
 Data Providers

OLE DB - Object Linking and Embedding Database

ODBC - Open Database Connectivity
Slide 4
 Core Objects of .NET Framework Data
Providers

• In addition to the core classes .NET Framework data provider also contains these classes:
• Transaction, CommandBuilder, ConnectionStringBuilder,
• Parameter, Exception, Error and ClientPermission

Slide 5
 ADO.NET DataSets
• The DataSet is a memory-resident representation of
data that provides a consistent relational
programming model regardless of the data source.
• The methods and objects in a DataSet are consistent
with those in the relational database model.
• DataSet contains a collection of zero or more tables
represented by DataTable objects.

Slide 6
DataSet Object Model

Slide 7
 DataSet Object Model cont’d
• The DataTableCollection contains all the DataTable objects in a
DataSet.
• A DataTable is defined in the System.Data namespace and
represents a single table of memory-resident data.
• A collection of rows (DataRow) represented by the
DataRowCollection
• A collection of columns (DataColumn) in a DataTable is
represented by a DataColumnCollection
• A collection of constraints on a DataTable is represented by a
ConstraintCollection
• A DataView enables you to create different views of the data
stored in a DataTable Slide 8
Retrieving and Modifying Data in ADO.NET

• .NET Framework data providers of ADO.NET allow

you to execute commands as well as to retrieve data
by using a
– DataReader
– DataAdapter

• Updating data involves using

– DataAdapter and DataSet, and Command objects; and it
may also involve using transactions.

Slide 9
 Connecting to a Data Source
• Each .NET Framework data provider included with
the .NET Framework has a DbConnection object
– Data Provider for OLE DB includes an OleDbConnection
object
– Data Provider for SQL Server includes a SqlConnection
object
– Data Provider for ODBC includes an OdbcConnection
object
– Data Provider for Oracle includes an OracleConnection
object

Slide 10
 Connection Strings
• A connection string contains initialization information
that is passed as a parameter from a data provider to
a data source.
• The data provider receives the connection string as
the value of the DbConnection.ConnectionString
property
• The provider parses the connection string and
ensures that the syntax is correct and that the
keywords are supported.

Slide 11
 Connection String Syntax
• A connection string is a semicolon-delimited list of
key/value parameter pairs:
keyword1=value; keyword2=value;
• Keywords are not case-sensitive. Values, however,
may be case-sensitive, depending on the data source.

Slide 12
 Connection String Parameters
• ApplicationIntent: Declares the application workload type
when connecting to a server.
– Possible values are ReadOnly and ReadWrite
• Data Source or Server or Address: the name or network
address of the instance of SQL Server to which to connect.
– The port number can be specified after the server name:
– server=tcp:servername, portnumber
• Initial Catalog or Database: The name of the database.
• User ID or UID or User: The SQL Server login account
• Password or PWD: The password for the SQL Server
account logging on
Slide 13
 Connection String Parameters cont’d
• Integrated Security or Trusted_Connection:
– When false, User ID and Password are specified in the
connection.
– When true, the current Windows account credentials are
used for authentication.
• Persist Security Info or PersistSecurityInfo: When set
to false or no (strongly recommended), security-
sensitive information, such as the password, is not
returned as part of the connection

Slide 14
Connection String: SqlClient Example
"Persist Security Info=False;Integrated
Security=true;Initial Catalog=Northwind;server=(local)”

"Persist Security Info=False; User ID=root;

Password=1290304;Initial Catalog=Northwind;
server=(local)”

Slide 15
Connection String: OleDb Example
"Provider=MSDAORA; Data Source=ORACLE8i7;Persist
Security Info=False;Integrated Security=Yes"

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\

bin\LocalAccess40.mdb"

"Provider=SQLOLEDB;Data Source=(local);Integrated
Security=SSPI"

Slide 16
 Connection String: Odbc Example
"Driver={SQL
Server};Server=(local);Trusted_Connection=Yes;Database=AdventureWorks;"

"Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security

Info=False;Trusted_Connection=Yes"

"Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\bin\Northwind.mdb"

"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls"

"Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin"

"DSN=dsnname"
Slide 17
Connection String: OracleClient Example
"Data Source=Oracle8i;Integrated Security=yes”

"Data Source=Oracle8i; Persist Security Info=False; User

ID=root; Password=1290304"

Slide 18
 Connecting to SQL Server
• The using block in C# automatically disposes of the connection
when the code exits the block, even in the case of an unhandled
exception
• A connection string contains initialization information that is
passed as a parameter from a data provider to a data source. E.g.
“Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword; “

using (SqlConnection connection = new SqlConnection(connectionString))

{
connection.Open();
// Do work here.
}

Slide 19
 Connecting to an OLE DB Data Source
// Assumes connectionString is a valid connection string.

using (
OleDbConnection connection = new OleDbConnection(connectionString)
)
{
connection.Open();
// Do work here.
}

Slide 20
 Connecting to an ODBC Data Source
// Assumes connectionString is a valid connection string.

using (
OdbcConnection connection = new OdbcConnection(connectionString)
)
{
connection.Open();
// Do work here.
}

Slide 21
 Connecting to an Oracle Data Source
// Assumes connectionString is a valid connection string.

using (
OracleConnection connection =
new OracleConnection(connectionString)
)
{
connection.Open();
// Do work here.
}

Slide 22
 Commands and Parameters
• After establishing a connection to a data source, you
can execute commands and return results from the
data source using a DbCommand object.
• Create a command for a particular connection using
the CreateCommand method of a DbConnection
object.
• The SQL statement being executed by the command
can be configured using the CommandText property

Slide 23
 Executing a Command
• Each .NET Framework data provider has its own
command object that inherits from DbCommand
– Data Provider for OLE DB includes an OleDbCommand
object
– Data Provider for SQL Server includes a SqlCommand
object
– Data Provider for ODBC includes an OdbcCommand object
– Data Provider for Oracle includes an OracleCommand
object

Slide 24
 Command Execution Methods & Command
Types
Command Objects expose methods for executing commands based on the type of command
and desired return value as described below:

Command object also supports a CommandType enumeration that specifies how a

command string is interpreted

Slide 25
 ExecuteReader Example
public void ReadMyData(string connectionString)
{ //Executes commands that return rows.
string queryString = "SELECT OrderID, CustomerID FROM Orders";
using (
OleDbConnection connection = new OleDbConnection(connectionString)
)
{
OleDbCommand command = new OleDbCommand(queryString, connection);
connection.Open();
OleDbDataReader reader = command.ExecuteReader();

while (reader.Read())
{
Console.WriteLine(reader.GetInt32(0) + ", " +
reader.GetString(1));
}
//always close the reader
reader.Close();
}
} Slide 26
 ExecuteNonQuery Example
public void InsertRow(string connectionString, string insertSQL)
{
using (OdbcConnection connection =
new OdbcConnection(connectionString))
{
// The insertSQL string contains a SQL statement that
// inserts a new row in the source table.
OdbcCommand command = new OdbcCommand(insertSQL, connection);

// Open the connection and execute the insert command.

try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// The connection is automatically closed when the
// code exits the using block.
Executes
} commands such as SQL INSERT, DELETE, UPDATE, and SET statements.
Slide 27
 ExecuteScalar Example
static public int GetTotalNumberOfRegions(string connString)
{
Int32 count = 0;
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
count = (Int32) cmd.ExecuteScalar();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return (int) count;
}

Slide 28
 Using Parameters
• Command objects use parameters to pass values to
SQL statements or stored procedures, providing type
checking and validation
• Unlike command text, parameter input is treated as a
literal value, not as executable code.
– This behavior helps guard against "SQL injection" attack
• When adding parameters, you must supply a
ParameterDirection property for parameters other
than input parameters.

Slide 29
ParameterDirection

Slide 30
Parameter Placeholders and Data Types
• The syntax for parameter placeholders depends on the data
source.
• The SqlCommand data provider uses named parameters in the
format @parametername
• With an OleDbCommand or OdbcCommand, parameters must
be added to the Parameters collection in the order defined
• You specify the type of a Parameter in a generic manner by
setting the DbType property of the Parameter object to a
particular DbType.
• E.g. DbType:
– Boolean, Binary, DateTime, Date, Decimal, Double, Int16, Int32, Int64,
String, VarNumeric, UInt16, UInt32, UInt64

Slide 31
Parameter Placeholders

Slide 32
 Adding Parameters
• Each .NET Framework data provider has its own
command object that inherits from DbParameter
– Data Provider for OLE DB includes an OleDbParameter
object
– Data Provider for SQL Server includes a SqlParameter
object
– Data Provider for ODBC includes an OdbcParameter object
– Data Provider for Oracle includes an OracleParameter
object

Slide 33
 Parameter Example
sql = SELECT * FROM Customers WHERE CustomerID = ?

OdbcCommand command = new OdbcCommand(sql,

connection);

OdbcParameter parameter =
new OdbcParameter(“@p1”,OdbcType.Int);

parameter.Value = 20;
parameter.Direction = ParameterDirection.Input;
command.Parameters.Add(parameter);

OdbcDataReader reader = command.ExecuteReader();

Slide 34
 DataReaders
• You can use ADO.NET DataReader to retrieve a read-
only, forward-only stream of data from a database.
• Results are returned as the query executes
• Results are stored in the network buffer on the client
until they are requested using the Read method of
the DataReader.
• Using the DataReader can increase application
performance both by
– retrieving data as soon as it is available,
– and (by default) storing only one row at a time in memory
Slide 35
 DbDataReaders
• Each .NET Framework data provider has its own
DbDataReaders
– Data Provider for OLE DB includes an OleDbDataReader
object
– Data Provider for SQL Server includes a SqlDataReader
object
– Data Provider for ODBC includes an OdbcDataReader
object
– Data Provider for Oracle includes an OracleDataReader
object

Slide 36
 DbDataReader Example
static void HasRows(SqlConnection connection)
{
using (connection)
{
SqlCommand command = new SqlCommand(
"SELECT CategoryID, CategoryName FROM Categories;",
connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();

if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
reader.GetString(1));
}
}
else
{
Console.WriteLine("No rows found.");
}
reader.Close();
}
}

Slide 37