VB.NET连接Oracle共通模块
使用方法: 1、在解决方案的引用中添加以下引用 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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |