This post was most recently updated on August 5th, 2024
If you have data in excel and you want to add data in you database using C# this is useful example. Follow below steps:
Step 1:
Create database tables to import data
|
1 2 3 4 5 6 |
CREATE TABLE ( [EmpID] [int] IDENTITY(1,1) NOT NULL, [EmpName] [nvarchar](150) NOT NULL, [ContactNo] [nvarchar](150) NOT NULL ) |
Step 2:
Add below namespaces in your project
|
1 2 |
using System.Data.OleDb; using System.Data.SqlClient; |
Step 3:
Add below method and send file path to this method as parameters.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
public void importdatafromexcel(string excelfilepath) { //declare variables - edit these based on your particular situation string ssqltable = "tdatamigrationtable"; // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different string myexceldataquery = "select EmpName,ContactNo from [sheet1$]"; try { //create our connection strings string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + ";extended properties=" + "\"excel 8.0;hdr=yes;\""; string ssqlconnectionstring = "server=mydatabaseservername;user"+ "id=dbuserid;password=dbuserpassword;database=databasename;connection reset=false"; //execute a query to erase any previous data from our destination table string sclearsql = "delete from " + ssqltable; sqlconnection sqlconn = new sqlconnection(ssqlconnectionstring); sqlcommand sqlcmd = new sqlcommand(sclearsql, sqlconn); sqlconn.open(); sqlcmd.executenonquery(); sqlconn.close(); //series of commands to bulk copy data from the excel file into our sql table oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring); oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn); oledbconn.open(); oledbdatareader dr = oledbcmd.executereader(); sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring); bulkcopy.destinationtablename = ssqltable; while (dr.read()) { bulkcopy.writetoserver(dr); } oledbconn.close(); } catch (exception ex) { //handle exception } } |
This method get file path as parameter then set connection string and after that delete data from sql table. If you want to delete then comment code for delete record. Then using oledbcommand & bulkinsert insert data into destinationtablename.
