Use OLE to obtain database architecture Information

The connection string used to connect to the SQL database:

Data Source = [servername]; user id = [username]; Password = [Password]; database = [databasename]

You only need to add "Provider = sqloledb;" in the header of the OLE connection string, that is:

Provider = sqloledb; Data Source = [servername]; user id = [username]; Password = [Password]; database = [databasename]

You need to add a reference to the code file:

Using system. Data. oledb;

Create a connection object

String SQL;
Datatable schematable;
SQL = "provider = sqloledb; Data Source = [servername]; user id = [username]; Password = [Password]; database = [databasename]";
Oledbconnection conn = new oledbconnection (SQL );
Conn. open ();

After opening the connection, you usually need to make a judgment to prevent connection failure.

Getoledbschematabl (guid, object []) is used to read the schema table information. The GUID is a member of oledbschemaguid and determines to obtain the specified schema information. The object [] is a restricted column array, different from oledbschemaguid. Commonly used oledbschemaguid is tables, columns, indexes, primary_keys.
 
1. Obtain the structure information of fields (columns) in a data table.

Schematable = conn. getoledbschematable (oledbschemaguid. columns, new object [] {null, null, [tablename], null });

The restricted columns are table_catalog, table_schema, table_name, and column_name. If no restriction is set, null is passed.
In this case, schematable contains a field structure table. Each record (each row) in this table is the structure information of each field in [tablename, the schematable structure can be obtained through the column information. The column information of schematable is fixed. The following Code reads the column information:

String fieldname;
String datatype;
String outstr;
Foreach (datacolumn Col in schematable. columns)
{
Fieldname = col. Name;
Datatype = col. DataType. Name;
Outstr + = fieldname + "=" + datatype + "\ r \ n"; // output string for generating Field Information
}

Each column name and type in schematable are as follows:
Table_catalog string
Table_schema string
Table_name string
COLUMN_NAME String
COLUMN_GUID
COLUMN_PROPID Int64
ORDINAL_POSITION Int64
COLUMN_HASDEFAULT Boolean
COLUMN_DEFAULT String
COLUMN_FLAGS Int64
IS_NULLABLE Boolean
DATA_TYPE Int32
TYPE_GUID Guid
CHARACTER_MAXIMUM_LENGTH Int64
CHARACTER_OCTET_LENGTH Int64
NUMERIC_PRECISION Int32
NUMERIC_SCALE Int16
DATETIME_PRECISION Int64
CHARACTER_SET_CATALOG String
CHARACTER_SET_SCHEMA String
Character_set_name string
Collation_catalog string
Collation_schema string
Collation_name string
Domain_catalog string
Domain_schema string
Domain_name string
Description string
COLUMN_LCID Int32
COLUMN_COMPFLAGS Int32
COLUMN_SORTID Int32
COLUMN_TDSCOLLATION Byte []
IS_COMPUTED Boolean

With the structure of each SchemaTable column, you can choose to obtain[TABLENAME]The field information

String fieldname;
String;
Foreach (DataRow dr in SchemaTable. Rows)
{
Fieldname = dr [COLUMN_NAME]. ToString (); fieldname here is the name of each field in [TABLENAME], the same below
... = Dr [DATA_TYPE]. ToString ();
..;
}

Ii. obtain primary key information

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL
PK_NAME