关于数据库的显示,添加,更新,删除的问题

Question

VB可以直接创建一个公用的函数(比如说 Public sub ConnectAccessModle),以后在执行添加数据/修改数据或者删除数据的过程中,就直接使用ConnectAccessModle(),而不是每一个动作都需要重新写一遍Public里面的过程,还有就是添加数据,删除,修改数据等,怎样写比较简便无误?!

Answer 1

你好，

这个是可以的， 你可以这样创建一个公共的函数，也可以用一个公共的类。我写了一个简单的，用来操作Access数据库的类，你可以直接通过类名调用其中的方法。

Public Class DBHelper
	Private conn As OleDbConnection
	Private oda As New OleDbDataAdapter()
	Private cmd As OleDbCommand
	Private ds As New DataSet()
	Public Sub New()
		Dim mdbPath As String = System.IO.Path.Combine(Application.StartupPath, "db1.accdb")
		conn = New OleDbConnection(Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=") & mdbPath)
	End Sub
	Public Function getDS(strSQL As String) As DataSet
		ds = New DataSet()
		oda = New OleDbDataAdapter(strSQL, conn)
		oda.Fill(ds)
		Return ds
	End Function

	Public Function setDS(strSQL As String) As Boolean
		conn.Open()
		cmd = New OleDbCommand(strSQL, conn)
		cmd.ExecuteNonQuery()
		conn.Close()
		Return True
	End Function
End Class

至于你说的怎么简便无误的写增删改查的代码，我觉得首先你得确保你要执行的sql是对的，先用一些sql调试的工具，看看sql返回值是否正确；

然后就是要进行代码调试，看看每一步的结果是不是和预期一样。看看数据库里数据的变化。

MSDN上还有这个方法操作Access数据库的，你也可以看看。http://support.microsoft.com/kb/821765

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

楼上的读数据到dataset里赞同，但直接拼接sql语句写数据库相对来说不怎么安全，而且容易出错，建议写到dataset里，然后直接更新dataset里的datatable到数据库。

我这有更新datatable的过程

Public Shared Function UpdataDataTab(ByVal sSql As String, ByVal DataTab As DataTable) As String
        Dim MyJetConn As String = “这是数据库连接字符串”
        Try
            SqlConnection.ClearAllPools()
            Using myConnection As New SqlConnection(MyJetConn)
                If myConnection.State <> ConnectionState.Open Then myConnection.Open()
                Dim tmpSDA = New SqlDataAdapter()
                tmpSDA.SelectCommand = New SqlCommand(sSql, myConnection)
                Dim builder As SqlCommandBuilder = New SqlCommandBuilder(tmpSDA)
                builder.ConflictOption = ConflictOption.OverwriteChanges
                Dim ret As String = ""
                ret = tmpSDA.Update(DataTab)
                Return ret
            End Using
        Catch ex As Exception
            Return "更新表错误:" & ex.Message
        End Try
    End Function