Returning SQL version using C#

Question

I'm struggling to return a SQL version using C#, I'm new to SQL programming so any help would be great. I'm getting various errors but below is my latest attempt.

private void buttonOK_Click(object sender, System.EventArgs e)
{
    string strSqlVersion = SQLVersion();
    MessageBox.Show(strSqlVersion);
}

private void sqlversion(string sqlver)
{
    OdbcConnection conn = null;
    try
    {
        conn = getConnection(comboBoxDatabase.Text);
        string strSql = "SELECT @@VERSION";
        conn.Open();
        OdbcCommand cmd = new OdbcCommand(strSql, conn);
        string returnvalue = (string)cmd.ExecuteScalar();
        return returnvalue;
    }
    catch (Exception ex){ }
    finally
    {
        conn.Close();
    }
}

Answer 1

Your method is declared void, change it to String:

private string sqlversion(string sqlver)
{
    OdbcConnection conn = null;
    try
    {
        conn = getConnection(comboBoxDatabase.Text);
        string strSql = "SELECT @@VERSION";
        conn.Open();
        OdbcCommand cmd = new OdbcCommand(strSql, conn);
        string returnvalue = (string)cmd.ExecuteScalar();
        return returnvalue;
    }
    catch (Exception ex){ }
    finally
    {
        conn.Close();
    }
}

Answer 2

What about:

private string SQLVersion()
{
    string version = "";
    try
    {
        using (OdbcConnection cn = new OdbcConnection("..."))
        using (OdbcCommand cm = cn.CreateCommand())
        {
            cn.Open();
            cm.CommandText = "SELECT @@Version";
            version = cm.ExecuteScalar() as string;
        }
    }
    catch (OdbcException) { }
    return version;
}

Answer 3

You can do something like:

var dbProviderFactory = DbProviderFactories.GetFactory(ConnectionStringSettings.ProviderName);
conn = dbProviderFactory .CreateConnection();
conn.ConnectionString = ...
conn.Open();
string serverVersion = conn.ServerVersion;

This is database-agnostic.

Answer 4

Try this:

private void buttonOK_Click(object sender, System.EventArgs e)
{
    string strSqlVersion = sqlversion();
    MessageBox.Show(strSqlVersion);
}

private string sqlversion()
{

    try
    {
        using (OdbcConnection conn = new OdbcConnection(comboBoxDatabase.Text))
        {
            string strSql = "SELECT @@VERSION";
            using (OdbcCommand cmd = new OdbcCommand(strSql, conn))
            {
                conn.Open();
                string returnvalue = Convert.ToString(cmd.ExecuteScalar());
                return returnvalue;
            }
        }
    }
    catch (OdbcException ex){ }
}

C# is a case sensitive language, so I've fixed the casing of your method call. Also, you cannot return a string from a void method. I've also declared your OdbcConnection as new and initialized it with what I presume is an appropriate connection string from a ComboBox.

EDIT: I've also added using blocks to ensure proper disposal of your OdbcCommand and OdbcConnection objects. Your method call to sqlversion also included a parameter, but none was specified, so I've removed the parameter.

EDIT 2: Modified the catch to only handle (and ignore) only an OdbcException.

Answer 5

Another way to do it, which is much shorter, is to use the SMO library like this:

var con = 
    new Microsoft.SqlServer.Management.Common.ServerConnection("MyServerName");
Debug.Print(con.ServerVersion.ToString());

On mine (SQL Server 2005) this prints "9.0".