Javascript to Expand Calendar Events

This javascript snippet will expand all SharePoint 2010 calendar events.

Create a CEWP on the page and add insert the code below.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

<script type="text/javascript">

function expandCal() {

setInterval(expandCalCheck, 900);

}

function expandCalCheck() {

var a=$("a.ms-cal-nav", $("div.ms-acal-rootdiv:last>div")).get(0);

if (a) {

for (var r=0; r<6; r++) {

var a=$("a.ms-cal-nav", $("div.ms-acal-rootdiv:last>div").get(r)).get(0);

if (a)

if (a.innerText.indexOf("more") >= 0)

a.click();

}

}

}

ExecuteOrDelayUntilScriptLoaded(expandCal, "sp.ui.applicationpages.calendar.js");

</script>

TradeStation Group, Inc. is the parent company of five separate but affiliated operating subsidiaries, TradeStation Securities, Inc. (Member NYSE, FINRA, NFA and SIPC), IBFX, Inc. (Member NFA), IBFX Australia Pty Ltd, which is authorized and regulated by the Australian Securities and Investments Commission, TradeStation Technologies, Inc., a trading software and subscription company, and TradeStation Europe Limited, a United Kingdom, FSA-authorized introducing brokerage firm. None of these companies provides trading or investment advice, recommendations or endorsements of any kind. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Forex products and services are offered by the TradeStation Forex divisions of IBFX, Inc. (Member NFA) and IBFX Australia Pty Ltd (ASIC registered).

How to Create a Dictionary in SharePoint

Overview
This tutorial will help you build a dictionary in SharePoint using an auto suggest feature that calls up terms from the centralized term store.
No server side web part development; everything in this article to create the dictionary was developed using JavaScript, SPServices, CSS and a custom list. Using the SharePoint Client Object Model would be a better choice for this type of project and has extensive capabilities of making your site more dynamic and functional.

Using SPServies
SPServices is a JQuery library abstract SharePoint web services and make them easy to use it. If you have not worked with SPServices,
check out the website for more detailed information about its functionality. It works entirely client side and requires no server install.

Download the jquery files from the site and upload them to your SharePoint server. For this project, I placed the files under the Site Assets directory so they could be accessed anywhere in the Site Collection.

Getting Started
Starting with HTML and some CSS, I created a web page similar to the image below. If a user clicks on a letter, all the terms and definitions starting with that letter will appear below inside a div element. Note that there are a few linked Javscript and external CSS files including the main jquery library, SPServices.js, and the autosuggest jquery files. Dictionary.js and dictionary_autosuggest.js are two Javascript files that contain the client side functionality of the search features. More details are explained later in this article.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<title>TS Dictionary</title>

<script type="text/javascript" src="/style%20library/services-plugin/js/jquery.js"></script>

<!-- autocomplete files -->

<link href="/Site Assets/jquery-ui-1.10.0.custom.css" rel="stylesheet"/>

<script src="/Site Assets/jquery-ui-1.10.3.custom.min.js"></script>

<script src="/Site Assets/jquery.SPServices-0.7.2.min.js" language="javascript" type="text/javascript"></script>

<script type="text/javascript" src="/site Assets/dictionary.js"></script>

<script type="text/javascript" src="/site Assets/dictionary_autoSuggest.js"></script>

<style type="text/css">

#author_alphabetic {
text-align:center;
}
#author_alphabetic ul {
}

#author_alphabetic li {
float:left;
margin:2px 2px 0 0;
}

#author_alphabetic a {
background-color:#3d7fac;
position:relative;
width:40px;
height:40px;
border:1px solid rgb(61, 127, 172);
text-decoration:none;
font-size:22px;
font-weight:bold;
font-family: "Verdana";
float:left;
display:inline;
color:white;
line-height:2.0;
}

#author_alphabetic a span {

/* background:url(../image/alpha_br.png) no-repeat right bottom; */
width:75px;
height:75px;
display:block;
position:absolute;
top:-2px;
left:-8px;
}

#author_alphabetic a:hover {
background:rgb(61, 127, 172);
z-index:100;
}

#author_alphabetic a:hover span.first {
display:block;
font-size:22px;
line-height:2.0;
font-weight:bold;
color:white;
cursor:pointer;
width:40px;
height:40px;
top:-1px;
left:-1px;
background:rgb(119, 165, 197);
border:1px solid rgb(255, 255, 255);
}

#author_alphabetic a:hover span span {
display:block;
position:relative;
left:1px;
top:-4px;
width:41px;
height:41px;
}

.ui-autocomplete {
max-height: 400px;
overflow-y: auto;
/* prevent horizontal scrollbar */
overflow-x: hidden;
}

/* IE 6 doesn't support max-height * we use height instead, but this forces the menu to always be this tall */*
html .ui-autocomplete {
height: 100px;
}
.myInput {
position:relative;
left:40px;
width:300px;
border:0px;
max-width:240px;
height:42px ;
background: url("/style%20library/enetThemeimages/input6.png") 0 0 ;
background-repeat: no-repeat;
line-height:32px;
padding-left:5px;
font-family:"Arial",Georgia,Serif;
color: #8a8484;
font-size:14px;
size:250px;
padding-right: 5px;
top:-20px;
}

.myButton{
position:relative;
left:45px;
background: no-repeat url("/style%20library/enetThemeimages/searchbutton.png") 0 0 ;
width:97px;
height:42px;
border:0px;
cursor:pointer;
top:-20px;
}
</style>

