خودم حلش کردم.
کد رو میزارم شاید به درد کسی بخوره
البته اضافاتشو خودتون زحمت بکشید حذف کنید
Dim sSourceConstr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;D ata Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath)
Dim cnst As String = ConfigurationManager.ConnectionStrings("dbconnecti on").ConnectionString
Dim cn As New SqlConnection(cnst)
Dim sSourceConnection As New OleDbConnection(sSourceConstr)
Using sSourceConnection
Dim sql As String = String.Format("Select [date],[weekday],[event],[year],[tatil] FROM [{0}]", "Sheet1$")
Dim command As New OleDbCommand(sql, sSourceConnection)
sSourceConnection.Open()
Dim submited As Integer = 0
Using dr As OleDbDataReader = command.ExecuteReader()
While dr.Read
If IsDBNull(dr(0)) Or IsDBNull(dr(1)) Or IsDBNull(dr(3)) Or IsDBNull(dr(4)) Then
Else
Dim pdate As Integer = dr(0)
Dim weekdaye As Integer = dr(1)
Dim pevent As String = dr(2).ToString
Dim pyear As Integer = dr(3)
Dim tatil As Integer = dr(4)
If IsNumeric(pdate) And IsNumeric(weekdaye) And IsNumeric(pyear) And IsNumeric(tatil) Then
Dim cm As New SqlCommand
cm.CommandText = "select id from salname where date=" & pdate
cm.Connection = cn
cn.Open()
Dim ex As Integer = cm.ExecuteScalar
cn.Close()
If ex = Nothing Then
cm.CommandText = "insert into salname (date,weekday,event,year,tatil) values" _
& " (" & pdate & "," & weekdaye & ",N'" & pevent & "'," & pyear & "," & tatil & ")"
cm.Connection = cn
cn.Open()
cm.ExecuteNonQuery()
cn.Close()
submited = submited + 1
Else
cm.CommandText = "update salname set" _
& " weekday=" & weekdaye _
& ",event=N'" & pevent & "',year=" & pyear & ",tatil=" & tatil & " where id=" & ex
cm.Connection = cn
cn.Open()
cm.ExecuteNonQuery()
cn.Close()
submited = submited + 1
End If
Else
Label1.Visible = True
Label1.Text = Label1.Text & "<br>" & "روز " & pdate & " ثبت نشد"
End If
End If
End While
Label2.Visible = True
Label2.Text = "تعداد " & submited & " ردیف ثبت شد"
' Using bulkCopy As New SqlBulkCopy(cn)
'bulkCopy.DestinationTableName = "salname"
'You can mannualy set the column mapping by the following way.
'bulkCopy.ColumnMappings.Add("date", "date")
'bulkCopy.ColumnMappings.Add("weekday", "weekday")
'bulkCopy.ColumnMappings.Add("event", "event")
'bulkCopy.ColumnMappings.Add("year", "year")
'bulkCopy.ColumnMappings.Add("tatil", "tatil")
'bulkCopy.WriteToServer(dr)
'End Using
End Using
End Using
اون قسمت کامنت هم روش بالک کپیه . خیلی چیز خوبیه اگه مثل من نیاز به کنترل ردیف به ردیف نداشته باشید
پاسخ با نقل قول