ado.net数据访问类
Imports System.Data.OleDb
Public Class ado_net
'ado.net实现数据访问类
'定义连接字符串
Private connstr As String
'定义一个连接,随时调用。
Dim mOleCnnDB As New OleDbConnection()
'close conn关闭连接
Public Sub CloseConn()
mOleCnnDB.Close()
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'access name and path
' username=admin
Public Sub ConnAccess(ByVal DBName As String, ByVal User As String, ByVal UserPwd As String)
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
DBName & ";User Id=" & User & ";Password=" & UserPwd
End Sub
'作用: 连接ODBC数据库(不需指定用户和密码 )
'sql
'参数：dsnName为ODBC名
Public Sub ConnODBC(ByVal dsnName As String)
connstr = "Provider=MSDASQL;Data Source='" & dsnName & "'"
End Sub
'作用: 连接ODBC数据库(需指定用户和密码 )
'sql
'参数：dsnName ODBC名,UserID 用户名，UserPwd 用户密码
Public Sub ConnODBC(ByVal dsnName As String, ByVal UserID As String, ByVal UserPwd As String)
connstr = "Provider = MSDASQL;"
connstr = connstr & "Data Source='" & dsnName & "'User ID='" & UserID & "';" & _
"Password='" & UserPwd & "' "
End Sub
'作用: 连接SQL Server数据库
'参数：ServerName 服务器名,DBName 数据库名
Public Sub ConnSQLServer(ByVal ServerName As String, ByVal DBName As String)
connstr = "uid=;pwd= ;driver={SQL Server};" & _
"server=" & ServerName & _
";database=" & DBName
End Sub
'作用: 连接SQL Server数据库
'参数：ServerName 服务器名,DBName 数据库名,UserID 用户名，UserPwd 用户密码
Public Sub ConnSQLServer(ByVal ServerName As String, ByVal DBName As String, ByVal UserID As String, ByVal UserPwd As String)
connstr = "uid='" & UserID & "';pwd='" & UserPwd & "';driver={SQL Server};" & _
"server=" & ServerName & _
";database=" & DBName
End Sub
'作用: 连接Oracle数据库
'参数：ServerName 服务器名,DBName 数据库名,UserID 用户名，UserPwd 用户密码
Public Sub ConnOracle(ByVal ServerName As String, ByVal UserID As String, ByVal UserPwd As String)
connstr = "Provider = MSDAORA"
connstr = connstr & "User ID='" & UserID & "';" & _
"Password='" & UserPwd & "';" & _
"Data Source='" & ServerName & "'"
End Sub
'以上是设置各种数据库的连接字符串。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'类属性返回连接字符串
'使用此属性前，先调用上面的方法设置字符串的值。
Public ReadOnly Property constr() As String
Get
Return connstr
End Get
End Property
'定义打开连接函数
Public Function ConOpen(ByVal constr As String) As OleDbConnection
Dim OleCnnDB As New OleDbConnection()
With OleCnnDB
.ConnectionString = constr
Try
.Open()
Catch er As Exception
MsgBox(er.ToString)
End Try
End With
mOleCnnDB = OleCnnDB
Return OleCnnDB
End Function
'
'获取数据集。TableName：表名，strWhere：条件
Public Overloads Function GetDataSet(ByVal TableName As String, ByVal strWhere As String) As DataSet
Dim strSql As String
Dim myDataSet As New DataSet()
Dim myOleDataAdapter As New OleDbDataAdapter()
myOleDataAdapter.TableMappings.Add(TableName, TableName)
'?
If strWhere = "" Then
strSql = "SELECT * FROM " & TableName
Else
strSql = "SELECT * FROM " & TableName & " where " & strWhere
End If
myOleDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)
Try
myOleDataAdapter.Fill(myDataSet)
Catch er As Exception
MsgBox(er.ToString)
End Try
Return myDataSet
End Function
'获取数据集。allstrsql=任意写
Public Overloads Function GetDataSet(ByVal allstrsql As String) As DataSet
Dim strSql As String
Dim myDataSet As New DataSet()
Dim myOleDataAdapter As New OleDbDataAdapter()
'myOleDataAdapter.TableMappings.a()
' myOleDataAdapter.TableMappings.Add(TableName, TableName)
' strSql = "SELECT * FROM " & TableName
strSql = allstrsql
myOleDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)
Try
myOleDataAdapter.Fill(myDataSet)
Catch er As Exception
MsgBox(er.ToString)
End Try
Return myDataSet
End Function
'获取物理表。TableName：表名，strWhere：条件
Public Overloads Function GetDataTable(ByVal TableName As String, ByVal strWhere As String) As DataTable
Dim myDataSet As New DataSet()
myDataSet = GetDataSet(TableName, strWhere)
Return myDataSet.Tables(0)
End Function
'OleDbDataReader快速返回记录
Public Function getdar(ByVal sqltxt As String) As OleDbDataReader
Dim cmd As New OleDbCommand()
Dim dap As OleDbDataReader
cmd = New OleDbCommand(sqltxt, mOleCnnDB)
dap = cmd.ExecuteReader
' mOleCnnDB.Close()
Return dap
End Function
'向物理表中插入一行数据。TableName：表名，Value：行数据，BeginColumnIndex：开始列
'Value用数组传过来。如：dim rowvalue(2) as string rowvalue(0)="wml";rowvalue(1)="qiyuan"
Public Overloads Function Insert(ByVal TableName As String, ByVal Value As Object, Optional ByVal BeginColumnIndex As Int16 = 0) As Boolean
Dim myDataAdapter As New OleDbDataAdapter()
Dim strSql As String
Dim myDataSet As New DataSet()
Dim dRow As DataRow
Dim i, len As Int16
strSql = "SELECT * FROM " & TableName
myDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)
Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)
myDataSet.Tables.Add(TableName)
myDataAdapter.Fill(myDataSet, TableName)
dRow = myDataSet.Tables(TableName).NewRow
len = Value.Length
For i = BeginColumnIndex To len - 1
If Not (IsDBNull(Value(i)) Or IsNothing(Value(i))) Then
dRow.Item(i) = Value(i)
End If
Next
myDataSet.Tables(TableName).Rows.Add(dRow)
Try
myDataAdapter.Update(myDataSet, TableName)
Catch er As Exception
MsgBox(er.ToString)
Return False
End Try
myDataSet.Tables.Remove(TableName)
Return True
End Function
' 动作查询
'sqltxt=UPDATE table_name SET column1='x' WHERE conditions
' sqltxt = "insert into 菜分类 (名称,打折) values('热菜',1)"
' sqltxt = "delete from 菜分类 where 名称='热菜'"
Public Function Executesql(ByVal sqltxt As String) As Boolean
Dim cmd As OleDbCommand
Try
cmd = New OleDbCommand(sqltxt, mOleCnnDB)
cmd.ExecuteNonQuery()
Executesql = True
Catch
Executesql = False
End Try
End Function
'用数据集的表实现动作查询
Public Function Executesql(ByVal sqltxt As String, ByVal table As DataTable) As Boolean
' table.Select()
' table.Rows.Remove(4)
' table.updeat()
'**********************************************
' table.NewRow()
' table.Rows.Add()
End Function
End Class
