Top 12 Smart Tips To Work With SQL

Your users probably don’t know a thing about SQL, but you know its value. It’s everywhere, it’s easy to learn, and SQL solutions are simple to implement. Whether you use SQL a lot or sparingly, working smarter with SQL will help you avoid errors and improve performance.

Many SQLs are vendor-specific. The following tips apply to Jet and Transact-SQL, but most SQL products are similar.

1: Working with Jet SQL in the Access SQL window

Access users create SQL statements every time they create a query; they just don’t know it. They’re using the Query Design window, a graphic representation of the actual SQL. Most users can’t create the appropriate SQL statements they need, so the graphical interface is helpful, but it can help you too. If you can’t create error-less SQL code off the top of your head, let the SQL window help out:

  • Create a query using the Query Design window and then copy the code from the SQL window into a VBA module for use in code.
  • When a query in code returns one of VBA’s meaningless error messages, copy the SQL statement to the SQL window and run it. Access will usually return better information about the error than VBA.

You can’t always copy a SQL statement straight from the module to the SQL window. If the statement includes concatenation or variables, add a Debug.Print statement right after the definition statement. Then, if you need to debug the statement, copy the evaluated statement from the Immediate window. For instance, the following statement in code won’t run in the SQL window because of the variables and concatenation:

However, if you follow the statement with a Debug.Print statement, you can copy the evaluated results, which follow, from the Immediate window:

2: Words reserved by SQL

SQL reserves several words, such as keywords. Avoid using these words to name columns, tables, variables, or any objects. Their use outside of the context in which the engine expects them confuses the engine and generates errors, or worse — incorrect results.

3: The difference between ALL, DISTINCT and DISTINCTROW

SQL’s SELECT statement supports three predicates: ALL, DISTINCT and DISTINCTROW. ALL is the default and returns all records that fulfil any specified conditions. DISTINCT limits the results to unique values in a specific column or columns. For instance, the following statement would return only one record for each LastName value:

In other words, if you have a record for both John and Mary Smith, this statement returns only one record for Smith. However, DISTINCT works with all the columns, not just the column that immediately follows. That means, this statement will return a record for both John and Mary because the combined columns don’t produce a duplicate record:

If the SELECT clause references more than one column, the combination of values from all the selected columns must be unique for a given record.

DISTINCT has a few quirks you should know about but might not expect:

  • Don’t use DISTINCT with the asterisk character (*). You must specify columns by name.
  • Any query using DISTINCT can’t be updated, which makes sense.

While DISTINCT works with columns, DISTINCTROW works with records. (Transact-SQL doesn’t support DISTINCTROW.) This predicate has a few limitations of its own, which can make an error hard to troubleshoot:

  • The engine ignores DISTINCTROW if there’s only one table in the query.
  • The engine ignores DISTINCTROW if you reference all columns from all of the underlying tables.

4: Retrieving only what you need

It’s tempting to use the asterisk character (*) when retrieving data via a SELECT clause, but don’t, unless you really need to retrieve all columns. The more data you retrieve, the slower your application will perform. For optimum performance, retrieve only the columns you need.

5: Variations in aggregates

Both SQL and VBA support aggregate functions, but not the same aggregates. Although they aren’t interchangeable, you can often use one or the other. Table A compares the two types within the context of Jet and Transact-SQL.

Table A

T-SQL Jet VBA Explanation Considers Null
Avg Avg DAvg Returns the mean or average of the specified domain No
Count Count DCount Counts the number of non-Null values in the specified column No
Count(*) Count(*) DCount(*) Counts the number of rows Yes
Sum Sum DSum Totals the values in the specified column No
Min Min DMin Returns the smallest value No
Max Max DMax Returns the largest value No
First Returns the value from the first row in the specified column Yes
Last Returns the value from the last row in the specified column Yes
StDev StDev DStDev Returns sample standard deviation No
StDevP StDevP DStDevP Returns population standard deviation No
Var Var DVar Returns sample variance No
VarP VarP DVarP Returns population deviation No

Don’t use VBA’s domain aggregates when a SQL aggregate will do. When you must use VBA’s domain aggregates, apply an index to the underlying column for best performance. Keep in mind that although SQL’s GROUP BY doesn’t eliminate null values, most SQL aggregates don’t evaluate them. If you want null values considered, you must accommodate them in the expression.

6: GROUP BY considerations

SQL’s GROUP BY clause defines subsets of data. The most important thing to remember when including a GROUP BY clause is to include only those columns that define the subset or summarise data for the subset. In other words, a GROUP BY can’t include extraneous data. For instance, to learn the number of orders placed on a specific date, you’d use a statement similar to the following:

This query would return one record for each date. Each record would display the date and the number of orders for that date. You can’t include any other columns.

GROUP BY is versatile. You don’t need to specify a column in the SELECT clause to group by it. For instance, you could omit OrderDate from the above query and return just the count for each date (although the results wouldn’t make much sense). As long as the GROUP BY column is in the source, SQL doesn’t require it in the SELECT clause. On the other hand, if you refer to a column in the SELECT clause, you must also include it in the GROUP BY clause or in an aggregate function. For instance, the following statement doesn’t work because the Freight column isn’t part of an aggregate or the GROUP BY clause:

In truth, it doesn’t really make sense to try to include a column in this way. If you want the Freight data within the context of a GROUP BY query, you probably want a summary of the freight values in the group, as follows:

Jet can’t group a Memo or OLE Object column. In addition, you can’t include a GROUP BY clause in an UPDATE statement, which makes sense. SQL would have no way of knowing which record to update.

7: Altering table structure

There are several SQL statements for altering the design of a table. Always approach such a task with care because you could destroy data. For instance, if you change a column’s data type, the engine might truncate or delete existing data to accommodate the new column’s data type. Keep the following limitations in mind when altering tables:

  • You can’t alter an existing column’s data to COUNTER if the column already contains data.
  • You can’t rename a column. You must remove the column using DROP COLUMN and then re-create it using the new name. To preserve the data, create the new column, copy data from the original column using UPDATE, and then delete the original column.
  • Before altering a table using ALTER TABLE, close it. Otherwise, the engine will return an error. The following VBA code will close an open table and inhibit the error that’s generated if the table isn’t open:

  • You can’t delete a column if a constraint exists on that column. First, delete the constraint using DROP CONSTRAINT.
  • Remove a CHECK CONSTRAINT before deleting the table to which it applies.
  • You can’t modify an existing relationship. You must delete it and then re-create it.

8: SQL’s problem child, INSERT INTO

SQL’s INSERT INTO statement lets you add new data to an existing table. Used correctly, INSERT INTO works great, but you must remember one critical thing: although the table designs don’t have to match, the specific columns identified on both sides of the task must match. In short, the table that receives the new data must contain the same columns as the incoming data.

You can also explicitly identify each column using the VALUES clause as follows:

However, this syntax adds only a single row at a time, so it will have limited use. You can omit the column references only if you supply a value for every column in target. When including the column references, their order must match the order in the table definition. You must include the primary key column, unless that key is an auto-numbering column — it isn’t necessary to include an auto-numbering column.

9: Using UPDATE to delete values

SQL’s DELETE statement deletes entire records. The statement won’t return an error if you specify a list of columns, but it will ignore the list. For instance, the following statements would delete all the data in a table named Employees:

Even though the last syntax specifies Salary, SQL will delete all the data, not just the Salary values. To delete specific values, use SQL’s UPDATE in the form:

However, even this will return an error if column properties are in conflict. For instance, if a column requires data, it will reject Null.

10: Dropped properties with SELECT INTO

SQL’s SELECT INTO creates a new table by copying rows from an existing table using the following form:

However, this statement doesn’t copy the table exactly. It won’t copy the original table’s primary key, indexes, or column and table properties (beyond the default settings). In addition, it ignores Caption settings and uses the original column names.

