ADO.Net Tutorial - how to use Ado.net

Introduction – ADO.NET

2008-11-12T01:09:00.000-08:00

ADO.NET is a model intended primarily for disconnected data access. ADO.NET provides two strategies for you to work with this model.

1. Store data in a dataset, which is an in-memory cache of records you can work with while disconnected from the data source. To use a dataset, you use a data adapter to fill it from the data source. You then work with the data in the dataset.

2. Perform operations directly against the database. In this strategy, you use a data command object. You can then open a connection, execute the command to perform the operation, and then close the connection.

Each strategy has its own advantages. So you should choose a strategy based on what your data-access requirements are.

Advantages of Storing Data in Datasets

The advantages of the dataset model are:

· Data exchange with other applications - A dataset provides a powerful way to exchange with other components of your application and with other applications.

· Moving data between tiers in a distributed application - By keeping data in a dataset, you can easily move it between the presentation tier, business tier, and data tier of your applications.

· Working with multiple tables - A dataset can contain multiple tables. You can work with the tables individually or navigate between them as parent-child tables.

· Maintaining records for reuse - A dataset allows you to work with the same records repeatedly without re-querying the database.

· Manipulating data from multiple sources - The tables in a dataset can represent data from many different sources. Once the data is in the dataset, you can manipulate it and relate it as if it had come from a single source.

· Data binding - If you are working with forms, it is usually easier to bind controls to data in a dataset than it is to programmatically load data values into the control after executing a command.

· Ease of programming - When you work with a dataset, you can generate a class file that represents its structure as objects. This makes it easier, clearer, and less error-prone to program with, and is supported by Visual Studio tools such as IntelliSense, the Data Adapter Configuration wizard, and so on.

Advantage of performing Database Operations Directly

The advantages of performing database operations directly:

· More control over execution - By using commands, you get more direct control over how and when an SQL statement or stored procedure is executed and what becomes of the results or return values.

· Less overhead - By reading and writing directly in the database, you can bypass storing data in a dataset. Because the dataset requires memory, you can reduce some overhead in your application. This is especially true in situations where you intend to use the data only once. In that case, creating and filling a dataset might be an unnecessary step in displaying the data.

· Extra functionality - There are some operations, such as executing DDL commands, that you can run only by executing data commands.

· Less programming in some instances - In a few instances, particularly Web applications, there is some extra programming required to save the state of a dataset. If you use a data reader to read directly from the database, you avoid the extra steps required to manage the dataset.

Recommendations for Accessing Data

The following sections provide recommendations for which data-access strategy to use with specific types of applications.

Web Forms

Use data commands in general; use a data reader to fetch data. Because Web Forms pages and their controls and components are recreated each time the page makes a round trip, it often is not efficient to create and fill a dataset each time, unless you also intend to cache it between round trips.

Use datasets under the following circumstances:

· You need to perform extensive processing with each record you get from the database.

· You want to work with multiple separate tables or tables from different data sources.

· If your data processing involves interdependent records.

· If you want to perform XML operations such as XSLT transformations on the data.

· You are exchanging data with another application or a component such as an XML Web service.

· If you prefer the ease of programming provided by datasets.

XML Web Services

XML Web services are ASP.NET Web applications, and therefore use the same model as Web Forms pages: the XML Web service is created and discarded each time a call is made to it. This suggests that the data-access model for an XML Web service is largely the same as it is for Web Forms. However, XML Web services are often middle-tier objects, and an important part of their purpose is often to exchange data with other applications across the Web.

Use a dataset if:

· Your XML Web service sends and receives data.

· For any of the reasons listed above for Web Forms.

Use a data command if:

· The XML Web service is performing a non-query operation, such as a DDL command.

· The XML Web service is retrieving a scalar value.

· The XML Web service is calling a stored procedure to execute logic within the database.

Windows Forms

In general, in a Windows Form, use a dataset. Windows Forms are typically used on rich clients where the form is not created and discarded with each user operation, as with Web Forms. Windows Forms applications also traditionally offer data-access scenarios that benefit from maintaining a cache of records, such as displaying records one by one in the form.

Use a dataset if:

· You are using the Windows Forms data-binding architecture, which is specifically designed to work with datasets.

· You are working with the same records repeatedly, such as allowing a user to navigate between records.

· For any of the other reasons listed under Web Forms above.

Use a data command if:

· You are performing a non-query operation, such as a DDL command.

· You are getting a scalar value from the database

· You are getting read-only data to display in a form — for example, creating a report. Stated differently, if there is no need to keep the data available after accessing it, use a data command.

What is ADO.net

ADO.net is data access architecture for the Microsoft .NET Framework.

Difference between ADO and ADO.net

· ADO used connected data usage, while ADO.net used disconnected data environment.

· ADO used OLE DB to access data and is COM-based, while ADO.net uses XML as the format for transmitting data to and from your database and web application.

· In ADO, Record set, is like a single table or query result, while in ADO.net Dataset, can contain multiple tables from any data source.

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

Difference between ADO.net Dataset and ADO Recordset

· A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
A DataSet is designed to work without any continuing connection to the original data source.
Data in a DataSet is bulk-loaded, rather than being loaded on demand.
There's no concept of cursor types in a DataSet.
DataSets have no current record pointer You can use For Each loops to move through the data.
You can store many edits in a DataSet, and write them to the original data source in a single operation.
Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.

ADO.net Terms

· Data Source: It can be a database, text file, excel spread sheet or an XML file.

· Data Provider: A set of libraries that is used to communicate with data source. Eg: SQL data provider for SQL, Oracle data provider for Oracle, OLE DB data provider for access, excel or mysql.

· SQL Connection: It establishes connection.

· SQL Command: It allows to manipulate database by executing stored procedure or sql statements.

· SQL DataReader: It provides a forward-only, read-only, connected recordset.

· DataSet: dataset is a disconnected, in-memory representation of data. It can contain multiple data table from different database.

· SQL DataAdapter: It populates dataset from data source. It contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database.

· DataView: It provides a means to filter and sort data within a data table.

Difference between Dataset and DataReader Object

Points to be consider while choosing between the DataSet and DataReader objects.

