asp consuming a web service, what do do with recordset object ?

Question

Currently I run an classic (old) ASP webpage with recordset object used directly in bad old spagethi code fasion.

I'm thinking of implementing a data layer in asp.net as web serivce to improve manageability. This is also a first step towards upgrading the website to asp.net. The site itself remains ASP for the moment...

Can anybody recommend a good way of replacing the recordset object type with a web service compatible type (like an array or something)? What do I replace below with?:

set objRS = oConn.execute(SQL)
while not objRS.eof
   ...
   name = Cstr(objRS(1))
   ...
wend

and also mutliple recordsets can be replaced with? I'm talking :

 set objRS = objRs.nextRecordset

Anybody went through this and can recommend?

@AdditionalInfo - you asked for it :-)

Let me start at the beginning. Existing Situation is: I have an old ASP website with classical hierachical content (header, section, subsection, content) pulled out of database via stored procedures and content pages are in database also (a link to html file).

Now bad thing is, ASP code everywhere spread over many .asp files all doing their own database connections, reading, writing (u have to register for content). Recently we had problems with SQL injection attacks so I was called to fix it.

I could go change all the .asp pages to prevent sql injection but that would be madness. So I thought build a data layer - all pages using this layer to access database. Once place to fix and update db access code.

Coming to that decision I thought asp.net upgrade isn'f far away, why not start using asp.net for the data layer? This way it can be re-used when upgrading the site.

That brings me to the questions above!

Answer 1

First my favorite advice of this week: do not treat your Web Service like it if was a local object or you are going to pay a very hefty performance price. Essentially, don't do things like this in your web application:

MyDataWebService ws = new MyDataWebService();
foreach(DataItem item in myData)
{
    ws.Insert(item);
}

You should always prefer to minimize calls to your Web Service (and SQL):

MyDataWebService ws = new MyDataWebService();
ws.Insert(myData); // Let the web service process the whole set at once.

Now, as far as the data type to use for your web service calls, you basically have two choices:

• DataSet
• Everything else (Array)

Most collections returned from a web service (like a List<MyData>) actually convert to an Array during the Web Service invocation. Remember that Web Services don't return objects (data + behavior) but just data structures (or a sequence of). Therefore, there is little distinction between a List and an Array.

DataSets are more complex classes; they use their own custom serializer and pretty much get fully recreated in the calling application. There is a cost in performance to be paid for using DataSets like that, so I don't usually recommend it for most scenarios. Using arrays to pass data back and forth tends to be more efficient, and quite frankly it's easier to do.

Your case is a bit different; because you are converting an existing site that already uses ADO, an ADO.NET DataSet might be your best updgrade path. ADO.NET and ADO are similar enough that a straight update might be easier that way. It kind of depends how your web site is built.

For the last part of your question, DataSets do support multiple recordsets similar to ADO's Recordset. They are called DataTables. Every DataSet has at least one DataTable and you can read them in any order.

Good luck.

Answer 2

I'd suggest using the XmlHttp class in your ASP code.

Assuming you have an ASMX web service similar to this, in MyService.asmx:

[WebMethod]
public string HelloWorld()
{
  return "Hello World";
}

You could call it in ASP something like this:

Dim xhr

Set xhr = server.CreateObject("MSXML2.XMLHTTP")

xhr.Open "POST", "/MyService.asmx/HelloWorld", false
xhr.SetRequestHeader "content-type", "application/x-www-form-urlencoded"
xhr.Send

Response.Write(xhr.ResponseText)

ResponseText would be an XML response of:

<string>Hello World</string>

Assuming your service returned a collection of data, you could iterate over it using XPath or any other XML processing technique/library.

Googling around about MSXML2 will probably answer any specific questions you have, since it's specific to ASP classic.

Answer 3

Instead of thinking in layers, why not try taking vertical slices through the application and converting those to .net. That way you will get entire features coded in .net instead of disjoint parts. What is the business value in replacing perfectly working code without improving the user experience or adding features?