When including a SELECT INTO statement, keep in mind that this statement will replace an existing table if one of the same name (newtable) exists within its scope. Fortunately, SQL will warn you first. In some cases, the engine will delete the existing table before it actually creates the new one, so if something goes wrong, you can’t rely on the original table because it’ll be gone. Before running a SELECT INTO, it’s a good idea to make a copy of the existing newtable, if one exists. In addition, if newtable is open, SQL will return an error.

You don’t have to copy data to the new table. You can create a new empty table by adding a WHERE clause as follows:

SQL will create newtable but copy no data to it because no record can satisfy the FALSE condition.

11: The difference between WHERE and HAVING

The WHERE and HAVING clauses perform similar functions but they aren’t interchangeable. WHERE limits the data returned by the SELECT clause; therefore, a GROUP BY is inconsequential. The engine compares data and eliminates records that don’t satisfy the WHERE clause before it groups the records. On the other hand, the HAVING clause eliminates data that doesn’t satisfy the grouping criteria.

If you have trouble remembering which clause to use, remember that the WHERE clause is positioned before the GROUP BY clause and the engine applies the WHERE clause before grouping the records.

12: UNION match-up

SQL’s UNION operator lets you combine records from different sources using the following form:

The important thing to remember with a UNION is that the column order in both SELECT statements must match. The column names don’t have to match, but each list must contain the same number of columns and their data types must be compatible. If the data types don’t match, the engine sometimes chooses the most compatible for you. The results might work, but then again, they might not.

By default, UNION sorts records by the values in the first column because UNION uses an implicit DISTINCT predicate to omit duplicate records. To include all records, including duplicates, use UNION ALL, which eliminates the implicit sort. If you know there are no duplicate records, but there are a lot of records, you can use UNION ALL to improve performance because the engine will skip the comparison that’s necessary to sort (to find duplicates).

Author: Susan Harkins

If you’ve got any thoughts, comments or suggestions for things we could add, leave a comment! Also please Subscribe to our RSS for latest tips, tricks and examples on cutting edge stuff.

Best Practices of ActiveX Data Object (ADO)

ADO – ActiveX Data Object is a library to manipulate data i.e. retrieving and storing records. Being a Microsoft library ADO offers great ease to help developers with their database queries. ADO is quite flexible and powerful as it doesn’t let the developer bother how the database is implemented. They can straightaway use the database if you can successfully connect to it. Although, ADO provides a great set of features to be used but here are best practices to use ADO objects efficiently to get the best out of it.

How to Instantiate Objects In ADO

Don’t use shortcuts while creating a new objects. If you use shortcuts then Visual Basic works harder to find out the object. If you use the following

The above method will create a new ADO connection and then assign to string conn. I would recommend you to use in the following manner

This will perform better than the its tempting shortcut.

Whether or Not to Qualify the Library

ADO and DAO share many of their objects so there is a chance that you won’t get the intended object if the library isn’t explicitly declared. Instead of using

use the following

Calling the library explicitly removes any chance of wrong reference.

Data Source Name (DSN) Connections Should Be Avoided

For a single connection to the database DSN will work perfectly fine but in case of large applications where you need to connect multiple times DSN will drastically slow your application down. You should avoid specifying all the connection details in code by referring to a DSN. However, there are few limitations to it i.e.

  1. You must install the DSN on each client system. Updates must be made to each client.
  2. They require a hit to the Registry.
  3. They often take longer to connect than OLE DB.

Don’t Forget to Release Connections

OLE DB establishes a new connection every time you connect. If you fail to release those Connection objects, you could eventually run out of connections. After you close a connection be sure to set each Connection object to Nothing as follows:

Do Not Use Recordset Data To Update Your Database

Use stored procedures or SQL statement to update your database rather than Recordset. Recordset objects are very costly and requires lot of resources. Ideally, stored procedures are best for updates, deletes etc. for the following reasons:

  1. A stored procedure is faster than an equivalent action query.
  2. Stored procedures cache execution plans, so they perform better.
  3. Stored procedures can handle multiple insert, update and delete operations, which reduces network traffic. Combine multiple tasks into one stored procedure, when possible. One trip of many tasks will (usually) perform better than several short trips.
  4. You can call a stored procedure from different applications.

OLE DB Providers Should be Preferred

Native OLE DB providers are best when it comes to database connections. As they give you access to properties and methods which others connections fails to provide.

Think About Efficient Cursors in ADO

Default cursor in ADO which is forward-only performs best and require very less overhead. When you are not using forward-only cursor, you have a choice to choose from the following cursors:

  1. adOpenStatic – provides a static copy of the records (you can’t see additions, changes or deletions by other users), but all types of movement are enabled.
  2. adOpenDynamic – is a dynamic cursor that requires more overhead, because updates are immediate and all types of movement are enabled. The dynamic cursor isn’t currently supported by the Microsoft Jet OLE DB Provider (Access), and therefore defaults to a keyset cursor if you specify adOpenDynamic against a Jet database.
  3. adOpenDynamic – is similar to the dynaset recordset type and dynamic cursor, but the resulting recordset is based on a single table. Since the dynamic cursor type isn’t currently supported by the Microsoft Jet OLE DB Provider, Jet defaults to a static cursor when you apply the adCmdTableDirect option.
  4. adOpenKeyset – allows you to update existing records at the time of creation, but you can’t see additions or deletions. All types of movement are enabled.

Record Count Property Should be Avoided with Forward-Only Cursor

ADO returns -1 instead of an error if you try to record count with the forward only cursor which is bit strange. You may try to count the record with other cursors too but unfortunately the results may not be correct. When the RecordCount property doesn’t return the right number, add a MoveLast method. Either way, moving through the records can take awhile, so avoid counting records during heavy processing times.

Connection Should Be Used Whenever Possible

You can pass a connection string to a Command, Recordset or Record object and in doing so, implicitly create a Connection object each time. A more efficient solution is to use one Connection object and pass it to each object when an active connection is required.

Read Connection String

An open Command, Recordset or Record object can return its connection string using the following statement:

Use a MsgBox() function or Debug.Print statement to display the statement’s results. Or use the above statement to specify the connection string in other statements or to return information about the connection.

Ideally there are various tips to improve ADO performance which i might write in my next post. If you’ve got any thoughts, comments or suggestions for things we could add, leave a comment! Also please Subscribe to our RSS for latest tips, tricks and examples on cutting edge stuff.

FileSystemWatcher to monitor directory changes in C#

You must have performed certain tasks where you need to determine if the files or directories are created or even modified.

One example of this is an import system that pulls flat files into a database. In this instance, a program must monitor a directory for newly created files. When a file is created, the program must pick up the file, parse it, and insert it into the database. Many times this type of functionality is accomplished by “polling” the directory and enumerating any new files that have been created since the last poll. The .NET Framework has provided developers with an alternative to constantly polling a directory for new files — the FileSystemWatcher object.

The FileSystemWatcher object does the work of monitoring a directory for you. When a file is created, updated, or deleted, the FileSystemWatcher fires an event to notify you that a change has occurred. This allows your program to know when a new file is available almost immediately after the file is created. Immediate notification of changes allows your system to work much more efficiently since you’re not constantly polling the directory for changes, and there is no time lapse between scans of the directory.

The FileSystemWatcher basics

There are a few basic properties and events you need to familiarize yourself with before working with the FileSystemWatcher object. Undoubtedly, the most important property of this object is the “EnableRaisingEvents” property. This property determines whether or not the object will fire events when it receives a change notification. If EnableRaisingEvents is set to false, the object will not fire the change events. If it is set to true, the events will be fired. Below are several other important properties/events that you will use as you take advantage of FileSystemWatcher:


  • Path — This property tells the FileSystemWatcher which path it needs to monitor. For example, if we set this property to “C:\\Temp\\”, all changes in that directory would be monitored.
  • IncludeSubDirectories — This property indicates whether or not the FileSystemWatcher should monitor subdirectories for changes.
  • Filter — This property allows you to filter the changes for certain file types. For example, if we wanted to be notified only when TXT files are modified/created/deleted, we would set this property to “*.txt”. This property is very handy when working with high-traffic or large directories.


  • Changed — This event is fired when a file has been modified in the directory that is being monitored. It is important to note that this event may be fired multiple times, even when only one change to the content of the file has occurred. This is due to other properties of the file changing as the file is saved.
  • Created — This event is fired when a file is created in the directory that is being monitored. If you are planning to use this event to move the file that was created, you must write some error handling in your event handler that can handle situations where the file is currently in use by another process. The reason for this is that the Created event can be fired before the process that created the file has released the file. This will cause exceptions to be thrown if you have not prepared the code correctly.
  • Deleted — This event is fired when a file is deleted in the directory that is being watched.
  • Renamed — This event is fired when a file is renamed in the directory that is being watched.

