插入数据表或更新数据表(如果存在

Question

我正在尝试更新一个数据表并将其插入到另一个数据表中。如果数据表中的工单( c2条码)相同，则更新数据表( c1条码)，如果不同，则从数据表c1条码中插入c2条码到数据表中。我能够运行代码，对于插入部分，它正确地插入到数据库中，但对于更新部分，它不起作用。我的更新或条件中有错误吗？

connection.Open();
OleDbCommand checkrecord = new OleDbCommand("SELECT workorder FROM [c1 barcode]", connection);
OleDbCommand checkrecord2 = new OleDbCommand("SELECT workorder FROM [c2 barcode]", connection);
if (checkrecord == checkrecord2)
{
    string query = "UPDATE [c1 barcode], [c2 barcode] SET [c1 barcode].[Close from care] = [c2 barcode].[close from care], [c1 barcode].[Name care] = [c2 barcode].[name care] WHERE ([c1 barcode].Workorder=[c2 barcode].[workorder]);";
    using (OleDbCommand cmd = new OleDbCommand(query, connection))
    {
        cmd.ExecuteNonQuery();
        MessageBox.Show("updated");
    }
}
else if (checkrecord != checkrecord2)
{
    string query2 = "INSERT INTO [c1 barcode] SELECT [c2 barcode].* FROM [c2 barcode]";
    using (OleDbCommand cmd = new OleDbCommand(query2, connection))
    {
        cmd.ExecuteNonQuery();
        MessageBox.Show("inserted");
    }
}
else
{
    MessageBox.Show("error");
}
connection.Close();

Answer 1

您不需要先运行检查。如果您只需运行两个SQL语句，它就会工作，如下所示：

connection.Open();
string updateQuery = "UPDATE [c1 barcode], [c2 barcode] SET [c1 barcode].[Close from care] = [c2 barcode].[close from care], [c1 barcode].[Name care] = [c2 barcode].[name care] WHERE ([c1 barcode].Workorder=[c2 barcode].[workorder]);";
string insertQuery = "INSERT INTO [c1 barcode] SELECT * FROM [c2 barcode] c2 WHERE NOT EXISTS (SELECT 1 FROM [c1 barcode] WHERE Workorder = c2.Workorder)";
using (OleDbCommand cmd = new OleDbCommand(updateQuery, connection))
{
    if ((int)cmd.ExecuteNonQuery() > 0)
    {
        MessageBox.Show("updated");
    }
}
using (OleDbCommand cmd = new OleDbCommand(insertQuery, connection))
{
    if ((int)cmd.ExecuteNonQuery() > 0)
    {
        MessageBox.Show("inserted");
    }
}
connection.Close();

这里假设Workorder是一个主键，由于您的insert语句，我认为它是正确的。

Answer 2

checkrecord == checkrecord2是错误的，您只是在比较两个OleDbCommand对象，我猜您让两个对象执行这两个命令，然后比较这些命令的输出

如下所示：

//psuedo code
connection.Open();
OleDbCommand cmd_checkrecord = new OleDbCommand("SELECT workorder FROM [c1 barcode]", connection);
OleDbCommand cmd_checkrecord2 = new OleDbCommand("SELECT workorder FROM [c2 barcode]", connection);
var checkrecord = Convert.ToString(cmd_checkrecord.ExecuteScalar());
var checkrecord2 = Convert.ToString(cmd_checkrecord2.ExecuteScalar());

if (checkrecord == checkrecord2)
{
    string query = "UPDATE [c1 barcode], [c2 barcode] SET [c1 barcode].[Close from care] = [c2 barcode].[close from care], [c1 barcode].[Name care] = [c2 barcode].[name care] WHERE ([c1 barcode].Workorder=[c2 barcode].[workorder]);";
    using (OleDbCommand cmd = new OleDbCommand(query, connection))
    {
        cmd.ExecuteNonQuery();
        MessageBox.Show("updated");
    }
}
else if (checkrecord != checkrecord2)
{
    string query2 = "INSERT INTO [c1 barcode] SELECT [c2 barcode].* FROM [c2 barcode]";
    using (OleDbCommand cmd = new OleDbCommand(query2, connection))
    {

        cmd.ExecuteNonQuery();

        MessageBox.Show("inserted");
    }
}
else
{
    MessageBox.Show("error");
}
connection.Close();