Strongly Typed DataSets with Multiple Tables

Strongly Typed DataSets with Multiple Tables

Post by Dan Orchar » Fri, 23 Aug 2002 09:20:42



Hi,

I am trying to fill a strongly typed dataset with the
result of a SQL stored procedure.  The stored procedure
returns 9 tables, and executes fine(in fact, the code
shown below works fine when the strongly typed dataset is
substituted for a plain DataSet).  All of the examples I
have seen on this subject rely on the fact that the Fill
method of the SQLDataAdapter is called with a table
name.  No exceptions are thrown when I try to do this,
but no records are returned.

    Public Function GenBaseDS() As STypedDataset

            Dim ds As New STypedDataset()
            Dim conn As New SqlConnection(connstr())
            conn.Open()

            Dim cmd As New SqlCommand("sp_SType", conn)
            cmd.CommandType = CommandType.StoredProcedure

            Dim adpt As New SqlDataAdapter(cmd)
            adpt.Fill(ds)
            conn.Close()

            Return ds
    End Function

There is a couple of made up functions for parameters in
here just to show my point.

I have been struggling with this for several hours, and
have exhaustively searched MSDN and the web to no avail.

Any help that you can provide will be greatly appreciated.

Thanks,

Dan

 
 
 

Strongly Typed DataSets with Multiple Tables

Post by Bill » Fri, 23 Aug 2002 11:26:16


Dan, I have lots of examples of executing multiple resulset stored
procedures in my book... here's one I whipped up for you just now. The  SP
is pretty simple (pardon the SELECT *, I was in a hurry).
The code returns 4 tables (with data) in both cases...with or without naming
the Tables in the Fill.

ALTER PROCEDURE MultipleResultsets
AS
 SELECT TOP 10 *  FROM Authors
 SELECT TOP 10 * FROM Customers
 SELECT TOP 10 * FROM Orders
 SELECT TOP 10 * FROM ITEMS
 RETURN
Dim cn As New SqlClient.SqlConnection()

Dim da As SqlClient.SqlDataAdapter

Dim ds As DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Try

cn.ConnectionString = "data
source=demoserver;database=biblio;uid=admin;pwd=pw"

da = New SqlDataAdapter("MultipleResultsets", cn)

da.SelectCommand.CommandType = CommandType.StoredProcedure

ds = New DataSet()

da.Fill(ds)

MsgBox("Tables created: " & ds.Tables.Count)

DataGrid1.DataSource = ds.Tables(0)

ds = New DataSet()

da.Fill(ds, "SomeName")

MsgBox("Tables created: " & ds.Tables.Count)

Catch ex As Exception

MsgBox(ex.ToString)

End Try

End Sub

--
William (Bill) Vaughn
Author, Trainer, Mentor
Microsoft Pacwest Regional Director
Beta V Corporation
"ADO and ADO.NET Examples and Best Practices for VB Programmers--2nd
Edition" (ISBN: 1-893115-68-2)
"ADO.NET Examples and Best Practices for C# Programmers" (ISBN:
1-590590-12-0)

www.betav.com

Please reply only to the newsgroup so that others can benefit. When posting,
This posting is provided "AS IS" with no warranties, and confers no rights.
________________________________


> Hi,

> I am trying to fill a strongly typed dataset with the
> result of a SQL stored procedure.  The stored procedure
> returns 9 tables, and executes fine(in fact, the code
> shown below works fine when the strongly typed dataset is
> substituted for a plain DataSet).  All of the examples I
> have seen on this subject rely on the fact that the Fill
> method of the SQLDataAdapter is called with a table
> name.  No exceptions are thrown when I try to do this,
> but no records are returned.

>     Public Function GenBaseDS() As STypedDataset

>             Dim ds As New STypedDataset()
>             Dim conn As New SqlConnection(connstr())
>             conn.Open()

>             Dim cmd As New SqlCommand("sp_SType", conn)
>             cmd.CommandType = CommandType.StoredProcedure

>             Dim adpt As New SqlDataAdapter(cmd)
>             adpt.Fill(ds)
>             conn.Close()

>             Return ds
>     End Function

> There is a couple of made up functions for parameters in
> here just to show my point.

> I have been struggling with this for several hours, and
> have exhaustively searched MSDN and the web to no avail.

> Any help that you can provide will be greatly appreciated.

> Thanks,

> Dan


 
 
 

Strongly Typed DataSets with Multiple Tables

Post by BurtonRobert » Fri, 23 Aug 2002 21:44:01


Dan:
For strongly typed datasets you need to use table mappings to be sure the
tables from your sproc fill the right tables in the dataset.
Tables, once they get into the adapter are named by default:  "Table",
"Table1", "Table2"......

Dim adpt As New SqlDataAdapter(cmd)
adpt.tablemappings.add("Table","YourTablename0")
adpt.tablemappings.add("Table1","YourTablename1")
.....
adpt.Fill(ds)

Burton Roberts


> Hi,

> I am trying to fill a strongly typed dataset with the
> result of a SQL stored procedure.  The stored procedure
> returns 9 tables, and executes fine(in fact, the code
> shown below works fine when the strongly typed dataset is
> substituted for a plain DataSet).  All of the examples I
> have seen on this subject rely on the fact that the Fill
> method of the SQLDataAdapter is called with a table
> name.  No exceptions are thrown when I try to do this,
> but no records are returned.

>     Public Function GenBaseDS() As STypedDataset

>             Dim ds As New STypedDataset()
>             Dim conn As New SqlConnection(connstr())
>             conn.Open()

>             Dim cmd As New SqlCommand("sp_SType", conn)
>             cmd.CommandType = CommandType.StoredProcedure

>             Dim adpt As New SqlDataAdapter(cmd)
>             adpt.Fill(ds)
>             conn.Close()

>             Return ds
>     End Function

> There is a couple of made up functions for parameters in
> here just to show my point.

> I have been struggling with this for several hours, and
> have exhaustively searched MSDN and the web to no avail.

> Any help that you can provide will be greatly appreciated.

> Thanks,

> Dan