Database Reference
In-Depth Information
How It Works
The
GetName
method gets a column name by its index. This method returns information
about
the result
set, so it can be called before the first call to
Read()
.
// Get column names
lbltype1.Text = rdr.GetName(0);
lblType2.Text = rdr.GetName(1);
The
GetDataTypeName
method returns the database data type of a column. It too can be called before
the first call to
Read()
.
//Get column data types
lbltype1.Text += "\n"+ rdr.GetDataTypeName(0).ToString();
lblType2.Text += "\n"+ rdr.GetDataTypeName(1).ToString();
The
FieldCount
property of the data reader contains the number of columns in the result set. This is
useful for looping through columns without knowing their names or other attributes.
// Get number of columns
lblType3.Text = "Number of columns in a row::" + rdr.FieldCount.ToString();
Finally, you see how the
GetOrdinal
and
GetFieldType
methods are used. The former returns a
column index based on its name; the latter returns the C# type. These are the countertypes of
GetName()
and
GetDataTypeName()
, respectively.
// Get info about each column
lblType4.Text = rdr.GetName(0).ToString() + " is at index::" +
rdr.GetOrdinal("FirstName").ToString() +
" and its type is::" + rdr.GetFieldType(0).ToString();
lblType5.Text = rdr.GetName(1).ToString() + " is at index:: "+
rdr.GetOrdinal("LastName").ToString() +
" and its type is::" + rdr.GetFieldType(1).ToString();
So much for obtaining information about result sets. You'll now learn how to get information about
schemas.
Getting Data About Tables
The term
schema
has several meanings in regard to relational databases. Here, we use it to refer to the
design of a data structure, particularly a database table. A table consists of rows and columns, and each
column can have a different data type. The columns and their attributes (data type, length, and so on)
make up the table's schema.
To retrieve schema information easily, you can call the
GetSchemaTable
method on a data reader. As
the name suggests, this method returns a
System.Data.DataTable
object, which is a representation
(schema) of the table queried and contains a collection of rows and columns in the form of
DataRow
and
DataColumn
objects. These rows and columns are returned as collection objects by the properties
Rows
and
Columns
of the
DataTable
class.
However, here's where a slight confusion usually occurs. Data column objects aren't column values;
rather, they are column definitions that represent and control the behavior of individual columns. They
can be looped through by using a column name indexer, and they can tell you a lot about the data set.
