ADO.NET Benefits

ADO.NET brings with it a number of benefits, which fall into the following categories:

Interoperability
The ability to communicate across heterogeneous environments.
Scalability
The ability to serve a growing number of clients without degrading system performance.
Productivity
The ability to quickly develop robust data access applications using ADO.NET’s rich and extensible component object model.
Performance
An improvement over previous ADO versions due to the disconnected data model.

Interoperability

All communication involves data exchange, whether the communication between distributed components is through a request/response methodology or a message-based facility. Current distributed systems assume that the components involved in the communication are using the same protocol and data format. This assumption is too restrictive for a client base to expand across an enterprise or for multiple companies. Data-access layers should impose no such restrictions.

In current Microsoft Windows Distributed interNet Applications (DNA) Architecture, application components pass data back and forth as ADO disconnected recordsets. The data-providing components, as well as the data-consuming components, are required to use the Component Object Model (COM). The payload, the actual content we are passing around, is packaged in a data format called Network Data Representation (NDR). These NDR packages are streamed between components.

There are two issues with current Windows DNA systems. The first is the requirement that both ends of the communication pipe have the COM library. The second issue is that it is difficult to set up and manage these communications across firewalls. If your middle-tier components are COM/DCOM-based and you are using them within your intranet, you are in good shape. To put it another way: if all your components use Microsoft technology, you’re fine. With the advent of electronic commerce (e-commerce), however, enterprise applications must interoperate with more than just Microsoft-development shops. ADO must improve for cross-platform components to seamlessly share data, breaking away from the limitations of COM/DCOM.

ADO.NET addresses the common data-exchange limitation by using XML as its payload data format. Since XML is text-based and simple to parse, it’s a good choice for a common, platform-independent, and transportable data format. Furthermore, because XML is nothing more than structured text, employing XML as the data format on top of the HTTP network protocol minimizes firewall-related problems. With ADO and its XML format, the clients do not have to know COM to de-serialize the packaged data. All they need is an XML parser, which is readily available in many flavors on many different platforms. The data producers and consumers need only adhere to the XML schema to exchange data among themselves.

Scalability

In a client/server model, it is typical for a client to acquire and hold onto a connection to the server until all requests are fulfilled. While this solution works fine in small- to medium-scale applications, it is not scalable across a large enterprise. As soon as the number of clients reaches a certain threshold, the server becomes the bottleneck, as database connections eat up network and CPU resources. ADO.NET moves away from the client/server model by promoting the use of disconnected datasets. When a client requests some data, the data is retrieved, it’s transferred to the client, and—as soon as possible—the connection is torn down. Since the connection between the client and the data source is short-lived, this technique allows more clients to request information from the server, thus solving the problem of limited connections.

You might think that setting up and tearing down connections is not a good idea since the cost of establishing a connection is usually high. This is a concern only in the absence of connection pooling. ADO.NET automatically keeps connections to a data source in a pool, so when an application thinks it is tearing down a connection, it’s actually returning it to the resource pool. This allows connections to be reused, avoiding the cost of reconstructing new connections from scratch.

Working with data in this disconnected fashion is not new to ADO programmers. The disconnected recordset was introduced in early versions of ADO. However, in ADO, it is up to the developer to implement this feature, whereas in ADO.NET, data is disconnected by nature.

ADO.NET has enhanced its predecessor by growing out of the client/server model and into the distributed components model. By using disconnected datasets as the paradigm for data exchange, ADO.NET is much more scalable than its predecessors.

Productivity

ADO.NET’s rich data access classes allow developers to boost their productivity. Current ADO developers should have no problems getting up to speed with the object model, because ADO.NET is a natural evolution of ADO. The core functionality remains the same. We still have the connection object, representing the pipeline through which commands are executed.[2] With ADO.NET, the functionality is factored and distributed to each object in the model—much better than in previous versions of ADO. For example, the connection object is responsible only for connecting to and disconnecting from the data source. In ADO.NET, we can no longer execute a query directly through the connection object. Although some developers might miss this ability, it is a step in the right direction for cohesion of component development.

Along with the familiar connection and command objects, ADO.NET introduces a number of new objects, such as DataSet and DataAdapter. All of these objects are discussed earlier in this chapter.

ADO.NET also boosts developers’ productivity through extensibility. Because ADO.NET framework classes are managed code, developers can inherit and extend these classes to their custom needs. If you prefer not to do this low-level legwork, you can use the Visual Studio. NET data-design environment to generate these classes for you.