DataSet object	DataReader object
Read/Write access	Read-only access
Supports multiple tables from different databases	Supports a single table based on a single SQL query of one database
Disconnected mode	Connected mode
Bind to multiple controls	Bind to a single control
Forward and backward scanning of data	Forward-only scanning of data
Slower access to data	Faster access to data
Greater overhead to enable additional features	Lightweight object with very little overhead
Supported by Visual Studio .NET tools	Must be manually coded

SQLDataReader makes exclusive use of connection

The SQLDataReader object makes exclusive use of its SQLConnection object as long as it is open. You are not able to execute any other SqlCommand objects on that connection as long as the SQLDataReader object is open. Therefore, you should always call SQLDataReader.close() as soon as you are done retrieving data.

Strongly Typed Dataset Object

Strongly typed Dataset object allows you to create early-bound data retrieval expression.

Advantage of Strongly Typed dataset

It is faster than late-bound data retrieval expression.

Its column name is shown in intellisense as you type code.

Improving Performance with Connection Pooling

Opening a connection is a database-intensive task. It can be one of the slowest operations that you perform in an ASP.NET page. Furthermore, a database has a limited supply of connections, and each connection requires a certain amount of memory overhead (approximately 40 kilobytes per connection).

If you plan to have hundreds of users hitting your Web site simultaneously, the process of opening a database connection for each user can have a severe impact on the performance of your Web site.

Fortunately, you can safely ignore these bad warnings if you take advantage of connection pooling. When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a new connection, an active connection is removed from the pool. When you close the connection, the connection is placed back in the pool.

Connection pooling is enabled for both OleDb and SqlClient connections by default.To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool. For this reason you should place your connection string in the web.config file and retrieve it from this file whenever you need to open a connection

To take advantage of connection pooling in your ASP.NET pages, you also must be careful to explicitly close whatever connection you open as quickly as possible. If you do not explicitly close a connection with the Close() method, the connection is never added back to the connection pool.

SQL Injection Problem

SQL injection is a strategy for attacking databases.

Example1:
An ASP page asks the user for a name and a password, and then sends the following string to the database:
SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'

It seems safe, but it isn't. A user might enter something like this as her user name:
' OR 1>0 --

When this is plugged into the SQL statement, the result looks like this:
SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''

This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.

The easiest way to prevent this sort of injection is to parse the SQL string and remove any occurrences of "--" before passing the statement.

Example 2:
You also have to beware of injections that contain semicolons because semicolons delimit SQL statements. Think about the implications of a user name like this:
' OR 1>0 ; DELETE Customers ; --

There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere. Thanks to the way SQL passes parameters, injections such as those above will produce errors, and the stored procedure will not execute.

Transaction Management in ADO.NET

2008-11-12T01:07:00.000-08:00

A Database is a software system that defines a collection of predefined operations. Mainly it includes following operations

· Efficient management of large amount of persistent data in a persistent storage (database)

· Transaction Management which includes Concurrency Control, Atomicity and backup recovery procedure

· A DataModel which gives a separate level of abstraction

In this article I am concentrating of transaction management that is Concurrency Control in .NET environment.

A transaction is an abstract unit of concurrent computation that execute automatically. The effect of transaction does not interfere with other transactions that access the same data. Also a transaction happens with all of its effects (In this case you will commit the changes) or it doesn't happen none of its effects (In this case you will rollback the changes).

In the transaction control we generally define code in between a block where we perform mission critical operation. If all operations get completed successfully then that part is committed in the database otherwise what ever modification you might have done during the process is roll backed from the database so that it never affect other user's operations.

In .NET environment we can define transaction boundary by Transaction object.

If you are using SqlClient (namespace System.Data.SqlClient) Managed Provider you can SqlTransaction object.

If you are using Oledb (namespace System.Data.Oledb) Managed Provider you can OledbTransaction object.

If you are using Odbc (namespace Microsoft.Data.Odbc) Managed Provider you can OdbcTransaction object

Let us discuss a simple block of transaction control. In this block I am taking SqlClient Managed Provider

string connectionString = ".........";

SqlConnection myConnection = new SqlConnection(connectionString);

myConnection.Open();

// Start transaction.

SqlTransaction myTransaction = myConnection.BeginTransaction();

// Assign command in the current transaction.

SqlCommand myCommand = new SqlCommand();

myCommand.Transaction = myTransaction;

try

{

.........................

Database operations

........................

myTransaction.Commit();

Console.WriteLine("Records are modified in the database.");

}

catch(Exception e)

{

myTransaction.Rollback();

Console.WriteLine(e.ToString());

Console.WriteLine("Neither record was written to database.");

}

finally

{

myConnection.Close();

}

In Above Block

BeginTransaction method of the Connection object to mark the start of the transaction, which returns a Transaction object.

The newly created transaction object is assigned to CommandObject so that what ever the database operation is performed by that commandObject can be managed by Transaction Object.

If anything gets wrong the Transaction object will raise an Exception otherwise it will run through a normal process.

Call the Commit method of the Transaction object to complete the transaction if everything works fine otherwise call the Rollback method to cancel the transaction.

Concurrency Control

While doing certain modification in the database some time you need to lock the data so that no one can else perform modification in that data. There are two commonly known approaches for locking database they are optimistic locking and pessimistic locking.

Both these approaches are used to maintain concurrency in the database. Pessimistic concurrency locking is done at rows of the data source to prevent users from modifying data in a way that affects other users. In a pessimistic model, when a user performs an action that causes a lock to be applied, no one else can perform action until unless owner releases that lock. But this is not case with optimistic currency model. In optimistic concurrency model user does not lock row while reading it, while user only locks the row while updating changes to the database.

In .NET we use DataSet object for modifying changes in the database. The DataSet object uses optimistic concurrency model with the help of DataAdaptor. The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities such as when you are working in distributed environment.

In real time execution DataSet maintains the versions of data that means if anyone modify any data in the DataSet then it get maintain in the dataset as old version and new version. While updating modified data in the database if any of the concurrency conflict occur it raises Exception, which sets DataRow's HasError Boolean value. This we can easily handle with DataAdaptor event and with our own programming logic.

Here I am giving a simple code sample, which explains you how can you manage, concurrency control in .NET environment

