Tag Archive | SqlCommand

Update data

Now this one seems to be a huge topic, but have no fear, we’ll overcome it. The “may include, but is not limited to” section says: update data by using stored procedures, update DataSets to data source, managing concurrency.

Updating data by using stored procedures is not the hardest task in our lives. Simply build a SqlCommand object, set the CommandType to StoredProcedure, and use parameters (always use parameterized SQL commands, because SQL injection attacks will haunt you in your dreams if you wouldn’t do so). I think this one is out.

To update DataSets (and all the related stuff) is a bit more complicated. You should use DataAdapters (or TableAdapters, strongly typed or not) to solve this issue. DataAdapters expose some properties related to updated data, such as UpdateCommand, or DeleteCommand and InsertCommand. If you remember what I wrote about the states of DataRows, you can easily put the pieces together. If not, here’s a brief refresher:

When you call the Update method of the DataAdapter, it will treat DataRows differently, based on their RowState property. On the ones marked Deleted, it will call the DeleteCommand. In a similar way, Modified rows will be processed by the UpdateCommand, and Added will be by the InsertCommand. Watch out of this, because there may be exam questions lurking out there on this topic.

Read More…

Advertisements

Build command objects

DbCommand is the object which plays the same role in working with data sources as DbConnection does in connecting to them. Everything that’s related to dealing with data can be done by using command objects, like selecting, inserting, deleting or updating.

As you could build connection objects with DbConnectionBuilder, you can do the same with DbCommand and DbCommandBuilder. But let’s talk about SqlCommandBuilder, instead of DbCommandBuilder.

You can pass a SqlDataAdapter object into the constructor. This is particularly useful if you’d like to generate automatic texts for the insert, delete and update commands, depending on the select command you specified. Consider the following code:

SqlConnection theConnection = new SqlConnection(“connectionstring”);
SqlDataAdapter theAdapter = new SqlDataAdapter(“SELECT * FROM Customers”, theConnection);
SqlCommandBuilder theBuilder = new SqlCommandBuilder(theAdapter);

And after this code, you are able to call the GetUpdate/Delete, etc command methods, which returns a pregenerated, strongly typed SqlCommand object depending on the conflict option and the SELECT command specified.

It is possible to execute commands in different ways. There are four methods you should be familiar with:
Read More…