<body>
<h3 style="padding-left:105px;padding-top:-30px; position:relative;top:30px; width:470px; color:#787878">Click search or use the enter key to call up a dictionary term. </h3>
<div style="position:relative; top:-20px; left:4px; background-color:transparent; width:100px; height:80px">
<img src="/style%20library/enetThemeImages/glass.png" />
<div style="background-color:transparent; width:500px; height:100px;position:relative; left:110px;">
<table border=0><tr><td>
<input type="text" id="txtSearch" class="myInput" value="Search the dictionary..." onClick="clearMe()" onkeypress="searchKeyPress(event);" />
</td><td>
<input type="button" id="btnSearch" value="" onclick="getTerm();" class='myButton' /> </td>
</table>
</div>
</div>
<div id="author_alphabetic" style="position:relative; top:10px; left:-14px">
<ul>
<li><a href="#" onclick="getVals('A')" title="A"><span class="first"><span>A</span></span></a></li>
<li><a href="#" onclick="getVals('B')" title="B"><span class="first"><span>B</span></span></a></li>
<li><a href="#" onclick="getVals('C')" title="C"><span class="first"><span>C</span></span></a></li>
<li><a href="#" onclick="getVals('D')" title="D"><span class="first"><span>D</span></span></a></li>
<li><a href="#" onclick="getVals('E')" title="E"><span class="first"><span>E</span></span></a></li>
<li><a href="#" onclick="getVals('F')" title="F"><span class="first"><span>F</span></span></a></li>
<li><a href="#" onclick="getVals('G')" title="G"><span class="first"><span>G</span></span></a></li>
<li><a href="#" onclick="getVals('H')" title="H"><span class="first"><span>H</span></span></a></li>
<li><a href="#" onclick="getVals('I')" title="I"><span class="first"><span>I</span></span></a></li>
<li><a href="#" onclick="getVals('J')" title="J"><span class="first"><span>J</span></span></a></li>
<li><a href="#" onclick="getVals('K')" title="K"><span class="first"><span>K</span></span></a></li>
<li><a href="#" onclick="getVals('L')" title="L"><span class="first"><span>L</span></span></a></li>
<li><a href="#" onclick="getVals('M')" title="M"><span class="first"><span>M</span></span></a></li>
<br><br><br><br><br>
<li><a href="#" onclick="getVals('N')" title="N"><span class="first"><span>N</span></span></a></li>
<li><a href="#" onclick="getVals('O')" title="O"><span class="first"><span>O</span></span></a></li>
<li><a href="#" onclick="getVals('P')" title="P"><span class="first"><span>P</span></span></a></li>
<li><a href="#" onclick="getVals('Q')" title="Q"><span class="first"><span>Q</span></span></a></li>
<li><a href="#" onclick="getVals('R')" title="R"><span class="first"><span>R</span></span></a></li>
<li><a href="#" onclick="getVals('S')" title="S"><span class="first"><span>S</span></span></a></li>
<li><a href="#" onclick="getVals('T')" title="T"><span class="first"><span>T</span></span></a></li>
<li><a href="#" onclick="getVals('U')" title="U"><span class="first"><span>U</span></span></a></li>
<li><a href="#" onclick="getVals('V')" title="V"><span class="first"><span>V</span></span></a></li>
<li><a href="#" onclick="getVals('W')" title="W"><span class="first"><span>W</span></span></a></li>
<li><a href="#" onclick="getVals('X')" title="X"><span class="first"><span>X</span></span></a></li>
<li><a href="#" onclick="getVals('Y')" title="Y"><span class="first"><span>Y</span></span></a></li>
<li><a href="#" onclick="getVals('Z')" title="Z"><span class="first"><span>Z</span></span></a></li>
</ul></div>
<span style=" line-height:32px"> <a href="#" onclick="getAllVals()" title="Show All" align="absmiddle">Show All</a> </span>
<br /><br><br><br><br><br>
<div id="MainDiv" style="background-color:transparent; padding-left:40px; ">
<div id="ShowMeTerm" style="background-color:transparent; font-size:12px; color:blue; width:700px" valign="top"></div
<div id="ShowMeDef" ></div></div></body></html>

Create Dictionary List

After the htm file was uploaded to the WFE, I created a custom list named ‘Dictionary’ and added a column called ‘Definition’ with a type of multilines of text. I used the Title column as the term to define. Added a few list items so content could be available for testing.

Create Term-Set

On the top level site of the site collection, go to Site Actions > Site Settings and under Site Administration click on > Term store management. On the left pane in the Taxonomy term store create a new term-set called ‘Dictionary.’ Give it a description and allow the submission policy be checked as Open. Add the same terms as you did in the custom list. Save changes.

Go back to the Dictionary list settings to add a new metadata column called ‘MetaData.’ In the term-set settings tree-view, select the Dictionary term-set to link the metadata to the term-set.

Once this is completed, a new field in the form called ‘MetaData’ will be added. Create a new .aspx page on your site where you created the Dictionary custom list. Add the Dictionary custom list to the page and publish. When you click ‘Add new item’, you can manually add new terms and definitions to the list. At this point, you have a SharePoint page that displays an input field a search button and A-Z boxes. Notice a div entitled ‘ShowMeTerm. ’ This is where the definitions will be displayed dynamically on the page.

Binding the AutoSuggest Feature to the input field

In the Javscript below, it starts off with variable declarations. As the function getKeywords gets initiated, the script is calling SharePoint client object model and begins to load term-set from the metadata columns “TSDictionary” that you created earlier. If the term-set is correctly found, then the function OnLoadSuccess is processed followed by two more functions; GetTermsRecursive and getVals(‘A’).

GetTermsRecursive calls the SPServices Jquery script which then queries the term-set. GetData function gathers all the data in the term-set into an array (“mySortedArry”) and then becomes binded to the input field “txtSearch.”

