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
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.
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:
Some ADO.NET classes supports built-in asynchronous query behavior. Some aren’t. In this post, we’ll revise the former classes, like SqlDataReader. However, if you are familiar with multithread computing (particularly, in the .NET Framework) you can easily implement asynchronous execution in classes which don’t support it, such as the TableAdapter.
Before you’d do anything, you should introduce a new name-value pair into your connection string: Async=true or Asynchronous Processing=true. Including it, you enable asynchronous queries to be executed on the database server. This was the easy part.
Writing your asynchronous data access class is a little bit more complicated. If you are familiar with delegates and the classes of System.Threading, then you are ready to go. But if you’d like to rely on built-in logic, consider using the DbCommand’s BeginExecuteReader and EndExecuteReader. First the code, then the explanation:
DataReader classes provide a forward-only, read-only cursor over your returned data. They are particularly useful when the need of fast querying arises. Remember the two keywords: forward-only, which means that once you’ve retrieved your data, you cannot turn back on the middle of an enumeration, and read-only, which means that the DataReader only supports selection of data.
Working with a DataReader is by all terms easy. Let’s see a quick example:
SqlConnection theConnection = new SqlConnection(@”Data Source=…”);
SqlCommand theCommand = new SqlCommand(“SELECT * FROM X”, theConnection);
theCommand.CommandType = CommandType.Text;
SqlDatReader theReader = theCommand.ExecuteReader();
for(int i=0; i<theReader.FieldCount; i++)