Need help with Stored Procedures being called from Client Code.

[Manny]

Hi there,

I created the stored procedures for Select, Insert, Update and Delete
I got the Select Stored procedure to select data from the database with no
problem. By the way, I'm kind of new to stored procedures.

So, I want to Insert, Update or Delete records from my Windows Application
using the above created stored procedures. I was using Dynamic SQL before
and decided to move to stored procedures for many reasons, one of them and
very important for me is performance and security. Now as I was saying, when
I call the Update command on the DataAdapter I got the message, depending of
what I'm doing, that I need to supply a valid Update, Insert or Delete
command to be performed.

So I guess that it's not as simple to call the Update command to update the
database, as I was doing it with Dynamics SQL statements????
How can I accomplish this in code? to be able to call just the update and
have my database updated through the stored procedures.

Thanks very much.

Manny

 

[Sahil Malik]

Manny,

Looks like you are using a SqlDataAdapter.

Well, SqlDataAdapter has 4 properties -

SelectCommand/UpdateCommand/InsertCommand/DeleteCommand.

When you set the above, and call SqlDataAdapter.Update() it will call the
above as per the rowstates of the dataset/datatable you have passed in. Now
those commands might be DynamicSql or Stored proc - doesn't make a
difference.

Per the error you got, I think you didn't set the
SqlDataAdapter.UpdateCommand property to a SqlCommand representing your
update statement.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[W.G. Ryan eMVP]

Manny:

Although I totally advocate the use of Stored Procs for many reasons,
Performance and Security aren't a slam dunk with procs - you can do the same
with Paramaterized SQL --- but that's another story.

Like Sahil said, you need to set a command for each operation that you are
going to perform. In general, if you are planning to call Update, then
you'll need all four commands because if you delete a row for instance, the
rowstate will be marked as Deleted. When Update is called the adapter will
look for a delete command and if it's not there, you'll have drama.

I'd recommend using the DataAdapterConfiguration wizard the first time and
look at the code it generates. Not only are you going to need to set an
update command, you'll need to set up column mappings and the configuration
wizard writes this code for you. After you see it once, it's pretty clear
how it works - it definitely helped me out a lot.

 

[Manny]

Thanks for your inputs guys. The update command I believe it'll go on the
commandText property of the sqldataadapter or doesn't? because when I
generated the sqldataadapter with store procs, the wizard places the name od
the store proc on the commandText property of the command, whatever this
maybe, update, insert, delete and select.

Now I looked at the code the wizard generated, but I just can't figure out
where should I place the let's say the update command for the command. I'm
definitely confused now.

Please some more help

Manny

 

[Sahil Malik]

Manny,

The command goes in the commandtext property of the _updatecommand_ of
SqlDataAdapter.

Similarly there are 3 more properties - DeleteCommand,InsertCommand and
SelectCommand (for fill).

 

[Manny]

Thanks for your respose Sahil,

Now, I'm not sure wht it goes in the CommandText. Should I put the whole SQL
statements there? for example the SQL statements that updates the database,
but I have all those SQL statements in my stored proc!!

Thanks,

Manny

 

[Manny]

Thanks Sahil, I finally figured it out.

Thanks for you inputs guys.

Manny

 

[W.G. Ryan eMVP]

Each command property will get its own SQL Statement or procedure name.
However the Paramaters will be different for each of them that's where the
columnmappings come in