Getting DATA from SQL Server by WCF Services using ADO.NET VB.NET

Question

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

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
– jmcilhinney, Commented Aug 6, 2017 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
– Zohar Peled, Commented Aug 6, 2017 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
– John, Commented Aug 6, 2017 at 15:41
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
– jmcilhinney, Commented Aug 6, 2017 at 17:01

John · Accepted Answer · 2017-08-06 18:09:06Z

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

Collectives™ on Stack Overflow

Getting DATA from SQL Server by WCF Services using ADO.NET VB.NET

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related