谁能给个vb.net的dbhelper

[kevn]

C#通用的有个DBhelper，谁能给个vb.net 的

[kevn]

谁也没有么

[threenewbee]

http://www.developerfusion.com/tools/convert/csharp-to-vb/

[Friday]

'以前用过的。。
Imports System.Configuration
Imports System.Data.OleDb
Imports System.Xml


Public Class ComData



    Public Shared localConnString As String = readConfig("localConnectionString")
    Public Shared serverConnString As String = readConfig("serverConnectionString")


    ' 取得本地连接
    Public Shared Function getLocalConn() As OleDbConnection
        Dim connection As OleDbConnection = Nothing
        Try
            connection = New OleDbConnection(localConnString)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Return connection
    End Function


    ' 取得服务器连接
    Public Shared Function getServerConn() As OleDbConnection
        Dim connection As OleDbConnection = Nothing
        Try
            connection = New OleDbConnection(serverConnString)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Return connection
    End Function

    ' 取得服务器连接(指定参数)
    Public Shared Function getServerConn(ByVal specifiedConnString As String)
        Dim connection As OleDbConnection = Nothing
        Try
            connection = New OleDbConnection(specifiedConnString)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Return connection
    End Function


    ' 执行一句更新SQL,返回执行影响的纪录数
    Public Shared Function updateData(ByVal sql As String, ByVal conn As OleDbConnection) As Integer
        Dim myCommand As OleDbCommand
        Dim updateCounts = 0
        Try
            myCommand = New OleDbCommand(sql)
            myCommand.Connection = conn
            conn.Open()
            updateCounts = myCommand.ExecuteNonQuery()
            myCommand.Connection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If Not conn Is Nothing Then
                conn.Close()
            End If
        End Try
        Return updateCounts
    End Function

    ' 执行一句删除SQL,返回执行影响的纪录数
    Public Shared Function deleteData(ByVal sql As String, ByVal conn As OleDbConnection) As Integer
        Dim myCommand As OleDbCommand
        Dim deleteCounts = 0
        Try
            myCommand = New OleDbCommand(sql)
            myCommand.Connection = conn
            conn.Open()
            deleteCounts = myCommand.ExecuteNonQuery()
            myCommand.Connection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If Not conn Is Nothing Then
                conn.Close()
            End If
        End Try
        Return deleteCounts
    End Function

    ' 执行一句插入SQL,返回插入后第一行第一列的数值,通常为ID
    Public Shared Function insertData(ByVal sql As String, ByVal conn As OleDbConnection, ByVal values As ArrayList) As Integer
        Dim myCommand As OleDbCommand
        Dim lastInsertId = 0
        Try
            myCommand = New OleDbCommand(sql)
            myCommand.Connection = conn
            setParameters(values, myCommand)
            conn.Open()
            myCommand.ExecuteNonQuery()
            myCommand.CommandText = "SELECT @@IDENTITY"
            lastInsertId = myCommand.ExecuteScalar()
            myCommand.Connection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If Not conn Is Nothing Then
                conn.Close()
            End If
        End Try
        Return lastInsertId
    End Function
    ' 根据特定查询得到一个数据库某表某行某字段的VALUE
    Public Shared Function getDataValue(ByVal sql As String, ByVal conn As OleDbConnection) As String
        Dim myCommand As OleDbCommand
        Dim returnStr = ""
        Try
            myCommand = New OleDbCommand(sql)
            myCommand.Connection = conn
            conn.Open()
            returnStr = myCommand.ExecuteScalar()
            myCommand.Connection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If Not conn Is Nothing Then
                conn.Close()
            End If
        End Try
        Return returnStr
    End Function

    ' 根据指定查询SQL,得到一个DATA READER,这个DR将会在关闭同时自动关闭CONN
    Public Shared Function getDataReader(ByVal sql As String, ByVal conn As OleDbConnection, ByVal values As ArrayList) As OleDbDataReader
        Dim myReader As OleDbDataReader = Nothing
        Try
            conn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
            setParameters(values, cmd)
            myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            MsgBox(ex.ToString)
            conn.Close()
        End Try
        Return myReader
    End Function


    Private Shared Sub setParameters(ByVal values As ArrayList, ByRef cmd As OleDbCommand)
        For Each r As OleDb.OleDbParameter In values
            cmd.Parameters.Add(r)
        Next

    End Sub
    '读取配置文件
    Public Shared Function readConfig(ByVal strKey As String) As String

        Dim strReturn As String
        Dim appReader As AppSettingsReader = New AppSettingsReader()
        Try
            strReturn = appReader.GetValue(strKey, Type.GetType("System.String")).ToString()
        Catch ex As Exception
            strReturn = ex.Message.ToString()
        Finally
            appReader = Nothing
        End Try

        Return strReturn
    End Function

 



    ' 读取配置文件中某个设置里面的某个数值
    Public Shared Function readConfigSubValue(ByVal configValue As String, ByVal subKey As String) As String
        Dim subConfigArr = configValue.Split(";")
        For Each subConfig In subConfigArr
            Dim tmpArr = subConfig.Split("=")
            If (tmpArr(0).Trim().ToString = subKey) Then
                Return tmpArr(1)
            End If
        Next
        Return ""
    End Function






