how to import the excel file to sql database using vb.net?

I have a more data in excel file .so i have to import it into sql开发者_Python百科 database using vb.net.can anyone send the source code?

---

Dim ExcelConnection As New 

System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyExcelSpreadsheet.xlsx; Extended Properties=""Excel 12.0 Xml; HDR=Yes""")
ExcelConnection.Open()

Dim expr As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader

Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=MMSQL1; Initial Catalog=DbName; User Id=UserName; Password=password;"
SQLconn.ConnectionString = ConnString
SQLconn.Open()

Using bulkCopy Asd SqlBulkCopy = New SqlBulkCopy(SQLConn)
bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER"

  Try
    objDR = objCmdSelect.ExecuteReader
    bulCopy.WriteToServer(objDR)
    objDR.Close()
    SQLConn.Close()

  Catch ex As Exception
    MsgBox(ex.ToString)
  End Try
End Using

---

If it's a one-off job, use DTS or SSIS. No code required.

Otherwise, you can open Excel as a data source, suck up its contents and insert into your database.