Mitch has posted a simple snippet that shows how you can work with stored procedures in C#. I figured that I’d firstly translate this to VB.NET:
‘ Execute a stored proc to read data
Using conn As New SqlConnection(connectionString), _
cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = “ProcName”
cmd.CommandType = CommandType.StoredProcedure
‘ Add any input Params…
cmd.Parameters.AddWithValue(“@SomeIDParam”, myID)
conn.Open()
‘ Assuming Stored Proc returns a set of records…
Using rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While rdr.Read()
‘ do something with each rdr row …
End While
End Using
End Using
Now that we have this snippet it would be great if we could add this to the [VS2005] snippets. The best way to do this is to grab the Snippet Editor from GotDotNet (not sure where this link will go when they close it down later this year, but oh well).
Running the Snippet Editor will display a tree on the left which shows all the snippets which are currently in VS2005. If you right-click one of the nodes you can “Add New Snippet”. You then have to give the snippet a name; in this case we use “Simple Stored Proc”. After creating the snippet you need to double-click it on the tree to open it in the edit window, as shown below (I’ve filled in some of the information textboxes on this screen).
At the moment the snippet is a great way to reduce the amount of code you need to write but doesn’t really highlight the parts of the snippet that you need to replace. For example it is unlikely when you use this snippet that you will be calling a stored proc with the name “ProcName”. To improve this snippet we can add replacements which will identify areas of the snippet that need to be replaced when the snippet is used.
Using the Replacements tab of the editor is relatively easy. You simply select the text in the edit window that you want to make a replacement (eg “ProcName”) and select the + (Add new) button on the navigation bar. This will allow you to spectify a name, default replacement and much more. Once you are done your snippet editor window might look like the following:
For my snippet I’ve also added System.Data.SqlClient to the Imports tab and System.Data to the References tab. This will ensure that appropriate imports and references are added to the project when this snippet is used.
Don’t forget to click “Save” when you are done!
Lastly if you want to share this snippet with others in your team (or on your blog) you can click Export which will create a Visual Studio installer.