将参数发送到存储过程 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.
相关文章