This chapter describes techniques to get schema information and metadata from databases, manage database objects, and enumerate installed database providers and drivers.
There are many ways to get schema information and other information from a SQL Server database. Some of these techniques are:
The Connection object in the OLE DB .NET data provider has a GetOleDbSchemaTable( ) method that returns schema information from an OLE DB data source. Recipe 10.2 shows how to use this method to return SQL server database schema information. Recipe 10.12 shows how to list the tables in a Microsoft Access database using this method.
Recipe 10.5 shows how to use the ExecuteScalar( ) method of the Command object to efficiently determine how many records are in a result set matching specified criteria.
Data Definition Language (DDL) statements are used to manage objects in a SQL Server databasefor example, adding or modifying objects such as databases, tables, indices, and views. You can execute a DDL statement through a .NET data provider to manipulate the database or catalog schema. Since DDL commands do not return a result set as a query does, these statements are executed using the ExecuteNonQuery( ) method of the Command object. Recipe 10.7 shows how to execute a DDL statement to create a new SQL Server database. Recipe 10.8 shows how to create a new table in a SQL Server database. Recipe 10.15 creates a method CreateTableFromSchema( ) that dynamically constructs a DLL statement from a DataTable schema and executes that DDL to create a table in a SQL Server database.
ADO Extensions for DDL and Security (ADOX) extend the ADO objects and programming model with objects for schema creation and maintenance and for security. Recipe 10.6 shows how to use ADOX programmatically from .NET though COM interop to create a new Microsoft Access database. Recipe 10.14 shows how to use ADOX to list the tables in a Microsoft Access database.
The Jet OLE DB Provider and Replication Objects (JRO) library was created to isolate Jet-specific functionality from the generic ADO library. Recipe 10.10 shows how to use JRO to compact a Microsoft Access database.
The SQL SET statements alter session handling of current information. Recipe 10.9 shows how to retrieve the execution plan for a query. Recipe 10.12 shows how to retrieve only column metadata when a query is executed.
Every Windows system has ODBC drivers and OLE DB providers installed on it. You can examine the registry to get a list of which are installed. Recipe 10.16 does this for ODBC drivers while Recipe 10.13 does it for OLE DB Providers. Recipe 10.16 also uses a SQL Server extended stored procedure to enumerate the OLE DB providers.
Connecting to Data
Retrieving and Managing Data
Searching and Analyzing Data
Adding and Modifying Data
Copying and Transferring Data
Maintaining Database Integrity
Binding Data to .NET User Interfaces
Working with XML
Optimizing .NET Data Access
Enumerating and Maintaining Database Objects
Appendix A. Converting from C# to VB Syntax