0

I have attached WCF service to my sample website.
WCF functions work fine except one which use SQL query to get data from SQL server.
I'm not very familiar with VB.NET, my experience comes from VBA, it's similar but setting query is quite different.

At first I tried to use SqlDataReader then SqlDataAdapter - The same results. My Service has stuck.
Visual Studio shows error that SQL Server can't pass data because there is an internal error.
This is strange because when I use "WCF Test Client" in Visual Studio, then both functions work good and receive correct data. Also when I have attached this functions directly to my website also worked good. The problem is using them by WCF.

Below is my function with SQLDataAdapter

Public Function GetCookiesPriceDS(ByVal nameOfCookie As String) _
    As DataSet Implements IService1.GetCookiesPriceDS

    Dim queryString As String
    Dim dataSet As DataSet = New DataSet("temporary")
    queryString = "select CookiesPrice from " &
    "tblCookies where CookiesName='" & nameOfCookie & "'"
    Using connection As New SqlConnection _
        ("Server= xyz\SQLEXPRESS; Database = Cookies2; " &
        "Integrated Security = true;User Id = xyz;Password = xyz")
        Dim adapter As New SqlDataAdapter()
        adapter.SelectCommand = New SqlCommand(queryString, connection)
        adapter.Fill(dataSet)
        Return dataSet
    End Using
End Function
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
John
  • 31
  • 4
  • 1
    Your connection string specifies that integrated security should be used but it also specifies user credentials, which doesn't make sense. Do you want to use the credentials you're providing or log in as the user the service is running under? – jmcilhinney Aug 06 '17 at 14:46
  • Also, your code is vulnerable to SQL injection attacks. Use parameterized queries. one more thing - SqlDataAdapter and SqlCommand both implement the IDisposable interface, you should be using them also in a using statement. One last tip - and this is my own personal opinion - Don't go to vb.net. Go to c#. The similarities in syntax is only going to confuse you since vb.Net works on a very different concept then VBA. – Zohar Peled Aug 06 '17 at 15:16
  • @jmcilhinney ok deleted user and password info from string. Thx guys for tips, but still I have no idea how to solve this problem:( – John Aug 06 '17 at 15:41
  • You don't get an error message back at all? – Emmie Lewis-Briggman Aug 06 '17 at 17:00
  • So, are you saying, without actually saying, that you do want to connect to the database using the same account that the service is running under? What happens if you call `Open` on the connection object before calling `Fill`? `Fill` will implicitly open and close the connection so doing it explicitly will indicate whether it's the connection or the query that is at issue. – jmcilhinney Aug 06 '17 at 17:01
  • Also, have you checked the `InnerException` for more detail? – jmcilhinney Aug 06 '17 at 17:01
  • @jmcilhinney i know it will sound strange, but yes this service is only for me, I want to check some possibilities, so that's why everything is written for my credential. In the past I used dao.recordset in ms access to connect the database, this is totally different way, so can you show me how it should look like? – John Aug 06 '17 at 17:18
  • @jmcilhinney I did't wrote, but I'm still reading and I think my and are wrong. I defined only function in like Function GetCookiesPriceDS(ByVal nameOfCookie As String) As DataSet. But i think i should define as a class (because it is dataset) and then define this class in , am I right? – John Aug 06 '17 at 17:21
  • You should read [this SO post](https://stackoverflow.com/questions/25874224/why-returning-dataset-or-data-table-from-wcf-service-is-not-a-good-practice-wha) (and it's comments, and it's answers) – Zohar Peled Aug 06 '17 at 17:24
  • @ZoharPeled thank you also for your insight, but this comments are mostly about C# i need something in vb.net, I know that dataset it is not a best way but according to my knowledge and considering the time it's only way at the moment for me. I want to get from sql really small amount of data from one table:) – John Aug 06 '17 at 17:32
  • You wrote the problem is only when using this method in WCF, so I'm assuming the code itself is not the problem here. my next step was simply checking if it's even possible to return a dataset from a WCF service... while it's probably still possible (the post I've linked to was written almost 3 years ago) It just might be still the problem. Try to simply return `true` from your method to eliminate that suspicion. – Zohar Peled Aug 06 '17 at 17:47
  • @ZoharPeled Yes My method works even on WCF Test Client module (I click "invoke" and it shows correct result, but doesn't work If I try to use it via www. I think (I didn't shown in previous post) I had a wrong definition of ServiceContract and DataContract. Please see my new post below. But I think I don't understand what you mean "Try to simply return true from your method to eliminate that suspicion" – John Aug 06 '17 at 18:08

1 Answers1

1

Ok, I have found some sample code in vb.net and have done some small changes. It doesn't work because in "WCF Test Client" red symbol appears next to function, with comment - "This operation is not supported in WCF Test Client because it uses type WcfService.CookiesData" But despite that I think this code is much better than my previous version and has better definition in and . But still there is some problem and I can't figure out what is wrong


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

<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

John
  • 31
  • 4