Mr Haugen

Hello

I was told that it was a REALLY bad idea to send SQL queries directly over the Internet. That's why I'm here.
I'm trying to make a web service to forward SQL queries from a VB.net plugin to my SQL server and return the result, but have little idea of how to attack this. Have been searching the web and have made something that look like it can work as a web service:

The simple WebService.asmx file (not made in .Net!) contains the following:


Code Snippet

<%@ WebService Language="VB" Class="MoodAndStyle" %>

Imports System.Web.Services
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class MoodAndStyle
Private Function GetDataSet(SQLQuery as String) as DataSet
'1. Create a connection
Dim SQLconn as New SqlConnection("Data Source=10.0.0.5;Initial Catalog=Mood;User Id=moodDBUser;Password=test;")

'2. Create the command object, passing in the SQL string
Dim command as New SqlCommand(SQLQuery, SQLconn)

SQLconn.Open()

'3. Create the DataAdapter
Dim dataAdapter as New SqlDataAdapter()
dataAdapter.SelectCommand = command


'4. Populate the DataSet and close the connection
Dim myDataSet as New DataSet()
dataAdapter.Fill(myDataSet)
SQLconn.Close()

'Return the DataSet
Return myDataSet
End Function

<WebMethod()> Public Function GetArtists() as DataSet
Return GetDataSet("SELECT * FROM Artist")
End Function
End Class


I need to return the dataset from the SQL server. I read that I could use a web service proxy, but don't really want to complicate this. I had a hard enough time making the actual plugin in VB.net...
Are there anyone that could tell me what kind of stuff I need to make this work For now I would just like to push a button to return the dataset. I know how to extract the data from the dataset. But thats about it.

- Carl



Re: ASMX Web Services and XML Serialization Web Service noob needs some help

rfreire

Hi Carl,

Let me see if I'm understanding the situation here.

You've developed this WS..... is it working .... you can test it just browsing for the ASMX in IE and executing the GetArtists() method from there..... you should see after that an XML that corresponds to the serialized Dataset. It this is happening you've succesfully developed a WS to get artists.

Now, you want to use this WS, right .. you want to create a client application to use this web method.

For that, you should create a new application, either a win forms application or whatever you want and then right click on the project and select "Add web reference"

To the dialog that opens there, you should put the url for your ASMX, and name this reference as you wish. This is all you need to create a proxy to call your WS.

Now from your client you should be able to see a new class (named just like your service): the proxy. This new class provides you with the same methods you defined in the service as web methods... so, suppose you named the reference MyService, and the service class is named ArtistsService.

You should be able to do this call in your client after adding the web reference:

MyService.ArtistsService srv = new MyService.ArtistsService();

DataSet artistsDataset = srv.GetArtists();

Could you try to do that .

Rgds,

Rodrigo





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Mr Haugen

Thanks Rodrigo. That made things a bit clearer.

The first answer have to be no. I did not test it. Did not think of running it in IE. Do not know how I can use the getArtist Method in IE but I tried to simply run the file.

It does not look like it is working. Have to sort this one out first I guess.

I get this error message:

Code Snippet

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


Re: ASMX Web Services and XML Serialization Web Service noob needs some help

rfreire

Ok, let's go step by step.

The problem you're facing here is that you've not installed ASP.NET, that's why you get this error and that's why you don't see the templates in Visual Studio.

I'd suggest you to re-run the Visual Studio installation so you can add features and then look for the ASP.NET item (or something with Web related - I don't really remember how it is shown in the installation).

Then, you have to go through Programs -> Visual Studio 2005 -> Visual Studio Tools -> Visual Studio 2005 Command Prompt.

This will open a cmd, there you'll register ASP.NET in IIS running this command:

> aspnet_regiis -i

Then, try to open the ASMX again in IE, and try to see whether in VS you have the templates to create web sites.

Please try this, and then we can continue with the remaing issues.

Regards,

Rodrigo





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Matt_

Hi,

For creating a new webservice project, open a VS IDE, click on File->New Web Site. It should open the Web Templates for you. Just select ASP.NET Web Service, and choose VB.NET from the combo "Language" at the bottom, and you're good to go...

Matias.





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Mr Haugen

Ok. Step by step is a good thing :-) I actually thought that ASP.Net was included in the .Net 2.0 framework. Silly me.

Matias, I tried your method, and you are totally right. I had to create a web page. NOT a project. And there it was, "ASP.Net Web Service".

Is it neccesary to create the webservice here or whould it be enough with the amsx file I have already created

Rodrigo. I don't see a ASP.net installation under "Add remove programs", so I guess I have to install this anyway Even though I can choose it in Visual Sudio There is a package with only ASP.net available right Don't have to reinstall the whole studio I hope.

One more thing. I think I have to explain my setup here. I'm sitting my my workstation to do the programming and testing the plugin. And preliminary testing of the web service.

