目前結論是不能設!!不管您是用Oledb或OracleClient都不行!
最近在寫一個Web Service讓廠商叫用,後端資料庫是ORACLE,用Oledb的方式連到資料庫抓資料。
當初規格是開10秒內要回應,昨天下班前廠商說要測試資料庫連不上的情形,所以我就把DB關掉,但是廠商反映Web Service都會超過10秒才回應不符規格要求,我在ConnectionString的後面加上Connect Timeout=5,測試一下!
嗯!!DataAdapter.SelectCommand.Connection.ConnectionTime如願的被改為5了,DataAdapter.Fill()時也會丟出exception,ex.Message怪怪的,什麼叫"多重步驟 OLE DB 操作發生錯誤。請檢查每一個可用的 OLE DB 狀態值",看不太懂...應該沒問題吧!所以我就下班讓廠商慢慢測囉!
今天快中午,廠商又打電話來,說他測完了叫我可以開DB!...咦!我不是一早就開DB了,追蹤程式才發現是只要Connection.Open()就會出這個exception,拿掉連線字串中的Connect Timeout=5就沒問題!所以就改一下讓廠商繼續測其他項目!
Google一下錯誤訊息,在微軟找到答案http://support.microsoft.com/kb/269495/zh-tw 應該是文中所說的但 ADO 連線字串有問題,後來又查了一堆資料,也作了一堆的測試....最後在MSDN文件中找到確定沒解的答案http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection(vs.71).aspx
Note Unlike the Connection object in the other .NET Framework data providers (SQL Server, OLE DB, and ODBC), OracleConnection does not support a ConnectionTimeout property. Setting a connection timeout using a property or in the connection string has no effect and value returned is always zero. OracleConnection also does not support a Database property or a ChangeDatabase method.
微軟這邊沒支援,我想只好再找看看Oracle有沒有什麼方法可以作限制,試了半天改sqlnet.ora的參數names.request_retries、names.initial_retry_timeout、SQLNET.INBOUND_CONNECT_TIMEOUT、SQLNET.RECV_TIMEOUT、SQLNET.RECV_TIMEOUT都試過了都沒效!
明天上班再試試吧....
============2008/03/06 補充=====
時光飛逝,其實隔天我在MSDN就找到解決的方法了,他是一個VB6的範例,印象中他是用Timer去作計時,不過我現在忘記是用什麼關鍵字去搜尋到的,所以沒有辦法附原始範例的連結囉....
既然我們用的是.Net,所以當然不要用Timer這種方式來作,我的解法是用.Net多執行緒的方式來解,範例如下:
Imports System.Data.OracleClient
Public Class frmOracleClient
Dim StartTime, EndTime As DateTime
Dim Cn As New OracleConnection
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim th As Threading.Thread
th = New Threading.Thread(AddressOf CreateConnection)
Try
Dim i As Integer = 0
th.Start()
'判斷是否另一個執行緒是否執行完畢戓是超過5秒
While th.ThreadState <> Threading.ThreadState.Stopped And i < 5
i = DateDiff(DateInterval.Second, StartTime, Now)
End While
If th.ThreadState = Threading.ThreadState.Running Then
th.Abort()
End If
Catch ex As Exception
End Try
MessageBox.Show("Stop")
End Sub
Private Sub CreateConnection()
Try
Cn.ConnectionString = "Data Source=Your_DB_Name;User ID=Your_ID;Password=Your_Password"
StartTime = Now
Cn.Open()
Catch ex As Exception
EndTime = Now
MessageBox.Show(ex.Message & vbCrLf & _
"StartTime : " & StartTime.ToString & vbCrLf & _
"EndTime : " & EndTime.ToString)
End Try
End Sub
End Class
