打开/关闭sql连接 - 冗余代码 [英] Opening/closing sql connection - redundant code

问题描述

我想知道什么是最基本的方法来避免以下。

I was wondering what is the most basic way to avoid the following.

    con.ConnectionString = connection_String
    con.Open()
    cmd.Connection = con

    'database interaction here

    cmd.Close()

我一直在我的项目中完成这些行，但我认为有一个更好的方式来保存打字这一遍又一遍。它使代码看起来比它已经是更粗糙的！

I keep making those lines all over in my project, but I figure there has to be a better way to save on typing this over and over. It makes the code look even more sloppy than it already is!

结束了，对我来说很好。感谢帮助：）

Ended up with this, works well for me. Thanks for the help :)

Public Sub connectionState()
    If con.State = 0 Then
        con.ConnectionString = connection_String
        con.Open()
        cmd.Connection = con
    Else
        con.Close()
    End If
End Sub

推荐答案

这是很多程序员试图创建数据库层，方法签名的变化如下：

This is where a lot of programmers are tempted to create a "database layer" with a variations on method signatures that look like this:

Public DataSet ExecuteSQL(ByVal sql As String) As DataSet

这允许你在一个地方隔离所有的样板连接代码。一个sql命令字符串进入，数据出来。很容易。

That allows you to isolate all that boilerplate connection code in one place. An sql command string goes in, and data comes out. Easy.

不要这样做！

Don't do it!

但有一个非常大的缺陷：它强制您使用字符串操作将参数值替换到您的sql查询。这会导致可怕的sql注入安全漏洞。

This is headed in the right direction, but has one very big flaw: it forces you to use string manipulation to substitute parameter values into your sql queries. That leads to horrible sql injection security vulnerabilities.

相反，请确保您在方法中包括一些机制，以单独提示sql参数。这通常以对函数的附加参数的形式出现，并且可以像KeyValuePairs的数组一样简单。如果你喜欢lambdas，我喜欢的模式是这样的：

Instead, make sure you include some mechanism in your methods to prompt for the sql parameters separately. This usually comes in the form of an additional argument to the function, and could be as simple as an array of KeyValuePairs. If you're comfortable with lambdas, my preferred pattern looks like this:

Public Iterator Function GetData(Of T)(ByVal sql As String, ByVal addParameters As Action(Of SqlParameterCollection), ByVal translate As Func(Of IDatarecord, T)) As IEnumerable(Of T)
    Using cn As New SqlConnection("connection string"), _
          cmd As New SqlCommand(sql, cn)

        addParameters(cmd.Parameters)

        cn.Open()
        Using rdr As SqlDataReader = cmd.ExecuteReader()
            While rdr.Read()
                Yield(translate(rdr))
            End While
        End Using
    End Using
End Function

要调用该函数，您可以这样做：

To call that function, you would do something like this:

Dim bigCustomers = GetData("SELECT * FROM Customers WHERE SalesTotal > @MinSalesTotal", _
                   Sub(p) p.Add("@MinSalesTotal", SqlDbType.Decimal, 1000000), _
                   MyCustomerClass.FromIDataRecord)

这篇关于打开/关闭sql连接 - 冗余代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！