Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Tell me about the advantages of stored procedures

What are the real advantages ? What nifty things can I do with them in VB.NET ? How can I pass criteria from my form to them, let's say I had a textbox that had some text in it, could I filter my results with that data ? I am just trying to understand what the point is.
0
reefcrazed
Asked:
reefcrazed
  • 4
  • 3
  • 3
  • +1
3 Solutions
 
levyukCommented:
They are more secure, can be reused, can perform calculations amongst other things. Using them in your code is just as easy as using normal SQL. Just need to look up some tutorials on it.
0
 
levyukCommented:
Here's an example of vb code I used with a stored procedure to return the rows

'Checks how many records have been entered for the current date/time slot
Function checkrows(ByVal DeliveryTime As String, ByVal DeliveryDate As String) As Integer
Dim rows As Integer
Dim ds As DataSet
Dim adapter As SqlDataAdapter
Dim conn As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Procedures.mdf;Integrated Security=True;User Instance=True")
Try
adapter = New SqlDataAdapter("sp_countRows ", conn)
adapter.SelectCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand.Parameters.Add(New SqlParameter("@rowcount", SqlDbType.Int, 4))
adapter.SelectCommand.Parameters.Add(New SqlParameter("@DeliveryTime", SqlDbType.Text))
adapter.SelectCommand.Parameters.Add(New SqlParameter("@DeliveryDate", SqlDbType.Text))
adapter.SelectCommand.Parameters("@DeliveryDate").Value = DeliveryDate
adapter.SelectCommand.Parameters("@rowcount").Direction = ParameterDirection.Output
adapter.SelectCommand.Parameters("@DeliveryTime").Value = DeliveryTime
ds = New DataSet()
adapter.Fill(ds, "Deliveries")
rows = CType(adapter.SelectCommand.Parameters(0).Value, Integer)
Catch ex As Exception
rows = 0
End Try
Return rows
End Function
0
 
reefcrazedAuthor Commented:
I am guessing there is also a store procedure "stored" on the server, where is the code on that ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gbzhhuCommented:
You can do anything you could do with normal sql query with stored procedure.  They have several advantages

Quick read here

http://4guysfromrolla.com/webtech/102300-1.shtml

and here

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=3&rl=1

I am sure there are better URLs if you spend a bit of time googling

And yes you can filter all you like.  you just pass the criteria as a parameter to the stored procedure and use that in your WHERE clause in the stored procedure

If you need examples look here, also explains advantages of stored procs

http://www.vbdotnetheaven.com/Code/Jun2003/2102.asp



0
 
levyukCommented:
TYeah that's correct, I don't have the stored procedure anymore so I can't show you. But they are easy to write so you shouldn't have a problem there
0
 
gbzhhuCommented:
Sorry wasn't duplicating anyone's post, you all posted while I was composiong my post, SLOW DOWN :-)!!
0
 
reefcrazedAuthor Commented:
Wow I hit a hot topic, easy to get people ecited about stored procedures.
0
 
mydasxCommented:
Ok here it is plain and simple like.

Stored Procs are compiled.  compiled code runs a billion times faster then interpretted code always.
Stored Procs protect you against Sql Injection Hacking.  If there is no way to modify overwrite, etc the sql statements,
your database is safe from unwarrented hacks.

Mydasx.
0
 
reefcrazedAuthor Commented:
Ok let us say I wrote this store procedure

CREATE PROCEDURE Get_Me_Data
(
      @selection    integer      OUTPUT
)
AS

SELECT index,firstname,lastname
FROM [TABLE] where index=@selection

What would my statement look like in VB.NET to pass that @selection on to the statement. I have been reading for quite a while and people have a way of giving you information overload and I just want the basics.
0
 
mydasxCommented:
Dim connection As SqlConnection = _
        New SqlConnection(connectionString)
    connection.Open()
    Try
        Dim command As SqlCommand = _
            New SqlCommand("Get_Me_Data", connection)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@selection", 1)
        Dim myReader As SqlDataReader
        myReader = myCommand.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
           Console.WriteLine((myReader.GetInt32(0) & ", " & myReader.GetString(1)))
        End While
        ' always call Close when done reading.
        myReader.Close()

    Catch ex As Exception
            Console.WriteLine(ex.Message)
            Throw
    Finally
            connection.Close()
    End Try


0
 
mydasxCommented:
point allocate them.  dont leave the experts hanging.
0
 
reefcrazedAuthor Commented:
Bumped the points up for easy division.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now