DataSet - Table names

  • Thread starter Thread starter Emmanuel Gravino
  • Start date Start date
E

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?
 
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 said:
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?


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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?


.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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?


.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top