[RESOLVED] Using WCF Service to connect between Windows Forms application and SQL Database

[EntityX]

gep13 before I saw your last post in my other thread I already thought it was time to start a new thread; besides the other one was getting too bulky. I went ahead and marked that other thread resolved so no one wastes any time looking at it. So now I know I need to use a WCF Service (or some kind of service) to connect between my Windows Forms application and my SQL database.

So I need to learn about WCF Services. I was looking at this MSDN article

How to: Define a Windows Communication Foundation Service Contract

I've never worked with a Console Application before. I'm using Visual Basic not C#. Here's steps 5 to 7 in the article. Step 7 I'm not sure about.

Attachment 82707

Step 7. Add an Imports statement for the System.ServiceModel namespace.

Are they talking about adding it to Module1? So that Module1 becomes like the example at the bottom of the article? If they are talking about adding that to Module1 then that's easy for me to do but if not I don't see how to add that Imports statement for the System.ServiceModel namespace. To make Module1 like the example below would be simple enough.

This is at the bottom of the MSDN article :

Example
The following code example shows a basic interface that defines a service contract.

Imports System
' Step 5: Add the Imports statement for the System.ServiceModel namespace
Imports System.ServiceModel

Namespace Microsoft.ServiceModel.Samples
    ' Step 6: Define a service contract.
    <ServiceContract(Namespace:="http://Microsoft.ServiceModel.Samples")> _
    Public Interface ICalculator
        <OperationContract()> _
    Function Add(ByVal n1 As Double, ByVal n2 As Double) As Double
        <OperationContract()> _
        Function Subtract(ByVal n1 As Double, ByVal n2 As Double) As Double
        <OperationContract()> _
        Function Multiply(ByVal n1 As Double, ByVal n2 As Double) As Double
        <OperationContract()> _
        Function Divide(ByVal n1 As Double, ByVal n2 As Double) As Double
    End Interface
End Namespace

[gep13]

Good stuff! I only slight change that I am going to make is that I am going to move this thread to the WCF Forum.

Thanks

Gary

[EntityX]

I tried just making Module1 like the example but it doesn't allow it. You have to leave the Sub Main() in there or else you get a build error.


No build errors if I do it like what you see below and it runs but it stops running after a second. I also did what the correction at the bottome of the article says to do.

In step 5, after changing Main's default namespace, your application will lose its startup routine. You can allow the debugger's error correction to add it for you, or you can go to the Application Tab on the Project Properties Page and change the startup object to include the namespace you just added.

Imports System
' Step 5: Add the Imports statement for the System.ServiceModel namespace
Imports System.ServiceModel


Namespace Microsoft.ServiceModel.Samples
    <ServiceContract(Namespace:="http://Microsoft.ServiceModel.Samples")> _
    Public Interface ICalculator
        <OperationContract()> _
        Function Add(ByVal n1 As Double, ByVal n2 As Double) As Double
        <OperationContract()> _
        Function Subtract(ByVal n1 As Double, ByVal n2 As Double) As Double
        <OperationContract()> _
        Function Multiply(ByVal n1 As Double, ByVal n2 As Double) As Double
        <OperationContract()> _
        Function Divide(ByVal n1 As Double, ByVal n2 As Double) As Double
    End Interface

    Module Module1
        Sub Main()

        End Sub
    End Module
End Namespace

[EntityX]

Let's take a different approach here. What if I just go ahead and add a WCF Service to my Website. That's easy for me to do. But how do I set it up so that it will connect with the database on my website. Here's what Solution Explorer looks like after I added the WCF Service. I notice that it has WCFServiceForDatabase.cs and IWCFServiceForDatabase.cs just above it in the App Code folder. Then there's WCFServiceForDatabase.svc near the bottom. Is there too much to explain? I'm using Visual Web Developer in Visual Studiio 2010 Pro.

The Web Service is working fine for downloading my application but that's a separate issue that I'm not talking about here. Perhaps one Web Service or WCF Service can do multi duty tasks.

[gep13]