When I'll test the web service later it would be just as good to do this on my server I think. Don't have IIS on my workstation, so it might be a problem otherwise. I got a server running win2k3, the SQL server database and IIS.

So here is my plan:

I'll download the ASP.net package and install this on my workstation, then do some minor testing on the asmx file. Later I could install ASP.Net on my server and then register it in IIS. Then I guess I need to make a virtual directory to host the web service in IIS (I allready got a homepage running on this server). This sounds like the right steps





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

rfreire

Carl,

Are you able to choose "New Web Site" in Visual Studio .... There, are you able to select "ASP.NET Web Service"

If so, you don't have to install anything.

Creating this new "ASP.NET Web Service" project will also create a web service for you with a single web method (HelloWorld).

You can try opening this new web service in IE to see if it works.

Now... IF after opening this WS in IE you get the same error.... you have to go and run the "aspnet_regiis -i" command as I mentioned in a previous post.

If not (if it works fine and you see the HelloWorld method there) you're all settled up.

And now you're ready to go back to the first post I sent you regarding the proxy, etc, etc.

Right

Rgds,

Rodrigo





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Mr Haugen

Bah! When I try to create a new Web Service I am told that my server is missing Frontpage Extention.

Not sure if this has anything to do with the URL I'm entering or not. I have tried to enter a URL that I want the web service to run at later http://10.0.0.5/webservice. And the URL to my webside as it is today (from within my local network). http://10.0.0.5

I looked at the current Web Service Extentions of my IIS. I only have ASP.NET 2.0 as allowed.

And there are no sign of Frontpage server extentions.

This Frontpage Extention, is it something I can install from a standalone package or is it included in something





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Matt_

Hello Mr Haugen.

You can try download the fp extensions from here: http://www.download.com/3001-2341_4-10106086.html

Or you can install them from your windows CD... Go to Add or remove programs->Add or remove windows components (a button on the left panel). Then on the list that is displayed select Internet Information Services and click on the button "Details". You should see the Front Page Extensions there, check it and press Ok, and then Next.... that will install them... and tell us if the problem is solved...

Matias





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Mr Haugen

