How to check successful connection to a SQL Server database in C#?

Question

I am trying to connect to the SQL Server database in C#, and check that the database contains an empty table with 3 columns, but I don't know how to check if it is successful or not..

My code:

protected bool checkDB()
{ 
    string ConnectionString = "Server=[serverName];Database=[databaseName];Trusted_Connection=true";

    SqlConnection con = new SqlConnection(ConnectionString);

    SqlCommand com = new SqlCommand("SELECT * FROM tableName", con);

    // use the connection here
    con.Open();

    con.Close();

    if (success)
    {
        return true;
    }
    else
    {
        return false;
    }
}

If you don't get exception on "con.Open();", then your connection is success... — , Jul 28 '16 at 03:34
one more question, i have a "\" in the server name, can i use "\\" to replace it? — king yau, Jul 28 '16 at 03:38
You can see lots of sample here : https://www.connectionstrings.com/sql-server/ — , Jul 28 '16 at 03:40
you can read here about exception handling http://stackoverflow.com/documentation/c%23/40/exception-handling#t=201607280338470828471 — Diligent Key Presser, Jul 28 '16 at 03:40

score 3 · Accepted Answer · 2016-07-28T04:16:09.193

3

protected bool checkDB()
{
  var connString = @"Server=myServerName\myInstanceName;Database=myDataBase;Integrated Security=true;";
  try
  {
    using (var con = new SqlConnection(connString))
    {
      con.Open();
      using (var com = new SqlCommand("SELECT * FROM tableName", con))
      {
        // use your query here...
      }
    }
    return true;
  }
  catch (Exception)
  {
    return false;
  }
}

edited Jul 28 '16 at 04:16

answered Jul 28 '16 at 03:46

hey, i am using windows authentication to connect sql server, so i don't know the username and password.. what should i do? – king yau Jul 28 '16 at 04:04
in fact i got an error about --- 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 Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) – king yau Jul 28 '16 at 04:05
For windows auth use : Integrated Security=true;, without username and password – Jul 28 '16 at 04:06
Test your connection using SQL Server Management Studio, sometimes you need to define port number 1433 for that, otherwise sql server port will be dynamic. – Jul 28 '16 at 04:07
https://msdn.microsoft.com/en-us/library/ms177440.aspx add 1433 to TCP Port – Jul 28 '16 at 04:09
now i am using Integrated Security=true; However, i got an error about `Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.` does it mean that i cannot login to the database? – king yau Jul 28 '16 at 04:12
Test that user "NT AUTHORITY\ANONYMOUS LOGON" using sql management studio. If not working, than that user has a problem. – Jul 28 '16 at 04:14
http://stackoverflow.com/questions/10957443/web-app-getting-login-failed-for-user-nt-authority-anonymous-logon – Jul 28 '16 at 04:15
I can login with windows authentication by entering noting about user name and password (disable to enter text, but the text box of userName contains my account, the text box of password is empty) – king yau Jul 28 '16 at 04:21
Check that link before, sorry, never had something like that. Google it... lots of answer there. – Jul 28 '16 at 04:23
The account you are running the application under is not your user account and does not have access to the database. Either open management studio and create a user that maps to the NT AUTHORITY\ANONYMOUS LOGIN Windows account (bad idea), change whatever context is running your application to use an account that also has access to the database (like yours), or just create a database user for your application and use that in the connection string instead of Windows auth. – S.C. Jul 28 '16 at 10:56

How to check successful connection to a SQL Server database in C#?

1 Answers1