mySortedArry = myListTerms.split(",");
var myListTerms2 = String(mySortedArry);
$("#txtSearch").autocomplete({ source: myListTerms2.split(",")

The getVals() function is initiated when a user clicks on any of the A-Z letters. This function uses a CAML to query the Dictionary custom list and select all the terms and definitions that start with the letter in the variable letter. For example, getVals(‘G’) will display all the terms beginning with G.

<View><Query><Where><BeginsWith><FieldRef Name=’Title’/>’ + ‘<Value Type=’Text’>’ + letter + ‘</Value></BeginsWith></Where><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy></Query></View>

The function onQuerySucceeded() gets called to display the results of the CAML query inside the “ShowMeTerm” div by using innerHTML.

In a similar manner, the Show All link will call up the getAllVals() function and by using another CAML query, will

display every result returned from the Dictionary list.

Dictionary_autosuggest.js



JSRequest.EnsureSetup();

    var myGlobalSearch ;
    var clientContext;
    var oList;
    var camlQuery;
    var listItemInfo = '';
    var listItemInfo2 = '';
    var listItemEnumerator;
    var termPresent;
    var srchTerm;
    var myListTerms= '';
    var TaxTerms = {
        termId: "",
        termName: "",
        child: null
    }

 

    //---------init  begin taxonomy ----------------->

    var TaxConfiguration = {
        SspId: "",
        GroupId: "",
        TermSetId: "",
        Configuration: "",
        ParseConfiguration: function () {

 

            xmlDoc = $.parseXML(this.Configuration);
            xml = $(xmlDoc);
            var properties = xml.find("Property");

            for (i = 0; i < properties.length; i++) {
            propertyName = properties[i].firstChild.textContent == undefined ?
            properties[i].firstChild.text : properties[i].firstChild.textContent;
            propertyValue = properties[i].lastChild.textContent == undefined ?
            properties[i].lastChild.text : properties[i].lastChild.textContent;
               if (propertyName == propertyValue) {
                    propertyValue = "";
                }

 

                switch (propertyName) {
                    case "SspId":
                        this.SspId = propertyValue;
                        break;
                    case "GroupId":
                        this.GroupId = propertyValue;
                        break;
                    case "TermSetId":
                        this.TermSetId = propertyValue;
                        break;
                }
            }
        }
    }

 

    // goes to this function

    function GetTermsRecursive(sspId, termSetId, myListTerms) {   
        var terms = new Array();
        var returnValue;
        $().SPServices({
            operation: "GetChildTermsInTermSet",
            sspId: sspId,
            termSetId: termSetId,
            lcid: 1033,
            completefunc: GetData
        });
    }


    function GetChildTermsRecursive(sspId, termSetId, roottermId) {
        var terms = new Array();
        $().SPServices({
            operation: "GetChildTermsInTerm",
            sspId: sspId,
            termId: roottermId,
            termSetId: termSetId,
            lcid: 1033,
            completefunc: GetData
        });
       return myListTerms;

    }

 
    function GetData(xData, Status) {
        if (Status == "success") {
            terms = new Array();
            xmlData = xData;
            // Fix for different XML parsing in IE and Chrome
            termsContent = $.parseXML(xmlData.responseText).firstChild.textContent == undefined ?
            $.parseXML(xmlData.responseText).text :
            $.parseXML(xmlData.responseText).firstChild.textContent;
            termsXML = $.parseXML(termsContent);
            $termsXML = $(termsXML);
            childTerms = $termsXML.find("T");
            parentTermId = null;
            filterOutput = "<ul>";
            for (i = 0; i < childTerms.length; i++) {
                termName = $(childTerms[i]).find("TL");

                hasChildTermsXml = $(childTerms[i]).find("TM");
                // request if child terms are avaliable

                hasChildTerms = $(hasChildTermsXml).attr("a69");
                var tsTerm = new Object();
                // Requesting actual term id
                tsTerm.termId = $(childTerms[i]).attr("a9");
                // Requesting term name

                tsTerm.termName = termName.attr("a32");

                // Setting Parent Term ID

                parentTermId = $(hasChildTermsXml).attr("a25");
                filterOutput += "<li id='" + tsTerm.termId + "'>" + tsTerm.termName;
                // try to put termName in doug comma delimited array - this works!!
                myListTerms+= ',' + tsTerm.termName;
                 // for debugging puroposes
                // If child terms are avaliable query child terms
                if (hasChildTerms != undefined && hasChildTerms == "true") {
                    // Request child Terms
                    tsTerm.child = GetChildTermsRecursive(taxconfig.SspId, taxconfig.TermSetId, tsTerm.termId);
                    tsTerm.hasChildTerms = true;
                } else {
                    tsTerm.child = null;
                    tsTerm.hasChildTerms = false;
                }

                filterOutput += "</li>";
                terms[i] = tsTerm;
            }
            filterOutput += "</ul>";
            // If parent element is specified query for parent element
            if (parentTermId != undefined || parentTermId != null) {
                $("#" + parentTermId).append(filterOutput);
            } else {
                currentFilter = $("#filter").html();
                $("#filter").html(currentFilter + filterOutput);

            }
            //  sort comma delimited string
             mySortedArry = myListTerms.split(",");
             var myListTerms2 = String(mySortedArry);                                                    
              $("#txtSearch").autocomplete({ source: myListTerms2.split(",")

            });
       
            //return dictionaryTerms;

        }

    }


    var fieldCollection;
    var termField = null;
    var termSetTerms = Array();
    var taxconfig = TaxConfiguration;
 

    function OnLoadFailed(sender, args) {
        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());

    }
 
    //------ end taxonomy code------------------------------//
    $(document).ready(function () {
                ExecuteOrDelayUntilScriptLoaded(getKeywords, "sp.js");
                clearFormData() ;    
    });


    function getKeywords() {
        var clientContext = SP.ClientContext.get_current();
        if (clientContext != undefined && clientContext != null) {
            var webSite = clientContext.get_site().get_rootWeb();
            // Select 'Action' Field
            fieldCollection = webSite.get_fields();
            this.termField = fieldCollection.getByInternalNameOrTitle("TSDictionary");
            // Load Context
            clientContext.load(this.fieldCollection);
            clientContext.load(this.termField);
            clientContext.executeQueryAsync(Function.createDelegate(this, this.OnLoadSuccess), Function.createDelegate(this, this.OnLoadFailed)
                );
        }

    }

    function OnLoadSuccess(myList) {
        var fieldInfo = '';
        var fieldschema = termField.get_schemaXml();
        taxconfig.Configuration = fieldschema;
        taxconfig.ParseConfiguration();
        try {
            var taxsspId = taxconfig.SspId;
            var termSetId = taxconfig.TermSetId;
            var dictionaryTerms = '';
            GetTermsRecursive(taxconfig.SspId, taxconfig.TermSetId, dictionaryTerms);
             getVals('A');
        }   catch (e) {
            alert(e);
        }
    }
       function onCreateSucceeded() {
        // just an empty function
    }
//-------------------------------------------------------------------------------------

function searchKeyPress(e)
    {
        // look for window.event in case event isn't passed in
        if (typeof e == 'undefined' && window.event) { e = window.event; }
        if (e.keyCode == 13)
        {

            var mySearchField = document.getElementById('txtSearch');
             myGlobalSearch  = mySearchField.value;
            document.getElementById('btnSearch').click();
            getVals(mySearchField.value);
        }
    }

  
function getTerm() {
   var mySearchField = document.getElementById('txtSearch');
   myGlobalSearch = mySearchField.value;
   getVals(mySearchField.value);

}


function  clearMe() {
   var myfield2 = document.getElementById('txtSearch');
   myfield2.autocomplete = "off";
   myfield2.value = "";
}

 

Dictionary.js

function onFilterQueryFailed(sender, args) { alert ("Unable to retrieve terms. Please contact your administrator.") }

function getVals(letter) {
       var fieldDisplayName = "Title";
        var clientContext = new SP.ClientContext.get_current();     
       var spWebobj = clientContext.get_web();
        var spListobj = spWebobj.get_lists().getByTitle('Dictionary');
        var spCamlQuery = new SP.CamlQuery();
        spCamlQuery.set_viewXml ('<View><Query><Where><BeginsWith><FieldRef Name=\'Title\'/>' +   '<Value Type=\'Text\'>' + letter + '</Value></BeginsWith></Where><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy></Query></View> ' );      
       this.collListItem = spListobj.getItems(spCamlQuery);
        clientContext.load(collListItem);
        clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onFilterQueryFailed));               

      }   


