1, SQLDMO
SQLDMO is the ideal way to manipulate SQL Server, and you can consider this approach if your database is SQL Server. Using SQLDMO in C # requires adding SQLDMO references, and then using SQLDMO in the current file, that is, SQLDMO can be used. The SQLDMO object model can be obtained in the help of SQL Server.
Copy Code code as follows:
private void Gettabels_dmo (string strservername,string struser,string strpwd,string strdatabase)
{
SQLDMO. SQL Server server = new SQLDMO. Sqlserverclass ();
Connect to Server
Server.connect (STRSERVERNAME,STRUSER,STRPWD);
Iterate over all the databases, get the specified database
for (int i=0;i<server.databases.count;i++)
{
Determines whether the current database is a specified database
if (Server.Databases.Item (i+1, "dbo"). Name ==strdatabase)
{
Get the specified database
Sqldmo._database db= Server.Databases.Item (i+1, "dbo");
Get all the 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 the ADO Extensions for DDL and security, which is an extension of Microsoft's ADO technology, which we can use to manipulate the structure of the database. It is a COM component that is expected to add some functionality to ADOX later in the ado.net. If you need some information about ADOX, I can provide. An example of this is using ADOX to get all the tables for the current database.
Copy Code code as follows:
private void Gettables_adox ()
{
Database connection for ADO
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);
Manipulating ADOX Catalog Objects
Catalogclass cat=new Catalogclass ();
Cat. ACTIVECONNECTION=CN;
for (int i=0;i<cat. tables.count;i++)
{
MessageBox.Show (cat. Tables[i]. Name);
}
}
Note: Cat in the above code. ActiveConnection cannot be the connection in ado.net, but the connection of ADO.
3, the OleDbConnection in the Ado.net
In C # We will first consider using ado.net to solve the problem, and if there is no way to consider using ADOX or SQLDMO to solve the problem. Although ADOX and SQLDMO can solve this problem, they are, after all, COM components that are not very handy to use in. NET and to have some difference in a non. NET platform. The following example shows the OleDbConnection method GetOleDbSchemaTable in Ado.net to obtain the schema of the database. You can see the description of this method in MSDN:
Public DataTable GetOleDbSchemaTable (
Guid schema,
Object[] restrictions);
Parameters
Schema
One of the OleDbSchemaGuid values that specifies the schema table to return.
Restrictions
An Object array that restricts values. These values are applied in the order in which the columns are restricted. That is, the first limit value is applied to the first constraint column, the second limit is applied to the second limit column, and so on.
return value
A DataTable that contains the schema information for the request.
For more information, you can query MSDN, and here's how to do it.
Copy Code code as follows:
private void Gettables_adonet ()
{
Handling OleDbConnection
String mailto:strconnectionstring=@%22integrated security=sspi;data source=hbxp;initial Catalog=Test; Provider=SQLOLEDB.1 ";
OleDbConnection cn=new OleDbConnection (strConnectionString);
cn. Open ();
Using OleDbConnection's getoledbschematable to get the structure of the database
DataTable dt = CN. GetOleDbSchemaTable (oledbschemaguid.tables,new object[] {null, NULL, NULL, "TABLE"});
foreach (DataRow dr in Dt. Rows)
{
MessageBox.Show ((String) dr["table_name"]);
}
}
4. Information Architecture View
The information Schema View is the Schema view defined in the SQL-92 standard, which is independent of the system tables. The great advantage of the information architecture view is that even if we make significant changes to the system tables, the application can use these views normally for access. The following example uses the information Schema view to work.
Copy Code code as follows:
private void Gettables_information_schema ()
{
Open connection
String strconnectionstring=system.configuration.configurationsettings.appsettings["ConnectionString"];
Sqlcn=new SqlConnection (strConnectionString);
SQLCN. Open ();
Using the Information Schema 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 table
If your database system is SQL Server, you can get all the tables for the current database by using the following methods:
Copy Code code as follows:
private void Gettables_systemtable ()
{
Open connection
String strconnectionstring=system.configuration.configurationsettings.appsettings["ConnectionString"];
Sqlcn=new SqlConnection (strConnectionString);
SQLCN. Open ();
Using the Information Schema View
SqlCommand sqlcmd=new SqlCommand ("Select object_name (id) from sysobjects WHERE xtype = ' U ' and objectproperty (ID, ' ISMSS hipped ') = 0 ", SQLCN);
SqlDataReader Dr=sqlcmd. ExecuteReader ();
while (Dr. Read ())
{
MessageBox.Show (Dr. GetString (0));
}
}
6. Using SQL Server's stored procedure "Sp_tables"
The following is a supplemental code from the "Sheng" friend's approach to using stored procedures.
Copy Code code as follows:
public void Gettables_storedprocedure ()
{
//handling OleDbConnection
string mailto:strconnectionstring=@%22integrated security=sspi;data source=hbxp;initial Catalog=Test; Provider=SQLOLEDB.1 ";
OleDbConnection cn=new OleDbConnection (strConnectionString);
CN. Open ();
//Execute 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 ());
}
}