But how do I set it up so that it will connect with the database on my website.

The short answer is that you DON'T!

You set up the database connection, using the ConnectionString in the .config file to point at a local database. Then, when you upload your service onto your hosting provider, you edit the .config file to point to your actual database.

Gary

[EntityX]

I'm finally getting back to working on this. I have to reacquaint myself with this stuff.

Then, when you upload your service onto your hosting provider, you edit the .config file to point to your actual database.

In Visual Studio I used Copy Website to transfer the WCFService to my "Remote Web Site". That will be sufficient right? Now here's what the .config file for my website looks like :

<?xml version="1.0"?>
<configuration>
	<connectionStrings>
		<add name="Database1ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
	</connectionStrings>
	<system.webServer>
		<directoryBrowse enabled="true"/>
		<defaultDocument>
			<files>
				<clear/>
				<add value="Default.htm"/>
				<add value="Default.asp"/>
				<add value="index.htm"/>
				<add value="Default.aspx"/>
				<add value="index.php"/>
				<add value="index.html"/>
				<add value="index.pl"/>
				<add value="default.html"/>
			</files>
		</defaultDocument>
	</system.webServer>
	<system.web>
		<compilation debug="true" targetFramework="4.0">
		</compilation>
		<pages controlRenderingCompatibilityVersion="3.5" clientIDMode="AutoID"/></system.web>
	<system.serviceModel>
		<behaviors>
			<serviceBehaviors>
				<behavior name="">
					<serviceMetadata httpGetEnabled="true"/>
					<serviceDebug includeExceptionDetailInFaults="false"/>
				</behavior>
			</serviceBehaviors>
		</behaviors>
		<serviceHostingEnvironment multipleSiteBindingsEnabled="true"/>
	</system.serviceModel>
	<appSettings>
	<add key="localhost.WebServiceForDownloads" value="http://localhost:49593/TaylorEntertainmentSite/WebServiceForDownloads.asmx"/>
	</appSettings></configuration>

Near the top you see Database1ConnectionString which is for the Database I want to go through the WCF Service. At the bottom you see WebServiceForDownloads which is not what I'm using with Database1. What would I add in there so Database1 uses the WCFService you see listed in Solution Explorer in post #4. I called it WCFServiceForDatabase. When I added it, it also added IWCFServiceForDatabase.

[gep13]

Hello,

Ok, I am slightly confused by what you are asking, but let me see if I can add something.

You have your database, and you are wanting to restrict access to the that database by providing a WCF Service which clients can interact with. As a result, your WCF Service needs to know the location of the Database, which in your case above is a SQLExpress database, which will have to change when you upload it onto your server, as we have discussed elsewhere), and then your clients will need the service address of the WCF Service in order to make calls against that service.

Gary

[EntityX]

Do I need to use SQL Server Management Studio to create a .bak or some kind of file for the WCF Service? We discussed in another thread about creating a .bak file for my database which has already been done and I had my hosting provider restore the database using the .bak file. There's probably a good source of information on all of this so I don't need to ask you these questions. I'll start looking around more for information on this since my understanding in this area is very sketchy.

[gep13]

Hello,

Nope, the WCF Service is just a .svc file with associated code files (compiled into a DLL). Within Visual Studio, if you "publish" the project, you will get all the files that you need to move up to your web host.

Gary

[EntityX]

The short answer is that you DON'T!

You set up the database connection, using the ConnectionString in the .config file to point at a local database. Then, when you upload your service onto your hosting provider, you edit the .config file to point to your actual database.

Gary

Could you show me an example of the line or lines in a .config file pointing to a database?

[gep13]

I can do better than that, I can show you all the connection strings:

http://connectionstrings.com/

And to read it back, have a look here:

http://msdn.microsoft.com/en-us/library/ms178411.aspx

Gary

[EntityX]

Ok thanks. The connection string for my database is working ok but I was specifically interested in having my database go through the service. You said

when you upload your service onto your hosting provider, you edit the .config file to point to your actual database.

This is what I was interested in when I made my last post. Pointing the service at some database so the database goes through the Web Service or WCF Service.

