ADO.NET

ADO.NET
 -
Nguyễn Mạnh Hiếu -

Contents


I.Introducing ADO.NET
II.The ADO.NET Object Model
III.Features of ADO.NET
IV.Working in a connected and a Disconnected environment
V.Creating and managing Connections
VI.Summary

I. Introducing ADO.NET

Most applications need to handle data, whether it is in the form of a database, a text file, or a spreadsheet. For a majority of modern-day applications, smooth data access has become crucial because voluminous amount of data is maintained in the databases. Therefore, to access this data the applications need to implement the functionality whereby they can interact with various databases, such as Microsoft SQL Server, oracle, and MS Access. This functionalities are based on the varied requirements of the user. For example, a user may require only retrieve and update of data. To provide these functionalities, ADO.NET is used as a data access technology. ADO.NET can be used with all .NET Framework compliant programming languages, such as visual basic .NET, VC#.NET and C++;

1) Understanding ADO.NET:

In organizations, business applications need to manage voluminous data. Data is stored in a relational database in the form of related tables. Retrieving and manipulating data directly from a database requires the knowledge of database commands. This may at times become a limitation because everyone may not be conversant with database commands. For example, updating the data stored in a SQL Server database may not be convenient for a person who does not know DML (data manipulation language) commands or statements.

To overcome this limitation, business applications allow users to retrieve data stored in a database and present it in user-friendly interface, without having to write database commands. The user can even add, delete, and update database records directly from these applications Microsoft has created a family of data access technologies to help programmer build efficient applications to manage data, regardless of its source. The following are the guidelines for selecting data access technology to be used in your application:

  • If you are writing a managed code targeting the .NET Framework in visual basic, C#, or C++, you should use ADO.NET
  • If you are writing a native code targeting windows by using C or C++, you should use Microsoft Open Database Connectivity (ODBC)
  • If you are writing a Microsoft Active Server pages (ASP)- based application, a VB6 Component Object Model(COM) application, or a C++ application that is already using com, you should use Object Linking and Embedding(OLE) DB.
  • If you are writing a java code targeting SQL Server, you should use java database connectivity(JDBC)

2.ADO.NET Isn’t a New Version of ADO

ADO.NET is a completely new data access technology, with a new design that was built.

+ Comparison of ADO.NET and ADO:

  ADO ADO.NET
In-memory Representations of Data Recordset: looks like a single table. Dataset: is a collection of one or more tables
Data Navigation In ADO you scan sequentially through the rows of the recordset using the ADO MoveNext method In ADO.NET, rows are represented as collections, so you can loop through a table as you would through any collection, or access particular rows via ordinal or primary key index

Sharing Data Between Applications

Richer data types

COM marshalling provides a limited set of data types Because the transmission of datasets in ADO.NET is based on an XML format, there is no restriction on data types.
Firewalls A firewall can interfere with two components trying to transmit disconnected ADO recordsets Because components exchange ADO.NET datasets using XML, firewalls can allow datasets to pass.

ADO.NET Isn’t a New Version of ADO. The following are the two most important ones:

  • ADO.NET is an integral part of .NET, not an external entity.
  • ADO.NET isn’t a collection of ActiveX components.

.NET Framework architecture 2.0

ADO.NET is a part of .NET Framework architecture. It is a model used by .NET applications to communicate with a database for retrieving, accessing, and updating data.

3) Client applications connect with these data sources using ADO.NET

A data centric application

As shown in the preceding figure, client application could be windows application, web application or any other applications such as office applications, mobile applications. These applications may need to retrieve and access data from various data sources. The data sources can also be Oracle, OLE DB, or Microsoft SQL Server data sources. The data sources can also be an XML document, a text file or a web service. The various types of client applications can connect with these data sources using ADO.NET

II. The ADO.NET Object Model

ADO.NET enables applications to connect to data sources and manipulate the data. It is based on an Object model that is based on the standards laid down by the world wide web consortium (w3c). This model has been designed in such a way that a developer can access and write to a wide variety of data sources such as Microsoft SQL Server and XML. By using ADO.NET, data can be retrieved from one data source and saved in another. For example, data can be retrieved from Microsoft excel and then saved in a XML document. The following figure shows the ADO.NET object model. In the ADO.NET object model, the data residing in a database is retrieved through a data provider. The data provider is a set of components including the connection, Command, Data Reader and Data Adapter objects. It provides data to the application can access data either through a dataset or through a Datareader object. ADO.NET also enables to create an XML representation of a dataset. In an XML representation of a data set, data is written in XML format, an dataset schema is written by using the XML schema definition language (XSD). The two key components of the ADO.NET object model are:
  • Data provider
  • Dataset

1) Data provider

