DataSet - Table names

when using a dataadapter to fill a dataset from a query that returns
multiple tables such as:

Select col1, col2, col3 from tableA
Select col1, col2, col3 from tableB

the DataTable names inside the dataset will not be named with the actual
name.

Is there a way i can go around this?

Emmanuel
Wed Jul 30 05:18:21 CDT 2003

thanks.... but what if i wanted to have it automatically done? i.e. can't
the dataadapter or something else do it? is there any schema information
along with the datatables/dataset or any other property which i could used
AFTER the query is executed?




"VineetBatta" <Vineet.Batta@wipro.com> wrote in message
news:092601c3567f$7f090a00$a501280a@phx.gbl...
> it defaults to the name specified
>
> If the Query
>
> SELECT * FROM PRODUCTS
>
> //Fill the dataset object.
> sqlData.Fill(inboundTables,"ASD");
>
> So we can use
> DataView dv = ds.Tables["ASD"].DefaultView ;
>
> But if there are multiple Queries then next table name
> will default to ASD1 and so on.....
>
> But what u can do is that call all the subsequent using
> Command Object.
>
> The following code was tested.
>
> try
> {
> SqlConnection sqlConn =
> new SqlConnection("Initial Catalog=NorthWind;Data Source=
> [];User ID=[];Password=[];");
>
> //Open the Connection.
> sqlConn.Open();
>
> //TODO: DataReader can be
> used for faster access.
> DataSet inboundTables =
> new DataSet();
>
>
> string query = "SELECT *
> FROM PRODUCTS";
>
> SqlCommand sqlCmd = new
> SqlCommand();
> sqlCmd.Connection =
> sqlConn;
> sqlCmd.CommandText =
> query;
>
>
> //Create DataAdapter
> object and Attach the SqlCommand Object.
> SqlDataAdapter sqlData =
> new SqlDataAdapter(sqlCmd);
>
>
> //Fill the dataset object.
> sqlData.Fill
> (inboundTables,"PRODUCTS");
>
> query = "SELECT * FROM
> ORDERS";
> sqlCmd.CommandText =
> query;
>
> //Fill the dataset object.
> sqlData.Fill
> (inboundTables,"ORDERS");
>
> sqlConn.Close();
>
> return inboundTables;
> }
> catch(Exception ex)
> {
> throw ex;
> }
>
> -Vineet Batta
> MCAD
>
> VineetBatta
> >-----Original Message-----
> >when using a dataadapter to fill a dataset from a query
> that returns
> >multiple tables such as:
> >
> >Select col1, col2, col3 from tableA
> >Select col1, col2, col3 from tableB
> >
> >the DataTable names inside the dataset will not be named
> with the actual
> >name.
> >
> >Is there a way i can go around this?
> >
> >
> >.
> >

Michael
Wed Jul 30 10:27:25 CDT 2003

It cannot be done automatically from the SQL string. SQL strings can
contain more than one table name in the FROM clause.

The framework does supply a way to "rename" each table in your dataset so
you can access them by name. Actually it is a mapping not a renaming. To
do this use the TableMappings property of the DataAdapter before filling the
DataSet. Add a TableMapping for each table...

//Assuming...
// Select ... from Orders
// Select ... from OrderDetails
myDataAdapter.TableMappings.Add("Table1", "Orders");
myDataAdapter.TableMappings.Add("Table2", "OrderDetails");
myDataAdapter.Fill(myDataSet);
myDataTableOrders = myDataSet["Orders"];
myDataTableOrderDetails = myDataSet["OrderDetails"];

Michael Lang

"Emmanuel Gravino" <emanuelg@gfi.com> wrote in message
news:O90JlOoVDHA.1512@TK2MSFTNGP11.phx.gbl...
> thanks.... but what if i wanted to have it automatically done? i.e. can't
> the dataadapter or something else do it? is there any schema information
> along with the datatables/dataset or any other property which i could used
> AFTER the query is executed?
>
>
>
>
> "VineetBatta" <Vineet.Batta@wipro.com> wrote in message
> news:092601c3567f$7f090a00$a501280a@phx.gbl...
> > it defaults to the name specified
> >
> > If the Query
> >
> > SELECT * FROM PRODUCTS
> >
> > //Fill the dataset object.
> > sqlData.Fill(inboundTables,"ASD");
> >
> > So we can use
> > DataView dv = ds.Tables["ASD"].DefaultView ;
> >
> > But if there are multiple Queries then next table name
> > will default to ASD1 and so on.....
> >
> > But what u can do is that call all the subsequent using
> > Command Object.
> >
> > The following code was tested.
> >
> > try
> > {
> > SqlConnection sqlConn =
> > new SqlConnection("Initial Catalog=NorthWind;Data Source=
> > [];User ID=[];Password=[];");
> >
> > //Open the Connection.
> > sqlConn.Open();
> >
> > //TODO: DataReader can be
> > used for faster access.
> > DataSet inboundTables =
> > new DataSet();
> >
> >
> > string query = "SELECT *
> > FROM PRODUCTS";
> >
> > SqlCommand sqlCmd = new
> > SqlCommand();
> > sqlCmd.Connection =
> > sqlConn;
> > sqlCmd.CommandText =
> > query;
> >
> >
> > //Create DataAdapter
> > object and Attach the SqlCommand Object.
> > SqlDataAdapter sqlData =
> > new SqlDataAdapter(sqlCmd);
> >
> >
> > //Fill the dataset object.
> > sqlData.Fill
> > (inboundTables,"PRODUCTS");
> >
> > query = "SELECT * FROM
> > ORDERS";
> > sqlCmd.CommandText =
> > query;
> >
> > //Fill the dataset object.
> > sqlData.Fill
> > (inboundTables,"ORDERS");
> >
> > sqlConn.Close();
> >
> > return inboundTables;
> > }
> > catch(Exception ex)
> > {
> > throw ex;
> > }
> >
> > -Vineet Batta
> > MCAD
> >
> > VineetBatta
> > >-----Original Message-----
> > >when using a dataadapter to fill a dataset from a query
> > that returns
> > >multiple tables such as:
> > >
> > >Select col1, col2, col3 from tableA
> > >Select col1, col2, col3 from tableB
> > >
> > >the DataTable names inside the dataset will not be named
> > with the actual
> > >name.
> > >
> > >Is there a way i can go around this?
> > >
> > >
> > >.
> > >
>
>

Kathleen
Wed Jul 30 10:07:37 CDT 2003

Emmanuel,

In addition to the mapping Michael's suggestion, you can simply rename the
table.

ds("Table").TableName = "RealTableName"

Unfortunately you can not do this automatically, as Michael explained.

--
Kathleen (MVP-VB)



"Emmanuel Gravino" <emanuelg@gfi.com> wrote in message
news:O90JlOoVDHA.1512@TK2MSFTNGP11.phx.gbl...
> thanks.... but what if i wanted to have it automatically done? i.e. can't
> the dataadapter or something else do it? is there any schema information
> along with the datatables/dataset or any other property which i could used
> AFTER the query is executed?
>
>
>
>
> "VineetBatta" <Vineet.Batta@wipro.com> wrote in message
> news:092601c3567f$7f090a00$a501280a@phx.gbl...
> > it defaults to the name specified
> >
> > If the Query
> >
> > SELECT * FROM PRODUCTS
> >
> > //Fill the dataset object.
> > sqlData.Fill(inboundTables,"ASD");
> >
> > So we can use
> > DataView dv = ds.Tables["ASD"].DefaultView ;
> >
> > But if there are multiple Queries then next table name
> > will default to ASD1 and so on.....
> >
> > But what u can do is that call all the subsequent using
> > Command Object.
> >
> > The following code was tested.
> >
> > try
> > {
> > SqlConnection sqlConn =
> > new SqlConnection("Initial Catalog=NorthWind;Data Source=
> > [];User ID=[];Password=[];");
> >
> > //Open the Connection.
> > sqlConn.Open();
> >
> > //TODO: DataReader can be
> > used for faster access.
> > DataSet inboundTables =
> > new DataSet();
> >
> >
> > string query = "SELECT *
> > FROM PRODUCTS";
> >
> > SqlCommand sqlCmd = new
> > SqlCommand();
> > sqlCmd.Connection =
> > sqlConn;
> > sqlCmd.CommandText =
> > query;
> >
> >
> > //Create DataAdapter
> > object and Attach the SqlCommand Object.
> > SqlDataAdapter sqlData =
> > new SqlDataAdapter(sqlCmd);
> >
> >
> > //Fill the dataset object.
> > sqlData.Fill
> > (inboundTables,"PRODUCTS");
> >
> > query = "SELECT * FROM
> > ORDERS";
> > sqlCmd.CommandText =
> > query;
> >
> > //Fill the dataset object.
> > sqlData.Fill
> > (inboundTables,"ORDERS");
> >
> > sqlConn.Close();
> >
> > return inboundTables;
> > }
> > catch(Exception ex)
> > {
> > throw ex;
> > }
> >
> > -Vineet Batta
> > MCAD
> >
> > VineetBatta
> > >-----Original Message-----
> > >when using a dataadapter to fill a dataset from a query
> > that returns
> > >multiple tables such as:
> > >
> > >Select col1, col2, col3 from tableA
> > >Select col1, col2, col3 from tableB
> > >
> > >the DataTable names inside the dataset will not be named
> > with the actual
> > >name.
> > >
> > >Is there a way i can go around this?
> > >
> > >
> > >.
> > >
>
>

Michael
Wed Jul 30 12:02:16 CDT 2003

That is a problem. I tried that before I went to table mappings. I was
confused when the following caused an error.

ds("Table").TableName = "RealTableName"; //< -- no problem, it is writeable.
DataTable dt = ds("RealTableName"); //< -- fails

I was told that only a TableMapping will allow the line above to work.
Updating the TableName property of a DataTable does not update the key in
the container DataSet. Which makes sense. What collection lets you change
a key of an item after it is added?

Michael Lang, MCSD

"Kathleen Dollard" <kathleen@mvps.org> wrote in message
news:eY3LQxqVDHA.656@tk2msftngp13.phx.gbl...
> Emmanuel,
>
> In addition to the mapping Michael's suggestion, you can simply rename the
> table.
>
> ds("Table").TableName = "RealTableName"
>
> Unfortunately you can not do this automatically, as Michael explained.
>
> --
> Kathleen (MVP-VB)
>
>
>
> "Emmanuel Gravino" <emanuelg@gfi.com> wrote in message
> news:O90JlOoVDHA.1512@TK2MSFTNGP11.phx.gbl...
> > thanks.... but what if i wanted to have it automatically done? i.e.
can't
> > the dataadapter or something else do it? is there any schema information
> > along with the datatables/dataset or any other property which i could
used
> > AFTER the query is executed?
> >
> >
> >
> >
> > "VineetBatta" <Vineet.Batta@wipro.com> wrote in message
> > news:092601c3567f$7f090a00$a501280a@phx.gbl...
> > > it defaults to the name specified
> > >
> > > If the Query
> > >
> > > SELECT * FROM PRODUCTS
> > >
> > > //Fill the dataset object.
> > > sqlData.Fill(inboundTables,"ASD");
> > >
> > > So we can use
> > > DataView dv = ds.Tables["ASD"].DefaultView ;
> > >
> > > But if there are multiple Queries then next table name
> > > will default to ASD1 and so on.....
> > >
> > > But what u can do is that call all the subsequent using
> > > Command Object.
> > >
> > > The following code was tested.
> > >
> > > try
> > > {
> > > SqlConnection sqlConn =
> > > new SqlConnection("Initial Catalog=NorthWind;Data Source=
> > > [];User ID=[];Password=[];");
> > >
> > > //Open the Connection.
> > > sqlConn.Open();
> > >
> > > //TODO: DataReader can be
> > > used for faster access.
> > > DataSet inboundTables =
> > > new DataSet();
> > >
> > >
> > > string query = "SELECT *
> > > FROM PRODUCTS";
> > >
> > > SqlCommand sqlCmd = new
> > > SqlCommand();
> > > sqlCmd.Connection =
> > > sqlConn;
> > > sqlCmd.CommandText =
> > > query;
> > >
> > >
> > > //Create DataAdapter
> > > object and Attach the SqlCommand Object.
> > > SqlDataAdapter sqlData =
> > > new SqlDataAdapter(sqlCmd);
> > >
> > >
> > > //Fill the dataset object.
> > > sqlData.Fill
> > > (inboundTables,"PRODUCTS");
> > >
> > > query = "SELECT * FROM
> > > ORDERS";
> > > sqlCmd.CommandText =
> > > query;
> > >
> > > //Fill the dataset object.
> > > sqlData.Fill
> > > (inboundTables,"ORDERS");
> > >
> > > sqlConn.Close();
> > >
> > > return inboundTables;
> > > }
> > > catch(Exception ex)
> > > {
> > > throw ex;
> > > }
> > >
> > > -Vineet Batta
> > > MCAD
> > >
> > > VineetBatta
> > > >-----Original Message-----
> > > >when using a dataadapter to fill a dataset from a query
> > > that returns
> > > >multiple tables such as:
> > > >
> > > >Select col1, col2, col3 from tableA
> > > >Select col1, col2, col3 from tableB
> > > >
> > > >the DataTable names inside the dataset will not be named
> > > with the actual
> > > >name.
> > > >
> > > >Is there a way i can go around this?
> > > >
> > > >
> > > >.
> > > >
> >
> >
>
>