插入excel记录一次，不重复 [英] Insert excel records once , no duplicates

问题描述

我正在插入数据库excel表，我已经能够上传有和没有工作表名称感谢该组的帮助，我只是想知道如何防止我的循环多次插入数据，例如如果我的工作表有2条记录，则循环将其插入两次，表格最终看起来像这样：............................ />
ID DOB NAME SURNAME



1 1/02/1998 jack turner



2 2/02/1989 jill blue



1 1/02/1998 jack turner



2 2 / 02/1989 jill blue



i希望数据在插入后看起来像这样：



ID DOB NAME SURNAME



1 1/02/1998 jack turner



2 2/02/1989 jill蓝色



我的尝试：

i am inserting into database excel sheet, i have been able to upload with and without sheet names thanks to help from the group , i just want to know how can i prevent my loop from inserting data multiple times e.g. if my sheet has 2 records the loop inserts it twice and the table ends up looking like this: ............................
ID DOB NAME SURNAME

1 1/02/1998 jack turner

2 2/02/1989 jill blue

1 1/02/1998 jack turner

2 2/02/1989 jill blue

i would like the data to look like this once inserted:

ID DOB NAME SURNAME

1 1/02/1998 jack turner

2 2/02/1989 jill blue

What I have tried:

public void up(string sFileName = @"filename")
        { 
        
        string ssqltable = "[dbo].[My_Table]";
        //string sFileName = @"filename";

    try{
        string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';", sFileName);
        DataTable dt = new DataTable();
        SqlConnection sqlconn = new SqlConnection(strConnString);

         sqlconn.Open();
        using (OleDbConnection connection = new OleDbConnection(sConStr))
        {
            connection.Open();
           dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
           var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
            foreach(var sheet in sheets) //loop through the collection of sheets ;)
            {
                //your logic here...
                        string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets);
                        //get data

                        OleDbConnection oledbconn = new OleDbConnection(sConStr);
                        OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
                        oledbconn.Open();
                        
                                             
                        OleDbDataReader dr = oledbcmd.ExecuteReader();
                             {
                       
                                
                                DataTable table = new DataTable("benlist");
                                table.Load(dr);

                                // add two extra columns to data table to be added to database table
                                table.Columns.Add("name",typeof(string));
                                table.Columns.Add("surname",typeof(string));

                  
                                // add data to additional columns
                                foreach (DataRow row in table.Rows){

                                row["name"] =Session["Username"].ToString();
                                row["surname"] = Session["Username"].ToString();
                }


                SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
                bulkcopy.DestinationTableName = ssqltable;
          
                
                ////Mapping Table column    

                bulkcopy.ColumnMappings.Add("IDNumber", "[IDNumber]");
                bulkcopy.ColumnMappings.Add("DOB", "[DOB]");
                bulkcopy.ColumnMappings.Add("name", "[name]");
                bulkcopy.ColumnMappings.Add("surname", "[surname]");
               

               


                //sqlcmd.ExecuteNonQuery();
            //    while (dr.Read())
            //    {
                    bulkcopy.WriteToServer(table);

            //    }
                connection.Close();
                sqlconn.Close();

                             }
                        
                    }
                }
        
        
            }
            catch (Exception){}
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);
        
        }

推荐答案

如果你事先无法验证数据，我想你需要在插入之前查找记录，看它是否存在？

If you can't validate your data beforehand, I'd imagine you need to look for a record before you insert it, to see if it exists?

所以我删除了循环，当我将数据插入数据库表时，数据不再重复，谢谢



参考：href =https://stackoverflow.com/questions/1438083/getting-the-first-sheet-from-an-excel-document-regardless-of -sheet-name-with-ole

so i removed the loop and the data no longer gets duplicated when i insert it into the database table, thanks

reference: href="https://stackoverflow.com/questions/1438083/getting-the-first-sheet-from-an-excel-document-regardless-of-sheet-name-with-ole"

using (OleDbConnection connection = new OleDbConnection(sConStr))
    {
        connection.Open();
        /// get sheet name
       dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
       //var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
       // foreach(var sheet in sheets) //loop through the collection of sheets ;)
       // {
       var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
            //your logic here...
                    string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets);
                    //get data

这篇关于插入excel记录一次，不重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！