SqlDataAdapter.update() not updating database

Question

I am searching for (PostId,UserId) into PostLikes table using SqlDataAdapter, if the row is found , I am using SqlCommandBuilder.GetDeleteCommand() to generate the delete instruction and deleting the underlying row, if the row is not found, then I use SqlCommandBuilder.GetInsertCommand() to generate the insert command and inserting the row to the table using SqlDataAdapter.Update(). But the row is not getting inserted to the table in database. Here is what I have done so far

SqlConnection con = new SqlConnection(connectionStrings);
SqlDataAdapter sqlDataAdapter=new SqlDataAdapter("select * from PostLikes where PostId like "
                                                 +postlike.PostId+" and UserId like "
                                                 +postlike.UserId,con);
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds, "Result");
con.Open();
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
if(ds.Tables["Result"].Rows.Count==1)
{
    sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand(true);
    msg = "Data is deleted";
}
else
{
    sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand(true);
    msg = "Data is inserted";
}
sqlDataAdapter.Update(ds, "Result");

and the table
PostLikes(LikeId,PostId,UserId)

Answer 1

There are a couple of issues:

• You are looking to reuse the same command to both detect whether the row exists, and to supply to the SqlAdapter for the SqlCommandBuilder.
• You should parameterise the initial select query to protect against SqlInjection attacks (and there is a minor performance benefit). The CommandBuilder will automatically parameterize the Insert / Delete commands
• After creating the Insert / Delete commands with the SqlCommandBuilder, you then need to change the underlying dataset in order for any changes to be made to the table during the Update.
• Note that many of the Sql objects are IDisposable and should be disposed ASAP - using scopes help here.

.

var postId = 1;
var userId = 1;
string msg;
using (var con = new SqlConnection(@"data source=..."))
using (var selectCommand = new SqlCommand(
 "select LikeId, PostId, UserId from PostLikes WHERE PostId=@PostId AND UserId=@UserId", con))
using (var sqlDataAdapter = new SqlDataAdapter(selectCommand))
using (var ds = new DataSet())
{
    con.Open();
    selectCommand.Parameters.AddWithValue("@PostId", postId);
    selectCommand.Parameters.AddWithValue("@UserId", userId);
    sqlDataAdapter.Fill(ds, "Result");
    using (var sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter))
    {
        if (ds.Tables["Result"].Rows.Count == 1)
        {
            sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand(true);
            ds.Tables["Result"].Rows[0].Delete();
            msg = "Data will be deleted";
        }
        else
        {
            sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand(true);
            // Null because LikeId is Identity and will be auto inserted
            ds.Tables["Result"].Rows.Add(null, postId, userId);
            msg = "Data will be inserted";
        }
        sqlDataAdapter.Update(ds, "Result");
    }
}

I've assumed the following Schema:

CREATE TABLE PostLikes
(
    LikeId INT IDENTITY(1,1) PRIMARY KEY,
    PostId INT,
    UserId INT
)

And I've assumed you want to 'toggle' the insertion or deletion of a row with the postId, userid combination.