在 SQL Server 中插入具有大量列的单行

发布于 2025-01-03 21:13:30 字数 495 浏览 1 评论 0原文

我有一个巨大的表单，大约有 110 个 ~~fields~~ 列（单行）需要保存在数据库中。

使用 ADO.NET 将这么多列插入数据库的最佳方法是什么？

我认为我不应该使用这样的插入语句，因为由于字段数量，查询会非常大。

conn.Open();
string insertString = @"
    insert into Categories (CategoryName, Description)
    values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.ExecuteNonQuery();

我考虑将数据转储到临时文件中，然后将它们添加到数据表中，然后使用 SqlBulkCopy 将它们插入数据库中。

有更好的方法吗？你会如何处理这种情况？

原文

I have a huge form that has around 110 ~~fields~~ columns (single row) that need to be saved in a database.

What is the best approach to insert these many columns into the database using ADO.NET?

I don't think I should be using an insert statement like this, since the query would be very large due to the number of fields.

conn.Open();
string insertString = @"
    insert into Categories (CategoryName, Description)
    values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.ExecuteNonQuery();

I think of dumping the data into temp file and then adding them into a datatable and then inserting them into database using SqlBulkCopy.

Is there a better approach? How would you handle this situation?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浮生面具三千个 2025-01-10 21:13:30

恐怕没有一个好的快捷方式来插入这么多列，但使用参数可能会节省一些调试时间。通过使用参数，您无需担心字符串中的撇号和类型转换等问题。这是一个示例：

public static void TryThis()
{
  try
  {
    using  (SqlConnection  con = new SqlConnection())
    {
      con.ConnectionString = "YourConnectionString";
      con.Open();
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = con;
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "INSERT INTO Categories (CategoryName, Description)  VALUES (@CategoryName, @Description)";
      cmd.Parameters.AddWithValue("CategoryName", "Miscellaneous");
      cmd.Parameters.AddWithValue("Description", "Whatever doesn't fit elsewhere");
    }
  }
  catch (Exception ex)
  {
    throw new Exception(ex.Message);
  }
}

I am afraid there isn't a good shortcut for inserting this number of columns, but using parameters will likely save some debugging time. By using parameters you do not need to worry about things likes apostrophes in strings and type conversions. Here's a sample:

public static void TryThis()
{
  try
  {
    using  (SqlConnection  con = new SqlConnection())
    {
      con.ConnectionString = "YourConnectionString";
      con.Open();
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = con;
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "INSERT INTO Categories (CategoryName, Description)  VALUES (@CategoryName, @Description)";
      cmd.Parameters.AddWithValue("CategoryName", "Miscellaneous");
      cmd.Parameters.AddWithValue("Description", "Whatever doesn't fit elsewhere");
    }
  }
  catch (Exception ex)
  {
    throw new Exception(ex.Message);
  }
}

回复收藏 0 原文

~没有更多了~