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

问题描述

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

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.

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

我的一些疑问是:

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

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

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

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.

推荐答案

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

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

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

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

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

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

SqlCommand has a shorthand for adding parameters:

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

您可能会觉得有用.

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

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.

这篇关于将参数发送到存储过程 vb.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！