none
Problem with WCF Service in VB.NET using ADO.Net in connection to SQL Server RRS feed

  • Question

  •  I have WCF service "WcfService" with few functions, they are working good, except my one function which is responsible for getting data from sql server. I'm not good familiar with vb.net, my experience comes from vba. I found some code on the Internet and did some small changes, but function doesn't work. I think the problem is wrong definition in ServiceContract() and DataContract(). WCF Test Client in Visual Studio Express for Web 2015 shows me this error description "This operation is no supported in the WCF Test Client because it uses type WcfService Cookies Data"

    IService1.vb.

    <ServiceContract()>
            Public Interface IService1
              <OperationContract()>
              Function GetCookiesPriceDS(ByVal nameOfCookie As String) As CookiesData
            End Interface
        
        <DataContract()>
        Public Class CookiesData
            Public Sub New()
                CustomersTable = New DataTable("TblCookies")
            End Sub
        
            <DataMember()>
             Public Property CustomersTable() As DataTable
        End Class

    Service1.scv
    Imports System.Data.SqlClient
        Imports System.Data
        Imports System.Configuration
        Imports System.ServiceModel
        Imports System.Runtime.Serialization
        Public Class Service1
            Implements IService1
        Public Function GetCookiesPriceDS(ByVal nameOfCookie As String) _
                As CookiesData Implements IService1.GetCookiesPriceDS
        
                Using con As New SqlConnection("Server= xyz\SQLEXPRESS; Database = Cookies2;" &
                    " Integrated Security = true;")
                    Using cmd As New SqlCommand("select CookiesPrice from tblCookies where " &
                                                "CookiesName='" & nameOfCookie & "'")
                        Using sda As New SqlDataAdapter()
                            cmd.Connection = con
                            sda.SelectCommand = cmd
                            Using dt As New DataTable()
                                Dim ck As New CookiesData()
                                sda.Fill(ck.CustomersTable)
                                Return ck
                            End Using
                        End Using
                    End Using
                End Using
            End Function
        End Class

    Monday, August 7, 2017 10:33 AM

All replies

  • https://docs.microsoft.com/en-us/dotnet/framework/wcf/getting-started-tutorial

    Maybe you need to learn the basics of WCF before jumping off trying to implement a SQL query in it.

    Monday, August 7, 2017 6:58 PM
  • Hi Radek,

    I made a test and I could get the same result.

    In my option, this is the limitation of WCF Test client, it could not be used for custom type.

    You need to create your own client, as my test, there is no problem in your code. You could follow below steps to test your code.

    1.Right Click Service1.svc, and View in Browser

    2.Create a client like console application, Right click the project-> Add-> Add Service Reference->Paste the URL from above step

    3.Enter the Namespace and press OK

    4.Test the method with below code. (it is in C#, you could implement the same code in VB.NET)

    5.Run both projects, and test the function.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 8, 2017 2:29 AM
  • Or ... another option is to make a few changes:

    Instead of

    Return ck

    You could change your Function signature to return a string instead of CookiesData (and, obviously change it in your OperationContract too), and then return the xml of the DataSet in your Function, like this:

    Function GetCookiesPriceDS(ByVal nameOfCookie As String) As String

    and then return:

    Return ck.GetXml()

    That will return the DataSet as an XML string.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, August 12, 2017 4:31 PM
    Moderator