.

Net Technologies

LECTURE NOTES

Unit 2-ADO.Net

Intro to ADO.Net,

ADO.Net v/s ADO,

Creating Connection Strings,

Data Binding,

Working with data adapter,

data reader and data set

Dr. Hitesh Kumar Sharma Page 1

.Net Technologies

ADO.NET

(ACTIVEX DATA OBJECTS)

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
DataAdaptor 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.

Dr. Hitesh Kumar Sharma Page 2

.Net Technologies

Fig: ADO.NET Architecture

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.

Dr. Hitesh Kumar Sharma Page 3

.Net Technologies
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 V/S ADO.NET

ADO is based on COM Technology and it used OLEDB data provider for accessing data . It has
a limited number of data types which are defined by the COM standard. ADO.NET basically
designed by .NET Framework for smooth interaction of application and database, it support
large and rich datatypes.

Classic ADO used OLE DB data provider to access data and is COM based, while ADO.net uses
XML as the format for transmitting data to and from your database and applications. It is
compatible with any component on any platform that understands XML.

ADO works with connected data architecture. That means, when you access the data from
data source, such as viewing or updating data, ADO recordset is keeping connection with the
data source. This is barring, of course, you have to develop special routines to pull all your
data into temporary tables.

ADO.NET uses data in a disconnected manner. When you access data, ADO.NET makes a copy
of the data using XML. ADO.NET only holds the connection open long enough to either pull
down the data or to make any requested updates and immediatly close the connection after
operation. This makes ADO.NET efficient to use in networking environment.

Classic ADO has one main object that is used to reference data, called the Recordset object,
it acts like a single table or query result. If an ADO recordset is to contain data from multiple

Dr. Hitesh Kumar Sharma Page 4

.Net Technologies
database tables, it should use a JOIN query, which assembles the data from the different
database tables into a single result table.

In ADO.NET, you have various objects that allow you to access data in various ways. ADO.net
Dataset can contain multiple tables from various data sources. The tables within a dataset
are called data tables. The DataSet object will actually allow you to store the relational model
of your database. If a dataset contains data from multiple database tables, it will typically
contain multiple DataTable objects. That is, each DataTable object typically corresponds to a
single database table or view.

ADO allows you to create client side cursors only, whereas ADO.NET gives you the choice of
either using client side or server side cursors. In ADO.NET, classes actually handle the work
of cursors. This allows a choice for programmers. In networking development environment ,
this choice is crucial in creating efficient applications.

In ADO.Net we can send multiple transactions using a single connection instance, whereas in
ADO, we cannot send multiple transactions using a single connection instance.

In ADO, it is sometime create problems because firewall prohibits many types of request,
while in ADO.net there is no such problem because XML is completely firewall-proof.

ADO ADO.NET

Disconnected models are

Business Model Connection-oriented Models used mostly
used:Message-like Models.

Disconnected Provided by Data Adapter and

Provided by Record set
Access Data set

XML Support Limited Robust Support

Client application needs to be connected

Client disconnected as soon as
Connection always to data-server while working on
the data is processed. DataSet
Model the data, unless using client-side cursors
is disconnected at all times.
or a disconnected Record set

ADO objects communicate in binary ADO.NET uses XML for passing

Data Passing
mode. the data.

Provides well-defined, factored

Includes implicit behaviors that may not
Control of data components with predictable
always be required in an application and
access behaviors behavior, performance, and
that may therefore limit performance.
semantics.

Dr. Hitesh Kumar Sharma Page 5

.Net Technologies

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 Provider It provides data access for Microsoft SQL Server. It
for SQL Server requires the System.Data.SqlClient namespace.

.NET Framework Data Provider It is used to connect with OLE DB. It requires
for OLE DB the System.Data.OleDb namespace.

.NET Framework Data Provider It is used to connect to data sources by using ODBC. It
for ODBC requires the System.Data.Odbc namespace.

.NET Framework Data Provider It is used for Oracle data sources. It uses
for Oracle the System.Data.OracleClientnamespace.

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

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

