ColdFusion query to C# Winform

Question

I was given a query, originally done in ColdFusion, but I am having difficulties with the translation to a Winform use. I have a textbox that contains a concatenated string of other textboxes to make a case number. The purpose of this is to check for a record that might have been a transfer. In the first query, it is based on column caa443400048 either having something or being NULL. How would I incorporate that into a conditional statement for checking?

<cfquery name="q_transfer" datasource=#DSN#>
    SELECT caa443400048
    FROM   caa44340
    WHERE  caa44340041 = '#SearchCaseNo#'
</cfquery>

<CFSET TransferCaseNo = "">
<CFSET TransferFlag = 'N'>

<CFIF #q_transfer.caa443400048# NEQ "">
    <cfquery name="q_newcaseno" datasource=#DSN# >
        SELECT caa44340041
        FROM   caa44340
        WHERE  caa443400018 = '#q_transfer.caa443400048#'
    </cfquery>

    <CFSET TransferFlag = 'Y'>
    <CFSET TransferCaseNo = #SearchCaseNo#>
    <CFSET SearchCaseNo = #q_newcaseno.caa44340041#>
</cfif>

Here is the C# code I am currently using:

    string sql = "select COUNT (caa443400048) FROM caa44340 WHERE caa44340041 = ? ";
    OdbcConnection con = new OdbcConnection("Dsn=XXXXX; User ID=XXXXX; Password=XXXXX");
    con.Open();
    OdbcCommand cmd = new OdbcCommand(sql, con);

    cmd.Parameters.AddWithValue("caa44340041", txtCustomCaseNumber.Text);

    int count = Convert.ToInt32(cmd.ExecuteScalar());
    con.Close();

    if (count != 0)
    {
        MessageBox.Show("This is a transfer");
    }
    else
    {
        MessageBox.Show("This is not a transfer");
    }

Answer 1

I'm not 100%, but maybe this may help:

var TransferFlag = "N";
var SearchCaseNo = "";
var q_transfer = "";
var q_newcaseno = "";
using (var con = new OdbcConnection("Dsn=XXXXX; User ID=XXXXX; Password=XXXXX"))
{
    con.Open();
    using (var cmd = new OdbcCommand("SELECT caa443400048 FROM caa44340 WHERE caa44340041 = ?", con))
    {
        cmd.Parameters.AddWithValue("@var", txtCustomCaseNumber.Text);
        q_transfer = (string)cmd.ExecuteScalar();
    }
    if (!string.IsNullOrEmpty(q_transfer))
    {
        using (var cmd = new OdbcCommand("SELECT caa44340041 FROM caa44340 WHERE caa443400018 = ?", con))
        {
            cmd.Parameters.AddWithValue("@var", q_transfer);
            q_newcaseno = (string)cmd.ExecuteScalar();
        }
        TransferFlag = "Y";
        SearchCaseNo = q_newcaseno;
        MessageBox.Show("This is a transfer");
    }
    else
        MessageBox.Show("This is not a transfer");
}