如何不将新数据添加到与数据库中的数据具有相似id的数据库中？ [英] How not add new data into database that has similar id with data in database ?

问题描述

大家好。所以我在Windows窗体中创建一个项目，我要做的是从Excel获取数据并在Datagridview中显示它。我正在上传Sql Server上的数据，而不是我在DVG中发送数据。每次我在服务器上传数据我必须向旧数据添加新数据，但我没有数据库中具有相似ID的数据。我有代码在sql Server中添加新数据，但我不知道如何添加新数据，它与sql中的数据具有类似的id。有谁知道怎么做？这是我的代码：

Hello everyone. So I am making a Project a in Windows Forms and what I have to do is to get data from Excel and display it in Datagridview. I am Uploading data on Sql Server and than I am sending it in DVG.Every Time I Upload data on Server I have to add new data to old data but I does not have datas in database with similar id. I have Code to add new data in sql Server but I donn't know how not to add new data, that have a similar id with data in sql. Has anyone idea how t do that ? Here is my Code:

SqlBulkCopy oSqlBulk = null;

            string excelFilePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            excelFilePath = Path.Combine(excelFilePath, label1.Text);
            OleDbConnection excelConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 12.0;");

            try
            {
                excelConn.Open();
                OleDbCommand excelComm = new OleDbCommand("SELECT *FROM [Sheet1$]", excelConn);

                OleDbDataReader excelreader = excelComm.ExecuteReader();

                using(SqlConnection con = new SqlConnection("Data Source=DESKTOP-OETHK6U\\SQLEXPRESS;Initial Catalog=Intel_DB;Integrated Security=True"))
                {
                    con.Open();
                    oSqlBulk = new SqlBulkCopy(con);
                    oSqlBulk.DestinationTableName = "Intel_Data";
                    oSqlBulk.WriteToServer(excelreader);
                }
                label2.Text = "Data has been added into database";
            }
            catch(Exception ex)
            {
                label2.Text = "Data has not been added into database";
                MessageBox.Show(ex.Message);
            }
            finally
            {
                oSqlBulk.Close();
                oSqlBulk = null;
                excelConn.Close();
                excelConn = null;
            }

推荐答案

，excelConn）;

OleDbDataReader excelreader = excelComm .ExecuteReader（）;

使用（SqlConnection con = new SqlConnection（< span class =code-string> Data Source = DESKTOP-OETHK6U \\ SQLEXPRESS; Initial Catalog = Intel_DB; Integrated Security = True ））
{
con.Open（）;
oSqlBulk = new SqlBulkCopy（con）;
oSqlBulk.DestinationTableName = Intel_Data;
oSqlBulk.WriteToServer（excelreader）;
}
label2.Text = 数据已添加到数据库;
}
catch （Exception ex）
{
label2.Text = 数据尚未添加到数据库;
MessageBox.Show（ex.Message）;
}
最后
{
oSqlBulk.Close（）;
oSqlBulk = null ;
excelConn.Close（）;
excelConn = null ;
}

", excelConn); OleDbDataReader excelreader = excelComm.ExecuteReader(); using(SqlConnection con = new SqlConnection("Data Source=DESKTOP-OETHK6U\\SQLEXPRESS;Initial Catalog=Intel_DB;Integrated Security=True")) { con.Open(); oSqlBulk = new SqlBulkCopy(con); oSqlBulk.DestinationTableName = "Intel_Data"; oSqlBulk.WriteToServer(excelreader); } label2.Text = "Data has been added into database"; } catch(Exception ex) { label2.Text = "Data has not been added into database"; MessageBox.Show(ex.Message); } finally { oSqlBulk.Close(); oSqlBulk = null; excelConn.Close(); excelConn = null; }

在我遇到这类问题的所有情况下，我已经通过

解决了这个问题。 br />
a）创建一个镜像原始目标表的'staging'表EXCEPT没有主/冲突键

b）使用sqlbulkcopy上传到'staging'表

c）将登台记录插入到真正的目标表中，其中不存在密钥违规

In all the situations where Ive come across this sort of issue issue, Ive solved it by

a) creating a 'staging' table that mirrors the original destination table EXCEPT has no primary/conflicting key
b) used sqlbulkcopy to upload into the 'staging' table
c) insert records from staging into the real destination table where there doesnt exist a key violation

这篇关于如何不将新数据添加到与数据库中的数据具有相似id的数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！