SqlDataAdapter.update（）不更新数据库 [英] SqlDataAdapter.update() not updating database

问题描述

我在寻找（PostId，用户Id）到PostLikes表中使用的SqlDataAdapter ，如果该行被发现，我使用 SqlCommandBuilder.GetDeleteCommand（ ）生成删除指令，并删除基础行，如果没有找到行，用 SqlCommandBuilder.GetInsertCommand（）生成插入命令用插入该行的表 SqlDataAdapter.Update（）。但该行没有得到插入到表中database.Here是我迄今所做的

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 row is not found ,using 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");

和表
PostLikes（LikeId，PostId，用户ID）

and the table
PostLikes(LikeId,PostId,UserId)

推荐答案

有几个问题：

• 您正在寻找重复使用相同的命令，同时检测到该行是否存在，并提供给 SqlAdapter 为 SqlCommandBuilder 。
• 您应该参数化的初始选择查询，以防止SqlInjection攻击（并有一个小的性能优势）。该CommandBuilder的自动参数化插入 / 删除命令
• 创建插入后/删除与 SqlCommandBuilder ，然后需要更改基础数据集，以便任何变化过程中向表<$命令C $ C>更新。
• 请注意，许多SQL对象是的IDisposable ，应尽快处理 - 使用示波器帮助这里<。 / LI>

• 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
)

和我一直以为你要'切换'连续用了 postId，用户ID 组合的插入或删除。

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

这篇关于SqlDataAdapter.update（）不更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！