CODEWITHSUNDEEP
c#.netsql-servervb.net
Eric Swiggum
Eric Swiggum

Reputation: 107

Fill the same dataset with different SQL Server stored procedures into datatables

I would like to fill a dataset from different SQL Server stored procedures (which each return 1 result set) into their own datatables within the dataset.

I don't really want to pass the SQL as text "exec sp1; exec sp2" ... I would like to use the cmd.CommandType = CommandType.StoredProcedure method like below, what is the best way of doing this?

Clear out the SqlDataAdapter and call it again to load it with the sp2 result set? Will it wipe out table(0)? I want it to fill table(1)

cnn.Open()
cmdSQL1 = New SqlCommand
cmdSQL1.Connection = cnn
cmdSQL1.CommandType = CommandType.StoredProcedure
cmdSQL1.CommandText = ("sp1")
cmdSQL2 = New SqlCommand
cmdSQL2.Connection = cnn
cmdSQL2.CommandType = CommandType.StoredProcedure
cmdSQL2.CommandText = ("sp2")

Dim da As New SqlDataAdapter(cmdSQL1)
da.Fill(ds)

edit 

da.Dispose()
da.SelectCommand = cmdSQL2
da.Fill(ds, "tab2")

dt = ds.Tables(0)
dt2 = ds.Tables(1)

Upvotes: 0

Views: 565

Answers (1)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

Keep It Simple. Just look at the example. 

Dim ds As New Data.DataSet() ' Dataset to fill
Using cnn As New Data.SqlClient.SqlConnection("my connection string")
    Dim cmd As New Data.SqlClient.SqlCommand("sp1", cnn) ' The only command
    cmd.CommandType = Data.CommandType.StoredProcedure
    cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@id", Data.DbType.Int32)).Value = 1234 ' parameters if we need some
    Dim dt As New Data.DataTable() 
    cnn.Open()
    dt.Load(cmd.ExecuteReader()) ' load dt from reader. DataAdapter in fact does the same
    ds.Tables.Add(dt) ' 0th tbl added
    cmd.CommandText = "sp2" ' prepare 2nd SP
    cmd.Parameters.Clear() ' no comment
    dt = New Data.DataTable() ' recreate dt
    dt.Load(cmd.ExecuteReader())
    ds.Tables.Add(dt) ' 1st tbl added
    cnn.Close() ' clean up your place
    cmd.Dispose() ' clean up your place
End Using

Upvotes: 1

Related Questions