CODEWITHSUNDEEP
c#sql-serverado.net
Aelgawad
Aelgawad

Reputation: 194

Testing Sql Connection Validity

I am trying to write a method that will check if a database connection is valid or not. Here is the code behind my Test Connection button.

    private void TestConn_btn_Click(object sender, EventArgs e)
    {
        DbConnection DBConnTest;

        if (DataSrc_cbx.SelectedIndex == 1)
        {
            DBConnTest = new SqlConnection("Server="+DatabaseAddress_Value+"; Database="+DatabaseName_Value+";Trusted_Connection=true");
            try
            {
                DBConnTest.Open();
                MessageBox.Show("\nTEST SUCCESSFUL\n");
            }
            catch (Exception exception)
            {
                MessageBox.Show("TEST FAILED Exception Thrown: " + exception.Message);
            }
            finally
            {
                DBConnTest.Close();
            }
        }
      }

The problem is that there is no exception thrown when I enter an invalid Database address ( or leave it empty all together), same applies to the Database name. It only throws an exception when there is no connection string, or in an incorrect format. So my question is, How do I make it check if there is indeed a server and a database on that server with the names input?

Upvotes: 1

Views: 1414

Answers (5)

wruckie
wruckie

Reputation: 1806

Your code is working for me. The issue here is that you have to wait till the SQL timeout period elapses before the exception is thrown. This will not be a method that returns an immediate answer. If you wrap this try/catch with a WaitCursor, you will at least see when the code is running.

private void TestConn_btn_Click(object sender, EventArgs e)
{
    this.Cursor = Cursors.WaitCursor;
    DbConnection DBConnTest;

    if (DataSrc_cbx.SelectedIndex == 1)
    {
        DBConnTest = new SqlConnection("Server="+DatabaseAddress_Value+"; Database="+DatabaseName_Value+";Trusted_Connection=true");
        try
        {
            DBConnTest.Open();
            MessageBox.Show("\nTEST SUCCESSFUL\n");
        }
        catch (Exception exception)
        {
            MessageBox.Show("TEST FAILED Exception Thrown: " + exception.Message);
        }
        finally
        {
            DBConnTest.Close();
        }
    }
    this.Cursor = Cursors.Default;
  }

Upvotes: 1

to StackOverflow
to StackOverflow

Reputation: 124696

You need to execute a query to connect to the database.

For SQL Server, I usually use IDbCommand.ExecuteScalar to execute:

SELECT @@VERSION

For Oracle:

SELECT banner from v$version where banner like 'Oracle%'

Would you provide the complete code, please?

It would be something like:

try
{
    using(SqlConnection conn = ...)
    {
        conn.Open();
        using(SqlCommand command = conn.CreateCommand())
        {
            command.CommandText = "SELECT @@VERSION";
            var result = (string) command.ExecuteScalar();
            MessageBox.Show("\nTEST SUCCESSFUL\n" + result); 
        }
    }
}
catch(Exception ex)
{
    MessageBox.Show("TEST FAILED Exception Thrown: " + exception.Message);
}

Upvotes: 1

Marcio
Marcio

Reputation: 1733

Your code looks incomplete!

Take this example from Microsoft.conexão c# com sql server 2008

Good luck!

Upvotes: 0

ProgrammingLlama
ProgrammingLlama

Reputation: 38727

Perhaps try:

        using (SqlConnection conn = new SqlConnection(builder.ToString()))
        {
            try
            {
                conn.Open();
            }
            catch (SqlException ex)
            {
                foreach (SqlError error in ex.Errors)
                {
                    Console.WriteLine(error.Number);
                }
            }
            catch (Exception ex)
            {

            }
        }

It will return the DB error code (run the following query for a list of error codes:

select * from sys.messages where language_id=1033 and severity between 11 and 16

Microsoft also provide some guidance here:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlerror.number(v=vs.110).aspx

Upvotes: 0

Aftab Ahmed
Aftab Ahmed

Reputation: 1737

You can apply validations on your Web Page if the fields are empty then prompt user to enter something. Now use this statement to check whether this database exist or not??

select name from sys.sysdatabases 
where dbid=db_id()

for user you need to .. SELECT id FROM user WHERE login="JohnSmith" and see if it gives you any rows. If yes - user exists.

You can use this work-around.

Upvotes: 2

Related Questions