Wortzel's blog

Finding a new job at Massive Impact

As I already posted I left my job two month ago and after a small vacation I started to look for a new place to work at.

The place I finally chose at is Massive Impact. Massive Impact is a start-up company that gives a very smart solution for sales over the mobile phones.

It's seems to be a great company with very talent people combine with .Net technologies and for desert, they working with Scrum methodology. It's really good place to work at!

Posted Friday, August 31, 2007 2:09 PM by Avi Wortzel | 9 comment(s)

DLINQ – Advanced topics – Debug mode – part 6

DLINQ is a great ORM engine with a lot of advantages. When I learned this tool I asked myself how I can look at the SQL code that it generates. At the beginning I did it by running my project in debug mode with a break point after the query statement. The SQL statement appears when we hover over the result object or by using the watch window.

Debugging using the default hover debug tool

Debugging using the watch window

As you can see it isn't the most comfortable way to look at the generated SQL. So I read Scott Guthrie recommendation about the debug visualizer tool and I decided to download this debug tool and to use it.

As you can see in the following screenshots it makes it easier and the SQL statement that have been generated looks more understandable (b.t.w the debug visualizer is an integral part of the VS2008 in version beta 2).

Debugging using the visualizer tool

Original query display in Debug visualizer tool

The query result window after w clicked on the "Execute" button

Until now everything is great (Only during development process of course). But what should we do if we want to print all your SQL statement to an output (for example to the screen) without entering the debug mode? Or if we want to know the exact SQL statement that the DataContext generates for manipulation operations before they really executes?

In this case DLIND provide us a series of debugging methods:

1. DataContect.Log – The Log property in DataContext object give us the ability to print SQL query statement before they execute. The type of the Log is TextWriter, so if you working on console application you can initialize this property with the Console output stream. Like in this example:

DataClasses1DataContext db = new DataClasses1DataContext();
db.Log = Console.Out;

// Get all customers list
IQueryable<Customer> list = from x in db.Customers
                            where x.Code > 0
                            select x;

foreach (Customer customer in list)
    Console.WriteLine(customer.Code);

The output in the console will look like this:

And if you are working in web application you can initialize this property with the output stream of the Response object. Like this:

DataClasses1DataContext db = new DataClasses1DataContext();
db.Log = Response.Output;

// Get all customers list
IQueryable<Customer> list = from x in db.Customers
                            where x.Code > 0
                            select x;

foreach (Customer customer in list)
    Console.WriteLine(customer.Code);

The output in the web page will look like this:

2. DataContext.GetCommand(IQueryable query) – This method gives us the command object according to the query we gave as a parameter. From the command object we can retrieve the command text and the command parameters. It also gives us a good picture of how the DataContext builds the right command object based on our query. (In VS2008 beta 1 there was a similar lightweight method that called GetQueryText(query) that return a string). The fallowing example shows how to use this method:

IQueryable<Customer> list = from x in db.Customers
                            where x.Code > 0 
                            && x.LastName.StartsWith("W")
                            select x;

IDataParameter parameter;
IDbCommand command = db.GetCommand(list);
Console.WriteLine(String.Format("Command text: {0}",
    command.CommandText));

for(int i=0;i<command.Parameters.Count;i++)
{
    parameter = command.Parameters[i] as IDataParameter;
    Console.WriteLine("Parameter number: {0} 
        Parameter type: {1} Parameter value: {2}",
        i, parameter.DbType, parameter.Value);
}

And this is the output for this code:

So in the next time you want to debug your DLINQ code or to add an external logger to your application try to use one of these options.

How to add multiple tracking accounts to a page in Google analytics

I want to add some tracking tools to my blog, just to know if someone is interesting in my posts. One of the tracking tools I decide to add was Google analytics – a really powerful tool that gives you a visual view on your site visitors, must viewable posts, traffic sources and much more. So I got into its site and registered my blog as one that I want to track for. Like all other tracking tools I just needed to add a piece of script code and everything supposed to work perfect. The script looks like this:

<script src="http://www.google-analytics.com/urchin.js" type="text/javascript">
</script>
<script type="text/javascript">
    _uacct = "UA-XXXXXXX-X"; // My Account id
    urchinTracker();
</script>

I added this script to the appropriate place but the data wasn't arriving to Google analytic site. So I started to look at the source code to check if the script was really in the place it should be. Than I saw the problem! There was another script that looked like mine, but with a different account id (My blog is a part of a Community Server, the community administrator added this tracking to all community blogs in high level). After a long search I found a solution to this problem. You must reset the "_uff" property before you register with the second account. It is an indication that tells the tracker to wait for others account registration before sending the data to Google analytic site.

The two accounts registration looked like this:

This javascript registration file will appear only once
<script src="http://www.google-analytics.com/urchin.js" type="text/javascript">
</script>

The first account registration
<script type="text/javascript">
    _uacct = "UA-XXXXXXX-X";
    urchinTracker();
</script>

The second account registration
<script type="text/javascript">
    _uff = 0; // Reset for second account 
    _uacct = "UA-YYYYYYY-Y";
    urchinTracker();
</script>

And now everything works perfect!

Posted Thursday, August 23, 2007 3:36 PM by Avi Wortzel | 20 comment(s)

DLINQ – Advanced topics – How it works in ASP.NET application – part 5

There are two approaches to work with the DataContext object. The first approach is to work with a single DataContext object which is responsible for all the quires and the manipulations in our system. In the second approach we work with several DataContext object. Each request refer to a single atomic operation and will work independency to others operation (The DataContext isn't aware to the previous operations at the current entity). The ASP.Net is a good example for the second approach because each request is being handled independently from the previous operation.

For example, if we'll create a user page with view and edit mode. In the first time that the page loads we present the user's details and after the client click on change name button we'll entire the edit mode and change the customer name.

public partial class User : System.Web.UI.Page
{
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindUserDetailsView ();
            }
        }

        private void BindUserDetailsView()
        {
            DataClasses2DataContext db1 = new DataClasses2DataContext();
            IQueryable<Customer> customer = db1.Customers.Where(x =>x.Code==1);

            UserView.DataSource=customer;
            UserView.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            DataClasses2DataContext db2 = new DataClasses2DataContext();
        
            // Init a new customer object and set the old object primary key
            Customer customer = new Customer(){Code=1};
        
            db2.Customers.Attach(customer);
            customer.FirstName = "Yuval";
            db2.SubmitChanges();
            BindDetailsView();
        }
}

In the first time the page will look like this:

And after we will click on the "Change name" button we create a new Customer instance and initialize it with the entity primary code. Then we need to attach it to our Customer collection and perform some changes and save them (By calling to the SubmitChanges method).

In this scenario we have tow separated data context object. In the first one we used to retrieve the customer details and in the second we used for the update the customer details.

If you want it to work you should do this steps:

1. Create a new Customer entity object.
2. Set the customer primary key (The primary key that has been defined in the database).
3. "Attach" the customer object. It's telling the DataContext engine to look for this entity and trace all changes in it.
4. Make some changes in the customer object.
5. Call the SubmitChanges method. Behind the scene it performs an update operation on the database and puts the customer primary key in the "where statement". In this scenario we'll get something like this:

UPDATE Customers

SET FirstName = @p1

WHERE (Code = @p0)

Notice:

You must check the UpdateCheck parameter of the "Column" attribute for the "FirstName" and "LastName" columns. If the value of this parameter isn't equal to "System.Data.Linq.UpdateCheck.Never" you will get this error:

"Row not found or changed (System.Data.Linq.ChangeConflictException: Row not found or changed)".

b.t.w I really don't know why, but the auto generation code tools (SqlMetal and "Linq to sql file") don't generate the UpdateCheck parameter in the Column attribute. And therfore, we must add this parameter with the UpdateCheck.Never value, to each column that isn't an integration part from our table primary key. If you have any idea why the automatic tools aren't generating this piece of code, I'll be happy to learn.

Read more:

Introducing to DLINQ

·   Introducing to DLINQ - Entities declaration - part 1

·   Introducing to DLINQ – How to write the queries - part 2

·   Introducing to DLINQ – How to write data manipulation in DLINQ (Update, insert and delete) - part 3

Advanced topics in DLINQ:

·   DLINQ – Advanced topics – Transaction support – part 4

·   DLINQ – Advanced topics – How it works in ASP.NET application – part 5

·   DLINQ – Advanced topics – Debug mode – part 6

Posted Friday, August 17, 2007 4:18 PM by Avi Wortzel | with no comments

DLINQ – Advanced topics – Transaction support – part 4

As I have mention before the DLINQ is a part of the ADO.NET family and this is a major reason for it to support transaction mechanisms. The DLINQ support the ADO.NET Transaction and the transaction scope. Let's see how we can use it at the following scenarios:

            ADO.NET Transaction

