Home arrow ADO.NET arrow Page 5 - A Practical Comparison of ADO and ADO.NET
ADO.NET

A Practical Comparison of ADO and ADO.NET


Since "classic" ASP's humble beginnings, ADO data access classes and methods have been re-written to form ADO.NET. In this article Joe looks at the differences (in terms of classes, methods, and calling conventions) that exist between ADO and ADO.NET. He provides several practical examples that show us how to evolve from using ADO with ASP to using ADO.NET with C# and ASP.NET.

Author Info:
By: Joe O'Donnell
Rating: 4 stars4 stars4 stars4 stars4 stars / 72
March 01, 2002
TABLE OF CONTENTS:
  1. · A Practical Comparison of ADO and ADO.NET
  2. · Old versus new: ADO versus ASP.NET
  3. · Connecting to a database
  4. · Working with recordsets/rowsets
  5. · Calling stored procedures
  6. · Retrieving records as XML
  7. · Conclusion

print this article
SEARCH DEVARTICLES

A Practical Comparison of ADO and ADO.NET - Calling stored procedures
(Page 5 of 7 )

In my opinion, stored procedures are what makes SQL Server such a robust and scalable database management system. I spent three years working with ADO 2.x and stored procedures, and if you've worked with stored procedures before then I'm sure you'll agree with me when I say that they're the most efficient way to manipulate data using a complex batch of TSQL code.

In just a minute we're going to compare how we call stored procedures using both ADO/ASP and ADO.NET/ASP.NET, but first, run the following TSQL code using query analyzer to create a new stored procedure in the pubs database:

USE PUBS

GO

CREATE PROC sp_AddJob

(

@jobDesc VARCHAR(50),

@minLevel TINYINT,

@maxLevel TINYINT

)

AS

-- Add a record to the jobs table

-- and return no data

SET NOCOUNT ON

INSERT INTO jobs(job_desc, min_lvl, max_lvl)

VALUES(@jobDesc, @minLevel, @maxLevel)


The stored procedure we've just created is called sp_AddJob, it accepts three parameters, and will insert a new record into the jobs table of the pubs database.

Using an ADO command object and parameters, here's how we would call it using ASP:

<%



const adVarChar = 200

const adTinyInt = 16

const adParamInput = 1

dim objConn

dim objComm

dim paramDesc

dim paramMinLvl

dim paramMaxLvl

set objConn = Server.CreateObject("ADODB.Connection")

set objComm = Server.CreateObject("ADODB.Command")

objConn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=Pubs; UId=sa; Pwd="

objComm.ActiveConnection = objConn

objComm.CommandType = 4 'adCmdStoredProc

objComm.CommandText = "sp_AddJob"

set paramDesc = objComm.CreateParameter("jobDesc", adVarChar, adParamInput, 50, "Test Job")

set paramMinLvl = objComm.CreateParameter("minLevel", adTinyInt, adParamInput, , 10)

set paramMaxLvl = objComm.CreateParameter("maxLevel", adTinyInt, adParamInput, , 100)

objComm.Parameters.Append(paramDesc)

objComm.Parameters.Append(paramMinLvl)

objComm.Parameters.Append(paramMaxLvl)

objComm.Execute

%>


As you can see, I've created three ADO parameter objects using the command objects CreateParameter() function. All are set as input parameters, and the Execute() function actually runs the stored procedure.

Similarly, to achieve the same functionality using ADO.NET we need to instantiate an SqlCommand or OleDbCommand class, set its CommandType property to CommandType.StoredProcedure, and create three parameters, like this:

<%@ import namespace="System.Data" %>

<%@ import namespace="System.Data.SqlClient" %>

<script language="c#" runat="server">

public void Page_Load(object sender, EventArgs e)

