Consuming a web service, importing the consumed xls file into a sql server database table

skaleem1
skaleem1 used Ask the Experts™
on
I am very new to web services and I have to work on the following:

I am provided a web service url. When I run this url on a browser, I see a popup that asks about either opening or saving a Microsoft Excel file. When I select open or save option, it appropriately opens or saves the xls file on my hard drive. I want to consume this web service and import this xls file into a table in the SQL Server database. Can you please help me step by step i.e.
(1) How to consume the web service and receive the Excel file through my web service
(2) Import the data in the xls file using c#.NET into a table in the SQL Server database

Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2008

Commented:
I believe that what you need to do is to add a web reference to your project, create an instance of the web service proxy class that is generated when you add a web reference, and then call the web method to get the file.

Author

Commented:
Thanks for your reply. When adding a web reference to the project, I have to enter the complete path to the Report Server Web service in the URL field. Can I add a url other than .asmx extension. In my case, the url I am provided has a .jsp extension. This shows that the web service is written in java. Following is the url (I have altered it for security reasons):

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=xyz&tabs=WhereUsed&outputFileName=Abc_Report_WhereUsedTab_28Jan2010.xls

If a .jsp web service can be consumed in .Net, do I have to enter the complete path above as the url or only the path for the server and the web service without parameters, i.e:

http://emat:7001/emat/common/JPOServlet.jsp?

Please guide me here as I am a novice...

Thanks
Most Valuable Expert 2012
Top Expert 2008

Commented:
Now, you are really diving into the deep end of the pool...it depends...

Does the Java web service use SOAP extensions, or JSON?  Is there anyone that can explain how this web service is configured?  It sounds like a REST web service.  .NET natively supports SOAP web services, but REST web services would require a different approach, such as using an HttpWebRequest.

Reference:

REST vs SOAP Web Services
http://www.petefreitag.com/item/431.cfm
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
The person in charge of the web service is out on vacation this week. I will try to find out the details sometimes next week. Thanks for the response.

Author

Commented:
I am still waiting to hear from the person in charge of the web service. I will let you know the details as soon as I get them. Just to keep you posted.

Author

Commented:
TheLearnedOne,

I have got the some info now. The web service is Rest based and when I try to add the web reference and enter the following url:

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=xyz&tabs=WhereUsed&outputFileName=Abc_Report_WhereUsedTab_28Jan2010.xls

Instead of building the web reference file, I rather see a popup to open or save the data file as Excel file. This is the same behaviour I get when I paste the url in a browser. In both cases, it correctly opes and saves the Excel file with all the data.

Now, at this point, I want to know if there is a way for not seeing the popup when I try to add web reference to my project, rather having a web reference built which I can programmatically consume?

Here is the comments of the person:

 "I’ve never done any C# programming, so I’m not familiar with the APIs.  However, in Java, one would open a “URLConnection” and an associated InputStream to pull down the file. "

Your help is greatly appreciated.

Thanks
Most Valuable Expert 2012
Top Expert 2008

Commented:
If you have simple requirements to connect to the URL, and get the file, you can use the System.Net.WebClient to do this:

WebClient client = new WebClient();
client.DownloadFile(url, localFileName);

Author

Commented:
Can you please elaborate. Are you saying that I do not have to add web reference to my project? Would I just use a simple aspx page and add the System.Net.WebClient in the Page_Load event?
Most Valuable Expert 2012
Top Expert 2008

Commented:
I believe that is the first time that you have mentioned what type of application you are working with.  If you are working with an ASP.NET web site, then I would think that you wouldn't need a page.  You could create a new class, add a static DownloadFile(string url, string localFileName) method.

Author

Commented:
But I would have to call the class from some page, right?
Most Valuable Expert 2012
Top Expert 2008

Commented:
I feel that I have too narrow a view (100 meter), when I really need a 10000 meter view.  Can you give me an overview of your process, please?

Author

Commented:
I am provided a web service url. When I run this url on a browser, I see a popup that asks about either opening or saving a Microsoft Excel file. When I select open or save option, it appropriately opens or saves the xls file on my hard drive. I want to consume this web service and import this xls file into a table in the SQL Server database.

In an attempt to consume the web service, I tried adding a web reference to my project within Visual Studio 2008. I entered the provided url in the URL field textbox present in the Report Server Web service. In my case, the url I am provided has a .jsp extension and the web service is written in java. The web service is Rest based, not SOAP based. Following is the url (I have altered it for security reasons):

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=xyz&tabs=WhereUsed&outputFileName=Abc_Report_WhereUsedTab_28Jan2010.xls

