private bool OpenDbConnection(SqlConnection sql_con_obj)
    {
        try
        {
            SqlConnection.ClearPool(sql_con_obj);
            SqlConnection.ClearAllPools();
            if (sql_con_obj.State == ConnectionState.Closed ||          sql_con_obj.State == ConnectionState.Broken)
            {
                sql_con_obj.ConnectionString = "Data Source=.;Initial Catalog=Practice;User ID=sa;Password=sa";
                sql_con_obj.Open();
                return true;
            }
            else
            {
                return false;
            }

        }
        catch (Exception ex)
        {
            ShowMessage(ex.Message);
        }
    }

I get following error on execution:

Error   1   '_Default.OpenDbConnection(System.Data.SqlClient.SqlConnection)': not all code paths return a value.

please suggest solution

Hi Sachin_coder, welcome at DANIWEB:)
Your catch clause doesn't return anything, that's what caused your error. Either return someting or throw an exception.
Please use code tags, the next time you post code! Read the rules.

The preferred way to accomplish this is to save your value in a place where you can return it at the end of your method. This is not strictly necessary, but it is good practice. The reasoning behind this is that you will likely have to re-arrange your code to this form if you plan on adding additional logic, and error handling based on your return value.
For example:

private bool OpenDbConnection(SqlConnection sql_con_obj)
{
bool val = false;
try
{
SqlConnection.ClearPool(sql_con_obj);
SqlConnection.ClearAllPools();
if (sql_con_obj.State == ConnectionState.Closed || sql_con_obj.State == ConnectionState.Broken)
{
sql_con_obj.ConnectionString = "Data Source=.;Initial Catalog=Practice;User ID=sa;Password=sa";
sql_con_obj.Open();
val = true;
}

}
catch (Exception ex)
{
// write to log, something like "OpenDbConnection() = " + val.ToString()
ShowMessage(ex.Message);
}

return val;
}

Also, please don't clear all the connections from the connection pool. Your application may optimize its own connections via some custom pooling algorithm, but some of the applications out there depend on the connection pool for performance. In fact, you won't believe how many applications assume their connections will be back in the pool, and reconnect if they aren't. You may be hosing your SQL server like that.

commented: I was about to say the same thing regarding connection pooling... +5

In your particular code example, you could eliminate the else clause and simply move the return false; to just before the end of your method block:

private bool OpenDbConnection(SqlConnection sql_con_obj)
        {
            try
            {
                //SqlConnection.ClearPool(sql_con_obj);
                //SqlConnection.ClearAllPools();
                if (sql_con_obj.State == ConnectionState.Closed || sql_con_obj.State == ConnectionState.Broken)
                {
                    sql_con_obj.ConnectionString = "Data Source=.;Initial Catalog=Practice;User ID=sa;Password=sa";
                    sql_con_obj.Open();
                    return true;
                }
            }
            catch (Exception ex)
            {
                ShowMessage(ex.Message);
            }
            
            return false;
        }

In structuring the method's return value this way, and should you decide to add more catch(exception) clauses, etc., you will always have a return false statement at the end of your method indication failure and not have to place additional return statements for every exception type handled, which litters up the code a little.

Thank you all , This solved my problem....:)

Fine to hear that, so mark this thread as solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.