Note: none of these events will be fired if you do not have EnableRaisingEvents set to true.

If at any point your FileSystemWatcher does not seem to be working, check EnableRaisingEvents first to make sure it is set to true.

Event processing

When an event handler is called by the FileSystemWatcher, it contains two arguments — an object called “sender”, and a FileSystemEventArgs object called “e”. The argument we’re interested in is the FileSystemEventArgs argument. This object contains information about what caused the event to be fired. The following is available from the FileSystemEventArgs object:


  • Name — This property contains the name of the file that caused the event to be fired. It DOES NOT contain that path to the file — only the file or directory name that caused the event to be fired.
  • ChangeType — This is a type of WatcherChangeTypes and indicates which type of event was fired. Valid values are:
    • Changed
    • Created
    • Deleted
    • Renamed
  • FullPath — This contains the full path to the file that caused the event to fire. It includes the filename and the directory name.Example codeThe code below is a simple example of how to use the FileSystemWatcher. In this example we are watching the “C:\\Temp” directory for any *.TXT files that are created. This is probably the simplest use of the FileSystemWatcher.


The uses of the FileSystemWatcher may not be obvious at first. Obviously, it will alert us that a file or directory has been modified, but in what situations would that information be necessary?

One situation where it could be used is in a system that allows clients to upload files to be processed. While this is obviously the age of web services and SOA, many systems still work by being fed flat files to certain locations, whether those locations are an FTP server or a network share. I have used the FileSystemWatcher for this type of system, and while it’s not flawless, it certainly has its advantages over polling the directories for new changes.

Another situation where it could be used is if you want to keep two files in sync with one another. You could use the FileSystemWatcher to monitor both file locations and fire a Changed event when one of them is updated. You could then copy the changes over to the other system/file.

If your system has any functionality that requires files or directories to be monitored, you should look closely at that component to see if the FileSystemWatcher could be of use. In my case, this object saves at least 10 seconds on every file that is sent to the system.

Serialize hash table in C# dynamically

I have realized while working on a problem that .NET Framework does not allow serialization of any object that implements the IDictionary interface. I was very much disappointing with the restriction but later i figured out that the restriction is not on hash tables. There are few ways which leads people to follow the following steps in order to  solve this problem

  • Few developers prefer to find an alternative to hash tables
  • Few developers implement the IXmlSerializable interface in a custom object and wrap it around the hash table.
  • My approach is to use a generic List<T> object as a base and implement the standard features used in a hash table within that object. The List<T> derivative can then be serialized.

Using List<T> as a base

List<T> objects are serializable, and this serialization is automatically handled in the .NET Framework. This means we will not have to implement our own serialization routines, and it’s one of the reasons I decided to use List<T> as a base for my solution.

Using List<T> also provides you with some “free” functionality as a result of inheritance:

  • Automatically keeps track of the number of entries
  • Gives us the ability to use the Find methods (FindAll, FindLast, Find, etc….)
  • Gives us the ability to use the Exists method
  • Gives us the ability to use Foreach() looping
  • Gives us the ability to use Sorting (and the ability to guarantee the sorting of objects within the list)

So obviously, the List<T> object gives us a nice start. However, there are still a few methods we need to implement within our object so that we have the basic hash table functionality:

  • An Add(key, value) method, which allows you to add values to the table
  • The ability to pull a value via a key (like the hash table’s indexer)
  • The ability to remove a value via a key
  • The ability to see if a key already exists or not

There are also a few other features that will make using this object easier:

  • A constructor that accepts a hash table and enumerates through the hash table to populate the object
  • A property that returns a hash table

With that list of features, we now have an object that supports the most commonly used hash table functions, can be translated automatically to/from a hash table, and is also serialisable. I chose to call this object the OrderedTable.

The OrderedTable object’s code

The first step in coding an OrderedTable is to inherit from the List<T> object and mark the object as serialisable (See the code below).

You’ll notice that this inherits from NameValuePair, which is a simple object with two properties — Name and Value. This object allows us to store/access the List’s data in a similar way that a hash table does.

The next step will be to create a constructor that accepts a hash table and converts it into an OrderedTable. The code for this constructor is shown below.

This function uses the GetEnumerator method on the hash table object to get a reference to an IDictionaryEnumerator interface. The IDictionaryEnumerator interface is then used to loop through the hash table’s data. Each key/value pair in the hash table is then added to the OrderedTable.

At this point, we need to create an Add() method that can add data into our OrderedList. The code for this is shown below.

This method simply accepts a key/value and then creates a NameValuePair object to add to the OrderedTable. You’ll notice that this method uses base.Add — the reason for this is that we want to add the NameValuePair object to the base List<T> object.

The indexer on the OrderedTable accepts a key and returns the value associated with that key. This is similar to how the hash table’s indexer works. The code for this is shown below.

This property uses the Find() functions as mentioned above to execute a delegate against the objects (NameValuePairs) stored within the OrderedTable. After the correct object is found, we either set the value or return the value depending on which accessor we’re in.

The last important property in the OrderedTable class is the HashTable property. This property takes care of the work of converting the OrderedTable back into a hash table. The code is shown below.

This property loops through the current OrderedTable and adds objects to a hash table for each object in the OrderedTable. It then returns the fully populated hash table.

Serialisation example

The code below is an example of first creating a hash table, converting it to an OrderedTable, serialising the OrderedTable, and deserialising the OrderedTable to give us access again to the hash table.

The code below shows what the OrderedTable.xml looks like after the OrderedTable is serialised.

Leave me a comment and let me hear your opinion. If you’ve got any thoughts, comments or suggestions for things we could add, leave a comment! Also please Subscribe to our RSS for latest tips, tricks and examples on cutting edge stuff.

11 reasons to use Access and few not to

The truly smart and effective IT professional knows that there are many tools. The key is to know which database engine is the best for the job at hand. Why throw massive resources at a simple need? In other words, you don’t want to pay for a Rolls Royce engine if you’re building a go-cart. Knowing when and when not to use Access will help your budget and make you look good, whether you’re a freelance developer or you’re managing IT resources.

1: It’s cheap

Access is a desktop database and its place in the hierarchy — layered between Excel and SQL Server — determines its price. Access costs the same as any other desktop application. Acquiring a copy of Access won’t require a loan or a call to investors.

The large client-server databases, such as SQL Server and Oracle, require specific hardware and expensive licensing. After the project’s up and running, a client-server database needs a dedicated administrator. Access requires none of that.

On the other hand, Access is a desktop application. That means that everyone who uses a customized database application needs Access installed on their local system. All those copies of Access can be expensive. One alternative is to invest the time and money it takes to turn your database into a runtime application. (Access 2007 doesn’t support this option.)

2: It’s easy

Anybody with just a bit of time and reasonable intelligence can learn to use Access. It doesn’t take weeks of classroom instruction and then months of mentored on-the-job training to acquire the skills necessary to create and administer a database. It’s safe to say that most Access databases have one user and they live out their lives on one system. The user generally creates the database in his or her spare time. The casual user with no professional database or development skills can get data into an Access database and then manipulate that data without blowing up the building.

A good database grows and a bad one dies — regardless of the data engine that’s driving it or the skill set of the person who created it.

3: Development costs less

