打开/关闭sql连接 - 冗余代码

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

3 个解决方案

#1

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

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

这允许您在一个地方隔离所有样板连接代码。 sql命令字符串进入,数据出来。简单。

Don't do it!

不要这样做!

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查询。这导致了可怕的SQL注入安全漏洞。

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:

相反,请确保在方法中包含一些机制来单独提示sql参数。这通常以函数的附加参数的形式出现,并且可以像KeyValuePairs数组一样简单。如果你对lambdas感到满意,我的首选模式如下:

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)

#2

You can try creating a class ( a singleton class ), and write the database connection syntax code and exceptions in that class, then call one object to the main class to create the database connection, that's the best way in performance and keep writing the same code on and on...

您可以尝试创建一个类(单例类),并在该类中编写数据库连接语法代码和异常,然后将一个对象调用到主类以创建数据库连接,这是性能的最佳方式并保持写入相同代码依旧......

#3

You can use just using block, using execute dispose on non managed object in the end of treatment.

您可以使用块,在处理结束时使用非管理对象上的execute dispose。

Link : http://msdn.microsoft.com/en-us/library/htd05whh(v=vs.80).aspx

链接:http://msdn.microsoft.com/en-us/library/htd05whh(v = vs。80).aspx

#1