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.

Work with strongly typed DataSets and DataTables

I think every developer working with any programming languages and frameworks knows the benefits of strongly typed classes. This is particularly true in the .NET Framework, where string literals fall outside the scope of compiler checks, and given this, it’s easy to introduce errors which show up run-time only.

When you are working with DataSets (and the classes of System.Data), it is much more easier to generate these types of errors, because of the massive number of indexers used in this part of the framework. Think about how you’d retrieve DataTables from a DataSet, or DataColumns from a DataTable. Something like this:

DataTable dt = DataSet.Tables[“myTable”];
DataColumn dc = dt.Columns[“myColumn”];

Two possible places where typos can raise hell. Even worse, when dealing with untyped DataSets, casting is heavily used:

DataRow dr = dt.NewRow();
dr[“ProductID”] = 1;
int ProductID = (int)dr[“ProductID”];

Fortunately, there is a solution. However, this is not part of the .NET Framework, and has nothing to do with the System.Data namespace. It is a feature of Visual Studio, and it’s been around since version 7. It is called a strongly-typed DataSet. The main benefits are:

• Type safety: no casting, and you can use your custom types safely.
• IntelliSense support: no more typos in strings, and faster lookups, since columns are properties of rows, etc.
• Wizards: Visual Studio provides powerful wizards to generate your strongly-typed DataSet in seconds.
• Separation: Visual Studio watches out not to hard-code your connection strings. Instead, it creates a custom entry for the generated connection strings in the connectionStrings section of your .config file.

Programmatically create data objects

Today is the great DataSet day, which means I hope that I can revise and publish all the following objectives from the syllabus:

• Programmatically create data objects
• Work with untyped DataSets and DataTables
• Expose a DataTableReader form a DataTable or from a DataSet
• Work with strongly-typed DataSets and DataTables

So much for announcements, let’s dive into the magical world of DataSets (and how to create them programmatically). As you could guess, creating a DataSet takes a single line of code, which is the following:

DataSet ds = new DataSet();

We have one overload of the constructor, which accepts a string as a parameter, and can be used to specify the name of the yet-to-be instantiated DataSet. A little hierarchy: on the top, lives the DataSet. It can have multiple (unlimited) DataTables, and can define any number of DataRelations between these DataTables. DataTables can be further divided to DataRows, DataColumns, and Constraints. They also expose a set of properties, which help you to control the primary keys, DataViews, etc. of them. Let’s not waste more time, and create a full-blown DataSet with two DataTables, a DataRelation, and the rest:

Manage occasionally connected data

As I’ve stated before in a number of posts, there are two types of data retrieved from a database: connected, and disconnected. Connected means that you query and update data in real time, thus you are dealing with up-to-date information. However, connected data has some severe limitations, aside from its benefits.

The biggest limitation, which is the biggest benefit in the same time, is that connected data requires an opened database connection. This is sometimes impossible, especially when you are working with data sources that aren’t present in your computer, but sit on remote servers. Sometimes, you can’t access these data sources, and you have two choices: you will only use your application when you are able to create a connection between it and the database server, or you’ll use a disconnected data managing solution. Fortunately, ADO.NET has one, well tailored to your needs.

The heart of all ADO.NET disconnected data classes is the DataSet. Everything dealing with disconnected data is in (or can be put in) context with it. You can store information in your DataSets, serialize them into and from XML files, etc. Even better, when you’ve finished editing your data locally, you have the option to send back everything updated to the remote database server, without writing a single query to do so.

This post was just the introduction of what is yet to come. The next few (few means four now) posts will deal with the features and the handling of the DataSet and all of its related classes: DataTable, DataRelation, DataColumn, DataRow, etc. Stay tuned!

ADO.NET – The Disconnected Layer

In the previous post, we discovered the connected layer of ADO.NET, namely the Command, Connection, DataReader classes. In this post, I’d like to review the disconnected layer of ADO.NET. The main classes are the DataSet and the various DataAdapter classes in this layer. But when should we use this one?

As noted previously, database connections are probably the most expensive resources in a given application. Therefore, it’s a good idea to use a limited number of queries for retrieving large pieces of data, than use numerous queries for little amount of data. The disconnected layer is built upon this theory. Large pieces of data (in fact, even a whole database) can be pulled to the client, then processed without the requirement of an open connection to the database. When the processing of data is completed on the client, it can send back the changes committed, and the database can be updated with these changes. Now let’s see the class that provides all of these features: