Search This Blog

Friday, May 27, 2016

WebForms UnobtrusiveValidationMode requires a ScriptResourceMapping for 'jquery'. Please add a ScriptResourceMapping named jquery(case-sensitive).

Error in 2012 VS when using validation control :

Server Error in '/it' Application.

WebForms UnobtrusiveValidationMode requires a ScriptResourceMapping for 'jquery'. Please add a ScriptResourceMapping named jquery(case-sensitive).

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: WebForms UnobtrusiveValidationMode requires a ScriptResourceMapping for 'jquery'. Please add a ScriptResourceMapping named jquery(case-sensitive).

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[InvalidOperationException: WebForms UnobtrusiveValidationMode requires 
a ScriptResourceMapping for 'jquery'. Please add a ScriptResourceMapping 
named jquery(case-sensitive).]
   System.Web.UI.ClientScriptManager.EnsureJqueryRegistered() +2170706
   System.Web.UI.WebControls.BaseValidator.RegisterUnobtrusiveScript() +10
   System.Web.UI.WebControls.BaseValidator.OnPreRender(EventArgs e) +9576593
   System.Web.UI.Control.PreRenderRecursiveInternal() +83
   System.Web.UI.Control.PreRenderRecursiveInternal() +168
   System.Web.UI.Control.PreRenderRecursiveInternal() +168
   System.Web.UI.Control.PreRenderRecursiveInternal() +168
   System.Web.UI.Control.PreRenderRecursiveInternal() +168
   System.Web.UI.Page.ProcessRequestMain(Boolean 
includeStagesBeforeAsyncPoint,Boolean includeStagesAfterAsyncPoint) +974

Solution:
Add in web.config :

 <appSettings>
      <add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />
 </appSettings>


Tuesday, May 17, 2016

Differences between Hash table and Dictionary.



Differences between Hash table and Dictionary
Sno.
Dictionary
Hash table
1
It returns error if we try to find a key which does not exist.
It returns null if we try to find a key which does not exist.
2
It is faster than a Hashtable because there is no boxing and unboxing.
It is slower than dictionary because it requires boxing and unboxing.
3
Only public static members are thread safe.
All the members in a Hashtable are thread safe.
4
Dictionary is a generic type which means we can use it with any data type.
Hashtable is not a generic type.

What is Hashtable ?

Hashtable optimizes lookups. It computes a hash of each key you add. It then uses this hash code to look up the element very quickly. It is an older .NET Framework type. It is slower than the generic Dictionary type.

A hash table is made up of a mapping function and an array. The array contains your data, while the mapping function is used to assign numerical values (keys) to the data. This helps in categorizing the data, which speeds up search times when you search for it.

using System.Collections;
using System;
class Example
{
    static void Main()
    {
                Hashtable hashtable = new Hashtable();
                hashtable [1] = "One";
                hashtable [2] = "Two";
                hashtable [3] = "Three";
                foreach (DictionaryEntry entry in hashtable)
                {
                    Console.WriteLine("{0} : {1}", entry.Key, entry.Value);
                }
    }
}

Output:
3: Three
2: Two
1: One

HashTable Method()

using System.Collections;
using System;
class Example
{
static Hashtable GetHashtable()
    {
                // Creating a simple hashtable called hashtable.
                Hashtable hashtable = new Hashtable();
                hashtable.Add("chandra", 10);
                hashtable.Add("prakash", 11);
                hashtable.Add("vipin", 12);
                return hashtable;
   }
static void Main ()
{
Hashtable hashtable = GetHashtable ();
Console.WriteLine(hashtable.ContainsKey (“chandra”));
}

Output:

True

Saturday, May 14, 2016

Example of Dictionary Class

A Dictionary class is a data structure that represents a collection of keys and values pair of data. The key is identical in a key-value pair and it can have at most one value in the dictionary, but a value can be associated with many different keys.
This class is defined in the System.Collections.Generic namespace, so you should import or using System.Collections.Generic namespace.

Dictionary<TKey,TValue>

TKey - The type of the keys in the dictionary.
TValue - The type of the values in the dictionary.

Input Occupancy String format:
Room_Adult_Children_Age  : 1_2_1_10  [room always in string 1]
1_2_1_10 ; 1_2_1_8   or 1_2_1_10  ; 1_2_1_8  ; 1_2_1_5 
Output: 2_1_2_10_11
Input: 1_1_0_-1; 1_1_0_-1
Output: 2_1_0_-1
Input: 1_2_2_6_8  ; 1_2_2_4_6  ; 1_2_2_6_9 
If you want to search for 2 single room with 2 adult and 0 child you pass the occupancy like that:
Input: 1_2_0_-1 ; 1_2_0_-1



If 2 or 3 room then Input Occupancy string format:

Scenario 1: Search for 1 Adult
If you want to search for 1 single room with 1 adult and 0 child you pass the occupancy like that: Input: 1_1_0_-1
Where -1 stands for an age of an adult which is not required so we pass it -1.

Scenario 2: Search for 1 Adult and 2 Children
If you want to search for 1 single room with 1 adult and 2 child you pass the occupancy like that:
Input: 1_1_1_10;1_1_1_11

