Best way to pass list of SQL parameter to web service?

Question

User2108892867 posted

Hello everyone I have this function:

Public Function PopulateDataSet(ByVal spName As String, ByVal location_id As String) As DataSet
        Dim cnn As SqlConnection
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ToString
        cnn = New SqlConnection(conString)
        Dim cmd As New SqlCommand(spName, cnn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        cmd.Parameters.AddWithValue("@location_id", location_id)
        Try
            If cnn.State = System.Data.ConnectionState.Closed Then
                cnn.Open()
            End If
            da.Fill(ds)
        Finally
            cnn.Close()
            cnn.Dispose()
        End Try
        Return ds
    End Function

As you see, this has limitation since I need to specify the name of parameter (@location_id). As a result, my function is not reuseable. Is there a way, I can replace the parameter location_id with a list (something similar to dataset) and then I can process the list and create the parameters accordingly.

I have read articles and it is saying it is not good to pass a dataset as parameter to web service. So what is the best way to do this?

Please let me know if my question is not clear.

Thanks. 

Answer 1

User2108892867 posted

This is a much more reuseable function:

Public Shared Function PoulateDataset(ByVal spName As String, ByVal spParameter As SqlParameter) As DataSet
        Dim cnn As SqlConnection
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ToString
        cnn = New SqlConnection(conString)
        Dim cmd As New SqlCommand(spName, cnn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        If spParameter IsNot Nothing Then
            da.SelectCommand.Parameters.Add(spParameter)
        End If
        Try
            cnn.Open()
            da.Fill(ds)
        Finally
            cnn.Close()
            cnn.Dispose()
        End Try
        Return ds
    End Function

But web service doesn't accept sql parameter as a parameter. So this doesn't work.

Thanks. 

Answer 2

User-488622176 posted

SqlParameter belongs to your data access layer and should not be passed to your service. 

You can use DTO's to pass info between layers. See the "WCF by example" series here : http://www.codeproject.com/KB/architecture/#Patterns+and+Practices, starting at http://www.codeproject.com/Articles/90060/WCF-by-Example-Chapter-I-Baseline 

Answer 3

User2108892867 posted

Thanks, Illeris for the link. I will need to do some more research cause it is too advanced for me to understand. By the way, I found this link: http://stackoverflow.com/questions/4966189/can-we-pass-dataset-to-a-web-service-method-if-yes-then-how

It said it is not recommended to pass a dataset to a web service. One of the forumer suggests this:

"it would be a better idea to write some basic classes that represent your model, and expose those to the web service, as they can be properly represented in xsd for SOAP purposes."

Any links you could share regarding the above suggestion? How can this be done? 

Thanks. 

Answer 4

User-488622176 posted

These basic classes are ... DTO's :-).  Make a class with a few public properties. Each class can represent a key-value referring to a parameter & value in your query. You pass a list of such classes to the service method.

Answer 5

User-488622176 posted

Public Class MyObject
  Public MyParamName as String
  Public MyParamType as String
  Public MyParamValue as object

End Class

Public Shared Function PoulateDataset(ByVal spName As String, ByVal myParam as MyObject) As DataSet
  ' Add try-catch to handle errors
  Dim sp as SqlParameter(MyParamName, MyParamValue)
  sp.DataType = new System.Type(MyParamType)
  ' Sorry, I'm a C# specialist, not a VB.NET one :-)
End Function

Answer 6

User2108892867 posted

Thank you Illeris. Your solutions helps me understand a lot.