April 24, 2006 at 3:22 pm
I'm doing a visual basic .NET app to talk to a SQL database and am trying to get a better explanation than the MS docs offer.
I have a form with a collection of bound controls that talk to a SQL database. I've figured out how to insert and update using parameterized queries pretty good so far but I just want to understand something a little better.
For efficiency reasons, I'm using global variables and creating all my database objects up front in a module that is not part of a form. This is working well so far but I've run into a bit of a snag.
When I go to update, the original way I did it was to Add() all the parameters to the SQLCommand object's parameters collection when I called the function that does all the adding. Ooops. I forgot that you only need to add all those params once so now I need to better understand just how the SQLCommand.parameters.Add("paramname", object) method works.
I *think* that once I've added all the parameters, being careful to get the types correct, then when I do the actual updating, I set the value of each param like this:
SQLCommand.parameter("paramname").Value = form.control.text
Then I exectute my query and voila, DB gets updated.
Of course, I could be wrong you know....
Any help is appreciated.
-JC.
April 25, 2006 at 6:47 am
If I'm understanding your structure correctly, then, yes, your solution should work properly.
You may want to also initialize all the parameters to default values before you start assigning them. Otherwise, you may end up with a value from a previous operation still being held in the parameter.
April 25, 2006 at 6:57 am
Thanks, it did.
The tricky part was understanding that you only need to add parameters once when the example in the book added them every time. Of course, the book example is very inefficient because it re-creates the entire SqlCommand object and parameters every time it goes to insert a record into the DB. I'm no expert but isnt it better to allocate as much as you can in the beginning, then just do the minimum to make things happen while the app is running? Then make sure you de-allocate everything at the end so there's no memory leaks.
-John C.
April 25, 2006 at 8:31 am
Sometimes yes, and sometimes no. Depending on the app and exactly what it does, it may be more efficient to allocate and de-allocate as you go. For instance, if you have a couple dozen different stored procs that follow the 80-20 rule (i.e., 80% of the procs get used 20% of the time), it's probably better to only allocate the uncommonly used SqlCommand objects when you need them.
Also, releasing and recreating the SqlCommand objects is the surest way to make sure you've completely refreshed all the options and parameters.
As with most optimizations, there are rules, there are suggestions, and there are exceptions. Each application uses a little from each category.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply