In previous posts, how to insert new records using entity framework and how to update records using entity framework. Those two processes are foundation blocks of most of the data operations we all perform in an application. The examples that showed in those posts were very simple involving one record for one table. In more complex work flow processes you have more than one database operations that need to take place to accomplish the intended outcome. When you have multiple records and tables being manipulated, you have to ensure ACID nature of transactions. There are very few cases I have come across where one could afford to have change in one table succeed if another operation in the same chain fail.

In this post I will show how to use transactions in Entity Framework. If you have dealt with transactions in SQL server queries or for that matter any database operations, you will notice that when using Entity Framework the concept stays the same. You perform the following steps.

• Create a transaction
• Perform database operations with in scope of that transaction
• Commit or rollback transaction depending on out come of database operation

When you are using Entity Framework, transactions are handled by Transaction framework. You will not find reference to those namespaces in EntityFramework assembly. Those references exist in System.Transactions assembly. Lets see how you will use transactions in entity framework.

Assembly reference and namespace

Add references to System.Transactions.dll assembly in your project. And now include System.Transactions namespace in your code file.

Create new transaction

Key to using transaction is creating a transaction or logically speaking create a transaction scope which will enclose all the data operations that need to be included in one single transaction. Use TransactionScope class to create new instance of implicit transaction scope for operations.

using System.Transactions;

var txScope = new TransactionScope();
using (txScope){ ......  }

Perform database operations

With in scope of transaction perform all the data operations that I talked about in previous posts.

try
{
 dataContext.Projects.Add(project);
 dataContext.SaveChanges();
}                        

Commit Transaction

After all the database operations have completed and succeeded, then you can commit transaction by calling Complete method on TransactionScope object.

try
{
 dataContext.Projects.Add(project);
 dataContext.SaveChanges();
 txScope.Complete();
}

If you are looking for method like Rollback, you will not find it. No, this does not mean that transaction can not be rolled back using Entity Framework. The framework is designed to commit a transaction only after Complete method is called. If you do not want to commit a transaction, you can explicitly call Dispose on TransactionScope object or wait till TransactionScope object goes out of scope.

Here is complete code snippet from one of my sample projects.

protected void OnAdd(object sender, EventArgs e)
{
  if (!ValidateContracts())
  {
    ErrorMessage_Label.Visible = true;
    return;
  }

  var project = new Project()
  {
   ProjectName = AntiXss.HtmlEncode(ProjectName_TextBox.Text.Trim()),
    ProjectDescription = AntiXss.HtmlEncode(ProjectDescription_TextBox.Text.Trim()),
    Active = true,
    Deleted = false,
    ClientId = _thisClient.ClientId,
    CreateDate = DateTime.UtcNow
  };

  var dataContext = new PMDataContext(Global.ConnectionString);
  using (dataContext)
  {
   var txScope = new TransactionScope();
   using (txScope)
   {
    try
    {
     dataContext.Projects.Add(project);
     dataContext.SaveChanges();
     txScope.Complete();
    }
    catch (UpdateException upEx)
    {
     //TODO: log this update exception
     System.Diagnostics.Debug.WriteLine(upEx.Message);
    }
    catch (Exception ex)
    {
     //TODO: log thjis general exception
     System.Diagnostics.Debug.WriteLine(ex.Message);
    }
   }
  }
}

In previous post How to insert new record using Entity Framework, I discussed one aspect of CRUD operation in an application or process. In this post I will talk about another aspect of the process, how to update a record using Entity Framework. If you are building some kind of business intelligence aplication where only action you have to take is read the data, then you may not be interested in UPDATE part of the process. But most of the applications require you to undertake whole spectrum of operations. And UPDATE is big part of the process.

The process of updating database records using Entity Framework is pretty straight forward. In previous post you saw that you use the collection property of Data Context to add new object in it and then call SaveChanges method. You will still be using SaveChanges method. Only difference is that you are not operating on the collection object. First you will use LINQ query on Data Context to get instance of the record object. Then make the changes to that object and then call SaveChanges. Entity Framework keeps track of what records are being changed when you change any property value of an object. When you call SaveChanges, it prepares a list of objects that need to be updated. And then take appropriate action. All the tracking is transparent to you. The following code snippet shows how you can update record using Entity Framework.

