ADO Vs ADO.NET Web Service

Introduction

In this example I'd like to demonstrate one of the most interesting features of the .NET runtime - the concept of Webservices. My second goal is to demonstrate using the "old" COM ADO 2.6 vs. the ADO.NET. It must be said, that the .NET environment DOES make it possible to use COM components and COM-based technologies, but it's not so straigthforward as using native .NET classes.

This example is built in the beta-1 of the Visual Studio.NET. You need a Windows 2000 IIS/PWS Webserver and SQL Server 7.0/2000 with the well-known Northwind Database in order to run this example.

1. First we create a C# Webservice project and call it "tws" as test-web-service for example. The IDE creates for us a set of files, but we are interested only on the *.asmx and *.cs files. The *.asmx file contains only one line of code:
   
   

   <%@ WebService Language="c#" Codebehind="tws.cs" Class= "PROINFO.WebService.Data.WS" %>
   
   Notice, that the Class= "..." must be identical with the namespace+classname used in your "codebehind" C# code file, otherwise your webservice project won't run. There is a bug in the IDE, because when you rename your namespace or class, this change isn't reflected in the *.asmx file automatically.

2. Further we will work with the C# code file. The IDE created for us all the necessary framework stuff, all we have to do is to write some useful Webmethods - this is a smart name for the class methods we can call through the HTTP/SOAP.
   
3. Now let's try to write a Webmethod, that will return the Customers table from the Northwind SQL Server database. In the first method GetCustomersOld() we will use the good old ADO 2.6, so we must reference the ActiveX Data Object COM component (project - add reference - COM). The code for creating an ADO connection and a recordset is well-known, and there is no explanation necessary. But the problem is, that as a Webmethod returns everything in XML format, we can't use ADO Recordset as a return type. Instead we must use a structure sCustomers and fill it with all the records found in the table - that isn't very practical. Finally we put the [Webmethod] attribute in front of the method and that's all - the webmethod is perfect and waiting for the first call.
   
4. In the second method GetCustomersNew() we decide to make the same using ADO.NET instead of ADO 2.6. Not only is this the native .NET technology, but we can simply return the data in ADO.NET DataSet, and the framework transforms it to a nice XML data stream. As you can see, is this method much more simple and there is no need for a COM-to.-NET wrapper.
   
5. Now we can test the code by running the project. The system will show us a default webbrowser interface for testing the webmethods, and the only we have to do is to push the appropriate "Invoke" button. When everything OK, we will see the result in a separate window as XML data. You can compare the XML format returning from both the methods and the performance.

Source Code

After you create your C# Webservice project, you can paste this code into the C# code file. Don't forget to change the server name, userid and password in the connection parameters of both webmethods.

namespace PROINFO.WebService.Data
{
using System;
using System.Collections;
using System.Configuration;
using System.ComponentModel;
using System.Data;
using System.Data.SQL;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
/// <summary>
/// Summary description for WS.
/// </summary>
public class WS : System.Web.Services.WebService
{
public WS()
{
//CODEGEN: This call is required by the ASP+ Web Services Designer
InitializeComponent();
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
public override void Dispose()
{
}
// Here starts the example code
public struct sCustomers
{
public String sCustomerID;
public String sCompanyName;
public String sContactName;
public String sContactTitle;
public String sAddress;
public String sCity;
public String sRegion;
public String sPostalCode;
public String sCountry;
public String sPhone;
public String sFax;
}
[WebMethod(Description="ADO 2.6 WebMethod Example")]
public sCustomers[] GetCustomersOld()
{
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
String strSQL;
int intRC;
int intCnt;
strSQL = "SELECT * FROM Customers";
cn.Open("Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog=Northwind;", "sa", null, 0);
rs.Open(strSQL, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
intRC = rs.RecordCount;
if (intRC < 1)
{
return null;
}
sCustomers[] c = new sCustomers[intRC];
rs.MoveFirst();
intCnt = 0;
while (!rs.EOF)
{
c[intCnt].sCustomerID = rs.Fields["CustomerID"].Value.ToString();
c[intCnt].sCompanyName = rs.Fields["CompanyName"].Value.ToString();
c[intCnt].sContactName = rs.Fields["ContactName"].Value.ToString();
c[intCnt].sContactTitle = rs.Fields["ContactTitle"].Value.ToString();
c[intCnt].sAddress = rs.Fields["Address"].Value.ToString();
c[intCnt].sCity = rs.Fields["City"].Value.ToString();
c[intCnt].sRegion = rs.Fields["Region"].Value.ToString();
c[intCnt].sPostalCode = rs.Fields["PostalCode"].Value.ToString();
c[intCnt].sCountry = rs.Fields["Country"].Value.ToString();
c[intCnt].sPhone = rs.Fields["Phone"].Value.ToString();
c[intCnt].sFax = rs.Fields["Fax"].Value.ToString();
rs.MoveNext();
intCnt++;
}
return c;
}
[WebMethod(Description="ADO.NET WebMethod Example")]
public DataSet GetCustomersNew()
{
DataSet ds = new DataSet();
SQLConnection cn = new SQLConnection("localhost", "sa", "", "Northwind");
cn.Open();
SQLDataSetCommand cm = new SQLDataSetCommand("SELECT * FROM Customers", cn);
cm.FillDataSet(ds, "Customers");
return ds;
}
}
}