none
Multiple Result Sets - dynamic SQL RRS feed

  • Question

  • Should I be able to pass procedure code like that shown below and get back multiple results sets via a Reader? Or?

    If I run the SQL below in the query tools (SQL Server 2008 R2) I get three result sets. If I try to pass the SQL dynamically I get a single Null set. (The SQL won't run for anyone else as it is dependent on my local DB. But trust me that in the Query tool I get back two scalars and a table.)

    DECLARE @gParcel geography;
    SET @gParcel = (
    	SELECT Geog
    	FROM Parcels_Lite 
    	WHERE APN = '053-063-017')
    SELECT @gParcel.STAsText() AS ParcelWKT; --returns WKT for parcel
    
    DECLARE @geogBuffer as Geography;
    SET @geogBuffer = (SELECT @gParcel.STBuffer(1000*0.3048));
    SELECT @geogBuffer.ToString() Buffer_1000WKT; -- returns WKT for 1000' buffer
    
    -- return APNs within 1000'
    SELECT T1.APN APNs_Within_1000
    FROM Parcels_Lite T1
    WHERE LEFT(T1.APN,3) IN ( -- narrow scope to books with 1000' vs all parcels
      SELECT AS_BOOK 
      FROM ASR_MAP_BOOKS 
      WHERE geog.STIntersects(@geogBuffer)=1)
    AND T1.geog.STIntersects(@geogBuffer)=1; 
    
    Tuesday, August 24, 2010 5:30 PM

Answers

  • Yes, you can do this. I typically use a SqlDataAdapter and the .Fill() method to fill a DataSet. With that code as your Select, your DataSet would contain 3 tables after the .Fill() executes. I prefer to use Stored Procs as well, but you don't have to.

    Can you show us the code you've tried to use and what problems you had with it?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Bob Heitzman Thursday, August 26, 2010 4:32 PM
    Thursday, August 26, 2010 4:45 AM

All replies

  • You showed us your SQL code, but not your .NET code.  That might help.

    Wednesday, August 25, 2010 11:28 PM
  • Yes, you can do this. I typically use a SqlDataAdapter and the .Fill() method to fill a DataSet. With that code as your Select, your DataSet would contain 3 tables after the .Fill() executes. I prefer to use Stored Procs as well, but you don't have to.

    Can you show us the code you've tried to use and what problems you had with it?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Bob Heitzman Thursday, August 26, 2010 4:32 PM
    Thursday, August 26, 2010 4:45 AM
  • Found the problem... I had comments interspersed and they interfered with the SQL. I bet if I ended each line with vbCrLf it would have worked....

    Private Sub btnParcelsInArea_Click(ByVal sender As System.ObjectByVal e As System.EventArgsHandles btnParcelsInArea.Click
            Dim SQL As String = "DECLARE @gParcel geography;"
            SQL &= " SET @gParcel = ("
            SQL &= "  SELECT Geog"
            SQL &= "  FROM Parcels_Lite "
            SQL &= "  WHERE APN = '053-063-017')"
            SQL &= " SELECT @gParcel.STAsText() AS ParcelWKT;" ' --returns WKT for parcel"
            SQL &= " "
            SQL &= " DECLARE @geogBuffer as Geography;"
            SQL &= " SET @geogBuffer = (SELECT @gParcel.STBuffer(1000*0.3048));"
            SQL &= " SELECT @geogBuffer.ToString() Buffer_1000WKT;" ' -- returns WKT for 1000' buffer"
            SQL &= " "
            'Sql  &= " -- return APNs within 1000'"
            SQL &= " SELECT T1.APN APNs_Within_1000"
            SQL &= " FROM Parcels_Lite T1"
            SQL &= " WHERE LEFT(T1.APN,3) IN (" ' -- narrow scope to books with 1000' vs all parcels"
            SQL &= "   SELECT AS_BOOK "
            SQL &= "   FROM ASR_MAP_BOOKS "
            SQL &= "   WHERE geog.STIntersects(@geogBuffer)=1)"
            SQL &= " AND T1.geog.STIntersects(@geogBuffer)=1; "

            Clipboard.SetText(SQL)

            Dim con As New System.Data.SqlClient.SqlConnection(ParcelsConnectString)
           
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            da = New SqlDataAdapter(SQL, con)
            ds = New DataSet()
            da.Fill(ds)
            con.Close()

            Debug.Print(ds.Tables.Count) ' = 3
            Debug.Print(ds.Tables(0).Rows(0)(0)) ' returns data from first select
            Debug.Print(ds.Tables(1).Rows(0)(0))
            Debug.Print(ds.Tables(2).Rows.Count)
            Stop
        End Sub
    3
    POLYGON ((-120.6443039107031 35.259331196362922, -120.64482119...
    POLYGON ((-120.64795624253324 35.258213283316969, -120.6478404...
    174
    Thursday, August 26, 2010 4:31 PM
  • Glad you figured out the problem. I didn't realize that comments would screw things up. Interesting ...

    I also have a couple of comments about your code. Perhaps what you have written is not "production" code and just for your own testing, but just in case you were unaware:

    1) You're better off using a StringBuilder if you're going to be adding that many strings together. I don't bother with StringBuilder when I have just a couple, but you have a lot.

    2) The SqlDataAdapter.Fill() method takes care of opening/closing your SqlConnection ... if it was already open, it leaves it open. If it wasn't open, it opens it and then closes it after the DataSet is filled. Consequently, that bit of code can be reduced to this:

    Dim ds As DataSet = New DataSet()    
    Dim da As SqlDataAdapter = New SqlDataAdapter(SQL, ParcelsConnectString)    
    da.Fill(ds)
    

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, August 26, 2010 5:24 PM
  • Thanks!

    If I would have used /* */ it would have been OK I would guess.

    The SQL string was from one of my utilities that turns the clipboard text to a series of assignements so I can easily move SQL from the SQL manager to VB. Thanks for the pointer on the StringBuilder.

    2) Has ths always been the case for ADO.Net? I did a lot of code in ADO so I go in the habit of using .Open.

    Monday, August 30, 2010 3:12 PM
  • If I would have used /* */ it would have been OK I would guess.

    Still, I'm fairly certain I've run SQL commands that had the -- comments in them without problem ... however, I believe it was using .ExecuteNonQuery(), not using .Fill() ... perhaps that also had something to do with it.

    The SQL string was from one of my utilities that turns the clipboard text to a series of assignements so I can easily move SQL from the SQL manager to VB. Thanks for the pointer on the StringBuilder.

    Handy little utility! If it's one you wrote yourself, you might as well switch to using StringBuilder. If not, there's no problem with adding to strings like you did other than it consumes more memory, which may or may not be an issue for you at that point.

    2) Has ths always been the case for ADO.Net? I did a lot of code in ADO so I go in the habit of using .Open.
    Yep, going all the way back to the old days of 1.0. However, this is the case *only* with the .Fill() method ... all others (such as .ExecuteNonQuery()) require that you manage the opening/closing of the connection yourself.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, August 30, 2010 3:36 PM
  • FYI

    Using the full SQL assignment above:

    worked CrLf delimiter:
    SQL &= " SELECT @gParcel.STAsText() AS ParcelWKT --returns WKT for parcel;" & vbCrLf

    worked /* style */:
    SQL &= " SELECT @gParcel.STAsText() AS ParcelWKT /*returns WKT for parcel*/;"

    Didn't - work I assume the rest of the SQL was treated as a comment:
    SQL &= " SELECT @gParcel.STAsText() AS ParcelWKT --returns WKT for parcel;"

    =============

    Tried the same think using a Reader (see code below) which may or may not perform better... Of course there would need to be defensive code added to deal with data dependent problems (like no records.)

            Using con As New System.Data.SqlClient.SqlConnection(ParcelsConnectString)
                Dim cmd As New SqlCommand(SQL, con)
                con.Open()
                Dim rdr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                Dim dt As New DataTable
                rdr.Read()
                Debug.Print(rdr(0).ToString) ' scalar
                rdr.NextResult()
                rdr.Read()
                Debug.Print(rdr(0)) ' scalar
                rdr.NextResult()
                dt.Load(rdr) ' row set
                rdr = Nothing
                Debug.Print(dt.Rows.Count)
            End Using

    =========

    Here's the clipboard to assignment "robo code":

        Sub MultilineStringToStringAssignment(Optional ByVal longString As String = "")
            If longString.Length = 0 Then longString = Clipboard.GetText
            Dim s As String = "SQL=""" & longString.Replace(vbCrLf, """" & vbCrLf & "SQL &= "" ")
            Clipboard.SetText(s)
            Stop
        End Sub
    Just breaks the line on the clipboard at CrLF. Should be a macro but I haven't got them to work yet....
    Monday, August 30, 2010 9:43 PM
  • Ah yes, you definitely needed the CR/LF after those inline comments. It makes sense.

    I'd rather use a DataAdapter and .Fill() a DataSet that way than use a DataReader ... but, that's just my preference. I think it looks  a lot cleaner than all the hoops you have to jump through for a DataReader. 

    Performance-wise, supposedly a DataReader is supposed to be faster ... but I have no idea where that "break-even" point is (how many rows you're reading).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, September 1, 2010 5:08 AM
  • DataAdapter vs DataReader

    The vast majority of my DB trips are for lookups so I use the DataReader in some library routines (GetDataTable below.) Here's some typical support code:

    Function GetScalar(ByVal SQL As StringOptional ByVal RtnZeroLenghtString As Boolean = FalseOptional ByVal ConnectString As String = ""As String
            Dim s As String = ""
            If ConnectString.Length = 0 Then ConnectString = g.OISConnectString
            Dim con As New SqlConnection(ConnectString)
            con.Open()
            Dim cmd As New SqlCommand(SQL, con)
            Try
                s = cmd.ExecuteScalar.ToString & ""
            Catch ex As Exception
                If RtnZeroLenghtString Then ' failure OK
                    s = ""
                Else
                    MsgBox("Unexpected error (GetScalar):" & vbCrLf & ex.Message & vbCrLf & "SQL: " & SQL & vbCrLf & "Notify Programmers")
                End If
            Finally
                cmd = Nothing
                con.Close()
                con = Nothing
            End Try
            GetScalar = s
        End Function
        Function GetDataRow(ByVal SQL As StringOptional ByVal ConnectString As String = ""As DataRow ' returns read only Datarow
            Try
                Dim dt As DataTable
                dt = GetDataTable(SQL)
                If dt.Rows.Count = 0 Then
                    Return Nothing
                Else
                    Return dt.Rows(0)
                End If
            Catch ex As Exception
                MsgBox(ex.Message, , "GetDataRow")
                Return Nothing
            End Try
        End Function
        Function GetDataTable(ByVal SQL As StringOptional ByVal ConnectString As String = ""As DataTable ' returns read only Datatable
            Try
                If ConnectString.Length = 0 Then ConnectString = OISConnectString()
                Dim con As New System.Data.SqlClient.SqlConnection(ConnectString)
                Dim cmd As New SqlCommand(SQL, con)
                con.Open()
                Dim rdr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                Dim dt As New DataTable
                dt.Load(rdr)
                rdr = Nothing
                Return dt
            Catch ex As Exception
                MsgBox(ex.Message, , "GetDataTable")
                Return Nothing
            End Try
        End Function
        Function GetDataView(ByVal SQL As StringOptional ByVal ConnectString As String = ""As DataView ' returns read only Datarow
            Try
                Dim dt As DataTable
                dt = GetDataTable(SQL, ConnectString)
                If dt.Rows.Count = 0 Then
                    Return Nothing
                Else
                    Dim dv As New DataView(dt)
                    Return dv
                End If
            Catch ex2 As NullReferenceException
                Return Nothing
            Catch ex As Exception
                MsgBox(ex.Message, , "GetDataRow")
                Return Nothing
            End Try
        End Function
    Wednesday, September 1, 2010 6:22 PM