我正在尝试将CSV文件数据导入SQL Server数据库。我有超过15000行,它每天都在向CSV文件中添加新的行。我所需要的就是将新添加的行插入到现有的数据库中。我现在有个问题,如果我导入这个文件,它会插入所有内容,包括15000行。我正在考虑将csv数据插入到临时表中,并过滤掉重复的行。但是我不知道该怎么做。
private void Save_Import_Data_SQL(DataTable importData)
{
using(SqlConnection conn = new SqlConnection(myconnstring))
{
conn.Open();
foreach(DataRow importRow in importData.Rows)
{
DateTime Start_Date_tt = ConvertStringToDate(importRow["Start date"].ToString());
Decimal Start_Time_tt = ConvertStringToDecimal(importRow["Start time"].ToString());
DateTime Finish_Date_tt = ConvertStringToDate(importRow["Finish date"].ToString());
Decimal Finish_Time_tt = ConvertStringToDecimal(importRow["Finish time"].ToString());
Decimal Pieces_tt = ConvertStringToDecimal(importRow["Pieces"].ToString());
Decimal cycle_tt = ConvertStringToDecimal(importRow["Average part cycle time"].ToString());
Decimal length_tt = ConvertStringToDecimal(importRow["Length_pa"].ToString());
SqlCommand cmd = new SqlCommand("INSERT INTO Silver_Robot(Program_S,Grpup_S,Start_Date_S,Start_Time_S,Pieces_S,Finish_Date_S,Finish_Time_S,Average_Part_Cycle_Time_S,Mode_S,Length_S) VALUES(@program,@group,@start_Date,@start_time,@pieces,@finish_date,@finish_time,@avarage_part,@mode_p,@length_p)", conn);
cmd.Parameters.AddWithValue("@program", importRow["Program"]);
cmd.Parameters.AddWithValue("@group", importRow["Group"]);
cmd.Parameters.AddWithValue("@start_Date", Start_Date_tt);
cmd.Parameters.AddWithValue("@start_time", Start_Time_tt);
cmd.Parameters.AddWithValue("@pieces", Pieces_tt);
cmd.Parameters.AddWithValue("@finish_date", Finish_Date_tt);
cmd.Parameters.AddWithValue("@finish_time", Finish_Time_tt);
cmd.Parameters.AddWithValue("@avarage_part", cycle_tt);
cmd.Parameters.AddWithValue("@mode_p", importRow["Mode"]);
cmd.Parameters.AddWithValue("@length_p", length_tt );
cmd.ExecuteNonQuery();
}
}
}
任何帮助都将不胜感激
发布于 2018-05-31 22:35:43
这就是我修复它的方法,到目前为止,一切都运行得很好。
我创建了一个程序
USE [Electrical_ENG]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Silver_Robot_Insert]
@program varchar(50),
@group varchar(50),
@start_Date datetime,
@start_time varchar(50),
@pieces decimal,
@finish_date datetime,
@finish_time varchar(50),
@avarage_part decimal,
@mode_p varchar(50),
@length_p decimal,
@Total_Time_p decimal
AS
DECLARE @err_msg nvarchar(255);
IF NOT EXISTS(SELECT * FROM Silver_Robot WHERE Program_S=@program AND Grpup_S=@group AND Start_Date_S=@start_Date AND Start_Time_S=@start_time AND Pieces_S=@pieces AND Finish_Date_S=@finish_date AND Finish_Time_S=@finish_time AND Average_Part_Cycle_Time_S=@avarage_part AND Mode_S=@mode_p AND Length_S=@length_p AND Total_Time_S=@Total_Time_p)
BEGIN
INSERT INTO Silver_Robot(Program_S,Grpup_S,Start_Date_S,Start_Time_S,Pieces_S,Finish_Date_S,Finish_Time_S,Average_Part_Cycle_Time_S,Mode_S,Length_S,Total_Time_S)
VALUES(@program,@group,@start_Date,@start_time,@pieces,@finish_date,@finish_time,@avarage_part,@mode_p,@length_p,@Total_Time_p)
END
插入:
public void Insert_Silver_Robot(String Program_S, String Grpup_S, DateTime Start_Date_S, String Start_Time_S, Decimal Pieces_S, DateTime Finish_Date_S, String Finish_Time_S, double Average_Part_Cycle_Time_S, String Mode_S, double Length_S, double Total_Time_S)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = con_str_S3;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "Silver_Robot_Insert";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@program", Program_S);
cmd.Parameters.AddWithValue("@group", Grpup_S);
cmd.Parameters.AddWithValue("@start_Date", Start_Date_S);
cmd.Parameters.AddWithValue("@start_time", Start_Time_S);
cmd.Parameters.AddWithValue("@pieces", Pieces_S);
cmd.Parameters.AddWithValue("@finish_date", Finish_Date_S);
cmd.Parameters.AddWithValue("@finish_time", Finish_Time_S);
cmd.Parameters.AddWithValue("@avarage_part", Average_Part_Cycle_Time_S);
cmd.Parameters.AddWithValue("@mode_p", Mode_S);
cmd.Parameters.AddWithValue("@length_p", Length_S);
cmd.Parameters.AddWithValue("@Total_Time_p", Total_Time_S);
//try
//{
conn.Open();
cmd.ExecuteNonQuery();
//}catch(Exception ex)
//{
// MessageBox.Show(ex.Message);
//}
//finally
//{
// if(con.State==ConnectionState.Open)
// {
conn.Close();
// }
// }
}
}
在upload click事件中:
foreach (DataRow importRow in importData.Rows)
{
DateTime Start_Date_tt = ConvertStringToDate(importRow["Start date"].ToString());
DateTime Finish_Date_tt = ConvertStringToDate(importRow["Finish date"].ToString());
Decimal Pieces_tt = ConvertStringToDecimal(importRow["Pieces"].ToString());
double cycle_tt = ConvertStringToDouble(importRow["Average part cycle time"].ToString());
double length_tt = ConvertStringToDouble(importRow["Length_pa"].ToString());
double total_time_tt = cycle_tt * length_tt;
string program_tt = importRow["Program"].ToString();
string group_tt = importRow["Group"].ToString();
string start_time_tt = importRow["Start time"].ToString();
string finish_time_tt = importRow["Finish time"].ToString();
string mode_tt = importRow["Mode"].ToString();
dbactions.Insert_Silver_Robot(program_tt,group_tt,Start_Date_tt,start_time_tt,Pieces_tt,Finish_Date_tt,finish_time_tt,cycle_tt, mode_tt,length_tt,total_time_tt);
}
同样,使用bulkinsert是最好的方法,但我没有在我的服务器中使用它的权限。谢谢你的帮助。
发布于 2018-05-31 06:49:11
您可以只使用IF NOT EXIST in SQL:https://forums.asp.net/t/1738957.aspx?SqlCommand+with+IF+NOT+EXISTS+statement
SQL Server Insert if not exist
因此,如果您只需替换SQL命令:
SqlCommand cmd = new SqlCommand("INSERT INTO Silver_Robot(Program_S,Grpup_S,Start_Date_S,Start_Time_S,Pieces_S,Finish_Date_S,Finish_Time_S,Average_Part_Cycle_Time_S,Mode_S,Length_S) VALUES(@program,@group,@start_Date,@start_time,@pieces,@finish_date,@finish_time,@avarage_part,@mode_p,@length_p)", conn);
如下所示:
SqlCommand cmd = new SqlCommand("IF NOT EXISTS (SELECT * FROM Silver_Robot WHERE Program_S = @program AND Grpup_S = @group AND Start_Date_S = @start_Date AND Start_Time_S = @start_time AND Pieces_S = @pieces AND Finish_Date_S = @finish_date AND Finish_Time_S = @finish_time AND Average_Part_Cycle_Time_S = @avarage_part AND Mode_S = @mode_p AND Length_S = @length_p) BEGIN INSERT INTO Silver_Robot(Program_S,Grpup_S,Start_Date_S,Start_Time_S,Pieces_S,Finish_Date_S,Finish_Time_S,Average_Part_Cycle_Time_S,Mode_S,Length_S) VALUES(@program,@group,@start_Date,@start_time,@pieces,@finish_date,@finish_time,@avarage_part,@mode_p,@length_p) END", conn);
应该能行得通。
但是,正如您在第一个链接中看到的,创建一个存储过程可能是一个好主意,因为在这种情况下,您将获得更好的性能。
https://stackoverflow.com/questions/50608333
复制相似问题