.Net Database how do I properly close my database connections?

Stan Shaw

So I've read a lot about SqlDataReaders not being disposed of properly in .Net - and I've been fighting the "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached" error for a couple days now. Obviously I could jack the max pool size up to 30,000 - but that doesn't address the actual problem.

As I step through the code, I execute the following SQL Query:

select * from sys.dm_os_performance_counters
where counter_name ='User Connections'

After the

cmd.Connection.Open(); 

line, the User Connections are incremented by 1. However, it NEVER goes back down unless I recycle the app pool on the web server (at which point all active database connections from the website are killed).

Here is my code:

public static DataTable SPExecuteDataTable(string[] ConnectionData, params object[] args)
{
    SqlConnection conn = null;
    SqlCommand cmd = null;
    SqlDataReader dr = null;
    try
    {
        conn = new SqlConnection(ConnectionData[1]);
        cmd = new SqlCommand(ConnectionData[0], new SqlConnection(ConnectionData[1]));
        cmd.CommandType = CommandType.StoredProcedure;

        for (int i = 0; i < args.Length; i++)
        {
            SqlParameter Param = new SqlParameter(ConnectionData[i + 2], DBNullIfNull(args[i]));
            cmd.Parameters.Add(Param);
        }

        cmd.Connection.Open();
        DataTable dt = new DataTable();

        using (dr = cmd.ExecuteReader())
        {
            if (dr != null)
                dt.Load(dr);
            else
                dt = null;
        }

        return dt;
    }
    catch (Exception e)
    {
        Exception x = new Exception(String.Format("DataAccess.SPExecuteDataTable() {0}", e.Message));
        throw x;
    }
    finally
    {
        conn.Close();
        cmd.Connection.Close();
        dr.Close();
        conn.Dispose();
        cmd.Dispose();
        dr.Dispose();
    }

So far, I've tried explicitly closing the connections (like in my finally block), but that doesn't work. I've also tried using statements like so:

using (SqlDataReader dr = blah blah blah)
{
    //code here
}

But that also doesn't work. What is wrong with my code, here?

Stan Shaw

Solution:

Use DataTables! To prevent having non-data-access layers of your application from having to communicate with the database, just do this in your data-access layer:

using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            if (dr != null)
                                dt.Load(dr);
                            else
                                dt = null;
                        }
                        return dt;

Then, you can manipulate the dt however you'd like in the rest of your solution, and the connections HAVE ALREADY BEEN properly disposed. Works like a charm and, luckily, the code for a datatable and datareader are very similar, so modifying the application in this manner was relatively painless.

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

How do i get MS EXCEL to Connect to MYSQL Database

来自分类Dev

How can I display the data from my Firebase Database in my web site?

来自分类Dev

How to properly close a pipe

来自分类Dev

IndexedDb:database.close()挂起

来自分类Dev

How to close all existing connections to a DB programmatically

来自分类Dev

How do I calculate the difference over time using one database table?

来自分类Dev

How do you create and edit tables etc in a PostreSQL database on heroku if I'm running Snow Leopard?

来自分类Dev

iOS/Objective-C - How do I query a web server database from an iPhone app?

来自分类Dev

With specific dates AND times in a database column, how do I query for all records on a specific day?

来自分类Dev

How i can onUpgrade me database in Android

来自分类Dev

How can I get my meteor app to connect to a database with MONGO_URL env variable in Meteor Up (mup)?

来自分类Dev

How do I add a close button to a tab of the MahApps TabControl?

来自分类Dev

I found a lot of weird string in my database, someone trying to get into my site?

来自分类Dev

How to Push some data from Database to asp.net webform?

来自分类Dev

How do i sort my listview alphabetically?

来自分类Dev

How do I link boost to my program?

来自分类Dev

How can I connect to a postgreSQL database into Apache Spark using scala?

来自分类Dev

How can I fetch a database, then show the result in a view?

来自分类Dev

How to restore postgres database into another database name

来自分类Dev

Should I serialize my data or create more columns for a SQL Server CE database?

来自分类Dev

How do I get the nth derivative in my scheme program?

来自分类Dev

How do i pass my scope correctly to look for variables

来自分类Dev

How do I use a Codeigniter method within my own function?

来自分类Dev

How do I get my aliases to have correct completion?

来自分类Dev

How do I use my Application Resources from another window?

来自分类Dev

How do I get MVC to find my controller path?

来自分类Dev

How do I choose the URL for my Spring Boot webapp?

来自分类Dev

How do I get my nested if statement to work in jQuery

来自分类Dev

How do I know if my app has been minimized?

Related 相关文章

  1. 1

    How do i get MS EXCEL to Connect to MYSQL Database

  2. 2

    How can I display the data from my Firebase Database in my web site?

  3. 3

    How to properly close a pipe

  4. 4

    IndexedDb:database.close()挂起

  5. 5

    How to close all existing connections to a DB programmatically

  6. 6

    How do I calculate the difference over time using one database table?

  7. 7

    How do you create and edit tables etc in a PostreSQL database on heroku if I'm running Snow Leopard?

  8. 8

    iOS/Objective-C - How do I query a web server database from an iPhone app?

  9. 9

    With specific dates AND times in a database column, how do I query for all records on a specific day?

  10. 10

    How i can onUpgrade me database in Android

  11. 11

    How can I get my meteor app to connect to a database with MONGO_URL env variable in Meteor Up (mup)?

  12. 12

    How do I add a close button to a tab of the MahApps TabControl?

  13. 13

    I found a lot of weird string in my database, someone trying to get into my site?

  14. 14

    How to Push some data from Database to asp.net webform?

  15. 15

    How do i sort my listview alphabetically?

  16. 16

    How do I link boost to my program?

  17. 17

    How can I connect to a postgreSQL database into Apache Spark using scala?

  18. 18

    How can I fetch a database, then show the result in a view?

  19. 19

    How to restore postgres database into another database name

  20. 20

    Should I serialize my data or create more columns for a SQL Server CE database?

  21. 21

    How do I get the nth derivative in my scheme program?

  22. 22

    How do i pass my scope correctly to look for variables

  23. 23

    How do I use a Codeigniter method within my own function?

  24. 24

    How do I get my aliases to have correct completion?

  25. 25

    How do I use my Application Resources from another window?

  26. 26

    How do I get MVC to find my controller path?

  27. 27

    How do I choose the URL for my Spring Boot webapp?

  28. 28

    How do I get my nested if statement to work in jQuery

  29. 29

    How do I know if my app has been minimized?

热门标签

归档