03-Jan-23
ADO
.NET
Contents
Introduction
ADO
.NET
ADO.NET is a set of computer software components that
programmers can use to access data and data services.
It is a part of the base class library that is included with the
Microsoft .NET Framework.
It is commonly used by programmers to access and modify data
stored in relational database systems, though it can also access
data in non-relational sources.
ADO.NET is sometimes considered an evolution of ActiveX Data
Objects (ADO) technology, but was changed so extensively that it
can be considered an entirely new product.
ADO.NET is an integral part of the .NET Compact Framework,
providing access to relational data, XML documents, and
application data. ADO.NET supports a variety of development
needs. You can create database-client applications and middle-tier
business objects used by applications, tools, languages or Internet
browsers.
1
03-Jan-23
ADO
.NET
ADO
.NET
System.data Core namespace, defines types that
represent data
System.Data.Common Types shared between managed providers
System.Data.OleDb Types that allow connection to OLE DB
compliant data sources
System.Data.SqlClient Types that are optimized to connect to
Microsoft® SQL Server
System.Data.SqlTypes Native data types in Microsoft® SQL
Server
2
03-Jan-23
ADO
.NET
The SqlConnection Object
To interact with a database, you must have a connection to it. The
connection helps identify the database server, the database name, user
name, password, and other parameters that are required for connecting to
the data base. A connection object is used by command objects so they will
know which database to execute the command on.
The SqlClient .NET Data Provider namespace:
using System.Data.SqlClient;
Steps involved in using connection object:
1. Declare a connection string pointing to a database
2. Create an SqlConnection object
3. Pass the connection string to this SqlConnection object.
SqlConnection con = new SqlConnection(conString);
4. Open connection when needed and close when finished
con.Open();
con.Close();
The SqlCommand Object ADO
.NET
The process of interacting with a database means that you must specify the
actions you want to occur. This is done with a command object. You use a
command object to send SQL statements to the database. A command object
uses a connection object to figure out which database to communicate
with. You can use a command object alone, to execute a command directly, or
assign a reference to a command object to an SqlDataAdapter, which holds a
set of commands that work on a group of data as described below.
1. Creating a SqlCommand Object
SqlCommand cmd = new SqlCommand("select CategoryName from
Categories", conn);
2. Querying Data
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from
Categories", conn);
// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();
3
03-Jan-23
3. Inserting Data ADO
.NET
// prepare command string
string insertString = @”insert into Categories(CategoryName, Description) values
(‘value1', ‘value2’);
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
4. Updating Data
// prepare command string
string updateString = @” update Categories set CategoryName = 'Other’ where
CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
ADO
5. Deleting Data .NET
// prepare command string
string deleteString = @”delete from Categories where
CategoryName = 'Other'";
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();
// 2. Set the CommandText property
cmd.CommandText = deleteString;
// 3. Set the Connection property
cmd.Connection = conn;
// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
4
03-Jan-23
6. Getting Single values ADO
.NET
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("select count(*)
from Categories", conn);
// 2. Call ExecuteNonQuery to send command
int count = (int)cmd.ExecuteScalar();
ADO
The SqlDataReader Object .NET
Many data operations require that you only get a stream
of data for reading. The data reader object allows you
to obtain the results of a SELECT statement from a
command object. For performance reasons, the data
returned from a data reader is a fast forward-only
stream of data. This means that you can only pull the
data from the stream in a sequential manner. This is
good for speed, but if you need to manipulate data, then
a DataSet is a better object to work with.
Steps involved:
1. Creating a SqlDataReader Object
SqlDataReader rdr = cmd.ExecuteReader();
5
03-Jan-23
2. Reading Data ADO
.NET
SqlDataReader returns data via a sequential
stream
The typical method of reading from the data stream
returned by the SqlDataReader is to iterate through
each row with a while loop.
while (rdr.Read())
{
// get the results of each column
string contact = (string)rdr["ContactName"];
string company = (string)rdr["CompanyName"];
// print out the results
Console.Write("{0,-25}", contact);
Console.Write("{0,-25}", company);
Console.WriteLine();
}
ADO
The SqlDataAdapter Object .NET
The data adapter makes it easy for you to accomplish these
things by helping to manage data in a disconnected mode.
The data adapter fills a DataSet object when reading the data
and writes in a single batch when persisting changes back to
the database.
A data adapter contains a reference to the connection object
and opens and closes the connection automatically when
reading from or writing to the database.
Additionally, the data adapter contains command object
references for SELECT, INSERT, UPDATE, and DELETE
operations on the data.
You will have a data adapter defined for each table in a
DataSet and it will take care of all communication with the
database for you.
All you need to do is tell the data adapter when to load from or
write to the database.
6
03-Jan-23
The DataAdapter provides four properties that allow us to ADO
.NET
control how updates are made to the server:
•SelectCommand
•UpdateCommand
•InsertCommand
•DeleteCommand
The four properties are set to Command objects that are used
when data is manipulated.
The DataAdapter includes three main methods:
•Fill (populates a DataSet with data).
•FillSchema (queries the database for schema information that
is necessary to update).
•Update (to change the database, DataAdapter calls
the DeleteCommand, the InsertCommand and
the UpdateCommand properties).
Note:More about SqlDataAdapter and creation dealt along with
SqlDataSet object.
The DataSet Object ADO
.NET
DataSet objects are in-memory representations of data. They
contain multiple Datatable objects, which contain columns and
rows, just like normal database tables. You can even define
relations between tables to create parent-child
relationships. The DataSet is specifically designed to help
manage data in memory and to support disconnected
operations on data, when such a scenario make sense. The
DataSet is an object that is used by all of the Data Providers,
which is why it does not have a Data Provider specific prefix.
1. Creating a DataSet Object
DataSet ds = new DataSet();
2. Creating A SqlDataAdapter
The SqlDataAdapter holds the SQL commands and connection
object for reading and writing data.
SqlDataAdapter da = new SqlDataAdapter("select CustomerID,
CompanyName from Customers", conn);
3. Filling the DataSet
da.Fill(ds, "Customers");
7
03-Jan-23
4. Using the DataSet ADO
.NET
A DataSet will bind with both ASP.NET and Windows forms
DataGrids. Here's an example that assigns the DataSet to a
Windows forms DataGrid:
dg.DataSource = ds;
dg.DataMember = "Customers";
5. Updating Changes
da.Update(ds, "Customers");
ADO
.NET
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "Data Source=(local);Initial Catalog=Northwind;" + "Integrated
Security=true";
SqlConnection con=new SqlConnection(connectionString) ;
String query= “select * from student”;
Con.Open();
SqlCommand cmd=new SqlCommand(query,con);
SqlDataReader dr=cmd.ExecuteReader();
while(dr.Read())
{
Console.Writeline(dr[“name”]);
}
dr.Dispose();
cmd.Dispose();
con.Close();
} }
8
03-Jan-23
ADO
Enter the following code: .NET
private void Form1_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("Data
Source=localhost;Database=Northwind;Integrated
Security=SSPI"); SqlCommand command = new
SqlCommand("GetProducts", conn); SqlDataAdapter adapter
= new SqlDataAdapter(command); DataSet ds = new
DataSet();
adapter.Fill(ds, "Products");
this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = "Products";
}
