Hi hemma123,
If you simply want to clear the table records before inserting the new ones then you can fire a SQL Query "TRUNCATE TABLE TABLENAME" before copying the datatable to database table.
You can achieve like below
Private Sub btnOpenExcel_Click(sender As Object, e As EventArgs)
Dim dt As New DataTable()
If openFileDialog1.ShowDialog() = DialogResult.OK Then
Dim filePath As String = openFileDialog1.FileName
Dim extension As String = "." + filePath.Split("."c)(filePath.Split("."c).Length - 1)
Dim conString As String = String.Empty
Select Case extension
Case ".xls"
'Excel 97-03.
conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Exit Select
Case ".xlsx"
'Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
Exit Select
End Select
conString = String.Format(conString, filePath)
Using connExcel As New OleDbConnection(conString)
Using cmdExcel As New OleDbCommand()
Using odaExcel As New OleDbDataAdapter()
cmdExcel.Connection = connExcel
'Get the name of First Sheet.
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim sheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
'Read Data from First Sheet.
connExcel.Open()
cmdExcel.CommandText = (Convert.ToString("SELECT * From [") & sheetName) + "]"
odaExcel.SelectCommand = cmdExcel
odaExcel.Fill(dt)
connExcel.Close()
End Using
End Using
End Using
' Convert empty cells in of DataTable to NULL for VARCHAR column and 0 for other.
For i As Integer = 0 To dt.Rows.Count - 1
For j As Integer = 0 To dt.Columns.Count - 1
If [String].IsNullOrEmpty(dt.Rows(i)(j).ToString()) Then
If dt.Columns(j).DataType = GetType(String) Then
dt.Rows(i)(j) = "NULL"
Else
dt.Rows(i)(j) = 0
End If
End If
Next
Next
If dt.Rows.Count > 0 Then
Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(consString)
Dim cmd As SqlCommand = New SqlCommand("TRUNCATE TABLE Customers", con)
cmd.ExecuteNonQuery()
Using sqlBulkCopy As New SqlBulkCopy(con)
sqlBulkCopy.DestinationTableName = "dbo.Customers"
sqlBulkCopy.ColumnMappings.Add("CustomerId", "CustomerId")
sqlBulkCopy.ColumnMappings.Add("Name", "Name")
sqlBulkCopy.ColumnMappings.Add("Country", "Country")
Try
con.Open()
sqlBulkCopy.WriteToServer(dt)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
con.Close()
MessageBox.Show("Record inserted successfully")
End Try
End Using
End Using
End If
End If
End Sub