You might also consider the trade-off of performance you are going to give up with a Web Service over direct ado calls. Web Services are a good solution to the problem of multiple disjoint applications/teams accessing a common schema; they do not make a single isolated application more maintainable, only slower and more complex.

Answer 4

If you wanted to stick with Classic ASP then I would suggest creating a Database handling object via ASP Classes then just use that object to do your recordset creations. This would centralize your database handling code and make it so that you only have to handle SQL Injection attacks in a single location.

A simple example.

Class clsDatabase

    Private Sub Class_Initialize()
     If Session("Debug") Then Response.Write "Database Initialized<br />"
    End Sub

    Private Sub Class_Terminate()
     If Session("Debug") Then Response.Write "Database Terminated<br />"
    End Sub

    Public Function Run(SQL)
     Set RS = CreateObject("ADODB.Recordset")
     RS.CursorLocation = adUseClient
     RS.Open SQLValidate(SQL), Application("Data"), adOpenKeyset, adLockReadOnly, adCmdText
     Set Run = RS
        Set RS = nothing
    End Function

    Public Function SQLValidate(SQL)
     SQLValidate = SQL
     SQLValidate = Replace(SQLValidate, "--", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, ";", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, "SP_", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, "@@", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, " DECLARE", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, "EXEC", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, " DROP", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, " CREATE", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, " GRANT", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, " XP_", "", 1, -1, 1)
     SQLValidate = Replace(SQLValidate, "CHAR(124)", "", 1, -1, 1)
    End Function
End Class

Then to use this you would change your calls to:

Set oData = new clsDatabase
Set Recordset = oData.Run("SELECT field FROM table WHERE something = another")
Set oData = nothing

Of course you can expand the basic class to handle parametrized stored procedures or what not and more validations etc.

Answer 5

Another alternative is to use COM Interop to create an assembly in .NET that is callable from classic ASP.

To create a COM Interop assembly from Visual Studio (e.g. Microsoft Visual C# 2005 Express Edition):

• Create a new Class Library project

• Open the project properties

  • Under Application select Assembly Information... and enable "Make assembly COM-Visible"
  • Under Signing enable Sign the assembly and create or select an existing strong name key file

• Write and build the library

  • COM Interop classes must have a default constructor and only non-static classes and methods are published

• Copy the .dll to the desired folder/machine

• Register the .dll for COM using RegAsm

For example (adjust as necessary):

"C:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe" "C:\path\to\assembly.dll" /tlb /codebase

• Call the assembly from ASP

For example (adjust as necessary):

Dim obj, returnValue
Set obj = Server.CreateObject("MyProject.MyClass")
returnValue = obj.DoSomething(param1, param2)

Note:

• the assembly must be re-registered via RegAsm when it's updated

See also:

• Exposing .NET Framework Components to COM (MSDN)
• CodeProject: Call a .NET component from an ASP Page

Answer 6

Sql injection should be handled by using parametrized sql queries. Not only will this eliminate the security risk but it will significantly speed up your database performance because it will be able to reuse an execution plan instead of recalcing it every time. The suggestion to handle it through string replacements is foolish. VB is terrible at handling strings and those "replace" statements will be extremely costly in performance and memory (also, you actually only need to handle the ' character anyway)

Moving code to .net doesn't make it better. Having db code in your pages isn't bad; especially if id you are talking about a small site with only a couple devs. Thousands of sites use that technique to process bazillions of dollars in transactions. Now, unparameterized dynamic sql is bad and you should work to eliminate that, but that doesn't require a rewrite of the app or .net to do it. I'm always curious why people see .net as a defacto improvement to their app. Most of the bad code and bad habits that existed in the COM model just propagate forward during a conversion.

You either need to make a commitment to creating a truly cohesive, minimally coupled, OO design; or just keep what you have going because it isn't all that bad.