使用方法:
1、在解决方案的引用中添加以下引用
imports system.Data.OracleClIEnt
2、将DBlink.vb添加到现有项目中
———————————————————华丽的分割线—————————————————————
OptionStrictOff
OptionExplicitOn
importsSystem.Data.OracleClIEnt
ModuleDBlink
#Region"自定义常量及变量"
PrivateOra_ConnectionAsNewOracleConnection
PrivateOra_CommandAsNewOracleCommand
PrivateStartTimeAsDateTime
PrivateEndTimeAsDateTime
PrivateTimeSpanAsNewTimeSpan
PrivateOra_DataAdapterAsOracleDataAdapter
PrivateOra_TransactionAsOracleTransaction
#EndRegion
#Region"打开数据库连接"
'''<summary>
'''打开数据库连接
'''</summary>
'''<remarks></remarks>
PublicSubopen()
Try
Ora_Connection=NewOracleConnection(getConnction())
Ora_Connection.open()
CatcheAsException
ThrowNewSystem.Exception(e.Message)
EndTry
EndSub
#EndRegion
#Region"得到连接字符串"
'''<summary>
'''得到连接字符串
'''</summary>
'''<returns></returns>
'''<remarks></remarks>
PrivateFunctiongetConnction()AsString
DimstrConnectionAsString=""
'设置要访问的数据库、登录名、密码
CallGetCOMMAND()
strConnection="DataSource="Databasename";"_
&"userID=" Username ";password="Password";"_
&"persistsecurityinfo=false"
ReturnstrConnection
EndFunction
#EndRegion
#Region"关闭数据库连接"
'''<summary>
'''关闭数据库连接
'''</summary>
'''<remarks></remarks>
PublicSubDBClose()
Try
IfOra_ConnectionIsnothingThen
Return
EndIf
IfOra_Connection.State=ConnectionState.ClosedThen
Return
EndIf
Ora_Command=NewOracleCommand
Ora_Connection.Close()
CatcheAsException
ThrowNewSystem.Exception(e.Message)
EndTry
EndSub
#EndRegion
#Region"释放资源"
'''<summary>
'''释放资源
'''</summary>
'''<remarks></remarks>
PublicSubDBdispose()
Try
Ora_Connection.dispose()
Ora_Command.dispose()
'Ora_CommandType=nothing
CatcheAsException
ThrowNewSystem.Exception(e.Message)
EndTry
EndSub
#EndRegion
#Region"执行查询"
'''<summary>
'''执行查询
'''</summary>
'''<paramname="strsql">要执行的strsql语句</param>
'''<paramname="outDatatable">返回的Datatable</param>
'''<returns>纪录条数</returns>
'''<remarks></remarks>
PublicFunctionExcutequery(ByValstrsqlAsString,ByRefoutDatatableAsDatatable)AsInteger
DimintCountAsInteger=0
strsql=Trim(strsql)
Ifstrsql.Equals("")Then
Return0
EndIf
Try
CallCheckConnection()
Ora_Command.Connection=Ora_Connection
Ora_Command.CommandText=strsql
'Ora_Command.ExecuteReader()
strsql=GetsqlPara(strsql)
StartTime=DateTime.Now
Ora_DataAdapter=NewOracleDataAdapter(Ora_Command)
Ora_DataAdapter.Fill(outDatatable)
EndTime=DateTime.Now
TimeSpan=(EndTime-StartTime)
intCount=outDatatable.Rows.Count
ReturnintCount
CatcheAsException
ThrowNewSystem.Exception(e.Message)
Finally
'log.log_Add(FCHlog.logType.sql,TimeSpan.Seconds(),GetsqlPara(strsql),intCount)
EndTry
EndFunction
#EndRegion
#Region"检查连接"
'''<summary>
'''检查连接
'''</summary>
'''<remarks></remarks>
PrivateSubCheckConnection()
IfOra_ConnectionIsnothingThen
ThrowNewSystem.Exception("未建立数据库连接,或数据库已关闭~ ")
EndIf
IfOra_Connection.State=ConnectionState.ClosedThen
ThrowNewSystem.Exception("未建立数据库连接,或数据库已关闭~ ")
EndIf
EndSub
#EndRegion
#Region"sql文的参数与值的转化"
'''<summary>
'''sql文的参数与值的转化
'''</summary>
'''<paramname="strsql">sql文</param>
'''<returns></returns>
'''<remarks></remarks>
PrivateFunctionGetsqlPara(ByValstrsqlAsString)AsString
IfOra_Command.Parameters.Count=0ThenReturnstrsql
DimstrValueAsString=""
DimiPosAsInteger=0
DimiTmpAsInteger=0
DimiSetPosAsInteger=0'最后一个 := 的位置
'是否没有参数的sql语句
iPos=strsql.IndexOf("'")
'"'"出现,视为无参数的sql语句
IfiPos>-1Then
Returnstrsql
EndIf
'无":"视为无参数的sql语句
Ifstrsql.IndexOf(":")=-1Then
Returnstrsql
EndIf
'是否存储过程
iTmp=strsql.IndexOf("=>")
IfiTmp>-1Then
iTmp=strsql.IndexOf("(",0)
iPos=strsql.IndexOf(":",iTmp+1)
WhileiPos<>-1AndiTmp<>-1
strsql=strsql.Remove(iTmp+1,iPos-iTmp-1)
iTmp=strsql.IndexOf("=>")
IfiTmp<>-1Then
iPos=strsql.IndexOf(":",iTmp+1)
iTmp=strsql.LastIndexOf(",",iTmp+1)
EndIf
EndWhile
EndIf
'取得sql文的参数
ForiIndexAsInteger=0ToOra_Command.Parameters.Count-1
WithOra_Command.Parameters.Item(iIndex)
SelectCase.OracleType
CaseOracleType.float
CaseOracleType.Double
CaseOracleType.Int16
CaseOracleType.Int32
CaseOracleType.LongRaw
strValue=.Value
CaseOracleType.DateTime
strValue="to_date('"&.Value&"','yyyy/MM/dd')"
CaseElse
strValue="'"&.Value&"'"
EndSelect
'最后一个 := 的位置
IfiIndex=0Then
iPos=strsql.IndexOf(":=")
WhileiPos>-1
iSetPos=iPos
iPos=strsql.IndexOf(":=",iPos+1)
EndWhile
EndIf
iPos=strsql.IndexOf(":",iSetPos+1)
'已经没有参数
IfiPos=-1Then
ExitFor
EndIf
iTmp=strsql.IndexOf(",iPos+1)
IfiTmp=-1Then
iTmp=strsql.IndexOf(")")
EndIf
IfTrim(.Parametername).IndexOf("@#$%^&*(")=-1Then
strsql=strsql.Replace(":"&.Parametername,strValue)
Else
strsql=strsql.Replace(strsql.Substring(iPos,iTmp-iPos),strValue)
EndIf
EndWith
Next
Returnstrsql
EndFunction
#EndRegion
#Region"开始事务"
'''<summary>
'''开始事务
'''</summary>
'''<remarks></remarks>
PublicSubBeginTransaction()
Try
CallCheckConnection()
Ora_Transaction=Ora_Connection.BeginTransaction()
Ora_Command.Transaction=Ora_Transaction
CatcheAsException
ThrowNewSystem.Exception(e.Message)
EndTry
EndSub
#EndRegion
#Region"回滚事务"
'''<summary>
'''回滚事务
'''</summary>
'''<remarks></remarks>
PublicSubRollBack()
Try
IfOra_TransactionIsnothingThenExitSub
Ora_Transaction.Rollback()
CatcheAsException
ThrowNewSystem.Exception(e.Message)
EndTry
EndSub
#EndRegion
#Region"提交事务"
'''<summary>
'''提交事务
'''</summary>
'''<remarks></remarks>
PublicSubCommit()
Try
IfOra_TransactionIsnothingThenExitSub
Ora_Transaction.Commit()
CatcheAsException
ThrowNewSystem.Exception(e.Message)
EndTry
EndSub
#EndRegion
#Region"执行更新,存储过程,参数为:String"
'''<summary>
'''执行更新,存储过程,参数为:String
'''</summary>
'''<paramname="strsql">要执行的strsql语句</param>
'''<returns>影响纪录条数</returns>
'''<remarks></remarks>
PublicFunctionExcuteUpdate(ByValstrsqlAsString)AsInteger
DimintCountAsInteger=0
strsql=Trim(strsql)
Ifstrsql.Equals("")Then
Return-1
EndIf
Try
CallCheckConnection()
Ora_Command.Connection=Ora_Connection
Ora_Command.CommandText=strsql
StartTime=DateTime.Now
intCount=Ora_Command.ExecuteNonquery()
EndTime=DateTime.Now
TimeSpan=(EndTime-StartTime)
ReturnintCount
CatcheAsException
ThrowNewSystem.Exception(e.Message)
Finally
'log.log_Add(FCHlog.logType.sql,intCount)
EndTry
EndFunction
#EndRegion
#Region"执行更新,存储过程,参数为:String,DataSet"
'''<summary>
'''执行更新,存储过程,参数为String,DataSet
'''</summary>
'''<paramname="strsql">要执行的strsql语句</param>
'''<paramname="outDataSet">返回的纪录集</param>
'''<remarks></remarks>
PublicSubExcuteUpdate(ByValstrsqlAsString,ByRefoutDataSetAsDataSet)
DimintCountAsInteger=0
strsql=Trim(strsql)
Ifstrsql.Equals("")Then
Return
EndIf
Try
CallCheckConnection()
Ora_Command.Connection=Ora_Connection
Ora_Command.CommandText=strsql
StartTime=DateTime.Now
Ora_DataAdapter=NewOracleDataAdapter()
Ora_DataAdapter.SelectCommand=Ora_Command
Ora_DataAdapter.Fill(outDataSet)
EndTime=DateTime.Now
TimeSpan=(EndTime-StartTime)
CatcheAsException
ThrowNewSystem.Exception(e.Message)
Finally
'log.log_Add(FCHlog.logType.sql,intCount)
EndTry
EndSub
#EndRegion
#Region"执行更新,存储过程,参数为:String、Datatable"
'''<summary>
'''执行更新,存储过程,参数为:String、Datatable
'''</summary>
'''<paramname="strsql">要执行的strsql语句</param>
'''<paramname="outDatatable">返回的纪录集</param>
'''<remarks></remarks>
PublicSubExcuteUpdate(ByValstrsqlAsString,ByRefoutDatatableAsDatatable)
DimintCountAsInteger=0
strsql=Trim(strsql)
Ifstrsql.Equals("")Then
Return
EndIf
Try
CallCheckConnection()
Ora_Command.Connection=Ora_Connection
Ora_Command.CommandText=strsql
StartTime=DateTime.Now
Ora_DataAdapter=NewOracleDataAdapter()
Ora_DataAdapter.SelectCommand=Ora_Command
Ora_DataAdapter.Fill(outDatatable)
EndTime=DateTime.Now
TimeSpan=(EndTime-StartTime)
CatcheAsException
ThrowNewSystem.Exception(e.Message)
Finally
'log.log_Add(FCHlog.logType.sql,intCount)
EndTry
EndSub
#EndRegion
EndModule
总结以上是内存溢出为你收集整理的VB.NET连接Oracle共通模块全部内容,希望文章能够帮你解决VB.NET连接Oracle共通模块所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)