Using Ado.net to get database schema information

ado| Schema | data | Database use Ado.net to get database schema information

Autumn Maple 2004-7-3

At the time of development, it is sometimes necessary to obtain database schema information to get some other information about the database, such as stored procedures, views, tables, types of fields, notes and so on. This can be achieved by oledb.net the data provider more succinctly.



GetOleDbSchemaTable Method of OleDbConnection

This object in the Ado.net object model acts as a connection to the data source, connects the database through the connection object, or disconnects from the database to free valuable resources. What we need to use here is the GetOleDbSchemaTable method of the OleDbConnection object,

The definition is as follows:

Public DataTable GetOleDbSchemaTable (Guid schema,object[] restrictions);

method returns a DataTable object;

The first parameter is a OleDbSchemaGuid enumeration value that specifies the type of schema information that can be used to determine the type of schema required, such as tables, columns, views, and so on, to refer to MSDN;

The second parameter, restrictions, is a oject array data type that acts as a filter, and if the second argument is set to NULL, it returns all the information for the specified OleDbSchemaGuid enumeration.

The following is illustrated with the sample program, the sample interface is as follows:




The entire form includes:

A textbox that is used to enter the database connection string;

A DataGrid, used to display schema information;

View the button and click Get schema information.

Here, for example, SQL Server's Northwind database is handled as follows in the View button events:

private void Buttonok_click (object sender, System.EventArgs e)

{

This.dataGridSchema.DataSource = Getsqldbschemausingoledbconnection (This.textBoxConnString.Text.Trim ());

}

Pass the connection string into the Getsqldbschemausingoledbconnection function through the text box, connect the database by the OleDbConnection object, and obtain the schema information.

Private DataTable getsqldbschemausingoledbconnection (string connstring)

{

OleDbConnection myconn = new OleDbConnection (connstring);

MyConn.Open ();

DataTable table1 = myconn.getoledbschematable (oledbschemaguid.columns,null);

Myconn.close ();

return table1;

}

The above function returns all the fields of the database, below to see how to filter the information through the restrictions parameter;

Parameter data can be defined as

object[] Restrictions =

New object[]{"Table_catalog", "Table_schema", "table_name", "column_name"};

Refer to the MSDN documentation for details.

To modify the function above, we only get information about the Customers table of the Northwind database:

Private DataTable getsqldbschemausingoledbconnection (string connstring)

{

OleDbConnection myconn = new OleDbConnection (connstring);

Object [] Restrictions = new object[]{"Northwind", "dbo", "Customers", null};

MyConn.Open ();

DataTable table1 = myconn.getoledbschematable (oledbschemaguid.columns,restrictions);

Myconn.close ();

return table1;

}



In addition, Access database schema information can be obtained through the above method,

Private DataTable getmdbschemausingoledbconnection (string connstring)

{

OleDbConnection myconn = new OleDbConnection (connstring);

MyConn.Open ();

DataTable table1 = myconn.getoledbschematable (oledbschemaguid.columns,null);

Myconn.close ();

return table1;

}

The previous interface diagram is the result of the execution, where I'm using Access 2003.



Two The GetSchemaTable method of OleDbDataReader

Another way to get a schema is to use the OleDbDataReader getschematable method,

Public virtual DataTable getschematable ();

The method needs to be combined with an overloaded method of OleDbCommand.ExecuteReader to set up the unique parameters of the overloaded method.

Public OleDbDataReader ExecuteReader (CommandBehavior behavior);

CommandBehavior is an enumeration object, defined as

[Flags]

[Serializable]

public enum CommandBehavior



Commandbehavior.schemaonly: Returns only the column information, does not affect the database state;

Commandbehavior.keyinfo: Returns column and primary key information. The selected row is not locked when this query is executed.

If you use commandbehavior.schemaonly, you don't need to add commandbehavior.keyinfo.

Here is the demo code:

Private DataTable Getschemausingoledbdatareader (string connstring)

{

OleDbConnection myconn = new OleDbConnection (connstring);

DataTable table1 = null;

Try

{

OleDbCommand cmd = new OleDbCommand ("SELECT * from Customers", myconn);

MyConn.Open ();

OleDbDataReader dataReader = cmd. ExecuteReader (commandbehavior.schemaonly);

Table1 = datareader.getschematable ();

Datareader.close ();

}

catch (Exception ex)

{

MessageBox.Show (ex. ToString ());

}

Finally

{

if (myconn.state!= connectionstate.closed)

Myconn.close ();

Myconn.dispose ();

}

return table1;

}

This method returns the schema information for the Customers table, and of course, if you connect to an Access database, the above function can be executed correctly only by modifying the connection string and the query string.



Third, other supplementary

What we are talking about here is the acquisition of database schema information through oledb.net. Other means can be implemented for specific data providers such as SQL Client.net and Oracle Client.net.

Like what:

You can get data from the information Schema view in SQL Server:

SELECT * from INFORMATION_SCHEMA. TABLES

Get a list of table names:

SELECT table_name from INFORMATION_SCHEMA. TABLES WHERE table_type = ' BASE TABLE '

To get a list of view names:

SELECT table_name from INFORMATION_SCHEMA. TABLES WHERE table_type = ' VIEW '

You can also view the online Help for SQL SERVER by getting columns, stored procedures, and so on.

Oracle databases allow you to query Oracle data dictionaries for this information, and refer to Oracle's related documentation.



This article refers to: "MSDN", "Ado.net Core Reference", "SQL Server 2000 Books Online"