将表格更新为Excel

本文关键字：Excel 更新 表格 | 更新日期: 2023-09-27 18:07:00

我试图"更新"的数据是放在一个dataGridView上的单元格。我从Excel文件导入数据到dataGridView。使用这个方法;

public static void updateTable1(string myNameRange, string mySET, string myWHERE)
{
    string sql = null;
    sql = "UPDATE " + myNameRange + " SET " + mySET + "' WHERE " + myWHERE + "'";
    //sql = "INSERT INTO" + myNameRange + " SET " + mySET + "'";
    myCommand.CommandText = sql;
    myCommand.ExecuteNonQuery(); 
}

如果我们有列头NAME和AGE，如果我输入一个名为Jack和Mike的表，年龄分别为10岁和20岁，如果我将Jack的年龄从10岁更改为30岁，则此操作有效。代码说。其中Column NAME = Jack，则将Column AGE设置为30。这也很有效。问题是如果我想更改NAME和AGE该怎么办?所以把杰克改成马特，把迈克的年龄改成40岁。或添加另一个名字和年龄到我的表。这种方法行不通。是否有一种简单的方法来更新整个表的任何更改?

将表格更新为Excel

假设您有3个文本框，用户在其中填写NAME和AGE(以及旧名):

创建一个检查方法:

  1. 文本框的填充情况
  2. 如果最后文本框中输入的名称存在于DB

假设Check是这个方法:

public static void updateTable1(string myNameRange, string mySET, string myWHERE)
    {
        if (Check()==0)/// Suppose 0 means all textboxes filled and name2 exists in DB
       {      
        string sql = "UPDATE myTable SET  NAME=@name , AGE=@age  WHERE Name=@name2";
        myCommand.CommandText = sql;
        myCommand.Parameters.AddWithValue("@name", textBox1.text);
        myCommand.Parameters.AddWithValue("@age", textBox2.text);
        myCommand.Parameters.AddWithValue("@name2", textBox3.text);
        myCommand.ExecuteNonQuery();
        }
        else if  (Check()==1)/// Suppose 1 means only name filled and name2 exists in DB
        {
          string sql = "UPDATE myTable SET  NAME=@name  WHERE Name=@name2";      
        myCommand.CommandText = sql;
        myCommand.Parameters.AddWithValue("@name", textBox1.text);       
        myCommand.Parameters.AddWithValue("@name2", textBox3.text);
        myCommand.ExecuteNonQuery();
        }
        ....
        else /// (name2 not in DB)
        {
         sql = "INSERT INTO" + myNameRange + " SET " + mySET + "'";
          ///.......
        }
    }

        Create stored procedure like the sample one below, here you only pass all the values that were changed, then the stored procedure will take care of the update. 
        public static void updateTable1(string? NameValue, int? AgeValue, int uniqueValue)
        {
            try 
            {           
                SqlCommand myCommand = new SqlCommand();
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = "spUpdate_ProcName";
                myCommand.Parameters.AddWithValue("@Name", NameValue);
                myCommand.Parameters.AddWithValue("@Age", AgeValue);
                myCommand.Parameters.AddWithValue("@UniqueColumn", uniqueValue);
                myCommand.ExecuteNonQuery();
            }
            catch (Exception)
            {       
                throw;
            }

        }
        //To be done in SQL
        CREATE PROCEDURE upProcName
        (
            @Name varchar(100) = NULL,
            @Age int = NULL,
            @UniqueColumn int -- unique column or primary key
        )
        AS
        BEGIN
            SET NOCOUNT ON;
            UPDATE yourTableName
            SET Name = COALESCE(@Name, Name),
                Age = COALESCE(@Age, Age)
            WHERE UniqueColumn = @UniqueColumn
        END
相关文章：