ADO.NET Connection Architecture in Web and Windows Application Development

Active Data Object, Short name ADO.NET is set of the .NET libraries by .NET applications used as a data source communication specializing the driver or provider to handle a large amount of data flow.

Glimpse of ADO.NET Architecture:

Data management is very crucial while working with large client application like windows, web and other client applications might the office purpose. The data uses the Structured Query Language (SQL) for retrieval and stored using relational databases, such as SQL Server, Oracle, and Access and etc.

Whereas ADO.net acts as an interface to bridge between an RDBMS system and a .NET application to access the data.

Furthermore, the Microsoft .NET framework familiarized a new version of Active X Data Objects (ADO) called ADO.NET that lifts a rich set environment of classes through the ADO.NET library. This framework Data can be retrieved from any database by means of connected or disconnected architecture.

Why to choose ADO.NET for windows Application Development?

The importance of ADO.NET connection architecture given an amazing potentiality to consider it for any enterprise-level .Net application development. Here are 4 well-known considerations driving the ADO.NET.

1. 1. 1.

   Performance

1. : The perfection over updated ADO versions.

1. 1. 2.

   Interoperability

1. : The ability to interconnect across diverse environments.

1. 1. 3.

   Scalability

1. : The ability to serve an increasing number of clients without humiliating system performance.

1. 1. 4.

   Productivity

: The ability to fast develop healthy data access applications using rich and extensible component object model of ADO.NET.

ADO.Net interface is most suitable to connect to a database, execute a command and retrieving the data from the database. And a few of .NET applications that will use the ADO.NET

1. 1. ASP.NET Web Applications

1. 2. Windows Applications

3. Console Applications

ADO.Net Various Connection Architectures

Data handling usually relies on a connection-based model and a two-tier model. As the data processing gradually requiring to use of multi-tier architectures, today’s programmers are swapping to the disconnected method to deliver better scalability for their applications. And ADO.Net has two architectures are as follows.

The only difference between these two models is, the data processing will be done in a tightly connected environment and whereas in disconnected model dataset connect/disconnects will be occurring simultaneously. In fact, the application remains connected with the database all the way while processing.

About ADO.NET Class library

The .NET Runtime class library comes with ADO.NET functionality in n number of provider-specific namespaces and provider-specific namespaces are System.Data.OleDb, Microsoft.Data.Odbc and System.Data.SqlClient. For general namespaces, the three general namespaces are System.Data, System.Data.Common and System.Data.SqlTypes

According to the above image collected from C- sharpcorner, here are several types of applications (Web Application, Console Application, Windows Application,… etc.) that use ADO.NET to connect with databases (SQL Server, Oracle, OleDb, ODBC, XML files,… etc.).

Key Classes in ADO.NET

The Knowledge class offers methods for packing and performing SQL statements and Stored Actions.

1. 1) Connection Class

1. 2) Command Class

1. 3) DataReader Class

1. 4) DataAdaptor Class

5) DataSet.Class

Let’s check out the few key ADO.Net classes from the picture concerned.

1. Connection Class

We use connection classes to link to the database. These connection classes similarly manage connections and connection pooling in ADO.NET.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“connection”].ConnectionString);
con.Open();
–
–
–
con.Close();

2. Command Class

Various commands that are executed by the Command Class. The Command class deliver methods for storing and executing SQL statements and Stored Actions. Such as.

• ExecuteReader: Typically be an SQL select statement that covers one or more select statements and Stored Procedures. This technique returns a DataReader object that can be used to fill a DataTable object or used straight for production reports and so forth.
• ExecuteNonQuery: This method returns an integer that is the sum of rows affected by the query. Performs a command that variations the data in the database, such as an update, delete, or insert statement, or a Kept Way that covers one or more of these statements.
• ExecuteScalar: is a kind of query returns a total count of rows or an intended value. This method returns a single value only.
• ExecuteXMLReader: Obtains data from an SQL Server 2000 database (SqlClient classes only) using the XML stream. And Returns an XML Reader object.

3. DataReader Class

DataReader class is used in the conjunction with the Command class that performs an SQL Select statement and then access resumed rows. The DataReader major operation is used to retrieve data.

4. DataAdapter Class

The DataAdapter is supremely useful when consuming data-bound controls in the Windows Forms, but it can likewise be used to bring an easy way to accomplish the connection in between your application and core database tables, views and Stored Procedures. The major job of the DataAdapter is to connect the DataSets to databases.

5. DataSet Class

DataSet is essentially a collection of DataTable objects. In turn, each object contains a group of the DataColumn and DataRow objects. DataSet also shields a kindred collection that can be used to label relations among Data Table Objects. The DataSet is the core of ADO.NET.

How to connect to a Database using ADO.NET:

Connecting to a database using the ADO.NET. Are the step of activities that starts from creating a connection, where you basic be familiar with connection strings. A connection string is important as a parameter to SQLConnection. A ConnectionString is a string adaptable (not case sensitive).

This covers key and value pairs, like provider, server, database, userid and term as in the following:
Server=”nameof the server or IP Address of the server”

Database=”name of the database”

userid=”user name who has permission to work with database”

word=”the word of userid”

Example
SQL Authentication

String constr=”server=.;database=xxx;user id=krify;word=abc@123″;

Or:

String constr=”data source=.;initial catalog=xxx;uid=krify;pwd=abc@213″;

Windows Authentication

String constr=”server=.;database=institute;trusted_connection=true”

Or:

String constr=”server=.;initial catalog=institute;integrated security=true”

Data Display and Retrieval setup from the database:

1. 1. Creating a SqlConnection object by a connection string.

1. 2. Handle exceptions.

1. 3. Open the connection.

1. 4. Create an SQLCommand. To signify an SQLCommand like (select * from employee) and attach the existing joining to it. Specify the sort of SQLCommand (text/storedprocedure).

1. 5. Perform the command (use executereader).

1. 6. Get the Outcome (use SqlDataReader). This is a forwardonly/readonly dataobject.

1. 7. Close connection

1. 8. Process result

9. Display Result

The following is code for connecting to a SQL Database:

Your essential use of the System.Data.SqlClient namespace is to connect to a SQL Database. In the preceding code, we are using SqlConnection class, SqlCommand class, and the SqlDataReader class since our application is talking to the SQL Server. SQL Server only understands SQL.