Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Making a DataTable from a complex SELECT statement

Back when I was working in PHP, I could easily do some things I can't do in ASP .NET:


1. In PHP I could issue a complex SELECT statement which pulled data from several tables, and navigate through the data.

The ADO .NET components don't let me do this.

I can use a DataReader, but then I can only navigate forward.

Or, I can use a DataAdapter, DataSet, etc - but it won't let me enter a complex SELECT statement which pulls data from several tables.

Is there a simple way to do this in ADO .NET - use a complex SQL SELECT statement to pull data from many tables, and have that data put into a DataTable?

The fact that I can't do this is driving me crazy!


2. In PHP, I could issue INSERT and UPDATE statements any way I wanted - they were just sent to the database and executed.

In ADO .NET, I can't do this.

There is a special ADO .NET component for executing SQL statements, and it works for queries such as DELETE, but it does not accept INSERT or UPDATE!

Is there a way I can freely issue commands to the database using ADO .NET?

This is driving me crazy, too!


Thank you!

John Moss
Monday, October 27, 2003

I don't quite understand what you mean by "complex SELECT statement".  Are you talking about joins are multiple result sets?  Can you give an example of the sort of query you're talking about because either of these should work (with either DataReader or DataSet)? 

For freely issuing commands like INSERT and UPDATE, check out the IDbCommand implementation for whatever provider you are using (SqlCommand, OleDbCommand, etc.).

SomeBody
Monday, October 27, 2003

C#+SQL Server version:

SqlConnection Connection = new SqlConnection( MyConnectionString );

SqlDataAdapter MyDataAdapter = new SqlDataAdapter( new SqlCommand( "SELECT * FROM T1 INNER JOIN T2 ON T1.T2ID = T2.ID", Connection ));

DataSet MyDataSet = new DataSet();
MyDataAdapter.Fill( MyDataSet, "MyJoinedTableName");

I haven't actually tested the above code but that's the jist of it anyway.

For inserts and updates use the SqlCommand (or the oledb version) object.

Then read the rest of the chapter before making blaming the technology.

Paul
Wednesday, October 29, 2003

and:

DataTable myTable = new DataTable("MyJoinedTableName");
MyDataAdapter.Fill( myTable );

foreach(DataRow row in myTable.Rows)
{
    ...
}

Duncan Smart
Friday, October 31, 2003

In a nutshell:

- To get data from one or more tables and go through it
once, use DataReader (Connection-Command-ExecuteReader);
- To quickly update/delete/insert stuff, do Connection-Command-ExecuteNonQuery;
- To do something serious enough with your data on the
client (like navigate back and forth, sort/search, non-trivial
updates, bind windows form controls, etc.), use DataSet or
DataTable (Connection - Command - DataAdapter - Fill).

The only limitation with "complex Select statements" that I
can think of is probably inability to issue an Update
command for a DataAdapter built on a join - but that would
simply be a limitation of the table schema and would be
failing just as well regardless of the data access library or
server-side language used (PHP or no PHP).

- Mike.

Mike
Monday, December 01, 2003

*  Recent Topics

*  Fog Creek Home