A data provider is user for connecting to a database, retrieving data, storing the data in a dataset, reading the retrieved data, and updating the database. Selecting an appropriate data provider for a client application depends on the type of data source that is being accessed. The four main types of data providers are:
  • .NET Framework data provider for SQL Server: the .NET Framework data provider for SQL Server is used to work specifically with Microsoft SQL Server. The System.data.dll assembly implements SQL Server .NET Framework data provider in the System.data.SQLclient namespace
  • .NET Framework data provider for OLE db; the .NET Framework data provider for OLE db user native OLE db through component object model (com) interoperability to enable data access. To user this type of provider, you must user an OLE db provider, this type of data provider works with the OLE DB provider. Oracle OLE be provider, and the jet OLE db provider. The System. Data.dll assembly implements OLE DB .NET Framework data provider in the System.data.OLE namespace
  • .NET Framework data provider for ODBC: the .NET Framework data provider for ODBC users the native ODBC driver manager through com interoperability to enable data access. To use this type of provider, you must user an ODBC driver. The System.dataODBC.dll essembly implements ODBC .NET Framework data provider. This assembly is not part of the visual studio.NET installation
  • .NET Framework data provider for oracle: the .NET Framework data provider for oracle enables data access to oracle data sources through the oracle client connectivity software. Oracle client software must be installed on the System before you can use the provider to connect to an oracle data source. The System.data.oracleclient.dll assembly implements the oracle .NET Framework data provider in the System.data.oracleclient namespace.
The four key components of a data provider are:
  • Connection: this component is used to establish a connection with a data source such as database
  • Command: this component is used to retrieve, insert, delete, or modify data in a data source
  • Data Reader: this component is used to retrieve data from a data source in a read-only and forward-only mode.
  • Data Adapter: this component is used to transfer data to and from a database. A data adapter retrieves data from a database into a dataset.

2) Dataset

The dataset is a memory-based relational representation of data. A dataset is a part of the disconnected environment. A dataset is a disconnected, cached set of records that are retrieved from a database. A dataset contains a collection of one ore more Datatable objects made up of rows and columns of data as well as constraint an relation information about data in the Datatable objects. A dataset is present in the dataset class in the System.Data namespace. The key components of a dataset are:
  • Datatablecollection: it contains all the tables retrieved from the datasource
  • Datarelationcollection: it contains relationship and links between tables in a dataset.
  • Datatable: it represents a table in the datatable collection of a dataset
  • Datarowcollection: it contains all the rows in a datatable.
  • Datacoloumcollection:it contains all the coloumns in a datatable
*)An application can access data either through a dataset or through a datareader object: • Using a dataset: in this case, the data is cached in a dataset and the application accesses the data from the dataset. • Using a data reader: in this method, a dataReader object, which is a component of the data provider, uses the connection object to connect to the database, uses the command object to retrieve data, and provides data to the application in a read-only and forward-only mode.

III. Features of ADO.NET

The key features of ADO.NET are:
  • Disconnected data architecture: ADO.NET also supports disconnected data architecture. Applications connect to database only while retrieving and updating data. After the data is retrieved, the connection with database is closed. When the database needs to be updated, the connection is re-established. Working with applications that do not follow a disconnected architecture leads to a wastage of valuable System resources. The application connects to the database and keeps the connection open till the application is running. However, the application interacts with the database only when is has to retrieve and update data. In a disconnected architecture, a database can cater to the needs of several applications simultaneously because the interaction is for a shorter duration.
  • Data cached in datasets: a dataset is the most common method of accessing data in ADO.NET because it implements a disconnected architecture. It is not possible for the application to interact with the database for processing each record. This is because ADO.NET is based on a disconnected data structure. Therefore, the data is retrieved and stored in datasets. You can work with the records stored in a dataset as you work with real data. The only difference is that the dataset is independent of datasource and you remain disconnected from the data source.
  • Scalability: ADO.NET supports scalability by working with datasets. Database operations are performed on the dataset instead of on the database. As a result, resoures are saved and the database can meet the increasing demands of users more efficiently
  • Data transfer in XML format: XML is the fundamental format for data transfer in ADO.NET. data is transferred from a database in to a dataset and from the dataset to another component by using XML. You can even use an XML file as a data source and store data from it in a dataset. Using XML as the data transfer language is beneficial because XML is a industry standard format for exchanging information between different types of applications. Because a dataset is stored in the XML format, you can transmit it between different types of application that support XML, and any component that can read the dataset structure can process the data.

IV. Working in a connected and a Disconnected environment

A challenge related to data-centric applications is that multiple users need to access data simultaneously from the data source. As the number of users accessing an application increases, the network becomes slow and might lead to network congestion or hamper the application performance. Therefore, some of the factors that decide the type of data access environment used for an application include number of users who intend to use the application and the functionality of the application. ADO.NET environment can be categorize into connected and disconnected environment. A connected environment requires a constant connection to transfer data between the client application and the data source. However, a disconnected environment retrieves data and performs modifications without having to get connected to the network all the time.

1) A Connected environment

A connected environment is one in which a user or an application is constantly connected to a data source. A connected environment provides the following advantages: ? Data concurrency issues are easier to control because if one user is connected to a database Server and updating the data, another user can not make modifications to the same data unless the first user has completed his modifications. Data is current and updated A connected environment have the following limitations :
  • A constant network connection may, sometimes, lead to network traffic logging.
  • Scalability and performance issues in applications: If multiple users are connected to the same data source, query processing time may increase while accessing the data from the data source

