I am pretty new with ado.net and currently working with inserting datatable records to my database tables.
I have 1 Excel file which contains some data and from this Excel file I am creating a dataset which contains lots of datatables.
In this dataset I have 2 datatables in the form of this:
Datatable 0 with records: Category
ParentCategory Description
Electronics jhdkhsd
Sports kjshfhs
Datatable 1 with records: SubCategory
Subcategory ParentCategory Description
Mobile Electronics weprwp
Tv Electronics sdflskd
Balls Sports kjshdfkjh
Shoes Sports uytuyt
Now my database tables is like this:
Category:Id,Name,parentid
So basically I am trying to insert all this datatables data that is Category datatable and SubCategory datatable in my database table that is category but when I am trying to insert getting error:
Error:The parameterized query '(@Id int output,@ParentCategory
nvarchar(50))insert into Category' expects the parameter
'@ParentCategory', which was not supplied.
This is my code so far:
var dsFinal = new DataSet();
//Some code to read Excel sheets and data from Excel and create datatables and records with it.
//code to insert records
using (SqlConnection connection = new SqlConnection(""))
{
SqlDataAdapter adapter = new SqlDataAdapter();
var insertCommand = new SqlCommand("insert into Category (Name) values (@ParentCategory) SET @Id = SCOPE_IDENTITY()", connection);
var parameter = insertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
insertCommand.Parameters.Add("@ParentCategory", SqlDbType.NVarChar, 50, "Name");
parameter.Direction = ParameterDirection.Output;
insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.InsertCommand = insertCommand;
adapter.Update(dsFinal .Tables[0]);
}
Here I have all my Category datatable rows rowstate state property to added so I am directly trying to insert all category records.
Do I have to loop into individual records and do insert? As I am having huge amount like thousands of categories along with its subcategories and doing this will slow down my system.
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();
}
}
