LINQ Examples

 Example 1

listProjectPersonDO  is a List<ClsRelProjectPersonDO>

foreach (ClsRelProjectPersonDO clsRelProjectPersonDO in 
            listProjectPersonDO.Where(element => element.IdProjectDomainSubdomain == row))
            {


             }

 Example2

 GeneralReport is a DataTable

 var qWorkingArea = GeneralReport.AsEnumerable()
             .GroupBy(r => r.Field<String>("workingArea"))
            .Select(g => new
            {
                totalW = g.Count(),
                workingArea = g.Key,

                onBoardW = ((from c in g where c["status"].ToString() == "ON BOARD" select c).Count())
              })
            .OrderBy(x => x.workingArea);

varGetTotalWorkingAreasOB = qWorkingArea.Sum(x => x.onBoardW); 

Example 3

How to do many selects.
 
var result = pyramid.Select(element => element.ClsRelProjectPersonDO.Where(item => item.IdProjectPerson == 
idProjectPerson)).First();

IEnumerable<ClsRelProjectPersonDO> a = result;

Asp - Send a popup message

 To send a message follow the next steps:

1. Add the reference:  using AjaxControlToolkit; 
2. Build the message through an alert using JQuery
3. Use the method RegisterClientScriptBlock 

 The function should look like this:

        StringBuilder sb = new StringBuilder();
        sb.Append("<script type=\"text/javascript\">alert('");
        sb.Append(mensaje.Replace("'", ""));
        sb.Append("'); ");
        sb.Append("window.location.href='");
        sb.Append(url);
        sb.Append("';</script>");
       ToolkitScriptManager.RegisterClientScriptBlock(this, typeof(Button), "Mensaje", sb.ToString(), false);

SQL - User-Defined Type (DataTable)

How to create a User-Defined in your database and use it in C# like DataTable

Create the data type and refresh the DB

[type_name] - The name for the new data type
[parameter] - The name of your paramenter
int - The data type
NULL - Declare if the parameter has to be null or not

 CREATE TYPE [relProjectDS] AS TABLE(
 
    [idProject] int NOT NULL ,
    [idCatDomain] int NULL,
    [catSubdomain] [nvarchar]  (50) NULL
)

Create the Store procedure

CREATE PROCEDURE  [dbo].[insertProject]
    @myDataType relProjectDS readonly
AS
BEGIN
      insert into [dbo].[Table] select * from @myDataType
END

Create and fill the DataTable in C#

idProject- Name of the parameter according with data type from the DB
typeof(int) - The type definition according with the DB
Rows.Add(param1, param2) - The method to add each row to the DataTable

DataTable projectsTable = new DataTable();
projectsTable.Columns.Add("idProject", typeof(int));
projectsTable.Columns.Add("idCatDomain", typeof(int));
projectsTable.Columns.Add("idCatSubdomain", typeof(int));

projectsTable.Rows.Add(1,2,3,4,5);

Pass dataTable as parameter

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})
{
    var dt = new DataTable(); //create your own data table
    command.Parameters.Add(new SqlParameter("@myDataType", dt));
    SqlHelper.Exec(command);
}

Update dataTable in Store Procedure

In the store procedure our dataType is readonly so to modify it we can pass to a temporally table and then update the data.

In the following steps after insert in another table we get the ID and the update the ID in the temporally table.

Finally we insert the teporally table in the DB. 

1.  SET @idProject = (SELECT SCOPE_IDENTITY()) 
2.  INSERT INTO #temp_table SELECT * FROM @relProjectDS
3. UPDATE #temp_table SET idProject = @idProject 
4. INSERT INTO [dbo].[relProjectDomainSubdomain] (idProject, idCatDomain, idCatSubdomain) SELECT idProject, idCatDomain, idCatSubdomain FROM  #temp_table

 

Tips:

How to create a temporal table

create table #temp_table
    (
        [idProjectDomainSubdomain] [int] IDENTITY(1,1) NOT NULL,
        [idProject] [int] NULL,
        [idCatDomain] [int] NOT NULL,
        [idCatSubdomain] [int] NULL,
    )

ASP - Inside a Gridview how get the DropDownList index

In the view:

• Define the method in the event OnSelectedIndexChanged
• Add the tag AutoPostBack="true"

<asp:TemplateField HeaderText="Competence Domain">
       <ItemTemplate>
              <asp:DropDownList ID="ddlCompDomain" runat="server" AppendDataBoundItems="true" AutoPostBack="true" OnSelectedIndexChanged="ddlCompDomain_SelectedIndexChanged">
              </asp:DropDownList>
       </ItemTemplate>
</asp:TemplateField>


In the code inside the method ddlCompDomain_SelectedIndexChanged:

