CHAPTER -5

Windows Programming (VB)

Cosc 4045

Database Programming
Department of Computer Science

Done by:Ashenafi W(Msc)

11/21/2025 DONE BY: A.W 1

11/21/2025 ANDARGACHEW A. 2
INTRODUCTION TO [Link]
C# and .Net can work with a majority of databases, the
most common being Microsoft SQL Server and Oracle.
But with every database, the logic behind working with all of
them is mostly the same.
In the Microsoft .NET Framework, access to a wide
variety of data sources is enabled through a group of
classes collectively named Microsoft [Link].

11/21/2025 ANDARGACHEW A. 3
[Link] (ActiveX Data Objects) is a data access
technology from Microsoft .Net Framework , which
provides communication between relational and non-
relational systems through a common set of components .
[Link] consist of a set of Objects that expose data access
services to the .NET environment.
[Link] is designed to be easy to use, and Visual
Studio provides several wizards and other features that
you can use to generate [Link] data access code.
11/21/2025 ANDARGACHEW A. 4
Database Access
Three steps:
1. Open connection to database
2. Execute SQL to retrieve records / update DB
3. Close connection

11/21/2025 ANDARGACHEW A. 5
DATABASE NAMESPACE AND CLASSES
The namespaces in the following table expose the classes and
interfaces used in .NET data access
Namespace Brief Description
[Link] All generic data access classes
[Link] Classes shared (or overridden) by individual data providers
[Link] Entity Framework classes
[Link] LINQ to SQL provider classes
[Link] ODBC provider classes
[Link] OLE DB provider classes
[Link] New base classes and connection factory classes
[Link] New generic interfaces and classes for SQL Server data access
[Link] SQL Server provider classes
11/21/2025 [Link] SQL Server data types 6
[Link]
This namespace of .NET Framework contains all of the classes
required to connect to the databases, read/write data to the
databases.

11/21/2025 7
SqlConnection class methods

11/21/2025 8
SqlConnection class properties

11/21/2025 9
CONNECTING TO A DATABASE
Connection to a database requires a connection string.
This string has the information about the server you're going to
connect, the database you will require and the credentials that
you can use to connect.
Each database has its own properties, its own server, name and
type of login information, using which you can connect to the
database to read/write the data from it.
The connection string may include attributes such as the
name of the driver, server and database, as well as
security information such as user name and password.
11/21/2025 10
You configure a connection object using a connection
string.
A connection string is a set of semicolon-separated name/value
pairs.
You can supply a connection string either as a
constructor argument or by setting a connection object’s
ConnectionString property before opening the connection.
Each connection class implementation requires that you
provide different information in the connection string.
11/21/2025 11
Connected Environment (Scenario)
1. Open connection
2. Execute command
3. Process rows in reader
4. Close reader
5. Close connection

11/21/2025 ANDARGACHEW A. 12
Working with data directly via open connection
Advantages
Simple security realization
Work with real data
Simple organization of distributed work
Drawbacks
Continual connection
Not available via Internet

11/21/2025 ANDARGACHEW A. 13
Disconnected Environment (Scenario)
1. Open connection
2. Fill the DataSet
3. Close connection
4. Process the DataSet
5. Open connection
6. Update the data source
7. Close connection

11/21/2025 ANDARGACHEW A. 14
Storage of data local copy from repository
Possibility to update the main data source
Advantages
Economy of server resources
Does not require continual connection
Drawbacks
Demands conflict resolution while data update
Data is not always up to date

11/21/2025 ANDARGACHEW A. 15
ARCHITECTURE OF [Link]

11/21/2025 16
The two key components of [Link] are Data
Providers and DataSet .
The .Net Framework includes mainly three Data Providers for
[Link]. They are the Microsoft SQL Server Data
Provider , OLEDB Data Provider and ODBC Data Provider +
Oracle .NET Data Provider
SQL Server uses the SqlConnection object ,
OLEDB (Object Linking and Embedding Database) uses the
OleDbConnection Object and
ODBC (Open Database Connectivity) uses OdbcConnection Object
respectively.

11/21/2025 ANDARGACHEW A. 17
11/21/2025 ANDARGACHEW A. 18
DataSet provides a disconnected representation of result
sets from the Data Source, and it is completely
independent from the Data Source.
DataSet provides much greater flexibility when dealing with
related Result Sets.
DataSet consists of a collection of DataTable objects that you can
relate to each other with DataRelation objects.
The DataTable contains a collection of DataRow and DataColumn Object
which contains Data.
The DataAdapter Object provides a bridge between the DataSet and the
Data Source.

11/21/2025 ANDARGACHEW A. 19
.NET Data Provider

Connection

Command SelectCommand

DataReader InsertCommand
DeleteCommand
DataAdapter
UpdateCommand

DataSet DataColumn
DataTable DataRow
DataRelation DataConstraint
 DATA PROVIDER COMPONENTS
Each .NET data provider consists of four main
components:
Connection – used to connect to the data source
 Command – used to execute a command against the data
source and retrieve a DataReader or DataSet, or to execute an
INSERT, UPDATE, or DELETE command against the data source
DataReader – a forward-only, read-only connected resultset
DataAdapter – used to populate a DataSet with data from the
data source, and to update the data source
MANAGING DATABASE CONNECTION
(CREATING AND CLOSING)
Creating a connection
Creating connection object using constructor and arguments
Example: (using integrated Windows security)

ConnectionString Parameters
Provider
Data Source
Initial Catalog
Integrated Security
UserID/Password

11/21/2025 ANDARGACHEW A. 22
Closing connection
When you’re finished with a connection, you should always call its
Close method to free the underlying database connection and
system resources.
Eg: [Link]()
Alternatively, using statement makes very clean and efficient
way of using connection objects in your code
When using statement is used, the method Close() of connection object is
automatically called to close the opened connection and to release any
resource.

