DataConnection wizard in application

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jakob Lithner - 30 Jan 2007 12:02 GMT

I need to let the users change data connection in runtime.
I tried to let them specify the SQL connection string by hand, but it is
quite complicated to remember and an error is fatal.

I read somewhere that it should be possible to use the inbuilt connection
wizard used by the Visual Studio IDE. That would certainly be much more
elegant!
Is it possible? How can it be done?

Reply to this Message

Mark Nelson - 30 Jan 2007 15:11 GMT

Jakob Lithner,

I played with the Connection String wizard of VS 2005 IDE. During
configuration you can switch between connection strings. When you are trying
to specify a connection string IDE asks you whether to save in the app.config
file. If you say yes, all the connection strings are stored in the
app.config file like the following.

<configuration>
<configSections>
</configSections>
<connectionStrings>
<add
name="DynamicConnection.Properties.Settings.AdventureWorksConnectionString"
connectionString="Data Source=CTSINTCOSGTO1;Initial
Catalog=AdventureWorks;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add
name="DynamicConnection.Properties.Settings.masterConnectionString"
connectionString="Data Source=ctsintcosgto1;Initial
Catalog=master;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>

But this does not provide the flexibility of making the user to change to
connection string at runtime. To achieve that, use this app.config and have
the user to select
from UI and thenk figure out accordingly to which database yoiu need to
connect.

The prinicple is that you need to have some identifier or a flag to
decide which connection string to hook-up. This only will achive the
switching of connection strings during run-time

Signature

Thanks & Regards,
Mark Nelson

> I need to let the users change data connection in runtime.
> I tried to let them specify the SQL connection string by hand, but it is
[quoted text clipped - 4 lines]
> elegant!
> Is it possible? How can it be done?

Reply to this Message

Jakob Lithner - 30 Jan 2007 15:44 GMT

Thanks for your suggestion.
But as you say yourself this will only take you part of the way.
I still hope for a fully flexible solution where the user can configure and
test a specified connection.

Reply to this Message

Linda Liu [MSFT] - 31 Jan 2007 03:27 GMT

Hi Jakob,

I am sorry I don't think there's a way for us to use the inbuilt connection
wizard used by the Visual Studio IDE in our own program.

But .NET 2.0 has brought us SqlConnectionStringBuilder class, which
provides a simple way to create and manage the contents of connection
strings used by the SqlConnection class.

I suggest that you create a UI for the user to specify each part of the
connection string and then use SqlConnectionStringBuilder to construct a
complete connection string.

For more information on SqlConnectionStringBuilder class and how to use it,
you may visit the following link.

'SqlConnectionStringBuilder Class'
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection
stringbuilder.aspx

Hope this helps.
If you have anything unclear, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Jakob Lithner - 31 Jan 2007 12:05 GMT

The SqlConnectionStringBuilder is a good help.
I can easily provide my own GUI to manipulate the different settings.

BUT: How do I save the resulting ConnectionString to my ConnectionString
setting?
I suddenly found out that this setting is ReadOnly!
Why is that? Is it not possible to override or change through some other
method?

Reply to this Message

Linda Liu [MSFT] - 01 Feb 2007 11:40 GMT

Hi Jakob,

Yes, you're right that the ConnectionString setting is readonly. The
desginers of application settings consider the connection string setting
should not be modified at run time. So this behavior is by design.

If you do want to change the connection string and save it to the
configuration file at run time, you may do it using ConfigurationManager
and Config classes to accomplish this task.

The following is a sample. Note that you should add a reference to
System.Configuration.dll into the project first.

Imports System.Configuration

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim config As Configuration =
ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)

Dim csSection As ConnectionStringsSection =
CType(config.GetSection("connectionStrings"), ConnectionStringsSection)

' the "DataAdapterMapping.My.MySettings.dataconn" is the name of
the connection string setting

csSection.ConnectionStrings("DataAdapterMapping.My.MySettings.dataconn").Con
nectionString = "the new connection string"

config.Save(ConfigurationSaveMode.Modified)

End Sub

Please try my suggestion and let me know the result.

Sincerely,
Linda Liu
Microsoft Online Community Support

Reply to this Message

Jakob Lithner - 01 Feb 2007 14:03 GMT

Very good suggestion!
I thought there must be a way.
The only drawback is that it doesn't work .... :-)

The settings value is actually changed to file which is a major step forward.
But this change is not observed by the My.Settings object.
I tried to force My.Settings.Reload but it did not help.
Any suggestions?