1. Cast the sender object to the element you wanted to know the index, for our case a DropDownList
2. From the DropDownList get the GridViewRow through the parent property
3. Get the index with RowIndex

protected void ddlCompDomain_SelectedIndexChanged(object sender, EventArgs e)
    {
        DropDownList ddlCurrentDropDownList = (DropDownList)sender;
        GridViewRow grdrDropDownRow = ((GridViewRow)ddlCurrentDropDownList.Parent.Parent);

        TextBox2.Text = grdrDropDownRow.RowIndex.ToString();

        TextBox3.Text = ddlCurrentDropDownList.SelectedItem.Text;
    }

JQuery - Ajax

AJAX (Asynchronous JavaScript Xml)

This is the method to invoke ajax

$.ajax({   

     url: "my_data.xml" ,      // URL of you want to GET via AJAX
    cache: false,
    dataType: "xml",           // data - The data return from the Ajax call

    success: function(xml_reader){       // Run this function is the method ajax is successful

        $(xml).find("runner").each(function() {

  var info = '<li>Name: ' + $(this).find("fname").text() + ' ' + $(this).find("lname").text() + '</li>';

 if( $(this).find("gender").text() == "m" ){
$('#finishers_m').append( info );
}else if ( $(this).find("gender").text() == "f" ){
$('#finishers_f').append( info );
}
  $('#finishers_all').append( info );
});                

    }

});

Ajax methods in JQuery

$.get()
$.getJSON(url_to_load, function(json){} ) - This method is the shortcut from $.ajax();

$.getScript()

$.post()

$.load()

Send information using Ajax

There are two ways: serialize and serializeArray

$("#my_form").serialize(); -  Sent the data separeted by &

Ej.-  a=1&b=3&c=5

$("#my_form").serializeArray();  -  Sent the data as following

{

   {

       a: "1", 

       b: "3",

       c: "5"  

   },

  {

       a: "11" ,

       b: "31",

       c: "51"  

   },

}

This method sent the information to the defined URL

$.post(url_to_send , data , function(json){ 

});

JQuery - Part II

Declare Objects

You can create objects using JavaScript, also you can assign this object to a variable.

function object(param1, param2){

 this.param   = param1;

 this.param2= param2;

  my_function: function(){

  

  }

}

var my_object = object(1, 2);

Properties

To access the properties use object.property

Ej:  var name =  object.param2;

Using JSON the syntax is    object['param2']

Arrays

The arrays can be created with the following sintax

var array = new Array();

var array = new Array('obj1', 'obj2', 'obj3');

var array = ['obj1', 'obj2', 'obj3'];

Functions

$.inArray get the index where is the element.

var element_index = $.inArray('value', array);

Window object

Each time a tab is open a window object is created.

Methods

window.onFocus - tells when a tab is activated.

window.onBlur - detects when the tab lose the focus.

Functions

window.setTimeout( function(){}, 400) - Wait the defined time before call the function

window.setInterval( function(){}, 400) - interval between a function is run repeatedly

window.clearInterval( function(){} ) - wipes clear the schedule of repetead function calls

JQuery - Methods

Method Listener

There are two ways to bind events to elements

Case 1: 

$("my_element").click( function () {

} );

Case 2: 

$("my_element").bind('click',function () {

} );

Unbind method

The function .unbind remove the mehod pass as paramenter. 

Also the function .each iterate in all the elements from the element selector.

$("div.guess_box").each( function(){

$(this).unbind ('click');

});

Functions

There are two ways to declare a function

Function declaration

function nameFunction() {

   //Code

}

Function variable

var nameFunction = function () {

   //Code

}

Anonymous Function

Is when inside another function you declare your own function. In this case $(this).unbind('click'); is the anonymous function.

$("div.guess_box").each( function(){

$(this).unbind('click');

});

Contains Function

Compate inside this if have the element with Id "has_discount"

if ($.contains(this , document.getElementById("has_discount"))) {

$(this).addClass("discount");

}else{

$(this).addClass("no_discount");

}

Functions Catalogue

$("p").empty() - Delete all the content from an element

$("span").replaceWith("<div>Change DOM</div>") - Delete all the content from an element

$("span").before("<div>Change DOM</div>") - Insert a element before span

$("span").after("<div>Change DOM</div>") - Insert a div element after span

Animate Functions

This function only work in numerical properties. The first parameter is the property to be affected with the end value and the second parameter is the duration.

The default time is 400 milliseconds.

$("p").animate({left:"100 px"}, 500);

In the first parameter can include many properties.

$("p").animate({left:"100 px", width: "200"}, 500);

Climb the DOM

parent() - Get the father element

$(".element").parent().   

$(".element").parents(). - Get all the parents elements   

$(".element").prev() - Get the first sibling  to the left

