DataSet - Table names

[Emmanuel Gravino]

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 Gravino]

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?

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 Dollard]

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)

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?

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 Lang]

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

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?

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 Lang]

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

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)

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?

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?


.