locked
How do I test a connection without freezing the program, and then connect to the proper database? RRS feed

  • Question

  • I'm a bit of a Winforms/C#/ADO/VS2005 newbie, so bear with me here.

     

    Two questions:

     

    1) I have an application that needs to run in two locations, but connects to one database server. It needs to run on a 192.168.0.x network, and a 10.0.0.x network. The two locations are physically nearby and the 10. network uses a specific IP address as an open gateway to route to a machine on the 192. network. (i.e. pinging 10.0.0.5 will NAT over to 192.168.0.105 on the other network.)

     

    I need to be able to test a connection at runtime and pick whichever connection is proper for the specific machine. I could just set this in the application's configuration file, which contains the connection string. But for computers that move between the two networks (i.e: laptops), that's not a great solution.

     

    The connection string is listed in my config file as "Namespace.Properties.Settings.ConnectionName" -- but it can only be accessed at runtime, not changed. How do I, without writing the gobs of code that it would take to change it for each query type, set the default connection string at runtime?

     

    2) How can I check the database connection in the background without the form and application locking up until the connection times out?

     

    Thanks,

    Karl Katzke

    Thursday, August 16, 2007 5:39 PM

Answers

  • 1) If you're using a typed dataset, setting the Connection property on the TableAdapter propagates the Connection down to the individual Command objects.  (Take a look at the setter for the Connection property of any of the TableAdapters in the generated Designer.cs code.  It's pretty useful to review this code and figure out what the TableAdapters are actually doing.)

     

    2) The same way you do anything else in the background - with the mighty BackgroundWorker component. See this thread for some sample code that explains most of the pieces of that little puzzle. (It doesn't address cancellation and progress reporting; for that you'll need to do some research.)

    Thursday, August 16, 2007 8:06 PM

All replies

  • 1) If you're using a typed dataset, setting the Connection property on the TableAdapter propagates the Connection down to the individual Command objects.  (Take a look at the setter for the Connection property of any of the TableAdapters in the generated Designer.cs code.  It's pretty useful to review this code and figure out what the TableAdapters are actually doing.)

     

    2) The same way you do anything else in the background - with the mighty BackgroundWorker component. See this thread for some sample code that explains most of the pieces of that little puzzle. (It doesn't address cancellation and progress reporting; for that you'll need to do some research.)

    Thursday, August 16, 2007 8:06 PM
  • 1) Ok, thanks. How would you suggest handling this program-wide so that I don't have to keep setting it every time I instantiate a form or call a TableAdapter? Is there a best practice?

     

    2) Thanks, that helps a lot. Now to figure it out....

    Friday, August 17, 2007 12:27 AM
  • It's a tricky problem to solve, because the TableAdapter classes are all different types that don't derive from anything useful. 

     

    This is one approach (as you might imagine, none of the code in this post has been tested, but I'm pretty sure the underlying ideas are sound):

     

    Code Snippet

     

       public static class AdapterFactory

       {

          public static SqlConnection Connection;

     

          public static MyDataSet.TableAdapter1 CreateTableAdapter1()

          {

             MyDataSet.TableAdapter1 a = new MyDataSet.TableAdapter1();

             a.Connection = AdapterFactory.Connection;

             return a;

          }

     

          public static MyDataSet.TableAdapter2 CreateTableAdapter2()

          {

             MyDataSet.TableAdapter2 a = new MyDataSet.TableAdapter2();

             a.Connection = AdapterFactory.Connection;

             return a;

          }

       }

     

     

    Once you've set AdapterFactory.Connection, instead of the usual idiom:

     

    Code Snippet
       MyDataSet.TableAdapter1 = new MyDataSetTableAdapter1();

     

     

    you write

     

    Code Snippet

       MyDataSet.TableAdapter1 = AdapterFactory.CreateTableAdapter1();

     

     

    It's still something of a pain, because you have to write a method for every TableAdapter class.  But at least it consolidates it all in one place.

     

    You could also use partial classes, and either put a static Create(string connectionString) method in each TableAdapter class or overload the constructors:

     

    Code Snippet

    public partial class TableAdapter1 : System.ComponentModel.Component

    {

       public TableAdapter1(string connectionString)

       {

          this._connection = new System.Data.SqlClent.SqlConnection();

          this._connection.ConnectionString = connectionString;

       }

    }

    public partial class TableAdapter2 : System.ComponentModel.Component

    {

       public TableAdapter2(string connectionString)

       {

          this._connection = new System.Data.SqlClent.SqlConnection();

          this._connection.ConnectionString = connectionString;

       }

    }

     

     

    I would tend to prefer using a static class to create the adapters, because then the connection string's always kept in exactly one place.

     

    Yet another possible approach would be to hide the TableAdapters from your code completely.  This will work if the DataTables map one-to-one onto TableAdapters, and if you are really doing all of your fills and updates one table at a time:

     

    Code Snippet

    public static class AdapterWrapper()

    {

       public static SqlConnection Connection;

     

       public static void Fill(MyDataTable1 dt)

       {

          MyDataSet.TableAdapter1 a = new MyDataSet.TableAdapter1();

          a.Connection = Connection;

          a.Fill(dt);

       }

       public static void Update(MyDataTable1 dt)

       {

          MyDataSet.TableAdapter1 a = new MyDataSet.TableAdapter1();

          a.Connection = Connection;

          a.Update(dt);

       }  

       public static void Fill(MyDataTable2 dt)

       {

          MyDataSet.TableAdapter2 a = new MyDataSet.TableAdapter2();

          a.Connection = Connection;

          a.Fill(dt);

       }

       public static void Update(MyDataTable2 dt)

       {

          MyDataSet.TableAdapter2 a = new MyDataSet.TableAdapter2();

          a.Connection = Connection;

          a.Update(dt);

       }   

    }

     

    This imposes significant limits (like if you want to pass a DataRow or the DataSet to an Update method, you have to implement yet another overload), but it results in really concise code; you just write

     

    Code Snippet

       dt = new MyNamespace.DataTable1

       AdapterWrapper.Fill(dt)

     

       ...

     

       AdapterWrapper.Update(dt);

     

     

    No more table adapters!

     

     

    Friday, August 17, 2007 6:54 AM