I enclose my procedure with minor adjustments:

Private Sub SaveConnection(ByVal connectionName As String, ByVal
connectionString As String)
Dim Config As Configuration
Dim Section As ConnectionStringsSection
Dim ConnectionFullName As String

Try
Config =
ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
Section = CType(Config.GetSection("connectionStrings"),
ConnectionStringsSection)
ConnectionFullName = String.Format("{0}.My.MySettings.{1}",
System.Reflection.Assembly.GetExecutingAssembly.GetName.Name, connectionName)

Section.ConnectionStrings(ConnectionFullName).ConnectionString =
connectionString
Config.Save(ConfigurationSaveMode.Modified)

Catch ex As Exception
Throw New Exception(String.Format("Failed saving connection '{0}': {1}",
connectionName, ex.Message), ex)
End Try
End Sub

Reply to this Message

Linda Liu [MSFT] - 02 Feb 2007 03:29 GMT

Hi Jakob,

Thank you for your prompt feedback.

Since the connection string setting is a application scoped setting, it
won't re-read the value from the config file even if we call the
My.MySettings.Reload method.

In fact, we could set the value of a application scoped setting at run-time
by another means, i.e. using the indexed property Item to access the
setting.

The following is a sample.

My.MySettings.Default.Item("dataconn") = "the new connection string"

FYI, by the above means, we could change the value of the application
scoped setting at run time, but the new value won't be saved back to the
config file after we call the My.MySettings.Save method. To save the new
value of the setting, you may use the means I suggested in my previous
reply.

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

Reply to this Message

Jakob Lithner - 02 Feb 2007 15:56 GMT

Excellent, Thanks!

It all works very nice now!
Actually I was so satisfied with my demo that I shared it on
www.codeproject.com for those interested. With proper credit of course to you
Linda, for all crucial suggestions!

Reply to this Message

LLcoolQ@gmail.com - 31 Jan 2007 13:28 GMT

On Jan 30, 10:27 pm, v-l...@online.microsoft.com (Linda Liu [MSFT])
wrote:

> Hi Jakob,
>
[quoted text clipped - 39 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights.

Here is a good example of what I think you are looking for:

http://adoguy.com/viewrant.aspx?id=2193

jay

Reply to this Message

Jakob Lithner - 31 Jan 2007 16:14 GMT

Interesting suggestion according to my initial track!
But for a couple of reasons I am still a bit hesitant:

1) Is it possible to enter current connection and let the user adjust it or
is it necessary to always define a new connection from scratch?

2) How about the licensing? These files are placed in a directory intending
that they are not part of the runtime environment. Are you sure you can
include these files in the deployment?

Reply to this Message

Larry Dodd - 31 Jan 2007 05:04 GMT

Here is an example of how to use the Property Grid to work with SQL
Connection strings. You will find the example in the download.

http://www.codeproject.com/vb/net/using_propertygrid.asp

> Thanks for your suggestion.
> But as you say yourself this will only take you part of the way.
> I still hope for a fully flexible solution where the user can configure
> and
> test a specified connection.

Reply to this Message

Otis Mukinfus - 30 Jan 2007 23:32 GMT

>I need to let the users change data connection in runtime.
>I tried to let them specify the SQL connection string by hand, but it is
[quoted text clipped - 4 lines]
>elegant!
>Is it possible? How can it be done?

This should get you started. It's not the wizard, but it works.

Add a PropertyGrid object to a form and let them fill in the property grid.

Here is some code from a form that does that:

        private void testForm_Load(object sender, EventArgs e)
        {
            SqlConnectionStringBuilder _sqlBldr = new SqlConnectionStringBuilder();
            //propGrid.SelectedObject = _sqlBldr;
            sqlServerRadioButton.Checked = true;
            _cnType = "SQL";
        }

        private void copyButton_Click(object sender, EventArgs e)
        {
            switch (_cnType)
            {
                case "SQL":
                    if (_sqlBldr.ConnectionString != "")
                        Clipboard.SetDataObject(_sqlBldr.ConnectionString);
                    break;
                case "OLE":
                    if (_oleBldr.ConnectionString != "")
                        Clipboard.SetDataObject(_oleBldr.ConnectionString);
                    break;
                case "ORA":
                    if (_oraBldr.ConnectionString != "")
                        Clipboard.SetDataObject(_oraBldr.ConnectionString);
                    break;
            }
        }
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

Reply to this Message

DataConnection wizard in application

Free Magazines