vbnet 同時併存 update , insert

參考引用來源:VB.NET Excel匯入Oracle DB前判斷資料是否已經存在，如果已經存在就在時間欄位加上現在時間；如果不存在就直接insert新的一筆資料進去。
--
第一次看到這種的用法! 真是驚訝~~~

 Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim strUrl As String
strUrl = FileUpload1.PostedFile.FileName.ToString()

If (strUrl = "") Then
Label1.Text = "請選擇上傳檔案路徑!"
Else
'--->連接Excel
Dim connString As String
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strUrl & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

Dim ExcelConn As New System.Data.OleDb.OleDbConnection(connString)
ExcelConn.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("select * from [UNIT_PRICE$]", ExcelConn)

Dim dt As New DataTable
da.Fill(dt)

'--->連接Oracle DB
Dim connString1 As String = WebConfigurationManager.ConnectionStrings("xxxxx").ConnectionString
Dim conn As New OracleConnection(connString1)
conn.Open()

Try
Dim cmdUpd As New OracleCommand()
cmdUpd.Connection = conn
cmdUpd.CommandText = "UPDATE WEB_SHIP_UNIT_PRICE SET DISABLE_DATE = SYSDATE WHERE partno = :partno AND DISABLE_DATE IS NULL"
cmdUpd.Prepare()
cmdUpd.Parameters.Add(New OracleParameter("partno", OracleDbType.Varchar2, 30))

Dim cmd As New OracleCommand()
cmd.Connection = conn
cmd.CommandText = "insert into WEB_SHIP_UNIT_PRICE(doc_type, org_id, partno, so_price, crrency, creation_date, created_by, last_update_date, last_updated_by) values " & _
" (:doc_type, :org_id, :partno, :so_price, :crrency, :creation_date, :created_by, :last_update_date, :last_updated_by)"
cmd.Prepare()


cmd.Parameters.Add(New OracleParameter("doc_type", 0))
cmd.Parameters.Add(New OracleParameter("org_id", 1))
cmd.Parameters.Add(New OracleParameter("partno", OracleDbType.Varchar2, 30))
cmd.Parameters.Add(New OracleParameter("so_price", 3))
cmd.Parameters.Add(New OracleParameter("crrency", OracleDbType.Varchar2, 10))
cmd.Parameters.Add(New OracleParameter("creation_date", CType(System.DateTime.Now.ToShortDateString, Date)))
cmd.Parameters.Add(New OracleParameter("created_by", Session("UserID")))
cmd.Parameters.Add(New OracleParameter("last_update_date", CType(System.DateTime.Now.ToShortDateString, Date)))
cmd.Parameters.Add(New OracleParameter("last_updated_by", Session("UserID")))

Dim i As Integer
For i = 0 To (dt.Rows.Count - 1)
cmd.Parameters(0).Value = dt.Rows(i)("doc_type")
cmd.Parameters(1).Value = dt.Rows(i)("org_id")
cmd.Parameters(2).Value = dt.Rows(i)("partno")
cmd.Parameters(3).Value = dt.Rows(i)("so_price")
cmd.Parameters(4).Value = dt.Rows(i)("crrency")
cmdUpd.Parameters(0).Value = dt.Rows(i)("partno")
cmdUpd.ExecuteNonQuery()
cmd.ExecuteNonQuery()
Next

Catch ex As Exception
Response.Write(ex.Message.ToString())
End Try
Label1.Text = "上傳成功!"

End If
End Sub