 where 10 stand for the first age of the first child and 11 stands for the second age of the child.

Scenario 3: Search for 2 rooms one for 1 Adult and 0 Children and the Second for 2 Adults
If you want to search for 2 rooms with 1 adult and 0 children and another room for 2 adults and 0 children you pass them like that:
Input: 1_1_0_-1;1_2_0_-1

Output: 1_1_0_-1;1_2_0_-1

Scenario 4: Search for 2 rooms for the same 1 Adult and 0 Children.
It will be like that

Scenario 5: Search for 3 rooms for the same 2 Adult and 2 Children.
It will be like that
Output: 3_2_2_6_8_4_6_6_9
Scenario 6: Search for 2 Adult 0 Children
Output: 2_2_0_-1


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        public string GroupOccupancy(string occupancy)
        {
            string abc = "";

            string[] arrOccupancy = occupancy.Split(';');
            List<string> objage = new List<string>();

            /*The Dictionary type provides fast lookups with keys to get values. With it we use keys and values of any type, including ints and strings.*/

            Dictionary<object, int> DifferentCarcategory = new Dictionary<object, int>();            

            foreach (Object obj in arrOccupancy)
            {
                /*ContainsKey method. It returns true if the key was found.*/
                string sssdd = Convert.ToString(obj).Substring(0, 5);
                //sssdd = Regex.Replace( "@"+sssdd , "@\\s*[\\d]+?_", "");
                if (!DifferentCarcategory.ContainsKey(sssdd))
                {
                    int totallength = Convert.ToString(obj).Length;
                    String age = Convert.ToString(obj).Substring(5, totallength - 5);
                    objage.Add(age);

                    DifferentCarcategory.Add(sssdd, 1);
                }

                else
                {
                    DifferentCarcategory[sssdd]++;

                    int totallength = Convert.ToString(obj).Length;
                    String age = Convert.ToString(obj).Substring(5, totallength - 5);
                    objage.Add(age);

                }
            }

            /* use foreach syntax and KeyValuePair generics in the foreach loop. With collections like Dictionary, 
             * we must always know the value types. With each KeyValuePair, there is a Key member and Value member.*/

            foreach (string kvp in DifferentCarcategory.Keys)
            {
                string strkv = kvp;
                if (Convert.ToInt32(DifferentCarcategory[kvp]) == 1)
                {
                    abc = abc + ";" + kvp;
                }
                else
                {
                    /*Regex.Replace static method with a string replacement.The "\d" metacharacter matches digit characters.  */
                    abc = abc + ";" + Regex.Replace(kvp, "^[\\d]*?_", Convert.ToString(DifferentCarcategory[kvp]) + "_");
                }
            }
            int spiltindex = 0;
            abc = abc.Trim().Trim(';');
            String sssnew = "";
            string xxxx = "";
            foreach (string kvp in objage)
            {
                if (!abc.Contains(";"))
                {
                   
                   abc = abc + kvp;
                }
                else
                {

                    sssnew = sssnew + abc.Split(';')[spiltindex] + kvp + ";";
                    
                }
                spiltindex++;
            }

           //this condition for no child case in this case only pass (-1) one time in string.
            xxxx = Convert.ToString(abc).Substring(3, 2);
            if (xxxx == "_0")
               abc = Convert.ToString(abc).Substring(0, 8);
            else
            
            abc = String.IsNullOrWhiteSpace(sssnew) ? abc : sssnew;
            abc = abc.Trim().Trim(';');
            return abc.ToString();

        }

        private void button1_Click(object sender, EventArgs e)
        {
            label1.Text = GroupOccupancy(textBox1.Text);
        }
    }
}

Thursday, June 25, 2015

Case statement in SQL



CASE is used to provide if-then-else type of logic to SQL.
This SQL case functionality provides the developer the ability to manipulate the presentation of the data without actually updating or changing the data as it exists inside the SQL table.

Solution:

SELECT a.supplierAmount,a.actionto
,case when action='DEBITTo' then a.toAmount else 00 end DEBIT
,case when action='CREDITTO' then a.toAmount else 00 end CREDIT,a.remark

 FROM [dbo].[tablexyz] a   

Input table Date :

