Use the following code snippet.
- You need to specify a semi colon between the INSETRT and SCOPE_IDENTITY statements in the SQL for insert command.
- Also, since your base table contains ID, Name, ParentId columns, so you must map all these columns to your insert command, and only insert Name and ParentId columns since ID is auto-generated. The ID column is mapped to an output parameter, while the other columns are mapped to input parameters.
Insert a single row into database using DataTable
using(SqlConnection connection = new SqlConnection("")) {
SqlDataAdapter adapter = new SqlDataAdapter();
var insertCommand = new SqlCommand("Insert into Category (Name, ParentId) Values (@name, @parentId); SET @ID = SCOPE_IDENTITY(); ", connection);
var parameter = insertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 50, "Name");
insertCommand.Parameters.Add("@parentId", SqlDbType.Int, 0, "ParentId");
SqlParameter parameter = adapter.InsertCommand.Parameters.Add("@ID",SqlDbType.Int, 0, "ID");
parameter.Direction = ParameterDirection.Output;
adapter.insertCommand = insertCommand;
adapter.insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.Update(dsControlSheet.Tables[0]);
}
The above should take care of the error message you mentioned in your post.
The code snippet below will help you insert rows in batch rather than one at a time when you need to insert many rows into the database. You need to specify adpapter.UpdateBatchSize to something greater than 1 for batching inserts statements.
Batch Insert rows into database from DataTable
using (SqlConnection connection = new SqlConnection(""))
{
SqlDataAdapter adapter = new SqlDataAdapter();
var insertCommand = new SqlCommand("Insert into Category (Name, ParentId) Values (@name, @parentId);", connection);
var parameter = insertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 50, "Name");
insertCommand.Parameters.Add("@parentId", SqlDbType.Int, 0, "ParentId");
adapter.insertCommand = insertCommand;
// When setting UpdateBatchSize to a value other than 1, all the commands
// associated with the SqlDataAdapter have to have their UpdatedRowSource
// property set to None or OutputParameters. An exception is thrown otherwise.
insertCommand.UpdatedRowSource = UpdateRowSource.None;
// Gets or sets the number of rows that are processed in each round-trip to the server.
// Setting it to 1 disables batch updates, as rows are sent one at a time.
adapter.UpdateBatchSize = 50;
adapter.Update(dsControlSheet.Tables[0]);
}
When doing batch inserts, a couple of points need to be kept in mind.
- CommandTimeout of insert command should be large enough to allow for batch inserts else you will end up with a timeout exception. If you set this timeout to 0 then the time allowed for inserts is indefinite.
- When doing batch inserts you want to get maximum performance else your inserts could end up being too slow. By executing the batch inserts as a single transaction you will achieve this increased performance. Without a transaction, the database will start a transaction for each INSERT in the batch which takes more time. For example, if you have 500 as the batch size (i.e. adapter.UpdateBatchSize), then 500 INSERT statements will result in 500 transactions in the database if the insert command has no transaction attached to it; but if you attach a transaction to insert command then only 1 transaction will be there for all 500 INSERTs which makes the performance go up.
High Performance with Batch Inserts into database from DataTable
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
var insertCommand = new SqlCommand("Insert into Category (Name, ParentId) Values (@name, @parentId);", connection);
var parameter = insertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 50, "Name");
insertCommand.Parameters.Add("@parentId", SqlDbType.Int, 0, "ParentId");
adapter.insertCommand = insertCommand;
// When setting UpdateBatchSize to a value other than 1, all the commands
// associated with the SqlDataAdapter have to have their UpdatedRowSource
// property set to None or OutputParameters. An exception is thrown otherwise.
insertCommand.UpdatedRowSource = UpdateRowSource.None;
// Gets or sets the number of rows that are processed in each round-trip to the server.
// Setting it to 1 disables batch updates, as rows are sent one at a time.
adapter.UpdateBatchSize = 50;
//NOTE: When doing batch updates it's a good idea to fine tune CommandTimeout value
//since default is 30 seconds. If your batch insert takes more than 30 s (default value)
//then make sure to increase this value. I am setting this to 90 s
//but you must decide this based on your situation.
//Set this to 0 if you are not sure how long your batch inserts will take
insertCommand.CommandTimeout = 90;
//HOW TO MAKE BATCH INSERTS FASTER IN PERFORMANCE
//Perform batch updates in a single transaction to increase batch insert performance
connection.Open();
var transaction = connection.BeginTransaction();
insertCommand.Transaction = transaction;
try {
adapter.Update(dsControlSheet.Tables[0]);
transaction.Commit();
}
catch(Exception e) {
if(transaction!=null) {
transaction.Rollback();
}
//log exception
}
finally {
connection.Close();
}
}
Add("@Id"andAdd("@ParentCategory") – Poltergeistvar parameter = insertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");after the lineinsertCommand.Parameters.Add("@ParentCategory", SqlDbType.NVarChar, 50, "Name");– Poltergeist