End Class

[Friday]

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Dim sql = "insert into loginuser (userName,[passWord]) values(@account,@password)"
        sql = String.Format(sql, "1", "2")


        Dim list As New ArrayList

        list.Add(New OleDb.OleDbParameter("@account", "abe"))
        list.Add(New OleDb.OleDbParameter("@password", "cde"))


        Try
            Dim result = ComData.insertData(sql, ComData.getLocalConn, list)
            ' Dim myReader = ComData.getDataReader(sql, ComData.getLocalConn, list)

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

[Friday]

sql = String.Format(sql, "1", "2")

这行去掉

[nice_L]

Imports System
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections.Generic
Imports System.Text
Imports System.Data

Public Class DBHelper
Private Shared com As SqlCommand
Private Shared reader As SqlDataReader
Private Shared adapter As SqlDataAdapter
Private Shared conn As SqlConnection

Public Shared ReadOnly Property NewConn() As SqlConnection
Get
Dim connectionString As String
connectionString = System.Configuration.ConfigurationManager.AppSettings("Supermarket")
'应该在这里先判断conn是否为Nothing
If conn Is Nothing Then
conn = New SqlConnection(connectionString)
End If
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Return conn
End Get
End Property

'执行增删改(无参)
Public Shared Function ExecuteNonQuery(ByVal sql As String) As Integer
com = New SqlCommand(Sql, NewConn())
Return com.ExecuteNonQuery()
End Function

'执行增删改(有参)
Public Shared Function ExecuteNonQuery(ByVal sql As String, ByVal para As SqlParameter()) As Integer
com = New SqlCommand(sql, NewConn)
com.Parameters.AddRange(para)
Return com.ExecuteNonQuery()
End Function

'执行增删改的存储过程
Public Shared Function ExecuteNonQuery(ByVal para As SqlParameter(), ByVal ProcedureName As String) As Integer
Dim cmd As SqlCommand
cmd = New SqlCommand()
cmd.Connection = NewConn()
cmd.CommandText = ProcedureName
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddRange(para)
Return com.ExecuteNonQuery
End Function

'执行查询(返回一个结果集，无参)
Public Shared Function GetScalar(ByVal sql As String) As Integer
com = New SqlCommand(sql, NewConn)
Return Convert.ToInt32(com.ExecuteScalar())
End Function

'执行查询(返回一个结果集，有参)
Public Shared Function GetScalar(ByVal sql As String, ByVal para As SqlParameter()) As Integer
com = New SqlCommand(sql, NewConn)
com.Parameters.AddRange(para)
Return Convert.ToInt32(com.ExecuteScalar())
End Function
'执行查询(返回一行数据,无参)

Public Shared Function GetReader(ByVal sql As String) As SqlDataReader
com = New SqlCommand(sql, NewConn)
reader = com.ExecuteReader()
Return reader
End Function
'执行查询(返回一行数据,有参)

Public Shared Function GetReader(ByVal sql As String, ByVal para As SqlParameter()) As SqlDataReader
com = New SqlCommand(sql, NewConn)
com.Parameters.AddRange(para)
reader = com.ExecuteReader()
Return reader
End Function
'执行查询(返回一个数据集,无参)

Public Shared Function GetDataSet(ByVal sql As String) As DataTable
Dim dataset As DataSet
dataset = New DataSet()
com = New SqlCommand(sql, NewConn)
adapter = New SqlDataAdapter(com)
adapter.Fill(dataset)
Return dataset.Tables(0)
End Function
'执行查询(返回一个数据集,有参)
Public Shared Function GetDataSet(ByVal sql As String, ByVal para As SqlParameter()) As DataTable
Dim dataset As DataSet
dataset = New DataSet()
com = New SqlCommand(sql, NewConn)
com.Parameters.AddRange(para)
adapter = New SqlDataAdapter(com)
adapter.Fill(dataset)
Return dataset.Tables(0)
End Function
End Class

[nice_L]

Imports System.Configuration
添加这个的引用你应该知道吧？ 还有在项目里面右键“添加引用” 添加这个引用

[somnusnrlq]

nice——L con没有在下面的方法中关闭啊