Dr. Hitesh Kumar Sharma Page 6

 .Net Technologies

.NET FRAMEWORK DATA PROVIDERS OBJECTS

Following are the core object of Data Providers.

Object Description

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

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

DataReader It is used to read data from data source. The DbDataReader is a base
class for all DataReader objects.

DataAdapter It populates a DataSet and resolves updates with the data source. The
base 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

SqlConnection It is used to create SQL Server connection. This class cannot be

inherited.

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

inherited.

SqlDataAdapter It represents a set of data commands and a database connection

that are used to fill the DataSet. This class cannot be inherited.

Dr. Hitesh Kumar Sharma Page 7

 .Net Technologies

SqlDataReader It is used to read rows from a SQL Server database. This class
cannot be inherited.

SqlException This class is used to throw SQL exceptions. It throws an exception

when 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-tier

Provider for SQL Server applications that use Microsoft SQL Server.

.NET Framework Data It is good for single-tier applications that use

Provider for OLE DB Microsoft Access databases.

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

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

Dr. Hitesh Kumar Sharma Page 8

.Net Technologies

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

Dr. Hitesh Kumar Sharma Page 9

.Net Technologies
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.

Dr. Hitesh Kumar Sharma Page 10

.Net Technologies

Click on the Ok button then it will create a database that we can see in the left window
of the below screenshot.

Dr. Hitesh Kumar Sharma Page 11

.Net Technologies

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. }

Dr. Hitesh Kumar Sharma Page 12

.Net Technologies

11. public void CreateTable()

