locked
Best way to pass list of SQL parameter to web service? RRS feed

  • 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. 

    Thursday, November 21, 2013 11:06 PM

Answers

  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 22, 2013 9:47 AM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 26, 2013 10:43 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 26, 2013 10:45 AM

All replies

  • 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. 

    Thursday, November 21, 2013 11:08 PM
  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 22, 2013 9:47 AM
  • 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. 

    Sunday, November 24, 2013 5:26 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 26, 2013 10:43 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 26, 2013 10:45 AM
  • User2108892867 posted

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

    Thursday, November 28, 2013 4:23 AM