supplier
supplierAmount
action
toamount
actionto
HB
150.0000
DebitTo
28.0000
Shimla
HB
200.0000
DebitTo
50.0000
Agra
MIKI
300.0000
CreditTo
150.0000
Agent
Hoojoozat
130.0000
CreditTo
57.0000
Agra
HotelRes
200.0000
CreditTo
100.0000
Agent
HB
40.0000
DebitTo
8.0000
Agent
Hoojoozat
80.0000
DebitTo
20.0000
Agra
GTA
308.0000
DebitTo
4.0000
Shimla
GTA
310.0000
DebitTo
2.0000
Agent

Output table Data:

supplierAmount
actionto
DEBIT
CREDIT
remark
150.0000
Shimla
28.0000
0.0000
booked by shimla
200.0000
Agra
50.0000
0.0000
booked by Agra
300.0000
Agent
0.0000
150.0000
back to agent
130.0000
Agra
0.0000
57.0000
DEBIT TO Agra
200.0000
Agent
0.0000
100.0000
TEST

How can calculate hours in sql query ?

I want that data from my table which have passed 48 hours from entry time.

Solution:


select name,lastname,address,city,country,mobile,dbo.udf_GetSimpleDate(entrydate) as entrydate from tab_cases where  (DATEDIFF(hh,getdate(),entrydate)=-48)


Using function for date format : udf_GetSimpleDate(entrydate)

CREATE FUNCTION [dbo].[udf_GetSimpleDate] ( @pDate    DATETIME )                  
RETURNS varchar(100)                  
AS                  
BEGIN                  
 declare @returndate varchar(100)            
 set  @returndate= right('00' + DATENAME(DD,@pDate),2) + '-' + right('00' + convert(varchar(2),datepart(MM,@pDate)),2) + '-' +DATENAME(YY, @pDate)                
 if datediff(d,@pDate,getdate())>10000      
 begin        
  set @returndate=''        
 end        
 return @returndate            
         
END  

Monday, November 10, 2014

What is the difference between a Clustered and Non Clustered Index?

What is the difference between a Clustered and Non Clustered Index?


A clustered index determines the order in which the rows of a table are stored on disk. If a table has a clustered index, then the rows of that table will be stored on disk in the same exact order as the clustered index.

An example will help clarify what we mean by that.


Suppose we have a table named Employee which has a column named EmployeeID. Let’s say we create a clustered index on the EmployeeID column. What happens when we create this clustered index? Well, all of the rows inside the Employee table will be physically – sorted (on the actual disk) – by the values inside the EmployeeID column. What does this accomplish? Well, it means that whenever a lookup/search for a sequence of EmployeeID’s is done using that clustered index, then the lookup will be much faster because of the fact that the sequence of employee ID’s are physically stored right next to each other on disk – that is the advantage with the clustered index. This is because the rows in the table are sorted in the exact same order as the clustered index, and the actual table data is stored in the leaf nodes of the clustered index.

Remember that an index is usually a tree data structure – and leaf nodes are the nodes that are at the very bottom of that tree. In other words, a clustered index basically contains the actual table level data in the index itself. This is very different from most other types of indexes as you can read about below.

When would using a clustered index make sense?


Let’s go through an example of when and why using a clustered index would actually make sense. Suppose we have a table named Owners and a table named Cars. This is what the simple schema would look like – with the column names in each table:


Owners
Owner_Name
Owner_Age
Cars
Car_Type
Owner_Name



Let’s assume that a given owner can have multiple cars – so a single Owner_Name can appear multiple times in the Cars table. Now, let’s say that we create a clustered index on the Owner_Name column in the Cars table. What does this accomplish for us? Well, because a clustered index is stored physically on the disk in the same order as the index, it would mean that a given Owner_Name would have all his/her car entries stored right next to each other on disk. In other words, if there is an owner named “Joe Smith” or “Raj Gupta”, then each owner would have all of his/her entries in the Cars table stored right next to each other on the disk.

When is using a clustered index an advantage?


What is the advantage of this? Well, suppose that there is a frequently run query which tries to find all of the cars belonging to a specific owner. With the clustered index, since all of the car entries belonging to a single owner would be right next to each other on disk, the query will run much faster than if the rows were being stored in some random order on the disk. And that is the key point to remember!

Why is it called a clustered index?


In our example, all of the car entries belonging to a single owner would be right next to each other on disk. This is the “clustering”, or grouping of similar values, which is referred to in the term “clustered” index.

Note that having an index on the Owner_Name would not necessarily be unique, because there are many people who share the same name. So, you might have to add another column to the clustered index to make sure that it’s unique.

What is a disadvantage to using a clustered index?