protected void OnUpdate(object sender, EventArgs e)
 {
     if (!ValidateInput())
     {
         ErrorMessage_Label.Visible = true;
         return;
     }

     decimal billRate = 0.0M;
     if (!string.IsNullOrEmpty(BillRate_TextBox.Text.Trim()))
     {
         decimal.TryParse(BillRate_TextBox.Text.Trim(), out billRate);
     }
     var displayName = AntiXss.HtmlEncode(DisplayName_TextBox.Text.Trim());
     if (string.IsNullOrEmpty(displayName))
     {
         displayName = AntiXss.HtmlEncode(FirstName_TextBox.Text.Trim());
         if (!string.IsNullOrEmpty(AntiXss.HtmlEncode(LastName_TextBox.Text.Trim())))
         {
             displayName += string.Format(" {0}", AntiXss.HtmlEncode(LastName_TextBox.Text.Trim()));
         }
     }
     var dataContext = new PMDataContext(Global.ConnectionString);
     var member = (from memberData in dataContext.Members
                   where (memberData.CompanyId == _thisCompany.CompanyId 
                             && memberData.MemberId == _thisMember.MemberId)
                   select memberData).First();

     member.MemberFirstName = AntiXss.HtmlEncode(FirstName_TextBox.Text.Trim());
     member.MemberLastName = AntiXss.HtmlEncode(LastName_TextBox.Text.Trim());
     member.MemberDisplayName = displayName;
     member.Active = _thisMember.Active;
     member.Deleted = _thisMember.Deleted;
     member.BillingRate = billRate;
     member.Availability = Convert.ToInt32(Availability_Dropdown.SelectedValue);

     try
     {
         dataContext.SaveChanges();
     }
     catch (Exception ex)
     {
         // TODO: Log the exception.
         System.Diagnostics.Debug.WriteLine(ex.Message);
     }
}

The implementation gets record for a specified Member using LINQ query. Then updates various values. And at the end calls SaveChanges. Very simple and straightforward process.

In earlier post, How to insert records in database using Entity Framework, I discussed how you can use code first approach to create a POCO (Plain old CLR object) and then use data context to manipulate the tables. In that example POCO name was one to one match with table names in the database. Entity Framework was able to map the table with class name without any problem.

But you will always have cases where table names will not match with class names. This will happen if you are trying to reuse some existing code where classes were created based on some older schema. But now you have database tables that have totally different names. Another example, which I will use in this discussion, is using ASP.Net membership provider tables and procedures. You will notice that each database table is prefixed with aspnet_ by default. Now if you have classes with named like aspnet_Applications in your project, it will violate all the coding standard constraints and policies. And if you have some TFS check-in policy in place then you are out of luck because all policies will be violated.

There is a simple solution to this problem. You can create your POCO names to comply with standard naming convention. For example in my application, I want to query aspnet_Applications table but I want to keep my class name as Application. Following code snipper shows how I defined my class.

using System.ComponentModel.DataAnnotations;

[Table("aspnet_Applications")]
public class Application
{
 public Guid ApplicationId { get; set; }
 public string ApplicationName { get; set; }
 public string Description { get; set; }
}

I added TableAttribute to the class and set the name of the actual database table to which this class maps to. To add this attribute you will need to include System.ComponentModel.DataAnnotations in your class file.

If you do not add this attribute, you will see an exception like below when trying to query data context for the objects from that table.

Invalid object name dbo.Application

You can follow the same approach to map data base fields to class properties that do not match one to one.

Recently while working on a reporting application, one of the tasks was to read and update an Excel file using .Net code. There are couple of approaches you can use to do it. If you are looking for a well defined Excel API to accomplish the tasks then you will have to use Excel Object model that gets installed on your machine when you install Microsoft Office. But this is not an option when you are running your application from a server where installing Microsoft Excel may not be an option. In that case, you can use ADO.Net objects to accomplish the tasks. What you need to is simple add reference to System.Data.OleDb in your project and you are good to go. Following code snippet shows how you can read an excel file. This code just iterates over all rows that it can find in a given worksheet. In subsequent posts I will demonstrate different CRUD operations that can be performed using ADO.Net on excel file.

class Program
{
 static void Main(string[] args)
 {
    int columnCount = 3;
    var excelFileConn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;
       Data Source='Data_20101213.xls';Extended Properties=Excel 8.0;");
    excelFileConn.Open();
    try
    {
     var cmd = new OleDbCommand("select * from [12-13-2010$]", excelFileConn);
     var da = new OleDbDataAdapter(cmd);
     var dt = new DataTable();
     da.Fill(dt);
     if (dt.Rows.Count != 0)
     {
        foreach (DataRow dr in dt.Rows)
        {
          Console.WriteLine("{0}\t{1}\t{2}", dr[0], dr[1], dr[2]);
        }
      }
     }
     finally
     {
       if (null != excelFileConn &&
           excelFileConn.State == ConnectionState.Open)
       {
          excelFileConn.Close();
       }
      }
    }

Some times when you execute a SQL query to insert or update data into a table, you may see the following error.

String or binary data would be truncated.
The statement has been terminated. 
Description: An unhandled exception occurred during the execution of the current web request. 
Please review the stack trace for more information about the error and where it 
originated in the code. 
Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

What this error means is that you are trying to insert more data into a field than the schema specified for. For example if you have a varchar field and limited its size to 100, if you try to insert a string with length more than 100, then you will end up with this exception. In older version of SQL, the server used to silently let this case go through and truncate the data and you would not know it. And then you will notice it when trying to display these fields in some form.