ADO.

NET Introduction
It is a module of .Net Framework which is used to establish connection
between application and data sources. Data sources can be such as SQL
Server and XML. ADO.NET consists of classes that can be used to connect,
retrieve, insert and delete data.

All the ADO.NET classes are located into System.Data.dll and integrated
with XML classes located into System.Xml.dll.

ADO.NET has two main components that are used for accessing and
manipulating data are the .NET Framework data provider and the DataSet.

.NET Framework Data Providers

These are the components that are designed for data manipulation and fast
access to data. It provides various objects such as Connection, Command,
DataReader and DataAdapter that are used to perform database
operations. We will have a detailed discussion about Data Providers in new
topic.

The DataSet
It is used to access data independently from any data resource. DataSet
contains a collection of one or more DataTable objects of data. The following
diagram shows the relationship between .NET Framework data provider and
DataSet.
Fig: ADO.NET Architecture

Which one should we use DataReader or DataSet?

We should consider the following points to use DataSet.

o It caches data locally at our application, so we can manipulate it.

o It interacts with data dynamically such as binding to windows forms
control.
o It allows performing processing on data without an open connection. It
means it can work while connection is disconnected.

If we required some other functionality mentioned above, we can

use DataReader to improve performance of our application.

DataReader does not perform in disconnected mode. It requires DataReader

object to be connected.

ADO.NET Framework Data Providers

 Data provider is used to connect to the database, execute commands and
retrieve the record. It is lightweight component with better performance. It
also allows us to place the data into DataSet to use it further in our
application.

The .NET Framework provides the following data providers that we can use in
our application.

.NET Framework data Description

provider

.NET Framework Data It provides data access for Microsoft SQL Server. It requires
Provider for SQL Server the System.Data.SqlClient namespace.

.NET Framework Data It is used to connect with OLE DB. It requires

Provider for OLE DB the System.Data.OleDb namespace.

.NET Framework Data It is used to connect to data sources by using ODBC. It

Provider for ODBC requires the System.Data.Odbc namespace.

.NET Framework Data It is used for Oracle data sources. It uses

Provider for Oracle the System.Data.OracleClient namespace.

EntityClient Provider It provides data access for Entity Data Model applications. It
requires the System.Data.EntityClient namespace.

.NET Framework Data It provides data access for Microsoft SQL Server Compact
Provider for SQL Server 4.0. It requires the System.Data.SqlServerCe namespace.
Compact 4.0.

.NET Framework Data Providers Objects

Following are the core object of Data Providers.

Object Description

Connectio It is used to establish a connection to a specific data source.

n

Command It is used to execute queries to perform database operations.

DataReade It is used to read data from data source. The DbDataReader is a base clas
r for all DataReader objects.
 DataAdapt It populates a DataSet and resolves updates with the data source. The bas
er class for all DataAdapter objects is the DbDataAdapter class.

.NET Framework Data Provider for SQL Server

Data provider for SQL Server is a lightweight component. It provides better
performance because it directly access SQL Server without any middle
connectivity layer. In early versions, it interacts with ODBC layer before
connecting to the SQL Server that created performance issues.

The .NET Framework Data Provider for SQL Server classes is located in
the System.Data.SqlClient namespace. We can include this namespace in
our C# application by using the following syntax.

1. using System.Data.SqlClient;

This namespace contains the following important classes.

Class Description

SqlConnectio It is used to create SQL Server connection. This class cannot be inherited
n

SqlCommand It is used to execute database queries. This class cannot be inherited.

SqlDataAdapt It represents a set of data commands and a database connection that ar

er used to fill the DataSet. This class cannot be inherited.

SqlDataRead It is used to read rows from a SQL Server database. This class cannot b
er inherited.

SqlException This class is used to throw SQL exceptions. It throws an exception whe
an error is occurred. This class cannot be inherited.

.NET Framework Data Provider for Oracle

It is used to connect with Oracle database through Oracle client. The data
provider supports Oracle client software version 8.1.7 or a later version. This
data provider supports both local and distributed transactions.

Oracle Data Provider classes are located

into System.Data.OracleClient namespace. We must use
 both System.Data.OracleClient and System.data to connect our
application with the Oracle database.

1. using System.Data;
2. using System.Data.OracleClient;

Which .NET Framework Data Provider is better

Selection of data provider is depends on the design and data source of our
application. Choice of optimum .NET Framework data provider can improve
the performance, capability and integrity of our application. The following
table demonstrates advantages and disadvantages of data provider.

Data Provider Note

.NET Framework Data It is good for middle-tier applications, single-tie

