asp.net - How to Insert whole DataTable into Sql table At once in C# -

i have excel sheet (named $sheet1) , sql table(named users) same formate given below

    id | username | firstname | lastname | dateofbirth |     1  | robert   | robert    | poinan   | 1984        |     2  | joy      | joy       | rob      | 1990        | 

i have read whole excel sheet data in dataset (named 'ds') want insert whole dataset (which 'ds') in sql table (which 'users')

i using loop (can use foreach loop) insert 'ds' (dataset) rows 1 one users (sql table) table

sqlconn.open(); (int = 0; < ds.tables[0].rows.count; i++)                 { sqlcommand cmd = new sqlcommand("insert [users] ([id],[username],[firstname],[lastname],[dateofbirth]) values(@id,@username,@firstname,@lastname,@dateofbirth))", sqlconn);   cmd.parameters.addwithvalue("@id", ds.tables[0].rows[i][0].tostring()); cmd.parameters.addwithvalue("@username", ds.tables[0].rows[i][1].tostring()); cmd.parameters.addwithvalue("@firstname", ds.tables[0].rows[i][2].tostring()); cmd.parameters.addwithvalue("@lastname", ds.tables[0].rows[i][3].tostring()); cmd.parameters.addwithvalue("@dateofbirth", ds.tables[0].rows[i][4].tostring()); cmd.executenonquery();                     }                 }                 sqlconn.close(); 

in code facing lot of problems 1 of these is, if there error in inserting row program stop rows inserted before exists in sql database next time when try run program data rows duplicated. have millions of records. if want check data row in sql table takes lot of time execute whole process.

my quetion is. there way insert whole 'datatable (which in dataset)' users table @ once

something this

insert [users](id, firstname,lastname,dateofbirth)  select id, firstname,lastname,dateofbirth ds.tables[0] 

;

this approach given in msdn(https://msdn.microsoft.com/en-us/library/ex21zs8x(v=vs.110).aspx). make dt , pass dt parameter bulkcopy.

datatable dt = new datatable(); dt.columns.add("fieldname", typeof(system.decimal));