Many developers make a good living creating custom database applications in Access. (Call me, let’s talk.) However, in general, they charge less than SQL Server and Oracle developers. Moreover, the development costs are just the beginning if you go with SQL Server or some other client-server software (see #1). If you really want to use Access and you’re smart, you’ll see that an enthusiastic and eager employee gets the right training. Then, pass out all that money you save in employee bonuses.

On the other hand, it doesn’t matter how much money you save initially, if you use the wrong database. Don’t let money be your only consideration or you’ll surely regret it. For instance, the security model is minimal (and doesn’t exist at all in Access 2007). Recovery isn’t as easy, either. Don’t use Access for mission-critical applications unless you really know what you’re doing — and even then, it might be a good idea to keep your resume updated.

4: Prototyping is a snap

Access is a great way to show fast results for the impatient client or boss. You can collect a little data and in just a few hours (or days) wow them with a few neat forms and reports — I can hear them ooing and ahhing already. You don’t have to use Access to build the production database, but you can ease client concerns by showing that you understand their needs. Access lets you get results fast and often with little to no code.

5: It’s easy to upsize once it outgrows Jet

People who control the purse strings aren’t usually willing to dedicate resources to developing a noncritical database. Most of the time, you’re on your own. However, that doesn’t mean that a good design won’t grow and evolve into a truly useful tool. If that happens, you can upsize an Access database to SQL Server. You don’t have to start from scratch.

Still, Access is limited to 2GB. Even if the database’s purpose isn’t critical, the amount of data alone might force you into the arms of a more robust engine. Realistically, you probably won’t run into that limitation too often. If you do, you can eliminate Jet from the picture and use an Access front end to link to SQL Server data.

6: It’s a one-time fling

Not every custom database has a long shelf life, but that’s not because it’s bad and dies an agonizing death. Sometimes its purpose is timed. For instance, generating, collecting, and analyzing questionnaire data can be a big job, even for Access, but a single questionnaire has a limited lifespan. If you’re going to use a database once, or for only a short time, use Access if possible.

7: It can provide a quick fix

The best solution for your needs might be a powerful client-server database such as SQL Server. However, while you’re waiting — and you will wait — how’s the work being done? You can use Access as a quick fix until the more robust version is ready. You’ll have to compromise, because if you really need SQL Server, you’re not going to get the same work done in Access. But you might get portions of the work done. Analyze the overall tasks and see what components you can automate in Access, at least for the time being.

8: You want to change what?

Access is flexible, and that’s one of its best attributes. Even if you can put a custom database together in a matter of weeks, needs are likely to change. Almost immediately, the user or client will think of something they want to add or change. If you designed the database well in the first place, Access will handle enhancements and changes without complaint.

9: It talks to Office

Access is part of the Microsoft Office suite, so it plays well with the other applications. Users can quickly and easily export data from or import data into Excel or publish reports to Word. In addition, it shares a similar interface with other Office apps, which helps new users feel more at home and diminishes the learning curve.

10: There’s less code!

All things being equal, Access can get the job done with less code than SQL Server (or some other client-server database). In addition, VBA is an easy language to master.

11: It offers connectability

Access offers an affordable solution for individual users and smaller teams. Despite protests from some member of the IT club, you can even use it across a network if you know what you’re doing (file server solutions on a local network).

On the other hand, Access isn’t optimized for the Web. Although a skillful developer can use Access on the Web, in general, it just isn’t a good idea. Jet can’t handle large numbers of simultaneous users, unless of course you really know what you’re doing — and that level of expertise is really closer to magic than development. It can be done, just not by many.

How to Store application data securely using encrypted DataSets?

.NET developers are familiar with using serialized DataSets to store local application data; this technique is used extensively in the WinForms development arena. The problems with this method are that anyone with access to the file can easily read the data and possibly even write to the file. The application would pick up the modified data and treat it as valid.

These issues caused me to see how I could secure local data held in serialized DataSets. There are many options for securing the data — from encrypting each field to hashing the data and comparing hashes when the data is deserialized. The most direct approach is to encrypt or decrypt the serialized DataSet before writing to it or after reading it from disk. This method provides a suitable amount of security and protects against anyone tampering with the file.

Serializing and encrypting the DataSet

The first step to encrypting the DataSet is to serialize it in memory. To do this, I will use the XmlSerializer object to serialize the DataSet into a StringWriter object. The StringWriter object allows you to treat a StringBuilder as if it was a normal Stream. This is what gives you the ability to serialize the DataSet in memory without writing it to disk.

In the code, there’s a call to the EncryptString function after the DataSet is serialized. This function is a wrapper around the DES encryption mechanisms that are included with .NET. EncryptString takes a plain-text string of data (along with a key value) and returns a string that has been DES encrypted using the supplied key value. The code for EncryptString is below

After the EncryptString function returns the encrypted data, the StreamWriter object is used to write the data to disk. At this point, the data is securely on the local disk and ready to be decrypted and read. Since the data is encrypted, anyone opening the file will see a jumble of characters and will not be able to get information out of the file or write usable information to the file.

Deserializing and decrypting the DataSet

Reading the encrypted DataSet is just as easy as writing it, except it is done in the opposite order. First, the encrypted data is read from disk using the StreamReader object as shown below

After the data is read, DecryptString is called. This function decrypts the data sent into it using the “key” parameter as the decryption key. If the key that is sent into DecryptString does not match the key that was used when EncryptString was called, the data will successfully decrypt. Following code shows the code for DecryptString.

After the data is decrypted, an XmlSerializer object is used to convert the raw data back into a usable DataSet. Note that the WriteEncryptedDataSet and ReadEncryptedDataSet functions are completely generic and do not rely on any specific schema of the DataSet. This allows you to use the same functions to read or write any DataSet in a secure manner.

DataContext class in depth : LINQ

In this part of the series I will explain the DataContext class in depth through a series of examples and explanations.


Whenever someone asks me about the DataContext I always refer to it as the “brains” of Linq to SQL, why? Because it is. It is responsible for the translation of Linq to T-SQL, and the mapping of the results (rows) of that query to objects, and so much more.

If you run any codegen tool (SqlMetal, or Linq to SQL file (VS Orcas designer)) you always get a class that derives from the DataContext class. The DataContext can be equated to a database, in that it contains a series of tables (normally this is implemented as a property that returns a Table), and sprocs (methods calls that invoke underlying sprocs at the DB layer).

Note: The DataContext can also handle other SQL Server features like views, and table functions etc.

What’s so special about the DataContext class?

Most notably the DataContext takes some Linq code and generates the appropriate T-SQL statement for that query; if that query returns rows then the DataContext object will take care of mapping those rows to objects.

Figure 5-1: DataContext core functionality

I want to see the T-SQL generated by my LINQ

If you are a control freak/perfectionist like me then you probably want to know exactly what T-SQL is being generated for you at all times.

Before I show you some code please remember that the T-SQL generation by the DataContext object is not perfect, if you know of a very efficient T-SQL solution then implement that in a sproc. Saying that I have found the T-SQL generation of the DataContext object to be efficient in some advanced queries. Like so many things in programming one way, and one way alone is often not the best approach – experiment to see what offers the best performance for your query.

There are a few ways in which we can monitor the T-SQL being generated by our DataContext object, these include:

  • Visual Studio Orcas
  • ToString() override of query
  • DataContext Log property
  • SQL Server 2005 profiler (not included with Express edition)
Note: As the majority of people reading this article will be using the Express editions of the developer tools I will skip the SQL Server 2005 profiler demo. Those who have used the profiler before will know that it is a real time T-SQL dump of everything that a particular SQL Server 2005 instance is doing so seeing what T-SQL the DataContext is generating is a trivial task.

Visual Studio Orcas

As Linq is a 1st class citizen within the C# and VB.NET languages it should be no surprise to you whatsoever to see rich debugging support within the IDE.

Figure 5-2: Seeing the T-SQL generated by the DataContext object in VS Orcas

ToString() override

This is a really nice feature – if you call the ToString() method on any variable whose value is a query you can see the associated T-SQL for that query.

Figure 5-3: Calling ToString()

  1. using System;
  2. using System.Linq;
  3. using System.Collections.Generic;
  4. namespace ConsoleApplication4
  5. {
  6. public class Program
  7. {
  8. public static void Main(string[] args)
  9. {
  10. using (BookShopDataContext db = new BookShopDataContext())
  11. {
  12. IEnumerable<Book> query = from b in db.Books select b;
  13. Console.WriteLine(query);
  14. }
  15. }
  16. }
  17. }

Figure 5-4: Result of running the code in Figure 5-3


Concurrency is always an issue – Linq to SQL by default uses optimistic concurrency. Take an example of where we retrieve a particular piece of data and then update that data, we then try to submit the updated data to the server but we get an exception as Linq to SQL knows that we have violated a concurrency check as we were acting upon “old” data.

Just to show this I did a pretty simple operation by adding a breakpoint on the line where I call the SubmitChanges(...) method and then executed some T-SQL code to alter that record and then continued the execution of my application so I would get an exception.

Figure 5-6: ChangeConflictException

The SubmitChanges(...) method is overrided to take a ConflictMode enum where we can change the default behaviour when data is submitted to the database, these are:

  • ContinueOnConflict
  • FailOnFirstConflict

The above can be used when within a TransactionScope. There are a few attribute values we can apply to our generated code to specifically say “we don’t care if this particular bit of data is changed – just overwrite it”. We do this using the UpdateCheck parameter to the Column attribute.

Figure 5-7: We don’t care about this data – just overwrite the value with what we have!

  1. [global::System.Data.Linq.Column(Storage=“_Title”, Name=“Title”,
  2. DBType=“NVarChar(50) NOT NULL”, CanBeNull=false,
  3. UpdateCheck=System.Data.Linq.UpdateCheck.Never)]

The UpdateCheck enum values:

  • Always
  • Never
  • WhenChanged

I’m pretty sure I’ve not covered everything in Linq to SQL with regards to concurrency – remember though that you can use the TransactionScope type in the .NET framework to assist you.

Using VS Orcas to look at data

Remember – queries only execute when we call the GetEnumerator() of the variable that holds the query! In previous CTP’s of VS Orcas it was hard to see what data we pulled back from the database as the debugger didn’t call the GetEnumerator() method when you wanted to look at that particular variable – the March CTP does however which is really great!

Let’s go ahead and look at using VS Orcas to look at data returned from our database using the Locals window when debugging. First place a breakpoint after the line you define a query, you don’t need a foreach to call the GetEnumerator() – the IDE will do that for us. When you get to your breakpoint you will see a screen similar to that in Figure 5-8 where the variable holding our query has a message saying something like “expand me to enumerate this IEnumerable”.

Figure 5-8: Thinking about enumerating the IEnumerable

Expand the results! You know you want to.

Figure 5-9: Viewing the results

You can see that in Figure 5-9 I have two objects returned from my query, I can drill down into the values of their properties and so forth.

On the face of it this is a pretty basic enhancement to the IDE, and you would be right in thinking so – but this is a great visual tool which I’m sure you will love when using Linq to SQL!


Will LINQ to SQL support Oracle?

This is a common question, the answer to which is no. Linq to SQL is a lightweight O/R mapper targeted for use with SQL Server 2000/2005. For a more powerful O/R mapper look at the Entity Data Model (EDM) – EDM will support Oracle and other 3rd party databases.

I hate codegen tools, how much code does it really take to hook up to my DB using LINQ to SQL?

Surprisingly little, essentially you need to create a property with the correct access (get, set, or both?) with the appropriate Column attribute. The class with your properties should be pluralized and use the Table attribute to map it to the corresponding table in the database.

Finally you will need to create a class that derives from DataContext. In this class you will want to expose some properties that return a generic Table.

Below is the code for the TodoListDataContext and Task types:

Task.cs (type that is associated with records in Tasks):

  1. using System;
  2. using System.Data.Linq;
  3. namespace ConsoleApplication5
  4. {
  5. [Table(Name=“Tasks”)]
  6. public class Task
  7. {
  8. private int _taskId;
  9. [Column(Name = “TaskID”, Storage = “_taskId”,
  10. IsDBGenerated = true, IsPrimaryKey = true,
  11. CanBeNull = false, DBType = “int not null identity”)]
  12. public int TaskID
  13. {
  14. get { return _taskId; }
  15. }
  16. [Column(Name = “Title”, CanBeNull = false,
  17. DBType = “nvarchar(50) not null”)]
  18. public string Title
  19. {
  20. get;
  21. set;
  22. }
  23. [Column(Name = “Complete”, CanBeNull = false)]
  24. public bool Complete
  25. {
  26. get;
  27. set;
  28. }
  29. }
  30. }

TodoListDataContext.cs (type that exposes a Table of type Task, and defines connection string)

  1. using System;
  2. using System.Data.Linq;
  3. namespace ConsoleApplication5
  4. {
  5. public class TodoListDataContext : DataContext
  6. {
  7. public TodoListDataContext(string conn) : base(conn)
  8. {
  9. }
  10. public Table<Task> Tasks
  11. {
  12. get { return GetTable<Task>(); }
  13. }
  14. }
  15. }

Why are the changes to my data in SQL Server not being persisted?

Remember, in order to persist changes to SQL Server you need to call the SubmitChanges(...) method on your DataContext object after you have made changes to any data.

Can I use XML to define the O/R mapping?

Yes. You can use SqlMetal to generate an XML mapping file, when creating a DataContext you need to specify that your mapping is defined in an XML file by using the XmlMappingSource type.

I’m hitting the database every time for two identical queries, can I prevent this?

To prevent hitting the database every time you can load the results into memory (e.g. Append ToList() to a query) any subsequent calls to that query will use the in-memory collection rather than going off and querying the database again.

Is it possible to create code from XML?

Yes. You can create a .dbml file using SqlMetal and then generate a codefile (either VB.NET or CS) based on the XML defined in the .dbml.

What’s this deferred loading stuff?

Linq to SQL uses deferred loading when accessing properties of a related entity. Given a simple select query returning all Books, if I then want the publisher for each book then I can do so, however, behind the scenes the DataContext is doing a select where query to get the publisher for the current book. This is deferred loading.

Note: You can disable deferred loading using the EnableDeferredLoading property of a DataContext object.

Developing 3 tier application in CSharp using LINQ

The ASP.NET application that we will create we be a 3 tier application, consisting of a data access layer (DAL), a business logic layer (BLL) and a presentation layer (PL).

We will be using Visual Studio Orcas Beta 1 for this tutorial.

Defining our database schema

Before we jump into coding some C# or ASP.NET we need to setup our database, more importantly we first need a database! If you haven’t already go and grab yourself a copy of SQL Express 2005. I will be using SQL Server Management Studio 2005 to create and define my database; however, you can do all of this using Visual Studio 2005 if you want.

Creating a database

Let’s go ahead and create a database for this tutorial. I’m going to create a database called TodoList. To create a database right click the Databases folder in the Object Explorer window and click New Database (Figure 4-1).

Figure 4-1: Creating a database

When the New Database window is visible enter TodoList (or any other name you want to use) as the Database name (Figure 4-2).

Figure 4-2: Specifying the database name

Adding the Tasks table

With our database created we will now add a Tasks table. In SQL Server Management Studio right click the Tables folder and select New Table (Figure 4-3).

Figure 4-3: Creating the Tasks table

In the table designer replicate the settings from Figure 4-4.

Figure 4-4: Defining the columns

Note: In Figure 4-4 TaskID is a primary key and identity.

When you save the table, save it as Tasks (Figure 4-5).

Figure 4-5: Saving the table

Creating the stored procedures

We need to define three stored procedures, one for adding a new task, one for updating the state of a task, and finally one for retrieving all tasks of a specific state (either active, or closed).

Creating a stored procedure is easy, simply right click on the Stored Procedures folder and click New Stored Procedure (Figure 4-6).

Figure 4-6: Creating a stored procedure

Below are the three code listings for the stored procedures we will use.

Figure 4-7: AddTask

  1. create procedure AddTask
  2. @Title nvarchar(50),
  3. @Complete bit
  4. as
  5. insert into Tasks (Title, Complete)
  6. values (@Title,[email protected])

Figure 4-8: GetTasksByState

  1. create procedure GetTasksByState
  2. @Complete bit
  3. as
  4. select TaskID, Title, Complete
  5. from Tasks
  6. where [email protected]
  7. order by Title

Figure 4-9: UpdateTaskState

  1. create procedure UpdateTaskState
  2. @TaskID int,
  3. @Complete bit
  4. as
  5. update Tasks
  6. set [email protected]
  7. where [email protected]

Setting up our data access layer

With our database ready let’s go ahead and create our DAL. We will use the LINQ to SQL file designer in Visual Studio Orcas to do this.

Note: You will first need to add a connection to the TodoList database in the Server Explorer window.

Right click on your website project and add a new LINQ to SQL file called TodoList (Figure 4-10).

Figure 4-10: Adding a LINQ to SQL file to our project

With the file created drag and drop the Tasks table and the three stored procedures we created on to the design canvas of the designer (Figure 4-11).

Figure 4-11: Adding our table and sprocs to our DAL

That’s it! Our DAL is all ready to use.

Adding a business logic layer

In this tutorial there is not much point to adding a BLL, but we will add one nonetheless.

This layer in a real life application this layer would enforce any business rules (parameter checking etc…). Also in a real life application you would want to separate your DAL and BLL into their own components.

Right click the App_Code folder and add a new class called TasksBll.cs, then copy and paste the class definition given in Figure 4-12 into that file.

Figure 4-12: TasksBll.cs

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. namespace Org.GBarnett.Dns
  5. {
  6. public static class TasksBll
  7. {
  8. /// <summary>
  9. /// Gets all tasks of a particular state
  10. /// </summary>
  11. public static List<GetTasksByState> GetTasksByState()
  12. {
  13. using (TodoListDataContext db = new TodoListDataContext())
  14. {
  15. return (from t in db.GetTasksByState() select t).ToList<GetTasksByState>();
  16. }
  17. }
  18. /// <summary>
  19. /// Adds a new task
  20. /// </summary>
  21. public static void AddTask(string title, bool complete)
  22. {
  23. using (TodoListDataContext db = new TodoListDataContext())
  24. {
  25. db.AddTask(title, complete);
  26. }
  27. }
  28. /// <summary>
  29. /// Updates a tasks state
  30. /// </summary>
  31. public static void UpdateTaskState(int taskId, bool complete)
  32. {
  33. using (TodoListDataContext db = new TodoListDataContext())
  34. {
  35. db.UpdateTaskState(taskId, complete);
  36. }
  37. }
  38. }
  39. }

Each method of the TasksBll.cs (Figure 4-12) performs the appropriate operation by accessing methods defined in our DAL.

Enter ASP.NET…

As previously mentioned we will use ASP.NET to define the UI.

The UI will consist of 3 controls:

  • DropDownList
  • GridView
  • FormView

Before we begin add a new AJAX Web Form to your project, the name you give it is irrelevant. Add all the following code snippets inside the form tag of the page.

We have two states

Either a task is active or complete. We will use a drop down list control to store these values (Figure 4-12).

Figure 4-13: DropDownList containing the tasks states

  1. <asp:DropDownList ID=“ddlState” runat=“server” AutoPostBack=“true”>
  2. <asp:ListItem Selected=“True” Text=“Active” Value=“false” />
  3. <asp:ListItem Text=“Complete” Value=“true” />
  4. </asp:DropDownList>

Adding an ObjectDataSource

Before we add any more controls to our web page we need to hook our presentation layer up with our BLL. To do this we will add an ObjectDataSource and wire it up to the appropriate methods defined in our BLL.

Figure 4-14: Adding an ObjectDataSource

  1. <asp:ObjectDataSource
  2. ID=“odsTasks”
  3. runat=“server”
  4. TypeName=“TasksBll”
  5. SelectMethod=“GetTasksByState”
  6. InsertMethod=“AddTask”
  7. UpdateMethod=“UpdateTaskState”>
  8. <SelectParameters>
  9. <asp:ControlParameter
  10. ControlID=“ddlState”
  11. Name=“complete”
  12. PropertyName=“SelectedValue”
  13. Type=“Boolean” />
  14. </SelectParameters>
  15. <UpdateParameters>
  16. <asp:Parameter Name=“taskId” Type=“int32” />
  17. <asp:Parameter Name=“complete” Type=“boolean” />
  18. </UpdateParameters>
  19. <InsertParameters>
  20. <asp:Parameter Name=“title” Type=“string” />
  21. <asp:Parameter Name=“complete” Type=“boolean” />
  22. </InsertParameters>
  23. </asp:ObjectDataSource>

Using a GridView to view the tasks

We will now go ahead and add a GridView to our page (Figure 4-15).

Note: I have customized the Item and Edit templates for the checkbox control.

Figure 4-15: Adding a GridView to our page to display the tasks

  1. <asp:GridView
  2. ID=“gvTasks”
  3. runat=“server”
  4. DataSourceID=“odsTasks”
  5. AutoGenerateColumns=“false”
  6. DataKeyNames=“TaskID”
  7. AllowPaging=“true”
  8. GridLines=“None”
  9. AlternatingRowStyle-CssClass=“alternate”>
  10. <Columns>
  11. <asp:BoundField DataField=“Title” HeaderText=“Task” ReadOnly=“true” />
  12. <asp:TemplateField HeaderText=“Complete”>
  13. <ItemTemplate>
  14. <asp:CheckBox ID=“cbI” runat=“server” Checked=‘<%# Eval(“Complete”) %>’
  15. Enabled=“false” />
  16. </ItemTemplate>
  17. <EditItemTemplate>
  18. <asp:CheckBox ID=“cbE” runat=“server” Checked=‘<%# Bind(“Complete”) %>’ />
  19. </EditItemTemplate>
  20. </asp:TemplateField>
  21. <asp:CommandField ShowEditButton=“true” />
  22. </Columns>
  23. </asp:GridView>

Using the FormView to add more tasks

To add a new task we will use a FormView (Figure 4-16).

Note: I have not included any data input checking to keep the example as simple as possible.

Figure 4-16: Adding a FormView to allow the user to add new tasks

  1. <asp:FormView ID=“fvTask” runat=“server” DefaultMode=“Insert” DataSourceID=“odsTasks”>
  2. <InsertItemTemplate>
  3. <p><strong>Title:</strong>
  4. <asp:TextBox ID=“txtTitle” runat=“server” Text=‘<%# Bind(“Title”) %>’ /></p>
  5. <p><strong>Complete:</strong>
  6. <asp:CheckBox ID=“cbC” runat=“server” Checked=‘<%# Bind(“Complete”) %>’ /></p>
  7. <p><asp:LinkButton ID=“lbAdd” runat=“server” Text=“Add Task” CommandName=“Insert” /></p>
  8. </InsertItemTemplate>
  9. </asp:FormView>

Adding AJAX functionality to the GridView and the FormView controls

Just to smooth things up I’ve added a few UpdatePanel’s to the page, each wrapping the GridView and FormView respectively.

Figure 4-17: The updated GridView

  1. <asp:UpdatePanel ID=“upTasks” runat=“server”>
  2. <ContentTemplate>
  3. <asp:GridView
  4. ID=“gvTasks”
  5. runat=“server”
  6. DataSourceID=“odsTasks”
  7. AutoGenerateColumns=“false”
  8. DataKeyNames=“TaskID”
  9. AllowPaging=“true”
  10. GridLines=“None”
  11. AlternatingRowStyle-CssClass=“alternate”>
  12. <Columns>
  13. <asp:BoundField DataField=“Title” HeaderText=“Task” ReadOnly=“true” />
  14. <asp:TemplateField HeaderText=“Complete”>
  15. <ItemTemplate>
  16. <asp:CheckBox ID=“cbI”
  17. runat=“server”
  18. Checked=‘<%# Eval(“Complete”) %>’
  19. Enabled=“false” />
  20. </ItemTemplate>
  21. <EditItemTemplate>
  22. <asp:CheckBox ID=“cbE” runat=“server” Checked=‘<%# Bind(“Complete”) %>’ />
  23. </EditItemTemplate>
  24. </asp:TemplateField>
  25. <asp:CommandField ShowEditButton=“true” />
  26. </Columns>
  27. </asp:GridView>
  28. </ContentTemplate>
  29. <Triggers>
  30. <asp:AsyncPostBackTrigger ControlID=“ddlState” />
  31. </Triggers>
  32. </asp:UpdatePanel>

Figure 4-18: The update FormView

  1. <asp:UpdatePanel ID=“upAdd” runat=“server”>
  2. <ContentTemplate>
  3. <asp:FormView ID=“fvTask” runat=“server” DefaultMode=“Insert” DataSourceID=“odsTasks”>
  4. <InsertItemTemplate>
  5. <p><strong>Title:</strong>
  6. <asp:TextBox ID=“txtTitle” runat=“server” Text=‘<%# Bind(“Title”) %>’ /></p>
  7. <p><strong>Complete:</strong>
  8. <asp:CheckBox ID=“cbC” runat=“server” Checked=‘<%# Bind(“Complete”) %>’ /></p>
  9. <p>
  10. <asp:LinkButton ID=“lbAdd” runat=“server” Text=“Add Task” CommandName=“Insert” />
  11. </p>
  12. </InsertItemTemplate>
  13. </asp:FormView>
  14. </ContentTemplate>
  15. </asp:UpdatePanel>

And on that note we are finished! You can download the full source code at the end of this tutorial.

The experience

From personal experience coding the data access layer can be a very boring process – in this tutorial we have seen how effective LINQ to SQL can be when creating our DAL.

Visual Studio Orcas has introduced a few really cool tools to further assist with the creation of the DAL – we no longer need to spend hours on OR mapping. The great thing about LINQ is the ability to further query your DAL, and create some anonymous type.

There is no doubt in my mind that LINQ is set to revolutionize the way programmers interact with data, more so is the fact that we always use a familiar set of standard query operators!

If you download and run the source you will see the site shown in Figure 4-19.

Figure 4-19: Our to-do list web site

Entities and Key Types in application using Linq

In this part we will look a little more at what entities are, as well as taking a closer look at the key types we can use and their application.


When we talk about entities we are generally talking about a more functional representation of our schema. A perfect view of this is the Visual Studio Orcas LINQ to SQL file designer (Figure 3-1). If you drag a few tables onto the design surface you will see an abstract view of your database schema showing only the entity names and the relationships between the entities.

Figure 3-1: Entities in Visual Studio Orcas

Essentially when we talk about entities in LINQ to SQL we are more or less talking about our applications view of the data – our entities don’t necessarily need to map to tables in our database.

Go ahead and drag a few tables onto the designer.

If you take a look at the code generated for you by the designer you will see several attributes that map the particular class (entity) to a table in the database (Figure 3-2). You will also see that the properties are associated with columns in the classes associated table (Figure 3-3). An interesting thing to note is that there is not a direct mapping between CLR and SQL Server types so these attributes take care of the relevant plumbing to map the types accordingly.

Figure 3-2: Associating a class with a table

  1. [System.Data.Linq.Table(Name=“dbo.Books”)]
  2. public partial class Book { //…

Figure 3-4: Associating a property with a column in a table

  1. [System.Data.Linq.Column(Storage=“_BookID”,
  2. Name=“BookID”,
  4. IsPrimaryKey=true,
  5. IsDBGenerated=true,
  6. CanBeNull=false)]

Inspecting the DataContext class

The DataContext class is the most important class when using LINQ to SQL. If you have inspected the code generated for you by the LINQ to SQL File designer then you will notice that the partial class derives from the System.Data.Linq.DataContext class.

In a nutshell the DataContext is in charge of generating the SQL statement from your language query, and then mapping the rows of data (if any) returned from your database to objects. The DataContext is indeed a very interesting class (we will revisit this class throughout this series!).

If we construct a simple query (Figure 3-4) we can inspect the SQL that the DataContext generates for us (Figure 3-5).

Figure 3-4: Simple query

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data.Linq;
  4. using System.Linq;
  5. namespace IntroToLinq
  6. {
  7. public class Program
  8. {
  9. public static void Main()
  10. {
  11. using (BookShopDataContext db = new BookShopDataContext())
  12. {
  13. IEnumerable<Book> books = from b in db.Books select b;
  14. foreach (Book b in books)
  15. {
  16. Console.WriteLine(b.Title);
  17. }
  18. }
  19. }
  20. }
  21. }

Figure 3-5: SQL generated by the DataContext object for Figure 3-4

If a class implements IDisposable then make good use of it!! By wrapping our BookShopDataContext object in a using statement we implicitly call the Dispose() method for this object. Calling Dispose() releases any resources held by our object. If you don’t want to use the using statement call the objects Dispose() method explicitly within a finally block.

I’m not going to cover all of the great things that the DataContext class offers to us – we will do that in subsequent parts. Stay tuned!

Stored Procedures

Let’s take a look at using stored procedures in LINQ to SQL. Up until now we have been composing ad-hoc queries in C#. I like to code my SQL queries as sprocs in the database layer and then invoke those sprocs via my apps DAL – this is a very, very common approach.

First thing we will do is create a simple sproc that simply returns all the names of the publishers, Figure 3-6 shows this.

Figure 3-6: Selecting all the names of the publishers

  1. create procedure GetPublishers
  2. as
  3. select PublisherID, PublisherName
  4. from Publishers
  5. order by PublisherName

I like to use Microsoft SQL Server Management Studio 2005 to code all my SQL. If you want you can do this in Visual Studio, however, there are some great features in Management Studio like being able to view the execution plan.

If you run this query you will get all the names of the publishers in the database as shown in Figure 3-7.

Figure 3-7: Result of executing sproc defined in Figure 3-6

With our sproc defined in our database we will go back into Visual Studio and drag the stored procedure from the server explorer window onto the designer canvas of the LINQ to SQL File (Figure 3-8).

Figure 3-8: Dragging the GetPublishers sproc onto the design canvas

When you have dragged your sproc onto the canvas you will see that the designer generates a method of the same name. There is a method pane on the designer that allows you to see all methods in your DAL (dragging a sproc onto the designer generates a method that executes your sproc).

Figure 3-9: The method pane

I mentioned in the previous part of this series that we would be using a tool called SQLMetal in this part. I decided not to use that tool for now purely because the designer is a little more educational in that it creates a visual representation of your DAL. In the next part of this series where we create an application using LINQ to SQL we will use the SQLMetal.exe command line tool.

Using our GetPublishers() method

Before we use this method in our code let us first take some time to look at the code that the designer generated for us (Figure 3-10).

Figure 3-10: Generated code for GetPublishers()

  1. [global::System.Data.Linq.StoredProcedure(Name=“dbo.GetPublishers”)]
  2. public global::System.Collections.Generic.IEnumerable<GetPublisher>
  3. GetPublishers()
  4. {
  5. global::System.Data.Linq.Provider.IQueryResults<GetPublisher> result =
  6. this.ExecuteMethodCall<GetPublisher>(this,
  7. (
  8. (global::System.Reflection.MethodInfo)
  9. (global::System.Reflection.MethodInfo.GetCurrentMethod()))
  10. );
  11. return
  12. (
  13. (global::System.Collections.Generic.IEnumerable<GetPublisher>)
  14. (result)
  15. );
  16. }

The GetPublishers() method is decorated with a StoredProcedure attribute, this attribute associates this method with the appropriated sproc in our database. What we return is an enumeration of type GetPublisher (coincidentally this type looks exactly the same as Publisher – we will rectify this in a moment!). For now we will ignore the reflection stuff and the IQueryResults interface – we will cover those bits in a few parts time!

Before we move on we will use the GetPublishers() method in a query (Figure 3-11).

Figure 3-11: Composing a query using the GetPublishers() method

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data.Linq;
  4. using System.Linq;
  5. namespace IntroToLinq
  6. {
  7. public class Program
  8. {
  9. public static void Main()
  10. {
  11. using (BookShopDataContext db = new BookShopDataContext())
  12. {
  13. var publishers = from p in db.GetPublishers() select p;
  14. foreach (GetPublisher publisher in publishers)
  15. {
  16. Console.WriteLine(“{0} {1}”,
  17. publisher.PublisherID,
  18. publisher.PublisherName);
  19. }
  20. }
  21. }
  22. }
  23. }

Hang on! What the GetPublishers() method returns is an enumeration of type GetPublisher! What the heck is that?! Good question! Well at the moment the designer is not smart enough to recognize that you are returning back an enumeration of type Publisher, which we already have defined! What we have at the moment are two types exactly the same! We will rectify this now.

  1. Go into the BookShop.designer.cs and delete the GetPublisher type.
  2. Replace all occurrences of GetPublisher in the GetPublishers method – this includes the method signature (return type), and a few other lines with Publisher.

Once you have completed the above steps your GetPublishers() method should look like that in Figure 3-12.

Figure 3-12: The new, slightly tweaked GetPublishers() method

  1. [global::System.Data.Linq.StoredProcedure(Name=“dbo.GetPublishers”)]
  2. public global::System.Collections.Generic.IEnumerable<Publisher>
  3. GetPublishers()
  4. {
  5. global::System.Data.Linq.Provider.IQueryResults<Publisher> result =
  6. this.ExecuteMethodCall<Publisher>
  7. (
  8. this,((global::System.Reflection.MethodInfo)
  9. (global::System.Reflection.MethodInfo.GetCurrentMethod()))
  10. );
  11. return ((global::System.Collections.Generic.IEnumerable<Publisher>)
  12. (result));
  13. }

You can now modify the code in Figure 3-11 to that shown in Figure 3-13.

Figure 3-13: A more elegantly named return type

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data.Linq;
  4. using System.Linq;
  5. namespace IntroToLinq
  6. {
  7. public class Program
  8. {
  9. public static void Main()
  10. {
  11. using (BookShopDataContext db = new BookShopDataContext())
  12. {
  13. var publishers = from p in db.GetPublishers() select p;
  14. foreach (Publisher publisher in publishers)
  15. {
  16. Console.WriteLine(“{0} {1}”,
  17. publisher.PublisherID,
  18. publisher.PublisherName);
  19. }
  20. }
  21. }
  22. }
  23. }

Querying relational data using Linq

This article is based on a single table schema (don’t worry subsequent parts will see a much more complex database schema!) so that you can get to grips with LINQ to SQL (the name for using LINQ with relational data – also known as DLINQ in a previous life, but we won’t go into that…) quickly and feel confident with this awesome new language enhancement.

Conceptual data access layer?

Conceptual what?! If you are familiar with the concept of a database schema then you don’t have much to worry about! You can think of a conceptual data access layer as being your applications view of the database.

I don’t want to bog you down with entities yet! That will come in part 3 of this series. Just know that what we deal with from our applications point of view is a series of entities describing the tables and relationships in our database schema. I will let you look forward to the diagrams explaining all of this in the next part!

Generating our DAL using Visual Studio Orcas

Typically DAL’s have consisted of boilerplate code to map relational data to objects and vice versa, speaking from experience I really enjoyed this the first time I did it – but now it just annoys the heck out of me! With that in mind it’s no surprise that the folks on the Visual Studio team decided to give us a few tools to generate that boilerplate code.

At present there are two tools that come with Visual Studio Orcas (both were in the LINQ May 2006 CTP):

  • SQLMetal.exe – a command line tool that gives you a tonne of options when creating your DAL. Things of note are pluralisation of entities, extracting stored procedures from your database schema, and defining the namespace, as well as a tonne of other stuff.
  • LINQ to SQL file – used to be called DLINQ objects in the LINQ May 2006 CTP, now it has a cooler name, but it’s pretty much the same tool. This is a file template that lives in Visual Studio Orcas – it’s a designer. Personally I don’t really like stuff that is “drag and drop” so I tend to opt for the finer control offered to me by SQLMetal – with that said it’s a bit of a shock to me that I have opted to use the designer for this part of the series.

Before we go any further I just want to give you a quick snapshot of the table that we will be using for this part of the series – like I mentioned earlier the next part will have several tables with many relationships…think of it as a throwaway schema (actually I don’t think if even qualifies as a database schema it’s so simple!).

Figure 2-1: Our unbelievably simple database schema (for now!)

Our unbelievably simple database schema (for now!)

I bet you are questioning my credibility now aren’t you?! Come on its simple so that you can learn the basics quick so when we look at more complex examples in the future you have a good solid understanding of using LINQ with relational data.

Ok, go ahead and add some dummy data to that table.

Fire up Visual Studio Orcas (the March 2007 CTP remember) and create a new .NET 3.5 Console Application. When you’ve done that right click the project and add a LINQ to SQL File – as all we’ll be throwing away all of the code in this part just name it Part2.dbml for now.

Figure 2-2: Adding a LINQ to SQL File to our solution

Adding a LINQ to SQL File to our solution

At this moment in time we have everything setup so drag the Books table from the server explorer window onto the canvas of Part2.dbml.

Figure 2-3: Result of dragging the Books table onto Part2.dbml

Result of dragging the Books table onto Part2.dbml

If you take a look at the code that has been generated for us you will notice there is a class called Book that implements a few interfaces (more on those in the next part). Take a look at the attribute that is placed before the class definition (fig. 2-4) here we are associating this type (Book) with Books table in our database.

Figure 2-4: Associating the Book type with our Books table

Take a moment to look at the fields in the Book type – notice that the designer has created two fields of the same names as the columns in the Books table; properties have also been defined in the class marked with an attribute to associate the fields with their corresponding column in the Books table (Fig. 2-5).

Figure 2-5: Associating the fields in the Book type with the columns in our Books table

Before we move on we will talk a little about the Column attribute (defined in the System.Data.Linq namespace). Take a look at Fig. 2-5, notice that we explicity state the field that stores the value of the column, e.g. the BookId columns’ value is stored in the _BookId field, we also state that this property is associated with the BookId column in the Books table (Name="BookId").

If you are familiar with SQL Server data types then you will also know that there is not a 1..1 mapping between SQL Server 2005 data types and CLR types, e.g. the CLR has no money data type. For this reason we can explicitly state the database column type, the designer will choose the most appropriate type for use for that particular property, e.g. for the BookId column the designer has chosen to use the default int value type (Int32).

Before we move on, just be aware that you have to explicitly state what values are generated by the database, and whether or not they can be null. In the case of BookId the database generates a new integer automatically for each row; this column is also a primary key.

Take some time to further examine the Column and Table attributes.

Querying our DAL

We will now go through a few simple queries against the DAL that we have created in previous steps.

For each example I have provided the query version and the lambda expression version.

Select all books

Figure 2-6: Selecting all books (query)

Figure 2-7: Selecting all books (lambda expressions/extension methods)

I should note that in fig. 2-7 you can omit the Select extension method and you will achieve the same result as when including the Select extension method and its lambda expression parameter; however, the example code given is clearer than when omitting the Select extension method.

Select the titles of the books whose title length is greater than 6 characters

Figure 2-8: Selecting the titles of the books with length > 6 (query)

Figure 2-9: Selecting the titles of the books with length > 6 (lambda expressions/extension methods)