I'm using a Web Service on my website that is used for downloading one of my applications. I use a WebMethod that enabled me to do that. Do I perhaps create some kind of WebMethod so the database uses the service or not? It sounds like from what you said before that I wouldn't have to do that but it's not clear to me how to get the database to go through the service.

[gep13]

Hello,

It would go something like this....

You would create a WebMethod called something like GetCustomers(). This method would return say a List(Of Customer). This WebMethod would then use the connection string in the web.config file, create a SqlConnection to the Database, create a SqlCommand object and execute a SQL Query/Stored Procedure to get the customers from the Database, then loop through the DataReader which is returned, and create your List of Customers, and then return this from the Web Method.

Your other application would then call the Web Method on your Web Service, knowing nothing about the database, only that there is a WebMethod called Get Customers.

Does that make sense?

Gary

[EntityX]

I'm now back on this problem once again. Let me describe what I'm trying to do in a little more detail. I have my Windows Forms application from which I want to be able to communicate with the SQL database on my website. After the help I received recently in the ASP.NET forum I can now query and update my database like I want to from my Windows Forms application but in another thread you and others told me I should be using a web service or a WCF service when I connect to my database so I just want to do what I'm now doing but go through either a web service or a WCF service. I have a table Ray Rover Activation in Database1 which is an SQL database on my website.

In the activation process for my application users will enter their email address and click a button and when they do a query using the email address they entered is done to see if the email address is in my Ray Rover Activation table in Database1. If it is then a message tells them their email address is in the database and to expect that an activation code that works for their computer will be emailed to them soon. At the same time an activation code is entered into the table and row associated with that particular email address. So I can do this all now but without going through a web service or WCF service. I just have to learn how to do it going through a web service or WCF service. Is there a place to find some examples of creating a web method in Visual Basic to do what I need to do?

[gep13]

Hello,

There are plenty of examples out there.

Here is one to get you started:

http://beyondrelational.com/blogs/dh...n-iis-7-5.aspx

Once you have that up and running you can start to add the methods that you require. This will involve taking the code that you already have, and porting it into the WCF Service. From there, your windows application will call the Methods that you create on the WCF Service, rather than executing the work directly.

Gary

[EntityX]

In my Windows Forms application I'm presently targeting .NET Framework 2.0. I did that because everything works fine using 2.0 and almost everyone with XP, Vista or Windows 7 will already have it installed on their computer. Can I call a WCF Service application from a Windows Forms appplication if the Windows Forms application is using .NET Framework 2.0. If not I can go to .NET Framework 3.0 or else if I keep my Windows Forms application using .NET Framework 2.0 I could use an ASP.NET Web Service Application. There's no WCF Service Application available in .NET Framework 2.0. Using an ASP.NET Web Service Application could I do what I need to do?

[gep13]

Hmmm, good question. Off the top of my head, I am not sure whether you could call a WCF Service from a .Net 2.0 Application. The best thing I could suggest would be to try it.

Yes, you could fall back to using a Web Service, there is nothing wrong with that. In terms of getting to where you want to be, i.e. an abstraction of the talking to the database directly via a service, both a Web Service and a WCF Service provide this.

Gary

[EntityX]

I decided I'm going to use an ASP.NET Web Service Application using .NET Framework 2.0 and see how that goes. I plan on using Visual Basic since that's what I'm most familiar with and that's what my ADO.NET code is written in. I found this article and was following it.

http://technet.microsoft.com/en-in/l...us,VS.71).aspx

Everything went fine until I got down to "Deploying the XML Web Service". They give you 2 options for deployment but I wasn't able to follow either because the options they talk about just aren't there for me. When I created the project in the walkthrough I had .NET Framework 2.0 selected and under Web I selected ASP.NET Web Service Application. There was also ASP.NET Web Application and if you have .NET Framework 4.0 selected there is no option ASP.NET Web Service Application only ASP.NET Web Application. This is all under Visual Basic not C#. If I'm using .NET Framework 2.0 should I select ASP.NET Web Service Application or ASP.NET Web Application? I would think the first.

