none
Reading excel into Datatable doesnt work in WCF after deployment in IIS RRS feed

  • Question

  • Hi All,

    I have a need to process couple of excel spreadsheets and I need to do this via WCF. As a first step my webservice gets input parameters as path of the excel sheet and it reads data from excel sheet using Microsoft.Jet.OLEDB.4.0

    My Webservice accepts input for path of the excel sheet which will be a networkshare path and then load the data in the spreadsheet into a datatable. My wcf method code works fine when tested locally but datatable always return null after hosting in IIS and accessing the webservice externally. I have checked my webservice is hosted fine and is accessible via wcftestclient.

    Please ignore any mistakes in the getexcel method as I can update it exactly later. But I have it working locally. But once I host in the IIS. I always get null returned for Datatable.

    public int readexcel(path)
    {
    
    string cmd= ("SELECT Name, name 2 FROM [data$]");
    DataTable table = GetExcel(path,cmd);
    
    if (table!=null)
    {
    
    return 1;
    
    }
    else 
    {
    return 0;
    }
    
    
    public Datatable GetExcel(string DataSource, string  cmdstring)
            {
                System.Data.OleDb.OleDbConnection NewOleDbConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + DataSource + "\'\"Extended Properties=\"Excel 8.0;\"\'");
                System.Data.OleDb.OleDbCommand NewOleDbCommand = new System.Data.OleDb.OleDbCommand(NewCommandString, NewOleDbConnection);
    
                try
                {
                    NewOleDbConnection.Open();
                    System.Data.OleDb.OleDbDataAdapter NewOleDbAdapter = new System.Data.OleDb.OleDbDataAdapter(NewOleDbCommand);
                    Datatable dt = new Datatable();
                    NewOleDbAdapter.Fill(dt);
                    NewOleDbConnection.Close();
                    return dt;
                }

    Monday, October 7, 2013 7:05 PM

Answers

  •  

    <copied>

    I have not defined any datacontracts. All I can see is when I run it in visual studio it runs fine as expected and returns 1 since the datatable is not null in my localhost server that VS runs it on

    <end>

    VS development server and IIS are two different things, and  I think you are beginning to find that out. Sorry, it's right in your face now about the differences between the VS development server and IIS. I always use local IIS.  

    Some other posters have discussed about using a datatable with WCF, and you are going to have to look for the post and/or replies in this forum.

    The only other thing to do is to find out how to serialize a dataset. You should be able to serialize a datatable as string and send it and deserialize the string back to a table. There are articles out there and you can use Bing or Google.

    Monday, October 7, 2013 11:09 PM

All replies

  • <copied>

    Please ignore any mistakes in the getexcel method as I can update it exactly later. But I have it working locally. But once I host in the IIS. I always get null returned for Datatable.

    <end>

    What do you mean you have it working locally? Do you mean you have it working and WCF is not  involved? The datatable is going to be empty with WCF if the datatable is not serializable.

    Monday, October 7, 2013 8:41 PM
  • when I say I have it working locally. It means on my dev machine. I debug and see everything works fine when I run my wcf service project with the same code. I have my datatable populated as expected. I deploy on my server machine in IIS and it always returns null value. Do u mean datatable don't need to be serializable in my dev machine when I run it there in debug mode. And it need to be serializable after deploying? What do I need to do to make it that way?

    Monday, October 7, 2013 9:10 PM
  • And just to clarify exactly the code for getexcel may not be exactly as I wrote here in the post but is very similar and it works when I run it in my dev machine in debug mode of wcf project..sorry if it confused.
    Monday, October 7, 2013 9:22 PM
  • <copied>

    Do u mean datatable don't need to be serializable in my dev machine when I run it there in debug mode. And it need to be serializable after deploying? What do I need to do to make it that way?

    <end>

    The datatable has to be serializable  period with WCF. If you are talking VS development server, then I would think it has to be serializable  there too. But I don't know as I have not used the VS development server only local IIS and using custom objects with DataContract and DataMember attributes and WCF takes care of serialization.

    How you serialize datatable  for WCF I don't know.  I have seen some posts about it in this forum. I guess you'll have to look.

    The best I can tell you is map the datatable to a List<T> of objects and send it back.

    http://www.fredmastro.com/post/Mapping-a-DataTable-to-an-Object-Class-Like-a-homemade-Linq-to-SQL-Send-over-WCF-to-SilverLight.aspx

    http://blogs.msdn.com/b/kaevans/archive/2008/01/24/wcf-and-custom-bindings.aspx

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

    Monday, October 7, 2013 9:42 PM
  • As you can see my code as simple as I have it above. Using visual studio and creating a new wcf service project and then exposing my readexcel method above and it has a return type of int depending on the value of the datatable. I have not defined any datacontracts. All I can see is when I run it in visual studio it runs fine as expected and returns 1 since the datatable is not null in my localhost server that VS runs it on. I published it and deployed it to my IIS server and it always returns datatable as null. I will read the links you posted but I didn't think it had to be so complicated.. :(
    Monday, October 7, 2013 10:12 PM
  •  

    <copied>

    I have not defined any datacontracts. All I can see is when I run it in visual studio it runs fine as expected and returns 1 since the datatable is not null in my localhost server that VS runs it on

    <end>

    VS development server and IIS are two different things, and  I think you are beginning to find that out. Sorry, it's right in your face now about the differences between the VS development server and IIS. I always use local IIS.  

    Some other posters have discussed about using a datatable with WCF, and you are going to have to look for the post and/or replies in this forum.

    The only other thing to do is to find out how to serialize a dataset. You should be able to serialize a datatable as string and send it and deserialize the string back to a table. There are articles out there and you can use Bing or Google.

    Monday, October 7, 2013 11:09 PM