SQLDataAdapter from 2 tables

  • Thread starter Thread starter Alan T
  • Start date Start date
A

Alan T

I want to get records by joining 2 tables:
SqlDataAdapter da = new SqlDataAdapter
("select p.ID, p.Name, c.Name from Product p left join Category c on p.catID
= c.ID");
DataSet ds = new DataSet();
da.Fill(ds, ....);

How do I fill the dataset ?
Or is there a better way to do that?
 
No the way you are doing it is fine.
You will get one table as a result, which contains the columns you
specified in your query.

I don't see where you specified your connection though.

To simplify, use a DataTable instead of a dataset.

DataTable dt = new DataTable("MyResults");
da.Fill(dt);
 
Sorry, are you saying :

DataTable dt = new DataTable("MyResults");

I can use "MyResult" or "YourResult" ?
I thought I need to fill in the real name of the 'table', like
DataTable dt = new DataTable("Product");
 
BTW, is there any differences in executing a stored procedure ?
The stored procedure will return a record set by 'Select ....'
 
A stored procedure is more efficient because it uses the RPC path through SQL
Server, can be compiled and cache parameters, and is more immune to hacking
attacks. Otherwise, no difference. If your SQLCommand is
CommandType.StoredProcedure and you have any required SqlParameters set, you
are "good to go" with the same DataAdapter.Fill method.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com
 
Peter said:
A stored procedure is more efficient because it uses the RPC path through SQL
Server, can be compiled
Click to expand...

What do you mean "can be compiled"? Everything _must_ be compiled in order
to be executed on SQL Server.
and cache parameters,
Click to expand...

If you mean "cache execution plan generated for specific parameters",
this may as well be bad rather than good. And again, this point applies
to all queries, not only stored procedures.
and is more immune to hacking attacks.
Click to expand...

Is parameterized query any less immune to hacking attack?
Otherwise, no difference.
Click to expand...

I would add, some code will now be on the SQL Server side, which
may have advantages in maintenance.
 
Hi Alan,

You are joining data so the table name would be neither one or the
other.
When naming a DataTable you can call it whatever you want, or even not
name it at all.
DataTable dt = new
DataTable("CombinationOfProductAndServiceAndUserTables");

A datatable is just an in memory representation of your result set.
..NET uses a 'disconnected architecture' where there is no formal
relationship between the data in your database and the data in your
application.
Thus, call the data table whatever you want. Good naming conventions
recommended of course. Most would argue that if your adapter was only
returning data from the 'Product' table that you should call your data
table 'Product'...
 
Back
Top