DataSet data table name

  • Thread starter Thread starter Ron Harter
  • Start date Start date
R

Ron Harter

I have setup for select statements to retrieve data from 4 tables. When all
is said and done and I look for certain tables in the returned DataSet I am
unable to locate the table. When I dig into the DataSet I discovered the 4
tables had names of table, table1, table2, and table3.

I expected VS2008 sp1 and SQL Server 2008 developers edition to return the
proper table name from the data base. Has anyone seen this problem? Is it a
problem? I am enclosing the code snippet

DataSet ds = new DataSet();
DbProviderFactory dbProviderFactory =
DbProviderFactories.GetFactory("System.Data.SqlClient");

using (DbConnection conn = dbProviderFactory.CreateConnection())
{
string s =
ConfigurationManager.ConnectionStrings["Events"].ConnectionString;
conn.ConnectionString = s;
conn.Open();

DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * from FixedHolidays Select * from
FloatingHolidays Select * from LostBoats Select * from Events";

DbDataAdapter dbAdapter = dbProviderFactory.CreateDataAdapter();
dbAdapter.SelectCommand = cmd;
dbAdapter.Fill(ds);

foreach (DataTable t in ds.Tables)
{
Console.WriteLine("Table " + t.TableName + " is in DataSet");
}
}
 
What happens when "Table1" is the result of a join Select

Example:
Select d.DeptID , d.DeptName , e.EmpID , e.LastName, e.FirstName
from
dbo.Dept d join dbo.Employee e on d.DeptID = e.DeptID


???
What should the table name be here?


Aka, you can't just assume a table name.
.............

Have you tried to create a (strong) (or typed) DataSet? And populate it?

PS
http://msdn.microsoft.com/en-us/library/bb748727.aspx


Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet
provides a string array parameter to name your tables
Public Overridable Sub LoadDataSet ( _
storedProcedureName As String, _
dataSet As DataSet, _
tableNames As String(), _
ParamArray parameterValues As Object() _
)



public virtual void LoadDataSet (
string storedProcedureName,
DataSet dataSet,
string[] tableNames,
params Object[] parameterValues
)



If you have a strong dataset

Lets say my strong dataset is named MyStrongDS, with 2 tables.
Department
Employee

The code (partial code sample) would look like this:


MyStrongDS returnDS = new MyStrongDS();
db.LoadDataSet(dbc, returnDS, new String[] { "Department" ,
Employee" });

Actually, I prefer this (below)
This would avoid accidently giving a bad name via a string.


MyStrongDS returnDS = new MyStrongDS();
db.LoadDataSet(dbc, returnDS, new String[] {
returnDS.Department.TableName, returnDS.Employee.TableName });


If I ever remove or rename a table in MyStrongDataSet, the consequences show
up immediately when I try to build.





Ron Harter said:
I have setup for select statements to retrieve data from 4 tables. When
all is said and done and I look for certain tables in the returned DataSet
I am unable to locate the table. When I dig into the DataSet I discovered
the 4 tables had names of table, table1, table2, and table3.

I expected VS2008 sp1 and SQL Server 2008 developers edition to return the
proper table name from the data base. Has anyone seen this problem? Is it
a problem? I am enclosing the code snippet

DataSet ds = new DataSet();
DbProviderFactory dbProviderFactory =
DbProviderFactories.GetFactory("System.Data.SqlClient");

using (DbConnection conn = dbProviderFactory.CreateConnection())
{
string s =
ConfigurationManager.ConnectionStrings["Events"].ConnectionString;
conn.ConnectionString = s;
conn.Open();

DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * from FixedHolidays Select * from
FloatingHolidays Select * from LostBoats Select * from Events";

DbDataAdapter dbAdapter = dbProviderFactory.CreateDataAdapter();
dbAdapter.SelectCommand = cmd;
dbAdapter.Fill(ds);

foreach (DataTable t in ds.Tables)
{
Console.WriteLine("Table " + t.TableName + " is in DataSet");
}
}
Click to expand...
 
Thanks, the mappings worked perfectly.


Cor Ligthert said:
Hi Ron,

Have a look at mappings

http://msdn.microsoft.com/en-us/library/ks92fwwh.aspx

Cor


Ron Harter said:
I have setup for select statements to retrieve data from 4 tables. When
all is said and done and I look for certain tables in the returned
DataSet I am unable to locate the table. When I dig into the DataSet I
discovered the 4 tables had names of table, table1, table2, and table3.

I expected VS2008 sp1 and SQL Server 2008 developers edition to return
the proper table name from the data base. Has anyone seen this problem?
Is it a problem? I am enclosing the code snippet

DataSet ds = new DataSet();
DbProviderFactory dbProviderFactory =
DbProviderFactories.GetFactory("System.Data.SqlClient");

using (DbConnection conn = dbProviderFactory.CreateConnection())
{
string s =
ConfigurationManager.ConnectionStrings["Events"].ConnectionString;
conn.ConnectionString = s;
conn.Open();

DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * from FixedHolidays Select * from
FloatingHolidays Select * from LostBoats Select * from Events";

DbDataAdapter dbAdapter = dbProviderFactory.CreateDataAdapter();
dbAdapter.SelectCommand = cmd;
dbAdapter.Fill(ds);

foreach (DataTable t in ds.Tables)
{
Console.WriteLine("Table " + t.TableName + " is in DataSet");
}
}
Click to expand...
Click to expand...
 
Back
Top