none
How to prevent this exception network related instance from display? RRS feed

  • Question

  • problem

    An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

    Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL

    I need to prevent this echo from display and on same time when connection have problem then stop and when connection available then reconnect without close console app and reopen again

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.SqlClient;
    using System.Data;
    using ConsoleApp1;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            public static string connection = @"Server=xxxxx;Database=xxxx;User Id=xxxx;Password=xxxx;";
            static void Main(string[] args)
            {
    
                FileValidation();
            }
            public static void FileValidation()
            {
                SqlConnection con = new SqlConnection(connection);
               
                    while (true)
                    {
                        //---------------- Get FilePending
                        DataTable DTRowData = new DataTable();
                        DTRowData = GetRowData();
                        //-----------------------------------------------
                        foreach (DataRow Row in DTRowData.Rows)
                        {
                            Console.WriteLine("Start Working In File : " + Row[0].ToString());
    
                            con.Open();
                            using (SqlCommand cmd = new SqlCommand("SP_FilesRunValidationOnFile2", con))
                            {
                                
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Add("@FileID", SqlDbType.VarChar).Value = Row[0];
                                cmd.ExecuteNonQuery();
    
                                Console.WriteLine("File Number : " + Convert.ToString(Row[0]) + "  Validated Successfully");
                                con.Close();
    
                            }
                        }
    
                        System.Threading.Thread.Sleep(10000);                
                }
            }
    
            public static DataTable GetRowData()
            {
                DataTable dt;
                SqlConnection con = new SqlConnection(connection);
                con.Open();
                using (SqlCommand cmd = new SqlCommand("sp_FilesGetPending", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    dt = new DataTable();
                    da.Fill(dt);
                    con.Close();
                    return dt;
                }
            }
    
        }
    }
    =====================End cs============================================
    Sql
    =======================================================================
    Create proc [dbo].[sp_FilesGetPending]
    as
    select Top 10 FileID from Files With (Nolock) where FileStatusID=0
    ======================================================================= 
    
      Create proc [dbo].[SP_FilesRunValidationOnFile2]
    	   @FileID int
    	   as
              declare @FileStartTime  DateTime =getdate()
    	   
              declare @ValidationDuration  int  =datediff (second ,  @FileStartTime ,  getdate() )
    
    
    	  Update  Files  set  FileStatusID =  1  , [FileDuration]=@ValidationDuration where  FileID=@FileID
    
    	
    CREATE TABLE [dbo].[Files](
    	[FileID] [int] IDENTITY(1,1) NOT NULL,
    	[FileName] [nvarchar](500) NULL,
    	[FileStatusID] [int] NULL,
    	[FileDuration] [int] NULL,
     CONSTRAINT [PK_TblFile] PRIMARY KEY CLUSTERED 
    (
    	[FileID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    


    Tuesday, March 3, 2020 11:52 AM

Answers

  • Hi,

    Has your problem been solved?

    If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 10, 2020 8:52 AM

All replies

  • Hello,

    The first thing to try is a while loop with n amount of retries in tangent with inspecting the reason why the connection failed and possibly setup a retry interval.

    Everything is laid out in the following Stackoverflow post, take you time reading through the replies rather than simply copy/pasting one of the suggestions as you might find they need slight modifications and pay close attention to the various exception types that may be thrown.

    Here is a start from the post link above.

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp2
    {
        class Program
        {
            static int _maximumNumberOfRetries = 3;
            static readonly int[] _retriableClasses = { 13, 16, 17, 18, 19, 20, 21, 22, 24 };
            static SqlConnection _connection = new SqlConnection();
    
            static void Main(string[] args)
            {
            }
    
            static void DoWork()
            {
                try
                {
                    var rebuildConnection = true; // First try connection must be open
    
                    for (int index = 0; index < _maximumNumberOfRetries; ++index)
                    {
                        try
                        {
                            // (Re)Create connection to SQL Server
                            if (rebuildConnection)
                            {
                                _connection?.Dispose();
    
                                // Create connection and open it...
                            }
    
                            // Perform your task
    
                            // No exceptions, task has been completed
                            break;
                        }
                        catch (SqlException e)
                        {
                            if (e.Errors.Cast<SqlError>().All(x => CanRetry(x)))
                            {
                                // What to do? Handle that here, also checking Number property.
                                // For Class < 20 you may simply Thread.Sleep(DelayOnError);
    
                                rebuildConnection = e.Errors.Cast<SqlError>().Any(x => x.Class >= 20);
                                continue;
                            }
    
                            throw;
                        }
                    }
                }
                finally
                {
                    // clean up resources
                }
            }
            private static bool CanRetry(SqlError error)
            {
                // Use this switch if you want to handle only well-known errors,
                // remove it if you want to always retry. A "blacklist" approach may
                // also work: return false when you're sure you can't recover from one
                // error and rely on Class for anything else.
                switch (error.Number)
                {
                    // Handle well-known error codes, 
                }
    
                // Handle unknown errors with severity 21 or less. 22 or more
                // indicates a serious error that need to be manually fixed.
                // 24 indicates media errors. They're serious errors (that should
                // be also notified) but we may retry...
                return _retriableClasses.Contains(error.Class); // LINQ...
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 3, 2020 12:15 PM
    Moderator
  • thank you for reply

    can you merge code above with code i write

    meaning i need full code

    i m not lazy but i need to know which place i will put code above

    Tuesday, March 3, 2020 12:21 PM
  • while (true)
                    {
    		try
    		{
                        //---------------- Get FilePending
                        DataTable DTRowData = new DataTable();
                        DTRowData = GetRowData();
                        //-----------------------------------------------
                        foreach (DataRow Row in DTRowData.Rows)
                        {
                            Console.WriteLine("Start Working In File : " + Row[0].ToString());
    			if (con.State == ConnectionState.Closed)  // the state can check wher open the connection with loop also
    
                            con.Open();
                            using (SqlCommand cmd = new SqlCommand("SP_FilesRunValidationOnFile2", con))
                            {
                                
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Add("@FileID", SqlDbType.VarChar).Value = Row[0];
                                cmd.ExecuteNonQuery();
    
                                Console.WriteLine("File Number : " + Convert.ToString(Row[0]) + "  Validated Successfully");
                                con.Close();
    
                            }
                        }
    
                        System.Threading.Thread.Sleep(10000);  
    		}
    		catch(SqlException e)
    		{	
    		Console.WriteLine("error :" + e.toString());
    		}       
    	       
                }
    
    
     
    

    Tuesday, March 3, 2020 12:49 PM
  • thank you for reply

    are add thread.sleep (1000)

    on catch to retry connection when fails or not necessary

    catch(SqlException e)
    		{	
    		Console.WriteLine("error :" + e.toString());
    thread.sleep(1000)
    		}   

    Tuesday, March 3, 2020 1:01 PM
  • thank you for reply

    can you merge code above with code i write

    meaning i need full code

    i m not lazy but i need to know which place i will put code above

    I'll see what time I have as it's not simply integrating the code but also testing it which is the hard part to properly simulate a broken connection that is what it's like in the wild rather than simply stopping/starting the SQL-Server service which is unrealistic.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 3, 2020 1:39 PM
    Moderator
  • hello friend

    Why you use loop from C# application create a type in database and send all the rows as  whole datatable to the procedure and in the procedure retrieve each row from the table ,then use in your transaction.

    Using cursor for it. Bellow is the code try to use .One connection open send all the rows at a time

    //cmd.Parameters.Add("@FileID", SqlDbType.VarChar).Value = Row[0];
    // instead of the above line add the bello line
    cmdAddProductDetails.Parameters.AddWithValue("@tblcontryDetails", DTRowData);
    
                                cmd.ExecuteNonQuery();
    
    
    
    -- Bellow define a sql type
    GO
    CREATE TYPE [dbo].[tblcontryDetails] AS TABLE(
    [Name] [varchar](100) NULL
    )
    GO
    
    -- bellow is the stored procedure and loop
    
    CREATE PROCEDURE [dbo].[SP_FilesRunValidationOnFile2]
    @tblDetails AS tblcontryDetails READONLY
    AS
    BEGIN
    SET NOCOUNT ON; 
    Declare @sql nvarchar(max) 
    Declare @name varchar(255)
    DECLARE c_cursor CURSOR FOR     
    SELECT name    
    FROM @tblProductDetails  
     
      
    OPEN c_cursor      
    FETCH NEXT FROM c_cursor     
    INTO @name    
      
      
      
    WHILE @@FETCH_STATUS = 0    
    BEGIN    
        
    	-- here your procedure statemnets
    	-- insert statement use @name  variable from table
          
        FETCH NEXT FROM c_cursor     
       INTO @name    
       
    END     
    CLOSE c_cursor;    
    DEALLOCATE c_cursor;    
    
    
    
    
    
    SET NOCOUNT OFF;
    
    END
    
    
    

     
    Thursday, March 5, 2020 8:18 AM
  • Something like this should work for you (modify the catch if you only want to try again based on certain SqlExceptions).

    Depending on where you put the try/catch, you can make it wait for a good connection inside the foreach (in which case, it will execute the GetRowData() first, then try to process all the rows when it has a good connection) or you can make it wait outside the foreach, in which case, it will execute the GetRowData() method anytime you lose your connection.

    public static void FileValidation()
    {
        SqlConnection con = new SqlConnection(connection);
       
        while (true)
        {
            //---------------- Get FilePending
            DataTable DTRowData = new DataTable();
            DTRowData = GetRowData();
            //-----------------------------------------------
            foreach (DataRow Row in DTRowData.Rows)
            {
                Console.WriteLine("Start Working In File : " + Row[0].ToString());
     
                try
                {
                    con.Open();
                }
                catch (SqlException ex)
                {
                    // This is not the best way to handle this, it'll probably work ok
                    // but you could get stuck in a loop if the SQLServer is down for good
                    KeepTryingToOpen(con);
                }
                using (SqlCommand cmd = new SqlCommand("SP_FilesRunValidationOnFile2", con))
                {
                    
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@FileID", SqlDbType.VarChar).Value = Row[0];
                    cmd.ExecuteNonQuery();
     
                    Console.WriteLine("File Number : " + Convert.ToString(Row[0]) + "  Validated Successfully");
                    con.Close();
                }
            }
     
            System.Threading.Thread.Sleep(10000);
        }
    }
    public static KeepTryingToOpen(SqlConnection conn)
    {
        bool StillAProblem = true;
        while (StillAProblem)
        {
            try
            {
                System.Threading.Thread.Sleep(60000); // 60 seconds
                conn.Open();
                StillAProblem = false;
            }
            catch(SqlException ex)
            {
                // Log the error if you want to, then sleep and try again
                System.Threading.Thread.Sleep(60000); // 60 seconds
            }
        }
    }
    public static DataTable GetRowData()
    {
        DataTable dt =new DataTable();
        try
        {
            using (SqlConnection con = new SqlConnection(connection))
            using (SqlCommand cmd = new SqlCommand("sp_FilesGetPending", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                // In case you didn't know this little tidbit:
                // A .Fill() command opens and closes the connection automatically
                // If the connection is already open, it leaves it open
                // If the connection was closed, it will open it, Fill, then close it
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
        }
        catch (SqlException ex)
        {
            // You can ignore the exception or log it
        }
     
        // if there was an exception, then dt will be a new empty DataTable.
        return dt;
    }

      

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, March 8, 2020 2:03 AM
    Moderator
  • Hi,

    Has your problem been solved?

    If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 10, 2020 8:52 AM
  • Hello @engahmedbarbary … you've marked the wrong reply as an Answer! Please mark the reply that helped you solve your problem. 

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, March 10, 2020 3:34 PM
    Moderator