Database

Member 12287337 wrote:

could i have done something wrong in the code?

// The values to insert are contained in the Properties of container class, I'll call it IData for this example
List<string> columns = new List<string>();
List<string> variables = new List<string>();
string sqlStatement = null;

// Dynamically create and execute a parameterized SqlStatement
using (SqlCommand command = new SqlCommand() { CommandType = CommandType.Text, Connection = connection })
{
 // IData is a container class that holds the properties and the name of the table that the object is stored in
 // The IProperty object contains the name of the column, the Sql Data Type to use, and the value to insert
 foreach (IProperty prop in IData.Properties)
 {
  columns.Add(prop.TableColumnName);
  variables.Add("@" + prop.TableColumnName);

  // Create the parameter
  var currentParameter = new Parameter("@" + prop.TableColumnName, prop.SqlType);
  currentParameter.Value = (objProp.Value == null) ? DBNull.Value : objProp.Value;
  command.Parameters.Add(currentParameter);
 }

 sqlStatement = String.Join(" ", new string[]
 {
  "INSERT INTO dbo." + IData.TableName,
  "(" + String.Join(", ", columns.ToArray()) + ")",
  "VALUES (" + String.Join(", ", variables.ToArray()) + ");"
 });

 command.CommandText = sqlStatement;
 command.ExecuteNonQuery();
}

Foothill wrote:

I should be passing the length of the data to the SqlParameter.

Foothill wrote:

Trying to weigh which overhead has greater impact, code conversion or database query plans.