I just remembered I used Apache 2.2 for my homepage. Instead of running Apache and IIS I desided to go IIS only, and rather make some virtual directories (I'll be testing out exchange and outlook web access later on). That was not as easy as I hoped.

At least not with the PHP part. But I finally made it work though.

I have now added ASP.Net and Frontpage extensions. My allowed extensions in IIS are as follows: Active Server Pages, ASP.Net 1 and 2, FrontPage server extentions 2002, PHP and PHP: Hypertext Processor. I hope thats it.

So, over to the testing of the service.

1. I tried to run the asmx file I had on the workstation directly in IE on the server. That did not work at all. Then I started to think, and put the file under my virtual directory, and ran it again. Then I got this error:

Parser Error Message: The page must have a directive.

Source Error:

Line 1: 
Line 2: 
Line 3: Imports System.Web.Services

I'm not exactly sure what that means and what I have to do to correct it.

So, over to the next option.

2. I tried to creat a new ASP.Net Web Service from Visual Studio. I entered the URL to my server and a directory under wwwroot I want to contain the service: http://10.0.0.5/WebService. I choose Visual Basic and HTTP.

After I enter my username and password for the server I get this message:

Code Snippet

Unable to add 'C:\Documents and settings\............\Service.vb' to the Web

Server error: The folder "/webService/App_Code" is marked executable. You are not allowed to put files into an executable folder on this server.

This was sorted out by removing executables and such from the virtual directory. Great! Found out something for my self.

I then tried to run the it in IE: http://webservice/service.asmx. That left me with a message that I had no execute permission in the folder. Oh, what a surprise...

Changed the permission back, and WOW! It actually works. "Hello world". That's sweet. Just bad I have to change the permissions every time....

Wait. It's not just to change it back and forth. After I had done it a couple of times I got the message that the Frontpage server extention was not installed. It was obviously the vti_bin that had to have the execution rights. This is confusing me.

So, now I just have to merg my previous asmx file into the VS web service. I'm pretty loaded with stuff to do at the moment. So I'll post a message later on when I have tried it.

By the way. How do this "Was this post helpfull" thing Am I supposed to click yes every time a post from people help me out or just one from the same person.

Thanks ALOT for the help so far. Would never gotten this far without your help guys!





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Mr Haugen

Ok. So, I got my web service up and running. No errors. Just the GetArtist method. Super!

I tried this in my plugin after adding the web reference:

MyService.ArtistsService srv = new MyService.ArtistsService();

DataSet artistsDataset = srv.GetArtists();

I get blue lines under almost everything. Fist of all. I can put this directly under a button or not I tried that.

I called my reference in my plugin refGetArtist. The class in the web service are called Service. The web method is GetArtist.

I have a little problem to see what is what.





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

rfreire

Hi,

Please post the compilation errors you're getting and the code you're using.

Regards,

Rodrigo





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Matt_

Hi Mr Haugen,

If the class in your web service is called "Service", the proxy instantiaton should be something like this...:

MyService.ArtistsService.Service srv = new MyService.ArtistsService.Service();

DataSet artistsDataset = srv.GetArtists();

This is just a longshot... posting the compilation errors would really tell us what is happenning...

Matias.





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Mr Haugen

I tried this:

Code Snippet

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.Data

Imports System.Configuration

Public Class MainInterface

Private Sub bt_serviceTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_serviceTest.Click

MyService.ArtistsService.Service(srv = New MyService.ArtistsService.Service())

DataSet(artistsDataset = srv.GetArtists())

End Sub

End Class

It gave me this build errors:

Code Snippet

Error 1 Name 'MyService' is not declared. 1472 9

Error 2 Name 'srv' is not declared. 1472 42

Error 3 Type 'MyService.ArtistsService.Service' is not defined. 1472 52

Error 4 'DataSet' is a type and cannot be used as an expression. 1474 9

Error 5 Name 'artistsDataset' is not declared. 1474 17

Error 6 Name 'srv' is not declared. 1474 34

Obviously alot of declaration missing. I could probably fix the DataSet, but have a problem with the rest of them. If you could give me a whole example of the neccesary code I would be forever greatfull. Again, my WS reference in the plugin are calle refGetArtist. And this is the code for the web service (in case you need it):

Code Snippet

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.Data.SqlClient

Imports System.Data

Imports System.Configuration

<WebService(Namespace:="http://10.0.0.5/webservice/")> _

<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public Class Service

Inherits System.Web.Services.WebService

Private Function GetDataSet(ByVal SQLQuery As String) As DataSet

'1. Create a connection

Dim SQLconn As New SqlConnection("Data Source=10.0.0.5;Initial Catalog=Mood;User Id=moodDBUser;Password=test;")

'2. Create the command object, passing in the SQL string

Dim command As New SqlCommand(SQLQuery, SQLconn)

SQLconn.Open()

'3. Create the DataAdapter

Dim dataAdapter As New SqlDataAdapter()

dataAdapter.SelectCommand = command

'4. Populate the DataSet and close the connection

Dim myDataSet As New DataSet()

dataAdapter.Fill(myDataSet)

SQLconn.Close()

'Return the DataSet

Return myDataSet

End Function

<WebMethod()> Public Function GetArtists() As DataSet

Return GetDataSet("SELECT * FROM Artist")

End Function

End Class





Re: ASMX Web Services and XML Serialization Web Service noob needs some help

Mr Haugen

I tried this:

Code Snippet

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.Data

Imports System.Configuration

Public Class MainInterface

Private Sub bt_serviceTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_serviceTest.Click

MyService.ArtistsService.Service(srv = New MyService.ArtistsService.Service())

DataSet(artistsDataset = srv.GetArtists())

End Sub

End Class

It gave me this build errors:

Code Snippet

Error 1 Name 'MyService' is not declared. 1472 9

Error 2 Name 'srv' is not declared. 1472 42

Error 3 Type 'MyService.ArtistsService.Service' is not defined. 1472 52

Error 4 'DataSet' is a type and cannot be used as an expression. 1474 9

Error 5 Name 'artistsDataset' is not declared. 1474 17

Error 6 Name 'srv' is not declared. 1474 34

Obviously alot of declaration missing. I could probably fix the DataSet, but have a problem with the rest of them. If you could give me a whole example of the neccesary code I would be forever greatfull. Again, my WS reference in the plugin are calle refGetArtist. And this is the code for the web service (in case you need it):

Code Snippet

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.Data.SqlClient

Imports System.Data

Imports System.Configuration

<WebService(Namespace:="http://10.0.0.5/webservice/")> _

<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public Class Service

Inherits System.Web.Services.WebService

Private Function GetDataSet(ByVal SQLQuery As String) As DataSet

'1. Create a connection

Dim SQLconn As New SqlConnection("Data Source=10.0.0.5;Initial Catalog=Mood;User Id=moodDBUser;Password=test;")

'2. Create the command object, passing in the SQL string

Dim command As New SqlCommand(SQLQuery, SQLconn)

SQLconn.Open()

'3. Create the DataAdapter

Dim dataAdapter As New SqlDataAdapter()

dataAdapter.SelectCommand = command

'4. Populate the DataSet and close the connection

Dim myDataSet As New DataSet()

dataAdapter.Fill(myDataSet)

SQLconn.Close()

'Return the DataSet

Return myDataSet

End Function

<WebMethod()> Public Function GetArtists() As DataSet

Return GetDataSet("SELECT * FROM Artist")

End Function

End Class