Schemas in ADO.NET 2.0:
ADO.NET 2.0 is the premier data access method for the Microsoft .NET platform, providing a uniform way of programmatically accessing metadata and schema information. Most data access technologies provide a way to query the schema of a database, and obtain information about the tables, stored procedures, data types, users, and other content of a database. However, the technique varies depending on the data provider or database driver that is used and is not always straightforward to implement. In ADO.NET 2.0, Microsoft has added a set of features to the standard managed code classes that you can use as an API to query database schemas.
Schema information and metadata
Metadata is a part of every data access API. Metadata can be used by various tools and code generators and Application package designers. They may allow end-users to customize an application by adding new tables or new columns to existing tables. When end-users change the database schema like this, a general purpose query and modification tool can use metadata to include the users new tables in maintenance, backup, and other application functions just like they were built in tables that ship with the application. Programmers can use metadata to write their own custom classes that derive from System.Data.Common.DbCommandBuilder and build insert, update, and delete commands for use with the DataSet. Builders of multi-database applications (that is, applications designed to run on the user's choice of database) can use metadata to maintain a common code base as much as possible, optimizing the data access code when needed.
It's better to expose the metadata through a generic metadata API than to have each consumer use the database-specific API. That way, tool writers can maintain a more manageable code base. Such an API must be very flexible as well, because there are four obstacles to consider when writing a generic API to expose metadata.
1. The metadata collections and information differ between databases. For example, SQL Server users might want to expose a collection of Linked Servers, whereas Oracle users might be interested in information about Oracle Sequences.
2. The underlying system tables in which common database metadata is stored is different, not only in different database products, but even in different versions of the same database. For example, SQL Server 2005 exposes its metadata using new tables under a "sys" schema (for example, sys.tables) while previous versions of SQL Server use metadata tables, such as sysobjects, to store the same data.
3.Different programs may have different views of metadata.
Varying levels of information about your database and database server can be obtained programmatically, including top level information such as the restricted words for your server and low level information such as the data type of a stored procedure parameter.
Various database products have different ways of storing this information. In SQL Server 2000, for example, the sys tables and Master database are used to store metadata and schema information, and the tables must be queried to retrieve the required information.
The GetSchema Method:
GetSchema grabs metadata associated with various MetaDataCollections. If you call GetSchema() with no parameters on your DbConnection class you will get a list of the various MetaDataCollection CollectionNames for which you can gather information. To query across databases, you have to create a separate connection to each one and then use these connections to query each database individually.
The GetSchema method is implemented within the abstract base class DbConnection (in the System.Data.Common namespace) and is available in all the provider-specific connection classes such as SqlConnection, OleDbConnection, OracleConnection, and OdbcConnection.
SqlConnection connection =new SqlConnection(connectionString);
connection.Open();
DataTable dt = connection.GetSchema();
connection.Close();
To get a list of databases for a given data source, you can call GetSchema with the parameter Databases. The list of databases for the datasource is returned as a DataTable. This returns a datatable with column headings of database name, database id and created date, and each is filled with information about the databases on the server.
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
DataTable dt = connection.GetSchema("Databases");
connection.Close();
GetSchema Restrictions
To filter the results returned by GetSchema, you can specify certain restrictions on each MetaDataCollection. The restrictions are used to limit the data returned based on columns.
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
DataTable dt = connection.GetSchema("Restrictions");
connection.Close();
GetSchema Identifiers:
Identifiers specify the columns required to uniquely identify a particular instance of metadata.
The Databases collection, for example, has one restriction (database name) and one identifier (database name). This specifies that the Databases collection can be restricted by the database name, and only the database name is required to uniquely identify information about one database.
GetSchema Overloaded Methods:
GetSchema() Returns a DataTable containing a row for each of the metadata collections that are available from the database. Equivalent to specifying the String value "MetaDataCollections" when using the GetSchema(String) overload.
GetSchema(String)takes the name of a metadata collection and returns a DataTable containing a row for each item found in that metadata collection in the database.
GetSchema(String, String-Array)takes the name of a metadata collection and an array of String values that specify the restrictions on which the rows in the returned DataTable will be filtered. Used to select and return a DataTable containing only the rows in the metadata collection specified in the first parameter that match the restrictions specified in the second parameter.
Various Metadata Collections:
Irrespective of which provider you use and which type of database you query using the Schema API, there are some common factors that you can rely on. Every provider and database combination should expose collections that allow code to find out about the built-in objects and the database engine itself, including:
• A collection named MetaDataCollections that contains a list of all the available collections, and the number of restrictions that apply to each collection. If in doubt, you can use the GetSchema method with no parameters to get the default collection, which should be the MetaDataCollections collection.
• A collection named DataSourceInformation, containing a single row that provides the name, version, and other details about the database. These details include the separation character for multi-part object identifiers (such as Northwind.dbo.Orders), the legal characters, and the format for string literals, parameters, identifiers, etc.
• A collection named DataTypes that lists all the supported data types and provides details about them such as the size, whether they are incrementable, case-sensitive, nullable, etc.
• A collection named ReservedWords that lists all the words that are reserved for use by the database and code that runs within it (including in stored procedures and SQL statements).
• A collection named Restrictions that lists the restrictions you can apply to filter the results when querying any of the metadata collections.
Most of the remaining collections allow code to enumerate the common objects that make up the contents of the database, such as tables, columns, indexes, stored procedures, etc
GetSchemaTable():
The ADO.NET datareaders expose a GetSchemaTable method similar to the ADO.NET connections. This GetSchemaTable method returns metadata information on the columns retrieved from the ADO.NET datareader.
SqlConnection con=new SqlConnection(connectionStr);
Con.open();
SqlCommand cmd=new SqlCommand(“select Empid,Empname from Emp”,con);
SqlDataReader dr=cmd.ExecuteReader();
DataTable dt=dr.GetSchemaTable();
The datatable is filled with column metadata on the Empid,Empname from the Emp table.
Creating and Using a Restriction Array
Restrictions are useful when selecting a specific item is needed, for example to find details about one table that exists in the database. For this, use a restrictions array of size four and the filters that can be applied are the database (catalog) name, the owner (or schema name), the table name, and the table type ("BASE TABLE" or "VIEW").
Dim restrictions(3) As String
restrictions(0) = "Northwind" ' database/catalog name
restrictions(1) = "dbo" ' owner/schema name
restrictions(2) = "Orders" ' table name
restrictions(3) = "BASE TABLE" ' table type
‘create a connection to northwind database
grid1.DataSource = conn.GetSchema("Tables", restrictions)
grid1.DataBind()
|
No responses found. Be the first to respond and make money from revenue sharing program.
|