Create Database Connection String Builder like SQL Connect to Server interface

[a808y]

Hi All,

I am trying to build a user control that will do exactly what the SQL 2005 Connect to Server interface do, eventually create a connection string and store in a app.config file.

It should allow user to select server type, server name, authentication, user name and password.

It should also automatically load available servers and domain login by default.

Any one have any idea on how it can be accomplished?

regards,
Andy

 

[jmcilhinney]

I've never tried to implement the server type part but I've done the rest for a server type of Database Engine. You can use the SqlConnectionStringBuilder class to break up and recombine connection strings. You can use the SqlDataSourceEnumerator class to get all browsable instances. You can use the SqlConnection.GetSchema method to get a list of databases on a server.

Here's my code for getting server names:

Public Function GetServerNames() As ReadOnlyCollection(Of String)
    If Not Me.CanEnumerateDataSources Then
        Throw New NotSupportedException("The current provider does not support data source enumeration.")
    End If

    'Get all the data source information.
    Dim servers As DataView = Me.Factory.CreateDataSourceEnumerator().GetDataSources().DefaultView
    Dim upperBound As Integer = servers.Count - 1
    Dim dataSources(upperBound) As String

    'Sort the data sources alphabetically by server name and instance name.
    servers.Sort = String.Format("{0}, {1}", _
                                 Me.ServerSchemaColumnName, _
                                 Me.InstanceSchemaColumnName)

    'Get the data source names.
    For index As Integer = 0 To upperBound
        dataSources(index) = Me.GetServerName(servers(index).Row)
    Next

    Return New ReadOnlyCollection(Of String)(dataSources)
End Function

and for getting databases:

Public Overridable Function GetDatabaseNames() As ReadOnlyCollection(Of String)
    Dim closeConnection As Boolean = (Me.Connection.State = ConnectionState.Closed)
    Dim rows As DataRowCollection
    Dim upperBound As Integer
    Dim databases() As String = Nothing

    With Me.Connection
        If closeConnection Then
            .Open()
        End If

        'Get all the database information.
        rows = .GetSchema(Me.DatabaseSchemaCollectionName).Rows

        'Close the connection if and only if it was already closed before the operation.
        If closeConnection Then
            .Close()
        End If
    End With

    upperBound = rows.Count - 1

    ReDim databases(upperBound)

    'Get the database names.
    For index As Integer = 0 To upperBound
        databases(index) = CStr(rows(index)(Me.DatabaseSchemaColumnName))
    Next

    Return New ReadOnlyCollection(Of String)(databases)
End Function

 

[a808y]

Thanks

Hey thanks. Where can I learn about specific classes like this, do you have any good reference site or book that I should look into?

 

[jmcilhinney]

Where can I learn about specific classes like this, do you have any good reference site or book that I should look into?

The MSDN Library, which you should have installed along with VS and you access from the Help menu.

 

[a808y]

I imported System, System.collections.generic, and System.Collections.ObjectModel

For the getServerName() the following are not recognized.
Me.CanEnumerateDataSources
Me.Factory
Me.ServerSchemaCOlumnName
Me.InstanceschemaColumnName
Me.GetServerName

For the getDatabaseNames()
Me.Connection
Me.DatabaseSchemaCollectionName

Where and how should I declare them??
I am putthing these code in the code behind of a user control, is that ok?

 

[jmcilhinney]

My code was supposed to be an example only. It was supposed to give you an idea of how you might implement it for yourself. You weren't supposed to copy it verbatim. I've told you what classes you should use and I've given you a general pattern to follow. You should primarily read the comments in my code to see what each part of it does, then research the types I suggested to see how you can use them to achieve your aim.

 

[a808y]

The classes you mentioned gave me pretty good idea, but the structure of your code seems to have problems. What are all the "Me" referencing? May be I just don't understand it enough. Thanks though.


Private Sub GetServerNames()
Dim dbProviderFactory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
If dbProviderFactory.CanCreateDataSourceEnumerator Then
Dim dbDataSourceEnumerator As DbDataSourceEnumerator = dbProviderFactory.CreateDataSourceEnumerator()
If dbDataSourceEnumerator IsNot Nothing Then
Dim dt As DataTable = dbDataSourceEnumerator.GetDataSources()
For Each row As DataRow In dt.Rows
ComboBox1.Items.Add(row(0))
Next
ComboBox1.Items.Add("<Enter manually...>")
End If
End If
ComboBox1.SelectedIndex = "0"
End Sub

