Manage transactions

Transactions are data operations that need to succeed or fail as a group. The usual example is the money transfer: the given amount must be subtracted from an account, and added to another. If an exception occurs during the transfer, everything should be rolled back. No one would tolerate money which evaporates on the internet…

If you’d like to write a transaction manually in C#, using the .NET Framework, there is nothing in your way. You should use the DbTransaction base class, or one of its provider-specific derivatives to implement your custom transaction logic. There are three steps to follow here:

  1. Create the transaction
  2. Execute the commands which are parts of the transaction
  3. Commit or roll back the transaction, depending the outcome

A quick example:

SqlConnection theConnection = new SqlConnection(theConnectionString);
SqlCommand theCommand = new SqlCommand(theCommandText, theConnection);
SqlCommand theCommand2 = new SqlCommand(theCommandText2, theConnection);
using (theConnection)
SqlTransaction theTransaction = theConnection.BeginTranscation();
theCommand.Transaction = theTransaction;
theCommand2.Transaction = theTransaction;
catch(Exception ex)

Continue reading “Manage transactions”

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.

Continue reading “Work with strongly typed DataSets and DataTables”

Expose a DataTableReader from a DataTable or from a DataSet

As DataReaders were ideal to read information from a connected database quickly (in a read-only, forward-only manner), DataTableReader does the same for your disconnected DataTables. The magic words (which I can’t underpin enough) are read-only, forward-only.

You work with the DataTableReader exactly the same way as you’ve done with DataReaders. The main difference is instantiation. You need to call the CreateDataReader method of a DataSet or a DataTable. When you call the DataSet method, you have the opportunity to pass an unspecified number of DataTable instances as parameter (with the help of the params keyword). Then you can call the NextResult method of DataTableReader to jump to the next table.

An example of how to do it:

DataTable myTable = new DataTable(“MyLittleTable);
SqlDataAdapter myAdapter = new SqlDataAdapter(aValidSqlCommand);
DataTableReader theReader = myTable.CreateDataReader();
for(int i=0; i<theReader.FieldCount; i++)
Console.WriteLine({0}: {1}”, i, theReader.GetValue(i));

Continue reading “Expose a DataTableReader from a DataTable or from a DataSet”

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:
Continue reading “Programmatically create data objects”

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!

Handle special data types

There are some data types introduced to SQL Server for use in special cases, like storing large objects in a database, or accessing the same objects stored in the file system through a database, and for working with spatial data. Hopefully, you won’t have to be able to write complex queries with geographical data involved for this exam, but you definitely should be able to select the right data type from a list of possible candidates. In this post, we’ll prepare ourselves for these kind of questions.

There is a beautiful acronym, BLOB, which stands for Binary Large Object. BLOBs can be MP3, or video, etc. As long as something is large and binary, it is valid to call it a BLOB. There are two places (from the aspect of ADO.NET) to store BLOBs. The first one is a database, the second is the file system. To work with BLOBs stored in a database, you just have to make yourself familiar with the following SQL types:

  • varbinary(max): stored data is in binary format
  • nvarchar(max): stored data is in text format

Continue reading “Handle special data types”

Manage data by using the DataAdapter or the TableAdapter

So far, we’ve only worked with connected data. It’s time to take a brief look on its disconnected state, and how to handle it. The classes involved in them are organized around the DataSet, which will be the topic of another post. For now, we only take a look at how to pull data from the data source into a DataSet. A task that is handled by DataAdapter, or in more special scenarios, the TableAdapter.

You can instantiate DataAdapters by passing a command text and a connection string to the constructor. Of course you can use a single Command object (which incorporates both of them), or pass a command text and a Connection object, or pass two strings… The point is that the DataAdapter must have a connection string and a select command to start working.

You can view a DataAdapter as a set of command objects well encapsulated. DataAdapters provide methods of all database-related commands, and expose events for them. The most important method of the DataAdapter is called Fill. It has many overloads, and the purpose of it is to push data into the DataSet/DataTable from the data source.

DataAdapters are capable of working with multiple result sets. When they encounter them, they create data tables for each of them. The returned tables will be called Table0, Table1, and so forth. If you’ve passed a name, the names will be TableName0, TableName1, etc.
Continue reading “Manage data by using the DataAdapter or the TableAdapter”