none
I need to create a web service that provides SQL server data to a different web application... HELP!

    Question

  • I manage a SQL Server database (2005) which provides data for a web application built on SharePoint.

    In SQL Server, I have a view [view1] which queries several tables in my database. I want to be able to share that data view with another developer who will include this data in his application (outside of sharepoint and on a different web server)

    The other developer asked me to create a web service that can provide the data view to him in an XML format, so that his application can consume the data and have (read-only) access to the data. He needs a URL with the .wsdl extension that he can consume with his application.

    I have a web server with IIS, where I can publish the web service, and I can also (if possible) publish the web service on my sharepoint portal. But I have not been able to find a clear way to do this.... HELP!!

    I have searched many forums, blogs, I've done a few walk-throughs, and I was able to create a web service on visual studio (the Hello world!) but I cannot find a way to include the SQL server view, and I cannot generate a .wsdl file

    I appreciate your help in advanced!!!



    Me
    Wednesday, December 21, 2011 7:31 PM

All replies

  • On 12/21/2011 2:31 PM, SharePoint Dude wrote:
    > I manage a SQL Server database (2005) which provides data for a web
    > application built on SharePoint.
    >
    > In SQL Server, I have a view [view1] which queries several tables in my
    > database. I want to be able to share that data view with another
    > developer who will include this data in his application (outside of
    > sharepoint and on a different web server)
    >
    > The other developer asked me to create a web service that can provide
    > the data view to him in an XML format, so that his application can
    > consume the data and have (read-only) access to the data. He needs a URL
    > with the .wsdl extension that he can consume with his application.
    >
    > I have a web server with IIS, where I can publish the web service, and I
    > can also (if possible) publish the web service on my sharepoint portal.
    > But I have not been able to find a clear way to do this.... HELP!!
    >
    > I have searched many forums, blogs, I've done a few walk-throughs, and I
    > was able to create a web service on visual studio (the Hello world!) but
    > I cannot find a way to include the SQL server view, and I cannot
    > generate a .wsdl file
    >
    > I appreciate your help in advanced!!!
    >
     
    I don't think you are going to get XML out of a SQL view in this
    situation unless you take custom objects that are serialized to XML and
    send that back.
     
    If you have a class/object that looked like this, then you can serialize
    it to a XML object.
     
    public class Customer
    {
       public string Name {get; set;}
       public decimal Cost {get; set;}
       public bool IsCurrent {get; set;}
    }
     
    Now you can serialize the object to XML. Only primitive type properties
    like int, double, decimal, etc are serializeable. And of course, XML is
    string so you would be sending string data back.
     
    Now, if you have these custom objects as a classlib project that can be
    shared between projects (your project and this other person's project),
    then you both can set reference to this DLL. It will allow one to cast
    the XML object back to a true class/object. It would be the only
    addressing of the XML, because after the cast, an object will be used.
     
    On the other hand, you don't have to cast anything back and just address
    the XML representation of the object's primitive type properties that
    was sent back as XML.
     
    You can have a master object with its primitive properties, and within
    the master object you can have a List<T> of child objects with parmative
    type properties, and you can serialize the master object that will
    serialize and child objects too down to XML.
     
    So, put yourself together a little project using custom objects,
    populate the object, serialize the object down to a XML file and look at
    the contents of the file with notepad or something.
     
    Now, a WCF Web service if you are sending those custom objects over the
    wire would do the XML serialization for you and cast XML objects back to
    objects by itself. That's the way WCF works in this situation.
     
    The object would have to look like this.
     
    <Seralizable>
    public class Customer
    {
       <Datamember>
       public string Name {get; set;}
        <Datamember>
       public decimal Cost {get; set;}
        <Datamember>
       public bool IsCurrent {get; set;}
    }
     
    That's just for future reference.
     
    You don't want to do that in this situation.
     
    All you want to do is XML serialize the custom object pointing it to a
    string variable and sending the string variable back to the client as
    string.
     
    As a matter of fact, you don't even need a WCF Web service for this,
    which would cause the client to deal with WCF configuration too on the
    client-side.
     
    What you want is a plain old lagacy ASP.NET Web service and give the
    client the URL.
     
     
    • Marked as answer by Yi-Lun Luo Wednesday, December 28, 2011 9:34 AM
    • Unmarked as answer by SharePoint Dude Wednesday, December 28, 2011 11:22 PM
    Wednesday, December 21, 2011 10:14 PM
  • Thanks Darnold, but although I consider my self a pretty saavy guy, I do not understand what exactly you are telling me to do. I guess I am more on the amateur side of development and so if you can simplify it a bit, or give me a step-by-step then that would help me better.

    In fact, let me rephrase the question, forget about a view, let's just share a couple of table fields using a web service... So that I put it in very simple ways, and perhaps get a very simple answer:

    I have a table of my SQL server, let's call it [Table1] that has two fields: [Field1] and [field2], I need to create a webservice with a WLSD file URL to tell another application where to get my data.

    I cannot believe that nobody out there has had the same issue?


    Me
    Wednesday, December 28, 2011 11:39 PM
  •  [WebMethod]
            public DataSet GetSqlData()
            {
                SqlConnection sqlCon;
                SqlDataAdapter sqlAdp;
                DataSet ds = new DataSet();
                sqlCon = new SqlConnection("Server=.;Database=YourDBName;Trusted_Connection=True;");
                sqlAdp = new SqlDataAdapter("SELECT Field1,Field2 From Table_1", sqlCon);
                sqlCon.Open();
                sqlAdp.Fill(ds);
                sqlCon.Close();
                return ds;
            }
    Lingaraj Mishra
    Thursday, December 29, 2011 12:34 PM
  • Thank you Lingaraj, that is much closer to what I was expecting. However, I just tried doing the code you gave me (using VS2010 C#) using the webservice template, and I get a squigly line under "DataSet" as it is expecting a class or a delegate.

    Any suggestions?

    And thank you for your help!!


    Me
    Thursday, December 29, 2011 1:16 PM
  • Probabaly You are missing to include

    using System.Data;
    using System.Data.SqlClient;
    

    I would encourge you to use WCF service insted of asmx as asmx is going to be obsolete.

     


    Lingaraj Mishra
    Thursday, December 29, 2011 1:40 PM
  • I have added the libraries and I still get the "Expected Class, delegate, enum, interface, or struct" with the squigly lines under the "DataSet" and "SqlConnection" and "SqlDataAdapter"

    Any ideas on what am I doing wrong?

    Thanks for response!


    Me
    Thursday, December 29, 2011 1:57 PM
  • have you included below two lines at top of your web service class ?

    using System.Data;
    using System.Data.SqlClient;


    Lingaraj Mishra
    Thursday, December 29, 2011 2:28 PM
  • Thanks again for the prompt response. Here is the full code I am using, I started with the web service template given by VS2010...

     

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Services;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SAdataWebService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    //[System.Web.Script.Services.ScriptService]
    [WebMethod]
        public DataSet SAdataWebService()
    {
        SqlConnection sqlCon;
        SqlDataAdapter sqlAdp;
        sqlCon = new SqlConnection("Server=SQLsrv1;Database=DB1;Trusted_Connection=True;");
        sqlAdp = new SqlDataAdapter("SELECT * FROM [Table1]",sqlCon);
        sqlCon.Open()
        sqlAdp.Fill(ds)
        sqlCon.Close();
        return ds;
    }

    I get errors in lines 17, 21, 22, and 27...

    Any ideas what is causing the errors when I try to debug it?


    Me
    Thursday, December 29, 2011 3:08 PM
  • Seems you are missing the class definition. You can try the complete code as below.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Services;
    using System.Data;
    using System.Data.SqlClient;
        /// <summary>
        /// Summary description for WebService2
        /// </summary>
        [WebService(Namespace = "http://tempuri.org/")]
        [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
        [System.ComponentModel.ToolboxItem(false)]
        // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
        // [System.Web.Script.Services.ScriptService]
        public class WebService2 : System.Web.Services.WebService
        {
            [WebMethod]
            public DataSet SAdataWebService()
            {
                SqlConnection sqlCon;
                SqlDataAdapter sqlAdp;
                DataSet ds = new DataSet();
                sqlCon = new SqlConnection("Server=SQLsrv1;Database=DB1;Trusted_Connection=True;");
                sqlAdp = new SqlDataAdapter("SELECT * FROM [Table1]", sqlCon);
                sqlCon.Open();
                sqlAdp.Fill(ds);
                sqlCon.Close();
                return ds;
            }
        }
     

    Lingaraj Mishra

    Thursday, December 29, 2011 3:57 PM
  • You are the man... I got rid of all the squigly lines, but I now get an error on line 1: "Could not create WebService"

    ????

    Man!!!


    Me
    Thursday, December 29, 2011 4:46 PM
  •  Look, a dataset is the most heaviest solution that you are sending as a
    container through WCF it's too heavy.
     
    Do do you know how to make a serializable object?
     public class Customer
    {
       public string Name {get; set;}
       public decimal Cost {get; set;}
       public bool IsCurrent {get; set;}
    }
     
    I assume that you know how to use an ADO.NET dataread to read the
    resultset that is created when you read a database table using ADO.NET
    and T-SQL against a SQL Server database.
     
     
     
    You can use the ordinal position 0 based to get the data out of a
    returned resultset.
     
    Or you can get the data from the returned resultset read by a datareader
    by using the table's column name.
     And in the reader loop you do this.
     
    public List<Customer> GetCustomer()
    {
       var customers = new List<Customer>();
       SQL commands to get the data.....
       while reader loop
      {
         var cust = new Customer();
        cust.Name =  Convert.ToString(reader["FirstName"]);
        cust.Cost =  Convert.ToDecimal(reader["Cost"]);
         ect ect
         customers.Add(cust);
      }
       return customers;
    }
     
     
     
    So, you return that List<T> of objects to a Web method that called the
    method to create and return the List<T> of objects.
     
    You then serialize the List<T> using a memory stream pointing to a
    "string variable"
     
     
    You then return the "string" of the serialized XML objects to the client.
     
    <WebMethod>
    public string ReturnStringXMLObjects()
    {
        do the stuff to get the data and load into a List<T>
        serialize the List<T> of objects to XML
        return the string of the XML objects
    }
     
    One example in this link shows what the XML will look like if you do it
    with List<T> of objects.
     
     
    Again, you don't make a file you make the XML in memory and then send it
    to a string variable and return the string variable of XML to the client.
     
    You don't need a WCF Web Service to this. You use a straight-up ASP.NET
    legacy/regular Web service to do it, because using a WCF Web service
    brings complication to the client, as opposed to a legacy ASP.NET Web
    service does not do it.
     
    Now, the client can address it as needed like maybe deserializing the
    XML back to a List<T> of objects on its side or reading the XML.
     
     
    Thursday, December 29, 2011 4:47 PM
  • Thanks darnold, but that's not the route I'm looking for right now. My data is only a couple of thousand records. And like I said earlier, I'm on the ameteur side of development, so I have little (actually NONE) experience with ADO.net. And although I can follow your train of thought, I am looking for the simplest solution at this time. Having said that, can you take a look at the code Lingaraj has been helping me with, and tell me what EXACTLY you think I should do.

    I only get ONE error with the code that Lingaraj provided me, and I think I'm close to a solution. But I still cannot get it to work.


    Me
    Thursday, December 29, 2011 5:02 PM
  • On 12/29/2011 12:02 PM, SharePoint Dude wrote:
    > Thanks darnold, but that's not the route I'm looking for right now. My
    > data is only a couple of thousand records. And like I said earlier, I'm
    > on the ameteur side of development, so I have little (actually NONE)
    > experience with ADO.net.
     
    You are using ADO.NET to even do what you are trying to do now, just
    another way of doing it is what you are doing.
     
    > And although I can follow your train of
    > thought, I am looking for the simplest solution at this time.
     
    What I am telling you is the simplest solution. You want the give the
    client XML back, then that's what you need to be doing it and not the
    dataset and datatable stuff that is outdated technology. I have consumed
    many Web services, 3rd party, and built some myself to be used by
    clients that was sending back XML not once have I sent back datasets and
    datatables nor have I consumed a Web service that was using datasets and
    datatables just straight-up XML.
     
    > Having
    > said that, can you take a look at the code Lingaraj has been helping me
    > with, and tell me what EXACTLY you think I should do.
     
    What I think that you should do is 1) abondon using datasets and
    datatables, and 2) abandon a ASP.NET WCF Web Service and use a ASP.NET
    legacy Web service.
     
    >
    > I only get ONE error with the code that Lingaraj provided me, and I
    > think I'm close to a solution. But I still cannot get it to work.
     
    Sorry, the code you are using and the technique of doing it with ADO.NET
    I have not seen in years.
     
     
    Thursday, December 29, 2011 5:28 PM
  • Darnold,

    Do you know where I can find a sample of such web service, in which I can just replace the variable and test it?


    Me
    Thursday, December 29, 2011 5:44 PM
  • On 12/29/2011 12:44 PM, SharePoint Dude wrote:
    > Darnold,
    >
    > Do you know where I can find a sample of such web service, in which I
    > can just replace the variable and test it?
    >
     
    You can forget about the security stuff in the link.
     
     
     
     
    The other thing you can do is use the dataset and datatable I'll assume
    that you know how to do that, build the XML documment in memory and
    populate it from the table and sent the XML document.
     
    That's even more simpler that what I first told you. Send back XML and
    not a dataset with datatable.
     
    Then have the client load the XML into an XML Document and read it.
     
    Thursday, December 29, 2011 6:46 PM
  • You can refer below two link too

    http://www.tutorialspoint.com/asp.net/asp.net_web_services.htm

    http://www.codeguru.com/csharp/csharp/cs_webservices/article.php/c19391

    If you are following First link than Just replace

    [WebMethod]
         public string HelloWorld()
         {
            return "Hello World";
         }

    with

    [WebMethod]
            public DataSet SAdataWebService()
            {
                SqlConnection sqlCon;
                SqlDataAdapter sqlAdp;
                DataSet ds = new DataSet();
                sqlCon = new SqlConnection("Server=SQLsrv1;Database=DB1;Trusted_Connection=True;");
                sqlAdp = new SqlDataAdapter("SELECT * FROM [Table1]", sqlCon);
                sqlCon.Open();
                sqlAdp.Fill(ds);
                sqlCon.Close();
                return ds;
            }

    And include below two lines at top of your namespace.

    System.Data;
    using System.Data.SqlClient;


    Lingaraj Mishra
    Friday, December 30, 2011 6:50 AM
  • Thanks Lingaraj, but that still did not work.

    Anyone knows of any templates out there where I can just change the variables (SQL, table names, connection string) and generate my own web service?


    Me
    Thursday, January 05, 2012 1:52 PM
  • i have problew with SqlConnection how can i know the address of my server because i try many possibilities and always i can't connect please help me 
    Thursday, July 11, 2013 10:51 AM