Datasheet

if (SortField == String.Empty)
CommandText = "select * from Suppliers order by CompanyName";
else
CommandText = "select * from Suppliers order by " + SortField;
These commands use SQL statements, but we could equally have used stored queries or stored
procedures. In practice, you should use stored queries, but using SQL directly here means we don't have
to create the stored query – since we're concentrating on ASP.NET we don't want to distract ourselves
with the stored procedure. We'll be looking at stored procedures later in the chapter.
Now we come to the part where we connect to the database. Don't worry too much about this
code – although we are going to explain it, we're not going to go into too much detail in this section, as
we'll be going over the theory later. To define the connection we use an
OleDbConnection object, and as
part of the instantiation we pass in the connection string details. This tells ASP.NET which database to
connect to, but doesn't actually open the connection. It defines where to connect to when we are ready to
connect:
OleDbConnection myConnection = new OleDbConnection(ConnectionString);
Now we use an OleDbDataAdapter to define the command to run – this will be the SELECT query to
fetch the data. The data adapter performs two functions. It provides the link between the database and
the
DataSet. It is also how data is fetched from and sent to the database (we'll be looking at the
DataAdapter in detail in the next chapter). The two arguments we pass in are the command text to run
the SQL statement, and the connection object. These define which command to run and which database
to run it against:
OleDbDataAdapter myCommand = new OleDbDataAdapter(CommandText,
myConnection);
Note that we still haven't connected to the database and fetched any data, as we've nowhere to store that
data. For that we use a
DataSet object, which you can think of as a mini database (it's not actually a
mini database, but that descriptions works well for the moment). It provides a place for the data to be
held while we manipulate it:
DataSet ds = new DataSet();
Now that we have all of the pieces in place (the connection, the command to run, and a place to put the
data), we can go ahead and fetch the data. For that we use the
Fill() method of the data adapter,
passing in the
DataSet. This opens the database connection, runs the command, places the data into the
DataSet, and then closes the database connection.
myCommand.Fill(ds);
The data is now in our DataSet so we can use it as the DataSource for the grid, and bind the grid:
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
}
This may look like a complex set of procedures, but it's actually a simple set of steps that is used many
times when you need to fetch data. You'll be seeing this many times during this book, and we'll go over
its theory later so you really understand what's happening. For now though, let's look at another way to
save time, by using the Web Matrix Code Wizards.
261
Reading from Databases
57084_08.qxp 30/01/2004 8:03 PM Page 261