Windows application needs to connect to web service to pull data

[Proctor]

Hello: I have an existing windows .net application that has an access database as the backend.

I want to place the access database on a web server and it was suggested that I create a web service in order to communicate with the database.

I've been looking at some tutorials but am still confused about the basic config needed. Here's some questions I have:

1) Does the web service need to be created inside my current project by right clicking on the solution and choosing to add a new web site...and then selecting web service?

2) If it's set up like this, what files do I place onto the web server?

3) Where does the database reside? Do I need to place the database inside the web server that I create?

4) My current project has existing sql that gets data and I'm uncertain how to re-write in order to retreive the data. e.g. which part goes into the client and which part goes to web service pages? here's an example of a procedure that currently exists...I'm getting data to populate a dropdown:

Public Sub PopulateCustomers()
        Dim myDB As New OleDb.OleDbConnection(sConnectionString)
        Dim myDS As New Data.DataSet
        Dim myDA As New OleDb.OleDbDataAdapter
        myDA = New OleDb.OleDbDataAdapter("select custid, name from tblCustomer order by name", myDB)
        Try
            myDA.Fill(myDS, "tblCustomer")

            With cboCustomer
                .DataSource = myDS.Tables("tblCustomer")
                .DisplayMember = "Name"
                .ValueMember = "CustID"
                If bGotCustId = True Then 'if user just did a save and they are passing back the custid, need to set the selected value to that
                    .SelectedValue = txtCustID.Text.ToString()
                End If
                If bSetCustomer = True Then 'is the user returning from another screen? we need to return to the same customer. 
                    If nCustomerId > 0 Then
                        .SelectedValue = nCustomerId
                        bSetCustomer = False 'reset
                    End If
                End If
            End With
            myDB.Close()
           
            cboCustomer.SelectedItem = 1
            txtCustID.Text = cboCustomer.SelectedValue
        Catch
            MsgBox("An error occurred while in: PopulateCustomers")
        End Try

    End Sub

I appreciate any help you can give. As you can see, I'm really lost right now.
Thanks,
Proctor

[gep13]

Hello,

A Web Service is simply a web application. You will need to create a new project, of type web service. Once this is completed, you will be able to compile the application, and then publish the resulting files, likely a dll and an asmx file into IIS, which will host the application for you.

From there, you would create a Web Reference to the Web Service, either directly under IIS, or as a Project Reference from the solution that you have both projects in.

What database are you using? SQL Server? MySQL? It will have to reside somewhere that the web service will be able to access it. That doesn't mean that it has to reside on the same machine, although, that would be the easiest way.

The majority of the code would live on the Web Service. However, you would need to add a return type on the on the PopulateCustomers method, to return something that you would be able to use on the client. Some types are not easily transferred across a web service call, the types need to be serializable.

Gary

Hope that helps!!

Gary

[Proctor]

gep13: thanks for your help. Ok, I created a web service and I added it's reference into my window app. I added a simple button to the "hello world" to verify it was working and it is!!

now, I need to figure out the database part of it. if you want me to repost to the database forum, let me know. but it's still related to the web service so hoping it's still ok to be here.

To begin, i placed my connection string in the web service: (for now, till i figure out how to retreive data, i'm keeping my connection local):

Public sConnectionString As String = "File name=C:\Program Files\windowsfolder\test.udl"

Here's the code I placed in the web app that makes a call to go get me some data:

 Public Sub Customer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim tblLookupCustomers As System.Data.DataTable

        Dim oService As New SloanLEDEstimator_WebService.Service
        tblLookupCustomers = oService.GetData()

        If tblLookupCustomers.Rows.Count > 0 Then
            Debug.Print("got some records")
        End If
End Sub

Here's the GetData() function that resides in the web service:

  <WebMethod()> _
  Public Function GetData()
        Dim SQL As String = ""
        'get inches first
        SQL = "select color from tblLookupColor order by color"
        Dim myDB As New OleDb.OleDbConnection(sConnectionString)
        Dim myDA As New OleDb.OleDbDataAdapter(SQL, myDB)
        Dim myDS As New Data.DataSet
        myDA.Fill(myDS, "tblTest")
        Dim tblDataTable As System.Data.DataTable
        tblDataTable = myDS.Tables("tblTest")
        Return tblDataTable
    End Function

When I run it, I invoke the web service, it runs through all the code ok, but after it hits the last line (End Function), it displays this error message:


System.InvalidOperationException: There was an error generating the XML document. ---> System.InvalidOperationException: The type System.Data.DataTable may not be used in this context. To use System.Data.DataTable as a parameter, return type, or member of a class or struct, the parameter, return type, or member must be declared as type System.Data.DataTable (it cannot be object). Objects of type System.Data.DataTable may not be used in un-typed collections, such as ArrayLists.

Can you please help me to figure out the best way to return the dataset?
I've got a ton of datasets to return in my app and so not sure what the quickest and best approach is.

Thanks again,
Proctor

[Proctor]

Hello: I was able to fix the error above -needed to add:

Public Function GetData() As System.Data.DataTable

At any rate, I'm now returning data so I'm going to try to connect to it via the internet as my next step.

Do you think I'm on the right track? I counted the number of subs and functions in my app that make trips to the db and it's over 50 so creating a web service will be a larger project than i anticipated - provided that I need to change up each one of my calls to the db.

Thanks,
Proctor

[gep13]

Yes, you are definitely on the right track

Although, what you said earlier about keeping the connection "local", that isn't right. The connection string information, as well as the connection itself, should be in the Web Service. It should hold all the information that is needed to connect to the database, and to return data.

The benefit that you get from taking this approach, i.e. abstracting the database calls into the web service, is that you can then get this information from any application that requires it. For instance, let's say you create a web site to display some data, just point it at the web service, and you can get all the same information. Additionally, let's say you wanted to create a mobile application, or a WPF application, all the information is in one place, it is just the UI that is changing.

Gary

[Proctor]

gep13: Thanks again for your reply. I placed the database in my webservice under the app_data folder. I created the connection string in the webservice and so i think i'm on my way.

I really do like the idea of having the database in one centralized location. right now, I'm having to collect everyone's data and merge it and - if I make any changes to the database structure, it's a real pain. this will be so much nicer. One thing I'm hoping though is that there won't be any performance issues as far as slowness from placing the database in my web service on the web server. do you have any thoughts on this?

I'm going to try to test this out today.
Thanks again - i really appreciate the direction.

Proctor

[gep13]

Hello,

If your users are used to having a database locally on their machine, then you are going to see a slower performance than they might be used to, but it really comes down to how much data we are talking about bringing back.

So, how much are we talking about?

There are techniques you can use, such as asynchronous calls, and caching, that can help to make the performance a bit better.

Gary

[Proctor]

There's a few screens that populate drop downs and those pull somewhere between 3 and 400 records.

The majority of the time, they'll be inserting or updating records...so I would think it might be ok.

thanks again,
Proctor

[gep13]

Yip, that sounds fine to me.

You can always do some performance testing once everything is up and running to see how things are performing.

Gary