data access layer connection problems RRS feed

  • Question

  • Hi guys

    I want to design a generic data access layer. Before in our programs we have a get connection method. In this method we opens a connection and retruns this connection. But getconnection takes a pooling parameter; if parameter is true we apply connection pooling. When we want to use executenonquery or want to retrieve data from database ; we call this getconnection method.

    So in every db operation we call this method and after making operation we close connection.

    So I think this can cause performance problems.

    Instead of calling getconnection method in every operation; I think to call this method on global asax session start method so one time this method wii be called. But at this time I must to use an external object in my data access layer and I can not make my data access layer as an independent class library.

    I want only one time connection is opened for same session; if any other sessions use same connection string, connection pool will used and in database operations I do not want to call getconnection method. I want to use existing connection. And on global asax session timeout event I want to close connection. But I am not sure whether I have problems about data readers.

    Can you guide me?



    Tuesday, January 27, 2009 10:01 AM

All replies

  • What you have right now is correct design of your application. You need to open connection only before you execute some SQL statements or stored procedure and close connection right after that. Keeping connection open all the time would lead to connection leaking issue and some other problems. Since you are using pooling for your connection, your application will not suffer big performance issues opening and closing database connections, but you will avoid potential issues if connections were not closed. If you believe application has some performance issues then you need to profile application and see where it spends most of execution time and I am pretty sure it is not connection issue.
    Val Mazur (MVP)
    Wednesday, January 28, 2009 11:02 AM