CODEWITHSUNDEEP
c#asp.net-core-mvcsqlcommand
shurda
shurda

Reputation: 65

SqlCommand.Parameters.Add not converting parameters when trying to do DELETE statement

I am doing an INSERT query to a db with the following code which works fine.

public int InsertPerson(Person person, out string errormsg)
{
    SqlConnection dbConnection = new SqlConnection();

    dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=PersonDB;Integrated Security=True";

    String sqlstring = "INSERT INTO PersonTable ( FirstName, LastName ) VALUES ( @firstname, @lastname )";
    SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);

    dbCommand.Parameters.Add("FirstName", SqlDbType.NVarChar, 30).Value = person.FirstName;
    dbCommand.Parameters.Add("LastName", SqlDbType.NVarChar, 30).Value = person.LastName;

    try
    {
        dbConnection.Open();
        
        int i = 0;
        i = dbCommand.ExecuteNonQuery();
        
        if (i == 1) 
        { 
            errormsg = ""; 
        }
        else
        {
            errormsg = "Could not add person";
        }

        return i;
    }
    catch (Exception e)
    {
        errormsg = e.Message;
        return 0;
    }
    finally
    {
        dbConnection.Close();
    }
}

However when I try to use the logic in a DELETE query it does not convert the @firstname and @lastname to the parameter values passed to the method call.

public int DeletePerson(Person person, out string errormsg)
{
    SqlConnection dbConnection = new SqlConnection();

    dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=PersonDB;Integrated Security=True";

    String sqlstring = "DELETE FROM PersonTable WHERE FirstName = @firstname AND LastName = @lastname";

    SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);

    dbCommand.Parameters.Add("FirstName", SqlDbType.NVarChar, 30).Value = person.FirstName;
    dbCommand.Parameters.Add("LastName", SqlDbType.NVarChar, 30).Value = person.LastName;

    try
    {
        dbConnection.Open();
        int i = 0;
        i = dbCommand.ExecuteNonQuery();

        if (i == 1)
        {
            errormsg = "";
        }
        else
        {
            //errormsg = "Could not delete person";
            errormsg = sqlstring;
        }

        return i;
    }
    catch (Exception e)
    {
        errormsg = e.Message;
        return 0;
    }
    finally
    {
        dbConnection.Close();
    }
}

The query that is created from the DELETE method looks like this DELETE FROM PersonTable WHERE FirstName = @firstname AND LastName = @lastname

It works fine when I hardcode the SQL query but not when I use the attributes of the Person parameter as parts of the query..

Upvotes: 0

Views: 267

Answers (1)

D Stanley
D Stanley

Reputation: 152626

You may misunderstand how parameters work. The value is not "replaced" in the SQL statement. The SQL statement is sent as-is with the parameter values provided separately. So you won't see a SQL string on the client side with the values replaced.

In other words, the value of sqlstring does not change. If that's what you're looking at then it won't tell you anything. Something else is wrong - either that name combination is not found in the DB, or there's some difference in casing, whitespace, etc.

Upvotes: 4

Related Questions