Professional Web Applications Themes

Best Practices for using SqlConnection - ASP.NET General

"Jonathan Simms" <com> wrote in message news:aIw2b.195018$srv.hcvlny.cv.net...  I find it is easier to put a connection in the web config, that way you dont have to declare it on each page! Also, in the future, if you need to change your connection to another database, you only need to change one instance of the connection, and not on each page. Example: In the web.config under the <configuration> tags, I have: <appSettings> <add key="SQLConn" value="server=192.168.1.1;user id=userl;password=pass;initial catalog=mydb" /> </appSettings> I then have a library file, ( library.vb) which I have the following code: (MyLib = the class name of the library) ...

  1. #1

    Default Re: Best Practices for using SqlConnection


    "Jonathan Simms" <com> wrote in message
    news:aIw2b.195018$srv.hcvlny.cv.net... 

    I find it is easier to put a connection in the web config, that way you dont
    have to declare it on each page!

    Also, in the future, if you need to change your connection to another
    database, you only need to change one instance of the connection, and not on
    each page.

    Example:

    In the web.config under the <configuration> tags, I have:

    <appSettings>
    <add key="SQLConn" value="server=192.168.1.1;user
    id=userl;password=pass;initial catalog=mydb" />
    </appSettings>


    I then have a library file, ( library.vb) which I have the following code:
    (MyLib = the class name of the library)

    ' ******************** Database connection information
    *********************
    Public Const DBSource As MyLib.DBConn = MyLib.DBConn.SQLConn

    Public Shared Function GetDBConn(ByVal MyConn As DBConn) As
    SqlClient.SqlConnection
    Return New
    SqlClient.SqlConnection(ConfigurationSettings.AppS ettings(MyConn.ToString))
    End Function

    '** You can put here the name of your connection(s) from the web.config
    Public Enum DBConn
    SQLConn
    End Enum



    And then, in my code, if I want to call the connection:

    Dim sqlcmd As New SqlCommand("", MyLib.GetDBConn(MyLib.DBSource))


    Krissy Guest

  2. #2

    Default Re: Best Practices for using SqlConnection

    I would recommend using the Application Block at:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
    In addition, I agree with Krissy on putting the connection string in the
    web.config file. If you look at the code in the application block, you'll
    see that it creates and destroys the connection for each operation. This
    seems to be quite fast in operation, I'm using it for all the SQL access in
    my current application.

    "Krissy" <com> wrote in message
    news:3f4aaac9$wn.com.au... [/ref]
    page 
    >
    > I find it is easier to put a connection in the web config, that way you[/ref]
    dont 
    on 
    SqlClient.SqlConnection(ConfigurationSettings.AppS ettings(MyConn.ToString)) 


    Rick Guest

  3. #3

    Default Re: Best Practices for using SqlConnection

    I definately recommend the MS Data Access Application Block. You may wish
    to scale it down for your use. For example, each overloaded method takes a
    SqlConnection object for later use. Most applications I work on only
    require one connection string, so I will create the SqlConnection in the
    final overloaded method in which it is used, as opposed to requiring the
    calling code to create and manage the connection. In the OnStart event of
    the application, I will set a public static property on the SqlHelper class
    in the data access app block.

    Also, I prefer to store the connection string encrypted using the DPAPI
    (available on w2k+) in the machine.config. I believe this to be a bit more
    secure, and my connection strings often differ from machine to machine.

    "Rick Spiewak" <com> wrote in message
    news:phx.gbl... 
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp 
    in [/ref]
    > page 
    > >
    > > I find it is easier to put a connection in the web config, that way you[/ref]
    > dont [/ref]
    not [/ref]
    code: 
    >[/ref]
    SqlClient.SqlConnection(ConfigurationSettings.AppS ettings(MyConn.ToString)) 
    >
    >[/ref]


    PJ Guest

  4. #4

    Default Re: Best Practices for using SqlConnection

    Hi,

    Itís better to keep the connection string in one place but not
    SqlConnection (object). SqlConnection should be open and close just
    after you finish your work against DB. Its bad practice to use one
    connection, open it when application starts, and use it for every
    request.

    The best practice is not to deal with DB in the pages at all! Create
    dedicate DLL that will handle all the DB access stuff and will be called
    from your pages. This layer approach will help you maintain easily your
    application.

    If you are going to adopt this layer approach and separate Data from
    visualization keep in mind that DtataSets break this model (and harm
    performance) since they hold DB definitions. Consider use one of the ORM
    tools instead.

    Natty Gur, CTO
    Dao2Com Ltd.
    34th Elkalay st. Raanana
    Israel , 43000
    Phone Numbers:
    Office: +972-(0)9-7740261
    Fax: +972-(0)9-7740261
    Mobile: +972-(0)58-888377


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Natty Guest

Similar Threads

  1. help with sqlconnection!!
    By Shakazulu89 in forum Macromedia Flex General Discussion
    Replies: 7
    Last Post: April 15th, 09:45 AM
  2. SqlConnection Problem
    By Calvin Willman in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 23rd, 02:09 PM
  3. SqlConnection, SqlCommand
    By Donnie Darko in forum ASP.NET Web Services
    Replies: 3
    Last Post: July 21st, 02:46 PM
  4. Access Denied to sqlConnection
    By John Giblin in forum ASP.NET Security
    Replies: 1
    Last Post: July 17th, 03:41 PM
  5. Reflection and SqlConnection
    By WebDevHokie in forum ASP.NET General
    Replies: 1
    Last Post: July 10th, 06:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139