At ADO.NET Transaction we have to handle the entire transaction process. It includes: opening a connection, starting the transaction, commit and rollback when needed, finish the transaction and close the connection.

In a case that the connection object has already been initialized in the DataContext class, we should start the transaction by calling the BeginTransaction(). All the changes that will occur during the current session will be executed in the same transaction.

      DataClasses1DataContext db = new DataClasses1DataContext();

      // Get all customers list

      IQueryable<Customer> list = from x in db.Customers

                                  where x.Code > 0

                                  select x;

      Customer cust1 = list.Single<Customer>(x => x.Code == 1);

      Customer cust2 = list.Single<Customer>(x => x.Code == 2);

      // Update the first customer object

      cust1.FirstName = "Avi";

      cust1.LastName = "Wortzel";

      // Delete the second customer object

      db.Customers.Remove(cust2);

      // The update & delete operation will execte

      // in the same transaction       

      db.Connection.Open();

      db.Transaction = db.Connection.BeginTransaction();

      try

      {

          db.SubmitChanges();

          db.Transaction.Commit();

      }

      catch

      {

          db.Transaction.Rollback();

          // And do some error handling...

      }

      finally

      {

          db.Connection.Close();

          db.Transaction = null;

   }

            TransactionScope

When we are using the TransactionScope the transaction starts as a single transaction and will be promoted to a distributed transaction only if required (you can find more details here). The DataContext has fully integration with the TransactionScope object. The following example will demonstrate how easy is to use the transaction within the TransactionScope section.

      DataClasses1DataContext db = new DataClasses1DataContext();

      // Get all customers list

      IQueryable<Customer> list = from x in db.Customers

                                  select x;

      Customer cust1 = list.Single<Customer>(x => x.Code == 1);

      Customer cust2 = list.Single<Customer>(x => x.Code == 2);

      // Update the first customer object

      cust1.FirstName = "Avi";

Introducing to DLINQ – How to write data manipulation in DLINQ (Update, insert and delete) - part 3

DLINK gives maximum flexibility in manipulation data with objects. You must retrieve the object before you update or delete it, and you must initialize a new object before inserting it into the db.

Let's see some examples:

Insert a new employee:

MyDbDataContext db = new MyDbDataContext();

// Init a new employee object using Object Initializer

Employee emp = new Employee()

{ Id = 10, Name = "New Employee", DepartmentCode = 5 };

db.Employees.Add(emp);

db.SubmitChanges();

You can see that I must create a new object before I added it to the Employee collection. After I added it, I must call the SubmitChanges method in order to commit the operation.

Delete an employee:

MyDbDataContext db = new MyDbDataContext();

Employee emp = db.Employees.Single(x => x.Id == 10);

db.Employees.Remove(emp);

db.SubmitChanges();

In this case the code is very similar to the insert operation (except that I retrieve the employee before).

Update an existing employee:

MyDbDataContext db = new MyDbDataContext();

Employee emp = db.Employees.Single(x => x.Id == 10);

emp.DepartmentCode = 1;

db.SubmitChanges();

In this case I retrieved an existing employee and changed his department code. In this case we didn't need to get throw the employee collection, we could update his details directly.

Introducing to DLINQ – How to write the queries - part 2

In this post I'll focus on the query options in DLINQ.

Let's take two simple scenarios:

1. Retrieve all the employees with the letter "a" in their first name.

2. Retrieve all employees in the "R&D" department

There are three common ways to write these queries in DLINQ:

      1. Using SQL syntax:

The most simple (or maybe intuitive) way to write a standard query statement is by using the SQL syntax.

For example, for the first scenario we should write a code like this:

IEnumerable<Employee> list = from x in db.Employees

                                where x.Name.Contains("a")

                             select x;

For the second scenario we should write this one:

IEnumerable<Employee> list =

from x in db.Employees

            where x.Department.Description.Equals("R&D")

         select x;

2. Using lambda expression:

Lambda expression is a new feature in framework 3.0 which gives us a new friendly and concisely syntax for anonymous delegates.

And now the first query will look like this:

IEnumerable<Employee> list = db.Employees.Where(

      x => x.Name.Contains("a"));

And the second one will look like this:

IEnumerable<Employee> list = db.Employees.Where(

x => x.Department.Description.Equals("R&D"));

3. Using anonymous delegates:

This isn't a common method but you can still write the query using the Anonymous delegates (one of the new .Net 2.0 feature).

