用于对数据库进行操作的类库经验的总结

//声明:引用或转载时请保留作者姓名.

'****************************************************

'Writer: furenjun 2006.5.10

'****************************************************

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Text

Imports ADOX

Imports System.Data.OleDb

Imports ADODB

Public Class C_Mrfu_DBHR

'Private SqlCon As SqlConnection = New _

'SqlConnection("data source=FURENJUN;initial catalog=CarDriManage;user id=sa;password=sa")

'定义一个数据连接对象，并初始化

'Dim olecon2 As OleDbConnection = New OleDbConnection()

'Dim str1 As String = "d:\mrfu\jsdb.mdb"

'olecon2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & str1 & "'"

'''Build a connection string

'Dim connectionString As String = ""

' connectionString = "Provider=SQLOLEDB;"

' connectionString += "Server=FURENJUN;Database=pubs;"

' connectionString += "User ID=sa;Password=sa"

'SqlCon在启动窗体中对其实例化，并为其赋初值

'''Create and open a connection using the connection string

' adoOleDbConnection = New OleDbConnection(connectionString)

'''Build a SQL statement to query the datasource

'Dim sqlString As String = ""

' sqlString = "Select * From authors where state='TN' "

'''Retrieve the data using the SQL statement and existing connection

' adoOleDbDataAdapter = New OleDbDataAdapter(sqlString, adoOleDbConnection)

'''Create a instance of a Dataset

' dataSet = New DataSet()

'''Fill the dataset with the data retrieved. The name of the table

'''in the dataset must be the same as the table name in the report.

' adoOleDbDataAdapter.Fill(dataSet, "authors")

Private myDataSet As DataSet

'定义一个数据集对象

Private mySqlDataAdapter As SqlDataAdapter

'定义一个数据适配器对象

Private myOleDBDataAdapter As OleDbDataAdapter

'定义一个数据适配器对象

'Private mySqlCon As SqlConnection

'定义一个数据库连接对象

Private SqlCmd As SqlCommand

'定义一个sql数据命令对象

Dim SqlServerName_Str_Pub As String '服务器名

Dim SqlUserName_Str_Pub As String '用户登录名

Dim SqlUserPassWord_Str_Pub As String '密码

Dim SqlInitDB_Str_Pub As String '默认数据库

Dim SqlLoginTimeout_Str_Pub As String '登录超时

Dim SqlConnectionTimeout_Str_Pub As String '连接超时

Dim m_sRunMsg As String = ""

Public Property GsRunMsg() As String

Get

' The Get property procedure is called when the value

' of a property is retrieved.

Return m_sRunMsg

End Get

Set(ByVal Value As String)

' The Set property procedure is called when the value

' of a property is modified.

' The value to be assigned is passed in the argument to Set.

m_sRunMsg = Value

End Set

End Property

'*************************************************************************************************************************************************'

'1.数据库连接操作(SqlCon and OleDBCon )

'*************************************************************************************************************************************************'

Private Function INI_Fun_Pub(ByVal SectionName As String, ByVal KeyName As String, ByVal DefaultStr As String, ByVal FileName As String) As String

Dim FileLine_Str_Dim As String

Dim SectionName_Str_Dim As String

Dim FileLineNum_Str_Dim As Double

Dim i As Integer

Dim KeyName_Str_Dim As String

Dim Information_Str_Dim As String

Dim FileExist_Str_Dim As String

Try

''''''''''''''''''''''''''''''查询文件是否存在

FileExist_Str_Dim = Dir(FileName)

If FileExist_Str_Dim = "" Then

INI_Fun_Pub = DefaultStr

Else

INI_Fun_Pub = DefaultStr

FileOpen(1, FileName, OpenMode.Input)

While Not EOF(1)

FileLine_Str_Dim = Trim(LineInput(1))

If FileLine_Str_Dim <> "" Then

'''''''''''''''''''''''''''''''判断是不是块索引部分

If Microsoft.VisualBasic.Left(Trim(FileLine_Str_Dim), 1) = "[" Then

EstimateSect:

SectionName_Str_Dim = Trim(Mid(Trim(FileLine_Str_Dim), 2, Len(Trim(FileLine_Str_Dim)) - 2))

If Trim(SectionName_Str_Dim.ToUpper()) = Trim(SectionName.ToUpper()) Then

While Not EOF(1)

FileLine_Str_Dim = Trim(LineInput(1))

''''''''''''''''''''''''''''进入块索引，引入下一句

If Microsoft.VisualBasic.Left(Trim(FileLine_Str_Dim), 1) = "[" Then