Visual Studio .NET is a great Rapid Application Development (RAD) tool for developing applications with ADO.NET. You can have the Component Designer generate ADO.NET typed DataSets. These typed DataSets are extended types, modeled for your data. You don’t have to reference database fields by their names or indices but instead by their names as properties of typed objects. For example, instead of oRow(customerName), you can use oRow.customerName. The generated code is much more readable, when compared to previous Microsoft code generators. In addition, these generated classes are type-safe, thus reducing the chances for errors and allowing compilers and the CLR to verify type usage.

In short, ADO.NET improves developers’ productivity through its rich and extensible framework classes. These features are complemented by the rich toolsets for ADO.NET in Visual Studio .NET, which enable rapid application development.

Performance

Because ADO.NET is mainly about disconnected datasets, the system benefits from improved performance and scalability. The database server is no longer a bottleneck when the number of connection requests goes up. Data Providers in ADO.NET also enable implicit connection pooling, which reduces the time required to open a connection.

Previous marshaling of recordsets required type conversion to make sure that the data types were all COM-based. Since the disconnected dataset is in XML format, there is no need for this type conversion during transport, as opposed to dealing with data in Network Data Representation format.

With the ADO.NET architecture, the data providers can be optimized for better performance by talking directly to the database server. .NET Framework Data Provider for SQL Server is an example of this as we can see later in this chapter.

Advertisements

ADO.Net Developers Guide

ADO.NET 3.5 Cookbook, 2nd Edition

m96s7l

Click here to download

Connection to Sql server using C#

Namespace for SQL Connection:

using System.Data.Sqlclient;

using System.Configration;

Components of ADO.NET

IDBConnection:

SqlConnection con=new SqlConnection();

Properites

ConnectionString

Syntax:

For Windows Authentication:

con.ConnectiionString=”Server=localhost; Database=DatabaseName; IntegratedSecurity=true”;

For Sql Authentication:

con.ConnectiionString=”Server=serverName; Database=DatabaseName; uid=as; pwd=sa”;

State
It gives State of the Connection

Open();
Close();
Broken();
Unknown();

IDBCOmmand:

Action Query——>Insert, Update, Delete

Retrival Query—->Select

1. ExcuteNonQuery()

Used for excuting Inserte, Update and Delete. Result of the method is a Integer

2. ExcuteScaler()

Returns Object as a Result.(Count no.of items in a perticular Column).

3.ExcuteReader()

Excute selecte statement and returns DataReader as a result.

DataReader

It is forward Read only, values cann’t be altered and does not MetaData not serializable

Methods

Read();
Close();

Application cann’t closed without closing DataReader

Properties of IDBCommand:

Command Type

TableDirect
Text
StoredProcedure

Connection to Sql server using C#

Step 1. open SQL server and create a datebase(name=org. Table name=emp). Table contains the following columns

Id —->Int

Name —->VarChar(50)

Session —->VarChar(50)

Dob —->VarChar(50)

step 2.Open VS 2008 and create new project(Windows form) named DbTest and design the form as given below.

DbTest

Step 3. From the solution Explorer add new Application configration file and enter Define ConnectionString

<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
<connectionStrings>
<add name=”Datacon” connectionString=”server=ASHOK\SQLEXPRESS;DataBase=org;Integrated Security=true”/>
</connectionStrings>
</configuration>

<?xml version=”1.0″ encoding=”utf-8″ ?>

<configuration>

<connectionStrings>

<add name=”Datacon” connectionString=”server=ASHOK\SQLEXPRESS;DataBase=org;Integrated Security=true”/>

</connectionStrings>

</configuration>

Step 4. Add a new class named DataControl

class DataConnection
{
public static SqlConnection Getconnection()
{
SqlConnection con = new SqlConnection();
string str = ConfigurationManager.ConnectionStrings[“Datacon”].ConnectionString;
con.ConnectionString = str;
return con;
}
}

Step 5. Add the given code to the TestConnection Btn control method

private void TestConBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
using (con = DataConnection.Getconnection())
{
con.Open();
MessageBox.Show(“Connection ” + con.State);
}
}

Step 6. Add the given code to the View Btn control method

