why ExecuteScalar() doesn't return the expected value?

Question

I want to check whether a logic name exists in the Logic table or not. my query and procedure are working correctly but in the C# code, it doesn't work correctly. it supposes to ExecuteSchalar() return 0 or 1 and then I check it and show a message box or something else. here is my code

string LogicNameCheck_Query = "exec searchLogicName '{0}'";
        LogicNameCheck_Query = string.Format(LogicNameCheck_Query , txtLogicName);
        SqlCommand sc = new SqlCommand();
        sc.Connection = sqlConnection1;
        sc.CommandText = LogicNameCheck_Query;
        
        if (sqlConnection1.State != ConnectionState.Open)
        {
            sqlConnection1.Open();
        }
        int existLogicName = Convert.ToInt32(sc.ExecuteScalar());
        
        MessageBox.Show(existLogicName +"");
        if(existLogicName == 1)
        {
            MessageBox.Show("the name is exist");
            return;
        }
        if (sqlConnection1.State != ConnectionState.Closed)
        {
            sqlConnection1.Close();
        }

below is searchLogicName procedure code:

ALTER procedure [dbo].[searchLogicName]
@LogName varchar(50)
 as
IF EXISTS  (select * from Logic where LogicName = @LogName)
select 1 else select 0

What is _txtLogicName_? It seems to be a textbox. – Steve May 13 '17 at 13:45 — Steve, May 13 '17 at 13:45

score 0 · Accepted Answer · edited May 23 '17 at 11:47

txtLogicName is a TextBox. If you put it as is in as string format parameter you get back the name of class IE System.Windows.Forms.TextBox not the content of the textbox. The content comes from the Text property.

However you should change your logic to use a parameterized query to avoid Sql Injection and parsing problems (a single quote in txtLogicName will break your string.Format code)

SqlCommand sc = new SqlCommand();
sc.Connection = sqlConnection1;
sc.CommandText = "searchLogicName";
sc.CommandType = CommandType.StoredProcedure;
sc.Parameters.Add("@LogName", SqlDbType.VarChar, 50).Value = txtLogicName.Text;
if (sqlConnection1.State != ConnectionState.Open)
{
    sqlConnection1.Open();
}
int existLogicName = Convert.ToInt32(sc.ExecuteScalar());
...

Other points to consider:
You don't need to exec anything. The SqlCommand could use directly the name of the stored procedure provided that you inform it that your commandtext is the name of a storedprocedure. This is done using the CommandType property.

Second point to change as soon as possible. Avoid to keep a global connection object and then check everytime if it is open or not. Just use a local SqlConnection variable created inside a using statement. This will guarantee a proper closing and disposing of the resources used by the connection when you have done with it. There is no penalty in doing this because the SqlClient classes work with an infrastructure called Connection Pooling

why ExecuteScalar() doesn't return the expected value?

1 Answers1