将参数发送到存储过程 vb.net

Sending parameters to stored procedures vb.net

提问by Diego

Hello this my first project in vb.net working with ms visual studio 2010, i want to create a class that can send parameters to stored procedures in an transact-sql database, i know how to do it in vb 6 but i'm not sure if this the right way to do it in here.

您好，这是我在 vb.net 中使用 ms Visual Studio 2010 的第一个项目，我想创建一个类，该类可以将参数发送到 transact-sql 数据库中的存储过程，我知道如何在 vb 6 中执行此操作，但我不是确定这是否是在这里做的正确方法。

Imports System.Data.SqlClient

Public Class ClsLineas

Public Sub Inserta(ByVal GridLineas As DataGrid, _
                   ByVal numero As String, _
                   ByVal tipo As String, _
                   ByVal estado As String, _
                   ByVal anexo As Integer, _
                   ByVal fechaInicio As String, _
                   ByVal fechaFin As String, _
                   ByVal pcReg As String, _
                   ByVal observaciones As String, _
                   ByVal usuReg As String)

    Dim cnx As SqlConnection = New SqlConnection(ClsCon.connectionString)
    'ClsCon.connectionString is a class that contains the connection string 
    Dim cmd As SqlCommand = New SqlCommand()

    If cnx.State = ConnectionState.Closed Then cnx.Open()

    cmd.Connection = cnx
    cmd.CommandText = "SP_INSERTA_LINEA"
    cmd.CommandType = CommandType.StoredProcedure

    Dim prm As New SqlParameter

    prm.ParameterName = "@TIPO"
    prm.SqlDbType = SqlDbType.NVarChar
    prm.Size = 30
    prm.Direction = ParameterDirection.Input
    prm.Value = tipo
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@FECHA_INICIO"
    prm.SqlDbType = SqlDbType.NVarChar
    prm.Size = 30
    prm.Direction = ParameterDirection.Input
    prm.Value = fechaInicio
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@FECHA_FIN"
    prm.SqlDbType = SqlDbType.NVarChar
    prm.Size = 30
    prm.Direction = ParameterDirection.Input
    prm.Value = fechaFin
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@ESTADO"
    prm.SqlDbType = SqlDbType.NVarChar
    prm.Size = 30
    prm.Direction = ParameterDirection.Input
    prm.Value = estado
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@NUMERO"
    prm.SqlDbType = SqlDbType.NVarChar
    prm.Size = 15
    prm.Direction = ParameterDirection.Input
    prm.Value = numero
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@ANEXO"
    prm.SqlDbType = SqlDbType.Int
    prm.Direction = ParameterDirection.Input
    prm.Value = anexo
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@PC_REG"
    prm.SqlDbType = SqlDbType.NVarChar
    prm.Size = 50
    prm.Direction = ParameterDirection.Input
    prm.Value = pcReg
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@USU_REG"
    prm.SqlDbType = SqlDbType.NVarChar
    prm.Size = 50
    prm.Direction = ParameterDirection.Input
    prm.Value = usuReg
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@OBSERVACIONES"
    prm.SqlDbType = SqlDbType.NVarChar
    prm.Size = 1000
    prm.Direction = ParameterDirection.Input
    prm.Value = observaciones
    cmd.Parameters.Add(prm)

    prm.ParameterName = "@ID"
    prm.SqlDbType = SqlDbType.Int
    prm.Direction = ParameterDirection.Output
    cmd.Parameters.Add(prm)

    Dim adp As SqlDataAdapter = New SqlDataAdapter(cmd)

    Dim DataSet As DataSet = New DataSet("Lineas")

    adp.Fill(DataSet)
    GridLineas.DataSource = DataSet.Tables(0)

End Sub
End class

Some of my doubts are:

我的一些疑问是：

Do i really need to open the database every time i call the methods of my class?

每次调用我的类的方法时，我真的需要打开数据库吗？

Are the sqlAdapter and Dataset really needed? In vb 6 you could do something like "command execute inserta" after appending the parameters and you where done.

真的需要 sqlAdapter 和 Dataset 吗？在 vb 6 中，您可以在附加参数后执行诸如“命令执行插入”之类的操作，然后您就完成了。

回答by AGB

If you're just reading data then checkout the SqlDataReader:

如果您只是读取数据，请查看 SqlDataReader：

Dim reader As SqlDataReader
reader = cmd.ExecuteReader()
While reader.Read
    //Do stuff with reader
End While

If you are doing an update or an insert then you can use the ExecuteNonQuery() method of the SqlCommand class.

如果您正在进行更新或插入，则可以使用 SqlCommand 类的 ExecuteNonQuery() 方法。

SqlCommand has a shorthand for adding parameters:

SqlCommand 有一个添加参数的简写：

cmd.Parameters.AddWithValue("@MyParamName", myParamValue)

Which you may find useful.

您可能会觉得这很有用。

And yes you should open and close a database connection every time you need to interact with the database. Read up on the Using statement, which will help you to do this nice and neatly.

是的，每次需要与数据库交互时，都应该打开和关闭数据库连接。阅读 Using 语句，这将帮助您很好地完成这项工作。

回答by Guffa

You don't need a separate database connection for each call, you can open it once and send it into each method that uses it, then close it.

您不需要为每个调用建立单独的数据库连接，您可以打开它一次并将其发送到使用它的每个方法中，然后关闭它。

It's important however that you close or dispose connections and commands that you use. If you don't, the connection will remain open for some time until the database itself kills it. If you leave enough connections hanging, you will run out of resources.

但是，关闭或处理使用的连接和命令很重要。如果不这样做，连接将保持打开状态一段时间，直到数据库本身杀死它。如果您留下足够多的连接挂起，您将耗尽资源。

A SqlDataAdapterand a DataSetis only needed if the stored procedure returns a result, and only if you want that result in a DataSetobject. You can use the SqlCommand.ExecuteNoQuerymethod to run a stored procedure that doesn't return any result. You can also get a result in a SqlDataReaderand read the data from that if you don't want to use a DataSet.

仅当存储过程返回结果时才需要ASqlDataAdapter和 a DataSet，并且仅当您希望该结果在DataSet对象中时才需要。您可以使用该SqlCommand.ExecuteNoQuery方法运行不返回任何结果的存储过程。SqlDataReader如果您不想使用 a ，您还可以在 a 中获得结果并从中读取数据DataSet。

Note: You have to create one SqlParameterfor each parameter. Now you create one parameter and change that over and over, so the parameter collection will end up having ten references to the same parameter.

注意：您必须SqlParameter为每个参数创建一个。现在您创建一个参数并一遍又一遍地更改它，因此参数集合最终将有十个对同一参数的引用。