Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Determine version of SQL Server from ADO.NET

I need to determine the version of SQL Server (2000, 2005 or 2008 in this particular case) that a connection string connects a C# console application (.NET 2.0). Can anyone provide any guidance on this?

Thanks, MagicAndi

Update

I would like to be able to determine the SQL Server version form the ADO.NET connection object if possible.

like image 560
Tangiest Avatar asked Jun 04 '09 10:06

Tangiest


People also ask

How do I determine SQL Server version?

Finding the SQL Server version with query We can use the @@VERSION function to find out all version details of the SQL Server instance. The @@VERSION function returns a one-line string output and this output also provides all the necessary information about the SQL Server.

What is latest SQL Server version?

The current version is Microsoft SQL Server 2019, released November 4, 2019. The RTM version is 15.0. 2000.5.


2 Answers

This code will determine the version of SQL Server database being used - 2000, 2005 or 2008:

try
{
    SqlConnection sqlConnection = new SqlConnection(connectionString);
    Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection));

    switch (server.Information.Version.Major)
    {
      case 8:
        MessageBox.Show("SQL Server 2000");
        break;
      case 9:
        MessageBox.Show("SQL Server 2005");
        break;
      case 10:
        MessageBox.Show("SQL Server 2008");
                break;
      default:
        MessageBox.Show(string.Format("SQL Server {0}", server.Information.Version.Major.ToString())); 
        break;   
    }
}
catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException)
{
    MessageBox.Show("Unable to connect to server",
        "Invalid Server", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

The code below will do the same, this time using NinthSense's answer:

try
{       
    SqlConnection sqlConnection = new SqlConnection(connectionString);
    sqlConnection.Open();

    string serverVersion = sqlConnection.ServerVersion;
    string[] serverVersionDetails = serverVersion.Split( new string[] {"."}, StringSplitOptions.None);

    int versionNumber = int.Parse(serverVersionDetails[0]);

    switch (versionNumber)
    {
        case 8:
            MessageBox.Show("SQL Server 2000");
            break;
        case 9:
            MessageBox.Show("SQL Server 2005");
            break;
        case 10:
            MessageBox.Show("SQL Server 2008");
            break;
        default:
            MessageBox.Show(string.Format("SQL Server {0}", versionNumber.ToString()));  
            break;  
    }
}
catch (Exception ex)
{
    MessageBox.Show(string.Format("Unable to connect to server due to exception: {1}", ex.Message),
        "Invalid Connection!", MessageBoxButtons.OK, MessageBoxIcon.Error);

}
finally
{
    sqlConnection.Close();
}
like image 118
Tangiest Avatar answered Sep 23 '22 09:09

Tangiest


SqlConnection con = new SqlConnection("Server=localhost;Database=test;user=admin;password=123456;");
con.Open();
Text = con.ServerVersion;
con.Close();

con.ServerVersion will give you:

  • 9.x.x for SQL Server 2005
  • 10.x.x for SQL Server 2008
like image 38
NinethSense Avatar answered Sep 19 '22 09:09

NinethSense