none
Best Practice Question: Where to define connection? RRS feed

  • Question

  • All,

     

    Please forgive me if this question has been answered here or elsewhere. I've spent quite a bit of my work and free time combing resources (MSDN, help files, third party books, Google) and apparently am looking in all the wrong places.

     

    I am developing a fairly simple MDI application using Visual Studio 2003, .NET 1.1, Visual Basic.NET, and ADO.NET 1.x connecting to a SQL Server 2000 instance. I am defining a connection each MDI Child form. It occurred to me that this may not be the best way to approach the situation. While it probably isn't critical for an application with 4 forms and 6 reports, it might be for larger applications.

     

    So what's the general consensus on this? Is it best to define a connection instance in each MDI child form? Or would it be better to define one connection instance that the entire application shares? If it is the latter, is it a better idea to define this connection instance in the MDI parent form, or in a module? Also, if it is best to implement one shared connection instance, where might I find good examples of how to implement this practice?

     

    Thanks very much for your assistance and patience!

    Christopher

    Tuesday, October 30, 2007 2:54 PM

Answers

  • Hey Chris,

    Let me try to answer your question.

    It will be a lot better as you have mentioned if you could open the connection to the database when your Parent form loads and share that connection among all your child forms. If many connections are open to a particular database it will create a performance problem for large scale applications, again you have mentioned this in your first post.

    I hope that you are very well aware as to how would you actually do the same in your parent form. If you don't, let me know, I can give it a try.

    Hope this answers your question.

    Regards,
    Abhisheik
    Wednesday, October 31, 2007 4:57 AM

All replies

  • Hi,

     

    Add your connection string to the app.config file

    <?xml version="1.0" encoding="utf-8" ?>

    <configuration>

    <connectionStrings>

    <add name="MyConnectionString" connectionString="Data Source=<DS Name>;Initial Catalog=Somethin;Integrated Security=True" />

    </connectionStrings>

     

    Then create class and use the class to return the connection string :

     

    SqlConnection sqlCon = new SqlConnection(ConfigurationSettings.AppSettings["MyConnectionString"].ToString());

    sqlCon.Open();

     

    Check this link to see a sample. Even though this link is for .NET 2.0 apps, you will get a fair idea of what I am talking about

     

    You could also try the Enterprise Application Blocks.

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

    Tuesday, October 30, 2007 3:19 PM
  • Hi,

     

    Check this example related to .NET 1.1

     

    http://www.codeproject.com/useritems/ReadWriteConfig.asp

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

     

    Tuesday, October 30, 2007 3:22 PM
  • Suprotim,

     

    Thanks for the replies, but I am not sure they answer my question. I understand how to set the connect string in a configuration file. That is not a problem.

     

    My question is more about instantiation. For example, I have an application with 4 forms, 1 MDI parent, 3 MDI children. Should each child form instantiate its own connection object? Or is it better to instantiate one connection object (say, during the form load of the MDI parent) and share it among all three of the child forms?

     

    Sorry if the original post wasn't clear on that point!

     

    Any insight is appreciated,

    Christopher

    Tuesday, October 30, 2007 7:59 PM
  • Hey Chris,

    Let me try to answer your question.

    It will be a lot better as you have mentioned if you could open the connection to the database when your Parent form loads and share that connection among all your child forms. If many connections are open to a particular database it will create a performance problem for large scale applications, again you have mentioned this in your first post.

    I hope that you are very well aware as to how would you actually do the same in your parent form. If you don't, let me know, I can give it a try.

    Hope this answers your question.

    Regards,
    Abhisheik
    Wednesday, October 31, 2007 4:57 AM
  • Abhisheik,

     

    That is precisely what I wanted to know, for exactly the reason I wanted to know. I questioned whether this was still the best practice based on what I've read about connection pooling in ADO.NET.

     

    Thanks for the help!

    Christopher

    Wednesday, October 31, 2007 2:47 PM
  • Hey Chris,

     

    I've had the same kind of usage and code layout problem/concerns. I've had to conclude that some of .Net usage patterns are materially counter-intuitive to conventional programming. In particular, object creation (memory reservation, et al) seems to be very cheap...for some objects (in an undocumented way....).

     

    As a result, it seems you should not think twice about using just-in-time object creation as a means of managing visibility or to support your code layout needs, at least for connection objects. In fact, that's how ADO.Net data adapters do it, instantiating and destroying connection objects (from connectionstrings typically stored in the configuration file) on the fly.

     

    Consequently, don't worry about preserving a connection object instance and then trying to ensure its visibility, you will be undermined by the .Net framework expectations, tools, classes, and documentation.

     

    Infact, .Net 2.0 has introduced a new class, TransactionScope, that automatically manifest coordinated transactions as required across multiple connection instances; the absence of this particular class in .Net 1.x was obviously an oversight in light of the implicit instantiation pattern of ADO.Net.

     

    George.

    Wednesday, January 16, 2008 9:43 PM