why my code doesn't work properly?

Question

i want to check is exist a logic name in Logic table or not. my query and procedure working properly but in C# code it doesn't work correctly.it suppose to ExecuteSchalar() return 0 or 1 and then i check it and show a messagebox or proceed other codes.my codes are below

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("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 my code doesn't work properly?

1 Answers1