SQL Server inserting a single row with a significant number of columns

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?

1
0
2/8/2012 4:51:55 PM

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);
  }
}
1
2/8/2012 5:07:04 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow