先看下INETA牛人Stephen Walther的比較測試結論.希望對大家有用
- DataReadear比DataSet快15%
- SqlDataReader比OleDbDataReader快50%
- 用DataReader的ASP風格的表格顯示比DataGrid綁定DataReader快60%
- 用Ordinal的DataReader訪問字段比用名字訪問快15%
- DataGrid中用AutoGenerateColumns=true比用顯式綁定快24%
- 盡量用緩存
測試由于比較局限，所以不一定很準確，但可以做個參考。
Imports
?System.Data.OleDb
Public
?
Class
?ComDataBase
????
Private
?LsConn?
As
?
String
?????????
????
Private
?LoleConn?
As
?OleDbConnection?????????
????
Private
?LoleTrans?
As
?OleDbTransaction????????
????
Public
?
Sub
?
New
()
????????
Dim
?bOracle?
As
?
Boolean
?
=
?
False
????????
Dim
?oIni?
As
?
New
?ComIniFile(
"
..\INI\CCCC001.ini
"
)
????????
If
?(oIni.GetValue(
"
DB
"
,?
"
DBTYPE
"
)?
=
?
"
ORACLE
"
)?
Then
????????????bOracle?
=
?
True
????????
End
?
If
????????
Dim
?sServer?
As
?
String
?
=
?oIni.GetValue(
"
DB
"
,?
"
SERVERNAME
"
)?
????????
Dim
?sDBName?
As
?
String
?
=
?oIni.GetValue(
"
DB
"
,?
"
DBNAME
"
)??????
????????
Dim
?sUser?
As
?
String
?
=
?oIni.GetValue(
"
DB
"
,?
"
USER
"
)??????????
????????
Dim
?sPsw?
As
?
String
?
=
?oIni.GetValue(
"
DB
"
,?
"
PASSWORD
"
)???????
????????
If
?(bOracle)?
Then
????????????LsConn?
=
?
"
Provider=OraOLEDB.Oracle;Data?Source=
"
?
&
?sDBName?_
????????????????????
&
?
"
;User?Id=
"
?
&
?sUser?
&
?
"
;Password=
"
?
&
?sPsw?
&
?
"
;OLEDB.NET=true
"
????????
Else
????????????
LsConn?
=
?
"
Provider=sqloledb;Data?Source=
"
?
&
?sServer?
&
?
"
;Initial?Catalog=
"
?_
?????????????????????
&
?sDBName?
&
?
"
;User?Id=
"
?
&
?sUser?
&
?
"
;Password=
"
?
&
?sPsw?
&
?
"
;
"
????????
End
?
If
????
End?Sub
'db open
????
Public
?
Sub
?Open()
????????
Try
????????
????????????
If
?(
Not
?(LoleConn?
Is
?
Nothing
))?
Then
??????
????????????????
If
?(LoleConn.State?
=
?ConnectionState.Open)?
Then
????????????????????
'
'接続文字列は定義しない文字列
????????????????????
If
?(LoleConn.ConnectionString?
<>
?LsConn)?
Then
????????????????????????LoleConn.Close()
????????????????????????LoleConn.ConnectionString?
=
?LsConn
????????????????????????LoleConn.Open()
????????????????????
End
?
If
????????????????
Else
????????????????????
'
'接続文字列は定義しない文字列
????????????????????
If
?(LoleConn.ConnectionString?
<>
?LsConn)?
Then
????????????????????????LoleConn.ConnectionString?
=
?LsConn
????????????????????
End
?
If
????????????????????LoleConn.Open()
????????????????
End
?
If
????????????
Else
????????????????LoleConn?
=
?
New
?OleDbConnection(LsConn)
????????????????LoleConn.Open()
????????????
End
?
If
????????
Catch
?ex?
As
?Exception
????????????ComLog.SetErrLog(
"
ComDataBase
"
,?
"
Open
"
,?
"
データベースの接続に失敗しました。
"
?
&
?ex.Message)
????????????ComMsgBox.ErrMsg(
"
E-0002
"
)
????????
End
?
Try
????
End?Sub
????
Public
?
Sub
?Close()
????????
Try
????????????
'
'データ?ソースへの接続を閉じする
????????????
If
?(
Not
?(LoleConn?
Is
?
Nothing
))?
Then
????????????????LoleConn.Close()
????????????
End
?
If
????????
Catch
?ex?
As
?Exception
????????????ComLog.SetErrLog(
"
ComDataBase
"
,?
"
Close
"
,?ex.Message)
????????
Finally
????????????
'
'対象を解放する
????????????
If
?(
Not
?(LoleConn?
Is
?
Nothing
))?
Then
????????????????LoleConn.Dispose()
????????????????LoleConn?
=
?
Nothing
????????????
End
?
If
????????
End
?
Try
????
End?Sub
????
Public
?
Sub
?BeginTrans()
????????
'
'トランザクションを開始する
????????LoleTrans?
=
?LoleConn.BeginTransaction()
????
End?Sub
????
Public
?
Sub
?Commit()
????????Execute(
"
Delete?システム管理?where?1=2
"
)
????????
'
'トランザクションの終點をマークする
????????LoleTrans.Commit()
????
End?Sub
????
Public
?
Sub
?RollBack()
????????Execute(
"
Delete?システム管理?where?1=2
"
)
????????
'
'データ変更を消去する
????????LoleTrans.Rollback()
????
End?Sub
????
Public
?
Function
?GetDataSet(
ByVal
?sSQL?
As
?
String
)?
As
?DataSet
????????
Dim
?oleAdapter?
As
?OleDbDataAdapter
????????
Dim
?oDataSet?
As
?DataSet?
=
?
New
?DataSet
????????
Try
????????????
Dim
?oleCommand?
As
?
New
?OleDbCommand(sSQL,?LoleConn)
????????????oleCommand.Transaction?
=
?LoleTrans
????????????oleAdapter?
=
?
New
?OleDbDataAdapter(oleCommand)
????????????oleAdapter.Fill(oDataSet)???????????????
'
'SQL文を検索する
????????
Finally
????????????oleAdapter.Dispose()????????????????????
'
'対象を解放
????????
End
?
Try
????????
Return
?oDataSet
????
End?Function
????
Public
?
Function
?Query(
ByVal
?sSQL?
As
?
String
)?
As
?OleDbDataReader
????????
Dim
?oleCommand?
As
?
New
?OleDbCommand(sSQL,?LoleConn)
????????
Try
????????????oleCommand.Transaction?
=
?LoleTrans
????????????
Return
?oleCommand.ExecuteReader()???????
'
'SQL文を検索する
????????
Finally
????????????oleCommand.Dispose()????????????????????
'
'対象を解放
????????
End
?
Try
????
End?Function
????
Public
?
Function
?Query(
ByVal
?sSQL?
As
?
String
,?
ByRef
?aryOleDbParameter?
As
?ArrayList)?
As
?OleDbDataReader
????????
Dim
?oleCommand?
As
?
New
?OleDbCommand(sSQL,?LoleConn)
????????
Dim
?oleParam?
As
?OleDbParameter
????????
Try
????????????oleCommand.Transaction?
=
?LoleTrans
????????????
For
?
Each
?oleParam?
In
?aryOleDbParameter
????????????????oleCommand.Parameters.Add(oleParam)
????????????
Next
????????????
Return
?oleCommand.ExecuteReader()???????
'
'SQL文を検索する
????????
Finally
????????????oleCommand.Dispose()????????????????????
'
'対象を解放
????????
End
?
Try
????
End?Function
????
Public
?
Function
?Execute(
ByVal
?sSQL?
As
?
String
)?
As
?
Integer
????????
Dim
?oleCommand?
As
?
New
?OleDbCommand(sSQL,?LoleConn)???
'
'OleDbCommandの新インスタンス
????????
Try
????????????oleCommand.Transaction?
=
?LoleTrans
????????????
Return
?oleCommand.ExecuteNonQuery()?????
????????
Finally
????????????oleCommand.Dispose()????????????????????
???????
?????? ?
End
?
Try
????
End?Function
?
????
Public
?
Function
?Execute(
ByVal
?sSQL?
As
?
String
,?
ByRef
?aryOleDbParameter?
As
?ArrayList)?
As
?
Integer
????????
Dim
?oleCommand?
As
?
New
?OleDbCommand(sSQL,?LoleConn)???
????????
Dim
?oleParam?
As
?OleDbParameter
????????
Try
????????????oleCommand.Transaction?
=
?LoleTrans
????????????
For
?
Each
?oleParam?
In
?aryOleDbParameter
????????????????oleCommand.Parameters.Add(oleParam)
????????????
Next
????????????
Return
?oleCommand.ExecuteNonQuery()?????
????????
Finally
????????????oleCommand.Dispose()??
?????? ?
End
?
Try
????
End?Function
基本功能都有了，對于小項目而言，這個類夠用了。
?