ClsConn.docx
《ClsConn.docx》由会员分享,可在线阅读,更多相关《ClsConn.docx(27页珍藏版)》请在冰豆网上搜索。
![ClsConn.docx](https://file1.bdocx.com/fileroot1/2023-4/29/a0661e3f-9751-4b5a-8f7f-3e300c9316c5/a0661e3f-9751-4b5a-8f7f-3e300c9316c51.gif)
ClsConn
ImportsSystem.io
PublicClassclsConn
'SQL数据库连接方式
PublicEnumSQLLinkModeAsInteger
SQL=1
WIN=2
SSPI=3
EndEnum
'数据库连接参数
PublicDBServerAsString'SQLServer数据库服务器或地址
PublicDBNameAsString'SQLServer数据库名称
PublicDBUserAsString'SQLServer数据库登陆名称
PublicDBPassAsString'数据库登陆密码
PublicLinkModeAsSQLLinkMode=SQLLinkMode.SQL'登陆SQLServer的模式,默认为SQLServer认证="sql";Windows认证="win"
'PublicAccFileAsString'Access数据库文件名称
'PublicExcFileAsString'Excel数据库文件名称
'PublicFoxPathAsString'Foxpro数据库文件存储路径
PublicErrMsgAsString=""'错误信息
PublicEnumFoxProVerAsInteger
IV=5
EndEnum
'SQL数据库连接
PublicFunctionConnSQL()AsSqlClient.SqlConnection
ErrMsg=""
DimCNAsNewSqlClient.SqlConnection
DimCNStringAsString
'生成连接字符串
CNString="DataSource="&DBServer&";"&"Initialcatalog="&DBName&";"
SelectCaseLinkMode
CaseSQLLinkMode.SSPI
CNString="Server="&DBServer&";DataBase="&DBName&";IntegratedSecurity=SSPI"
CaseSQLLinkMode.WIN
CNString=CNString&"IntegratedSecurity=SSPI;"
CaseSQLLinkMode.SQL,""
CNString=CNString&_
"IntegratedSecurity=False;"&_
"UserID="&DBUser&";"&_
"Password="&DBPass&";"
CaseElse
ErrMsg="SQLServer认证模式录入错误!
您必须选择Windows认证(WIN)或者SQLServer认证(SQL)。
"
ReturnNothing
ExitFunction
EndSelect
'建立连接
Try
WithCN
CN.Close()
CN.ConnectionString=CNString
CN.Open()
EndWith
ReturnCN
CatcheErrAsSystem.Data.SqlClient.SqlException
ErrMsg=eErr.Message
ReturnNothing
EndTry
EndFunction
'Access数据库连接
PublicFunctionAccessLink()AsOleDb.OleDbConnection
ErrMsg=""
DimCNAsNewOleDb.OleDbConnection
IfDir(DBName)=""Then
ErrMsg="文件["&DBName&"]不存在。
不能建立数据库连接。
"
ReturnNothing
ExitFunction
EndIf
Try
WithCN
.Close()
.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:
DatabasePassword="&DBPass&";DataSource="&DBName
.Open()
EndWith
ReturnCN
CatchexAsException
ErrMsg=ex.Message
ReturnNothing
EndTry
EndFunction
'Excel数据库连接
PublicFunctionExcelLink()AsOleDb.OleDbConnection
ErrMsg=""
DimCNAsNewOleDb.OleDbConnection
IfDir(DBName)=""Then
ErrMsg="文件["&DBName&"]不存在。
不能建立数据库连接。
"
ReturnNothing
ExitFunction
EndIf
Try
WithCN
.Close()
.ConnectionString="provider=Microsoft.Jet.OLEDB.4.0;"&_
"datasource="&DBName&";"&_
"ExtendedProperties=Excel8.0;"
.Open()
EndWith
ReturnCN
CatchexAsException
ErrMsg=ex.Message
EndTry
EndFunction
'DBF数据库连接
PublicFunctionFoxProLink(ByValFoxVerAsFoxProVer)AsOleDb.OleDbConnection
ErrMsg=""
DimCNAsNewOleDb.OleDbConnection
Try
WithCN
.Close()
DimdbVerAsString
SelectCaseFoxVer
'版本判断
CaseFoxProVer.IV
dbVer="dBASEIV"
EndSelect
.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&DBName&";ExtendedProperties="&dbVer&";"
.Open()
EndWith
ReturnCN
CatchexAsException
ErrMsg=ex.Message
ReturnNothing
EndTry
EndFunction
'读取SQL语句的信息,返回DR
PublicFunctionGetDR(ByValSQLStringAsString,ByValCNAsSqlClient.SqlConnection)AsSqlClient.SqlDataReader
ErrMsg=""
DimDRAsSqlClient.SqlDataReader
DimCMDAsNewSqlClient.SqlCommand
Try
CMD.Connection=CN
CMD.CommandType=CommandType.Text
CMD.CommandText=SQLString
DR=CMD.ExecuteReader
CMD.Dispose()
ReturnDR
CatchexAsException
ErrMsg=ex.Message
ReturnNothing
EndTry
EndFunction
'读取SQL语句的信息,返回DR,用于Access数据库,Excel数据库
PublicFunctionGetDROle(ByValSQLStringAsString,ByValCNAsOleDb.OleDbConnection)AsOleDb.OleDbDataReader
ErrMsg=""
DimDRAsOleDb.OleDbDataReader
DimCMDAsNewOleDb.OleDbCommand
Try
CMD.Connection=CN
CMD.CommandType=CommandType.Text
CMD.CommandText=SQLString
DR=CMD.ExecuteReader
CMD.Dispose()
ReturnDR
CatchexAsException
ErrMsg=ex.Message
ReturnNothing
EndTry
EndFunction
'执行SQL语句
PublicFunctionExecSQL(ByValSQLStringAsString)AsBoolean
ErrMsg=""
DimCNAsSqlClient.SqlConnection
DimCMDAsNewSqlClient.SqlCommand
Try
CN=ConnSQL()
CMD.Connection=CN
CMD.CommandType=CommandType.Text
CMD.CommandText=SQLString
CMD.ExecuteNonQuery()
CMD.Dispose()
CN.Close()
ReturnTrue
CatchexAsException
ErrMsg=ex.Message
ReturnFalse
EndTry
EndFunction
'执行SQL语句,用于Access数据库,Excel数据库
PublicFunctionExecSQLOle(ByValSQLStringAsString,ByValCNAsOleDb.OleDbConnection)AsBoolean
ErrMsg=""
DimCMDAsNewOleDb.OleDbCommand
Try
CMD.Connection=CN
CMD.CommandType=CommandType.Text
CMD.CommandText=SQLString
CMD.ExecuteNonQuery()
CMD.Dispose()
ReturnTrue
CatchexAsException
ErrMsg=ex.Message
ReturnFalse
EndTry
EndFunction
'执行SQL语句,返回一个字符值
PublicFunctionGetValue(ByValSQLStringAsString,ByRefsValueAsString)AsBoolean
ErrMsg=""
DimCNAsNewSqlClient.SqlConnection
DimDRAsSqlClient.SqlDataReader
CN=ConnSQL()
DR=GetDR(SQLString,CN)
Try
IfDR.ReadThen
sValue=Trim(DR.Item(0).ToString)
DR.Close()
CN.Close()
ReturnTrue
Else
ErrMsg="没有取到任何值。
"
DR.Close()
CN.Close()
ReturnFalse
EndIf
CatchexAsException
CN.Close()
ErrMsg=ex.Message
ReturnFalse
EndTry
EndFunction
'执行SQL语句,返回一个字符值,用于Access数据库,Excel数据库
PublicFunctionGetValueOle(ByValSQLStringAsString,ByValCNAsOleDb.OleDbConnection)AsString
ErrMsg=""
DimDRAsOleDb.OleDbDataReader
DR=GetDROle(SQLString,CN)
Try
IfDR.ReadThen
DR.Close()
ReturnTrim(DR.Item(0).ToString)
Else
ErrMsg="没有取到任何值。
"
DR.Close()
ReturnNothing
EndIf
CatchexAsException
DR.Close()
ErrMsg=ex.Message
ReturnNothing
EndTry
EndFunction
'执行SQL语句,返回取得的字段值
PublicFunctionExecSQLGetVal(ByValSQLStringAsString,ByRefValues()AsString)AsBoolean
'得到需要得到的字段和数量
ErrMsg=""
DimFields()AsString
DimFieldsCount,iAsInteger
DimFieldsStrAsString
DimBeginstr,EndStrAsInteger
SQLString=SQLString.ToLower.Trim
Beginstr=7
EndStr=SQLString.IndexOf("from")-6
FieldsStr=Mid(SQLString,Beginstr,EndStr).Trim
IfFieldsStr="*"Then
ErrMsg="不能使用[*]作为字段查询"
ReturnFalse
EndIf
Fields=FieldsStr.Split(",")
FieldsCount=Fields.GetUpperBound(0)
ReDimValues(FieldsCount)
DimCNAsNewSqlClient.SqlConnection
DimDRAsSqlClient.SqlDataReader
Try
CN=ConnSQL()
DR=GetDR(SQLString,CN)
IfDR.ReadThen
Fori=0ToFieldsCount
Values(i)=DR.Item(i).ToString
Next
EndIf
DR.Close()
CN.Close()
ReturnTrue
CatchexAsException
ErrMsg=ex.Message
DR.Close()
CN.Close()
ReturnFalse
EndTry
EndFunction
'执行SQL语句,返回取得的字段值。
用于Access数据库,Excel数据库
PublicFunctionExecSQLOleGetVal(ByValSQLStringAsString,ByValCNAsOleDb.OleDbConnection,ByRefValues()AsString)AsBoolean
ErrMsg=""
'得到需要得到的字段和数量
DimFields()AsString
DimFieldsCount,iAsInteger
DimFieldsStrAsString
DimBeginstr,EndStrAsInteger
SQLString=SQLString.ToLower.Trim
Beginstr=7
EndStr=SQLString.IndexOf("from")-6
FieldsStr=Mid(SQLString,Beginstr,EndStr).Trim
IfFieldsStr="*"Then
ErrMsg="不能使用[*]作为字段查询"
ReturnFalse
EndIf
Fields=FieldsStr.Split(",")
FieldsCount=Fields.GetUpperBound(0)
ReDimValues(FieldsCount)
DimDRAsOleDb.OleDbDataReader
Try
DR=GetDROle(SQLString,CN)
IfDR.ReadThen
Fori=0ToFieldsCount
Values(i)=DR.Item(i).ToString
Next
EndIf
DR.Close()
ReturnTrue
CatchexAsException
ErrMsg=ex.Message
DR.Close()
ReturnFalse
EndTry
EndFunction
'执行SQL脚本
PublicFunctionExecSQLFile(ByValSQLFileAsString)AsBoolean
ErrMsg=""
'执行SQL脚本
IfDir(SQLFile)=""Then
'没有该文件
ErrMsg="执行SQL脚本失败。
文件["&SQLFile&"]不存在。
"
ReturnFalse
ExitFunction
EndIf
'文件存在
DimSRAsStreamReader
DimFSAsFileStream
Try
FS=NewFileStream(SQLFile,FileMode.Open)
CatchexAsException
ErrMsg="执行SQL脚本失败。
"&ex.Message
ReturnFalse
ExitFunction
EndTry
SR=NewStreamReader(FS,System.Text.ASCIIEncoding.Default)
Dimi,countAsInteger
DimSQLAsString
DimBiaoZHuAsString
DimExceSQLAsString
Try
DimItemAsObject
Item=SR.ReadLine
WhileNotItemIsNothing
SQL=Item&""
IfSQL.Substring(0,2).ToUpper<>"GO"Then
ExceSQL=ExceSQL&SQL&Chr(13)&Chr(10)
Else
IfTrim(ExceSQL)<>""Then
IfNotExecSQL(ExceSQL)Then
ReturnFalse
EndIf
ExceSQL=""
EndIf
EndIf
Item=SR.ReadLine
EndWhile
'读完文件之后,如果还有语句没有执行,说明没有GO语句
IfExceSQL<>""Then
IfNotExecSQL(ExceSQL)Then
ReturnFalse
EndIf
ExceSQL=""
EndIf
SR.Close()
FS.Close()
ReturnTrue
CatchexAsException
ErrMsg="执行SQL脚本失败。
"&ex.Message
SR.Close()
FS.Close()
ReturnFalse
EndTry
EndFunction
'执行SQL脚本
PublicFunctionExecSQLFileOle(ByValSQLFileAsString,ByValCNAsOleDb.OleDbConnection)AsBoolean
ErrMsg=""
'执行SQL脚本
IfDir(SQLFile)=""Then
'没有该文件
ErrMsg="执行SQL脚本失败。
文件["&SQLFile&"]不存在。
"
ReturnFalse
ExitFunction
EndIf
'文件存在
DimSRAsStreamReader
DimFSAsFileStream
Try
FS=NewFileStream(SQLFile,FileMode.Open)
CatchexAsException
ErrMsg="执行SQL脚本失败。
"&ex.Message
ReturnFalse
ExitFunction
EndTry
SR=NewStreamReader(FS,System.Text.ASCIIEncoding.Default)
Dimi,countAsInteger
DimSQLAsString
DimBiaoZHuAsString
DimExceSQLAsString
Try
DimItemAsObject
Item=SR.ReadLine
WhileNotItemIsNothing
SQL=Item&""
IfSQL.Substring(0,2).ToUpper<>"GO"Then
ExceSQL=ExceSQL&SQL&Chr(13)&Chr(10)
Else
IfTrim(ExceSQL)<>""Then
ExecSQLOle(ExceSQL,CN)
ExceSQL=""
EndIf
EndIf
Item=SR.ReadLine
EndWhile
'读完文件之后,如果还有语句没有执行,说明没有GO语句