Connected Architecture In Ado.net Pdf

Chapter Learning Objectives. ADO.NET Architecture. Connection class. Command and Data Reader Class. DataAdapter and DataTable class. DataSet class.

• Ado.net Disconnected Architecture
• Connected Architecture In Ado.net Pdf Tutorial
• Connected Architecture In Ado.net Pdf Free

Provider Agnostic codeADO.NET ArchitectureADO.NET uses a multilayer architecture that mainly has a few concepts, for instance Connection, Reader, Command, Adapter and Dataset objects. ADO.NET introduced data providers that are a set of special classes to access a specific database, execute SQL commands and retrieve data. The Data providers are extensible. Developers can create their own providers for a proprietary data source. There are some examples of data providers such as SQL Server providers, OLE DB and Oracle provider.ADO.NET provides the following two types of classes objects:. Connection-based: They are the data provider objects such as Connection, Command, DataAdapter and DataReader.

Ado.net Disconnected Architecture

They execute SQL statements and connect to a database. Content-based: They are found in the System.Data namespace and includes DataSet, DataColumn, DataRow and DataRelation. They are completely independent of the type of data source.ADO.NET NamespacesNamespacesDescriptionSystem.DataContains the definition for columns,relations,tables,database,rows,views and constraints.System.Data.SqlClientContains the classes to connect to a Microsoft SQL Server database such as SqlCommand, SqlConnection and SqlDataAdapter.System.Data.OdbcContains classes required to connect to most ODBC drivers.

These classes include OdbcCommand and OdbcConnection.System.Data.OracleClientContains classes such as OracleConnection and OracleCommand required to connect to an Oracle database.Table 1.1 ADO.NET NamespaceConnection ClassYou need to establish a connection class object for inserting, updating, deleting and retrieving data from a database. The Connection class allows you to establish a connection to the data source. The Connection class object needs the necessary information to discover the data source and this information is provided by a connection string.Connection StringsYou need to supply a connection string in the Connection class object. The connection string is a series of name/value settings separated by semicolons (;). A connection string requires a few peices of information such as the location of the database, the database name and the database authentication mechanism.This connection is used to connect to the Master database on the current computer using integrated security (indicating the currently logged-in Windows user can access the database).C# Code.

SqlConnectionStringBuilder obj = new SqlConnectionStringBuilder;. obj.DataSource = 'localhost';. obj.InitialCatalog = 'Master';. obj.IntegratedSecurity = true;. SqlConnection Conn = new SqlConnection(obj.ConnectionString);Important: Connections are a limited server resource so it is imperative to release the open connection as soon as possible.Command and Data Reader ClassesThe Command Class allows performing any data definition tasks such as creating and altering tables and databases, retrieving, updating and deleting of records.

Connected Architecture In Ado.net Pdf Tutorial

The Command object used to execute SQL queries can be inline text or a Stored Procedure. It is all dependent on the type of command you are using. Before using the command, you need to configure the Command Type, Text and Connection properties as in the following.C# Code. //Automatically releasing the Reader class Object. sdr = sc.ExecuteReader(CommandBehavior.CloseConnection);DataReader ClassThe DataReader Class object allows you to read the data returned by a SELECT command by a simple forward-only and read-only cursor. It requires a live connection with the data source and provides a very efficient way of looping and consuming all parts of the result set. The object of the DataReader cannot be directly instantiated.

Instead you must call the ExecuteReader method of the Command object and close the connection when you are done using the Data Reader, otherwise the connection remains alive until it is explicitly closed.DataReader with ExecuteReader MethodOnce you have the DataReader you can cycle through its records by calling the Read method in a while loop. This moves the row cursor to the next record.C# Code. string query = @'update AdventureWorksLT2008.SalesLT.Customer. set FirstName= 'ajay'. where CustomerID=2';.

//Command Class definition. SqlCommand sc = new SqlCommand(query, Conn);. // Data Adapter definition. SqlDataAdapter sda = new SqlDataAdapter;. sda.UpdateCommand = sc;Parameterized Commands (Stored Procedure)A Stored Procedure is a batch of one or more SQL Statements that are stored in the database. They are similar to a function in that they are well-encapsulated blocks of the logic that accept data using an input parameter and return data via result set or output parameter.

Connected Architecture In Ado.net Pdf Free

The following is the SQL code needed to create a procedure for extracting a single something from the customer table on behalf of a specific CustomerID.Sql.script.