'''''''''''''''''''''''如果还是块索引

GoTo EstimateSect

Else

FileLineNum_Str_Dim = Len(Trim(FileLine_Str_Dim))

For i = 1 To FileLineNum_Str_Dim

KeyName_Str_Dim = Trim(Microsoft.VisualBasic.Left(FileLine_Str_Dim, i))

If Microsoft.VisualBasic.Right(Trim(KeyName_Str_Dim), 1) = "=" Then

KeyName_Str_Dim = Trim(Microsoft.VisualBasic.Left(Trim(KeyName_Str_Dim), Len(Trim(KeyName_Str_Dim)) - 1))

Information_Str_Dim = Trim(Microsoft.VisualBasic.Right(Trim(FileLine_Str_Dim), Len(Trim(FileLine_Str_Dim)) - i))

If Trim(KeyName.ToUpper()) = Trim(KeyName_Str_Dim.ToUpper()) Then

INI_Fun_Pub = Trim(Information_Str_Dim)

FileClose(1)

Exit Function

Else

Exit For

End If

End While

End If

End While

FileClose(1)

End If

Catch

m_sRunMsg = (Err.Description)

End Try

End Function

Private Sub ReadIniFile()

'*=====读INI=====*

Try

Dim MyPath As String

MyPath = CurDir()

'MsgBox(MyPath)

Dim str1, str2, str3, str4, str5, str6 As String

SqlServerName_Str_Pub = INI_Fun_Pub("SQLSERVER", "MrFuServerName", "TestServer", MyPath & "\LinkMsSql.INI")

SqlUserName_Str_Pub = INI_Fun_Pub("SQLSERVER", "MrFuUserName", "sa", MyPath & "\LinkMsSql.ini")

SqlUserPassWord_Str_Pub = INI_Fun_Pub("SQLSERVER", "MrFuUserPassword", "athena", MyPath & "\LinkMsSql.ini")

SqlInitDB_Str_Pub = INI_Fun_Pub("SQLSERVER", "MrFuInitDB", "AttSysDB", MyPath & "\LinkMsSql.ini")

SqlLoginTimeout_Str_Pub = INI_Fun_Pub("SQLSERVER", "LoginTimeout", "20", MyPath & "\LinkMsSql.ini")

SqlConnectionTimeout_Str_Pub = INI_Fun_Pub("SQLSERVER", "ConnectionTimeout", "30", MyPath & "\LinkMsSql.ini")

'str1 = INI_Fun_Pub("SQLSERVER", "ServerName", "TestServer", MyPath & "\LinkMsSql.INI")

'str2 = INI_Fun_Pub("SQLSERVER", "UserName", "sa", MyPath & "\LinkMsSql.ini")

'str3 = INI_Fun_Pub("SQLSERVER", "UserPassword", "athena", MyPath & "\LinkMsSql.ini")

'str4 = INI_Fun_Pub("SQLSERVER", "InitDB", "AttSysDB", MyPath & "\LinkMsSql.ini")

'str5 = INI_Fun_Pub("SQLSERVER", "LoginTimeout", "20", MyPath & "\LinkMsSql.ini")

'str6 = INI_Fun_Pub("SQLSERVER", "ConnectionTimeout", "30", MyPath & "\LinkMsSql.ini")

'MsgBox(str1 + str2 + str3 + str4 + str5 + str6)

Catch

m_sRunMsg = ("ReadIniFile 错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString)

End Try

End Sub

Public Function SQL_Con() As SqlClient.SqlConnection

Try

ReadIniFile()

' Dim objSqlConnection As SqlConnection = New _

' SqlConnection("data source=furenjun;initial catalog=CarDriManage;user id=sa;password=sa")

Dim SqlCnn As SqlConnection = New SqlConnection("data source = " & SqlServerName_Str_Pub _

& ";user id=" & SqlUserName_Str_Pub _

& ";password=" & SqlUserPassWord_Str_Pub _

& ";initial catalog = " & SqlInitDB_Str_Pub)

SQL_Con = SqlCnn

Catch

m_sRunMsg = ("SQL_Con 错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString)

End Try

End Function

Public Function MyOleCon() As OleDb.OleDbConnection

Dim adoOleDbConnection As OleDbConnection

Try

ReadIniFile()

Dim connectionString As String = ""

connectionString = "Provider=SQLOLEDB;Server =" & Trim(SqlServerName_Str_Pub) & ";Database=" & Trim(SqlInitDB_Str_Pub) & ";User ID=" & Trim(SqlUserName_Str_Pub) & ";Password=" & Trim(SqlUserPassWord_Str_Pub)

'最好不要采用单行写再相加

''Build a connection string

'Dim connectionString As String = ""

'connectionString = "Provider=SQLOLEDB;"

'connectionString += "Server=FURENJUN;Database=CarDriManage;"

'connectionString += "User ID=sa;Password=sa"

adoOleDbConnection = New OleDbConnection(connectionString)

Catch

m_sRunMsg = (" MyOleCon 错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString)

End Try

Return adoOleDbConnection

End Function

Public Function MyOleConStr() As String

Dim connectionString As String = ""

Try

ReadIniFile()

connectionString = "Provider=SQLOLEDB; Server=" & Trim(SqlServerName_Str_Pub) & "; Database=" & Trim(SqlInitDB_Str_Pub) & "; User ID=" & Trim(SqlUserName_Str_Pub) & "; Password=" & Trim(SqlUserPassWord_Str_Pub)

Catch

m_sRunMsg = (" MyOleConStr 错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString)

End Try

Return connectionString

End Function

Public Function DataTransition_ADODBCon() As ADODB.Connection

Dim conn As New ADODB.Connection

Try

ReadIniFile()

conn.ConnectionString = "Provider=SQLOLEDB;Data Source='" & Trim(SqlServerName_Str_Pub) & " ';Initial Catalog='" & Trim(SqlInitDB_Str_Pub) & "';Integrated Security=SSPI;" & " ," & " '" & Trim(SqlUserName_Str_Pub) & "' " & " , " & " '" & Trim(SqlUserPassWord_Str_Pub) & "'" & ", " & " -1"

Catch

m_sRunMsg = (" MyOleConStr 错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString)

End Try

DataTransition_ADODBCon = conn

End Function

Public Function SqlConStr() As String

Dim sqlconstring As String = ""

Try

ReadIniFile()

sqlconstring = "data source=" & Trim(SqlServerName_Str_Pub) & "; initial catalog=" & Trim(SqlInitDB_Str_Pub) & "; persist security info=true; user id=" & Trim(SqlUserName_Str_Pub) & "; Password=" & Trim(SqlUserPassWord_Str_Pub) & "; workstation id=" & Trim(SqlServerName_Str_Pub) & "; packet size=4096"

Catch

m_sRunMsg = ("错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString)

End Try

Return sqlconstring

End Function

Public Function SqlConStr2() As String

Dim sqlconstring As String = ""

Try

ReadIniFile()

sqlconstring = Trim(SqlServerName_Str_Pub) & "\" & Trim(SqlInitDB_Str_Pub) & "\" & Trim(SqlUserName_Str_Pub) & "\" & Trim(SqlUserPassWord_Str_Pub)

Catch

m_sRunMsg = ("SqlConStr 错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString)

End Try

Return sqlconstring

End Function

'*************************************************************************************************************************************************'

'1.2.连接ACCESS数据库

'*************************************************************************************************************************************************'

Public Function Access_OleDBCon(ByVal DBPath As String) As OleDbConnection

Dim OleCon As OleDbConnection = New OleDbConnection

'Dim DBPath As String = "d:\mrfu\DriverManager.mdb"

OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & DBPath & "'"

Return OleCon

End Function

'*************************************************************************************************************************************************'

'对ACCESS数据库进行操作

'*************************************************************************************************************************************************'

Public Function Access_ADODBConNeedPassword(ByVal DBPathAndDatabaseName As String, ByVal UserID As String, ByVal Password As String, ByVal SqlStr As String) As String

Dim FlagStr As String = "0"

Dim ConnectionString As String = ""

ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _

"DBQ=" & DBPathAndDatabaseName & ";DefaultDir=;" & _

"UID=" & UserID & ";" & _

"PWD=" & Password & ";"

Dim Adocon As ADODB.Connection

Try

Adocon = New ADODB.Connection

Adocon.ConnectionString = ConnectionString

Adocon.ConnectionTimeout = 120

Adocon.CommandTimeout = 160

Adocon.Open()

Adocon.Execute(SqlStr)

Adocon.Close()

Adocon = Nothing

FlagStr = "1"

Catch

If Adocon.State = ConnectionState.Open Then

Adocon.Close()

End If

FlagStr = "0"

m_sRunMsg = (Err.Description.ToString) ', MsgBoxStyle.Exclamation, "错误提示")

End Try

Return FlagStr

End Function

'*************************************************************************************************************************************************'

'访问ACCESS数据库,并返回小批量数据

'*************************************************************************************************************************************************'

Public Function Access_GetDataReturnArrayList(ByVal DBPathAndDatabaseName As String, ByVal UserID As String, ByVal Password As String, ByVal SqlStr As String, ByVal TableName As String, ByVal FiledsName As String) As ArrayList

Dim TempArrayList As ArrayList

TempArrayList = Nothing

Dim rs As ADODB.Recordset

Dim ConnectionString As String = ""

ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _

"DBQ=" & DBPathAndDatabaseName & ";DefaultDir=;" & _

"UID=" & UserID & ";" & _

"PWD=" & Password & ";"

Dim Adocon As ADODB.Connection

Try

Adocon = New ADODB.Connection

Adocon.ConnectionString = ConnectionString

Adocon.ConnectionTimeout = 120

Adocon.CommandTimeout = 160

Adocon.Open()

rs = New ADODB.Recordset

rs.Open(SqlStr, Adocon, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, CInt(ADODB.CommandTypeEnum.adCmdText))

TempArrayList = New ArrayList

'如果记录集为空,输出一个错误信息

If (rs.BOF Or rs.EOF) Then

Adocon.Close()

Adocon = Nothing

rs = Nothing

m_sRunMsg = ("没有找到任何记录,请检查你的" & TableName & " 表 ") ', MsgBoxStyle.Information, "系统消息")

Return TempArrayList

End If

'循环存入数据

While (Not rs.EOF)

TempArrayList.Add(rs.Fields(FiledsName).Value.ToString())

rs.MoveNext()

End While

Adocon.Close()

Adocon = Nothing

rs = Nothing

Catch

If Adocon.State = ConnectionState.Open Then

Adocon.Close()

End If

m_sRunMsg = (Err.Description.ToString) ', MsgBoxStyle.Exclamation, "错误提示")

End Try

Return TempArrayList

End Function

'*************************************************************************************************************************************************'

'访问ACCESS数据库,并返回数据集

'*************************************************************************************************************************************************'

Public Function GetDataFromAccess(ByVal DBPathAndDatabaseName As String, ByVal UserID As String, ByVal Password As String, ByVal SqlStr As String, ByVal TableName As String) As DataSet

Dim custDA As OleDbDataAdapter = New OleDbDataAdapter

Dim custDS As DataSet = New DataSet

Dim rs As ADODB.Recordset

Dim ConnectionString As String = ""

ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _

"DBQ=" & DBPathAndDatabaseName & ";DefaultDir=;" & _

"UID=" & UserID & ";" & _

"PWD=" & Password & ";"

Dim Adocon As ADODB.Connection

Try

Adocon = New ADODB.Connection

Adocon.ConnectionString = ConnectionString

Adocon.ConnectionTimeout = 120

Adocon.CommandTimeout = 160

Adocon.Open()

rs = New ADODB.Recordset

rs.Open(SqlStr, Adocon, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, CInt(ADODB.CommandTypeEnum.adCmdText))

'如果记录集为空,输出一个错误信息

If (rs.BOF Or rs.EOF) Then

Adocon.Close()

Adocon = Nothing

rs = Nothing

m_sRunMsg = ("没有找到任何记录,请检查你的" & TableName & " 表 ") ', MsgBoxStyle.Information, "系统消息")

Return custDS

End If

'存入dataset数据

custDA.Fill(custDS, rs, "Customers")

Adocon.Close()

Adocon = Nothing

rs = Nothing

Catch

If Adocon.State = ConnectionState.Open Then

Adocon.Close()

End If

m_sRunMsg = (Err.Description.ToString) ', MsgBoxStyle.Exclamation, "错误提示")

End Try

Return custDS

End Function

'***************************************************************

'取出Access数据库中的所有表名

'***************************************************************

Public Function GetTableNameFromAccess(ByVal AccessDbPathAndFileName As String) As ArrayList

Dim TablesArray As New ArrayList

Dim cnn1 As New ADODB.Connection

Dim cmd1 As New ADODB.Command

Dim tbl1 As New Table

Try

cnn1.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & AccessDbPathAndFileName)

Dim cat1 As New Catalog

cat1.ActiveConnection = cnn1

Dim i As Integer = cat1.Tables.Count '统计access库中表的个数

' MsgBox("共有 " & CStr(i) & "张表")

'Dim TableName1(i + 1) As String

Dim j As Integer = 0

If i > 0 Then

For j = 0 To i - 1

' TableName1(j) = (cat1.Tables(j).Name.ToString)

' MsgBox("表名:" & TableName1(j))

TablesArray.Add(cat1.Tables(j).Name.ToString())

Next j

End If

cnn1.Close()

Catch ex As Exception

If (cnn1.State = ConnectionState.Open) Then

cnn1.Close()

End If

End Try

Return TablesArray

End Function

'***********************************************************************

'在Access表执行Sql语句

'***********************************************************************

Public Function ExecuteSqlstrInAccess(ByVal AccessDbPathAndFileName As String, ByVal SqlStr As String) As Boolean

Dim bResult As Boolean = True

Dim cnn1 As New ADODB.Connection

Dim cmd1 As New ADODB.Command

Dim tbl1 As New Table

Try

cnn1.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & AccessDbPathAndFileName)

cmd1 = New ADODB.Command

With cmd1

.ActiveConnection = cnn1

.CommandText = SqlStr '执行 select ,insert,update,delete 等sql语句.

.CommandType = ADODB.CommandTypeEnum.adCmdText

.Execute()

End With

cnn1.Close()

Catch ex As Exception

bResult = False

If (cnn1.State = ConnectionState.Open) Then

cnn1.Close()

End If

m_sRunMsg = (Err.Description.ToString) ', MsgBoxStyle.Information, "ExecuteSqlstrInAccess错误提示")

End Try

Return bResult

End Function

'*************************************************************************************************************************************************'

'2.调用存储过程操纵数据库

'*************************************************************************************************************************************************'

Public Function P_GetDataFromDB(ByVal Proc1 As String, ByVal mySqlCon As SqlConnection) As DataSet

myDataSet = New DataSet

myDataSet.Clear()

'实例化一个数据集对象

Dim scmd As New SqlCommand

scmd = New SqlCommand(Proc1, mySqlCon)

scmd.CommandType = CommandType.StoredProcedure

Dim mySqlDataadapter1 As New SqlDataAdapter

mySqlDataadapter1 = New SqlDataAdapter(scmd)

Try

mySqlCon.Open()

mySqlDataadapter1.Fill(myDataSet)

Catch

myDataSet = Nothing

m_sRunMsg = ("P_GetDataFromDB 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

Finally

If mySqlCon.State = ConnectionState.Open Then

mySqlCon.Close()

End If

End Try

Return myDataSet

End Function

'使用存储过程和数据阅读器获取数据

Public Function P_ExecAddPra(ByVal ProStr1 As String, ByVal Pram1 As String, ByVal Pram2 As String, ByVal SqlCon1 As SqlConnection) As SqlDataReader

Dim myReader As SqlClient.SqlDataReader

Dim scmd As New SqlCommand(ProStr1, SqlCon1)

scmd.CommandType = CommandType.StoredProcedure

With scmd.Parameters

'.Add(New SqlParameter("@no", SqlDbType.VarChar)).Value = "NO.1005"

'.Add(New SqlParameter("@cb", SqlDbType.VarChar)).Value = "CB.1006"

'或者采用下面的方法也可

.Add(Pram2, SqlDbType.VarChar, 50)

.Item(Pram2).Value = Pram1

End With

Try

myReader = Nothing

SqlCon1.Open()

myReader = scmd.ExecuteReader

Catch expsql As SqlException

'MsgBox(expsql.ToString & "错误号 :" & Err.Number & "错误描述 :" & Err.Description, MessageBoxButtons.OK, MessageBoxIcon.Error)

myReader.Close()

m_sRunMsg = ("P_ExecAddPra 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

Finally

If SqlCon1.State = ConnectionState.Open Then

SqlCon1.Close() '关闭连接

End If

End Try

Return myReader

End Function

Public Function P_ExecAndAddPra2(ByVal ProStr1 As String, ByVal praWorth As String, ByVal PraVar As String, ByVal SqlCon1 As SqlConnection) As String

Dim scmd As New SqlCommand(ProStr1, SqlCon1)

Dim resultStr As String = ""

scmd.CommandType = CommandType.StoredProcedure

With scmd.Parameters

'.Add(New SqlParameter("@no", SqlDbType.VarChar)).Value = "NO.1005"

'.Add(New SqlParameter("@cb", SqlDbType.VarChar)).Value = "CB.1006"

'或者采用下面的方法也可

.Add(PraVar, SqlDbType.VarChar, 50)

.Item(PraVar).Value = praWorth

End With

Try

SqlCon1.Open()

resultStr = scmd.ExecuteScalar

Catch expsql As SqlException

'MessageBox.Show(expsql.ToString, MessageBoxButtons.OK, MessageBoxIcon.Error)

m_sRunMsg = ("P_ExecAndAddPra2 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

Finally

If SqlCon1.State = ConnectionState.Open Then

SqlCon1.Close()

End If

End Try

Return resultStr

End Function

Public Function P_ExecAndAddPra4(ByVal ProName As String, ByVal PraVar1 As String, ByVal praValue1 As String, ByVal PraVar2 As String, ByVal praValue2 As String, ByVal SqlCon1 As SqlConnection) As String

Dim scmd As New SqlCommand(ProName, SqlCon1)

Dim resultStr As String = "-1"

scmd.CommandType = CommandType.StoredProcedure

With scmd.Parameters

'.Add(New SqlParameter("@no", SqlDbType.VarChar)).Value = "NO.1005"

'.Add(New SqlParameter("@cb", SqlDbType.VarChar)).Value = "CB.1006"

.Add(New SqlParameter(PraVar1, SqlDbType.VarChar)).Value = praValue1

.Add(New SqlParameter(PraVar2, SqlDbType.VarChar)).Value = praValue2

'或者采用下面的方法也可

'.Add(PraVar1, SqlDbType.VarChar, 50)

'.Item(PraVar1).Value = praWorth1

End With

Try

SqlCon1.Open()

resultStr = Convert.ToString(scmd.ExecuteScalar)

Catch expsql As SqlException

'MessageBox.Show(expsql.ToString, MessageBoxButtons.OK, MessageBoxIcon.Error)

resultStr = "-1"

m_sRunMsg = ("P_ExecAndAddPra2 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

Finally

If SqlCon1.State = ConnectionState.Open Then

SqlCon1.Close()

End If

End Try

Return resultStr

End Function

Public Function P_ExecAndAddPra3(ByVal ProStr1 As String, ByVal PraWorth1 As String, ByVal PraVar1 As String, ByVal PraWorth2 As String, ByVal PraVar2 As String, ByVal SqlCon1 As SqlConnection) As Integer

Dim n As Integer = 0

Dim scmd As New SqlCommand(ProStr1, SqlCon1)

Dim resultStr As String = ""

scmd.CommandType = CommandType.StoredProcedure

With scmd.Parameters

'.Add(New SqlParameter("@no", SqlDbType.VarChar)).Value = "NO.1005"

'.Add(New SqlParameter("@cb", SqlDbType.VarChar)).Value = "CB.1006"

'或者采用下面的方法也可

.Add(PraVar1, SqlDbType.VarChar, 50)

.Item(PraVar1).Value = PraWorth1

.Add(PraVar2, SqlDbType.VarChar, 50)

.Item(PraVar2).Value = PraWorth2

End With

Try

SqlCon1.Open()

resultStr = scmd.ExecuteNonQuery

n = 1

Catch expsql As SqlException

m_sRunMsg = ("P_ExecAndAddPra3 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

n = 0

Finally

If SqlCon1.State = ConnectionState.Open Then

SqlCon1.Close() '关闭连接

End If

End Try

Return n

End Function

Public Function P_ExecAndNoPra(ByVal ProStr1 As String, ByVal SqlCon1 As SqlConnection) As Integer

Dim n As Integer = 0

Dim scmd As New SqlCommand(ProStr1, SqlCon1)

Dim resultStr As String = ""

scmd.CommandType = CommandType.StoredProcedure

Try

SqlCon1.Open()

resultStr = scmd.ExecuteNonQuery

n = 1

Catch expsql As SqlException

m_sRunMsg = ("P_ExecAndNoPra 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

n = 0

Finally

If SqlCon1.State = ConnectionState.Open Then

SqlCon1.Close() '关闭连接

End If

End Try

Return n

End Function

'*************************************************************************************************************************************************'

'3.创建ACCESS数据库

'*************************************************************************************************************************************************'

Public Function CreateAccessDB(ByVal NewDBPathName As String) As Integer

Dim i As Integer = 1

Dim cat As Catalog = New Catalog

Try

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & NewDBPathName & _

"Jet OLEDB:Engine Type=5")

'Console.WriteLine("Database Created Successfully")

Catch

i = 0

m_sRunMsg = ("CreateAccessDB 错误号 : " & Err.Number.ToString & "　错误描述：" & Err.Description.ToString)

End Try

cat = Nothing

Return i

End Function

'*************************************************************************************************************************************************'

'4.获取或更新数据库中的数据

'*************************************************************************************************************************************************'

Public Function GetMaxNumFrom_SqlDB(ByVal SqlStr As String, ByVal mySqlCon As SqlConnection, ByVal TableName As String) As String

'获取当前所有纪录的最大纪录号

Dim str2 As String = "1000"

Dim myDataset2 As DataSet

myDataset2 = New DataSet

mySqlCon.Open()

Try

myDataset2.Clear()

myDataset2 = GetDataFrom_SqlDB(SqlStr, mySqlCon, TableName)

str2 = myDataset2.Tables(0).Rows(0).Item(0)

Dim m As Integer = 0

m = myDataset2.Tables(0).Rows.Count()

If m = 0 Then

str2 = "0001"

m_sRunMsg = ("没有找到最大值，系统自动生成初始编号'0001'.") ', MsgBoxStyle.Information, "提示")

Else

Dim n As Integer = CInt(str2) + 1

str2 = CStr(n)

' MsgBox("最大值为:" & str2)

End If

Catch exp As Exception

myDataSet = Nothing

m_sRunMsg = (" GetMaxNumFrom_SqlDB 错误号：" & Err.Number & "错误描述：" & Err.Description)

Finally

If mySqlCon.State = ConnectionState.Open Then

mySqlCon.Close() '关闭连接

End If

End Try

Return str2

End Function

Public Function GetDataFrom_SqlDB(ByVal SqlStr As String, ByVal mySqlCon As SqlConnection, ByVal TableName As String) As DataSet

'注意此处mysqlcon要采用动态连接

'mySqlCon = New SqlConnection()

'SqlCnn1 = New CTYSqlCon_CLB.Class1()

'mySqlCon = SqlCnn1.MSSQLLink

myDataSet = New DataSet

myDataSet.Clear()

'实例化一个数据集对象

Try

mySqlCon.Open()

'打开数据库连接

mySqlDataAdapter = New SqlDataAdapter(SqlStr, mySqlCon)

'将数据库的数据映射到数据适配器

mySqlDataAdapter.Fill(myDataSet, TableName)

'填充数据集的数据

Catch

myDataSet = Nothing

m_sRunMsg = (" GetDataFrom_SqlDB 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

Finally

If mySqlCon.State = ConnectionState.Open Then

mySqlCon.Close()

'关闭数据连接，这一点很重要

End If

End Try

Return myDataSet

End Function

'*****************************************************

'获取sql数据库中用户所建表的表名,id号

'*****************************************************

Public Function GetUserTableNameFrom_SqlDB(ByVal mySqlCon As SqlConnection) As DataSet

'注意此处mysqlcon要采用动态连接

'mySqlCon = New SqlConnection()

'SqlCnn1 = New CTYSqlCon_CLB.Class1()

'mySqlCon = SqlCnn1.MSSQLLink

Dim SqlStr As String = "select name,id from sysobjects where xtype='U'"

Dim TableName As String = "sysobjects"

myDataSet = New DataSet

myDataSet.Clear()

'实例化一个数据集对象

Try

mySqlCon.Open()

'打开数据库连接

mySqlDataAdapter = New SqlDataAdapter(SqlStr, mySqlCon)

'将数据库的数据映射到数据适配器

mySqlDataAdapter.Fill(myDataSet, TableName)

'填充数据集的数据

Catch

myDataSet = Nothing

m_sRunMsg = (" GetDataFrom_SqlDB 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

Finally

If mySqlCon.State = ConnectionState.Open Then

mySqlCon.Close()

'关闭数据连接，这一点很重要

End If

End Try

Return myDataSet

End Function

'**************************************************

'更新Sql数据库，返回受影响的纪录条数

'**************************************************

Public Function Update_Sql_Data(ByVal SqlStr As String, ByVal mySqlCon As SqlConnection) As Double

'注意此处mysqlcon要采用动态连接

Dim objCommand As SqlCommand = New SqlCommand

'mySqlCon = New SqlConnection()

'SqlCnn1 = New CTYSqlCon_CLB.Class1()

'mySqlCon = SqlCnn1.MSSQLLink

'此处应用到具体项目中的全局变量，到程序中先行实例化．

Dim i As Double = 0

Try

mySqlCon.Open()

objCommand.Connection = mySqlCon

objCommand.CommandText = SqlStr

objCommand.CommandType = CommandType.Text

i = objCommand.ExecuteNonQuery()

Catch

i = 0

m_sRunMsg = ("Update_Sql_Data 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

Finally

If mySqlCon.State = ConnectionState.Open Then

mySqlCon.Close() '关闭连接

End If

End Try

Return i

End Function

'**************************************************

'更新Sql数据库，返回受影响的纪录条数

'**************************************************

Public Function Get_Sql_ExecuteScalar(ByVal SqlStr As String, ByVal mySqlCon As SqlConnection) As Double

'注意此处mysqlcon要采用动态连接

Dim objCommand As SqlCommand = New SqlCommand

'mySqlCon = New SqlConnection()

'SqlCnn1 = New CTYSqlCon_CLB.Class1()

'mySqlCon = SqlCnn1.MSSQLLink

'此处应用到具体项目中的全局变量，到程序中先行实例化．

Dim i As Double = 0

Try

mySqlCon.Open()

objCommand.Connection = mySqlCon

objCommand.CommandText = SqlStr

objCommand.CommandType = CommandType.Text

i = Convert.ToInt64(objCommand.ExecuteScalar())

Catch

i = 0

m_sRunMsg = ("Update_Sql_Data 错误号：" & Err.Number.ToString & "错误描述：" & Err.Description.ToString)

Finally

If mySqlCon.State = ConnectionState.Open Then

mySqlCon.Close() '关闭连接

End If

End Try

Return i

End Function

Public Function GetDataFrom_OleDB(ByVal SqlStr As String, ByVal myOleDBCon As OleDbConnection, ByVal TableName As String) As DataSet

'注意此处mysqlcon要采用动态连接

myDataSet = New DataSet

myDataSet.Clear()

'实例化一个数据集对象

Try

myOleDBCon.Open()

'打开数据库连接

myOleDBDataAdapter = New OleDbDataAdapter(SqlStr, myOleDBCon)

'将数据库的数据映射到数据适配器

myOleDBDataAdapter.Fill(myDataSet, TableName)

Catch

myDataSet = Nothing

m_sRunMsg = (" GetDataFrom_OleDB 错误号：" & Err.Number & "错误描述：" & Err.Description)

Finally

If myOleDBCon.State = ConnectionState.Open Then

myOleDBCon.Close() '关闭连接

End If

End Try

Return myDataSet

End Function

'*********************************************************

'更新（UPDATE、INSERT 和 DELETE）采用ole方式联接的数据库,返回受影响的纪录条数

'*********************************************************

Public Function UpDate_OleDB(ByVal SqlStr As String, ByVal OleDBCon As OleDbConnection) As Integer

'Dim SqlConStr As String = "provider=SQLOLEDB.1;Data Source=" & _

'"localhost;uid=sa;pwd=sa;Initial Catalog=CarDriManage;"

Dim i As Integer = 0

Try

'Dim OleDBCon As New OleDbConnection(SqlConStr)

OleDBCon.Open()

Dim oleSqlCommand As New OleDbCommand(SqlStr, OleDBCon)

oleSqlCommand.CommandText = SqlStr

i = oleSqlCommand.ExecuteNonQuery()

Catch e As Exception

i = 0

m_sRunMsg = (" UpDate_OleDB 错误号：" & Err.Number & "错误描述：" & Err.Description)

Finally

If OleDBCon.State = ConnectionState.Open Then

OleDBCon.Close() '关闭连接

End If

End Try

Return i

End Function

'*********************************************************

'访问采用ole方式联接的数据库,返回结果的第一行第一列

'*********************************************************

Public Function OleDB_ExecuteScalar(ByVal SqlStr As String, ByVal OleDBCon As OleDbConnection) As Integer

'Dim SqlConStr As String = "provider=SQLOLEDB.1;Data Source=" & _

'"localhost;uid=sa;pwd=sa;Initial Catalog=CarDriManage;"

'如:select count(*) from myTableName where yourCondition

Dim ValueOfFirstRowFirstColumn As Integer = Nothing

Try

'Dim OleDBCon As New OleDbConnection(SqlConStr)

OleDBCon.Open()

Dim oleSqlCommand As New OleDbCommand(SqlStr, OleDBCon)

oleSqlCommand.CommandText = SqlStr

ValueOfFirstRowFirstColumn = oleSqlCommand.ExecuteScalar

Catch e As Exception

ValueOfFirstRowFirstColumn = Nothing

m_sRunMsg = (" UpDate_OleDB 错误号：" & Err.Number & "错误描述：" & Err.Description)

Finally

If OleDBCon.State = ConnectionState.Open Then

OleDBCon.Close() '关闭连接

End If

End Try

Return ValueOfFirstRowFirstColumn

End Function

'使用数据阅读器获取数据

Public Function Get_SqlData_UseReader(ByVal SqlStr As String, ByVal SqlCon As SqlConnection) As ArrayList '返回使用大小按需分配的数组

Dim Command As New SqlCommand(SqlStr, SqlCon)

Dim myAL As New ArrayList '动态增长容量的数组

Dim myDR As SqlClient.SqlDataReader

Try

SqlCon.Open()

'executeReader method returns a SqlDataReader

'that allows you to sequentially read data from a table

myDR = Command.ExecuteReader

'create an arraylist object

While myDR.Read

myAL.Add(myDR.GetValue(0)) 'myDR.GetValues

End While

Catch

myAL = Nothing

m_sRunMsg = (" Get_SqlData_UseReader 错误号：" & Err.Number & "错误描述：" & Err.Description)

Finally

If SqlCon.State = ConnectionState.Open Then

SqlCon.Close() '关闭连接

End If

If myDR.IsClosed = False Then

myDR.Close()

End If

End Try

Return myAL

End Function

End Class