[AccessD] Data interface The best way

Charlotte, does that mean you are using dynamic SQL in the BE? I tend to see
a lot more of that now that I am starting to use .Net more and more.
Couldn't the xml file hold a list of sproc names and you could just execute
a sproc instead? I was always instructed not to create SQL Strings in the
front end as passing them to the back end was a security risk. This is of
course, unless you are dealing with the possibility of SQL injection on the
back end and not allowing certain characters and/or reserved words.

Jim, are you asking how to make a connection into the SQL BE via code? I had
a bit of a time understanding how to do this (coming form an Access
background). I created a test page with has a cascading combo box for
automobile makes and models. Here is how I assign the rowsource to the
"Model" combo box after selecting a "Make" (or manufacturer):

    Private Sub cboMake_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cboMake.SelectedIndexChanged
        If Me.SqlConnection1.State <> ConnectionState.Open Then
Me.SqlConnection1.Open()
        Dim A As System.Data.SqlClient.SqlDataReader
        Dim B As SqlClient.SqlCommand
        B = New SqlClient.SqlCommand
        B.Connection = Me.SqlConnection1
        B.CommandText = "stpSelectModel " & Me.cboMake.SelectedItem.Value

        A = B.ExecuteReader()
        Me.cboModel.DataSource = A
        Me.cboModel.DataTextField = "Model"
        Me.cboModel.DataValueField = "ModelID"
        Me.cboModel.DataBind()

        Me.SqlConnection1.Close()
    End Sub

HTH
David

-----Original Message-----
From: Charlotte Foust

We use typed datasets and build a data "entity" in the solution that
inherits that typed dataset and handles validations, update calls,
fills, etc.  We build dataproviders for each typed
dataset/entity/interface and let it handle creating the connections and
command strings, building the data adapter and passing a filled "entity"
back to the calling routine.  We actually store the SQL for these
commands in xml and look up the one we need, passing the parameters in
through the dataprovider.  It works quite well.

<snip>

Charlotte Foust


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Thursday, October 13, 2005 10:09 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Data interface The best way


Hi All:

I am current working building a .Net solution for a client. ASP.Net 2,
ADO.Net and MS SQL 200x.

Traditionally, when designing the system I have passed all the Selects,
Inserts, Updates and Deletes requests through to the server via ADO
commands using parameter lists and then to MS SQL side Stored Procedures
to manage any final validation and data work.

I am not sure of the best way to manage the data flow with this new
architecture (i.e. Datasets etc.). Any suggestions and experiences would
be very helpful.

TIA
Jim