How to import excel file to sql by clicking a button in c#

Question

Hi guys. 

 

I am new in this forum and i need an answer on my problem. How am I going to import an excel file to my database(SQL) by just clicking a button in C# windows form Application. I have read so many articles and all of them are saying about SqlBulkCopy. I tried one which is inserted in a class file but I do not know on how to call it to my button for it to import an excel file. Can anyone help me? below is thecode that I found:

 

 

 

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 student,rollno,course 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=BENJ\\SQLEXPRESS;user id=sa;password=aspire4736z;database=ComputerizedEnrollmentSystem;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

}

}