2) A disconnected environment

A disconnected environment is one in which a user or an application is not directly connected to a data source. In a disconnected environment, users or applications connect to a data source only to retrieve data. After data is retrieved, the connection re-established. A disconnected environment provides the following advantages:
  • Allows multiple applications to simultaneously interact with the data source.
  • Improves the scalability and performance of applications.
A disconnected environment has the following limitations:
  • Data is not always up to date as no connection is established with the data source.
  • Data concurrency issues can occur when multiple users are updating the data to the data source.

V. Creating and managing Connections:

1. Creating a connection object:

The connection component of data provider is used to establish a connection with a data source. To move data between a data source and a client application, you must first create a connection object and then provide a connection string for this object.

To connect to a Microsoft SQL Server database, you use the sqlConnection class to open the connection. The following table displays the commonly used properties and methods of a SqlConnection class. Name Descripton ConnectionString property It provides information, such as the data source and database name, that is used to establish connection with a database Open() method It open a database connection with the property setting specified by the connectionString property Close() method It closes the connection to the database The ConnectionString property provides the information that defines a connection o a database by using a string of parameters. Parameter Description Provider It is used to set or return the name of the provider for the connection Initial catalog It is used to specify the name of the database Data Source It is used to specify the name of the server to be used when a connection is open User ID It is used to specify the server login account Password It is used to specify the login password for the server login account Integrated security It is used to determine whether or not the connection needs to be a secure connection. When true, the current windows account credentials are used for authentication. Recognized values are true, false, yes, no and SSPI( security support provider interface), which is equivalent to true

For example:

// create an object of the SqlConnection class

SqlConnection con=new SqlConnection();

// create a connection string to the hr database

Connection.ConnectionString= “Data Source=SQLSERVER01;Initial Catalog=hr;user ID=sa;Password=niit“;

2. Creating a Command object

After a connection has been established with the data source, you can execute commands and return results from the data source using a command object.

Ex:

// create an object of the SqlConnection class

SqlConnection con=new SqlConnection();

// create a connection string to the hr database

Connection.ConnectionString=“Data Source=SQLSERVER01;Initial Catalog=hr;user ID=sa;Password=niit“;

// create an object of the sqlcommand class

SqlCommand cmd=new SqlCommand(“ select * from monthlysalary”,con);

3. Opening thee connection object

After you have defined the connection string, you need to open the connection by using the Open() method. The Open() method users the information in the Connectionstring property of the connection object to connect to the data source and establish a connection.

Ex:

// create an object of the SqlConnection class

SqlConnection con=new SqlConnection();

// create a connection string to the hr database

Connection.ConnectionString=“Data Source=SQLSERVER01;Initial Catalog=hr;user ID=sa;Password=niit“;

//open the connection

Con.Open();

// create an object of the sqlcommand class

SqlCommand cmd=new SqlCommand(“ select * from monthlysalary”,con);

4. Executing SQL Statements in the Command Object

To execute the query passed in the command object, you can call its methods. The following code snippet demonstrates how to execute SQL statements in a command object:

// create an object of the SqlConnection class

SqlConnection con=new SqlConnection();

// create a connection string to the hr database

Connection.ConnectionString=“Data Source=SQLSERVER01;Initial Catalog=hr;user ID=sa;Password=niit“;

//open the connection

Con.Open();

// create an object of the sqlcommand class

SqlCommand cmd=new SqlCommand(“ select * from monthlysalary”,con);

// create an object of the SqlDataReader class

SqlDataReader myReader=cmd.EcecuteReader();

Closing the Connection object

After executing the command that you want to execute, you should close the connection. The connection to the database can be closed using the Close() method on the connection object.

Ex:

// create an object of the SqlConnection class

SqlConnection con=new SqlConnection();

// create a connection string to the hr database

Connection.ConnectionString=“Data Source=SQLSERVER01;Initial Catalog=hr;user ID=sa;Password=niit“;

//open the connection

Con.Open();

// create an object of the sqlcommand class

SqlCommand cmd=new SqlCommand(“ select * from monthlysalary”,con);

// create an object of the SqlDataReader class

SqlDataReader myReader=cmd.EcecuteReader();

// read a row of data at a time

myReader.Read();

//close the connection

Con.close();

VI. Summary:

  • Every program manipulates data. Most real-world programs use data stored in relational databases, so every C# programmer needs to know how to access relational data.
  • ADO.NET is a mature (but still growing) that has considerably more power. However, we don’t interact with data in datasets in the same way we do with data in database tables. The difference between the relational model of data and the object-oriented model of programming is considerable, and ADO.NET does relatively little to reduce the impedance between the two models.
  • But the future is quite promising. Microsoft is adding a general-purpose query capability, called LINQ (Language-Integrated Query)
  • Nguyen Manh hieu

Thông tin liên hệ:
Nguyen Lam Thi Minh Dieu
Email: dieu.nguyenlamthiminh@niit.edu.vn.
Website: www.nguyenlamminhdieu.com