Extract database architecture (metadata) using ADO. net)

Recently, I am working on a small project in the company. Some of the functions involve obtaining database architecture (metadata) Information in the program. The so-called database architecture is the table/view structure of the database, including the field name, field type, length, primary key, and foreign key information contained in the data table. I remember that I used the C ++ Builder tool to implement related functions, but the source code was not saved for a long time, and the implementation methods were completely lost. Think about it. The development tool is also changed to vs, so it is better to start over again.

It is not very difficult to obtain the database architecture information. You can search for the database metadata on the Internet and find many related methods, however, most of the methods are related to the database system. For example, this article describes which data tables under SQL Server store metadata information and which database systems can provide functions to obtain the metadata, two links are also provided here. If you are interested, you can read the relevant articles on your own:

Http://searchdatabase.techtarget.com.cn/tips/433/3388433.shtml (How to Get SQL Server database metadata)

Http://www.pipcn.com/blog/user1/master/archives/2006/1119.shtml (get metadata information for SQL, access, Oracle databases)

But I always want to use a more general method and less program code to do this. To be honest, after implementation, I found that if I can feel at ease with the ADO. NET system learning, in fact, this problem is easy to solve, but I want to find a ready-made implementation, but delayed some time. In short, I cannot find the ready-made code by searching on the network. I have to go back and read the relevant documents of ADO. net. Now, I quickly found the getschema method of the connection object to obtain information about the data table and view, and the executereader method of the command object through commandbehavior. the schemaonly parameter extracts the table structure, so the implementation code is very simple:

/// <Summary>
/// Obtain the table schema information of the database
/// </Summary>
/// <Returns> returns the schema information list of the current data source. </returns>

Public void getschema (out list <sqlschemainfo> lst)
{

LST = new list <sqlschemainfo> ();
// String parameters required by the getschema method. Tables and views indicate data tables and views respectively.

String tables = system. Data. sqlclient. sqlclientmetadatacollectionnames. tables;
String views = system. Data. sqlclient. sqlclientmetadatacollectionnames. views;

// The connection object created here is only used as an example and only supports sqlserver and Oracle
If (_ providername. Trim () = "sqlserver ")
{
_ Connection = new system. Data. sqlclient. sqlconnection (_ connectionstring );
}
Else if (_ providername. Trim () = "oracle ")
{
_ Connection = new system. Data. oracleclient. oracleconnection (_ connectionstring );
}
Try
{
If (_ connection. State = connectionstate. Closed)
{
_ Connection. open ();
}
// Call getschema to retrieve the table schema of the Data Source
Datatable TBL = _ connection. getschema (tables );

Foreach (datarow row in TBL. Rows)
{
Sqlschemainfo SSI = new sqlschemainfo ();

If (_ providername. Trim () = "oracle ")
{
SSI. Owner = (string) Row ["owner"];
}
SSI. type = "table ";
SSI. Name = (string) Row ["table_name"];
Lst. Add (SSI );
}
// Call getschema to retrieve the view architecture of the Data Source
TBL = _ connection. getschema (Views );
If (TBL. Rows. Count> 0)
{
Foreach (datarow row in TBL. Rows)
{
Sqlschemainfo SSI = new sqlschemainfo ();

SSI. type = "View ";
If (_ providername. Trim () = "oracle ")
{
SSI. Name = (string) Row ["view_name"];
SSI. Owner = (string) Row ["owner"];
}
Else
{
SSI. Name = (string) Row ["table_name"];
}

Lst. Add (SSI );
}
}
}
Catch (exception ex)
{
Throw (Ex );
}
}

Note that when extracting the Oracle Data Table schema, you must save the value of the "owner" field so that the following table structure can be extracted based on the data table name or view name without exception, in addition, the view name of Oracle is saved in the "view_name" field, while the view name of SQL Server is saved in the "table_name" field.

The following program code obtains the structure of the specified table/view based on the data table name or view name. The table name/view name is obtained through the above method. In the Oracle database system, the passed table name (tablename) parameter should be in the form of owner + "." + tablename or owner + "." + viewname:

/// <Summary>
/// Obtain the data table structure based on the table name
/// </Summary>
/// <Param name = "tablename"> data table/view name </param>
/// <Param name = "Cols"> returns the list of table structures. </param>
Public void getsqlcolumns (string tablename, out list <sqlcolumn> Cols)
{
Idbcommand cmsql = NULL;
Idatareader DR = NULL;
Cols = new list <sqlcolumn> ();
Try
{
// Open up a connection
If (_ connection = NULL)
{
Return;
}

If (_ connection. State = connectionstate. Closed)
{
_ Connection. open ();
}
Cmsql = _ connection. createcommand ();
Cmsql. commandtext = "select * from" + tablename;
Dr = cmsql. executereader (commandbehavior. schemaonly );
For (INT I = 0; I <dr. fieldcount; I ++)
{
Sqlcolumn SC = new sqlcolumn ();
SC. Name = dr. getname (I );
SC. datatype = dr. getfieldtype (I );
Cols. Add (SC );
}
}
Catch (exception sqle)
{
Throw (sqle );
}
Finally
{
_ Connection. Close ();
If (cmsql! = NULL)
{
Cmsql. Dispose ();
If (Dr! = NULL)
Dr. Close ();
}
}
}

These two methods have passed the test of SQL Server and Oracle database systems, and can support oledb and ODBC data sources through expansion.