9. Querying Schema Information
In an environment where data is being collected
from a variety of sources and stored in your data tables, knowing the
schema of that data can be very important.
Historically, database engines have always provided a method for
querying information about a database schema, such as the names of the
tables, the names and data types of the columns, and the names and
included columns of the primary keys and foreign keys.
The database engines all did this differently,
mainly because their internal storage mechanisms for holding this
information varied from engine to engine. To improve cross-engine
compatibility, the ANSI Standards Committee introduced a standard syntax
for querying schema information, known as the INFORMATION_SCHEMA views. For instance, using this syntax you can query the database for a list of table names by submitting the following query:
SELECT TABLE_NAME
FROM Information_Schema.TABLES
WHERE TABLE_TYPE = 'TABLE'
SQL Server CE schema querying capability differs
significantly from that of SQL Server and from the ANSI standard. This
is necessary to maintain the smaller footprint of SQL Server CE. Many of
the ANSI standard tables related to constraints have been removed and
replaced by a single table that is more appropriate for a single-user
database: the MSysConstraints table. For instance, the
following ANSI standard query obtains foreign key information when
submitted to SQL Server; but it yields a syntax error when submitted to
SQL Server CE:
--***
--*** SQL Server version
--***
SELECT C.CONSTRAINT_NAME
, PK.TABLE_NAME
, PK.COLUMN_NAME
, FK.TABLE_NAME
, FK.COLUMN_NAME
FROM Information_Schema.REFERENTIAL_CONSTRAINTS C
JOIN Information_Schema.CONSTRAINT_COLUMN_USAGE PK
ON PK.CONSTRAINT_NAME = C.UNIQUE_CONSTRAINT_NAME
JOIN Information_Schema.CONSTRAINT_COLUMN_USAGE FK
ON FK.CONSTRAINT_NAME = C.CONSTRAINT_NAME
Instead, the following query must be used with SQL Server CE to obtain foreign key information:
--***
--*** SQL Server CE version
--***
SELECT C.CONSTRAINT_NAME
, C.REFERENCED_TABLE_NAME
, C.REFERENCED_COLUMN_NAME
, C.TABLE_NAME
, C.CONSTRAINT_COLUMN_NAME
FROM MSysConstraints C
WHERE C.CONSTRAINT_TYPE = 1
In the world of SQL Server CE, schema queries are very useful toolsThe data in your SQL Server CE database may have been imported from a
connected database such as SQL Server. You may need to ascertain schema
information after the data has been received. Additionally, as you move
data from your SQL Server CE database into a data set, you may wish to
reproduce the database schema, such as constraints and relationships,
into the data set.
As an example of querying schema information, BuildDataSet, which does the following:
Creates a data set
Finds all the tables in a specified SQL Server CE database
Uploads them to the data set
Finds all the constraints in the specified SQL Server CE database
Creates the corresponding relations and constraints in the data set
Returns the data set
Listing 8 presents the code.
Listing 8. Building a Data Set from a Database
public static DataSet BuildDataSet( string strDB )
{
DataSet dsetWork;
if (! File.Exists(strDB) ) { return(null) ; }
string strConn = "Data Source=" + strDB;
SqlCeConnection connDB;
connDB = new SqlCeConnection(strConn);
connDB.Open();
dsetWork = new DataSet("ourProduceCo");
SqlCeDataReader drdrTableNames;
drdrTableNames = GetTableNames(connDB);
while ( drdrTableNames.Read() )
{
LoadTable(dsetWork,
connDB,
drdrTableNames.GetString(0));
}
drdrTableNames.Close();
LoadRelationships(dsetWork, connDB);
connDB.Close();
return dsetWork;
}
// *****************************************************//
//
// GENERIC ROUTINES
//
public static SqlCeDataReader GetTableNames
( SqlCeConnection connDB )
{
SqlCeCommand cmndDB =
new SqlCeCommand(strGetTableNames, connDB);
return cmndDB.ExecuteReader();
}
public static void LoadTable (DataSet dsetDB,
SqlCeConnection connDB,
string strTable)
{
SqlCeCommand cmndDB =
new SqlCeCommand(strGetTableRows + strTable, connDB);
SqlCeDataAdapter daptProducts =
new SqlCeDataAdapter(cmndDB);
daptProducts.Fill(dsetDB, strTable);
}
public static SqlCeDataReader GetRelationships
( SqlCeConnection connDB )
{
SqlCeCommand cmndDB =
new SqlCeCommand(strGetRelationships, connDB);
return cmndDB.ExecuteReader();
}
public static void LoadRelationships(
DataSet dsetDB,
SqlCeConnection connDB)
{
// Retrieve foreign key information from the
// database. For each foreign key, create
// a relationship in the data set.
// Create the GetRelationships command object.
SqlCeCommand cmndDB =
new SqlCeCommand(strGetRelationships, connDB);
// Execute GetRelationships.
SqlCeDataReader drdrRelationships;
drdrRelationships = cmndDB.ExecuteReader();
string strRelation;
DataColumn dcolParent, dcolChild;
while ( drdrRelationships.Read() )
{
// For each foreign key in the database
// Extract and convert name, parent, child info.
strRelation =
drdrRelationships.GetString(0);
dcolParent =
dsetDB.Tables[drdrRelationships.GetString(1)]
.Columns[drdrRelationships.GetString(2)];
dcolChild =
dsetDB.Tables[drdrRelationships.GetString(3)]
.Columns[drdrRelationships.GetString(4)];
// Add the relation to the data set.
dsetDB.Relations.Add
(strRelation, dcolParent, dcolChild);
}
drdrRelationships.Close();
// Make each relationship a nested relationship.
foreach( DataRelation drelForXML in dsetDB.Relations )
{
drelForXML.Nested = true;
}
}
|
Note
The SqlServerCe
namespace contains the classes for accessing a Microsoft SQL Server CE
database. You need a connection and a command object, plus either a SqlCeDataReader or a SqlCeDataAdapter
object. The data reader lets you retrieve rows into your application
one at a time, forward only. The data set is a memory-resident database
that allows for programmatic access to data from a SQL Server CE
database and from other sources. It provides data binding and automatic
updating.
Interestingly, all of
these points are as true for Microsoft SQL Server as they are for
Microsoft SQL Server CE. But programming for one is not exactly the same
as programming for the other, as we are about to see.
