0

I am writing an application using ASP.NET webforms with login dialog. After successfull authentication the connection is opened, but connection object is not visible from other webforms. Is there any way to make it persistent and accessible (in some server session objects), or is it commonly done in another way ? Any tips appreciated.

Jan Bohac
  • 69
  • 7
  • The database Connection should not be left open unless it's actually performing anything. I.e when you login you open a Connection to check the user credentials, then Close it again. A common way of doing this is to have a Data Access Layer that you call in order to do DB operations, otherwise I'd recommend that you set up some static function that returns the Connection object to the Calling method and open/Close the db from that method. – JaggenSWE Apr 18 '16 at 12:35

2 Answers2

1

The best way is to open/close the connection where you want to use it. Don't share it and never use a static connection in ASP.NET which is a multi-threading environment. You should use the using statement for all objects implementing IDisposable which ensures that all unmanaged resources are disposed. This will also close the connection, even on error:

using(var con = new SqlConnection("connection string here"))
{
    // do something ...
}

You don't need to be afraid that the physical connection must be opened/closed everytime. The .NET connection pool will handle that for you. Related

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
1

A simple example.

Set up a class to handle the SqlConnection object (or whatever DB you're using):

DbConnectionProvider.cs

using System.Data.SqlClient

public class DbConnectionProvider
{
  public static SqlConnection GetDbConnection()
  {
    return new SqlConnection("MyConnectionString");
  }
}

Then from all classes that needs to use the database

ApplicationClass.cs

using System.Data.SqlClient

public class ApplicationClass
{
  private void GetSomeDbWorkDone()
  {
     using(SqlConnection Conn = DbConnectionProvider.GetDbConnection())
     {
       //Do some fancy database operations here
     }
  }
}

This way, if you need to change your Connection details or do something else regarding the SqlConnection object, you only need to do it once and not everywhere in your code.

JaggenSWE
  • 1,950
  • 2
  • 24
  • 41
  • 2
    What is the benefit of that class? Imo it just introduces an unneeded layer. This is a source of disimprovements if someone thinks that it would be smart to make the connection static. – Tim Schmelter Apr 18 '16 at 12:42
  • @TimSchmelter "What is the benefit of that class?" Separation of concerns. One provider class concerns itself with _connecting_ to the database, so that every other consumer class can concern itself with _using_ the database. Make it an interface, return `IDbConnection`, suddenly you can swap it out for any type of database connection and the consumer class doesn't even need to know or care. – JuSTMOnIcAjUSTmONiCAJusTMoNICa May 25 '21 at 19:16