Instead of building the web reference file, I rather saw a popup to open or save the data file as Excel file. This is the same behaviour I get when I paste the url in a browser. In both cases, it correctly opens and saves the Excel file with all the data.

All I want is to consume this rest based web service and get a handle on the input stream of data and then programmatically insert the data into SQL Server database.

From your last few conversations I got an impression that I do not have to consume a web service. Since it is a simple requirement to connect to the URL and get the file, I rather can use the System.Net.WebClient to do this, correct?

If the answer is yes, as I understand, I can write a class and call this download method from a simple aspx page, am I right?


Most Valuable Expert 2012
Top Expert 2008

Commented:
OK, now we're getting somewhere...please expand on "Report Server Web service"...Crystal Reports, SSRS, etc...

Author

Commented:
I apologize, what I meant is the Add Web Reference Dialog box in the Visual Studio Solution Explorer (not sure why I put the wrong name of dialog box). In this dialog box I tried to enter the url I mentioned above...

Author

Commented:
Let me repeat the whole issue below with minor modification:

I am provided a web service url. When I run this url on a browser, I see a popup that asks about either opening or saving a Microsoft Excel file. When I select open or save option, it appropriately opens or saves the xls file on my hard drive. I want to consume this web service and import this xls file into a table in the SQL Server database.

In an attempt to consume the web service, I tried adding a web reference to my project within Visual Studio 2008. I entered the provided url in the URL field textbox present in the Add Web Reference Dialog Box. In my case, the url I am provided has a .jsp extension and the web service is written in java. The web service is Rest based, not SOAP based. Following is the url (I have altered it for security reasons):

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=xyz&tabs=WhereUsed&outputFileName=Abc_Report_WhereUsedTab_28Jan2010.xls

Instead of building the web reference file, I rather saw a popup to open or save the data file as Excel file. This is the same behaviour I get when I paste the url in a browser. In both cases, it correctly opens and saves the Excel file with all the data.

All I want is to consume this rest based web service and get a handle on the input stream of data and then programmatically insert the data into SQL Server database.

From your last few conversations I got an impression that I do not have to consume a web service. Since it is a simple requirement to connect to the URL and get the file, I rather can use the System.Net.WebClient to do this, correct?

If the answer is yes, as I understand, I can write a class and call this download method from a simple aspx page, am I right?
Most Valuable Expert 2012
Top Expert 2008

Commented:
Let's go through this:

1) You can't add a web reference using the dialog with the REST web service, since it is URL-based, and not WSDL-based.

2) You need to download the file with something like an HttpWebRequest (complex requirement), or WebClient (simple requirement).  The WebClient is a simple wrapper for an HttpWebRequest.

3) The trigger for call to the class method is what I am not seeing.  Are you needing a hyperlink on a web page, that will download the file, and then upload to SQL Server?  If it is something else, please indicate.

Author

Commented:
I am not sure if it can be done or not, but if it can be, ideally I want that class to be called from a DTS or SSRS on nightly bases. It should download data from the url and insert into an SQL Server database. I want to minimize any human interaction as much as I can.
Most Valuable Expert 2012
Top Expert 2008
Commented:
If you mean SSIS, then it is possible to create something that downloads a file, even though there isn't a specific task for that.

Downloading a file over HTTP the SSIS way
http://www.sqlis.com/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

If this question was representative of driving in a car, we would be swerving all over the place *BIG GRIN*.
Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()

        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)

        ' Create a new HTTP client connection
        Dim connection As New HttpClientConnection(nativeObject)


        ' Download the file #1
        ' Save the file from the connection manager to the local path specified
        Dim filename As String = "C:\Temp\Sample.txt"
        connection.DownloadFile(filename, True)

        ' Confirm file is there
        If File.Exists(filename) Then
            MessageBox.Show(String.Format("File {0} has been downloaded.", filename))
        End If


        ' Download the file #2
        ' Read the text file straight into memory
        Dim buffer As Byte() = connection.DownloadData()
        Dim data As String = Encoding.ASCII.GetString(buffer)

        ' Display the file contents
        MessageBox.Show(data)


        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Open in new window

Author

Commented:
Thanks a lot TheLearnedOne. I think you deserve all the points however I need further help on this. Should I open a new question and redirect you to that link. I made sure that I have the SSIS installed and accessible. Now I need a walk through on how to go to HTTP Connection Manager Editor on SQL Server 2005, how to create, debug and execute the task etc. Please suggest!

Author

Commented:
I have opened a new question. Please help if you can. Here is the link:

http://www.experts-exchange.com/Programming/Languages/.NET/Web_Services/Q_25123641.html?fromWizard=true


Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial