OLE DB obtains schema information for the database

Architecture | data | database

As for how to obtain database schema information, it may be simpler and more method to obtain SQL Server and ORACAL database structure.

This is a common method for all databases that can be linked with ado.net, such as the DBF free table in ACCESS,DB4 format.





1, the first is linked to a variety of database links string, are used Ado.net, the namespace is: using System.Data.OleDb;

Using several databases for example, you need additional database links to check here: http://www.connectionstrings.com/

But if Ole DB, OleDbConnection (. NET) is linked.



SQL Server database connection string sConnStr = "Provider=sqloledb;data source=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; ";

Access database connection string

String sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=\ omepath\\mydb.mdb; User id=admin; password=; ";D BF table (DB4 format) connection string sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\folder;" Extended properties=dbase IV; User id=admin; Password= "; FoxPro Database Connection string

String sConnStr = "Provider=vfpoledb.1;data source=c:\\mydatadirectory\\; Collating sequence=general "; Excel File connection string

String sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\myexcel.xls; Extended properties= "" Excel 8.0; Hdr=yes;imex=1 "" "; UDL File connection string

String sConnStr = "File name=c:\\mydatalink.udl;";





2, get the database schema information, here I take the database table list for example, the code is as follows





<summary>///gets a tabular list of databases that can be used for all OLE DB connected databases///</summary>///<param name= "sConnStr" > Database connection characters String </param>///<returns> table list array </returns> public string[] Gettablist (string sconnstr) {if (sconnst r = = null) throw new ArgumentNullException ("sConnStr");

String[] stblist; using (OleDbConnection conn = new OleDbConnection (sConnStr)) {Conn.    Open (); DataTable DT = conn.    GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] {null, NULL, NULL, "TABLE"}); Conn. Close ();

if (dt. Rows.Count > 0) {stblist = new String[dt.     Rows.Count];     int i = 0; foreach (DataRow dr in Dt. Rows) {Stblist[i] = dr["table_name"].      ToString ();     i + 1;     } else {stblist = new string[1];    Stblist[0] = "< no form >";  } return stblist; }






which

DataTable DT = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] {null, NULL, NULL, "TABLE"});

This is the key to using the OleDbConnection object's GetOleDbSchemaTable method to demonstrate schema information

GetOleDbSchemaTable returns a DataTable populated with schema information.

You can make this DataTable appear directly, which is the detailed information.



If you need to look up other information about the database schema,

such as the data source, table and view of the directory and the constraints exist. Schema information in a table includes primary keys, columns, and AutoNumber fields.

Only need to change the GetOleDbSchemaTable parameter settings, the specific parameters can be viewed

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/ Frlrfsystemdataoledboledbschemaguidmemberstopic.asp





3, call Gettablist (SCONNSTR) will be able to return to that database in the table list

sConnStr is connected to that kind of database and can return results as long as it is supported by Ado.net.

When the connection is a UDL file, want to connect to the other database through the UDL file, the selected driver must also be OLE DB.







Reference:

OleDbSchemaGuid member http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/ Frlrfsystemdataoledboledbschemaguidmemberstopic.asp

How to: Retrieve schema information using getoledbschematable and Visual C #. NET

Http://support.microsoft.com/default.aspx?scid=kb;zh-cn;309681#3

connectionstrings

http://www.connectionstrings.com/