A disadvantage to using a clustered index is the fact that if a given row has a value updated in one of it’s (clustered) indexed columns what typically happens is that the database will have to move the entire row so that the table will continue to be sorted in the same order as the clustered index column. Consider our example above to clarify this. Suppose that someone named “Rafael Nadal” buys a car – let’s say it’s a Porsche – from “Roger Federer”. Remember that our clustered index is created on the Owner_Name column. This means that when we do a update to change the name on that row in the Cars table, the Owner_Name will be changed from “Roger Federer” to “Rafael Nadal”.

But, since a clustered index also tells the database in which order to physically store the rows on disk, when the Owner_Name is changed it will have to move an updated row so that it is still in the correct sorted order. So, now the row that used to belong to “Roger Federer” will have to be moved on disk so that it’s grouped (or clustered) with all the car entries that belong to “Rafael Nadal”. Clearly, this is a performance hit. This means that a simple UPDATE has turned into a DELETE and then an INSERT – just to maintain the order of the clustered index. For this exact reason, clustered indexes are usually created on primary keys or foreign keys, because of the fact that those values are less likely to change once they are already a part of a table.

A comparison of a non-clustered index with a clustered index with an example


As an example of a non-clustered index, let’s say that we have a non-clustered index on the EmployeeID column. A non-clustered index will store both the value of the EmployeeID AND a pointer to the row in the Employee table where that value is actually stored. But a clustered index, on the other hand, will actually store the row data for a particular EmployeeID – so if you are running a query that looks for an EmployeeID of 15, the data from other columns in the table like EmployeeName, EmployeeAddress, etc. will all actually be stored in the leaf node of the clustered index itself.



This means that with a non-clustered index extra work is required to follow that pointer to the row in the table to retrieve any other desired values, as opposed to a clustered index which can just access the row directly since it is being stored in the same order as the clustered index itself. So, reading from a clustered index is generally faster than reading from a non-clustered index.

A table can have multiple non-clustered indexes


A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes.

Why can a table have only one clustered index?


Because a clustered index determines the order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored? Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.

Summary of the differences between clustered and non-clustered indexes


Here’s a summary of the differences:


  • A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.
  • Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speeds up retrieval because the data lives close to each other on disk. Also, if data is accessed in the same order as the clustered index, the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index.
  • A clustered index can be a disadvantage because any time a change is made to a value of an indexed column, the subsequent possibility of re-sorting rows to maintain order is a definite performance hit.
  • A table can have multiple non-clustered indexes. But, a table can have only one clustered index.
  • Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the clustered index actually stores the row-level data in it’s leaf nodes.



Difference between Primary Key and Unique Clustered Index in SQL Server

Difference between Primary Key and Unique Clustered Index in SQL Server


The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.



You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.


A primary key must be unique, but that is just one of the its requirements. Another one would be that it cannot be null, which is not required of a unique constraint.

Also, while, in a way, unique constraints can be used as a poor man's primary keys, using them with IGNORE_DUP_KEY = ON is plainly wrong. That setting means that if you try to insert a duplicate, the insertion will fail silently.



They're definitely different. As mentioned in other answers:

  • Unique key is used just to test uniqueness and nothing else
  • Primary key acts as an identifier of the record.

Also, what's important is that the primary key is usually the clustered index. This means that the records are physically stored in the order defined by the primary key. This has a big consequences for performance.

Also, primary key is automatically included in all other indexes, so getting it doesn't require a record lookup, just reading the index is enough.

To sum up, always make sure you have a primary key on your tables. Indexes have a huge impact on performance and you want to make sure you get your indexes right.


Difference between Primary Key and Unique Clustered Index in SQL Server

Wednesday, July 23, 2014

How can use multiple aggregation in pivot query.

 Syntax for PIVOT.

PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

Below table we have Suppliercode and amount. We want change Row data in column means HP, CBS & GRN as column name also find total number of supplier code in table base of unique supplier.
Another output total amount base of unique supplier code.

ID
SupplierCode
Amount
1
HP
29
2
HP
30
3
CBS
40
4
CBS
50
5
HB
50

In below pivot query using multiple aggregation in pivot query for our output.
select * from cost
SELECT * FROM (
select CBS as CBS_Count,HB as HB_Count,HP as HP_Count from (
select count(SupplierCode)  as Code,SupplierCode  from cost
group by SupplierCode) as t
PIVOT (SUM(CODE) FOR SupplierCode IN([CBS],[HB],[HP])) AS PIB) T1,
(select * from
(
SELECT SupplierCode,SUM(Amount) as Total  from cost group by SupplierCode) as T2
pivot
(
SUM(Total) for SupplierCode in([CBS],[HB],[HP])) AS totalamount) T3





OUTPUT:

CBS_Count
HB_Count
HP_Count
CBS
HB
HP
2
1
2
90
50
59