I have the above code, but it seems pretty slow when the interface load.
Any idea on when it should run?

My code was supposed to be an example only. It was supposed to give you an idea of how you might implement it for yourself. You weren't supposed to copy it verbatim. I've told you what classes you should use and I've given you a general pattern to follow. You should primarily read the comments in my code to see what each part of it does, then research the types I suggested to see how you can use them to achieve your aim.

 

[jmcilhinney]

Me always refers to the current instance. I took that code form a Provider class of mine that wraps a DbProviderFactory. That's what Me.Factory refers to. The factory is used to create all the data access objects. This line:

Dim servers As DataView = [B][U]Me.Factory.CreateDataSourceEnumerator()[/U][/B].GetDataSources().DefaultView

is using the factory to generate a DbDataSourceEnumerator. I inherit that Provider class once for each data source. For instance, I have a SqlProvider class that inherits Provider and returns a SqlClientFactory from its Factory property. That means that Me.Factory.CreateDataSourceEnumerator() returns a SqlDataSourceEnumerator. A Provider for a different data source would have a different type of factory so it would return a different type of data source enumerator.

That also explains why there's a Connection property. That property is type DbConnection and each different provider returns the appropriate type of connection, e.g. the SqlProvider returns a SqlConnection. It also explains why I have properties for column names and the like: because each different data source may use different column names, so each provider must specify the appropriate column names for its corresponding data source.

It also explains the CanEnumerateDataSources property. Some ADO.NET providers don't include functionality to enumerate data sources. Each DbFactoryProvider has a CanCreateDataSourceEnumerator property. I use that to determine whether getting server names is possible or not.

 

[a808y]

Hi Thanks for the help and I almost got it done. My last question is when I get the list of database from a server, how can I filter out the system database such as master, model, msdb, and tempdb.

myconn.ConnectionString = stringBuilder.ConnectionString
myconn.Open()
rows = myconn.GetSchema("databases").Rows
myconn.Close()

Me always refers to the current instance. I took that code form a Provider class of mine that wraps a DbProviderFactory. That's what Me.Factory refers to. The factory is used to create all the data access objects. This line:

Dim servers As DataView = [B][U]Me.Factory.CreateDataSourceEnumerator()[/U][/B].GetDataSources().DefaultView

is using the factory to generate a DbDataSourceEnumerator. I inherit that Provider class once for each data source. For instance, I have a SqlProvider class that inherits Provider and returns a SqlClientFactory from its Factory property. That means that Me.Factory.CreateDataSourceEnumerator() returns a SqlDataSourceEnumerator. A Provider for a different data source would have a different type of factory so it would return a different type of data source enumerator.

That also explains why there's a Connection property. That property is type DbConnection and each different provider returns the appropriate type of connection, e.g. the SqlProvider returns a SqlConnection. It also explains why I have properties for column names and the like: because each different data source may use different column names, so each provider must specify the appropriate column names for its corresponding data source.

It also explains the CanEnumerateDataSources property. Some ADO.NET providers don't include functionality to enumerate data sources. Each DbFactoryProvider has a CanCreateDataSourceEnumerator property. I use that to determine whether getting server names is possible or not.

 

[jmcilhinney]

Have a look at the DataTable returned by GetSchema. I think you'll find that there's a column that indicates whether it's a system or a user database. If not then I think you can simply ignore the first four databases.

 

[a808y]

Hey thanks again,

Is the Datatable returned by GetSchema the same as the following SQL ?

select * from sys.sysdatabases

Have a look at the DataTable returned by GetSchema. I think you'll find that there's a column that indicates whether it's a system or a user database. If not then I think you can simply ignore the first four databases.

 

[jmcilhinney]

Hey thanks again,

Is the Datatable returned by GetSchema the same as the following SQL ?

select * from sys.sysdatabases

It would be something like that, if not exactly the same. GetSchema is just a convenient way to execute canned queries against system tables. If you need more control then you're certainly free to write your own queries and execute them directly.