function getAllVals() {
var fieldDisplayName = "Title";
        var clientContext = new SP.ClientContext.get_current();     
        var spWebobj = clientContext.get_web();
        var spListobj = spWebobj.get_lists().getByTitle('Dictionary');
        var spCamlQuery = new SP.CamlQuery();
        spCamlQuery.set_viewXml ('<View><Query><Where><IsNotNull><FieldRef Name="Title" /></IsNotNull></Where><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy></Query></View> ');

        this.collListItem = spListobj.getItems(spCamlQuery);
        clientContext.load(collListItem);
        clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onFilterQueryFailed));

}

function onQuerySucceeded(sender, args) {
    var listItemInfo = '';
    var listItemEnumerator = collListItem.getEnumerator();
    document.getElementById("ShowMeTerm").innerHTML ='';
    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        listItemInfo += '\nID: ' + oListItem.get_id() +
            '\nTitle: ' + oListItem.get_item('Title') +
            '\nBody: ' + oListItem.get_item('Definition');
          myterm =   oListItem.get_item('Title');
     document.getElementById("ShowMeTerm").innerHTML += oListItem.get_item('Title') + ":<br />" +  "<div style=color:black; font-weight:normal;>" + oListItem.get_item('Definition') +  "</div> <br /><br />" ;
       }


     if (listItemInfo.toString()== "") {
        document.getElementById("ShowMeTerm").innerHTML = "No matches found."
     }
}

Understanding SQL Join Statements

Overview

This article is to explain how JOIN statements are written using SQL in MicroSoft Studio Manager application. When comparing data with more than one table, you use a JOIN. An SQL JOIN clause combines records from two or more tables in a database. A JOIN clause combines fields (columns) from each table that shares a common value. An SQL programmer writes a JOIN statement to display the records for joining. The combined data can then be used a record set or stored in a temporary table.

There are four types of JOIN: INNER, OUTER, LEFT, and RIGHT. Lets work with using these statements using the two tables below:

Model table Car table

In the above tables the CarID column of the Car table (which can be designated as Car.CarID) is the primary key, while CarID of the Model table is a foreign key, or model.CarID. Notice that the BMW has no value assigned (NULL) and that Lexus does not have any models assigned to it. Using SSMS, The SQL statement below will compile and create the following tables in the database:



CREATE TABLE Car

(

CarID INT,

CarName VARCHAR(20)

);

CREATE TABLE model

(

modelName VARCHAR(20),

CarID INT

);

INSERT INTO car(CarID, CarName) VALUES(1,'Acura');

INSERT INTO car(CarID, CarName) VALUES(2, 'Honda');

INSERT INTO car(CarID, CarName) VALUES(3, 'Ford');

INSERT INTO car(CarID, CarName) VALUES(4, 'Lexus');

INSERT INTO model (modelName, CarID) VALUES('TL', 1)

INSERT INTO model (modelName, CarID) VALUES('Civic', 2)

INSERT INTO model (modelName, CarID) VALUES('Accord',2)

INSERT INTO model (modelName, CarID) VALUES('Escape',3)

INSERT INTO model (modelName, CarID) VALUES('Explorer',3)

INSERT INTO model (modelName, carID) VALUES('335i', NULL);

CROSS JOIN

CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. Multiply the number of rows for each table (6 x 4) and that is the number of records (24) displayed in the result.


SELECT * FROM model CROSS JOIN car

Or

SELECT * FROM model, car

INNER JOIN

Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. In other words, The INNER JOIN keyword returns rows when there is at least one match in both tables.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation". The "explicit join notation" uses the JOIN keyword along with the ON keyword to specify the predicates for the join, as in the following example:


