ASP.Net n-tier Design Thoughts...

[plenderj]

Righty.

I am working on a personal project at the moment, so have total free range on all design issues.
Basically, a telecomms operator will connect to my service and pass some data by HTTP POST.
I then connect back once I have generated a response and I sent that back by HTTP POST.
When they send me the HTTP POST data I have to reply with either "Success" for "Failure" - depending on whether my service has accepted the message okay.

Here comes the interesting parts. How to get the data through to my SQL Server.
So. My idea was that I could have an interface page - SMSInterface.aspx
That accepts the HTTP POST data.

Then I pass the data to a method of a web service SMSHandler.asmx and wait for a response - True or False.
Here is where I have few options, and I'm not quite sure.
What I'm doing is passing the data, in SMSHandler.asmx into the SQL Server, and if there are no errors then return True.
If there were any errors then return False.

That boolean true or false is returned to the SMSInterface.aspx file, and then it sends Success or Failure to the client browser.
This is the SMSInterface.aspx file :

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim SMSHandler As New x.localhost.SMSHandlerService()
        With Request            
            If SMSHandler.SubmitSMS(Request.Form("Channel"), Request.Form("Reference"), Request.Form("Trigger"), Request.Form("Shortcode"), Request.Form("Phone"), Request.Form("Content"), Request.Form("PayloadType"), Request.Form("DateReceived"), Request.Form("CampaignID")) Then
                strPageData = "Success"
            Else
                strPageData = "Fail"
            End If
        End With
    End Sub

This is the SMSHandler Service :

<WebMethod()> Public Function SubmitSMS(ByVal strChannel As String, ByVal strReference As String, ByVal strTrigger As String, ByVal strShortcode As String, ByVal strPhone As String, ByVal strContent As String, ByVal strPayloadType As String, ByVal strDateReceived As String, ByVal strCampaignID As String) As Boolean
        Dim blnRetVal As Boolean = True, strSQLStatement As String, mySQLConnection As SqlConnection
        Dim mySQLAdapter As SqlDataAdapter, mySQLDataSet As DataSet
        Dim mySQLCommand As SqlCommand
 
        Try
            mySQLConnection = New SqlConnection(ConnectionString)
            mySQLConnection.Open()
        Catch ex As Exception
            blnRetVal = False
            DespatchError(ex)
            GoTo eof
        End Try
 
        strSQLStatement = "INSERT INTO tblIncomingMessages VALUES (" & Apostrophise(strChannel, strReference, strTrigger, strShortcode, strPhone, strContent, strPayloadType, strDateReceived, strCampaignID) & ")"
 
        Try
            mySQLCommand = New SqlCommand(strSQLStatement, mySQLConnection)
            mySQLCommand.ExecuteNonQuery()
        Catch ex As Exception
            blnRetVal = False
            DespatchError(ex)            
            GoTo eof
        End Try
 
eof:
        Return blnRetVal
    End Function
 
    Private Function Apostrophise(ByVal ParamArray strVars As String()) As String
        Dim i As Long, strRetVal As String
        For i = 0 To strVars.Length - 1
            strRetVal &= "'" & Replace(strVars(i), "'", "^") & "', "
        Next
        Return Left(strRetVal, Len(strRetVal) - 2)
    End Function
 
    Private Sub DespatchError(ByVal ex As Exception)
        WriteToFile(vbCrLf & "***" & Now & "***" & vbCrLf & vbCrLf, "error.log")
        WriteToFile(ex.ToString, "error.log")
    End Sub
 
    Private Sub WriteToFile(ByVal strData As String, ByVal strFilePath As String)
        Dim sw As New StreamWriter(AppLocalPath & strFilePath, True)
        sw.WriteLine(strData)
        sw.Close()
    End Sub

So its not very difficult - nothing complicated there.
What I'm thinking though, is that, in an n-tier design, shouldn't the database code be removed from any business logic?
So perhaps I should change SMSHandler.asmx such that there's no DB code in it, as above, and instead instantiate a reference to another web service.
And that web service would purely handle database requests.

Any thoughts?

I was also thinking that if this project takes off, and the database is put onto a different server - then this decision will may make an impact as follows:
I've got my web server, and I've got my DB Server.
If the database access code is on the web server, then its ado.net database access data going over the network.
But if the database access code is on the DB Server, and is instantiated as a method to a web service from the web server, then its SOAP/XML going over the network.
So which would be faster, database data streaming across the network, or SOAP/XML going across?


I know in my heart that this is all nearly irrelevant, but I just wanted to know what any of you thought about it from a design point of view...?

[hellswraith]

My first thought is you are over designing this system. When I have more time to give it more thought, I will try to post.

[DNA7433]

I structured an app of mine similiar to that. It sounded like a really good design to me. What I did was have my ASP.NET page contact a webservice for everything, and the webservice called stored procedures in the database. My company wanted a way to input timesheet hours over the web, but I also wanted to write a standard EXE for my computer that would be more automatic. On the web you would enter your project and the number of hours you worked each day. But on my desktop you would select the project and then click start and stop throughout the day as you worked, and at the end of the day it would contact the web service and update my hours. Only problem was halfway through development I found out they had someone else build half an app in lotus notes among other lesser languages so I'm waiting to see what managment decides. Screwy.

[techgnome]

Using the DB access as a Web Service maynot be such a hot idea- it leaves it open to the world. BUT, you can create it as a Com/MTS object that then sits inside the firewall (and subsequently is protected from outside intervention). Then again, maybe it's just me.

TG

[DNA7433]

the webservice uses windows security, supposedly. I haven't tested it but I don't think you're allowed access unless you have a windows account?

[techgnome]

That's what it runs as.... but to send it info to process..... don't know.

TG

[plenderj]

My web services will be running on a server not accessible from the outside world - so DB access etc. should be safe

[shunt]

I'm with HellsWraith! Way overkill for what you are doing. But, if the design is all you are looking at...

Sifting through all the code and other blerb, I see the real question being

shouldn't the database code be removed from any business logic?

I would say the answer is yes. Your business logic is one logical layer while your database code (persistence) is another logical layer. But in your eg. I dont see any business logic to speak of. What you are doing is more an import function. What you should be doing is some serious format checking when you receive the data. This is where XML is the way you should be going! You would check the input against an XSL to make sure the data is in a valid format. Much better than writing your own format checking!

Something to remember. In B2B integration, its all about format and data validity!

NOOOO! SQL concatenation! NOOOO! You are leaving yourself wide open for some SQL injection attack! Parameterise! Parameterise! Parameterise!

[shunt]

Sorry,

check the input against an XSL

in the previous post should be "check the input against an XSD "