Sending parameters to stored procedures vb.net

Question

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

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.

Solution 1

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

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

SqlCommand has a shorthand for adding parameters:

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.

Solution 2

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 SqlDataAdapter and a DataSet is only needed if the stored procedure returns a result, and only if you want that result in a DataSet object. You can use the SqlCommand.ExecuteNoQuery method to run a stored procedure that doesn't return any result. You can also get a result in a SqlDataReader and read the data from that if you don't want to use a DataSet.

Note: You have to create one SqlParameter for 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.