SELECT *
FROM model INNER JOIN car
ON model.CarID= car.CarID

Implicit join notation uses the WHERE clause as a filter predicate:


SELECT *
FROM model, car
WHERE model.CarID = car.CarID;

The result turns out a table like this:

Note that the resulting table has no record of 335i because the CarID has a null value and no reference to the car table. There is also no record of Lexus because there is no CarID associated with it in the model table.

OUTER JOIN

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table’s rows are retained (left, right, or both).

(In this case left and right refer to the two sides of the JOIN keyword.)

LEFT OUTER JOIN

The LEFT JOIN keyword returns all rows from the left table (model_CarID), even if there are no matches in the right table (car_CarID).


SELECT *
FROM model LEFT OUTER JOIN car
ON model.carID = car.carID

RIGHT OUTER JOIN

The RIGHT JOIN keyword returns all the rows from the right table (car_carID), even if there are no matches in the left table (model_CarID)

SELECT *
FROM model RIGHT OUTER JOIN car
ON model.CarID = Car.CarID

FULL OUTER JOIN

FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching

SELECT *
FROM model FULL OUTER JOIN car
ON model.carID = car.carID

EQUI JOIN

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.

There are two types of EQUI JOINS:

1) SQL INNER JOIN

2) SQL OUTER JOIN

An SQL non EQUI JOIN is a JOIN condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=.

What is the difference between Equi Join and Inner Join in SQL?

An EQUI JOIN is a join with a join condition that only contains an equality operator. An EQUI JOIN returns only the rows that have equivalent values for the specified columns. An inner join is a join with a join condition that may contain both equality or non-equality.

Understanding Namespaces

Understanding Namespaces using C#

Namespaces are heavily used in object oriented programming languages. All .NET Framework classes are organized in namespaces to help developers organize their code more clearly and to avoid chaos. Furthermore, custom namespaces are extensively used by programmers, both to organize their work and to avoid naming collisions.

Implementing namespaces in your own code is a good habit because it is likely to save you from problems later when you want to reuse some of your code.

When referencing a class, you should specify either its fully qualified name, which means namespace followed by the class name.


System.Console.WriteLine("Hello World!");
int i = Convert.ToInt32("123");

In the above examples, System is a .NET namespace, and Console and Convert are classes defined within System.

Another way to program this is the USING directive.


Using System;
Using System.Text;


Each USING directive lists the namespaces that the application will be using frequently, and saves you from specifying a fully qualified name every time that a method is used.
So instead of :


System.Console.WriteLine("Hello World");

You can replace it with:


USING System;
Console.WriteLine("Hello World");

SCOPE
Namespaces are also used to declare a scope. By creating scopes in your project, you will better organize your code and you can create globally unique types. Namespaces can also be nested inside another namespace.


namespace myUniquenamespace
{
     class mySampleClass
      { 
          public void SampleMethod()
           {
            System.Console.WriteLine ("Hello World");
           }
       }

// Create a nested namespace, and define another class.

namespace myNestedNamespace
   {
    class mySampleClass2
        {
          public void SampleMethod()
             {
              System.Console.WriteLine ("hello world");
             }
        }
    }

}

FULLY QUALIFIED NAMES

namespace n1 //n1
    {
       class class1
         {
           class class2 // n1.class1.class2
             {
             }
         }
 namespace n2 // n1.n2
         {
         class class2 // n1.n2.class2
       {
       }
    }
}


The fully qualified namespace n1 is a member of the global namespace.

The namespace n2 is a member of n1. The fully qualified name is n1.n2

The class class1 is a member of n1 and the fully qualified name is n1.class1.

The class name class2 is used twice in this code. However, the fully qualified names are unique. The first instance of class1 is declared inside class1 therefore, its fully qualified name is: n1.class1.class2

The second instance of class2 is declared inside a namespace n2 therefore, its fully qualified name is n1.n2.class2

SQL Glossary of Common SQL Commands

SQL Glossary of Common SQL Commands

*

The asterisk character returns all the columns of a particular table.

ALTER DATABASE

ALTER DATABASE database_name;

The ALTER DATABASE statement changes the size or settings of a database.

Its syntax varies widely among different database systems.

ALTER USER

ALTER USER user

The ALTER USER statement changes a user’s system settings such as a

password.

BEGIN TRANSACTION

1> BEGIN TRANSACTION transaction_name

2> transaction type

3> if exists

4> begin

The BEGIN TRANSACTION statement signifies the beginning of a user transaction.

A transaction ends when it is either committed (see COMMIT TRANSACTION)

or canceled (see ROLLBACK TRANSACTION). A transaction is a logical

unit of work.

CLOSE CURSOR

close cursor_name

The CLOSE cursor_name statement closes the cursor and clears it of data. To

completely remove the cursor, use the DEALLOCATE CURSOR statement.

COMMIT TRANSACTION

COMMIT;

The COMMIT TRANSACTION statement saves all work since the beginning of the transaction

(since the BEGIN TRANSACTION statement was executed).

CREATE DATABASE

CREATE DATABASE database_name;

The CREATE DATABASE statement creates a new database. Many different options can be

supplied, such as the device on which to create the database and the size of the initial

database.

CREATE INDEX

CREATE INDEX index_name

ON table_name(column_name1, [column_name2], …);

An index can order the contents of a table based on the contents of the indexed field(s).

CREATE PROCEDURE

CREATCCREATE PROCEDURE procedure_name

