Get database architecture information using ADO.NET
Author: Autumn maple 2004-7-3
Sometimes you need to get database architecture information when developing, some of the databases, such as stored procedures, views, tables, and fields, note descriptions, etc. This is a relatively simple implementation through the OLEDB.NET data provider.
I. OLEDBCONNECTION GETOLEDBSCHEMATABLE method
This object in ADO.NET acts as a connection between the data source, connects the database through the Connection object or disconnects from the database to release valuable resources. Here we need to use the getoledbschematable method of the OLEDBConnection object,
Defined as follows:
Public DataTable getoledbschematable (Guid Schema, Object [] Restrictions);
Method Returns a DataTable object;
The first parameter is the enumeration value of OLEDBSChemaGuid, is used to specify the schema information type, which can determine the desired schema type according to a certain value, such as the table, column, view, etc., specifically can refer to MSDN;
The second parameter is the parameter of the restrictions, as an OJECT array data type, which functions as a filter, if the second parameter is set to Null, then return all the information of the specified OLEDBSChemaGuid enumeration.
The following description is performed with the sample program, and the example interface is as follows:
The entire form includes:
A TextBox to enter a database connection string;
A DataGrid used to display architecture information;
View the button, click Get Architecture Information.
Here is the following processing in the view button event by SQL Server's Northwind database.
Private Void Buttonok_Click (Object Sender, System.EventArgs E)
{
THIS.DATAGRIDSCHEMA.DataSource = getsqldbschemausingoledbconnection (this.TextBoxConnstring.Text.trim ());
}
Connect the connection string into the GetSqldbschemausingoledbConnection function via the text box, connect the database by the OLEDBConnection object, and get the archive 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, look at how to filter information through the Restrictions parameter;
Parameter data can be defined as
Object [] restricts =
New object [] {"Table_catalog", "Table_SChema", "Table_Name", "Column_Name"};
Refer to the MSDN documentation for details.
Modify the above function, we only get information about the Northwind database Customers table:
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, the acquisition of the Access database architecture information can also be completed by 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 front interface icon is the execution result, which I use Access 2003.
two. GetbDataReader's getSchematable method
Another method of acquiring the architecture is the use of the GetSchemAtable method of OLEDBDATAREADER.
Public Virtual DataTable getSchematable ();
This method needs to be completed with an overload method of OLEDBCommand.executeReader to complete, you can set the unique parameters of the overload method.
Public OLEDBDataReader ExecuteReader (Commandbehavior Behavior);
Commandbehavior is an object, defined as
[Flags]
[Serializable]
Public Enum Commandbehavior
Commandbehavior.Schemaomaonly: only returns column information, does not affect the status status;
Commandbehavior.KeyInfo: Returns column and primary key information. Do not lock the selected row when you perform this query.
If you use commandbehavior.schemaonly, you don't need to add Commandbehavior.KeyInfo.
Below is a demo code:
Private DataTable GetSchemausingoledBDataRead (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.schema);
Table1 = DataReader.getschematable ();
DataReader.Close ();
}
Catch (Exception EX)
{
Messagebox.show (ex.totring ());
}
Finally
{
IF (MyConn.State! = connectionState.closed)
MyConn.close ();
Myconn.dispose ();
}
Return Table1;
}
This method returns the architecture information of the Customers table, of course, if connected to the Access database, the above function can be modified to modify the connection string and query strings.
Third, other supplements
What we are talking about here is the acquisition of database architecture information through oledb.net. For a specific data provider SQL Client.NET and Oracle Client.NET, etc. can also be implemented by other means.
such as:
Data can be obtained by an information architecture view in SQL Server:
Select * from information_schema.tables
Get the list of table names:
SELECT TABLE_NAME from Information_schema.tables Where Table_Type = 'base table'
Get the list of views:
SELECT TABLE_NAME from Information_schema.tables Where Table_Type = 'View'
There is also a column, stored procedure, etc., you can view the online help of SQL Server.
In the Oracle database, you can get this information by querying the Oracle data dictionary, and details can be referred to Oracle's related documents.
Reference: "MSDN", "ADO.NET Core Reference", "SQL Server 2000 Book"
