giovedì 17 novembre 2011

Trace T-SQL statements generated by Entity Framework

If we want to keep track of a query with Entity Framework without use a profiler, we can use the method ToTraceString which is part of the ObjectQuery object. In practice, this method displays the commands sent from our T-SQL query to the database.

First, when using the ToTraceString method we need an open connection to the database, otherwise we will raise an exception.

Its use is very simple. Here an example:
using (MyEntities ctx= new MyEntities()) {
  var strSql = "SELECT VALUE t FROM MyEntities.Employees AS t";
  var myQuery = ctx.CreateQuery(strSql);
 
  ctx.Connection.Open();
 
  Console.WriteLine(myQuery.ToTraceString());
  Console.ReadLine();
}


The output will be something like:
SELECT
[it].[FirstName] AS [FirstName],
[it].[LastName] AS [LastName],
[it].[Age] AS [Age]
FROM [dbo].[Employees] AS [it]

One important thing about ToTraceString method is that it doesn’t execute the query but it's only output the statement to be executed.

domenica 24 luglio 2011

Layout for multiple file upload with jQuery

Within forms, many times we need to empower users to enter one or more files. In some cases you may need to perform the upload of multiple files, without knowing in advance how they might be.
Limit the number of inputs for uploading files is sometimes not the best choice, so we could give the user a chance to put other files in his complete autonomy.

This post explains how to use jQuery without any plugins for creating html for inserting multiple fileupload.

We start from a simple html page that contains only those elements essential for this application:


<!DOCTYPE html>
<html>
<head>
    <title>Multiupload file</title>
    <script type="text/javascript" src="Scripts/jquery-1.6.2.min.js"></script>
    <script type="text/javascript" src="Scripts/JScript.js"></script>
</head>
<body>
      
    <div class="container">
        <button id="btAddFileUpload" type="button">Add file</button>
        <div id="fileUp1">
            <input id="File1" type="file" />
            <button id="btDelete1" type="button" value="1" onclick="javascript:deleteUpload(value)">Delete</button>        
        </div> 
      
        <input id="Submit1" type="submit" value="Submit" />
    </div>
  
</body>
</html>
 

I copied into my "Scripts" directory latest jQuery library version, but you can refer it in the classic mode by Google:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>

I wrote my JavaScript code in an external file called "JScript.js" and referenced in html script tag. This is the code:

$(document).ready(function () {
    $('#btAddFileUpload').click(add);
});

//upload file tags counter
var count = 1;


//add fileupload method
function add() {

    //upload file tags increase
    count++;   
   
    //clone div tag with all children elements
    var copied = $('#fileUp1').clone(true);

    //increase all children tags ids
    $('input:file', copied).attr('id', 'File' + count);
    $('button', copied).attr(
        { id: 'btDelete' + count,
          value: count
        }
    );

    //increase div tag id
    $(copied).attr('id', 'fileUp' + count);

    /* another mode to change attribute names
    copied.children('input').attr('id', 'File' + count);
    copied.children('button').attr('id', 'btDelete' + count);
    */

    //append copied div and relative children tags in container div, but before submit button
    $(copied).appendTo('.container').insertBefore('#Submit1');
}


//delete method fileupload method
function deleteUpload(id) {

    //remove file upload (parent div and childrens)
    if (count > 1) {

        //decrease total amount of uploadfile tags
        count--;

        var nameElemToDelete = '#fileUp' + id;
        $(nameElemToDelete).remove();
    }


    //to avoid holes, recalculating all the ids after the removed one
    for (indx = id; indx <= count; indx++) {
           
        var inputDiv = $('#fileUp' + (parseInt(indx) + 1));
        $(inputDiv).attr('id', 'fileUp' + indx);
       
        $('input:file', inputDiv).attr('id', 'File' + indx);
        $('button', inputDiv).attr(
            { id: 'btDelete' + indx,
              value: indx
            }
        );
    }
}




At the begin, I call the $(document).ready jQuery statement to join the add function to add button at the startup (page entirely loaded).
Each time you click on the button "btAddFileUpload", all you clone the "
" tag including its children, renaming the id attributes with a new name including the number increases by one.
All this is done using the jQuery methos "clone" on an object (tag) that you want to replicate. 


With "deleteUpload" function you can delete the same tag (and all its childrens) and recalculate all ids to avoid holes. This was done to iterate over all the inputs file without any problems.

mercoledì 22 giugno 2011

C# - read range of dates without considering all holidays

The problem
I need to calculate the elapsed days between a date and the other, regardless of public holidays and national holidays.

The idea
The first thing to do was to remove from the range of dates on Saturdays and Sundays, in order to consider only working days.
For national holidays but I thought I'd use a database table containing a list of dates as smalldatetime fields.
But I did not want every year to update all the dates of the table, only to set the new year. So I decided to set any date on the database with a fixed year 2011 (but I could also use more than one year 'recently).
The only date to be updated every year is the Easter holiday because it is considered a date "variable".

This is the table:
















My solution
Calculating the difference (in days) between the end date and the start and this would eliminate any other festive dates, according to Saturdays and Sundays and holidays are based on. However, if a holiday falls in one of these two days, obviously the date is eliminated once.

This is the C# code:

public static int GetDateDiff(DateTime start, DateTime end) {

TimeSpan dsDiff = end - start;
int diff = dsDiff.Days;

int diff2 = diff;

if (diff <= 0)
return diff;
using (var ctx = new MyEntities()) {
  //run a cycle on any date, from start to finish
  for(int i = 0; i <= diff2; i++) {
   DateTime dtTemp = start.AddDays(i);
       //a cycle on any holiday date
                 foreach (var t in ctx.T_HOLIDAYS) {
   int yearDiff = t.date.Year - dtTemp.Year;  
   TimeSpan ts = t.date - dtTemp.AddYears(yearDiff);
                        if (ts.Days == 0 && t.date.DayOfWeek != DayOfWeek.Saturday && t.date.DayOfWeek != DayOfWeek.Sunday) {
    diff--;
    break;
   }
   else if (dtTemp.DayOfWeek == DayOfWeek.Saturday || dtTemp.DayOfWeek == DayOfWeek.Sunday) {
    diff--;
    break;
   }
  }//end foreach
 }//end for
}