数据库只更新第一行不同的值c#

本文关键字：一行 更新 数据库 | 更新日期: 2023-09-27 18:14:46

        string connetionString = null;
        SqlConnection connection;
        SqlCommand command;
        SqlDataAdapter adpter = new SqlDataAdapter();
        DataSet ds = new DataSet();
        XmlReader xmlFile;
        string sql = null;
        int ID = 0;
        string Name = null, Text = null, Screenname = null;

        connetionString = "myconnection";
        connection = new SqlConnection(connetionString);
        xmlFile = XmlReader.Create("my.XML", new XmlReaderSettings());
        ds.ReadXml(xmlFile);
        int i = 0;
        connection.Open();
        for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
        {
            ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
            Text = ds.Tables[0].Rows[i].ItemArray[1].ToString().Replace("'", "''");
            Name = ds.Tables[0].Rows[i].ItemArray[2].ToString().Replace("'", "''");
            Screenname = ds.Tables[0].Rows[i].ItemArray[3].ToString().Replace("'", "''");
            //sql = "insert into nicktest values(" + ID + ",'" + Text + "'," + Name + "," + Screenname + "," + DateTime.Now.ToString() + ")";
            sql = "If Exists(Select * from niktest2 Where Id  = ID) " +
                                        " BEGIN " +
                                        " update niktest2 set Name  = '" + Text + "' , Screenname = '" + Name + "', Profimg= '" + Screenname + "', InsertDateTime= '" + DateTime.Now.ToString() + "' where Id=ID" +
                                        " END " +
                                        " ELSE " +
                                        " BEGIN " +
                                        " insert into niktest2(Id,Name,Screenname,Profimg,InsertDateTime) values('" + ID + "','" + Text + "','" + Name + "','" + Screenname + "' ,'" + DateTime.Now.ToString() + "')" +
                                        " END ";
            command = new SqlCommand(sql, connection);
            adpter.InsertCommand = command;
            adpter.InsertCommand.ExecuteNonQuery();
        }
    }

运行这段代码后，只有第一行得到更新，即使我的XML文件有更多的数据。我想把所有的数据插入数据库与分配id到它在xml文件。请帮. .

数据库只更新第一行不同的值c#

一旦插入了一行，这个条件将为真:

If Exists(Select * from niktest2 Where Id  = ID)

因此您将执行更新，而不是插入，因此您将只在数据库中获得一行。

由于您使用的是SQL Server 2008，我会采用完全不同的方法，使用参数化查询，合并和表值参数。

第一步是创建您的表值参数(我不得不猜测您的类型: )
CREATE TYPE dbo.nicktestTableType AS TABLE
(   
    Id INT,
    Name VARCHAR(255),
    Screenname VARCHAR(255),
    Profimg VARCHAR(255)
);

然后你可以写MERGE语句upsert到数据库:

MERGE nicktest WITH (HOLDLOCK) AS t
USING @NickTestType AS s
    ON s.ID = t.ID
WHEN MATCHED THEN 
    UPDATE
    SET Name = s.Name,
        Screenname = s.Screenname,
        Profimg = s.Profimg,
        InsertDateTime = GETDATE()
WHEN NOT MATCHED THEN 
    INSERT (Id, Name, Screenname, Profimg, InsertDateTime)
    VALUES (s.Id, s.Name, s.Screenname, s.Profimg, GETDATE());

然后你可以把你的数据表作为参数传递给查询:

using (var command = new SqlCommand(sql, connection))
{
    var parameter = new SqlParameter("@NickTestType", SqlDbType.Structured);
    parameter.Value = ds.Tables[0];
    parameter.TypeName = "dbo.nicktestTableType";
    command.Parameters.Add(parameter);
    command.ExecuteNonQuery();
}

如果你不想做这么大的改变，那么你至少应该使用参数化查询，所以你的SQL将是:

IF EXISTS (SELECT 1 FROM nicktest WHERE ID = @ID)
BEGIN
    UPDATE  nicktest
    SET     Name = @Name,
            ScreenName = @ScreeName,
            InsertDateTime = GETDATE()
    WHERE   ID = @ID;
END
ELSE
BEGIN
    INSERT (Id, Name, Screenname, Profimg, InsertDateTime)
    VALUES (@ID, @Name, @Screenname, @Profimg, GETDATE());
END

或者最好仍然使用MERGE作为HOLDLOCK表提示，以防止(或至少大量减少)竞争条件:

MERGE nicktest WITH (HOLDLOCK) AS t
USING (VALUES (@ID, @Name, @ScreenName, @ProfImg)) AS s (ID, Name, ScreenName, ProfImg)
    ON s.ID = t.ID
WHEN MATCHED THEN 
    UPDATE
    SET Name = s.Name,
        Screenname = s.Screenname,
        Profimg = s.Profimg,
        InsertDateTime = GETDATE()
WHEN NOT MATCHED THEN 
    INSERT (Id, Name, Screenname, Profimg, InsertDateTime)
    VALUES (s.Id, s.Name, s.Screenname, s.Profimg, GETDATE());

虽然使用表值参数，但这将比第一种解决方案效率低得多那么你的c#应该是这样的:

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
    using (var command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@ID", ds.Tables[0].Rows[i][0]);
        command.Parameters.AddWithValue("@Name", ds.Tables[0].Rows[i][1]);
        command.Parameters.AddWithValue("@ScreeName", ds.Tables[0].Rows[i][2]);
        command.Parameters.AddWithValue("@ProfImg", ds.Tables[0].Rows[i][3]);
        command.ExecuteNonQuery();
    }
}
相关文章：