11/21/2025 23
EXAMPLE

11/21/2025 24
EXECUTE A SQL COMMAND OR STORED
PROCEDURE
SqlCommand object
A SqlCommand object allows you to specify what type of
interaction you want to perform with a database.
For example, you can do select, insert, modify, and delete
commands on rows of data in a database table.
Properties Methods
Connection ExecuteNonQuery
CommandType ExecuteReader
CommandText ExecuteScalar
Parameters
11/21/2025 ANDARGACHEW A. 25
[Link] Property
Gets or sets the SQL statement, table name or stored procedure to
execute at the data source.
Eg:
SqlCommand command = new SqlCommand(); [Link] = "SELECT
* FROM Categories ORDER BY CategoryID";
[Link] Property
Gets or sets a value indicating how the CommandText property is to
be interpreted.
Possible values are:
 [Link]
 [Link]

11/21/2025 ANDARGACHEW A. 26
[Link] Property
The time in seconds to wait for the command to execute. The
default is 30 seconds.
Gets or sets the wait time before terminating the attempt to
execute a command and generating an error.

11/21/2025 ANDARGACHEW A. 27
CREATING SQLCOMMAND OBJECT
Constructors
1. SqlCommand()
2. SqlCommand(cmdText)
3. SqlCommand(cmdText, connection)
Example
SqlCommand command = new SqlCommand();
[Link] = "SELECT * FROM Categories";
[Link] = 15;
[Link] = [Link];
Remarks
oWhen the CommandType property is set to StoredProcedure, the CommandTextproperty
should be set to the name of the stored procedure.

11/21/2025 ANDARGACHEW A. 28
HOW TO ADD PARAMETERS TO COMMANDS?
Use Parameters property of Command Object
[Link]("@i", [Link]);
[Link]["@i"].Value = idno;
[Link]("@f", [Link]);
[Link]["@f"].Value =fn;
Or
[Link](“i", idno);
[Link]("f", fn);

11/21/2025 ANDARGACHEW A. 29
SqlCommand cmd = new SqlCommand( "select * from Customers
where city = @City", conn);
//define parameters used in command object
SqlParameter param = new SqlParameter(); [Link]
= "@City";
[Link] = inputCity;
// add new parameter to command object [Link](param);
Or
[Link](new SqlParameter("@i", idno));
// [Link](new SqlParameter("@f", fn));

11/21/2025 ANDARGACHEW A. 30
EXECUTING COMMANDS
The SqlCommand class provides the following methods
for executing commands against the SQL Server database:
ExecuteReader(): Executes the command and returns a typed
IDataReader
ExecuteNonQuery(): Executes the command but does not return
any output
ExecuteScalar(): Executes the command and returns the value from the first
column of the first row of any result set

11/21/2025 ANDARGACHEW A. 31
ExecuteReader() method
Executes commands that return rows.
Sends the CommandText to the Connection and builds
a SqlDataReader.
ExecuteReader() - Returns DataReader
Example
SqlCommand command = new SqlCommand(queryString, connection);
SqlDataReader reader = [Link]();

11/21/2025 ANDARGACHEW A. 32
Retrieving Records with the SqlDataReader Class
DataReader object: used to retrieve read-only, forward-only
data from a data source
Forward-only: the program can only move forward sequentially
through the records in the returned data from the first to the last
Use a DataReader object when you want to read data but not
add, delete, or modify records
SqlDataReader class: used to retrieve data from SQL Server

11/21/2025 ANDARGACHEW A. 33
 ExecuteReader() method of the SqlCommand class: creates a
SqlDataReader object
Must assign the SqlDataReader object to a variable
Read() method of the SqlDataReader class: advances the SqlDataReader
object to the next record
Cursor: your position within the recordset
Initially placed before the first row in the recordset
First use of the Read() method places the cursor in the first row of the recordset

11/21/2025 34
 Use the Read() method to determine if a next record is available
Returns true if there is another row in the recordset
Field names in a database table are assigned as variables in a
SqlDataReader object collection
Content of each variable changes when the cursor position moves to a new
row
Use the Close() method of the SqlDataReader class to close it
when you are finished working with it
SqlDataReader has exclusive access to the connection object
You cannot access any other commands until the SqlDataReader object is
closed.

11/21/2025 ANDARGACHEW A. 35
EXAMPLE

11/21/2025 36
DISPLAY SELECT QUERY RESULT USING
DATADGRIDVIEW CONTROL

11/21/2025 37
Executing action queries
Use action queries when you need to modify a DB
Updates
Inserts
Deletes

Execute action queries via ExecuteNonQuery method

11/21/2025 38
ExecuteNonQuery
To execute operations where database is changed
Example: insert, update, delete, create database, create table, etc.
Insert, update, delete: Returns number of rows affected by the
operation and returns -1 for others
Example
SqlCommand cmd = new SqlCommand
("insert into titles (title_id, title, type, pubdate)"+
"values ('CS150','C++ Programming'," +
" 'computer science', 'May 2006'), conn);
[Link]();

11/21/2025 39
Insert Query

11/21/2025 40
Delete Query

11/21/2025 41
Update Query

11/21/2025 42
ExecuteScalar
Returns the 1st row of the 1st column in the result
Used for commands such as: count, avg, min, max, sum

11/21/2025 43
Example

11/21/2025 44
Displaying column names of returned record set
Using FieldCount property and GetName() method

11/21/2025 45
 Questions?
11/21/2025 ANDARGACHEW A. 46
 End of the Course!!!

11/21/2025 47
 Thank You
11/21/2025 ANDARGACHEW A. 48