get the list of database

[Lloyd Dupont]

how do I get the list of database ?
like Northwind, etc ....

with ADO.NET ?


same question for MySQL too

 

[Miha Markic [MVP C#]]

Hi Lloyd,

Depends on the database server.
One way is to use OleDbConnection.GetOleDbSchemaTable method.

 

[Paul Clement]

¤ how do I get the list of database ?
¤ like Northwind, etc ....
¤
¤ with ADO.NET ?
¤
¤
¤ same question for MySQL too

The below code will work with SQL Server. Keep in mind that the OLEDB database provider for the
database type will need to support this feature. Otherwise it is likely you will need to call a
stored procedure or query a database table which consists of the names of all of the databases for
the server. You also need to keep in mind that there may be security issues with respect to
accessing this information which is dependent once again upon the type of database you are working
with.

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=Northwind;" & _
"Integrated Security=SSPI"

DatabaseConnection.Open()

'Retrieve schema information about the database catalogs.
SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Catalogs, Nothing)

Dim RowCount As Int32
For RowCount = 0 To SchemaTable.Rows.Count - 1
Console.WriteLine(SchemaTable.Rows(RowCount)!CATALOG_NAME.ToString)
Console.WriteLine(SchemaTable.Rows(RowCount)!DESCRIPTION.ToString)
Next RowCount

DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Cor Ligthert]

Hi Loyd,

This post came yesterday as answer in the languages VB newsgroup
\\\List of servers as supported by Manohar Kamath
Private Function LoadSQLServerList() As Integer
Dim serverIndex As Integer
Dim sqlServers As SQLDMO.NameList
Dim sqlServer As Object
' Get a list of servers
sqlServers = New SQLDMO.Application().ListAvailableSQLServers()
' Iterate through the server names, and add it to the combobox
For Each sqlServer In sqlServers
If Not sqlServer Is Nothing Then
cboServers.Items.Add(sqlServer)
End If
Next
' Send back the count of servers
LoadSQLServerList = cboServers.Items.Count

'table list in a server
Dim sqlServer As SQLDMO.SQLServer
Dim sqlDB As Object
sqlServer = New SQLDMO.SQLServer()
Try
' Connect to your database with userID and password
sqlServer.Connect(cboServers.Text, txtUserID.Text,txtPassword.Text)
' Check for database names in the server
' This loop adds the database name to another combobox
For Each sqlDB In sqlServer.Databases
If Not sqlDB.Name Is Nothing Then
cboDatabases.Items.Add(sqlDB.name)
End If
Next
Catch Exp As Exception
MsgBox(Exp.ToString)
End Try
////

 

[Lloyd Dupont]

thanks all

but what if I don't and can't use the ODBC/OleDB provider (let say because I
run on PocketPC)
And I want to use the System.Data.SqlClient.SqlConnection,
System.Data.SqlClient.SqlCommand objects of ADO.NET ?

and I have a ConnectionString in the form:
"Address=myIP;User ID=aUser;Pwd=aPwd" with the 'Database' setting as I'm
trying to get the list of it ....


what kind of SqlCommand should I use .......
is it only possible ?
is the user/password needed for it ?

 

[Miha Markic [MVP C#]]

Hi Lloyd,

You might try using sp_databases stored procedure then.
Of course, you'll need privileges to run it.

 

[Lloyd Dupont]

thanks again Miha !
I'll try that !

--
ihookdb
Get your data mobile
http://www.ihookdb.com

Hi Lloyd,

You might try using sp_databases stored procedure then.
Of course, you'll need privileges to run it.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

thanks all

but what if I don't and can't use the ODBC/OleDB provider (let say

because

I
run on PocketPC)
And I want to use the System.Data.SqlClient.SqlConnection,
System.Data.SqlClient.SqlCommand objects of ADO.NET ?

and I have a ConnectionString in the form:
"Address=myIP;User ID=aUser;Pwd=aPwd" with the 'Database' setting as I'm
trying to get the list of it ....


what kind of SqlCommand should I use .......
is it only possible ?
is the user/password needed for it ?