{

SqlConnection objConn = new SqlConnection("Server=(local); Database=Pubs; UId=sa; Pwd=");

objConn.Open();

SqlCommand objComm = new SqlCommand("sp_AddJob", objConn);

objComm.CommandType = CommandType.StoredProcedure;

objComm.Parameters.Add(new SqlParameter("@jobDesc", SqlDbType.VarChar, 50));

objComm.Parameters.Add(new SqlParameter("@minLevel", SqlDbType.TinyInt, 0));

objComm.Parameters.Add(new SqlParameter("@maxLevel", SqlDbType.TinyInt, 0));

objComm.Parameters[0].Value = "Test Job";

objComm.Parameters[1].Value = 10;

objComm.Parameters[2].Value = 100;

objComm.ExecuteNonQuery();

objComm.UpdatedRowSource = UpdateRowSource.None;

}

</script>


In the example above, we create a new SqlCommand object, passing in "sp_AddJob" (which is the name of the stored procedure we want to execute) as the first argument to its constructor.

We call the "Add" function of our SqlCommand's Parameters collection to add three new parameters. Once the parameters have been added, we still have to set their values. The Parameters collection of our SqlCommand object exposes a public indexer which we use the set the values of the parameters explicitly. Instead of referencing the parameters by their index, we could specify use their name, such as:

objComm.Parameters["@jobDesc"].Value = "Test Job";

Also notice that each parameter must be prepended with the @ symbol. The last line of our Page_Load() function is:

objComm.UpdatedRowSource = UpdateRowSource.None;

The UpdatedRowSource property of our SqlCommand object is used to specify how query command results are applied to the row(s) being updated. It has five possible values which are Both, FirstReturnedRecord, None, OutputParameters, and value__. We specify none, because our query returns no results and therefore no data updates are required.

It's great being able to execute a stored procedure with no return values, but what about when we want to return output parameters, or even entire rowsets? Run the following code in query analyzer. It creates a new stored procedure in the pubs table called sp_GetBookCosts, which returns the sum of all price fields in the titles table that have a specific royalty field value:

USE PUBS

GO

CREATE PROC sp_GetBookCosts

(

@royalty INT,

@priceTotal INT OUTPUT

)

AS

-- Get the sum of all books

-- whose royalty field matches @royalty

SET NOCOUNT ON

SET @priceTotal = (SELECT SUM(price) FROM titles WHERE royalty = @royalty)


To get all books from the titles table whose royalty field is equal to 10, we would use this ASP/ADO code:

objComm.CommandType = 4 'adCmdStoredProc

objComm.CommandText = "sp_GetBookCosts"

set paramRoyalty = objComm.CreateParameter("royalty", adInteger, adParamInput, , 10)

set paramPrice = objComm.CreateParameter("priceTotal", adInteger, adParamOutput)

objComm.Parameters.Append(paramRoyalty)

objComm.Parameters.Append(paramPrice)

objComm.Execute

totalPrice = objComm.Parameters("priceTotal").value

Response.Write "Total is " & totalPrice


Just like in the ASP/ADO example above, we must specify our parameters as output parameters in ADO.NET. We must also set the UpdatedRowSource value of our SqlCommand object to UpdateRowSource.OutputParameters, which tells ADO.NET to provide us with a value for our output parameter:

SqlCommand objComm = new SqlCommand("sp_GetBookCosts", objConn);

objComm.CommandType = CommandType.StoredProcedure;

objComm.Parameters.Add(new SqlParameter("@royalty", SqlDbType.Int));

objComm.Parameters.Add(new SqlParameter("@priceTotal", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "priceTotal", DataRowVersion.Default, null));

objComm.Parameters["@royalty"].Value = 10;

objComm.ExecuteNonQuery();

objComm.UpdatedRowSource = UpdateRowSource.OutputParameters;

int totalPrice = (int)objComm.Parameters["@priceTotal"].Value;

Response.Write("Total is " + totalPrice);


The only thing new about our example above is that we've called a different constructor to add a new parameter to our SqlCommand object, setting the parameter direction to ParameterDirection.Output.
blog comments powered by Disqus
ADO.NET ARTICLES

- Datasets in Microsoft.Net
- Latest Developments in the .Net World
- Introduction to .NET
- Automatic Generation of Single Table SQL Sta...
- Data Access in .NET using C#: Part 1
- All You Need To Know About ADO.NET: Part 2/2
- All You Need To Know About ADO.NET: Part 1/2
- Easing Transition From ASP and ADO to ASP.NE...
- A Practical Comparison of ADO and ADO.NET

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials