得到错误，而在我的数据库表中插入数据表记录 [英] Getting error while inserting datatables records in my database table
问题描述
我与ado.net很新，目前与插入数据表的记录到我的数据库表的工作。
I am pretty new with ado.net and currently working with inserting datatable records to my database tables.
我 1 excel文件其中包含了一些数据，并从这个Excel文件，我创建集，它含有许多数据表
I have 1 excel file which contains some data and from this excel file i am creating dataset which contains lots of datatables.
在此集我有2个数据表中这样的形式：
In this dataset i have 2 datatables in the form of this:
与记录 DataTable中0：类别
Datatable 0 with records:Category
ParentCategory Description
Electronics jhdkhsd
Sports kjshfhs
数据表1的记录 子类别
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
所以基本上我试图插入这一切数据表的数据是类别的数据表和子类别datatabl E在我的数据库表中的类别，但是当我试图插入收到错误：
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:
错误：参数化查询（@Id INT输出，@ ParentCategory
为nvarchar（50））插入。类别预计参数
'@ParentCategory'，这是不提供
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]);
}
我有我所有的类别的数据表行的RowState state属性设置为添加，所以我直接试图插入所有类别的记录。
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 badly gonna slow down my system.
任何人可以帮助我解决这个??
Can anybody help me with this??
推荐答案
使用下面的代码片段。
Use the following code snippet.
- 您需要指定在SQL的INSERT命令的INSETRT和SCOPE_IDENTITY语句之间分号。
- 同样，因为你的基础表中包含ID，名称的ParentId列，所以您必须将所有这些列映射到您的插入命令，只能插入名称和的ParentId字段，因为ID是自动生成的。 ID列被映射到一个输出参数，而其他列映射到输入参数。
插入单行到使用数据表
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.
下面的代码片段将帮助您一次插入批处理而不是一个行时，你需要多行插入到数据库中。您需要指定 adpapter.UpdateBatchSize 来的东西比1配料插入语句越大。
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.
批量插入行到从数据表数据库中使用（SqlConnection的连接=新的SqlConnection（））
{
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.
- 的 insert命令的CommandTimeout应足够大，允许批量插入的否则你将最终超时例外。如果此超时设置为0，则允许插入时间是不定的。
- 在做你想获得最大的性能否则你插入最终可能太慢批量插入。通过执行批量插入作为单个事务你将实现这种增加的性能。如果没有交易，数据库将在其中需要更多的时间分批每个INSERT启动事务。例如，如果你有500个为批量大小（即adapter.UpdateBatchSize），那么500 INSERT语句将导致数据库中的500交易如果插入命令没有连接到它的交易;但如果你连接一个事务插入命令则只有1交易将在那里为所有500插入操作，这使得业绩上去了。
- 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.
高性能，批量插入到从数据表数据库
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();
}
}
这篇关于得到错误，而在我的数据库表中插入数据表记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
