Subscribe to Cache Cow        RSS Feed

Using Stored Procedures with VB.NET and SQL Server

Icon 3 Comments
I thought about writing some insightful message that would, eventually, lead up to my version of how to execute a stored procedure in SQL Server from a VB.NET application. After giving it some thought, I decided to just get right down to what everyone cares about...the code. So here it is:

What I am using: VS2010, fw 3.5, and the System.Data.SqlClient namespace.
Assumed: You have a connection string (myConnectionString) already set up and you have a data table (myData) set up to receive the data from the database.

The first thing we need is a connection. You can use a literal connection string or a connection string stored in a variable somewhere:
Dim conn as New SqlConnection(myConnectionString)

The next step is to set up the SqlCommand object:
Dim cmd as new SqlCommand("myStoredProcedureName", conn)

With cmd
     .CommandType = CommandType.StoredProcedure
     .Parameters.Add("@myDate", SqlDbType.DateTime).Value = Now.ToString
     .Parameters.Add("@myName", SqlDbType.VarChar).Value = "Joel"
End With

Note that the parameters must match those parameters required by the stored procedure in both data type and name.
Now that we are done setting up the SqlCommand object, we need to set up the data adapter to fill the table with the data:
Dim da as New SqlDataAdapter(cmd)


That is pretty much it. Not too hard, right? Of course, we are assuming that you know what to do with the data once you get it. Now for the disclaimer: Use this code at your own risk. I do not claim that it is the best way to do it, that it is the only way to do it, or even that this particular code will work. In fact, you should probably just disregard everything you read in this blog. Thanks for reading!

3 Comments On This Entry

Page 1 of 1

NotarySojac Icon

28 January 2012 - 11:38 AM
I don't know very much about SQL server, but is it easy to describe how to define what a "hello world" stored procedure looks like on the server side? Perhaps one taking two parameters that match what you called in this code?

Psyguy Icon

01 February 2012 - 01:37 PM
I'll work on a more complete example so you can see what it would look like.

Psyguy Icon

15 November 2012 - 05:32 PM
Here is a link to a more in depth look

Page 1 of 1

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

September 2014

14151617 18 1920