Maybe some of you want to ask me: OK. SQL Server 2008 is the latest version of SQL Server but what's new in it?

Today I want to introduce you to some of top features available in SQL Server 2008. This is an screen shot of a SQL Server 2008 query. You can see some of new features you can use in the latest version of SQL Server:

As you saw, there are some amazing features has been added to the new version. Here is a list of most interesting features that implemented in the latest version of SQL Server:

It may happen to everyone; sometimes you may want to execute your LINQ to SQL commands in one transaction like you do it in SQL Server as following:

BEGIN TRANSACTION

IF ([something happend])

begin

    -- Your code is here

end

ELSE

begin

    -- Restore to original

    ROLLBACK TRANSACTON

end

COMMIT TRANSACTION

In this post I want to show how you can do it using LINQ to SQL:

Create your LINQ to SQL project and write some code in order to do something ex.: Add a new record to a database. You should pass the following steps in order to manage transactions in LINQ to SQL:

First, You must add a new reference named System.Transactions to your project:

Then, you must create a new variable from System.Transactions.TransactionScope like this:

using (TransactionScope ts = new TransactionScope())

{

     // Your code comes here.

}

After that you must add your insert code between using tags:

using (TransactionScope ts = new TransactionScope())

            {

                try

                {

                    Post p = new Post();

                    p.title = title;

                    p.keywords = keywords;

                    p.shortDesc = shortDesc;

                    p.content = content;

                    p.startRate = Convert.ToByte(startRate);

                    p.enableComments = enableComments;

                    p.usersCanRate = usersCanRate;

                    p.createDate = DateTime.Now;

                    p.lastModifyDate = DateTime.Now;

                    c.Posts.InsertOnSubmit(p);

                    c.SubmitChanges();

                    Guid postId = p.postId;

                    foreach (var author in authors)

                    {

                        Posts_AddAuthorRelation(postId, author.AuthorID);

                    }

                    foreach (var category in categories)

                    {

                        Posts_AddCategoryRelation(postId, category.CategoryID);

                    }

                    foreach (var source in sources)

                    {

                        Posts_AddSourceRelation(postId, source.SourceID);

                    }

                    foreach (var media in medias)

                    {

                        Posts_AddMediaRelation(postId, media.MediaID);

                    }

                }

                catch

                {

                    Transaction.Current.Rollback();

                }

            }

As you can see I put a try...catch statements to my code. In try section I just wrote my insert code. If there is any error with my database or something. It will goes to my catch block.
Because I insert to more than one table, I should delete all inserted tables if there is any exception(s).

So you must use System.Transactions.Transaction.Rollback() method in order roll it back like SQL Server's ROLLBACK TRANSACTION command that we talked about it above.

With System.Transactions, you can manage your LINQ to SQL commands in transactions.

I was waiting a lot for this realase of SQL Server and finally it has been released today. Lets take a look at product overview published from Microsoft official website:

Your Data, Any Place, Any Time

SQL Server 2008 delivers on Microsoft’s Data Platform vision by helping your organization manage any data, any place, any time. It enables you to store data from structured, semi-structured, and unstructured documents, such as images and music, directly within the database. SQL Server 2008 delivers a rich set of integrated services that enable you to do more with your data such as query, search, synchronize, report, and analyze. Your data can be stored and accessed in your largest servers within the data center all the way down to desktops and mobile devices, enabling you to have control over your data no matter where it is stored.

SQL Server 2008 enables you to consume your data within custom applications developed using Microsoft .NET and Visual Studio and within your service-oriented architecture (SOA) and business process through Microsoft BizTalk Server while information workers can access data directly in the tools they use every day, such as the 2007 Microsoft Office system. SQL Server 2008 delivers a trusted, productive, and intelligent data platform for all your data needs.

SQL Server 2008 New Features

TRUSTED

SQL Server provides the highest levels of security, reliability, and scalability for your business-critical applications.

INTELLIGENT

SQL Server 2008 provides a comprehensive platform, delivering intelligence where your users want it.

 For more information about this release and download it, you can check out Microsoft SQL Server 2008 Home Page

 By the way I have to thanks a lot from Microsoft for releasing this version of SQL Server.

Source: Microsoft SQL Server 2005 Programming - Microsoft Press

A transaction is a single operation or set of operations that succeed or fail together, thereby ensuring consistency of data should unforeseen circumstances arise. An example might be a financial transaction. For example, let's say you buy a car. The single transaction of buying a car consists of three distinct operations:

1. You select a car.

2. You pay for it.

3. You drive the car off the lot.

Skipping any of these steps could cause major angst to one or more of the parties involved. This is a simple example of a set of steps that must always occur together in a consistent manner.

Transactions allow us to ensure consistency in our data through four basic principles. These principles provide a set of rules that must be followed in order for our transaction to succeed. The four principles help ensure that the state of our data is atomic, consistent, isolated, and durable regardless of the success or failure of the transaction. These properties are covered in the next topic.

Understanding the ACID Properties

Consider the transaction of going to an ATM and withdrawing $100 from your bank account. The data for this transaction can be easily represented in a database as a table with two columns, one holding your AccountID and the other holding your AccountBalance.

