Whether you develop for .NET not, you have to admit that Microsoft have come along way in the last 3 to 5 years. Back in the late nineties Bill Gates had a vision of using the Internet to facilitate distributed computing, and today web services and the .NET framework have made this vision an exciting reality.
In terms of the .NET framework, the way we access our data has also changed. Pretty much everything in .NET uses XML as its underlying data structure, and even records from database are represented internally as well formed XML, which is then converted to the data type required by the client application.
Since "classic" ASP's humble beginnings, ADO data access classes and methods have been re-written. When Microsoft decided to embark on its multi billion dollar .NET adventure, they also built a new version of ActiveX Data Objects (ADO) called ADO.NET.
Today we're going to take a look at some of the fundamental differences that exist between ADO and ADO.NET in a practical sense. We will be using ASP, ASP.NET and C# to both instantiate ADO and ADO.NET classes, seeing how the calling conventions to each of these classes differ.
To test the samples explained in this article, you should have the Microsoft .NET framework installed on your machine, which you can download here. We won't be using Visual Studio.NET to create web forms, so notepad or any other plain text editor is fine.
In ADO 2.x we used one common set of classes to perform database connections, retrieve records, update data, execute stored procedures, etc. They were the ADO command, connection, error, field, parameter, property, record, recordset and stream classes.
In ADO.NET we have two sets of classes that we can use to access our data, depending on how that data is stored. Each of these classes is stored in a separate namespaces that clearly identifies them. To access data stored in an SQL Server database, we would use the classes that exist in the System.Data.SqlClient namespace. To access data stored in any other OLEDB accessible repository (such as Excel, MySQL, Access, and even SQL Server) we use the classes available under the System.Data.OleDb namespace.
[Note] If you're wondering why you can use both SQL and OleDb providers to access an SQL Server database, it's because the classes under the SqlClient namespace have been optimised to do so, where as the classed under the OleDb namespace are more generic, and therefore their performance isn't as good when working with SQL server. [End Note]
Here's a list of the namespaces that we use to access our data with ADO.NET:
ADO.Net also defines several shared classes that are common to both the SqlClient and OleDb namespaces (i.e. can be used when we're working with either SQL server or any other OleDb provider). In ADO 2.x we used the recordset, command and stream classes to access data, but with ADO.NET we now have access to several new data access classes. These classes can be used to store, access, manipulate and relate data from one source to another. We will take a look at these later.
Let's now look at some examples of how we can access and manipulate our data using ASP/ADO and ASP.NET/ADO.NET. In the examples that follow I will be working with SQL server only.
With ADO 2.x we instantiate a connection object from which we then call its open method, passing in a connection string. In ADO.NET the same principles apply, instead we instantiate an SqlConnection object, which exists under the System.Data.SqlClient namespace.
Here's how we connect to an SQL Server database on the local machine using classic ASP and ADO 2.6:
Here's an example that does the same thing, only this time we're using ASP.NET and ADO.NET:
In our ADO.NET example above, we've used the SqlClient namespace. We could just have easily used the OleDb namespace and OleDb connection class to connect to our SQL Server database, because the SQLOLEDB provide is OleDb compatible.
The great thing about ADO.NET is that in 99% of the cases, you can simply copy and paste your old ADO connection strings into your .NET applications and they should work fine.
As with ADO 2.x, it's extremely easy to execute a command against a database using ADO.NET. If we wanted to update a record in the authors' table of the pubs database using ADO 2.6, we would do something like this:
In ADO.NET we instantiate the SqlCommand class, which resides under the System.Data.SqlClient namespace. We pass the SQL command we want to execute along with a connection object to its constructor, like this:
Just like with ADO 2.x, we can specify the type of command that we're executing. We specify the value using the CommandType enumeration. The three possible choices are:
In our example we're executing a direct query command, so we specify the Text type, like this:
One of the many things that are different with ADO.NET is that we can specify how we want our query executed. Both the SqlCommand and OleDbCommand classes expose the following methods:
Because our command updates a record and doesn't return anything, we use the ExecuteNonQuery method, like this:
In ADO 2.6, we could run a select * query and use the "fields" collection of the returned recordset to get the number returned:
In ADO.NET, we would do exactly the same thing using the ExecuteScalar method to return a single value, like this:
Sometimes it's good to just return an entire table. In ASP using ADO 2.6, we can accomplish this by simply specifying the table name for the command object and changing the command type to adCmdTable, or 2:
If we want to retrieve a table in the same way using ADO.NET, then we have to use a data source that supports a managed OLEDB provider. The SqlCommand object doesn't support the CommandType.TableDirect command type, so we have to use the System.Data.OleDb namespace and classes. Remember that SQL Server supports the SQLOLEDB provider, so we add this to our connection string as well. Here's how we would do it in ASP.NET:
As you can see in the example above, we use the ExecuteReader method to return an OleDbDataReader object, which can be used to display the contents of the table.
This article was contributed by the team at http://www.devarticles.com.devArticles provides ASP, PHP and .NET articles, tutorials, reviews, interviews and FREE eBooks. If you're after some serious programming tutorials then...Visit http://www.devarticles.com now...OR, for free eBooksvisit http://www.devarticles.com/ebooks.php