[[(]@parameter_name

datatype [(length)
| (precision [, scale])

[= default][output]

[, @parameter_name

datatype [(length)
| (precision [, scale])

[= default][output]]…[)]]

[with recompile]

AS SQL_statements

The CREATE PROCEDURE statement creates a new stored procedure in the database. This

stored procedure can consist of SQL statements and can then be executed using the

EXECUTE command. Stored procedures support the passing of input and output parameters

and can return an integer value for status checking.

CREATE TABLE

CREATE TABLE table_name

( field1 datatype [ NOT NULL ],

field2 datatype [ NOT NULL ],

field3 datatype [ NOT NULL ]…)

The CREATE TABLE statement creates a new table within a database. Each optional field is

provided with a name and data type for creation within that table.

PDF:2 APPENDIX E: Glossary of Common SQL Commands

CREATE TRIGGER

CREATE TRIGGER trigger_name

ON table_name

FOR {insert, update, delete}

AS SQL_Statements

The CREATE TRIGGER statement creates a trigger object in the database that will execute

its SQL statements when its corresponding table is modified through an INSERT, UPDATE,

or DELETE. Triggers can also call stored procedures to execute complex tasks.

CREATE USER

CREATE USER user

The CREATE USER statement creates a new user account, complete with user ID and

password.

CREATE VIEW

CREATE VIEW <view_name> [(column1,
column2…)] AS

SELECT <table_name column_names>

FROM <table_name>

A view is often described as a virtual table. Views are created by using the CREATE VIEW

statement. After a view is created, it can be queried, and data within the view can be

modified.

DEALLOCATE CURSOR

DEALLOCATE CURSOR cursor_name

The DEALLOCATE CURSOR statement completely removes the cursor from memory and

frees the name for use by another cursor. You should always close the cursor with the

CLOSE CURSOR statement before deallocating it.

DECLARE CURSOR

DECLARE cursor_name CURSOR

FOR select_statement

The DECLARE CURSOR statement creates a new cursor from the SELECT statement query.

The FETCH statement scrolls the cursor through the data until the variables have been

loaded. Then the cursor scrolls to the next record.

DROP DATABASE

DROP DATABASE database_name;

Glossary of Common SQL Commands PDF:3

E

The DROP DATABASE statement completely deletes a database, including all data and the

database’s physical structure on disk.

DROP INDEX

DROP INDEX index_name;

The DROP INDEX statement removes an index from a table.

DROP PROCEDURE

DROP PROCEDURE procedure_name

The DROP PROCEDURE statement drops a stored procedure from the database; its function

is similar to the DROP TABLE and DROP INDEX statements.

DROP TABLE

DROP TABLE table_name;

The DROP TABLE statement drops a table from a database.

DROP TRIGGER

DROP TRIGGER trigger_name

The DROP TRIGGER statement removes a trigger from a database.

DROP VIEW

DROP VIEW view_name;

The DROP VIEW statement removes a view from a database.

EXECUTE

EXECUTE [@return_status = ]

procedure_name

[[@parameter_name
=] value |

[@parameter_name
=] @variable [output]…]]

The EXECUTE command runs a stored procedure and its associated SQL statements.

Parameters can be passed to the stored procedure, and data can be returned in these

parameters if the OUTPUT keyword is used.

FETCH

FETCH cursor_name [into fetch_target_list]

PDF:4 APPENDIX E: Glossary of Common SQL Commands

The FETCH command loads the contents of the cursor’s data into the provided program

variables. After the variables have been loaded, the cursor scrolls to the next record.

FROM

FROM <tableref> [, <tableref> …]

The FROM statement specifies which tables are used and/or joined.

GRANT

GRANT role TO user

or

GRANT system_privilege TO {user_name | role | PUBLIC}

The GRANT command grants a privilege or role to a user who has been created using the

CREATE USER command.

GROUP BY

GROUP BY <col> [, <col> …]

The GROUP BY statement groups all the rows with the same column value.

HAVING

HAVING <search_cond>

The HAVING statement is valid only with GROUP BY and limits the selection of groups to

those that satisfy the search condition.

INTERSECT

INTERSECT

The INTERSECT statement returns all the common elements of two SELECT statements.

ORDER BY

ORDER BY <order_list>

The ORDER BY statement orders the returned values by the specified column(s).

REVOKE

REVOKE role FROM user;

Glossary of Common SQL Commands PDF:5

E

or

REVOKE {object_priv | ALL [PRIVILEGES]}

[, {object_priv
|
ALL [PRIVILEGES]} ] …

ON [schema.]object

FROM {user | role | PUBLIC} [, {user
|
role |
PUBLIC}] …

The REVOKE command removes a database privilege from a user, whether it be a system

privilege or a role.

ROLLBACK TRANSACTION

ROLLBACK TRANSACTIONROLLBAKC;

The ROLLBACK TRANSACTION statement effectively cancels all work done within a transaction

(since the BEGIN TRANSACTION statement was executed).

SELECT

SELECT [DISTINCT
| ALL]

The SELECT statement is the beginning of each data retrieval statement. The modifier

DISTINCT specifies unique values and prevents duplicates. ALL is the default and allows

duplicates.

SET TRANSACTION

SET TRANSACTION (READ ONLY | USE ROLLBACK SEGMENT);

The SET TRANSACTION command enables the user to specify when a transaction should

begin. The READ ONLY option locks a set of records until the transaction ends to ensure

that the data is not changed.

UNION

UNION

The UNION statement returns all the elements of two SELECT statements.

WHERE

WHERE <search_cond>

The WHERE statement limits the rows retrieved to those meeting the search condition.

SharePoint Glossary and Definitions

SharePoint 2010 Glossary and Definitions

Active Directory The Active Directory stores information about a variety of objects in the network. Importantly, user accounts, computer accounts, groups, and all related credential information used by windows and SharePoint.
Activities Activities are tracked updates related to a specific user. They are often related to the users social interaction within SharePoint (such as tagging, rating, etc).
Activity Feed A message that provides updates about items of interest based on custom notification settings. This includes updates about changes to documents, the status of colleagues, social tags, and colleague profiles.
Alerts To notify users of changes to existing information or new information add an alert to track new matches to search queries, changes to content in an area (list/library, folder, and document/item), or a new site added to the Site Directory.
Anonymous Authentication/Access An authentication mode in which neither party verifies the identity of the other party.
Anonymous User A user who presents no credentials when identifying himself or herself.
Areas A means to organize the portal for navigational simplification.
Ascending Order A sort order in which text strings are arranged in alphabetical order, numerical values are arranged from smallest to largest, and dates and times are arranged from oldest to newest.
Attachment An external file that is included with an email message or associated with an item in a SharePoint list.
Audience A named group of users that is used for targeting content.
Audiences Groups of users, who meet certain criteria, created for targeted distribution of information. Users are said to be a member of an audience if they meet that audience’s membership criteria. The criteria are associated with properties found in the Active Directory.
Authentication (1) The ability of one entity to determine the identity of another entity. (2) The act of proving an identity to a server while providing key material which binds the identity to subsequent communications. (How the system knows who the user is)
BCS Business Connectivity Services – A new service that empowers users (who have sufficient access rights) to pull in external data use in SharePoint.
BDC Business Data Catalog – For connecting with other lines of business (enterprise level feature)
Collaboration Content Collaboration content is content stored in lists like calendars, task lists, and document libraries.
Content Type A named and uniquely identifiable collection of settings and fields that store metadata for individual items in a SharePoint list. One or more content types can be associated with a list, which restricts the contents to items of those types.
Data Validation The process of testing the accuracy of data; a set of rules that specify the type and range of data that users can enter.
Default View The view of a list that is defined by the owner of the list to appear when users browse to the list without specifying a view.
Descending Order A sort order in which text is arranged in reverse alphabetical order, numerical values are arranged from largest to smallest, and dates and times are arranged from newest to oldest.
Discussions SPS supports two types of discussions: discussion boards and Web discussions. Discussion boards are similar to any newsgroup forum. Web discussions, on the other hand, are a new way to comment on documents and share those comments with others. When a new site is created, a discussion board is automatically created.
Distribution List A collection of users, computers, contacts, or other groups that is used only for e-mail distribution, and addressed as a single recipient.
Document Center A document library template that is preconfigured to store a large quantity of documents.
Document Library A configurable list in which documents and folders can be stored. The document library has special settings above and beyond a folder such as versioning settings, workflow settings, and information policies.
Document Workspace A document repository that enables users to collaborate on one or more documents.
Document Workspace SharePoint Services site that contain a document library, tasks, links, and other information. Document workspaces can be created directly in SPS as a new site, or they may be created ad-hoc from within an Office product. End users can collaborate without having SPS open alongside Office.
External Content Type A reusable collection of metadata that defines a set of data from one or more external data sources, the operations avaialble on that data, and connectivity information related to that data.
External List A list of items of an external content type.
Feature A package that can be activated or deactivated at either the site or site collection level. Each feature varies considerably so you must refer to it’s description to understand the impact of activating or deactivating a specific feature.
Gallery A library that is used to store a collection of site resources, such as Web Parts, list templates, or site templates.
Item An individual entry within a SharePoint list. Each list item can have multiple columns associated with it depending on the list it is contained within and, depending on the content type of the item.
Key Performance Indicator (KPI) A predefined measure that is used to track performance against a strategic goal, objective, plan, initiative, or business process. A visual cue is frequently used to communicate performance against the measure.
Keyword One or more words or phrases that site administrators identified as important. A keyword provides a way to display best bets and definitions on a search results page.
Libraries In order to share files, SharePoint makes use of libraries. The three types of libraries available in SharePoint are document libraries, form libraries and picture libraries.
List Template An XML-based definition of list settings, including fields and views, and optionally list items. List templates are stored in .stp files in the list template gallery.
Lists Along with document libraries, lists form the foundation of content within SPS. A list is a collection of information items displayed in an area or on a site. List types include: Announcements, Links, Contacts, Events, Tasks and Issues.
Lookup Column A column of the Lookup type that allows a user to select items from another list/library. This column can also bring in extra data/fields from the list it is referencing.
Major Version An iteration of a document, or list item that is ready for a larger group to see, or has changed significantly since the previous major version. For an item on a SharePoint site, the minor version is always zero for a major version.
Managed Term A word or a phrase that can be associated with a SharePoint item . Managed terms, are usually predefined, can be created only by users with the appropriate permissions, and are often organized into a hierarchy. Also called “term” where “managed” is clear from the context.
Masterpage The masterpage is the structural content which often surrounds your site. Often it makes up the header and footer of the site and may or may not also contain the left navigation. Typically if you have multiple masterpages these will significantly alter the look of each site they are applied to.
Meeting Workspace A specialized SharePoint Services site. Can be created directly in SPS or in Outlook as a part of a meeting request. Should not be confused with Live Meeting, etc.
Metadata Data about data. This is often seen in the form of column values in a SharePoint library or list, but can take many other forms.
Minor Version For an item on a SharePoint site, the minor version number is never zero and is incremented for each new version of an item, unless a major version is explicitly published. When minor versioning is disabled on a SharePoint site, only major version numbers are incremented, and the minor version is always zero.
Moss Microsoft Office SharePoint Server 2007 – The fully licensed product in 2007 (note there is still enterprise and standard variations)
My Network My network is a page designed to provide a summary and roll up of the activities going on in your network.
My Site A single page portal that contains the user’s personal sites, links, etc. My Site consists of both a public and private view. The private view is intended as a personal workplace for the individual end user. The public view, on the other hand, acts like a business card that can be accessed by other portal users. You can see the different views by clicking either Private or Public under the Select View list.I am leaving this, because many will still refer to My Home, or Personal Sites as MySites for some time.
OOTB Out of the box. Typically in SharePoint this represents the components and features shipped with the product. As in the existing webparts, and templates in the product. There is contention around whether using SharePoint Designer is OOTB as SharePoint Designer (while free) is still another product from SharePoint.
Page Layout A dynamic Web template that is stored as a document. It contains content placeholders that bind to fields of a publishing page. A page layout has an associated content type that determines which publishing pages it can be bound to. The page layout can contain webpart zones, as well as webparts. In SharePoint think of the Masterpage as the wrapper for page layouts.
Permission A rule that is associated with something to regulate which users can gain access to that something and in what manner.
Permission Level A set of permissions that can be granted to users, distribution lists or SharePoint groups on an entity such as a site, list, folder, item, or document.
Personal Site A type of SharePoint site that is used by an individual user for personal productivity. The site appears to the user as My Site.
Privacy Level A setting that specifies the category of users who are allowed to view the personal information of other users, such as user profile properties, colleagues, or memberships.
Private Web Part A Web Part added to a Web Part Page by a user who is working on the page in personal view. Private Web Parts are available only to the user who added or imported the Web Part.
Provision The process of creating and deploying something, and in some cases, populating that something with default data and settings.
Published Version The version of a list item that is approved and can be seen by all users. The user interface (UI) version number for a published version is incremented to the next positive major version number and the minor version is zero. and minor version.
Publishing Pages Publishing pages are pages under /Pages/ document libraries on each site that include specific content types. Content for these pages are stored in this document library like columns in a list, with each column storing data for a page field.
Recycle Bin A container for items that are deleted. Items in this container are visible to users with the appropriate permissions and to site collection administrators and can be recovered or removed (default of 45 days before they are automatically emptied).
Ribbon The Ribbon is a new UI component added to simplify navigation, and increase the visibility of functions. It is Context Sensitive (which means when you click on something, it knows what you clicked on).
Search Scope A list of attributes that define a collection of items to be filtered/returned in searches.
Server Farm A central group of network servers maintained by an enterprise. A server farm provides a network with load balancing, scalability, and fault tolerance. In some configurations, multiple servers may appear to users as a single resource. Each SharePoint farm has a single, unique configuration database where information and configuration settings for the farm are registered. Each server in the farm relies on that configuration database to get information about the farm and to provide services in the farm.
Shared Documents Library A document library that is included by default in the Team Site site template.
Shared Web Part A Web Part added to a Web Part Page by a user who is working on the page in shared view. Shared Web Parts are available to all users of a Web Part Page who have the appropriate permissions.
SharePoint Foundation (Server) This is the next version of WSS renamed. This version has no licensing costs (aside from infrastructure it runs on) and has less functionality than the full copy of SharePoint Server.
SharePoint Server The new product/platform name. No longer called MOSS or Microsoft Office SharePoint Server.
Site A complete Web site stored in a named leaf of the top-level Web site.
Site Collection A set of Web sites on a Web application that has the same owner and share administration settings. Each site collection contains a top-level Web site and can contain one or more sites (or subsites). There can be multiple site collections on each Web application. A site collection can use only a single content database. Everything is now a site collection: a portal is a site collection where Home is the top-level Web site and the areas are sites (or subsites), a channel hierarchy is a site collection where the root channel is the top-level Web site and sub-channels are sites (or subsites).
Site Collection Administrator A user who has administrative permissions for a site collection.
Site Column A column that can be associated with a content type or list within a site or site collection.
Site Content Type A named and uniquely identifiable collection of settings and fields that store metadata for lists within individual sites. This is at the site collection level, so that it is available throughout the subsites or sites beneath it.
Site Groups Site groups are custom security groups that apply to a specific Web site. Users are assigned to site groups to grant them permissions on a SharePoint site.
Site Membership The status of being a member of a site and having a defined set of user rights for accessing or managing content on that site.
Site Templates Whenever you create a new site, SPS use predefined templates to simplify the creation of the new elements for the site. These templates allow you to create everything from a specialized team site to a blank site you can use to create content from scratch.
SPD / Sharepoint Designer SharePoint Designer is a free tool which allows users to modify pages, and customize their SharePoint sites in more powerful ways than the out of the box GUI interface.
SPS SharePoint Portal Server
Subsite A named subdirectory of the top-level Web site that is a complete Web site. Each subsite can have independent administration, authoring, and browsing permissions from the top-level Web sites and other subsites.
Survey A Web site component that enables users to respond to a set of questions specified by the creator of the survey. Results are tallied in a graphical summary. Surveys provide a way to poll portal users for input on a subject. Surveys support a wide variety of response types from simple Yes/No answers to free-form text.
Term Set A collection of related terms. For example, the term set named “milestone” could include the terms “M0” “M1” “M2” “Alpha” “Beta1” “Beta2” “RC1” “RC2” and “RTM.”
Top-Level Web Site The top, root default site in a site collection. Every site collection has, at its root a top-level Web site. Access to the top-level web site is provided supplying the URL of the site collection (like http://ServerURL or http://ServerURL/sites/SiteCollectionName) without specifying a page name or subsite.
View A named collection of settings for querying and displaying items/documents in a SharePoint list/library. There are two types of views: Personal, which can be used only by the user who creates them; and Public, which can be used by all users who have access to the site.
Web Application A virtual server that resides on an HTTP server but appears to the user as a separate HTTP server. Several Web applications can reside on one computer, each capable of running its own programs and each having individualized access to input and peripheral devices. Each Web application can have its own domain name and IP address.
Web Part Customizable Web page element that can be added to SharePoint pages
Web Part Connection An element in a Web Parts page that defines a provider-consumer data relationship between two Web Parts. When a Web Parts page is rendered, data provided by one Web Part can affect how and what is rendered by the other Web Part.
Web Part Page Often certain pages will be called web part pages – Really this just means that these pages most likely have pre-defined zones you can use to add and re-arrange webparts on.
Web Part Properties The properties which can be changed for a web part to appear or function differently.
Web Part Zone A container with a set of properties that can be configured to control the organization and format of Web Parts on a Web Part Page. Web Part zones can also be used to provide protection against changes to Web Parts.
Workflow The automation of business processes, where business documents and tasks are passed automatically from one user to another for action, according to a set sequence.
WSS Windows SharePoint Services

Using REST in Sharepoint 2013