无法执行批量插入操作 [英] cannot perform batch insert operations
问题描述
我有一个从excel文件中提取记录的应用程序.
必须从excel文件将大约1000条记录插入数据库.
我将记录存储在大约4个集合中(每个集合代表一行)，所以我有一个SqlDataAdapter，以便可以一次将记录插入.
明智地在后端创建了一个存储过程，该存储过程随后用于执行插入操作.
I have an application that extracts records from an excel file.
There are about 1000 records which must be inserted into a database from the excel file.
I store the records in about 4 collections (each one representing a row) so I have an SqlDataAdapter so that I can insert the records in one go.
Backend wise I created a stored procedure which is then used to perform the insert operation.
CREATE PROCEDURE [dbo].[spAddDebtor]
@name char(50),
@person char(50),
@number char(50),
@address char(255)
AS
IF EXISTS(SELECT * FROM dbo.Debtor WHERE Debtor_Name = @Name)
RETURN
INSERT INTO DEBTOR (Debtor_Name, Contact_Person, Contact_number, Contact_Address)
VALUES (@name, @person, @number, @address)
GO
在C#中，我使用此方法
and in C# I use this method
// Insert records into database
private void insertRecords()
{
// populate collections from Excel file
populateAddress();
populateContactPerson();
populateNames();
populatePhones();
string sql = "SELECT * FROM DEBTOR";
// Create data adapter
SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString);
// Create and fill dataset
DataSet ds = new DataSet();
da.Fill(ds, "debtor");
// Get data table reference
DataTable dt = ds.Tables["debtor"];
sCommand.UpdatedRowSource = UpdateRowSource.None;
SqlDataAdapter adpt = new SqlDataAdapter();
try
{
// iterate through all records
// and perform insert on each iteration
for (int i = 0; i < names.Count; i++)
{
sCommand.Parameters.AddWithValue
("@debtor_name", Names[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_person", ContactPeople[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_number", Phones[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_address", Addresses[i].ToString());
}
adpt.InsertCommand = sCommand;
// Specify the batch size
Size = Names.Count;
adpt.UpdateBatchSize = size;
// Open the connection
objConnection.Open();
RowsAffected = adpt.Update(dt); // Execute the statement and show
// number of rows affected
objConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
// Realease resources
objConnection = null;
objCommand = null;
sCommand = null;
sConnection = null;
adpt = null;
dt = null;
ds = null;
da = null;
}
}
问题是此代码运行没有错误，但是在数据库中没有新记录.
我尝试放置断点来检查集合中是否填充了excel数据，并且看起来它们实际上确实具有完全没有插入的数据.
我从sql本身测试了sproc，它似乎正常工作，没有人对我该如何提出建议吗?
The thing is this code runs with no errors but in the database the new records are not there.
I tried putting break point to check if the collections where populated with the excel data and it appears that they infact have the data the insertion is simply not happening.
I tested the sproc from sql itself at it seems to work with no problems does anyone have a suggestion as to how I can go about it?
推荐答案
代码
for (int i = 0; i < names.Count; i++)
{
sCommand.Parameters.AddWithValue
("@debtor_name", Names[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_person", ContactPeople[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_number", Phones[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_address", Addresses[i].ToString());
}
您正在遍历名称列表并将参数添加到sCommnand对象.
如果直接运行sCommand.ExecuteNonQuery();，它将抛出异常，因为存储过程中实际指定的参数比实际指定的要多.
你也在打电话
You are Looping through the names List and adding parameter to sCommnand object.
If you directly run sCommand.ExecuteNonQuery();, it will throw an exception as there are more parameters than actually specified int the Stored Procedure.
Also you are calling
RowsAffected = adpt.Update(dt);
在您的代码中，没有sCommand & dt 对象.
adpt.InsertCommand = sCommand;在这种情况下是没有意义的，因为dt 没有添加额外的行.
解决方案1:
你可能会这样称呼
From your code there is no relation of sCommand & dt object.
adpt.InsertCommand = sCommand; is meaningless in this context beacuse dt has no extra rows added.
Solution 1:
you may call like this
for (int i = 0; i < names.Count; i++)
{
sCommand.Parameters.AddWithValue
("@debtor_name", Names[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_person", ContactPeople[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_number", Phones[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_address", Addresses[i].ToString());
//New Code to directly call the stored procedures
sCommand.ExecuteNonQuery();
sCommand.Parameters.Clear();
}
//Comment following lines
/*
//Specify the batch size
Size = Names.Count;
adpt.UpdateBatchSize = size;
// Open the connection
objConnection.Open();
RowsAffected = adpt.Update(dt);
// Execute the statement and show
// number of rows affected
objConnection.Close();
*/
解决方案2:
Solution 2:
for (int i = 0; i < names.Count; i++)
{
/* sCommand.Parameters.AddWithValue
("@debtor_name", Names[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_person", ContactPeople[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_number", Phones[i].ToString());
sCommand.Parameters.AddWithValue
("@contact_address", Addresses[i].ToString());
*/
//New Code for sol 2 :
DataRow f = dt.NewRow();
f["@debtor_name"] = Names[i].ToString();
f["@contact_person"] = ContactPeople[i].ToString();
f["@contact_number"] = Phones[i].ToString();
f["@contact_address"] = Addresses[i].ToString();
dt.Rows.Add(f);
}
// Specify the batch size
Size = Names.Count;
adpt.UpdateBatchSize = size; // NOW THIS WILL BE CALLED ..
// Open the connection
objConnection.Open();
RowsAffected = adpt.Update(dt); // Execute the statement and show
// number of rows affected
objConnection.Close();
您是否授予使用连接字符串的用户权限来执行SP?
抱歉，在使用Linq时无法为您提供代码.
Did you give rights to the user used in the connection string to execute the SP?
Sorry cannot help you with the code as I use Linq.
这篇关于无法执行批量插入操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