The same query but now with anonymous delegate:

   IEnumerable<Employee> list = db.Employees.Where(

      delegate(Employee emp)

      {

            return emp.Name.Contains("a");

   });

And the second one:

IEnumerable<Employee> list = db.Employees.Where(

      delegate(Employee emp)

      {

            return emp.Department.Description.Equals("R&D");

   });

Notes:

1. If we'll look on the compile code of the sql syntax and the lambda expression in reflector we'll get exactly the same thing.

2. The two first options (sql syntax & Lambda expression) are using an expression tree that really improves the application performance. We don't need to bring all the table rows when we need just a few of them. In run time the lambda expression will integrate to a one sql statement. But on the third case, (when we are using anonymous delegates) it will always bring all the data and after we performed some operation (GetEnumerator() method for example) on the query the anonymous delegates will execute for all the result records.

Introducing to DLINQ - Entities declaration - part 1

One of the major parts in LINQ project is DLINQ – .Net Language Integrated Query for relational Data. DLINQ is a smart data access layer framework (component of the ADO.NET family), with an advanced runtime infrastructure and design time tools for managing data (queries and data manipulations) from a relational db as an object.

The usage of this framework is very easy: The basic class in DLINQ is call "Entity class", each class represent a single database table. The properties in this class will represent the table columns.

For example:

Employees table:

Departments table:

 The tables diagram look like this:

Now let's create the entity classes for these tables.

We will create new classes which are called "Employee" and "Department". We have to declare the classes as a DLINQ table by adding the "Table" attribute (This attribute found in the System.Data.Linq namespace), and declare the class properties as table columns by adding the Column attribute.

Our classes should look like this:

[Table(Name="Employees")]
class Employee
{
      [Column(IsPrimaryKey = true)]
      public int Id;

      [Column]
      public string Name;
} 
 
[Table(Name="Employees")]
class Department
{
      [Column(IsPrimaryKey = true)]
      public int Id;

      [Column]
      public string Name;
} 

Notes:

1. The Table attribute has Name parameter, it's good if you want to call your object in a different name from the name in the database table.
2. The Column attribute has several parameters for details declaration of the database column. In this example I used the IsPrimaryKey parameter that specifies if this column is the table primary key.

And now let add the relationship between those tables:

[Table(Name="Employees")]
class Employee
{
       …
    
      private EntityRef<Department> _Department;

      [Association(ThisKey = "DepartmentCode", OtherKey="Id")]
      public Department Department
      {
          get
          {
              return this._Department.Entity;
          }
          set
          {
              return this._Department.Entity = value;
          }
      }    
}


[Table(Name="Employees")]
class Department
{
         …

        private EntitySet<Employee> _Employees;
        
        [Association(ThisKey="Id",  OtherKey="DepartmentCode")]
        public EntitySet<Employee> Employees
        {
            get
            {
                return this._Employees;
            }
            set
            {
                this._Employees.Assign(value);
            }
        }
}

Note:

In the father's table of the relationship (In department table) we used the EntitySet class to hold a list of "sons". In the son's table we used the EntityRef struct in order to give us the ability to see the father object when we holding the son (if we want to get the department name for example when we holding an employee object).

Before we are going to use our new class lets talk about the DataContext engine.

The DataContext is the smart brain which works behind the scene. You just need to initialize it with the connection and use his sophisticated functionality. This engine is responsible to translate the queries and manipulation that you made on the object to a SQL statements (And in a case there is a return value it will create an appropriate object with this data). In addition, it implements the SQO (Standard Query Operators) and gives us the ability to use the standard familiar SQL syntax.

Now after we know what DataContxt is good for, let's use it. In the following example I'll create an instance of the DataContext class (Initialized by the connection string). I'll request from it to retrieve the object that manage my employees table. After I'll get that object I'll use some SQL to retrieve all employees that their names start with the string "Y". Then I'll print the results to console.

string connectioString = "...";
DataContext myDb = new DataContext(connectioString);
Table<Employee> employees = myDb.GetTable<Employee>();
var query = from c in employees
                  where c.Name.StartsWith("Y")
                  select c;
            
foreach (var var in query)
{
       Console.Write(var.Name);
} 

Note:

In this example I used the weakly-type for the table name. The best practice here is to inherit from the DataContext class, and to create collection for each table that managed by the DataContext.

Posted Thursday, August 02, 2007 11:16 AM by Avi Wortzel | with no comments