在 VB.NET 中设置 SqlParameter？

Set SqlParameter in VB.NET?

提问by Winston Madiano

I'm new with classes and I want to create a SqlCommandManagerclass and I can't figure out how to pass SqlParameteron my class.

我是新来的班级，我想创建一个SqlCommandManager班级，但我不知道如何传授SqlParameter我的班级。

For example if I want to insert data I would just use my class like client below.

例如，如果我想插入数据，我会像下面的客户端一样使用我的类。

'Client

Dim m_SqlComManager as new SQLCommandManager("MyConnectionString")
m_SqlCommandManager.Commandtext = "INSERT INTO [TableName]([Field1],[Field2])VALUES(@Field1,Field2);"
m_SqlCommandManager.Parameters.AddWithValue("@Field1","SomeValue1")
m_SqlCommandManager.Parameters.AddWithValue("@Field2","SomeValue2")
m_SqlCommandManager.ExecuteNonQuery()


'Here is my class

Imports System.Data.SqlClient
Public Class SQLCommandManager
Private m_SqlParameters As SqlParameter()
Private m_Commandtext As String
Private m_ConStr As String

Public WriteOnly Property SQlParameter() As SqlParameter()
    Set(ByVal value As SqlParameter())
        value = m_SqlParameters
    End Set
End Property

Public Property CommandText() As String
    Get
        Return m_Commandtext
    End Get
    Set(ByVal value As String)
        value = m_Commandtext
    End Set
End Property

Public Sub New(ByVal con As String)
    m_ConStr = con
End Sub

Public Sub ExecuteNonQuery()
    Using con As New SqlConnection(m_ConStr)
        Using com As New SqlCommand
            com.Connection = con
            com.CommandText = m_Commandtext
            'Please help
            'How can i insert parameter here from client..

            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            com.ExecuteNonQuery()
        End Using
    End Using
End Sub
End Class

How how can I set the parameters before the ExecuteNonQuerymethod?

如何在ExecuteNonQuery方法之前设置参数？

Thanks in advance..

提前致谢..

采纳答案by marc_s

I would do something like this:

我会做这样的事情：

Public Class SqlCommandManager
    Private m_SqlParameters As List(Of SqlParameter)
    Private m_Commandtext As String
    Private m_ConStr As String

    Public Sub New()
        m_SqlParameters = New List(Of SqlParameter)()
    End Sub

    Public ReadOnly Property SqlParameters() As List(Of SqlParameter)
        Get
            Return m_SqlParameters
        End Get
    End Property

    Public Property CommandText() As String
        Get
            Return m_Commandtext
        End Get
        Set
            value = m_Commandtext
        End Set
    End Property

    Public Sub New(con As String)
        m_ConStr = con
    End Sub

    Public Sub ExecuteNonQuery()
        Using con As New SqlConnection(m_ConStr)
            Using com As New SqlCommand(m_Commandtext, con)
                com.Parameters.AddRange(m_SqlParameters.ToArray())

                con.Open()
                com.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Sub
End Class

What I've changed:

我改变了什么：

• Changed the class name to SqlCommandManagerto be in line with Microsoft's recommendations (don't capitalize more than 2 letters in an abbreviation; IOis fine, Sqland Xmlshould not be all capitalized)

• I would use a List(Of SqlParameter)rather than an array - much easier to deal with, much easier to add additional parameters to it

• I prefer to pass the CommandTextand the SqlConnectionright into the constructor of the SqlCommand- that way,you definitely never forget these two vital bits of information!

• Just before your .ExecuteQuery, add the parameters defined in your list to the parameter array of the SqlCommandusing a single call to .AddRange()

• 改变了类名SqlCommandManager是在与微软的建议（不利用多比的缩写2个字母，IO是罚款，Sql而且Xml不应该是全部大写）

• 我会使用一个List(Of SqlParameter)而不是数组 - 更容易处理，更容易向它添加额外的参数

• 我更喜欢将 theCommandText和SqlConnectionright传递给the的构造函数SqlCommand- 这样，您绝对不会忘记这两个重要的信息！

• 就在您之前.ExecuteQuery，将列表中定义的参数添加到SqlCommand使用单个调用的参数数组中.AddRange()