Inserting a single row with large number of columns in SQL Server

ado.net bulkinsert sqlbulkcopy sql-server

Question

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?

Accepted Answer

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);
  }
}



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why