从 csv 文件中检索值并在 C# 中的数据库中替换它 [英] Retrieve value from csv file and replace it in database in C#

问题描述

我正在创建一个应用程序，它将 csv 文件读入 C# 中的数据网格视图.该文件由项目编号和库存等数据组成.

I am creating an application which reads a csv file into a data grid view in C#. The file consists of data such as item number and stock.

如果项目编号相同，则应添加其库存，然后用相同的项目编号替换数据库中的现有库存.

If item number is same then its stock should be added and then replace the present stock in the database with same item number.

我可以将 csv 文件中的库存添加到数据库中，并且在数据库中具有相同的项目编号，但我无法替换库存.

I am able to add stock from the csv file to the database with same item numbers in the database but I am not able to replace stock.

这是我的代码:

    string sql_select = "select count(*) from PRODUCTS where item_no= '" + 
                            itemNo + "'";
    SqlCommand cmdCheckPmk = new SqlCommand(sql_select, Class1.conn);

    int selectItemNo = Convert.ToInt32(cmdCheckPmk.ExecuteScalar());

    if (selectItemNo != 0)
    {
        string sql_update = "update PRODUCTS set item_stock=+'" + Stock + 
                            "' where item_no= '" + itemNo + "'";
        SqlCommand cmd1 = new SqlCommand(sql_update, Class1.conn);
    }
    else
    {
        SqlCommand cmd11 = new SqlCommand("insert into PRODUCTS(item_no,item_name,price,cost,item_stock,dept_id,tax_rate1,tax_rate2,bulk_price,bulk_qty) values ('" + itemNo + "','" + itemName + "'," + price + "," + cost + "," + Stock + ",'" + dept + "','" + tax1 + "','" + tax2 + "'," + BulkPrize +"," + BulkQty +") ", Class1.conn);
        cmd11.ExecuteNonQuery();
    }

我使用的是 SQL Server 2008 R2.

I am using SQL server 2008 R2.

推荐答案

你上面的例子有很多小问题.

Your example above had lots of little problems.

研究下面的基本示例作为更好的方法.

Study the basic example below as a better approach.

// Pass in your SQL Connection object so you do not have to worry about
// multiple open connections
public int jp2Test(SqlConnection conn) {
  // Verify someone did not pass in a NULL object
  if (conn != null) {
    string sql_text = "select count(*) from PRODUCTS where item_no=@item_no";
    using (SqlCommand cmd = new SqlCommand(sql_text, conn)) {
      // Get in the habit of using Parameters. If you know the SqlDbType, use one of
      // the Parameter overloads that provides for this.
      cmd.Parameters.AddWithValue("@item_no", itemNo);
      // Open the connection if it is not already
      if ((cmd.Connection.State & ConnectionState.Open) != ConnectionState.Open) {
        cmd.Connection.Open();
      }
      // initialize an item_number variable
      int selectedItemNo = -1;
      object value = cmd.ExecuteScalar();
      // Check for both NULL and DBNull
      if ((value != null) && (value != DBNull.Value)) {
        selectedItemNo = Convert.ToInt32(value);
      }
      // Update your SQL Text based on the value you received.
      if (0 < selectedItemNo) {
        sql_text = "update PRODUCTS set item_stock=@item_stock where item_no=@item_no";
        // this value is already included
        // cmd.Parameters.AddWithValue("@item_no", itemNo);
        // this is a common value that will be added after the conditional
        // cmd.Parameters.AddWithValue("@item_stock", Stock);
      } else {
        sql_text = "insert into PRODUCTS" +
          " (item_no, item_name, price, cost, item_stock, dept_id, tax_rate1, tax_rate2, bulk_price, bulk_qty)" +
          " values " +
          "(@item_no,@item_name,@price,@cost,@item_stock,@dept_id,@tax_rate1,@tax_rate2,@bulk_price,@bulk_qty)";
        // this value is already included
        // cmd.Parameters.AddWithValue("@item_no", itemNo);
        cmd.Parameters.AddWithValue("@item_name", itemName);
        cmd.Parameters.AddWithValue("@price", price);
        cmd.Parameters.AddWithValue("@cost", cost);
        // this is a common value that will be added after the conditional
        // cmd.Parameters.AddWithValue("@item_stock", Stock);
        cmd.Parameters.AddWithValue("@dept_id", dept);
        cmd.Parameters.AddWithValue("@tax_rate1", tax1);
        cmd.Parameters.AddWithValue("@tax_rate2", tax2);
        cmd.Parameters.AddWithValue("@bulk_price", BulkPrize);
        cmd.Parameters.AddWithValue("@bulk_qty", BulkQty);
      }
      cmd.CommandText = sql_text;
      cmd.Parameters.AddWithValue("@item_stock", Stock);
      // Return the number of SQL records that were affected.
      return cmd.ExecuteNonQuery();
    }
  }
  // return -1 on Error
  return -1;
}

这篇关于从 csv 文件中检索值并在 C# 中的数据库中替换它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！