string connectionString = ".......................";

SqlConnection myConnection = new SqlConnection(connectionString);

SqlDataAdapter myAdaptor = new SqlDataAdapter("SELECT Name, City FROM Employee ORDER BY EmpID", myConnection);

// Add the RowUpdated event handler.

myAdaptor.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

DataSet supplierData = new DataSet();

myAdaptor.Fill(supplierData, "Supplier");

// Modify the DataSet contents.

..........................................

.........................................

myAdaptor.Update(supplierData, "Supplier");

foreach (DataRow myRow in supplierData.Tables["Supplier"].Rows)

{

if (myRow.HasErrors)

Console.WriteLine(myRow[0] + "\n" + myRow.RowError);

}

protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)

{

if (args.RecordsAffected == 0)

{

args.Row.RowError = "Optimistic Concurrency Violation Encountered";

args.Status = UpdateStatus.SkipCurrentRow;

}

Explanation of Code:

In this code you have SqlDataAdaptor, which is retrieving supplier record from a database and filling it in a DataSet supplierData. After that you have performed certain modification in that data via DataSet. After modifying data we have used dataAdaptor to update that changes in the database.

So what is new in this code? You might have noticed that in this code we have defined a event handler on dataAdaptor's RowUpdated event. This event will be fired when row is updated in the database and in that event handler mechanism we can define different status to argument so that further action can be take place.

In the main code I have specified code to write all those rows in which error has occurred during modification.

There are different type of status is available for SqlRowUpdatedEventArgs by which you can direct the updating process. Those status are as follows

Status Description

Continue - Continue the update operation.

ErrorsOccurred - Abort the update operation and throw an exception.

SkipCurrentRow - Ignore the current row and continue the update operation.

SkipAllRemainingRows - Abort the update operation but do not throw an exception.

Transactions
So far we haven't used explicit transaction handling and therefore auto-commit has been enabled. This means that all commands have been executed in their own transaction, and that might not be what we want. Fortunately, transaction handling is quite easy and straightforward in ADO.NET using the OdbcTransaction class.

A transaction is started by calling the OdbcConnection.BeginTransaction(isolationlevel) that returns an instance of OdbcTransaction. The different isolation levels are available in System.Data.IsolationLevel. For example, System.Data.IsolationLevel.Serializable can be used.

All SQL statements that we want to be part of the transaction have to be associated with the transaction object. This can be done by passing the transaction through the constructor of the OdbcCommand or by setting the Transaction property. When we are done with the work we simply call OdbcTransaction.Commit() or OdbcTransaction.Rollback() to commit or roll back the transaction.

try
{
OdbcConnection con = new OdbcConnection("...");
con.Open();

//Start the transaction
OdbcTransaction trans =
con.BeginTransaction(System.Data.IsolationLevel.Serializable);
OdbcCommand command = new OdbcCommand(sql, con, trans);

//An alternative but equivalent approach
OdbcCommand command2= new OdbcCommand(sql2, con);
command2.Transaction=trans;

//Execute and commit
command.ExecuteNonQuery();
command2.ExecuteNonQuery();
trans.Commit();
con.Close();
}
catch(OdbcException oe)
{
//Show errors
try
{
trans.Rollback();
}
catch(OdbcException oe2)
{
//Show errors
}
}

As you can see, we simply commit when we are done and if an OdbcException is thrown we do a roll back. Note that the trans.RollBack() method can throw an OdbcEvent itself and is therefore surrounded by its own try-catch.

ADO .NET

2008-11-12T01:04:00.000-08:00

Most applications need data access at one point of time making it a crucial component when working with applications. Data access is making the application interact with a database, where all the data is stored. Different applications have different requirements for database access. VB .NET uses ADO .NET (Active X Data Object) as it's data access and manipulation protocol which also enables us to work with data on the Internet. Let's take a look why ADO .NET came into picture replacing ADO.

Evolution of ADO.NET

The first data access model, DAO (data access model) was created for local databases with the built-in Jet engine which had performance and functionality issues. Next came RDO (Remote Data Object) and ADO (Active Data Object) which were designed for Client Server architectures but soon ADO took over RDO. ADO was a good architecture but as the language changes so is the technology. With ADO, all the data is contained in a recordset object which had problems when implemented on the network and penetrating firewalls. ADO was a connected data access, which means that when a connection to the database is established the connection remains open until the application is closed. Leaving the connection open for the lifetime of the application raises concerns about database security and network traffic. Also, as databases are becoming increasingly important and as they are serving more people, a connected data access model makes us think about its productivity. For example, an application with connected data access may do well when connected to two clients, the same may do poorly when connected to 10 and might be unusable when connected to 100 or more. Also, open database connections use system resources to a maximum extent making the system performance less effective.

Why ADO.NET?

To cope up with some of the problems mentioned above, ADO .NET came into existence. ADO .NET addresses the above mentioned problems by maintaining a disconnected database access model which means, when an application interacts with the database, the connection is opened to serve the request of the application and is closed as soon as the request is completed. Likewise, if a database is Updated, the connection is opened long enough to complete the Update operation and is closed. By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance. Also, ADO .NET when interacting with the database uses XML and converts all the data into XML format for database related operations making them more efficient.

The ADO.NET Data Architecture

Data Access in ADO.NET relies on two components: DataSet and Data Provider.

DataSet

The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft SQL server database, an Oracle database or from a Microsoft Access database.

Data Provider

The Data Provider is responsible for providing and maintaining the connection to the database. A DataProvider is a set of related components that work together to provide data in an efficient and performance driven manner. The .NET Framework currently comes with two DataProviders: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle. Each DataProvider consists of the following component classes:

The Connection object which provides a connection to the database
The Command object which is used to execute a command
The DataReader object which provides a forward-only, read only, connected recordset
The DataAdapter object which populates a disconnected DataSet with data and performs update

Data access with ADO.NET can be summarized as follows:

A connection object establishes the connection for the application with the database. The command object provides direct execution of the command to the database. If the command returns more than a single value, the command object returns a DataReader to provide the data. Alternatively, the DataAdapter can be used to fill the Dataset object. The database can be updated using the command object or the DataAdapter.

Component classes that make up the Data Providers

The Connection Object

The Connection object creates the connection to the database. Microsoft Visual Studio .NET provides two types of Connection classes: the SqlConnection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and the OleDbConnection object, which can provide connections to a wide range of database types like Microsoft Access and Oracle. The Connection object contains all of the information required to open a connection to the database.

The Command Object

The Command object is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are used to execute commands to a database across a data connection. The Command objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:

ExecuteNonQuery: Executes commands that have no return values such as INSERT, UPDATE or DELETE
ExecuteScalar: Returns a single value from a database query
ExecuteReader: Returns a result set by way of a DataReader object

The DataReader Object

The DataReader object provides a forward-only, read-only, connected stream recordset from a database. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of the Command object's ExecuteReader method. The SqlCommand.ExecuteReader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. The DataReader can provide rows of data directly to application logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader.

The DataAdapter Object

The DataAdapter is the class at the core of ADO .NET's disconnected data access. It is essentially the middleman facilitating all communication between the database and a DataSet. The DataAdapter is used either to fill a DataTable or DataSet with data from the database with it's Fill method. After the memory-resident data has been manipulated, the DataAdapter can commit the changes to the database by calling the Update method. The DataAdapter provides four properties that represent database commands:

SelectCommand
InsertCommand
DeleteCommand
UpdateCommand

When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed.

OOP with VB

OOP Basics

Visual Basic was Object-Based, Visual Basic .NET is Object-Oriented, which means that it's a true Object-Oriented Programming Language. Visual Basic .NET supports all the key OOP features like Polymorphism, Inheritance, Abstraction and Encapsulation. It's worth having a brief overview of OOP before starting OOP with VB.

Why Object Oriented approach?

A major factor in the invention of Object-Oriented approach is to remove some of the flaws encountered with the procedural approach. In OOP, data is treated as a critical element and does not allow it to flow freely. It bounds data closely to the functions that operate on it and protects it from accidental modification from outside functions. OOP allows decomposition of a problem into a number of entities called objects and then builds data and functions around these objects. A major advantage of OOP is code reusability.

Some important features of Object Oriented programming are as follows:

Emphasis on data rather than procedure
Programs are divided into Objects
Data is hidden and cannot be accessed by external functions
Objects can communicate with each other through functions
New data and functions can be easily added whenever necessary
Follows bottom-up approach

Concepts of OOP:

Objects
Classes
Data Abstraction and Encapsulation
Inheritance
Polymorphism

Briefly on Concepts:

Objects

Objects are the basic run-time entities in an object-oriented system. Programming problem is analyzed in terms of objects and nature of communication between them. When a program is executed, objects interact with each other by sending messages. Different objects can also interact with each other without knowing the details of their data or code.

Classes

A class is a collection of objects of similar type. Once a class is defined, any number of objects can be created which belong to that class.

Data Abstraction and Encapsulation

Abstraction refers to the act of representing essential features without including the background details or explanations. Classes use the concept of abstraction and are defined as a list of abstract attributes.

Storing data and functions in a single unit (class) is encapsulation. Data cannot be accessible to the outside world and only those functions which are stored in the class can access it.

Inheritance

Inheritance is the process by which objects can acquire the properties of objects of other class. In OOP, inheritance provides reusability, like, adding additional features to an existing class without modifying it. This is achieved by deriving a new class from the existing one. The new class will have combined features of both the classes.

Polymorphism

Polymorphism means the ability to take more than one form. An operation may exhibit different behaviors in different instances. The behavior depends on the data types used in the operation. Polymorphism is extensively used in implementing Inheritance.

Advantages of OOP

Object-Oriented Programming has the following advantages over conventional approaches:

OOP provides a clear modular structure for programs which makes it good for defining abstract datatypes where implementation details are hidden and the unit has a clearly defined interface.
OOP makes it easy to maintain and modify existing code as new objects can be created with small differences to existing ones.
OOP provides a good framework for code libraries where supplied software components can be easily adapted and modified by the programmer. This is particularly useful for developing graphical user interfaces.

XML And ADO.NET

2008-08-28T22:21:00.001-07:00

A powerful feature of ADO.NET is its ability to convert the data stored in a data source in XML and vice versa. In this article we would read records from a data source and display them in a 'DataGrid' control. At the same time we would write the records in an XML file. The contents of the XML file would get displayed in a text box where we can modify it. The modified contents would get added to the data set and would get displayed in the 'DataGrid' control. We are going to use OLEDB for accessing the database.

Let us first understand few basic issues in the ADO.NET technology. The System.Data namespace is the root namespace of ADO.NET API. The primary namespaces of ADO.NET, System.Data.OleDb and System.Data.SqlClient contain classes that enable programs to connect with and modify the datasources. A datasource can be any file containing records such as a '.mdb' file. The namespace OleDb can be used to work with any datasource. On the other hand SqlClient namespace is used for working with MS SQL Server 2000 datasources.

ADO.NET has two major components-DataSet and .NET Data Providers. A .NET Data Provider is used for connecting to a database, executing commands, and retrieving results.

DataSet is used to cache the data read from the datasource in local memory. The data stored in the memory can be modified and written back to the datasource. The System.Data.DataSet class is provided to take care of the DataSets.

Let us move on and write a program using ADO.NET. Create a Windows application and design the form as shown in the following figure.

The dark gray colored control is the 'DataGrid' control. The controls, their Names and handlers are given in the following table.

Control	Name	Handler
DataGrid	dg	-
Write Button	wb	Click
Clear Button	cb	Click
Read Button	rb	Click
TextBox	xmltext	-

First of all we would look at the Click event handler for the 'Write' button.

private void wb_Click ( object sender, System.EventArgs e )
{

String constr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\book.mdb" ;
OleDbConnection con = new OleDbConnection ( constr ) ;
con.Open( ) ;

String comstr = "SELECT Name, Email, Phone FROM addressbook" ;
OleDbCommand com = new OleDbCommand ( comstr, con ) ;

OleDbDataAdapter adapt = new OleDbDataAdapter ( com ) ;
adapt.Fill ( dset, "addressbook" ) ;

dg.SetDataBinding ( dset, "addressbook" ) ;

dset.WriteXml ( "addressbook.xml" ) ;
xmltext.Text = dset.GetXml( ) ;

con.Close( ) ;

}

Interacting with a database using ADO.NET involves connection and command objects. Since we have used OLEDB to access the database, the connection must be established between the database and OLEDB .NET Provider. The class OleDbConnection is used for this job. So, in this method we have firstly created an object of this class passing to it the connection string. The connection string contains the name of the OLEDB .NET Provider and that of the datasource. Our datasource is 'book.mdb' that maintains an address book. This file is already created using Microsoft Access. It contains details like names, e-mail IDs and phone numbers. Only creating object of connection class does not physically open a connection to the database. Calling Open( ) method does. So we have called the Open( ) method in the next statement.

The Command object allows us to execute an SQL statement or a stored procedure in a data source. We have created the command object using the OleDbCommand class and passing to it the command string. The command string contains the SQL statement to select all the records from the datasource.

The command object is used to connect the connection object to a DataAdapter object. A DataAdapter is used to fill data from the database into the DataSet object. To read the records into the DataSet we have used the OleDbDataAdapter.Fill( ) method. To this method we have passed the DataSet object dset and the table name. Add the data member dset of type DataSet to the form class. Initialize the data set object in the constructor as

dset = new DataSet( ) ;

Next, we have called the DataGrid.SetDataBinding( ) method to bind the grid control with the datasource. We have passed the DataSet object as the first parameter to specify whose data the grid control should display. Second parameter is the name of table within the datasource we want to bind to the control. When this function call is executed, the data grid is filled with the records in the DataSet.

Next comes the main part of the program i.e writing records to an XML file. The DataSet.WriteXml( ) method generates the XML representation of the data contained in the DataSet object and writes it to the specified file. We have displayed the same XML data that we have just written to the file in the text box. For this, we have called the DataSet.GetXml( ) method.

The OleDbConnection.Close( ) method closes the connection with the datasource. A part of the XML file is given below.

Meghana
Meg2003@hotmail.com
2242567

….

On clicking the 'Clear' button the grid control should get cleared so that we can display modified contents in it. The code to clear the control is given in the cb_CLick( ) event handler. The handler is given below.

private void cb_Click ( object sender, System.EventArgs e )

{

dset.Clear( ) ;
dg.Update( ) ;

}

Since the grid control is attached to the datasource represented by dset, clearing dset and updating the grid control clears the contents from it.

Now add the following node to the XML data displayed in the text box.

Sanjay
sanju@hotmail.com
227257

Click the 'Read' button. Following handler would get called.

private void rb_Click ( object sender, System.EventArgs e )

{

StreamWriter sw = new StreamWriter ( "addressbook.xml", false ) ;
sw.Write ( xmltext.Text ) ;
sw.Close( ) ;
dset.ReadXml ( "addressbook.xml" ) ;
dg.SetDataBinding ( dset, "addressbook" ) ;

}

Here, we have used the StreamWriter class to write the contents from textbox to the XML file. To read new contents of the file and fill the DataSet with it we have used the ReadXml( ) method. Again to fill the grid control we have called the SetDataBinding( ) method. The result is shown in the following figure.

Note that we must add the following declarations at the beginning of the program.

using System.Data.OleDb ;
using System.IO ;

Bank Example in ADO.NET

2008-08-26T21:22:00.004-07:00

In the following program we have created a WinForm. In this form we plan to show all the contents of a table. Here too we have used the OLE DB .NET Data Provider. We plan to add functionality to add, delete and modify the table.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb ;
namespace AdoProgram1
{

class bank
{

OleDbDataAdapter dest ;
DataSet ds ;
DataTable dt ;

public bank ( )
{

string str =" Data Source = c:\\bank.mdb; Provider = Microsoft.Jet.OLEDB.4.0";
string cmd = "SELECT accno, name, balance from account" ;

dest= new OleDbDataAdapter( cmd, str ) ;
OleDbCommandBuilder obd = new OleDbCommandBuilder( dest ) ;
ds = new DataSet ( ) ;
dest.Fill ( ds, "account" ) ;
dt = ds.Tables["account"] ;

}

public void addnewrec ( int id, String name, int bal )
{

DataRow r = getrowbyid ( id ) ;

if ( r == null )
{

DataRow nrow = dt.NewRow ( ) ;
nrow[0] = id ;
nrow[1] = name ;
nrow[2] = bal ;
dt.Rows.Add ( nrow ) ;
dest.Update ( ds, "account" ) ;

}
else
{

MessageBox.Show (" Record Already Exists") ;

}

public void deleterec ( int id )
{

string exp = " accno = " + id ;
DataRow[] r = dt.Select ( exp ) ;
r[0].Delete ( ) ;
dest.Update ( ds, "account" ) ;

}

public void updaterec ( int id, String name, int bal )
{

string exp = " accno = " + id ;
DataRow[] r = dt.Select ( exp ) ;
r[0][1] = name ;
r[0][2] = bal ;
dest.Update ( ds, "account" ) ;

}

public DataRow getrowbyid ( int id )
{

try
{

string exp = " accno =" + id ;
DataRow[] r = dt.Select ( exp ) ;
return r[0] ;

}

catch ( Exception e )
{

return null ;

}

public DataSet dset
{

get
{

return ds ;

}

public class Form1 : System.Windows.Forms.Form
{

private System.Windows.Forms.TextBox name;
private System.Windows.Forms.TextBox bal;
private System.Windows.Forms.TextBox acc;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Button searchbut;
private bank b = new bank ( ) ;
private int m_oper ;
private System.Windows.Forms.Button commitbut;
private System.Windows.Forms.DataGrid dgrid;
private System.Windows.Forms.GroupBox groupBox2;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.Button addbut;
private System.Windows.Forms.Button updatebut;
private System.Windows.Forms.Button delbut;
private System.ComponentModel.Container components = null;

public Form1( )
{

m_oper = 0 ;
InitializeComponent( ) ;
dgrid.SetDataBinding ( b.dset, "account" ) ;

}

protected override void Dispose( bool disposing )
{

if( disposing )
{

if ( components != null )
{

components.Dispose ( ) ;

}

base.Dispose( disposing ) ;

}

#region Windows Form Designer generated code
private void InitializeComponent ( )
{

//Wizard generated code found in Zip file

}

#endregion

[STAThread]
static void Main ( )
{

Application.Run ( new Form1 ( ) ) ;

}

private void searchbut_Click ( object sender, System.EventArgs e)

{

DataRow r = b.getrowbyid ( Int32.Parse ( acc.Text ) ) ;
if ( r != null )
{

name.Text = r[1].ToString() ;
bal.Text = r[2].ToString ( ) ;
name.Enabled = true ;
bal.Enabled = true ;

}

else

MessageBox.Show ( "Record not found" ) ;

}

private void addbut_Click ( object sender, System.EventArgs e )
{

acc.Enabled = true ;
name.Enabled = true ;
bal.Enabled = true ;
commitbut.Enabled = true ;
searchbut.Enabled = false ;
m_oper = 1 ;

}

private void updatebut_Click ( object sender, System.EventArgs e )
{

acc.Enabled = true ;
name.Enabled = false ;
bal.Enabled = false ;
commitbut.Enabled = true ;
searchbut.Enabled = true ;
m_oper = 2 ;

}

private void delbut_Click ( object sender, System.EventArgs e )
{

acc.Enabled = true ;
name.Enabled = false ;
bal.Enabled = false ;
commitbut.Enabled = true ;
searchbut.Enabled = true ;
m_oper = 3 ;

}

private void commitbut_Click ( object sender, System.EventArgs e )
{

int a = Int32.Parse ( acc.Text ) ;
string n = name.Text ;
int balance = Int32.Parse ( bal.Text ) ;
switch ( m_oper )
{

case 1:

b.addnewrec ( a, n, balance ) ;
break ;

case 2:

b.updaterec ( a, n, balance ) ;
break ;

case 3:

b.deleterec ( a ) ;
break ;

}

acc.Text = "" ;
name.Text = "" ;
bal.Text = "" ;
commitbut.Enabled = false ;
searchbut.Enabled = false ;
acc.Enabled = false ;
name.Enabled = false ;
bal.Enabled = false ;

}

In this program we have added a control called the Data Grid. We have used the Data Grid control to view the Data. This control is wrapped in a class called the DataGrid class derived from the Control class. We get the following output when we run this program.

To add a new DataRow, we must press the Add button, fill in the entries as shown and press Commit. The values will get added. To delete a row we must type in the accno and press Search. All the values would be shown in the corresponding textboxes. After pressing Commit, the row will get deleted. Same works for updating a row. We must type in the accno and press Search. After doing this we should press Commit for the changes to be made in the data.

If we press the small, depressed arrow on the side of accno field, the rows get sorted according to descending values of accno. On pressing it again they get sorted according to ascending values of accno.

We have wrapped all functionality of adding, deleting and updating the database in a class called bank.

In the constructor of the bank class, we initialized the connection and command string passed it to the constructor of the DataAdapter class. Next we filled the DataSet with account and extracted the table in object dt of the DataTable class.

We have written different functions for adding, deleting and updating rows. We plan to add rows in the table only if the accno is unique. This means every account holder should have just one row. So no two rows with same accno are added. To check this we have written a new function getrowbyid( ). In this method we have used the Select( ) method which gets an array of all DataRow objects that match the filter criteria. The filter criterion in our case is the accno. If such a row is found then it is returned. If such a row is not found an exception will be thrown. If an exception is thrown, we catch it and return just a null. In the addnewrec( ) method if a null is returned by the getrowbyid( ) method it means that no such row is already present. If its so we add a new row. The logic is same as the previous program. If not we just flash a message.

For deletion we have again used the Select( ) method. This method will return the collection of rows satisfying the selection criterion. We will definitely have just one row in the collection because in our table the accno is not repeated. (Such a field, which has unique value and no two rows have the same value, is called the primary key of the table). The Delete( ) method just deletes the specified row. After deleting it from the DataSet we call he Update( ) method to reconcile the changes.

Updating is done by initializing the corresponding field with new values and just calling the Update( ) method. A property dset is also written which gets the DataSet ds.

As shown in the form there are three buttons provided for the three operations viz:- addition of a row, deletion of a row and updating a row, one for searching the table and another for committing the operation. The whole logic behind the working of the form is that we have created a private data member of the bank class in the form class. We have also added an int m_oper, which will hold an int denoting which operation has to be performed. The default operation is 0 i.e. nothing. Events are added for all the buttons.

When the "Add" button is clicked all the texboxes are enabled. The "Commit" button is enabled and the "Search" button is disabled. This is so because we don't have to search the table to add a row. Finally and most importantly m_oper is set to value 1.
When the "Update" button is clicked the Acc no textbox is enabled. The Search button is enabled. This is because we have to search for the record and then update it. On pressing "Search" the search_click( ) event gets called. In this event we first get the row to be updated using the following statement:

DataRow r = b.getrowbyid ( Int32.Parse ( acc.Text ) ) ;

Int32.Parse( ) method converts the text in the acc textbox to an int. This int is passed to the getrowbyid( ) method which returns the specified selected row. After getting the row we set the textboxes with the values from the row and enable them.

name.Text = r[1].ToString ( ) ;
bal.Text = r[2].ToString ( ) ;

We can then change the text in the textboxes to the new values. This is all done in the search_click( ) event. After the control returns back to updatebut_Click( ) event m_oper is set to 2.

When the "Delete" button is clicked a similar thing happens and m_oper is set to 3. We do not change the text in the textboxes and hence the values, because we plan to delete the row and not update it.

After all this is done we press the "Commit" button. This calls the corresponding event. In this event we kept a switch on m_oper and called the corresponding methods of the bank class.

Using DataSet & DataTable

2008-08-26T21:22:00.003-07:00

A DataSet is used for the disconnected approach. We connect to the database, fill in the DataSet with the data and go offline. The DataSet is designed as an offline container of data. Actually filling of data in a DataSet need not be necessarily through a database, it could be through any file also or straight away from a device. To fill in a DataSet we need a DataAdapter. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. Take a look at the following program. We have used the OLE DB .NET Data Provider. We have used Microsoft Access to create the account table.

class myclass
{

static void Main( string[] args)
{

string str="Provider=Microsoft.Jet.OLEDB.4.0 ;Data
Source=c:\\bank.mdb";
string cmd = "SELECT accno, name, balance from account" ;
OleDbDataAdapter dest= new OleDbDataAdapter( cmd, str ) ;
OleDbCommandBuilder mybuilder= new
OleDbCommandBuilder (dest) ;
DataSet ds = new DataSet ( ) ;
dest.Fill ( ds, "account" ) ;
DataTable dt = ds.Tables["account"] ;
DataRow nrow = ds.Tables["account"].NewRow ( ) ;
nrow[0] = 6;
nrow[1] = "Rashi";
nrow[2] =76000 ;
ds.Tables["account"].Rows.Add ( nrow ) ;
dest.Update ( ds, "account" ) ;

}

After initializing the command and connection strings, we created the DataAdapter object. This object is passed to the constructor of the OleDbCommandBuilder class. This is because the DataAdapter class does not automatically generate the SQL statements to make changes in the database. The OleDbCommandBuilder class does so. Now we need to populate a DataSet with the DataAdapter. The Fill( ) method does the filling of DataSet. The statement

ds.Tables["account"]

returns the table "account" from the DataSet. Tables is a property belonging to the DataSet class. It gets the collection of Tables contained in the DataSet. "account" is an indexer on the collection of tables. We collected this "account" table in a new DataTable object: - dt. We then created a new row in the account table. Next we initialized it with values. Now we need to add it to the table. This is achieved through the following

ds.Tables["account"].Rows.Add ( nrow ) ;

Rows is a property belonging to the DataTable class. It gets the collection of rows contained in the table i.e. it returns a DataRowCollection object. This class has a method Add( ) in it. The method Add( ) adds a DataRow to the DataRowCollection. Now to reconcile the changes in the database we use the Update( ) method. This method calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable.

SqlCommand Class

2008-08-26T21:22:00.001-07:00

ExecuteReader( ), ExecuteNonQuery( ) and ExecuteScalar( ) are three important methods in the Command class.

ExecuteReader( ):-This method will execute the command and return an SqlDataReader object.

ExecuteNonQuery( ):- Executes the command and returns the number of records affected.

ExecuteScalar( ): - Executes the query and returns a single result such as count on records in a given table or max value of some field etc.

ExecuteReader( )

Below program Shows how to use ExecuteReader( ) Method:

using System;
using System.Data;
using System.Data.SqlClient;
class myclass
{

static void Main( string[ ] args)
{

string str= "Database = bank ; server = kicit ; Trusted_Connection =true";
string cmd = "SELECT accno, name, balance from account" ;
SqlConnection con = new SqlConnection ( str ) ;
SqlCommand com = new SqlCommand ( cmd, con ) ;
con.Open();

SqlDataReader r = com.ExecuteReader ( ) ;

while ( r.Read ( ) )

Console.WriteLine ( r[0] + " " + r[1] + " " + r[2] ) ;

con.Close ( ) ;

}

The output is :

1 Rahul 200000
2 Subash 300000
3 Anita 100000
4 Preeti 120000
5 Kapil 8000

First we need to get connected to the database. To do so, we have to write a connection string. The connection string includes database name, server, database provider, user name, password and whether it�s a trusted connection or not. In our program the database name is bank and server name is kicit. We have initialized Trusted Connection to true. This means even if we don�t specify the username and password its fine. If the value is set to false we have to specify a username and password. This connection string is stored in str.

The command string is stored in cmd. cmd contains the query to be executed. The query we have used here is �SELECT accno, name, balance from account�. This is an Sql query, which selects the specified fields:- accno, name and balance from the �account� table.

To make a connection, we pass the connection string to the SqlConnection constructor. To the SqlCommand constructor we pass the connection as well as the command string. The Open( ) method opens a database connection with the property settings specified by the connection string. The SqlDataReader class reads a stream of rows from an SQL Server database. To create an SqlDataReader class we have to call the ExecuteReader( ) method belonging to the SqlCommand class. This method will execute the command and return an SqlDataReader object. The Read( ) method iterates through the records advancing the SqlDataReader by one record every time.

ExecuteNonQuery( )

Now lets have a look at a program using the ExecteNonQuery( ) method.

using System;
using System.Data;
using System.Data.SqlClient ;
class myclass
{

static void Main( string[] args)
{

string str = " Database = bank ; server = kicit ;
Trusted_Connection =true";
string cmd = "Insert into account values ( 6, 'Aakash', 25000 ) ";
SqlConnection con = new SqlConnection ( str ) ;
SqlCommand com = new SqlCommand ( cmd, con ) ;
con.Open ( ) ;
com.ExecuteNonQuery ( ) ;
con.Close ( ) ;

}

The "Insert into account values (6, 'Aakash', 25000)" query inserts a new row in the table. After opening the connection, the query is executed and the connection is closed.

ExecuteScalar( )

Now lets look at a program a program using the ExecuteScalar( ) method:

using System;
using System.Data;
using System.Data.SqlClient;
class myclass
{

static void Main( string[] args)
{

string str = " Database = bank ; server = kicit ;
Trusted_Connection=true";
string cmd = "SELECT count(*) from account" ;
SqlConnection con = new SqlConnection ( str ) ;
con.Open ( ) ;
SqlCommand com = new SqlCommand ( cmd, con ) ;
Object o = com.ExecuteScalar( ) ;
Console.WriteLine ( o ) ;
con.Close ( ) ;

}

The output here is: - 6. The "SELECT count(*) from account" query counts the no of rows in our database.

Data Providers

2008-08-26T21:21:00.001-07:00

MSDE is the Microsoft Database Engine, and is a version of SQL Server; this is shipped along with VS.NET and does not need explicit installation.

SQL Data Providers

To write programs for accessing, manipulating, or updating database, we have to first create a database. To create a database, open the server explorer window. If its not visible press Ctrl+Alt+S.

In the �Servers� tree expand the �SQL servers� node. You will find your computer name added there. Right click on it and select �New Database�. Name the new database as �bank� as shown in the following dialog box:

We have checked the �Use Windows NT Integrated Security� radio button. This program will hence work only when we log on with our username and password. Press OK and a new database called bank will appear in the tree. Now we need to create a table in our database called account. To create a new table, expand the bank node and right click on �Tables�, select �New Table� from the menu. A new blank table would appear. Now we need to fill in the entries and specify the attributes of the table. This is how we want our table to be:

We want three attributes in the table viz:- accno (int), name (varchar) and balance(float). Save the table and name it as: - account. Close this window. Expand the table�s tree and you will find that account has been added. Double click on this and start filling in the entries as shown:

Now lets write a program to access these values and print them out Here the code for the program:

using System;
using System.Data;
using System.Data.SqlClient;
class myclass
{

static void Main( string[ ] args)
{

SqlDataReader r = com.ExecuteReader ( ) ;

while ( r.Read ( ) )

Console.WriteLine ( r[0] + " " + r[1] + " " + r[2] ) ;

con.Close ( ) ;

}

The output is :

1 Rahul 200000
2 Subash 300000
3 Anita 100000
4 Preeti 120000
5 Kapil 8000

Explanation: -

r.GetString(0)

to get the accno field of the row referenced by the SqlDataReader. There is another way of doing it: -r[�name�]. In the end the connection is closed using the Close( ) method.

OLEDB Data Providers

For OLE DB .NET Data Provider we will have to create our tables in Microsoft Access. These tables should have the same values and fields. After creating such a table in Microsoft Access, save it as bank.mdb in the C directory. The program will look like this:

using System;
using System.Data;
using System.Data.OleDb;
class myclass
{

static void Main( string[ ] args)
{

string str="Provider =Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\bank.mdb";string cmd = "SELECT accno, name, balance from account" ;
OleDbConnection con = new OleDbConnection ( str ) ;
OleDbCommand com = new OleDbCommand ( cmd, con ) ;
con.Open();

OleDbDataReader r = com.ExecuteReader ( ) ;

while ( r.Read ( ) )

Console.WriteLine ( r[0] + " " + r[1] + " " + r[2] ) ;

con.Close ( ) ;

}

The output here remains the same. We have only changed the connection string and the names of classes, just replacing �Sql� everywhere with �OleDb�.

There are three types of OleDb .NET Data Providers viz: - SQLOLEDB, MSDAORA and Microsoft.Jet.OLEDB.4.0. We have used the last one. This is specified in the connection string used.

Introduction to ADO.NET

2008-08-26T21:20:00.000-07:00

ADO.NET deals with accessing Databases. It�s a new technology that runs within the .NET environment. It uses the concept of managed code. ADO.NET provides access to data sources such as Microsoft SQL Server, OLE DB and XML. Applications connect to these data sources to access, manipulate or update data.

The traditional difficulties regarding database is that it relies on connection-based two-tier model. Instead programmers today wish to make n-tier applications using a disconnected approach.

A two-tier model means there is a server and a client in the application. The server accesses the client. An n-tier applications means there will be more than a server or a client viz: - one or more databases (data-tier), business tier, and presentation-tier.
In early days the client had to be connected with the server and remain connected till the whole procedure or transaction was completed. This was called the connected approach. In such an approach lots of bandwidth was required because if 100 clients were to use a server, each would need a connection. Time, resources and bandwidth became major constraints on such architectures.

In ADO 2.0 disconnected data approach was used. In this approach ADO used Record Sets. All the contents of the server were copied in the Record Set. This allowed the client to get disconnected from the server, work on the Record Set and copy the changes back to the data store again. This approach did not catch on due to security problems. This approach used COM marshalling to transmit a disconnected data set, it supported only those data types defined by the COM standards and hence required type conversions.

ADO.NET is designed from ground up to help solve these problems. Here transmission of data is in XML format. XML format places no restriction on the data types and requires no type conversion.

ADO.NET Architecture

The ADO.NET architecture consists of two main components:- the DataSet and the NET Data Providers.

DataSet

ADO.NET uses the disconnected approach using the DataSet. The DataSet is explicitly designed for data access independent of any data source. It can be used for data sources, XML data, or used to manage data local to the application. The DataSet acts as a container. All the data coming from the database is dumped in the DataSet for the local machine to use and manipulate. The database is then later updated back with the DataSet. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint and relation information about the data in the DataTable objects.

A DataSet can be thought of as a container of data. The DataSet consists of DataTables. DataTables are similar to tables or relations in DBMS (Data Base Management Systems). A table consists of zero or more columns. A table is also generically called as a schema. DataTables contain DataRows and DataColumns. Rows indicate one full entry or a tuple and columns indicate one property or attribute of the table.

The following figure will serve as an example

.NET Data Providers

There are two .NET data providers available: - The SQL Server .NET Data Provider and the OLE DB .NET Data Provider. Depending on the design and data source for our application we can use any one of these two. The SQL Server .NET Data Provider uses its own protocol to communicate with the SQL Server. The OLE DB .NET Data Provider uses native OLE DB through COM interoperability to enable data access. To use the SQL Server .NET Data Provider, you must have access to Microsoft SQL Server 7.0 or later. SQL Server .NET Data Provider classes are located in the System.Data.SqlClient namespace. To use the OLE DB .NET Data Provider, you must use an OLE DB provider (we have used Microsoft Access). OLE DB .NET Data Provider classes are located in the System.Data.OleDb namespace. To use the OLE DB .NET Data Provider, you will have to include the System.Data.OleDb namespace in your applications.

These .NET providers include the Connection object, the Command Object, the DataReader and the DataAdapter in their corresponding namespaces. For e.g. the Connection class in the Sql Server .NET Provider is written as SqlConnection and its equivalent in the OLE DB Service Provider is written as OleDbConnection. In the same way for all the four classes the corresponding names are prefaced with Sql or OleDb accordingly.

The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. A command in its simplest form is nothing but an SQL query. The DataReader provides a stream of read-only and forward-only data from the data source. A read-only data means that it cannot be changed using the DataReader and forward-only means we cannot traverse backwards. Finally, the DataAdapter provides the bridge between the DataSet object and the data source.

You should see the next article to get this clear. We have written two programs using both the .NET Data Providers. Both programs do the same thing.