Another thing in the above walkthrough at the beginning under "To create an ASP.NET Web Service Project", step 3 reads, "Click the ASP.NET Web Service icon." Since I'm using .NET Framework 2.0 I don't have that exact choice but just the ones I mentioned above. That might be part of the problem.

Maybe I could copy the files from this project to my website just for testing purposes and then when I get that working right I could create another ASP.NET Web Service Application that has the web methods I need to do what I'm trying to do. If I go that route what files would I copy from the ASP.NET Web Service Application to my website?

[gep13]

Hello,

When you are using .Net 4.0 they are encouraging you to use WCF Services rather than ASP.Net Web Services, that is why you don't see this option when you select .Net 4.0. What you want is to select .Net 2.0 and then select ASP.Net Web Service Application.

When they refer to "ASP.Net Web Service" this is when you have went "File | New Web Site..." rather that "File | Project..."

I would recommend that you go with ASP.Net Web Service Application under "File | New Project...".

Gary

[EntityX]

Ok thanks for your help. I'm using an ASP.NET Web Service Application that uses .NET Framework 2.0.

Right now I have an ASP.NET Web Service application that is just for testing purposes. I was going to put that on my website and try to access the web method from a windows forms application and then when I have success with that I'll create another ASP.NET Web Service application with web methods to do what I need to do so I can query and update my SQL database like I want to.

[EntityX]

I just got an email from Arvixe telling me how to use Publish and I was able to Publish the ASP.NET Web Service application to my website.

The ASP.NET Web Service application has the web method from the walkthrough I discussed in post # 18. If I try to navigate to the ASP.NET Web Service I can but if I click on any of the links there is always an error.

http://www.taylorentertainment.biz/W...heitToCelsius/

I'll try to sort through them.

[gep13]

Hello,

If you follow what is suggested in the "Yellow Screen of Death" (YSOD) you can turn on customErrors temporarily so that you can find out what exception is occuring in your application.

Gary

[EntityX]

Ok I just had to do the Publish differently. There was a check box, Mark as IIS application on destination, that I had left unchecked. Now the Service1.asmx link works ok. Now I need to learn how to access the ASP.NET Web Service application from a Windows Forms application.

[gep13]

Good stuff.

In your Windows Form application add a Web Reference to your project, and from there create an instance of the Web Service and from there, you can simply call the methods on it.

Gary

[EntityX]

Ok, I did step 1. I created a new Windows Forms application and I added a Web Reference using http://www.taylorentertainment.biz/W.../Service1.asmx. Step 2, create an instance of the Web Service; not clear on how to do that. Would I do that in the Form1 code for my Windows Forms application or I would I do it in reference.vb that you see in Solution Explorer for my newly created Windows Forms application or somewhere else?

[gep13]

Looks like you are getting there.

The part you are missing is the following:

Imports WindowsApplication6.WebServiceFahrenheitToCelciusReference

Public Class Form1

    Dim MyWebService As New Service1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        MessageBox.Show(String.Format("15 Degrees Fanhrenheit is {0} Degrees Celcius", MyWebService.ConvertTemperature(15)))
    End Sub
End Class

First add an Imports Statement to the top of the code file to the named Web Service that you created, i.e. WebServiceFahrenheitToCelciusReference.

From there, create a new instance of Service1. This is what you are calling the Web Service currently, in all likelihood this will change, so you will need to watch when you do this that you will need to update the reference in your windows form application.

With that in place you can then simply call methods on the Web Service instance. You currently only have one method and I show example of converting 15 degrees Fahrenheit to degrees Celcius.

Note that your Web Service Reference will actually expose this method as:

MyWebService.ConvertTemperatureAsync

So if you want, you can call it asynchronously so as to not block the UI thread.

Gary

[EntityX]

Very good. Thanks for all the help. I added a textbox and a label but I also used your code. Wasn't familiar with using a MessageBox like that so I also displayed the result using a label just so I know what's going on a little better. Everything worked just fine.

