is it necessary to use using Dispose the connection , open and close connection if using DbContext dapper in MS access database on vb.net

huangapple go评论61阅读模式
英文:

is it necessary to use using Dispose the connection , open and close connection if using DbContext dapper in MS access database on vb.net

问题

以下是翻译好的部分:

"使用 DbContext 在 VB.NET 中的 MS Access 数据库中使用 Dapper 是否需要使用 Dispose 连接、打开和关闭连接?

这是我第一次使用 Dapper,请指导我以确保在执行 CRUD 操作时没有错误,并且高效地使用 OleDb 数据库中的 Dapper。我也担心我的代码实现中可能存在错误。

谢谢。

' 模型
Public Class User
Public Property Id() As Integer
Public Property Name() As String
Public Property Email() As String
Public Property Password() As String
End Class

' 数据库
Public Module DbContext
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dapperdemo.accdb;Persist Security Info=False;"
End Function
End Module

Public Class UserService
Private ReadOnly _conn As OleDbConnection
Private _connectionString As String = DbContext.GetOledbConnectionString()
Public Sub New()
_conn = New OleDbConnection(_connectionString)
End Sub
Public Function GetUser() As IEnumerable(Of User)
Dim sql = "SELECT * FROM Users"
Dim result = Me._conn.Query(Of User)(sql).ToList()
Return result
End Function
Public Sub InserUser(ByVal user As User)
Dim sql = $"INSERT INTO users (Name, Email, Password) VALUES ('{user.Name}', '{user.Email}', '{user.Password}');"
Me._conn.Execute(sql)
End Sub
End Class

' 在 DataGridView 中加载数据的窗体加载
Partial Public Class Pguser
Inherits UserControl
Private uService As New Database.UserService()
Public Sub New()
If Program.IsInDesignMode(Me) Then
Return
End If
InitializeComponent()
LoadData()
End Sub
Private Sub LoadData()
Dim user = uService.GetUser()
grid.DataSource = user
End Sub
End Class"

英文:

is it necessary to use using Dispose the connection, open and close connection if using DbContext dapper in MS access database on vb.net.

This is my first time using dapper, please guide me so that there are no errors in doing the crud and efficiently use Dapper in oledb database access.
and also I'm afraid there is an error in implementing my code.

Thanks

'model
Public Class User
        Public Property Id() As Integer
        Public Property Name() As String
        Public Property Email() As String
        Public Property Password() As String
    End Class

'Database
  Public Module DbContext
        Public Function GetOledbConnectionString() As String
            Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dapperdemo.accdb;Persist Security Info=False;"
        End Function
    End Module

 Public Class UserService
        Private ReadOnly _conn As OleDbConnection
        Private _connectionString As String = DbContext.GetOledbConnectionString()
        Public Sub New()
            _conn = New OleDbConnection(_connectionString)
        End Sub
        Public Function GetUser() As IEnumerable(Of User)
            Dim sql = "SELECT * FROM Users"
            Dim result = Me._conn.Query(Of User)(sql).ToList()
            Return result
        End Function
        Public Sub InserUser(ByVal user As User)
            Dim sql = $"INSERT INTO `users` (`Name`, `Email`, `Password`) VALUES ('{user.Name}', '{user.Email}', '{user.Password}');"
            Me._conn.Execute(sql)
        End Sub
'form load in datagridview
 Partial Public Class Pguser
        Inherits UserControl
        Private uService As New Database.UserService()
        Public Sub New()
            If Program.IsInDesignMode(Me) Then
                Return
            End If
            InitializeComponent()
            LoadData()
        End Sub
        Private Sub LoadData()
            Dim user = uService.GetUser()
            grid.DataSource = user
        End Sub
    End Class

答案1

得分: 1

关于您的请求:

>请指导我...在oledb数据库访问中有效地使用Dapper

在提到效率之前,正确使用实现IDisposable接口的对象应该是首要考虑的事情。有关Dapper的更多信息,请参阅此答案。看起来Dapper会自动为您打开(和关闭)连接,但由于您正在进行打开操作,那么您也可以同时进行关闭操作。

Public Class UserService
    Public Function GetUser() As IEnumerable(Of User)
        Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
            Return _conn.Query(Of User)("SELECT * FROM Users").ToList()
        End Using
    End Function
    Public Sub InserUser(ByVal user As User)
        Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
            _conn.Execute($"INSERT INTO `users` (`Name`, `Email`, `Password`) VALUES ('{user.Name}', '{user.Email}', '{user.Password}');")
        End Using
    End Sub
End Class

使用 Try ... Finally

Public Function GetUser() As IEnumerable(Of User)
    Dim _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
    Try
        Return _conn.Query(Of User)("SELECT * FROM Users").ToList()
    Finally
        _conn?.Dispose()
    End Try
End Function

您可以使用 Try ... Finally 显式关闭连接,但是使用 Using 语法更加简洁。

您还可以考虑使用Entity Framework来摆脱文本命令的使用。

英文:

About your request

>please guide me... efficiently use Dapper in oledb database access

Properly using the object which implements IDisposable should come before you mention efficiency. See this answer for more info about Dapper specifically. It seems Dapper will open (and close) the connection for you but since you are doing the opening, you might as well do both.

Public Class UserService
    Public Function GetUser() As IEnumerable(Of User)
        Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
            Return _conn.Query(Of User)("SELECT * FROM Users").ToList()
        End Using
    End Function
    Public Sub InserUser(ByVal user As User)
        Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
            _conn.Execute($"INSERT INTO `users` (`Name`, `Email`, `Password`) VALUES ('{user.Name}', '{user.Email}', '{user.Password}');")
        End Using
    End Sub
End Class

With Try ... Finally

Public Function GetUser() As IEnumerable(Of User)
    Dim _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
    Try
        Return _conn.Query(Of User)("SELECT * FROM Users").ToList()
    Finally
        _conn?.Dispose()
    End Try
End Function

You can explicitly close with a Try ... Finally but the Using syntax is much cleaner.

You can also look into Entity Framework to move away from text commands.

huangapple
  • 本文由 发表于 2023年5月22日 22:59:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76307472.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定