Generic way to test connection string of various rdbms

Question

I am having different data source like mysql, SQL Server and Oracle and for each of the data source i will test the whether handshake with the server is successful or not based on the connection string.

So I have created 3 class and each of this class will have a method to test connection string but the code is same so I was thinking that whether it is possible to to create 1 generic method that will handle all the 3 data source like mysql, SQL Server and Oracle so that I don't have to create 3 method for each of the data source to test connection string.

Below is my code :

public class ConnectionViewModel
    {
        public string RdbmsType { get; set; }
        public string  ConnectionString { get; set; }
    }

 [HttpPost]
        public ActionResult RdbmsServerHandshake(ConnectionViewModel model)
        {
             if (model.RdbmsType =="Mysql")
                {
                     var mySqlRepo = new MysqlRepository();
                     var test = mySqlRepo.TestConnectionString(model.ConnectionString);
                }
                else if(model.RdbmsType == "SqlServer")
                {
                     var sqlServerRepo = new SqlServerRepository();
                     var test = sqlServerRepo.TestConnectionString(model.ConnectionString);
                }
                else // for oracle
                {
                   // code for oracle
                }
        }

    public class SqlServerRepository
    {
        public bool TestConnectionString(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
            }
        }
    }

    public class MysqlRepository
    {
        public bool TestConnectionString(string connectionString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
            }
        }
    }

So any better way to do this above process??

Alessandro D'Andria · Accepted Answer · 2017-05-09 08:14:12Z

2

You can define a generic method like this:

static bool TestConnectionString<T>(string connectionString) where T : DbConnection, new()
{
    using (DbConnection connection = new T())
    {
        connection.ConnectionString = connectionString;

        try
        {
            connection.Open();
            return true;
        }
        catch (SqlException)
        {
            return false;
        }
    }
}

answered May 9, 2017 at 8:14

Alessandro D'Andria

8,8882 gold badges39 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ranch Camal · Accepted Answer · 2018-04-16 16:35:10Z

0

You should close the connection. Otherwise you will be keep on opening connections and leaving them open. Similar to Alessandro's answer but with close included in the code:

static bool TestConnectionString<T>(string connectionString) where T : DbConnection, new()
{
    using (DbConnection connection = new T())
    {
        connection.ConnectionString = connectionString;

        try
        {
            connection.Open();
            connection.Close();
            return true;
        }
        catch (SqlException)
        {
            return false;
        }
    }
}

answered Apr 16, 2018 at 16:35

Ranch Camal

6001 gold badge5 silver badges12 bronze badges

Collectives™ on Stack Overflow

Generic way to test connection string of various rdbms

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related