12. {
13. SqlConnection con = null;
14. try
15. {
16. // Creating Connection
17. con = new SqlConnection("data source=.; database=student; integrated
security=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("create table student(id int not nu
ll,
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.

Dr. Hitesh Kumar Sharma Page 13

.Net Technologies

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();

Dr. Hitesh Kumar Sharma Page 14

.Net Technologies

10. }
11. public void CreateTable()
12. {
13. SqlConnection con = null;
14. try
15. {
16. // Creating Connection
17. con = new SqlConnection("data source=.; database=student; integrated
security=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("insert into student
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.

Dr. Hitesh Kumar Sharma Page 15

.Net Technologies

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=student; integrated
security=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("Select * from student", 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. {

Dr. Hitesh Kumar Sharma Page 16

 .Net Technologies

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. {

Dr. Hitesh Kumar Sharma Page 17

 .Net Technologies

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=student; integrated securit
y=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("delete from student where id = '101'", c
on);
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:

It displays the following output.

Dr. Hitesh Kumar Sharma Page 18

.Net Technologies

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

Dr. Hitesh Kumar Sharma Page 19

 .Net Technologies

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, ICloneable, IDispo

sable

SQLCONNECTION CONSTRUCTORS

Constructors Description

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

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

string as an argument.

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

SqlCredential) parameters. First is connection string and 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 open SqlConnec

ChangePassword(String, String) It changes the SQL Server password for the user indicated in the

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

Dr. Hitesh Kumar Sharma Page 20

 .Net Technologies

CreateCommand() It enlists in the specified transaction as a distributed transaction.

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

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.

// 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")

Dr. Hitesh Kumar Sharma Page 21

 .Net Technologies

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

Dr. Hitesh Kumar Sharma Page 22

 .Net Technologies

17. con = new SqlConnection("data source=.; database=student; integrated securit

y=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:

ADO.NET SQLCOMMAND CLASS

This class is used to store and execute SQL statement for SQL Server database. It is a sealed
class so that cannot be inherited.

SQLCOMMAND SIGNATURE

1. public sealed class SqlCommand : System.Data.Common.DbCommand, ICloneable, IDisposa

ble

CONSTRUCTORS
This class provides the following constructors.

Dr. Hitesh Kumar Sharma Page 23

.Net Technologies

Constructor Description

SqlCommand() It is used to initialize a new instance

class.

SqlCommand(String) It is used to initialize a new instance

class with a string parameter.

SqlCommand(String, SqlConnection) It is used to initialize a new instance

class. It takes two parameters, first
second is connection string.

SqlCommand(String, SqlConnection, SqlTransaction) It is used to initialize a new instance

class. It takes three parameters que
transaction string respectively.

SqlCommand(String, SqlConnection, SqlTransaction, It Initializes a new instance of the Sql

SqlCommandColumnEncryptionSetting) specified command text, connection
encryption setting.

METHODS

Method Description

BeginExecuteNonQuery() It is used to Initiate the asynchronous execution of the SQL stateme

SqlCommand.

Cancel() It tries to cancel the execution of a SqlCommand.

Clone() It creates a new SqlCommand object that is a copy of the current instan

CreateParameter() It creates a new instance of a SqlParameter object.

ExecuteReader() It is used to send the CommandText to the Connection and builds a SqlD

ExecuteXmlReader() It is used to send the CommandText to the Connection and builds an Xm

ExecuteScalar() It executes the query and returns the first column of the first row in the
columns or rows are ignored.

Dr. Hitesh Kumar Sharma Page 24

 .Net Technologies

Prepare() It is used to create a prepared version of the command by using the inst

ResetCommandTimeout() It is used to reset the CommandTimeout property to its default value.

EXAMPLE
In this example, we are creating a SqlCommand instance and executing a SQL statement.

// 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=student; integrated securit
y=SSPI");
18. // writing sql query
19. SqlCommand cm = new SqlCommand("select * from student", con);
20. // Opening Connection
21. con.Open();
22. // Executing the SQL query
23. SqlDataReader sdr = cm.ExecuteReader();
24. while (sdr.Read())
25. {
26. Console.WriteLine(sdr["name"]+" "+ sdr["email"]);

Dr. Hitesh Kumar Sharma Page 25

 .Net Technologies

27. }
28. }
29. catch (Exception e)
30. {
31. Console.WriteLine("OOPs, something went wrong." + e);
32. }
33. // Closing the connection
34. finally
35. {
36. con.Close();
37. }
38. }
39. }
40. }

Output:

Execute this program by combination of Ctrl+F5 and it will produce the following output.

It prints name and email of the student.

ADO.NET SQLDATAREADER CLASS

This class is used to read data from SQL Server database. It reads data in forward-only stream
of rows from a SQL Server database. it is sealed class so that cannot be inherited. It inherits
DbDataReader class and implements IDisposable interface.

SQLDATAREADER SIGNATURE

1. public class SqlDataReader : System.Data.Common.DbDataReader, IDisposable

SQLDATAREADER PROPERTIES

Property Description

Dr. Hitesh Kumar Sharma Page 26

.Net Technologies

Connection It is used to get the SqlConnection associated with the

SqlDataReader.

Depth It is used to get a value that indicates the depth of nesting for the
current row.

FieldCount It is used to get the number of columns in the current row.

HasRows It is used to get a value that indicates whether the SqlDataReader

contains one or more rows.

IsClosed It is used to retrieve a boolean value that indicates whether the

specified SqlDataReader instance has been closed.

Item[String] It is used to get the value of the specified column in its native
format given the column name.

Item[Int32] It is used to get the value of the specified column in its native
format given the column ordinal.

RecordsAffected It is used to get the number of rows changed, inserted or deleted

by execution of the Transact-SQL statement.

VisibleFieldCount It is used to get the number of fields in the SqlDataReader that are
not hidden.

METHODS

Method Description

Close() It is used to closes the SqlDataReader object.

GetBoolean(Int32) It is used to get the value of the specified column as a

Boolean.

GetByte(Int32) It is used to get the value of the specified column as a byte.

GetChar(Int32) It is used to get the value of the specified column as a single

character.

Dr. Hitesh Kumar Sharma Page 27

 .Net Technologies

GetDateTime(Int32) It is used to get the value of the specified column as a

DateTime object.

GetDecimal(Int32) It is used to get the value of the specified column as a Decimal

object.

GetDouble(Int32) It is used to get the value of the specified column as a double-

precision floating point number.

GetFloat(Int32) It is used to get the value of the specified column as a single-

precision floating point number.

GetName(Int32) It is used to get the name of the specified column.

GetSchemaTable() It is used to get a DataTable that describes the column

metadata of the SqlDataReader.

GetValue(Int32) It is used to get the value of the specified column in its native
format.

GetValues(Object[]) It is used to populate an array of objects with the column

values of the current row.

NextResult() It is used to get the next result, when reading the results of
SQL statements.

Read() It is used to read record from the SQL Server database.

To create a SqlDataReader instance, we must call the ExecuteReader method of the

SqlCommand object.

EXAMPLE
In the following program, we are using SqlDataReader to get data from the SQL Server. A C#
code is given below.

// Program.cs

1. using System;

Dr. Hitesh Kumar Sharma Page 28

 .Net Technologies

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

Dr. Hitesh Kumar Sharma Page 29

 .Net Technologies

38. }
39. }
40. }

Output:

Execute this program by combination of Ctrl+F5 and it will produce the following output.

ADO.NET DATASET
It is a collection of data tables that contain the data. It is used to fetch data without interacting
with a Data Source that's why, it also known as disconnected data access method. It is an
in-memory data store that can hold more than one table at the same time. We can use
DataRelation object to relate these tables. The DataSet can also be used to read and write
data as XML document.

ADO.NET provides a DataSet class that can be used to create DataSet object. It contains
constructors and methods to perform data related operations.

DATASET CLASS SIGNATURE

1. public class DataSet : System.ComponentModel.MarshalByValueComponent, System.Compo

nentModel.IListSource,
2. System.ComponentModel.ISupportInitializeNotification, System.Runtime.Serialization.ISerial
izable,
3. System.Xml.Serialization.IXmlSerializable

DATASET CONSTRUCTORS

Constructor Description

DataSet() It is used to initialize a new instance of the

DataSet class.

Dr. Hitesh Kumar Sharma Page 30

.Net Technologies

DataSet(String) It is used to initialize a new instance of a

DataSet class with the given name.

DataSet(SerializationInfo, It is used to initialize a new instance of a

StreamingContext) DataSet class that has the given serialization
information and context.

DataSet(SerializationInfo, It is used to initialize a new instance of the

StreamingContext, Boolean) DataSet class.

DataSet Properties

Properties Description

CaseSensitive It is used to check whether DataTable objects are case-

sensitive or not.

DataSetName It is used to get or set name of the current DataSet.

DefaultViewManager It is used to get a custom view of the data contained in the

DataSet to allow filtering and searching.

HasErrors It is used to check whether there are errors in any of the

DataTable objects within this DataSet.

IsInitialized It is used to check whether the DataSet is initialized or not.

Locale It is used to get or set the locale information used to

compare strings within the table.

Namespace It is used to get or set the namespace of the DataSet.

Site It is used to get or set an ISite for the DataSet.

Tables It is used to get the collection of tables contained in the

DataSet.

DataSet Methods

Dr. Hitesh Kumar Sharma Page 31

.Net Technologies
The following table contains some commonly used methods of DataSet.

Method Description

BeginInit() It is used to begin the initialization of a DataSet that

is used on a form.

Clear() It is used to clear the DataSet of any data by removing

all rows in all tables.

Clone() It is used to copy the structure of the DataSet.

Copy() It is used to copy both the structure and data for this
DataSet.

CreateDataReader(DataTable[]) It returns a DataTableReader with one result set per

DataTable.

CreateDataReader() It returns a DataTableReader with one result set per

DataTable.

EndInit() It ends the initialization of a DataSet that is used on a

form.

GetXml() It returns the XML representation of the data stored in

the DataSet.

GetXmlSchema() It returns the XML Schema for the XML representation

of the data stored in the DataSet.

Load(IDataReader, LoadOption, It is used to fill a DataSet with values from a data

DataTable[]) source using the supplied IDataReader.

Merge(DataSet) It is used to merge a specified DataSet and its schema

into the current DataSet.

Merge(DataTable) It is used to merge a specified DataTable and its

schema into the current DataSet.

Dr. Hitesh Kumar Sharma Page 32

 .Net Technologies

ReadXml(XmlReader, It is used to read XML schema and data into the

XmlReadMode) DataSet using the specified XmlReader and
XmlReadMode.

Reset() It is used to clear all tables and removes all relations,

foreign constraints, and tables from the DataSet.

WriteXml(XmlWriter, It is used to write the current data and optionally the

XmlWriteMode) schema for the DataSet using the specified XmlWriter
and XmlWriteMode.

EXAMPLE:
Here, in this example, we are implementing DataSet and displaying data into a gridview.
Create a web form and drag a gridview from the toolbox to the form. We can find it inside
the data category.

// DataSetDemo.aspx

1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataSetDemo.aspx.cs"

2. Inherits="DataSetExample.DataSetDemo" %>

Dr. Hitesh Kumar Sharma Page 33

 .Net Technologies

3. <!DOCTYPE html>
4. <html xmlns="http://www.w3.org/1999/xhtml">
5. <head runat="server">
6. <title></title>
7. </head>
8. <body>
9. <form id="form1" runat="server">
10. <div>
11. </div>
12. <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#33333
3" GridLines="None">
13. <AlternatingRowStyle BackColor="White" />
14. <EditRowStyle BackColor="#2461BF" />
15. <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
16. <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
17. <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /
>
18. <RowStyle BackColor="#EFF3FB" />
19. <SelectedRowStyle BackColor="#D1DDF1" Font-
Bold="True" ForeColor="#333333" />
20. <SortedAscendingCellStyle BackColor="#F5F7FB" />
21. <SortedAscendingHeaderStyle BackColor="#6D95E1" />
22. <SortedDescendingCellStyle BackColor="#E9EBEF" />
23. <SortedDescendingHeaderStyle BackColor="#4870BE" />
24. </asp:GridView>
25. </form>
26. </body>
27. </html>

CodeBehind

// DataSetDemo.aspx.cs

1. using System;
2. using System.Data.SqlClient;
3. using System.Data;
4. namespace DataSetExample
5. {

Dr. Hitesh Kumar Sharma Page 34

 .Net Technologies

6. public partial class DataSetDemo : System.Web.UI.Page

7. {
8. protected void Page_Load(object sender, EventArgs e)
9. {
10. using (SqlConnection con = new SqlConnection("data source=.; database=st
udent; integrated security=SSPI"))
11. {
12. SqlDataAdapter sde = new SqlDataAdapter("Select * from student", con);
13. DataSet ds = new DataSet();
14. sde.Fill(ds);
15. GridView1.DataSource = ds;
16. GridView1.DataBind();
17. }
18. }
19. }
20. }

Output:

Execute this code by the combination of Ctrl+F5. It will produce the following output.

Dr. Hitesh Kumar Sharma Page 35

 .Net Technologies

ADO.NET DATAADAPTER
The DataAdapter works as a bridge between a DataSet and a data source to retrieve data.
DataAdapter is a class that represents a set of SQL commands and a database connection. It
can be used to fill the DataSet and update the data source.

DATAADAPTER CLASS SIGNATURE

1. public class DataAdapter : System.ComponentModel.Component, System.Data.IDataAdapte

r

DataAdapter Constructors

Constructors Description

DataAdapter() It is used to initialize a new instance of a DataAdapter

class.

DataAdapter(DataAdapter) It is used to initializes a new instance of a DataAdapter

class from an existing object of the same type.

Methods

Method Description

CloneInternals() It is used to create a copy of this instance

of DataAdapter.

Dispose(Boolean) It is used to release the unmanaged

resources used by the DataAdapter.

Fill(DataSet) It is used to add rows in the DataSet to

match those in the data source.

FillSchema(DataSet, SchemaType, String, It is used to add a DataTable to the

IDataReader) specified DataSet.

Dr. Hitesh Kumar Sharma Page 36

 .Net Technologies

GetFillParameters() It is used to get the parameters set by

the user when executing an SQL SELECT
statement.

ResetFillLoadOption() It is used to reset FillLoadOption to its

default state.

ShouldSerializeAcceptChangesDuringFill() It determines whether the

AcceptChangesDuringFill property should
be persisted or not.

ShouldSerializeFillLoadOption() It determines whether the FillLoadOption

property should be persisted or not.

ShouldSerializeTableMappings() It determines whether one or more

DataTableMapping objects exist or not.

Update(DataSet) It is used to call the respective INSERT,

UPDATE, or DELETE statements.

EXAMPLE
// DataSetDemo.aspx

1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataSetDemo.aspx.cs"

2. Inherits="DataSetExample.DataSetDemo" %>
3. <!DOCTYPE html>
4.
5. <html xmlns="http://www.w3.org/1999/xhtml">
6. <head runat="server">
7. <title></title>
8. </head>
9. <body>
10. <form id="form1" runat="server">
11. <div>
12.

Dr. Hitesh Kumar Sharma Page 37

 .Net Technologies

13. </div>
14. <asp:GridView ID="GridView1" runat="server" CellPadding="3" BackColor="#DEBA
84"
15. BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellSpacing="2">
16. <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
17. <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
18. <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
19. <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
20. <SelectedRowStyle BackColor="#738A9C" Font-
Bold="True" ForeColor="White" />
21. <SortedAscendingCellStyle BackColor="#FFF1D4" />
22. <SortedAscendingHeaderStyle BackColor="#B95C30" />
23. <SortedDescendingCellStyle BackColor="#F1E5CE" />
24. <SortedDescendingHeaderStyle BackColor="#93451F" />
25. </asp:GridView>
26. </form>
27. </body>
28. </html>

CodeBehind

1. using System;
2. using System.Data.SqlClient;
3. using System.Data;
4. namespace DataSetExample
5. {
6. public partial class DataSetDemo : System.Web.UI.Page
7. {
8. protected void Page_Load(object sender, EventArgs e)
9. {
10. using (SqlConnection con = new SqlConnection("data source=.; database=st
udent; integrated security=SSPI"))
11. {
12. SqlDataAdapter sde = new SqlDataAdapter("Select * from student", con);
13. DataSet ds = new DataSet();
14. sde.Fill(ds);
15. GridView1.DataSource = ds;

Dr. Hitesh Kumar Sharma Page 38

 .Net Technologies

16. GridView1.DataBind();
17. }
18. }
19. }
20. }

Output:

Dr. Hitesh Kumar Sharma Page 39

 .Net Technologies

ADO.NET DATATABLE
DataTable represents relational data into tabular form. ADO.NET provides a DataTable class
to create and use data table independently. It can also be used with DataSet also. Initially,
when we create DataTable, it does not have table schema. We can create table schema by
adding columns and constraints to the table. After defining table schema, we can add rows to
the table.

We must include System.Data namespace before creating DataTable.

DATATABLE CLASS SIGNATURE

1. public class DataTable : System.ComponentModel.MarshalByValueComponent, System.Com

ponentModel.IListSource,
2. System.ComponentModel.ISupportInitializeNotification, System.Runtime.Serialization.ISerial
izable,
3. System.Xml.Serialization.IXmlSerializable

DATATABLE CONSTRUCTORS
The following table contains the DataTable class constructors.

Constructors Description

DataTable() It is used to initialize a new instance of the

DataTable class with no arguments.

DataTable(String) It is used to initialize a new instance of the

DataTable class with the specified table name.

DataTable(SerializationInfo, It is used to initialize a new instance of the

StreamingContext) DataTable class with the SerializationInfo and
the StreamingContext.

DataTable(String, String) It is used to initialize a new instance of the

DataTable class using the specified table name
and namespace.

DATATABLE PROPERTIES
The following table contains the DataTable class properties.

Dr. Hitesh Kumar Sharma Page 40

.Net Technologies

Property Description

Columns It is used to get the collection of columns that belong to this

table.

Constraints It is used to get the collection of constraints maintained by this

table.

DataSet It is used to get the DataSet to which this table belongs.

DefaultView It is used to get a customized view of the table that may

include a filtered view.

HasErrors It is used to get a value indicating whether there are errors in

any of the rows in the table of the DataSet.

MinimumCapacity It is used to get or set the initial starting size for this table.

PrimaryKey It is used to get or set an array of columns that function as

primary keys for the data table.

Rows It is used to get the collection of rows that belong to this table.

TableName It is used to get or set the name of the DataTable.

DataTable Methods
The following table contains the DataTable class methods.

Method Description

AcceptChanges() It is used to commit all the changes made to this table.

Clear() It is used to clear the DataTable of all data.

Clone() It is used to clone the structure of the DataTable.

Copy() It is used to copy both the structure and data of the

DataTable.

Dr. Hitesh Kumar Sharma Page 41

 .Net Technologies

CreateDataReader() It is used to returns a DataTableReader corresponding to

the data within this DataTable.

CreateInstance() It is used to create a new instance of DataTable.

GetRowType() It is used to get the row type.

GetSchema() It is used to get schema of the table.

ImportRow(DataRow) It is used to copy a DataRow into a DataTable.

Load(IDataReader) It is used to fill a DataTable with values from a data source

using the supplied IDataReader.

Merge(DataTable, It is used to merge the specified DataTable with the

Boolean) current DataTable.

NewRow() It is used to create a new DataRow with the same schema

as the table.

Select() It is used to get an array of all DataRow objects.

WriteXml(String) It is used to write the current contents of the DataTable

as XML using the specified file.

DATATABLE EXAMPLE
Here, in the following example, we are creating a data table that populates data to the
browser. This example contains the following files.

// DataTableForm.aspx

1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataTableForm.aspx.cs

"
2. Inherits="DataTableDemo.DataTableForm" %>
3. <!DOCTYPE html>
4. <html xmlns="http://www.w3.org/1999/xhtml">
5. <head runat="server">
6. <title></title>

Dr. Hitesh Kumar Sharma Page 42

 .Net Technologies

7. </head>
8. <body>
9. <form id="form1" runat="server">
10. <div>
11. </div>
12. <asp:GridView ID="GridView1" runat="server">
13. </asp:GridView>
14. </form>
15. </body>
16. </html>

CodeBehind
// DataTableForm.aspx.cs

1. using System;
2. using System.Collections.Generic;
3. using System.Data;
4. using System.Linq;
5. using System.Web;
6. using System.Web.UI;
7. using System.Web.UI.WebControls;
8. namespace DataTableDemo
9. {
10. public partial class DataTableForm : System.Web.UI.Page
11. {
12. protected void Page_Load(object sender, EventArgs e)
13. {
14. DataTable table = new DataTable();
15. table.Columns.Add("ID");
16. table.Columns.Add("Name");
17. table.Columns.Add("Email");
18. table.Rows.Add("101", "Rameez","rameez@example.com");
19. table.Rows.Add("102", "Sam Nicolus", "sam@example.com");
20. table.Rows.Add("103", "Subramanium", "subramanium@example.com");

21. table.Rows.Add("104", "Ankur Kumar", "ankur@example.com");

22. GridView1.DataSource = table;

Dr. Hitesh Kumar Sharma Page 43

 .Net Technologies

23. GridView1.DataBind();
24. }
25. }
26. }

Output:

C# Public Access Specifier Example

1. using System;
2.
3. namespace AccessSpecifiers
4. {
5. class PublicTest
6. {
7. public string name = "Santosh Singh";
8. public void Msg(string msg)
9. {
10. Console.WriteLine("Hello "+ msg);
11. }
12. }
13.
14. class Program
15. {
16. static void Main(string[] args)
17. {
18. PublicTest publicTest = new PublicTest();
19. // Accessing public variable

Dr. Hitesh Kumar Sharma Page 44

 .Net Technologies

20. Console.WriteLine("Hello "+publicTest.name);

21. // Accessing public method
22. publicTest.Msg("Peter Dicosta");
23. }
24. }
25. }

Output:

Hello Santosh Singh

Hello Peter Dicosta

Dr. Hitesh Kumar Sharma Page 45