c# - SqlBulkCopy Ignore Duplicate Records of Datatable From DataBase -

can ignore duplicate records of data present in sql database datatable passing sqlbulkcopy. if yes how , explain me if no , other option.

as previous poster said, not built in. achieve similar using following:

sql stored procedure accepts tablevaluedparameter data require.

in stored proc, insert records temp table. once have there, can use sqls merge statement in stored proc insert data doesn't exist.

so, let assume our data people's names stored in table people. hold id , name. assume table called 'people'.

here's how create table valued parameter type (created in sql server)

create type udt_person table( [id] [int] not null, [name] [nvarchar(50)] null ) go 

i create stored procedure:

create procedure savenewpeople @ppeople udt_person begin     -- create temp table     create table #tmppeople (id int, name varchar 50)      -- stage data passed in temp table     insert #tmppeople     select id, name @ppeople      -- nb: need think locking strategy bit here     merge people p     using #tmppeople t     on p.id = t.id     when not matched target         -- want insert new person         insert (id, name) values (t.id, t.name)     when matched         -- may not need this, assume updating name example         update set p.name = t.name  end 

now have sql in place.

let create bulk of data in c#:

datatable ppl = new datatable(); ppl.columns.add("id", typeof(int)); ppl.columns.add("name", typeof(string));  // table created, let's add people var bob = ppl.newrow(); bob["id"] = 1; bob["name"] = "bob"; ppl.rows.add(bob);  var jim = ppl.newrow(); jim["id"] = 2; jim["name"] = "jim"; ppl.rows.add(jim);  // that's enough people now, let's call stored procedure using(var conn = new sqlconnection("youconnstringhere")) {     using(var cmd = new sqlcommand("savenewpeople", conn))     {         cmd.commandtype = commandtype.storedprocedure;          var tvp = new sqlparameter         {           parametername = "@ppeople",           sqldbtype = sqldbtype.structured,           value = ppl,           typename = "udt_person"         }         cmd.parameters.add(tvp);         conn.open();         cmd.executenonquery();     }  } 

hopefully gives idea. if modified c# datatable, should see rows inserted, updated or ignored.

good luck.