To begin with, your account balance is $100, so after withdrawing $100, your updated balance should be zero. Also, it makes logical sense that before the money can be disbursed, the system must ensure that you have the funds available in your account. What this means in database terms is that two database queries must be run in this transaction. The first query checks the account balance.

SELECT AccountBalance FROM Account WHERE AccountID = @AccountID

If the query returns an AccountBalance value greater than or equal to the requested withdrawal, you can perform the operation of withdrawing cash.

After withdrawing the cash, you must update the account record with the updated balance. To do so, you run an UPDATE query.

UPDATE AccountSET AccountBalance = AccountBalance 100WHERE AccountID = @AccountID

The three distinct operations in this transaction are two database queries and one operation of withdrawing cash. All three must succeed or fail together in an atomic manner, or the transaction should not be considered complete. Atomicity is the first ACID property.

Now let's change the nature of the transaction a bit. Let's say the original account balance is $150 and that within the same transaction, the user requests to withdraw $100 and to transfer another $75 to a second account. The first update query will succeed, changing the account balance to $150 $100 = $50. But the second operation will fail because there won't be enough money left in the account to transfer the $75. You therefore need a way to undo the cash transfer and withdrawal steps and return the database to its original state. You cannot leave the database midway through a transaction because it may be in an inconsistent state. In a real-world scenario, you would not normally wrap a withdrawal and a transfer in the same transaction in the event that you want at least one of the operations to be successful. The point here is to show how the data can end up in an inconsistent state between two operations. Rolling back operations that cannot be completed in this manner demonstrates the second ACID property, consistency.

Let's say the two operationscash withdrawal and cash transferare broken up into two distinct transactions instead of one but that they happen to run simultaneously, or concurrently. Each transaction will have to check the current balance. They will do this by attempting to execute a query like this:

SELECT AccountBalance FROM Account WHERE AccountID = @AccountID

Unless your system has explicit checks blocking concurrent reads, both transactions will get the same result: $150. Thus they will both assume that the account has enough funds for the transaction. One transaction will disburse $100, and the other will transfer $75. But this will result in an overall deduction of $100 + $75 = $175, even though the account actually has only $150. In a well-designed system, the transactions must be isolated from each other to prevent what is known as "dirty reads." A dirty read happens when data is read at one point in a transition state and the "read" doesn't reflect the data's true state at the end of the current operation. This brings us to the third ACID property, isolation.

Isolation means that other transactions attempting to request a common resource will be blocked. Blocking, in turn, seriously affects the response times of your application. As it turns out, you'll often want to relax this blocking behavior to suit your application architecture. This involves using isolation levels, which we'll discuss later in the chapter.

Finally, when you have successfully completed all your operations within a transaction, you don't want to lose the changes made. In other words, system failures must not affect the transactional integrity of your operations. This brings us to the fourth ACID property, durability.

Let's review the four ACID properties again before we continue:

• Atomicity Operations succeed or fail together. Unless all steps succeed, the transaction cannot be considered complete.

• Consistency Operations leave the database in a consistent state. The transaction takes the underlying database from one stable state to another, with no rules violated before the beginning or after the end of the transaction.

• Isolation Every transaction is an independent entity. One transaction will not affect any other transaction that is running concurrently.

• Durability Every transaction is persisted on a reliable medium that cannot be undone by system failures. Also, if a system failure occurs in the middle of a transaction, the completed steps must be undone by the system or the uncompleted steps must be executed to finish the transaction. This typically happens by use of a log that can be played back to return the system to a consistent state.

If you have been worked with T-SQL, You know that we have an IN keyword in our WHERE clause like the following instance:

SELECT * FROM MyTable WHERE ID IN (1, 2, 3)

In the example above, I selected a table from my database named "MyTable" and then select rows that the ID column of each row is equal to 1, 2 and 3. If rows are exists so the select statement should return 3 results.

When you're working with .NET framework 3.5, You may need to do such a thing using LINQ to SQL. In this post I'll show you how to do that.

Before we get started, lets review some simple LINQ commands and basics:

• In order to start using LINQ to SQL, You have to create a new item named LINQ to SQL Class by right click on your project in solution explorer, point to add menu and select new item as shown below:

• In the new item windows select LINQ to SQL class and name it SampleDb:

• Now it's time to add some SQL tables from our SQL Server Database by drag them from Server Explorer (Available in View menu) and drop them to our LINQ to SQL Class designer. When you do such a thing, Visual Studio adds some classes with properties and methods to your project automatically.

• We can add some code in order to access our data. First you have to declare a new variable from our LINQ to SQL class. By default, the name of our LINQ to SQL class is YouClassName + DataContext. So here it is:

private SampleDbDataContext db = new SampleDbDataContext();

You can select, insert, update and delete rows from your selected table(s). What we're looking for right now is to select 4 specific rows from our table to simulate SQL "IN" keyword. In order to do this, have to create an array of your selected IDs. In this case I want to select rows with IDs (1, 2, 3); So just create an integer array as following:

int[] MySelectedRows = {1, 2, 3} ;

Now lets write some codes in order to select some columns from our table:

var MyData = (from data in db.Mails
                          select data);

With the code above, you can select all rows in Mails table. Now we have to specify to select only rows with IDs 1, 2 and 3 so we must add a where clause to our select statement:

var MyData = (from data in db.Mails
                          where data.MailID == Convert.ToInt32(MySelectedRow)
                          select data);