private void ViewBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
SqlCommand cmd;
SqlDataReader dr;
using (con = DataConnection.Getconnection())
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = “Select Name from Employee”;
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr.GetString(0));
}
}
}

Step 7. Add the given code to the ComboBox control method

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
SqlCommand cmd;
SqlDataReader dr;
string s = comboBox1.SelectedItem.ToString();
using(con = DataConnection.Getconnection())
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = “Select * from Employee where name='” + s + “‘”;
dr = cmd.ExecuteReader();
while (dr.Read())
{
textBox1.Text = dr.GetInt32(0).ToString();
textBox2.Text = dr.GetString(1);
textBox3.Text = dr.GetString(2);
textBox4.Text = dr.GetString(3);
}
}
}

Step 8. Add the given code to the AddBtn control method

private void AddBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
using(con = DataConnection.Getconnection());
{
string str = “insert into Employee(Id,name,session,DOB) values(@Id,@name,@session,@DOB)”;
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue(“@Id”,textBox1.Text);
cmd.Parameters.AddWithValue(“@name”, textBox2.Text);
cmd.Parameters.AddWithValue(“@session”,textBox3.Text);
cmd.Parameters.AddWithValue(“@DOB”,textBox4.Text);
con.Open();
int i = cmd.ExecuteNonQuery();
MessageBox.Show(“New Record Added” + i.ToString());
}
}

Step 9. Add the given code to the UpdateBtn control method

private void UpdateBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
using(con = DataConnection.Getconnection())
{
string str = “Update Employee SET Id=@Id,name=@name,session=@session,DOB=@DOB where id=@Id”;
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue(“@Id”, textBox1.Text);
cmd.Parameters.AddWithValue(“@name”, textBox2.Text);
cmd.Parameters.AddWithValue(“@session”, textBox3.Text);
cmd.Parameters.AddWithValue(“@DOB”, textBox4.Text);
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show(“Record Updated”);
}
}

Step 10. Add the given code to the DeleteBtn control method

private void DeleteBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
using (con = DataConnection.Getconnection())
{
string str = “Delete from Employee where id=@Id”;
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue(“@Id”, textBox1.Text);
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show(“” + textBox1.Text + “Record Deleted”);
}
}

.NET Framework Essentials by O’Reilly Media

Here is a cool document that i found about .NET Framework

Data access namespaces and Objects in.Net..

Data access namespaces:

Following are the most common data access namespaces :

  • System.Data
  • System.Data.OleDb
  • System.Data.SQLClient
  • System.Data.SQLTypes
  • System.Data.XML

Main objects in ADO.NET

Following are the main objects in ADO.NET :

  • OleDbConnection / SQLConnection
  • OleDbCommand / SQLCommand
  • OleDbDataReader / SQLDataReader
  • OleDbDataAdapter / SQLDataAdapter
  • OleDbParameter / SQLParameter
  • DataSet
  • DataTable
  • DataView
  • DataRow
  • DataColumn

Most of the objects mentioned above can be created as ‘stand-alone’ objects via code


Introduction to ADO.NET

ADO.NET is made of a set of classes that are used for connecting to a database, providing access to relational data, XML, application data, and retrieving results. ADO.NET includes .NET Framework data providers for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, or placed in an ADO.NET DataSet object

The ADO.NET classes are found in System.Data.dll, and are integrated with the XML classes found in System.Xml.dll. When compiling code that uses the System.Data namespace, reference both System.Data.dll and System.Xml.dll

Differance between ADO and ADO.NET

I’ve just introduces something Microsoft calls ADO.NET. Don’t confuse this new .NET data access interface with what we have grown to know and understand as ADO—I think it’s really very different. Yes, ADO.NET and ADO both open connections and fetch data, however, they do so in different ways using different objects and with different limitations. No, they aren’t the same—no matter what Microsoft names them. Yes,

ADO.NET has a Connection object, Command object, and Parameter objects (actually implemented by the SqlClient, OleDb and Odbc .NET Data Providers), however, they don’t have the same properties, methods, or behaviors as their ADOc counterparts.

ADO

1. This object model could be used even for non RDBMS products. We can read data from xml excel files also.

2. ADO objects are dependent on OLEDB providers and OLEDB providers would connect to the back end.

3. Fully COM based.

ADO.NET

1. It is not an enhanced version of ADO. Its a new object model which has some objects similar to ADO.

2.In ADO.NET we use Managed Providers.