जगदीश खोलिया

Monday, November 21, 2011

Difference between TRUNCATE, DELETE and DROP

Difference between TRUNCATE, DELETE and DROP?

DELETE & TRUNCATE are two TSQL commands used to remove records from a particular table. But they differ in how they execute and operate.
DELETE: (http://msdn.microsoft.com/en-us/library/ms189835.aspx)
- Removes some or all rows from a table.
- A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
- Causes all DELETE triggers on the table to fire.
- It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
- According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
- Thus it requires more locks and database resources.
- This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.
TRUNCATE: (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
- Removes all rows from a table.
- Does not require a WHERE clause, not allowed here.
- Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
- No triggers are fired on this operation because it does not log individual rows.
- It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
- Thus it also requires less number of locks.
- TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
- This is a DDL command as its resets identity columns, deallocates data pages and empty them for use of other objects in the database.
Note: It is a misconception among some people that TRUNCATE cannot be rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE & TRUNCATE both can be rollbacked if provided inside a transaction.
DROP: (http://msdn.microsoft.com/en-us/library/ms173790.aspx)
The DROP TABLE command removes one or more table(s) from the database.
- All related data, indexes, triggers, constraints, and permission specifications for those tables are dropped by them operation.
- Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
- Cannot drop a table that is referenced by any Foreign Key constraint.
- According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

Thursday, November 17, 2011

Reverse a string using CTE in sql

DECLARE @StringToReverse VARCHAR(55)
    SET @StringToReverse = 'jagdish kholiya'   
    ;WITH cte AS (
          SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln        
           UNION ALL
          SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
          FROM cte
          WHERE ln >= 1)
    SELECT @StringToReverse AS String, revStr FROM cte WHERE ln = 0

Finnding & Removing Duplicate records from a table

There are lots of questions/posts about dealing with duplicate records in many SQL Server forums. Many of these questions are asked in “SQL Server Interviews” and many developers starting their carrier in database programming find it challenging to deal with. Here we will see how we can deal with such records.
Duplicate data is one of the biggest pain points in our IT industry, which various projects have to deal with. Whatever state-of-art technology and best practices followed, the big ERP, CRM, SCM and other inventory based database management projects ends up in having duplicate & redundant data. Duplicate data keeps on increasing by manual entries and automated data loads. Various data leads getting pumped into system’s databases without proper deduping & data cleansing leads to redundant data and thus duplicated record-sets.
Data cleansing requires regular exercise of identifying duplicates, validating and removing them. To minimize these type of scenarios various checks and filters should also be applied before loading new leads into the system.
Lets check this by a simple exercise how we can identify & remove duplicate data from a table:
1USE [AdventureWorks]
2GO
3 
4-- Insert some sample records from Person.Contact table of [AdventureWorks] database:
5SELECT TOP 10 ContactID, FirstName, LastName, EmailAddress, Phone
6INTO DupContacts
7FROM Person.Contact
8 
9SELECT * FROM DupContacts

1-- Insert some duplicate records from the same list inserted above.
2INSERT INTO DupContacts
3SELECT TOP 50 PERCENT FirstName, LastName, EmailAddress, Phone
4from DupContacts
5 
6SELECT * FROM DupContacts

1-- Insert some more duplicate records from the same list inserted above.
2INSERT INTO DupContacts
3SELECT TOP 20 PERCENT FirstName, LastName, EmailAddress, Phone
4from DupContacts
5 
6SELECT * FROM DupContacts

01--// Identify Duplicate records & delete them:-
02 
03-- Method #1: by using ROW_NUMBER() function:
04;WITH dup as (
05SELECT ContactID, FirstName, LastName, EmailAddress, Phone,
06ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ContactID) AS NumOfDups
07FROM DupContacts)
08SELECT * FROM dup
09WHERE NumOfDups > 1
10ORDER BY ContactID

1-- Remove/Delete duplicate records:
2;WITH dup as (
3SELECT ContactID, FirstName, LastName, EmailAddress, Phone,
4ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ContactID) AS NumOfDups
5FROM DupContacts)
6DELETE FROM dup
7WHERE NumOfDups > 1
8 
9SELECT * FROM DupContacts

1-- Method #2: by using SELF-JOIN:
2SELECT DISTINCT a.ContactID, a.FirstName, a.LastName, a.EmailAddress, a.Phone
3FROM DupContacts a
4JOIN DupContacts b
5ON a.FirstName = b.FirstName
6AND a.LastName = b.LastName
7AND a.ContactID > b.ContactID

1-- Remove/Delete duplicate records:
2DELETE a
3FROM DupContacts a
4JOIN DupContacts b
5ON a.FirstName = b.FirstName
6AND a.LastName = b.LastName
7AND a.ContactID > b.ContactID
8 
9SELECT * FROM DupContacts

1-- Method #3: by using AGGREGATES & Sub-QUERY:
2SELECT * FROM DupContacts
3WHERE ContactID NOT IN (SELECT MIN(ContactID)
4FROM DupContacts
5GROUP BY FirstName, LastName)

1-- Remove/Delete duplicate records:
2DELETE FROM DupContacts
3WHERE ContactID NOT IN (SELECT MIN(ContactID)
4FROM DupContacts
5GROUP BY FirstName, LastName)
6 
7SELECT * FROM DupContacts



 
Referenced from : http://sqlwithmanoj.wordpress.com/category/sql-tips/

Friday, October 14, 2011

Ajax,Linq and XML in ASP.NET 3.5 and C#


AJAX and LINQ are probably the most-talked about Microsoft offerings at the moment, and rightly-so. Both provide us with powerful features to add to our development arsenal- AJAX allows us to create more user-friendly web applications that can almost rival our desktop equivalents; and then LINQ provides a unified way for us to communicate and interact with a wide array of data sources.
This tutorial will combine the two, and throw in a bit of XML too. In this example we are going to create a web application that will use an XML file to store small amounts of data, that we will display and also add to using AJAX and LINQ.
To demonstrate this, we will be storing names, cities and ages of people in the XML file. We will use a literal control to display the XML data and three textboxes to input new data.
Our XML file structure will look something like this:


<?xml version="1.0" encoding="utf-8"?>
<Persons>
<Person>
<Name>Paxton</Name>
<City>Munich</City>
<Age>29</Age>
</Person>
<Person>
<Name>Mike</Name>
<City>Orlando</City>
<Age>33</Age>
</Person>
</Persons>

Because we are using VS.NET 2008, all we need to do to enable AJAX on our page is add the following:

<form id="form1" runat="server">
<asp:ScriptManager id="ScriptManager1" runat="server" />

<asp:UpdatePanel ID="updAdd" runat="server">
<ContentTemplate>

</ContentTemplate>
</asp:UpdatePanel>
</form>
This is the AJAX structure we will be using for our page. The ScriptManager will handle all of our AJAX calls, we don't need to do anything with that. The UpdatePanel specifies an area of the page that will partially refresh- instead of posting back the whole page. We will also add a trigger, to specify what will 'trigger' the partial refresh. To do this, though, we need the name of our button.
We add a button and the trigger to the page:

<form id="form1" runat="server">
<asp:ScriptManager id="ScriptManager1" runat="server" />

<asp:UpdatePanel ID="updAdd" runat="server">
<Triggers>
<asp:AsyncPostBackTrigger ControlID="butAdd" EventName="Click" />
</Triggers>
<ContentTemplate>

<asp:Button ID="butAdd" runat="server" Text="Add" onclick="butAdd_Click" />
</ContentTemplate>
</asp:UpdatePanel>
</form>


We reference the button in the trigger field of the UpdatePanel, which will cause the partial refresh to initiate when the button is clicked.
Next, we can add the rest of our form - the textboxes for data input, and the literal control for reading the XML. We can also add another button to initiate the reading of the XML file:

<form id="form1" runat="server">
<asp:ScriptManager id="ScriptManager1" runat="server" />

<asp:UpdatePanel ID="updAdd" runat="server">
<Triggers>
<asp:AsyncPostBackTrigger ControlID="butAdd" EventName="Click" />
</Triggers>
<ContentTemplate>
<strong>Add to XML</strong><br />
Name:<br />
<asp:TextBox ID="txtName" runat="server" /><br />
City:<br />
<asp:TextBox ID="txtCity" runat="server" /><br />
Age:<br />
<asp:TextBox ID="txtAge" runat="server" /><br />
<asp:Button ID="butAdd" runat="server" Text="Add" onclick="butAdd_Click" /><br />
<asp:Label ID="lblStatus" runat="server" />
<br /><br />
<strong>Read XML:</strong><br />
<asp:Button ID="butRead" runat="server" Text="Read" onclick="butRead_Click" /><br />
<asp:Literal ID="litResults" runat="server" />
</ContentTemplate>
</asp:UpdatePanel>
</form>

Notice that both of our buttons have the onclick handlers specified. We can now code these in the code-behind, like so:

protected void butRead_Click(object sender, EventArgs e)
{
readXML();
lblStatus.Text = "";
}

protected void butAdd_Click(object sender, EventArgs e)
{
try
{
XDocument xmlDoc = XDocument.Load(Server.MapPath("People.xml"));

xmlDoc.Element("Persons").Add(new XElement("Person", new XElement("Name", txtName.Text),
new XElement("City", txtCity.Text), new XElement("Age", txtAge.Text)));

xmlDoc.Save(Server.MapPath("People.xml"));
lblStatus.Text = "Data successfully added to XML file.";
readXML();
}
catch
{
lblStatus.Text = "Sorry, unable to process request. Please try again.";
}
}
We tell the read button to call another method when it is clicked because we can reuse it - notice we call the same method at the end of the add button. This is so we can update the displayed XML when we add new content. We are using LINQ to open the XML document first, and then add a new element into the parent element of Persons. Then we save changes to the XML file and let the user know.
The readXML method looks like this:

protected void readXML()
{
XDocument xmlDoc = XDocument.Load(Server.MapPath("People.xml"));

var persons = from person in xmlDoc.Descendants("Person")
select new
{
Name = person.Element("Name").Value,
City = person.Element("City").Value,
Age = person.Element("Age").Value,
};

litResults.Text = "";
foreach (var person in persons)
{
litResults.Text = litResults.Text + "Name: " + person.Name + "<br />";
litResults.Text = litResults.Text + "City: " + person.City + "<br />";
litResults.Text = litResults.Text + "Age: " + person.Age + "<br /><br />";
}

if (litResults.Text == "")
litResults.Text = "No Results.";
}
As you can see, LINQ looks similar to SQL, but instead of communicating with a database only, we can use LINQ to communication with any data source - in this case an XML file. First we declare a variable persons and assign to it the collection from the XML file's descendants, Person. Then we select all the data from within and simply loop through our collection and output to the literal control.

Monday, October 3, 2011

LINQ to SQL


What is LINQ TO SQL ?
LINQ to SQL is new component that is released with .NetFramework 3.5. LINQ to SQL provides a way where a developer can manage a relational database object as a .net object. In simple terms, it is an ORM (Object-relational mapping) that is used to manage a back end object as a front end object in object oriented way i.e. relational to object oriented type system.
We can query, insert, update and delete the underlying database object with LINQ to SQL classes.  When we query or update or delete these .net objects, LINQ to SQL will automatically take care of the operations in underlying relational data objects.


LINQ to SQL uses LINQ as querying technique to query the database object through the LINQ to SQL.

What is LINQ?
LINQ stands for Language Integrated Query.  LINQ is a data querying methodology which provides querying capabilities to .Net languages with syntax similar to SQL query. LINQ has a set of querying operators that can be used to query in memory object collection, Sql database, XML, etc. LINQ processing engine will then convert the LINQ query to native query specific to the database to execute against the datasource.  Since, the querying feature is integrated with the language; one can build an efficient query based on the language of their choice.  With Visual Studio, we have intellisense support and with language support, we have type safety and compile-time error checks.

There are many advantages when we use ORM in our projects. Below are few,
1.      You can query the database object treating it as a .net object using .net language.
2.      Your data access development will be easier.
3.      It handles the SQL injection attacks automatically.
4.      You will get type safety and compiler advantages since the database objects are managed as .net object.
5.      Using Visual Studio provides you intellisense support and other tools for developing the LINQ to SQL classes

Constructing your first LINQ to class
Visual studio 2008 is packed with tool called LINQ to SQL designer which will help us to build the LINQ to SQL classes very easily.

LINQ to SQL designer
It is a new designer to design relational database object as LINQ to SQL objects.  We can either, drag and drop database object from “Server Explorer” or, we can design the LINQ to SQL object manually using the designer and tools.

To understand better, we will create two simple tables, Employee and Department, will design our LINQ to SQL class.

Open Visual Studio 2008, Click File >Website and choose ASP.Net Website. Choose the language of your choice and name your website according to your need.

In solution explorer, right the project and select “Add New Item”. Select “LINQ to SQL classes” as shown in below figure. I have named it as EmployeeInfo.
 What is LINQ to SQL?
This will add an EmployeeInfo.dbml file inside “App_Code” folder.  In Visual Studio, EmployeeInfo.dbml will have 2 panes. The left pane is for deigning the data objects and the right pane can be used for creating methods that operates on the data objects.
The Visual Studio toolbar will contain a new toolbar for designing LINQ to SQL objects. Refer below,
 What is LINQ to SQL?
We will see more about using these toolbar to create our own object in future articles. Once we include the LINQ to SQL Class in the project, there will be 3 files added to the App_Code.

EmployeeInfo.dbml
An XML file that contains information about the tables like table name, column name, type, etc
EmployeeInfo.layout
An XML file that specifies how the designer places the objects.
EmployeeInfo.designer.cs
The C# code file that will have code to interact with the underlying database called DataContext object. It also has entity class definition.

Designing the LINQ to SQL classes
Open Server Explorer, Expand the database tables.
Drag Employee and Department into LINQ to SQL designer and click Save. The Objects will be created automatically. Refer the below figure.

 What is LINQ to SQL?

This will update the EmployeeInfo.designer.cs file with class definition for Employee and Department. It will also generate the DataContext object for these objects for interacting with the underlying datasource i.e. it is with the help of this DataContext class we can interact with the database objects in our managed environment. In our case, it is EmployeeInfoDataContext.

The arrow between the object is the foreign key relationship between them. This means that the Employee class will have the Department class as a member.

Thus, we have modelled LINQ to SQL classes which has O/R mapping. Moving forward, we will see how we can we use these objects to do our data access.

Using LINQ to SQL classes
The below code will fetch all the employees rows and will populate a GridView control.
EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();
        var LINQQuery = from emp in dbEmp.Employees
                        select emp;
        gvEmployee.DataSource = LINQQuery;
        gvEmployee.DataBind();


Updating Employee Information
The below code uses the LINQ to SQL class to update the employee information whose employee id is fetched from txtEmpID.
EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();       
        string EmpID = txtEmpID.Text;           
        Employee empTemp = (from emp in dbEmp.Employees
                           where emp.EmpID == int.Parse(EmpID)
                           select emp).Single();
     
         empTemp.EmpName = txtEmpName.Text;       
         empTemp.DeptID = int.Parse(ddlTemp.SelectedValue);      
         empTemp.Age = int.Parse(txtAge.Text);       
         empTemp.Address = txtAddr.Text;
       
dbEmp.SubmitChanges();






Deleting a Row
EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();      
        string EmpID = null;      
        EmpID = txtEmpID.Text;
        Employee empTemp = (from emp in dbEmp.Employees
                            where emp.EmpID == int.Parse(EmpID)
                            select emp).Single();
        dbEmp.Employees.DeleteOnSubmit(empTemp);
        dbEmp.SubmitChanges();

Inserting a new Row
EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();
        Employee emp = new Employee();      
       emp.EmpName = txtEmpName.Text;     
       emp.DeptID = int.Parse(ddlTemp.SelectedValue);     
       emp.Age = int.Parse(txtAge.Text);     
       emp.Address = txtAddr.Text;
       dbEmp.Employees.InsertOnSubmit(emp);
       dbEmp.SubmitChanges();

Wednesday, September 21, 2011

JQuery Concepts

How do I select an item using class or ID?

This code selects an element with an ID of "myDivId". Since IDs are unique, this expression always selects either zero or one elements depending upon whether or not an element with the specified ID exists.
 $('#myDivId')
This code selects an element with a class of "myCssClass". Since any number of elements can have the same class, this expression will select any number of elements.
 $('.myCssClass')
A jQuery object containing the selected element can be assigned to a JavaScript variable like normal:
 var myDivElement = $('#myDivId');
Usually, elements in a jQuery object are acted on by other jQuery functions:
 var myValue = $('#myDivId').val();    // get the value of a form input
 
 $('#myDivId').val("hello world");     // set the value of a form input

How do I select elements when I already have a DOM element?

If you have a variable containing a DOM element, and want to select elements related to that DOM element, simply wrap it in a jQuery object.
 var myDomElement = document.getElementById('foo'); // a plain DOM element
 $(myDomElement).find('a'); // finds all anchors inside the DOM element
Many people try to concatenate a DOM element or jQuery object with a CSS selector, like so:
 $(myDomElement + '.bar'); // WRONG! equivalent to $("[object HTMLElement].bar")
This is wrong. You cannot concatenate strings to objects.

How do I test whether an element has a particular class?

hasClass (added in version 1.2) handles this common use case:
 $("div").click(function(){
   if ( $(this).hasClass("protected") )
     $(this)
       .animate({ left: -10 })
       .animate({ left: 10 })
       .animate({ left: -10 })
       .animate({ left: 10 })
       .animate({ left: 0 });
 });
You can also use the is() method along with an appropriate selector for more advanced matching:
 if ( $('#myDiv').is('.pretty.awesome') )
   $('#myDiv').show();
Note that this method allows you to test for other things as well. For example, you can test whether an element is hidden (by using the custom :hidden selector):
 if ( $('#myDiv').is(':hidden') )
   $('#myDiv').show();

Use the length property of the jQuery collection returned by your selector:
 if ( $('#myDiv').length )
   $('#myDiv').show();
Note that it isn't always necessary to test whether an element exists. The following code will show the element if it exists, and do nothing (with no errors) if it does not:
 $('#myDiv').show();

How do I determine the state of a toggled element?

You can determine whether an element is collapsed or not by using the :visible and :hidden selectors.
 var isVisible = $('#myDiv').is(':visible');
 var isHidden = $('#myDiv').is(':hidden');
If you're simply acting on an element based on its visibility, just include ":visible" or ":hidden" in the selector expression. For example:
 $('#myDiv:visible').animate({left: '+=200px'}, 'slow');

How do I select an element by an ID that has characters used in CSS notation?

Because jQuery uses CSS syntax for selecting elements, some characters are interpreted as CSS notation. For example, ID attributes, after an initial letter (a-z or A-Z), may also use periods and colons, in addition to letters, numbers, hyphens, and underscores (see W3C Basic HTML Data Types). The colon (":") and period (".") are problematic within the context of a jQuery selector because they indicate a pseudo-class and class, respectively.
In order to tell jQuery to treat these characters literally rather than as CSS notation, they must be "escaped" by placing two backslashes in front of them.
 // Does not work
 $("#some:id")
 
 // Works!
 $("#some\\:id")
 // Does not work
 $("#some.id")
 
 // Works!
 $("#some\\.id")
The following function takes care of escaping these characters and places a "#" at the beginning of the ID string:
 function jq(myid) { 
   return '#' + myid.replace(/(:|\.)/g,'\\$1');
 }

The function can be used like so:
 $( jq('some.id') )

How do I disable/enable a form element?

There are two ways to disable/enable form elements.
Set the 'disabled' attribute to true or false:
 // Disable #x
 $('#x').attr('disabled', true);
 // Enable #x
 $('#x').attr('disabled', false);
Add or remove the 'disabled' attribute:
 // Disable #x
 $("#x").attr('disabled', 'disabled');
 // Enable #x
 $("#x").removeAttr('disabled');
You can try an example of enabling/disabling with the following demo:

and here's the source code to the demo:
 <select id="x" style="width:200px;">
   <option>one</option>
   <option>two</option>
 </select>
 <input type="button" value="Disable" onclick="$('#x').attr('disabled','disabled')"/>
 <input type="button" value="Enable" onclick="$('#x').removeAttr('disabled')"/>

How do I check/uncheck a checkbox input or radio button?

There are two ways to check/uncheck a checkbox/radio button.
Set the 'checked' attribute to true or false.
 // Check #x
 $('#x').attr('checked', true);
 // Uncheck #x
 $('#x').attr('checked', false);
Add or remove the 'checked' attribute:
 // Check #x
 $("#x").attr('checked', 'checked');
 // Uncheck #x
 $("#x").removeAttr('checked');

and here's the source code to the demo:
 <label><input type="checkbox" id="c"/> I'll be checked/unchecked.</label>
 <input type="button" value="Check" onclick='$("#c").attr("checked","checked")'/>
 <input type="button" value="Uncheck" onclick='$("#c").removeAttr("checked")'/>

How do I get the text value of a selected option?

Select elements typically have two values that you want to access. First there's the value to be sent to the server, which is easy:
 $("#myselect").val();
 // => 1
The second is the text value of the select. For example, using the following select box:
 <select id="myselect">
   <option value="1">Mr</option>
   <option value="2">Mrs</option>
   <option value="3">Ms</option>
   <option value="4">Dr</option>
   <option value="5">Prof</option>
 </select>
If you wanted to get the string "Mr" if the first option was selected (instead of just "1"), you would do that in the following way:
 $("#myselect option:selected").text();
 // => "Mr"
You can see this in action in the following demo:

and here's the full source code to the demo:
 <select id="myselect">
   <option value="1">Mr</option>
   <option value="2">Mrs</option>
   <option value="3">Ms</option>
   <option value="4">Dr</option>
   <option value="5">Prof</option>
 </select>
 <input type="button" value="Get Value" onclick="alert($('#myselect').val())"/>
 <input type="button" value="Get Text Value" onclick="alert($('#myselect option:selected').text())"/>

How do I replace text from the 3rd element of a list of 10 items?

Either the :eq() selector or the .eq() method will allow you to select the proper item. However, to replace the text, you must get the value before you set it:
  // This doesn't work; text() returns a string, not the jQuery object
  $(this).find('li a').eq(2).text().replace('foo','bar');

  // This works
  var $thirdLink = $(this).find('li a').eq(2);
  var linkText = $thirdLink.text().replace('foo','bar');
  $thirdLink.text(linkText);
The first example just discards the modified text. The second example saves the modified text and then replaces the old text with the new modified text. Remember, .text() gets; .text("foo") sets.



How do I get and use the server response from an AJAX request?

The 'A' in AJAX stands for asynchronous. When invoking functions that have asynchronous behavior you must provide a callback function to capture the desired result. This is especially important with AJAX in the browser because when a remote request is made, it is indeterminate when (or even if) the response will be received.
The following snippet shows an example of making an AJAX call and alerting the response (or error):
 $.ajax({
     url: 'myPage.php',
     success: function(response) {
        alert(response);
     },
     error: function(xhr) {
        alert('Error!  Status = ' + xhr.status);
     }
 });
But how can the response be used in context of a function? Consider this flawed example where we try to update some status information on the page:
 function updateStatus() {
     var status;
     $.ajax({
         url: 'getStatus.php',
         success: function(response) {
             status = response;
         }
     });
     // update status element?  this will not work as expected
     $('#status').html(status);
 }
The code above does not work as desired due to the nature of asynchronous programming. The provided success handler is not invoked immediately, but rather at some time in the future when the response is received from the server. So when we use the 'status' variable immediately after the $.ajax call, its value is still undefined. The next snippet shows how we can rewrite this function to behave as desired:
 function updateStatus() {
     $.ajax({
         url: 'getStatus.php',
         success: function(response) {
             // update status element
             $('#status').html(response);
         }
     });
 }
But how can I return the server response from an AJAX call? Here again we show a flawed attempt. In this example we attempt to alert the http status code for the url of 'getStatus.php':
 //...
 alert(getUrlStatus('getStatus.php'));
 //...
 function getUrlStatus(url) {
     $.ajax({
         url: url,
         complete: function(xhr) {
             return xhr.status;
         }
     });
 }
The code above will not work because you cannot 'return' data from a function that is called asynchronously. Instead, it must be rewritten to use a callback:
 //...
 getUrlStatus('getStatus.php', function(status) {
     alert(status);
 });
 // ...
 function getUrlStatus(url, callback) {
     $.ajax({
         url: url,
         complete: function(xhr) {
             callback(xhr.status);
         }
     });
 }

How do I pull a native DOM element from a jQuery object?

A jQuery object is an array-like wrapper around one or more DOM elements. To get a reference to the actual DOM elements (instead of the jQuery object), you have two options. The first (and fastest) method is to use array notation:
 $('#foo')[0]; // equivalent to document.getElementById('foo')
The second method is to use the get function:
 $('#foo').get(0); // identical to above, only slower
You can also call get without any arguments to retrieve a true array of DOM elements.

Why do ... ?


Why do my events stop working after an AJAX request?

Frequently, when you've added a click (or other event) handler to all links using $('a').click(fn), you'll find that the events no longer work after you've loaded new content into a page using an AJAX request.
When you call $('a'), it returns all the links on the page at the time it was called, and .click(fn) adds your handler to only those elements. When new links are added, they are not affected.
You have two ways of handling this:

Using event delegation

Event delegation is a technique that exploits event bubbling to capture events on elements anywhere in the DOM.
As of jQuery 1.3, you can use the live and die methods for event delegation with a subset of event types. As of jQuery 1.4, you can use these methods (along with delegate and undelegate starting in 1.4.2) for event delegation with pretty much any event type.
For earlier versions of jQuery, take a look at the Live Query plugin by Brandon Aaron. You may also manually handle event delegation by binding to a common container and listening for events from there. For example:
 $('#mydiv').click(function(e){
    if( $(e.target).is('a') )
       fn.call(e.target,e);
 });
 $('#mydiv').load('my.html');
This example will handle clicks on any <a> element within #mydiv, even if they do not exist yet when the click handler is added.

Using event rebinding

This method requires you to call the bind method on new elements as they are added. For example:
 $('a').click(fn);
 $('#mydiv').load('my.html',function(){
   $('#mydiv a').click(fn);
 });
Beware! As of jQuery 1.4.2, binding the same handler to the same element multiple times will cause it to execute more than once. This differs from previous versions of jQuery as well as the DOM 2 Events spec (which normally ignores duplicate event handlers).

Why doesn't an event work on a new element I've created?

As explained in the previous question about AJAX, events are bound only to elements that exist at the time when you issue your initial jQuery call. When you create a new element, you must bind the event to it separately, or use event delegation.

Why do animations set the display style to block?

Only block-level elements can have a custom width or height. When you do an animation on an element that animates the height or width (such as show, hide, slideUp, or slideDown), the display CSS property will be set to 'block' for the duration of the animation. The display property will be reverted to its original value after the animation completes. (This does not work properly for inline-block elements.)
There are two common workarounds:
If you want the element to stay inline, but you just want it to animate in or out, you can use the fadeIn or fadeOut animations instead (which only affect the opacity of an element).
 // Instead of this:
 $("span").show("slow");
 
 // do this:
 $("span").fadeIn("slow");
The other option is to use a block-level element, but to add a float such that it appears to stay inline with the rest of the content around it. The result might looks something like this:
 // A floated block element
 <div style="float:left;">...</div>
 
 // Your code:
 $("div").show("slow");

Threading Concepts

Multithreading or free-threading is the ability of an operating system to concurrently run programs that have been divided into subcomponents, or threads.
Technically, multithreaded programming requires a multitasking/multithreading operating system, such as GNU/Linux, Windows NT/2000 or OS/2; capable of running many programs concurrently, and of course, programs have to be written in a special way in order to take advantage of these multitasking operating systems which appear to function as multiple processors. In reality, the user's sense of time is much slower than the processing speed of a computer, and multitasking appears to be simultaneous, even though only one task at a time can use a computer processing cycle.

Objective

The objective of this document is:
  • A brief Introduction to Threading
  • Features of Threading
  • Threading Advantages

Features and Benefits of Threads

Mutually exclusive tasks, such as gathering user input and background processing can be managed with the use of threads. Threads can also be used as a convenient way to structure a program that performs several similar or identical tasks concurrently.
One of the advantages of using the threads is that you can have multiple activities happening simultaneously. Another advantage is that a developer can make use of threads to achieve faster computations by doing two different computations in two threads instead of serially one after the other.

Threading Concepts in C#

In .NET, threads run in AppDomains. An AppDomain is a runtime representation of a logical process within a physical process. And a thread is the basic unit to which the OS allocates processor time. To start with, each AppDomain is started with a single thread. But it is capable of creating other threads from the single thread and from any created thread as well.

How do they work

A multitasking operation system divides the available processor time among the processes and threads that need it. A thread is executed in the given time slice, and then it is suspended and execution starts for next thread/process in the queue. When the OS switches from one thread to another, it saves thread context for preempted thread and loads the thread context for the thread to execute.
The length of time slice that is allocated for a thread depends on the OS, the processor, as also on the priority of the task itself.

Working with threads

In .NET framework, System.Threading namespace provides classes and interfaces that enable multi-threaded programming. This namespace provides:
  • ThreadPool class for managing group of threads,
  • Timer class to enable calling of delegates after a certain amount of time,
  • A Mutex class for synchronizing mutually exclusive threads, along with classes for scheduling the threads, sending wait notifications and deadlock resolutions.
Information on this namespace is available in the help documentations in the Framework SDK.

Defining and Calling threads

To get a feel of how Threading works, run the below code:
using System;
using System.Threading;

public class ServerClass
{
    // The method that will be called when the thread is started.

    public void Instance Method()
    {
        Console.WriteLine("You are in InstranceMethod.Running on Thread A�);
        Console.WriteLine("Thread A Going to Sleep Zzzzzzzz�);

        // Pause for a moment to provide a delay to make threads more apparent.

        Thread. Sleep(3000);
        Console.WriteLine ("You are Back in InstanceMethod.Running on Thread A");
    }

    public static void StaticMethod()
    {
        Console.WriteLine("You are in StaticMethod. Running on Thread B.");
        // Pause for a moment to provide a delay to make threads more apparent.

        Console.WriteLine("Thread B Going to Sleep Zzzzzzzz");

        Thread.Sleep(5000);
        Console.WriteLine("You are back in static method. Running on Thread B");
    }
}

public class Simple
{
    public static int Main(String[] args)
    {
        Console.WriteLine ("Thread Simple Sample");
        ServerClass serverObject = new ServerClass();
        // Create the thread object, passing in the 

        // serverObject.InstanceMethod method using a ThreadStart delegate.

        Thread InstanceCaller = new 
             Thread(new ThreadStart(serverObject.InstanceMethod));

        // Start the thread.

        InstanceCaller.Start();

        Console.WriteLine("The Main() thread calls this " + 
          "after starting the new InstanceCaller thread.");

        // Create the thread object, passing in the 

        // serverObject.StaticMethod method using a ThreadStart delegate.

        Thread StaticCaller = new Thread(new 
               ThreadStart(ServerClass.StaticMethod));
        // Start the thread.

        StaticCaller.Start();
        Console.WriteLine("The Main () thread calls this " + 
                "after starting the new StaticCaller threads.");
        return 0;
    }
}
If the code in this example is compiled and executed, you would notice how processor time is allocated between the two method calls. If not for threading, you would have to wait till the first method slept for 3000 secs for the next method to be called. Try disabling threading in the above code and notice how they work. Nevertheless, execution time for both would be the same.
An important property of this class (which is also settable) is Priority.

Scheduling Threads

Every thread has a thread priority assigned to it. Threads created within the common language runtime are initially assigned the priority of ThreadPriority.Normal. Threads created outside the runtime retain the priority they had before they entered the managed environment. You can get or set the priority of any thread with the Thread.Priority property.
Threads are scheduled for execution based on their priority. Even though threads are executing within the runtime, all threads are assigned processor time slices by the operating system. The details of the scheduling algorithm used to determine the order in which threads are executed varies with each operating system. Under some operating systems, the thread with the highest priority (of those threads that can be executed) is always scheduled to run first. If multiple threads with the same priority are available, the scheduler cycles through the threads at that priority, giving each thread a fixed time slice in which to execute. As long as a thread with a higher priority is available to run, lower priority threads do not get to execute. When there are no more run able threads at a given priority, the scheduler moves to the next lower priority and schedules the threads at that priority for execution. If a higher priority thread becomes run able, the lower priority thread is preempted and the higher priority thread is allowed to execute once again. On top of all that, the operating system can also adjust thread priorities dynamically as an application's user interface is moved between foreground and background. Other operating systems might choose to use a different scheduling algorithm.

Pausing and Resuming threads

After you have started a thread, you often want to pause that thread for a fixed period of time. Calling Thread.Sleep causes the current thread to immediately block for the number of milliseconds you pass to Sleep, yielding the remainder of its time slice to another thread. One thread cannot call Sleep on another thread. Calling Thread.Sleep(Timeout.Infinite) causes a thread to sleep until it is interrupted by another thread that calls Thread.Interrupt or is aborted by Thread.Abort.

Thread Safety

When we are working in a multi threaded environment, we need to maintain that no thread leaves the object in an invalid state when it gets suspended. Thread safety basically means the members of an object always maintain a valid state when used concurrently by multiple threads.
There are multiple ways of achieving this � The Mutex class or the Monitor classes of the Framework enable this, and more information on both is available in the Framework SDK documentation. What we are going to look at here is the use of locks.
You put a lock on a block of code � which means that that block has to be executed at one go and that at any given time, only one thread could be executing that block.
The syntax for the lock would be as follows:
using System;
using System.Threading;

//define the namespace, class etc.


...
public somemethod(...)
{
    ...
    lock(this)
    {
        Console.WriteLine(Inside the lock now);
        ...
    }
}
In the above code sample, the code block following the lock statement will be executed as one unit of execution, and only one thread would be able to execute it at any given time. So, once a thread enters that block, no other thread can enter the block till the first thread has exited it.
This becomes necessary in the kind of database transactions required in banking applications and reservations systems etc.

Word of Caution

Although multithreading can be a powerful tool, it can also be difficult to apply correctly. Improperly implemented multithreaded code can degrade application performance, or even cause frozen applications.

Tuesday, September 20, 2011

Delete Duplicate Records – Rows(SQL Server)

SQL SERVER – Delete Duplicate Records – Rows

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.
DELETE
FROM
MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Friday, September 2, 2011

UPDATE_STATISTICS in Sql Server


UPDATE_STATISTICS
updates the indexes on these tables accordingly. Basically this command is used when we have to do a large data process. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. Or we can say that it updates information about the distribution of key values in specified indexes, for all columns in an index, table, or partition.

Syntax:


update
statistics table_name
[[ partition data_partition_name ] [ (column_list ) ]
|
index_name [ partition index_partition_name ] ]
[ using step values ]
[ with consumers = consumers ][, sampling=N percent ]

Monday, August 8, 2011

Page Life Cycle events

Seq Events Controls Initialized View state
Available
Form data
Available
What Logic can be written here?
1 Init No No No Note: You can access form data etc. by using ASP.NET request objects but not by Server controls.Creating controls dynamically, in case you have controls to be created on runtime. Any setting initialization.Master pages and them settings. In this section, we do not have access to viewstate , posted values and neither the controls are initialized.
2 Load view state Not guaranteed Yes Not guaranteed You can access view state and any synch logic where you want viewstate to be pushed to behind code variables can be done here.
3 PostBackdata Not guaranteed Yes Yes You can access form data. Any logic where you want the form data to be pushed to behind code variables can be done here.
4 Load Yes Yes Yes This is the place where you will put any logic you want to operate on the controls. Like flourishing a combobox from the database, sorting data on a grid, etc. In this event, we get access to all controls, viewstate and their posted values.
5 Validate Yes Yes Yes If your page has validators or you want to execute validation for your page, this is the right place to the same.
6 Event Yes Yes Yes If this is a post back by a button click or a dropdown change, then the relative events will be fired. Any kind of logic which is related to that event can be executed here.
7 Pre-render Yes Yes Yes If you want to make final changes to the UI objects like changing tree structure or property values, before these controls are saved in to view state.
8 Save view state Yes Yes Yes Once all changes to server controls are done, this event can be an opportunity to save control data in to view state.
9 Render Yes Yes Yes If you want to add some custom HTML to the output this is the place you can.
10 Unload Yes Yes Yes Any kind of clean up you would like to do here.