Database Concept
Course Code: CSC 2210 Course Title: Object Oriented Programming 2
Dept. of Computer Science
Faculty of Science and Technology
Lecturer No: 11 Week No: 12 Semester: Spring 2023-2024
Lecturer: Taslimur Rahman; Taslimur.Rahman@aiub.edu
Lecture Outline
• Introduction to ADO.NET.
• Introduction to MS SQL Server.
• Database CRUD operation.
• Familiarize with Desktop based Management System.
ADO.NET
Introduction to ADO.NET
ADO.NET is a set of classes (a framework) to interact with data sources such as
databases and XML files.
ADO is the acronym for ActiveX Data Objects.
It has classes and methods to retrieve and manipulate data.
ADO.NET provides a bridge between the front end controls and the back end database.
ADO.NET provides a rich set of components for creating distributed, data-sharing
applications.
The following are a few of the .NET applications that use ADO.NET to connect to a
database, execute commands and retrieve data from the database.
ASP.NET Web Applications
Console Applications
Windows Applications.
ADO.NET Basic Architecture
There are the following
two types of
connection
architectures:
1.Connected
architecture: the
application remains
connected with the
database throughout
the processing.
2.Disconnected
architecture: the
application
automatically
connects/disconnects
during the processing.
The application uses
temporary data on the
application side called
DataSet Class
The dataset represents a subset of the database. It does not have a continuous
connection to the database. To update the database a reconnection is required. The
DataSet contains DataTable objects and DataRelation objects. The DataRelation objects
represent the relationship between two tables.
Some important properties of the DataSet class: Namepace, prefix, Relations, Tables etc.
Some important methods of the DataSet class: GetDataSetSchema, GetType, Copy, Clone,
Equals(Object) etc.
For More details please follow the link:
https://www.tutorialspoint.com/asp.net/asp.net_ado_net.htm
Important Classes
We can also observe various classes our work. They are:
Connection Class
Command Class
DataReader Class
DataAdaptor Class
DataSet Class
We can also observe that there are various types of applications that use ADO.NET to
connect to several databases such as: SQL Server, Oracle, OleDb, ODBC, XML files and so
on.
We will discuss our situation based on SQL Sever.
Connection Class
In ADO.NET, we use these connection classes to connect to the database. These
connection classes also manage transactions and connection pooling.
Command Class
The Command class provides methods for storing and executing SQL statements and
Stored Procedures. The following are the various commands that are executed by the
Command Class.
ExecuteReader: Returns data to the client as rows.
ExecuteNonQuery: Executes a command that changes the data in the database, such
as an update, delete, or insert statement, or a Stored Procedure that contains one or
more of these statements.
ExecuteScalar: This method only returns a single value. This kind of query returns a
count of rows or a calculated value.
ExecuteXMLReader: (SqlClient classes only) Obtains data from an SQL Server 2000
database using an XML stream. Returns an XML Reader object.
DataReader Class
The DataReader is used to retrieve data. It is used in conjunction with the Command
class to execute an SQL Select statement and then access the returned rows.
DataAdapter & DataSet Class
The DataAdapter is used to connect DataSets to databases. The DataAdapter is most
useful when using data-bound controls in Windows Forms, but it can also be used to
provide an easy way to manage the connection between your application and the
underlying database tables, views and Stored Procedures.
The DataSet is the heart of ADO.NET. The DataSet is essentially a collection of
DataTable objects. In turn each object contains a collection of DataColumn and
DataRow objects. The DataSet also contains a Relations collection that can be used to
define relations among Data Table Objects.
Example
string sql = @"select * from person;";
SqlConnection sqlcon = new SqlConnection(@"");//Connection String
sqlcon.Open();
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
SqlDataAdapter sda = new SqlDataAdapter(sqlcom);
DataSet ds = new DataSet();
sda.Fill(ds);
sqlcon.Close();
Search &
CRUD
Example (Biniding GridView)
private void PopulateGridView(string sql = "select * from table;")
{
var dt = this.Da.ExecuteQuery(sql);
this.dgvMain.AutoGenerateColumns = false;
this.dgvMain.DataSource = dt;
}
Example (Insert & Update)
//Insert
string query = @"insert into table
values('" + this.txtId.Text + "','" +
this.txtTitle.Text + "'," + this.txtVal1.Text + "," + this.txtVal2.Text +
",'" + this.dtpVal3.Text + "','" + this.cmbVal4.Text + "');";
int count = this.Da.ExecuteUpdateQuery(query);
//Update
string query = @"update table
set title = '" + this.txtTitle.Text + @"',
val1 = " + this.txtVal1.Text + @",
val2 = " + this.txtVal2.Text + @",
val3 = '" + this.dtpVal3.Text + @"',
val4 = '" + this.cmbVal4.Text + @"'
where id = '" + this.txtId.Text + "';";
int count = this.Da.ExecuteUpdateQuery(query);
Example (Delete)
string query = "delete from table where id = '" + id + "';";
int count = this.Da.ExecuteUpdateQuery(query);
Thank You
Books
• C# 4.0 The Complete Reference; Herbert Schildt; McGraw-Hill Osborne Media; 2010
• Head First C# by Andrew Stellman
• Fundamentals of Computer Programming with CSharp – Nakov v2013
References
• https://www.codeproject.com/Articles/823854/How-to-Connect-SQL-Database-to-
your-Csharp-Program
• https://www.guru99.com/c-sharp-access-database.html
• https://www.udemy.com/course/csharp-database-programming/
• https://codesamplez.com/database/desktop-form
• https://docs.microsoft.com/en-us/visualstudio/data-tools/create-a-simple-data-
application-by-using-adonet?view=vs-2019
• https://docs.microsoft.com/en-us/visualstudio/data-tools/create-a-sql-database-
by-using-a-designer?view=vs-2019
• https://www.c-sharpcorner.com/UploadFile/18fc30/understanding-the-basics-of-
ado-net/
• https://www.tutorialspoint.com/asp.net/asp.net_ado_net.htm
• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/
• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-
examples