$(".element").children() - Get all the children elements

$(".element").next() - Get from the sibling elements, the one at the right from the first element at the left.

$(".element").closet("#id_element") - Climb through all parents until find the #id_element parent.

Filter the DOM

$(".element").children().first(); - Get the first element from the collection.

$(".element").children().last(); - Get the last element from the collection.

$(".element").children().eq(2); - Get the element from the index.

$(".element").children().slice(2,3); - Get the elements between the indexes.

$(".element").children().filter(".organic"); - Get all the father elements from the selector element.

$(".element").children().not("#id_element"); - Get the elements that not mathc the #id_element.

Best Practice

$variable  - Use a dollar sign before the variable name to indicate you store values from JQuery.

Positions

• Relative
  
  
  
  
  
  
• Absolute

           

• Fixed

        

JQuery

Function

$( ) is the shortcut of jquery( )

Syntaxis :   

• $( "Selector Element" ).Method();
• $( "Selector Element" ).Method( Function( ){  } );

JQuery select elements as CSS

h1 -> Element selector

.h1 { color: red}   ->  Css Class select a grouping of elements

#my_h1 { color: #3300FF; } -> Id Selector, only affects one element

Example 1

$("span.Italian").toggle();

<span class="Italian">Nel Mezzo del cammin di 

nostra vita</span> 

<span class="English">In the middle of this road 

called "our life"</span> 

  

<span class="Italian">mi ritrovai per una selva 

oscura</span>

Example 2

$("p#mytext").show();

<p id="mytext">One morning, when Gregor Samsa 

woke from troubled dreams . . . 

</p> 

<p id="mytext">he found himself transformed in 

his bed into a horrible vermin.</p> 

<p>He lay on his armour-like back, and if he 

lifted his head a little . . . </p>

Set up JQuery

$(document).ready( function() {

});

This mean when the DOM is ready and loaded, then do the function.

Human-Computer Interaction - The Power of Prototyping

What do Prototypes Prototype?

Feel - What might it look like?

Implementation - What might it work like?

Role - What might the experience be like?

The best way to have a good idea is to have lots of ideas.

   - Linus Pauling

• Biggest changes at early stages.

Evaluating Designs

1. Labs
2. Surveys
3. Feedback from Experts
1. Comparatives Experiments
2. Participant Observation
4. Simulation

Users

• Estreme Users
• Users seldom use it

Web Services - Layers

Presentation

• Operation Contract
• Data Contract
• Implementation  -> svc.cs

RN

• Business Rules

Data

•  Data Access Layer (DAL)
• Entities Class
• Data Repository
• Data Methods

Lightswitch - Open a Web Page

This code show how to open an external web page. Evaluating if is an Desktop or Web Apllication.


using System.Runtime.InteropServices.Automation;
using System.Windows.Browser;
using Microsoft.LightSwitch.Client;
using Microsoft.LightSwitch.Threading;

var uri = new Uri("http://google.com", UriKind.RelativeOrAbsolute);
Dispatchers.Main.BeginInvoke(() =>
{
   try{              
          // If is a Desktop application
           if (AutomationFactory.IsAvailable){
                 var shell = AutomationFactory.CreateObject("Shell.Application");
                 shell.ShellExecute(uri.ToString());
            }
          // If is a Web application
             else if (!System.Windows.Application.Current.IsRunningOutOfBrowser){
                  HtmlPage.Window.Navigate(uri, "_blank");
            }
             else{
                  throw new InvalidOperationException();
             }

   }
   catch (Exception ex){
      //handle the exception however you want  
   }
});


In case you want to open in the same page, use:

                  HtmlPage.Window.Navigate(uri, "_self");

BI - SSIS Expressions

Name a File dinamically

In connections (as a File) you can change the Output File name using expressions. 

With the following expression you can named a file according with the actual date.

“C:\\Projects\\SSISPersonalTrainer\\EmployeeCount_”+
RIGHT( “0”+(DT_WSTR, 2) Month(GETDATE() ), 2 ) +
RIGHT( “0”+(DT_WSTR, 2) Day(GETDATE() ), 2 ) +
(DT_WSTR, 4) Year(GETDATE() )+”.txt”

BI - Integration Services

Description of the SSIS Toolbox Items

Script Task

Just do a simple task and you can code some actions like evaluate some variables, also you can evaluate two Script task with AND or OR conditional.

File System Task
Can do all the actions to interact with a FTP.

Execute SQL 
You can execute a sql sentence, also you can put the result in a variable or variables.

Execute Process Task
You can execute an external program like a *.bat where you can send it some parameters

Expression Task
Evaluate variables with many functions like Round, Sum, Date functions, etc. You can use it in a For Loop Container.

