Obtain all tables in the database (C #)

In many cases, we need to list all the tables in the specified database. When using c # for software development, what methods can we achieve this goal? The following six methods can be used to achieve this goal.

1. sqldmo
SQLDMO is an ideal way to operate SQLServer. If your database is SQLServer, you can consider using this method. To use SQLDMO in C #, you need to add reference to SQLDMO, and then using SQLDMO in the current file; that is, you can use SQLDMO. You can obtain the object model of SQLDMO with the help of SQLServer.

Private void GetTabels_DMO (string strServerName, string strUser, string strPWD, string strDatabase)
{
SQLDMO. SQLServer Server = new SQLDMO. SQLServerClass ();
// Connect to the server
Server. Connect (strServerName, strUser, strPWD );
// Traverse all databases to obtain the specified database
For (int I = 0; I <Server. Databases. Count; I ++)
{
// Determine whether the current database is a specified database
If (Server. Databases. Item (I + 1, "dbo"). Name = strDatabase)
{
// Obtain the specified database
SQLDMO. _ Database db = Server. Databases. Item (I + 1, "dbo ");
// Obtain all tables in the specified database
For (int j = 0; j <db. Tables. Count; j ++)
{
MessageBox. Show (db. Tables. Item (j + 1, "dbo"). Name );
}
}
}
}

2. ADOX

ADOX is ADO extensions for DDL and security. It is an extension of Microsoft's ADO technology. We can use it to operate the database structure. It is a COM component. It is estimated that some ADOX functions will be added in ADO. net in the future. If you need some ADOX materials, I can provide them. The following example uses ADOX to obtain all tables of the current database.

Private void gettables_adox ()
{
// ADO database connection
ADODB. connectionclass Cn = new ADODB. connectionclass ();
String ConnectionString = "Provider = SQLOLEDB.1; Integrated Security = SSPI; Initial Catalog = Test; Data Source = HBXP ";
Cn. Open (ConnectionString, "sa", "", 0 );
// Operate the catox object of ADOX
CatalogClass cat = new CatalogClass ();
Cat. ActiveConnection = cn;
For (int I = 0; I <cat. Tables. Count; I ++)
{
MessageBox. Show (cat. Tables [I]. Name );
}
}
Note: In the code above, Cat. activeconnection cannot be the connection in ADO. net, but it should be the connection of ADO.

3. oledbconnection in ado.net

In C #, we will first consider using ado.net to solve the problem. If there is no way, we will consider using ADOX or sqldmo to solve this problem. Although ADOX and sqldmo can solve this problem, they are COM components after all. They are not very easy to use in. NET and may differ from non-. Net platforms. The following example shows the oledbconnection method getoledbschematable in ado.net to obtain the database architecture. You can see the description of this method in msdn:

Public datatable getoledbschematable (
Guid schema,
Object [] restrictions );
Parameters
Schema
Oledbschemaguid value, which specifies the schema table to be returned.
Restrictions
Array of objects with limit values. These values are applied in the order of restricted columns. That is, the first limit value is applied to the first limit column, the second limit value is applied to the second limit column, and so on.
Return Value
The datatable that contains the requested schema information.
For more information, see msdn. The following example shows how to implement it.

Private void GetTables_ADONET ()
{
// Process OleDbConnection
String strConnectionString = @ "Integrated Security = SSPI; Data Source = HBXP; Initial Catalog = Test; Provider = SQLOLEDB.1 ";
OleDbConnection cn = new OleDbConnection (strConnectionString );
Cn. Open ();
// Use OleDbConnection's GetOleDbSchemaTable to obtain the database structure
DataTable dt = cn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, new object [] {null, "TABLE "});
Foreach (datarow DR in DT. Rows)
{
MessageBox. Show (string) Dr ["table_name"]);
}

}

4. Information Architecture View

Information Architecture views are architectural views defined in sql-92 standards that are independent of system tables. The biggest advantage of the Information Architecture view is that even if we make important changes to the system table, the application can normally use these views for access. The following example uses the information architecture view.

Private void gettables_information_schema ()
{
// Open the connection
String strconnectionstring = system. configuration. configurationsettings. etettings ["connectionstring"];
Sqlcn = new sqlconnection (strconnectionstring );
Sqlcn. open ();
// Use the information architecture View
Sqlcommand sqlcmd = new sqlcommand ("select table_name from information_schema.tables where table_type = 'base table'", sqlcn );
Sqldatareader DR = sqlcmd. executereader ();
While (dr. Read ())
{
MessageBox. Show (dr. GetString (0 ));
}
}

5. Use System tables

If your database system is sqlserver, you can use the following method to obtain all the tables of the current database:

Private void GetTables_SystemTable ()
{
// Open the connection
String strConnectionString = System. Configuration. ConfigurationSettings. etettings ["ConnectionString"];
Sqlcn = new sqlconnection (strconnectionstring );
Sqlcn. open ();
// Use the information architecture View
Sqlcommand sqlcmd = new sqlcommand ("select object_name (ID) from sysobjects where xtype = 'U' and objectproperty (ID, 'ismsshipped ') = 0", sqlcn );
Sqldatareader DR = sqlcmd. executereader ();
While (dr. Read ())
{
MessageBox. Show (dr. getstring (0 ));
}
}

6. Use the SQL Server Stored Procedure "sp_tables"

Public void GetTables_StoredProcedure ()
{
// Process OleDbConnection
String strConnectionString = @ "Integrated Security = SSPI; Data Source = HBXP; Initial Catalog = Test; Provider = SQLOLEDB.1 ";
OleDbConnection cn = new OleDbConnection (strConnectionString );
Cn. Open ();
// Execute the Stored Procedure
OleDbCommand cmd = new OleDbCommand ("sp_tables", cn );
Cmd. CommandType = CommandType. StoredProcedure;
OleDbDataReader dr = cmd. ExecuteReader ();
While (dr. Read ())
{
MessageBox. Show (dr ["TABLE_NAME"]. ToString ());
}
}