Provider for SQL Server applications that use Microsoft SQL Server.

.NET Framework Data It is good for single-tier applications that use Microsof
Provider for OLE DB Access databases.

.NET Framework Data It is good for middle and single-tier applications that us
Provider for ODBC ODBC data sources.

.NET Framework Data It is good for middle and single-tier applications that us
Provider for Oracle Oracle data sources.

ADO.NET SQL Server Connection

To connect with SQL Server, we must have it installed in our system. We are
using Microsoft SQL Server Management Tool to connect with the SQL
Server. We can use this tool to handle database. Now, follow the following
steps to connect with SQL Server.

1. Open Microsoft SQL Server Management Tool

It will prompt for database connection. Provide the server name and
authentication.
 After successful connection, it displays the following window.

2. Creating Database

Now, create database by selecting database option then right click on

it. It pops up an option menu and provides couple of options.
Click on the New Database then it will ask for the database name.
Here, we have created a Student database.
Click on the Ok button then it will create a database that we can see in
the left window of the below screenshot.
3. Establish connection and create a table

After creating database, now, let's create a table by using the following
C# code. In this source code, we are using created student database
to connect.

In visual studio 2017, we created a .NET console application project

that contains the following C# code.

// Program.cs

1. using System;
2. using System.Data.SqlClient;
3. namespace AdoNetConsoleApplication
4. {
5. class Program
6. {
7. static void Main(string[] args)
8. {
9. new Program().CreateTable();
10. }
11. public void CreateTable()
12. {
13. SqlConnection con = null;
14. try
15. {
16. // Creating Connection
17. con = new SqlConnection("data source=.; database=s
tudent; integrated security=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("create table st
udent(id int not null,
20. name varchar(100), email varchar(50), join_date date)"
, con);
21. // Opening Connection
22. con.Open();
23. // Executing the SQL query
24. cm.ExecuteNonQuery();
25. // Displaying a message
26. Console.WriteLine("Table created Successfully");
27. }
28. catch (Exception e)
29. {
30. Console.WriteLine("OOPs, something went wrong."+e);

31. }
32. // Closing the connection
33. finally
34. {
35. con.Close();
36. }
37. }
38. }
39. }

Execute this code using Ctrl+F5. After executing, it displays a

message to the console as below.

We can see the created table in Microsoft SQL Server Management

Studio also. It shows the created table as shown below.

See, we have a table here. Initially, this table is empty so we need to

insert data into it.

4. Insert Data into the Table

// Program.cs

1. using System;
2. using System.Data.SqlClient;
3. namespace AdoNetConsoleApplication
4. {
5. class Program
6. {
7. static void Main(string[] args)
8. {
9. new Program().CreateTable();
10. }
11. public void CreateTable()
12. {
13. SqlConnection con = null;
14. try
15. {
16. // Creating Connection
17. con = new SqlConnection("data source=.; database=s
tudent; integrated security=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("insert into stud
ent
20. (id, name, email, join_date)values('101','Ronald Trump'
,'ronald@example.com','1/12/2017')", con);
21. // Opening Connection
22. con.Open();
23. // Executing the SQL query
24. cm.ExecuteNonQuery();
25. // Displaying a message
26. Console.WriteLine("Record Inserted Successfully");
27. }
28. catch (Exception e)
29. {
30. Console.WriteLine("OOPs, something went wrong."+e);

31. }
32. // Closing the connection
33. finally
34. {
35. con.Close();
36. }
37. }
38. }
39. }

Execute this code by using Ctrl+F5 and it will display the following
output.

5. Retrieve Record

Here, we will retrieve the inserted data. Look at the following C# code.

// Program.cs

1. using System;
2. using System.Data.SqlClient;
3. namespace AdoNetConsoleApplication
4. {
5. class Program
6. {
7. static void Main(string[] args)
8. {
9. new Program().CreateTable();
10. }
11. public void CreateTable()
12. {
13. SqlConnection con = null;
14. try
15. {
16. // Creating Connection
17. con = new SqlConnection("data source=.; database=s
tudent; integrated security=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("Select * from s
tudent", con);
20. // Opening Connection
21. con.Open();
22. // Executing the SQL query
23. SqlDataReader sdr = cm.ExecuteReader();
24. // Iterating Data
25. while (sdr.Read())
26. {
27. Console.WriteLine(sdr["id"] + " " + sdr["name"]+"
"+sdr["email"]); // Displaying Record
28. }
29. }
30. catch (Exception e)
31. {
32. Console.WriteLine("OOPs, something went wrong.\
n"+e);
33. }
34. // Closing the connection
35. finally
36. {
37. con.Close();
38. }
39. }
40. }
41. }

Execute this code by Ctrl+F5 and it will produce the following result.
This displays two records, one we inserted manually.

Output:
 6. Deleting Record

This time student table contains two records. The following C# code delete
one row from the table.

// Program.cs

1. using System;
2. using System.Data.SqlClient;
3. namespace AdoNetConsoleApplication
4. {
5. class Program
6. {
7. static void Main(string[] args)
8. {
9. new Program().CreateTable();
10. }
11. public void CreateTable()
12. {
13. SqlConnection con = null;
14. try
15. {
16. // Creating Connection
17. con = new SqlConnection("data source=.; database=studen
t; integrated security=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("delete from student
where id = '101'", con);
20. // Opening Connection
21. con.Open();
22. // Executing the SQL query
23. cm.ExecuteNonQuery();
24. Console.WriteLine("Record Deleted Successfully");
25. }
26. catch (Exception e)
27. {
28. Console.WriteLine("OOPs, something went wrong.\n"+e);
29. }
30. // Closing the connection
31. finally
32. {
33. con.Close();
34. }
35. }
36. }
37. }

Output:

Play Videox

It displays the following output.

 We can verify it by retrieving data back by using SqlDataReader.

ADO.NET SqlConnection Class

It is used to establish an open connection to the SQL Server database. It is a
sealed class so that cannot be inherited. SqlConnection class uses
SqlDataAdapter and SqlCommand classes together to increase performance
when connecting to a Microsoft SQL Server database.

Connection does not close explicitly even it goes out of scope. Therefore, you
must explicitly close the connection by calling Close() method.

SqlConnection Signature
1. public sealed class SqlConnection : System.Data.Common.DbConnection, ICl
oneable, IDisposable

SqlConnection Constructors

Constructors Description

SqlConnection() It is used to initializes a new instance of the SqlConnectio

class.

SqlConnection(String)0 It is used to initialize a new instance of the SqlConnectio

class and takes connection string as an argument.

SqlConnection(String, It is used to initialize a new instance of the SqlConnectio

SqlCredential) class that takes two parameters. First is connection string an
second is sql credentials.

SqlConnection Methods

Method Description

BeginTransaction() It is used to start a database transaction.

ChangeDatabase(String) It is used to change the current database for an ope

SqlConnection.

ChangePassword(String, It changes the SQL Server password for the user indicated i
 String) the connection string.

Close() It is used to close the connection to the database.

CreateCommand() It enlists in the specified transaction as a distribute

transaction.

GetSchema() It returns schema information for the data source of thi

SqlConnection.

Open() It is used to open a database connection.

ResetStatistics() It resets all values if statistics gathering is enabled.

SqlConnection Example
Now, let's create an example that establishes a connection to the SQL
Server. We have created a Student database and will use it to connect.
Look at the following C# code.

1. using (SqlConnection connection = new SqlConnection(connectionString))

2. {
3. connection.Open();
4. }

Using block is used to close the connection automatically. We don't need to

call close () method explicitly, using block do this for ours implicitly when
the code exits the block.

Play Videox
 // Program.cs

1. using System;
2. using System.Data.SqlClient;
3. namespace AdoNetConsoleApplication
4. {
5. class Program
6. {
7. static void Main(string[] args)
8. {
9. new Program().Connecting();
10. }
11. public void Connecting()
12. {
13. using (
14. // Creating Connection
15. SqlConnection con = new SqlConnection("data source=.;
database=student; integrated security=SSPI")
16. )
17. {
18. con.Open();
19. Console.WriteLine("Connection Established Successfully");
20. }
21. }
22. }
23. }

Output:

What, if we don't use using block.

 If we don't use using block to create connection, we have to close connection
explicitly. In the following example, we are using try-block instead of using
block.

// Program.cs

1. using System;
2. using System.Data.SqlClient;
3. namespace AdoNetConsoleApplication
4. {
5. class Program
6. {
7. static void Main(string[] args)
8. {
9. new Program().Connecting();
10. }
11. public void Connecting()
12. {
13. SqlConnection con = null;
14. try
15. {
16. // Creating Connection
17. con = new SqlConnection("data source=.; database=studen
t; integrated security=SSPI");
18. con.Open();
19. Console.WriteLine("Connection Established Successfully");
20. }
21. catch (Exception e)
22. {
23. Console.WriteLine("OOPs, something went wrong.\n"+e);
24. }
25. finally
26. { // Closing the connection
27. con.Close();
28. }
29. }
30. }
31. }

Output: