Best practices: Ajax and server side scripting with stored procedures

28/10/2024 softwareengineering

I need to rebuild an old huge website and probably to port everyting to ASP.NET and jQuery and I would like to ask for some suggestion and tips. Actually the website uses:

Ajax (client site with prototype.js)
ASP (vb script server side)
SQL Server 2005
IIS 7 as web server

This website uses hundred of stored procedures and the requests are made by an ajax call and only 1 ASP page that contain an huge select case

Shortly an example:

JAVASCRIPT + PROTOTYPE:

var data = {
    action:     'NEWS',
    callback:   'doNews',
    param1:     $('text_example').value,
    ......:     ..........};
AjaxGet(data); // perform a call using another function + prototype

SERVER SIDE ASP:

<% ......
select case request("Action")
case "NEWS"
    With cmmDB
        .ActiveConnection = Conn
        .CommandText = "sp_NEWS_TO_CALL_for_example"
        .CommandType = adCmdStoredProc 

        Set par0DB = .CreateParameter("Param1", adVarchar, adParamInput,6)
        Set par1DB = .CreateParameter(".....", adInteger, adParamInput)
        ' ........ ' can be more parameters

        .Parameters.Append par0DB
        .Parameters.Append par1DB

        par0DB.Value = request("Param1")
        par1DB.Value = request(".....")

        set rs=cmmDB.execute

        RecodsetToJSON rs, jsa ' create JSON response using a sub
    End With
.... %>

So as you can see I have an ASP page that has a lot of CASE and this page answers to all the ajax request in the site.

My question are:

Instead of having many CASES is it possible to create dynamic vb code that parses the ajax request and creates dynamically the call to the desired SP (also implementing the parameters passed by JS)?
What is the best approach to handle situations like this, by using the advantages of .Net + protoype or jQuery?
How the big sites handle situation like this? Do they do it by creating 1 page for request?

Thanks in advance for suggestion, direction and tips.

Your best bet is to totally forget the stored procedures, and build a standards complient .net app. I say it that way because I am not familiar with .NET, but it is an excellent web framework and is designed around what is loosely known as a MVC structure.

In MVC you separate your logic in three distinct components. The view which handles display like HTML/CSS. The AJAX blurs this a bit, but AJAX requests themselves should also follow an MVC structure. The controller is where requests are sent. A controller should handle a distinct request (i.e. page request). This is where your current site is using a single point of entry which is very complex. The MVC framework will have a router component that routes the requests to the respective controller. The last part is the model or database. This will be classes that map to your database. The queries will be either embedded as methods in the model or in the controller or a mix of both (rails uses a blend of both).

You would only put something in a stored procedure if it is especially complex (which can often still be done in the model), or it needs to do special handling in the db like triggers etc. I have written some extremely complex web apps and have used stored procedures in only one of them, and in that case it was to implement some legacy data security method.

You have a lot of work ahead of you since the database was probably not designed around this style. Don’t be afraid to build views to fix some cases where the database doesn’t map cleanly to your models. If you can pull it off redesigning the database may be needed with a migration over to the new model. That will depend on your cutover plan and if you have other systems that access the database. The reverse view could help you with that as well. In that case you build your new tables and make a view for the legacy apps.

Sorry I was so long winded.

Instead to have many CASES is possible to create dynamic vb code that parse the ajax request and create dynamically the call to the desired SP (also implementing the parameters passed by JS)?

Yes, the “action” parameter should just contain the name of the SP. Then you can just delete all hundreds of switch cases and if.

Or what is the best approach to handle situation like this, by using the advantages of .Net + protoype or jQuery ?

You don’t even need all those libraries, I’ve implemented the exact same thing with just a few lines of JS and a single ashx generic handler. Best approach depends on your exact needs.

How the big sites handle situation like this ? by creating 1 page for request ?

Each “big sites” have implemented their solution around their specific needs.

Overall, I think this question is too generic to have an “answer”, but here is my “opinion” as a first ditch effort which doesn’t reflect even a great approach.

I see this post is a bit old so hopefully by now, the OP has obtained a bit more ASP.NET as well as Object-Oriented Programming experience. With that, I would recommend forgetting about the stored procedures for a bit and focus on adding OOP principals to the web application initially. With the assumption that the use of ajax is necessary, I’d say the existing approach can be salvaged and to officially promote the use of the parsing ASP page to be an “engine” implemented as a service (think web/WCF service). Perhaps the service is responsible for parsing or it delegates parsing to a stored procedure or to a .NET object; both have their pros and cons: updating the parsing logic in a stored procedure would not require recompiling/deploying the application and could be delegated to a DBA (who could further optimize the routine), however, storing the logic in code would better suit versioning requirements of the application as the parser and code that uses the parser are likely stored together.

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website Kho Theme wordpress Kho Theme WP Theme WP

LEAVE A COMMENT Hủy