none
ADO.NET Connection Resiliency RRS feed

  • Question

  • Hi All,

    I am testing ADO.NET Connection Resiliency in .NET 4.5.1. I have a Windows forms application and in the Program.cs, I have a static SqlConnection and in the Main method I am opening the connection. In my Form1.cs, I have a simple button which will retrieve some data from a remote database.

    After running the application, I am closing the connection using TCPView. If ADO.NET Connection Resiliency is working, when I click on the button to get data, the connection should be automatically re-connected (well that's what I understood). But still I am getting the same old error "A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

    I really need to know what I am missing here.

    Appreciate your all help.


    Happy Coding.

    Regards,
    Jaliya Udagedara (MCPD,MCSD) | My Blog

    Wednesday, October 23, 2013 5:19 AM

Answers

  • This feature is called "ADO.NET Idle Connection Resiliency" and it has a server dependency, as you might have changed database context or SET options after opening the connection. 

    Also there is a .NET 4.0 feature that will reconnect connections that become disconnected while in the connection pool, and that doesn't have a server dependency.

    Currently Windows Azure SQL Database and SQL Server 2014 CTP2 support "ADO.NET Idle Connection Resiliency". 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, October 23, 2013 9:09 PM

All replies

  • A SqlConnection——If that's closed, it CANNOT automatically be re-opened unless you call Open method.

    But for a SqlDataAdapter, it will automatically re-open when you use Fill method into a DataTable to fetch out data contents into that instance.


    Click For donating:Free Rice For the poor
    For spamming-sender issues, you can either report it at Microsoft Spamming Issue, or just find "Report Spam Here+Number" at Forum Issue;You can also find "Verify Your Account+Number" at "Forum Issue", where you can submit to be confirmed to paste links or images.
    For more things to talk about? StackOverFlow is your choice.

    Wednesday, October 23, 2013 5:40 AM
  • Yes, it was the behavior of SqlConnection prior to NET Framework 4.5.1. With new .NET Framework 4.5.1, Microsoft has  introduced new features to manage ADO.NET Connection Resiliency.

    Please find the below link.

    http://blogs.msdn.com/b/dotnet/archive/2013/10/17/net-framework-4-5-1-rtm-gt-start-coding.aspx


    Happy Coding.

    Regards,
    Jaliya Udagedara (MCPD,MCSD) | My Blog

    Wednesday, October 23, 2013 7:04 AM
  • OK Jaliya, you are meaning that new feature in 4.5.1……

    So you can try:

    1) First don't disconnect to db but just keep connected to db and then click the button (yes, final result will be shown).

    2) Then Close connection forcely with TcpView.

    3) Click the button again to test.

    4) Notice it that your SqlConnection should be defined and Open at "constructor" instead of in a Button's click

    If still fails, please show us your detailled codes;)


    Click For donating:Free Rice For the poor
    For spamming-sender issues, you can either report it at Microsoft Spamming Issue, or just find "Report Spam Here+Number" at Forum Issue;You can also find "Verify Your Account+Number" at "Forum Issue", where you can submit to be confirmed to paste links or images.
    For more things to talk about? StackOverFlow is your choice.


    • Edited by ThankfulHeart Wednesday, October 23, 2013 7:27 AM Modify
    Wednesday, October 23, 2013 7:22 AM
  • Yes, the thing is, it's not working as expected. Here is my code.

    static class Program
        {
            public static SqlConnection MySqlConnection = new SqlConnection("Data Source=192.168.10.28;Initial Catalog=DemoDB;User ID=sa;Password=sa");
    
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                MySqlConnection.Open();
    
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Application.Run(new Form1());
            }

    Form1.cs

    void GetData()
            {
                string query = "SELECT * FROM Employee";
                SqlCommand cmd = new SqlCommand(query, Program.MySqlConnection);
                SqlDataReader reader = null; 
                try
                {
                    reader = cmd.ExecuteReader();
                    reader.Close();
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

    I am calling GetData() after closing the connection using TCPView. Still I am getting the error. Appreciate the help.




    Happy Coding.

    Regards,
    Jaliya Udagedara (MCPD,MCSD) | My Blog

    Wednesday, October 23, 2013 8:21 AM
  • Hi there,

    it is normal to receive an exception with the next "usage" of the connection if the tcp-connection itself  has been terminated from an "external" side, and not from the program itself.

    Can´t you just handle this specific exception? If you get an exception that tells you about a communication error, reopen the connection and try again.

    You should of course respect some recursive call issues (if the database is not reachable for any reason, you should not retry to connect "forever").

    Best regards,

    Andreas

    EDIT: Excuse me, just saw that you would expect that situation to be handled internally by a ADO.NET feature...


    Wednesday, October 23, 2013 9:48 AM
  • Hi Andreas, 

    Yes, with the new .NET Framework 4.5.1, reconnecting of ADO.NET idle connections (when needed) is handled by the .NET Framework it self. Now for me it's not working.


    Happy Coding.

    Regards,
    Jaliya Udagedara (MCPD,MCSD) | My Blog

    Wednesday, October 23, 2013 10:53 AM
  • This feature is called "ADO.NET Idle Connection Resiliency" and it has a server dependency, as you might have changed database context or SET options after opening the connection. 

    Also there is a .NET 4.0 feature that will reconnect connections that become disconnected while in the connection pool, and that doesn't have a server dependency.

    Currently Windows Azure SQL Database and SQL Server 2014 CTP2 support "ADO.NET Idle Connection Resiliency". 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, October 23, 2013 9:09 PM
  • Hi David,

    Thank you so much for clearing out the things. From what I read, there wasn't a single word about server dependency. So that's why it didn't work for me. I was connecting to a SQL Server 2008R2.

    Thanks again.


    Happy Coding.

    Regards,
    Jaliya Udagedara (MCPD,MCSD) | My Blog

    Thursday, October 24, 2013 8:01 AM