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...?