Send Mail Task
As the name say, you send an email with all the attributes you can imagen.

Data Flow
The Data Flow Task is used to transfer data from a source to a destination and can transform the data as needed.

Data Conversion
Transfor the data type, usefull in case of non-unicode to unicode

Devived Column
 create or replace a column in the data stream

Aggregate
Rolling up data, with these you can Groups by the information. This element is the one who use the most memoty in the process.

Sort
Sort data based on any column in the Data Flow path

Look up
Perform the equivalent of an inner and outer hash join. The only difference is that the operations occur outside the realm of the database engine.

Row Count
Count the row and store the value in a variable to be used later, for example in an expression that evaluate if is bigger than cero then continue.

Union All
Combines multiple inputs in the Data Flow into a single output rowset. It is very similar to the Merge Transform, but does not require the input data to be sorted.

Script Component
Can manipulate the data:

• Transform—Generally, the focus of your Data Flow will be on using the script as a transform. In this role, you can perform advanced cleansing with the out-of-the-box components.
• Source—When the script is used as a source, you can apply advanced business rules to your data as it’s being pulled out of the source system. (This happens sometimes with COBOL files.)
• Destination—When the script is used as a destination, you can use the script to write out to a non-OLE DB destination, like XML or SharePoint.

Conditional Split
The Conditional Split Transform uses the SSIS expression language to determine how the data pipeline should be split. For this example, all you need to know is that the Conditional Split Transform is checking to see if customers have more than five kids so they can receive the extra coupon.

OLE DB Commmand
Is used to run a SQL statement for each row in the Data Flow

Fuzzy Lookup
Gives other alternatives to dealing with dirty data while reducing your number of unmatched rows. The Fuzzy Lookup Transform matches input records with data that has already been cleansed in a reference table. It returns the match and can also indicate the quality of the match. This way you know the likelihood of the match being correct.
NOTE A best practice tip is to use the Fuzzy Lookup Transform only after trying a regular lookup on the field first. The Fuzzy Lookup Transform is a very expensive operation that builds specialized indexes of the input stream and the reference data for comparison purposes. Therefore, it is recommended to first use a regular Lookup Transform and then divert only those rows not matching to the Fuzzy Lookup Transform.

Fuzzy Grouping 
To examine the contents of suspect fields and provide groupings of similar words. You can use the matching information provided by this transform to clean up the table and eliminate redundancy.

BI - Include a packake as a Job

After did your package, insert as a job in SQL Server

• Select in Visual Studio -> Save As

• Go to the file system and copy the file to C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL

*You have to put the file in this path if not SQL Server will not be able to read the file.

• Go to SQL Server Manager 
• Expand SQL Server Agent

• Right click in Jobs
• Select New Job
• Type a Name

• Click in Steps
• Click in New
• Type a Name
• In Package select the file *.dtsx

• In the Tab "Execution options", select "Use 32 bit runtime"
• Click OK

• In Jobs do right click and select Start Job at Step
• Ready!!

BI - Load an Excel File to DB

The purpose is to upload an Excel file into a Database without duplicate information.

• Install SQL Server Data Tools for Visual Studio 2012
• Create a New Project 
• Type: Integration Services Project
• Create New Package
• Menu: Project -> New SSIS Package 
• Inside the Package
• In the tab Control Flow , drag a "Data Flow Task"
• Inside the Data Flow tab, drag the following Items, as the image
• Excel Source
• OLE DB Source
• Sort elements (x2)
• Mege Join
• Conditional Split
• Data Conversion
• OLE DB Destination

Excel Source

• Double click in the element
• In Connection Manager, Select "New" and then choose the file path

• Select the Excel tab 

• In Columns, select all the items

OLE DB Source

• Double click in the element
• In Connection Manager, Select "New" and then choose the Server Name and Table
• Click Ok

• Select the table where you are comparing the information (is the same table where you are going to store the new data)

• In Columns, select all the items
• Click OK

SORT

• Double click in the left Sort
• Select the key that you want to use to compare

• Do the same for the right Sort, selecting the same key to compare

MERGE JOIN

• Double click in the Merge Join
• Select in Join Type: Left outer join
• Select all the left colums and only the key in the right table
• Change the Output Alias for the right key
• Click OK

CONDITIONAL SPLIT

• Double click in the Conditional Split
• Open the Colums Menu at the left and drag and drop the right key to the down part
• Change the conditional to ISNULL([NewCodigo])
• Connect to DATA Conversion with the line "New Rows"

DATA CONVERSION

• Double click in the element
• Change the Data Type as in the image, especially in "Codigo"

OLE DB DESTINATION

• Select the connection manager
• Select the table to store the new rows
• Save the project

TEST

• Execute the Package 
• Do a select query to review the new data added