Imports Connect_To_Web_Service_Application_7_13_11.WebServiceFahrenheitToCelsiusReference
Public Class Form1
    Dim MyWebService As New Service1
    Dim DFahrenheit As Double = 0

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If IsNumeric(TextBoxDegreesFahrenheit.Text) = True Then
            DFahrenheit = CDbl(TextBoxDegreesFahrenheit.Text)
            MessageBox.Show(String.Format(DFahrenheit & " Degrees Fanhrenheit is {0} Degrees Celsius", MyWebService.ConvertTemperature(DFahrenheit)))
            LabelShowConversion.Text = DFahrenheit & " Degrees Fanhrenheit is " & MyWebService.ConvertTemperature(DFahrenheit) & " Degrees Celsius"
        Else
            MessageBox.Show("Enter numeric value")
        End If
    End Sub
End Class

Now I'll create another ASP.NET Web Service Application with web methods to perform queries and updates for my SQL database.

[gep13]

Hello,

On a side note, the reason for using String.Format is to prevent the need to concatenate strings, and in my opinion make things slightly more readable.

You could for instance change this:

MessageBox.Show(String.Format(DFahrenheit & " Degrees Fanhrenheit is {0} Degrees Celsius", MyWebService.ConvertTemperature(DFahrenheit)))

To this:

MessageBox.Show(String.Format("{0} Degrees Fanhrenheit is {1} Degrees Celsius", DFahrenheit, MyWebService.ConvertTemperature(DFahrenheit)))

For every {} with a number in it, you need to put a variable at the end of the string. Notice how I have moved DFahrenheit to the end, in front of the second variable {0} = DFahrenheit and {1} = MyWebService.ConvertTemperature(DFahrenheit)

Also, given that you are using it in two places, you might want to do something like this:

            Dim resultText As String = String.Format("{0} Degrees Fanhrenheit is {1} Degrees Celsius", DFahrenheit, MyWebService.ConvertTemperature(DFahrenheit))
            MessageBox.Show(resultText)
            LabelShowConversion.Text = resultText

Hope that helps!

Gary

[EntityX]

Thanks for that explanation. I'm working on creating the ASP.NET Web Service application with the web methods I need to query and update my SQL database. I'm thinking that the web methods will contain functions. As far as I know a function can only return one value. I could break things up and use a number of web methods/functions but let's say I used a sub and generated 2 or more results for different variables. Is there a way to communicate the results of those 2 or more variables through a web method that uses one Sub? Maybe not but I thought I would ask. If not I'll just break it up into 2 or more web methods with thier separate functions.

[gep13]

Hello,

If you needed to return multiple "things" back from the WebMethod, then you could return a class of your creation. This class would then contain the properties that you want to return, and all you would need to do is return an instance of this class, loaded with the values that you require.

Gary

[EntityX]

Ok. I'm going to look into that. I've already had success creating another ASP.NET Web Service application with a WebMethod to query my database and then I called the WebMethod from another windows forms app but I think it would be good for me to create a class like you suggest because things would happen faster than having a series of separate methods. I could just have one WebMethod to get all the information I need instead of more than one and then maybe another to update my database.

[gep13]

Sounds like a plan.

Depending on "what" you are returning, let's use the obvious example of a "Person", you can return a List(Of Person) from your web service if you have lots of them.

If you provide more details about exactly what you are trying to do, and what methods you are going to be using, I am sure we could have a proper discussion about it.

Gary

[EntityX]

I'm going to keep in mind what you said about creating a class since that might be useful to me in the future. I was able to create one WebMethod that does everything I need it to. It just returns one value but it updates my database the way I need it to so for now it seems that I don't need a WebMethod that returns more than one thing at a time. I'll mark this thread resolved and if I have any more problems I'll start a new thread.

Thanks again for all the help. You gave me just what I needed to solve my problems.

[gep13]

Not a problem at all!

I don't often check this forum, so if you do start a new thread and I don't pick up on it, feel free to shoot me a PM and I will take a look and help if I can.

Gary