C# SQL server connection

Question

HI As a daily check I have to check the SQL connection to all our servers. At the moment this is done by manually logging on through SQL server managment studio. I was just wondering if this can be coded in C# so that I can run it first thing in a morning and its checks each server and the instance for a valid SQL connection and reports back to say if the connection is live or not.

Thanks Andy

Answer 1

Look at the SqlConnection Class. It includes a basic sample. Just put a loop around that sample to connect to each server, and if any server fails to connect it throws an exception.

Then just set it up as a scheduled task in Windows.

A nice way to report the status might be with an email, which can easily be sent out with SmtpClient.Send (the link has a nice simple sample.

Answer 2

Here's a little console app example that will cycle through a list of connections and attempt to connect to each, reporting success or failure. Ideally you'd perhaps want to extend this to read in a list of connection strings from a file, but this should hopefully get you started.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace SQLServerChecker
{
    class Program
    {
        static void Main(string[] args)
        {
            // Use a dictionary to associate a friendly name with each connection string.
            IDictionary<string, string> connectionStrings = new Dictionary<string, string>();

            connectionStrings.Add("Sales Database", "< connection string >");
            connectionStrings.Add("QA Database", "< connection string >");

            foreach (string databaseName in connectionStrings.Keys)
            {
                try
                {
                    string connectionString = connectionStrings[databaseName];

                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        Console.WriteLine("Connected to {0}", databaseName);
                    }
                }
                catch (Exception ex)
                {
                    // Handle the connection failure.
                    Console.WriteLine("FAILED to connect to {0} - {1}", databaseName, ex.Message);
                }
            }

            // Wait for a keypress to stop the console closing.
            Console.WriteLine("Press any key to finish.");
            Console.ReadKey();
        }
    }
}

Answer 3

You can also have a look at this method:

SqlDataSourceEnumerator.Instance.GetDataSources()

It gives you a list of SQL servers available on the network.

Answer 4

Why c#? You could make a simple batch file that does this using the osql command.

osql -S servername\dbname -E -Q "select 'itworks'"

Answer 5

try this code.

 private DataTable GetSQLInstances()
    {
      return SqlDataSourceEnumerator.Instance.GetDataSources();
    }



     private void Main()
        {


    DataTable dtSQLInstaces=GetSQLInstances();


                    for(int iter=0; iter< dtSQLInstaces.Rows.Count; iter ++)
                    {
                        try
                        {
                            string connectionString =String.Format("Data Source={0};Initial Catalog=AzerEnerjiF;User ID=userName; Password=password",dt.Rows[0][0]);

                            using (SqlConnection connection = new SqlConnection(connectionString))
                            {
                                connection.Open();
                                Console.WriteLine("Connected to {0}", dt.Rows[0][0]);
                            }
                        }
                        catch (Exception ex)
                        {

                            Console.WriteLine(String.Format("Failed to connect to  {0}", databaseName));
                        }
                    }

                    Console.ReadKey();

        }

Answer 6

You know, they make monitoring tools that let you know if your sql server goes down . . .