Gorav Handa: May 2011

Tuesday, May 10, 2011

C# - Spell Check

    public void SpellCheck(TextBox tBox, Label lLbl)
        {
            int iErrorCount = 0;
            Microsoft.Office.Interop.Word.Application app = new Microsoft.Office.Interop.Word.Application();
            if (tBox.Text.Length > 0)
            {
                app.Visible=false;
                // Setting these variables is comparable to passing null to the function.
                // This is necessary because the C# null cannot be passed by reference.
                object template=Missing.Value;
                object newTemplate=Missing.Value;
                object documentType=Missing.Value;
                object visible=true;
                object optional = Missing.Value;

                _Document doc = app.Documents.Add(ref template, ref newTemplate, ref documentType, ref visible);
                doc.Words.First.InsertBefore (tBox.Text );
                ProofreadingErrors we = doc.SpellingErrors;
                iErrorCount = we.Count;

                doc.CheckSpelling( ref optional, ref optional, ref optional, ref optional,
                    ref optional, ref optional, ref optional,
                    ref optional, ref optional, ref optional, ref optional, ref optional);

                if (iErrorCount == 0)
                    lLbl.Text = "Spelling is correct. No errors corrected ";
                else if (iErrorCount == 1)
                    lLbl.Text = "Spelling is correct now. 1 error corrected ";
                else
                    lLbl.Text = "Spelling is correct now. " + iErrorCount + " errors corrected ";
                object first=0;
                object last=doc.Characters.Count -1;

                tBox.Text = doc.Range(ref first, ref last).Text;
            }
            else
                lLbl.Text = "Textbox is empty";

            object saveChanges = false;
            object originalFormat = Missing.Value;
            object routeDocument = Missing.Value;
            app.Quit(ref saveChanges, ref originalFormat, ref routeDocument);
        }

Ethical Hacking

Prevent Dll to be decompiled or disassembled

By design .NET embeds rich Meta data inside the executable code using MSIL. Any one can easily decompile or disassemble your DLL using tools like ILDASM (owned by Microsoft) or Reflector for .NET which is a third party.

Secondly there are many third party tools which make this decompiling process a click away. So any one can easily look in to your assemblies and reverse engineer them back in to actual source code and understand your logic which can make it easy to crack your application.
The process by which you can stop this reverse engineering is using “obfuscation”.

It’s a technique which will foil the decompilers. There are many third parties (Dotfuscator, XenoCode, Demeanor for .NET, Crypto Obfuscator, etc…) which provide an obfuscation solution. When evaluating obfuscators, they are not all equal in quality. Most do not handle XAML well if at all. Also, some products are more sophisticated than others with basic obfuscation as well as string encryption. Cheaper is not always better.

Microsoft includes Dotfuscator Community Edition with Visual Studio.NET but it may not be sophisticated enough for your application.

Cloud Computing – SaaS+PaaS+IaaS+Daas

Trying to understand the complexities of Cloud-based services can be like navigating through a minefield, particularly if you’re an ISV looking to broaden your product offering. With that said, here’s a quick 101 in the basic concepts of cloud services, which should help clear the muddy waters a little.
SaaS – Software-as-a-Service is a model of software deployment whereby a provider licenses an application to customers for use as a service on demand. One example of SaaS is the Salesforce.com CRM application.
IaaS – Infrastructure-as-a-Service is the delivery of computer infrastructure (typically a platform virtualization environment) as a service. Rather than purchasing servers, software, data center space or network equipment, clients instead buy those resources as a fully outsourced service. One such example of this is the Amazon web services.
PaaS – Platform-as a-Service is the delivery of a computing platform and solution stack as a service. It facilitates the deployment of applications without the cost and complexity of buying and managing the underlying hardware and software layers. PaaS provides the facilities required to support the complete lifecycle of building and delivering web applications and services. An example of this would the GoogleApps.
DaaS – Desktop-as-a-Service enables users to use their desktops virtually from anywhere. Commonly known as “Desktop Virtualization”, this concept separates personal computer desktop environments from the physical machine through a client-server computing model. Nowadays, with the rise of SaaS and RIA (Rich Internet Applications) this method of usage is becoming obsolete.
So what does all this mean to you? Well, as a precursor to adopting the Cloud Computing model, you first need to have an application that is “Cloud ready”

10 excuses your boss doesn’t want to hear

1: I didn’t understand the assignment
2: The deadline was impossible
3: A valuable resource was not available
4: The requirements shifted
5: I have personal issues
6: I don’t have enough time
7: I don’t know what went wrong
8: We ran into blockages
9: The only copy of the work got destroyed
10: The dog ate my homework

BizTalk – How does one create a Singleton Orchestration?

1.    Create a correlation type with the property “BTS.ReceivePortName;” this will ensure a single instance of an orchestration, since all the messages come from a single receive location.

2.    Create a correlation set with the correlation type created in the previous step.

3.    In the first Receive Shape (Activate = true), set the property “Initializing Correlation Sets” as the correlation set (created in the previous step) and in the Second receive shape (Activate = false), set the property “Following Correlation Sets” as the correlation type (created in the previous step).

NOTE: Any promoted property which would be the same across all the messages can also be used as the correlation type property.

The first message that arrives at the Receive port creates a new instance of the Orchestration. All other messages would use the same Orchestration for processing.

A BizTalk Correlation would ensure that the messages which arrive into the Orchestration have at least one similar property. For example, the messages arrive from the same Receive Port or they have the same InstanceID or flag property. For a single Orchestration to exist, all the messages which arrive at the orchestration must have one common property. This common property of the messages must be encapsulated into a Correlation set.

The Correlation set created above shall be initialized on the receipt of the first message which creates the Singleton Orchestration, all other subsequent messages would use the same Correlation set and, therefore, the same Orchestration instance.

Cannot open database “db1″ requested by the login. The login failed for user ‘Machine\ASPNET’

Here’s a quick breakdown of some options:
1.) Use a user name and password in the connection string
2.) Have the application run under a different account that has permission to access the database
3.) Grant permission to the ASP.NET account
4.) Consider having ASP.NET impersonate the current user
I hope this information proves helpful.
———————————————–
Try executing a query like:
exec sp_grantlogin ‘MachineName\ASPNET’
———————————————–
<identity impersonate=”true”
userName=”domain\user”
password=”password” />

What is site minder ? what is use of siteminder?

It seems to be another 2nd party helper for security and ease of protecting important information.
SiteMinder provides policy-based authentication as well as single sign-on for all Web-based applications. SiteMinder is used in conjunction with IdentityMinder, which manages detailed user profiles, and TransactionMinder, which provides access to Web services.
I am guessing this would be used for a dataCenter or a Webhosting or ISP company, never seen it in action mind you.
Please help me out if anybody has any idea.

The Two Interceptors: HttpModule and HttpHandlers

Many times we want to implement pre-processing logic before a request hits the IIS resources. For instance you would like to apply security mechanism, URL rewriting, filter something in the request, etc. ASP.NET has provided two types of interception HttpModule and HttpHandler.

HttpHandler – The Extension Based Preprocessor

HttpHandler help us to inject pre-processing logic based on the extension of the file name requested. So when a page is requested, HttpHandler executes on the base of extension file names and on the base of verbs. We can also map one handler to multiple file extensions. For instance, when any client requests for file with extension ‘GIF’ and ‘JPEG’, handler3 pre-processing logic executes.

HttpModule – The Event Based Preprocessor

HttpModule is an event based methodology to inject pre-processing logic before any resource is requested. When any client sends a request for a resource, the request pipeline emits a lot of events.
Below is a detailed explanation of the events.

BeginRequest: Request has been started. If you need to do something at the beginning of a request (for example, display advertisement banners at the top of each page), synchronize this event.
AuthenticateRequest: If you want to plug in your own custom authentication scheme (for example, look up a user against a database to validate the password), build a module that synchronizes this event and authenticates the user in a way that you want to.
AuthorizeRequest: This event is used internally to implement authorization mechanisms (for example, to store your access control lists (ACLs) in a database rather than in the file system). Although you can override this event, there are not many good reasons to do so.
PreRequestHandlerExecute: This event occurs before the HTTP handler is executed.
PostRequestHandlerExecute: This event occurs after the HTTP handler is executed.
EndRequest: Request has been completed. You may want to build a debugging module that gathers information throughout the request and then writes the information to the page.

We can register these events with the HttpModules. So when the request pipe line executes depending on the event registered, the logic from the modules is processed.

Javascript | Calender

http://www.javascriptkit.com/script/script2/tengcalendar.shtml

Javascript | Clipboard

<html>
<head>
<title>Clipboard</title>
<script type=”text/javascript” language=”javascript”>
function SetClipBoard()
{
var email = document.getElementById(‘txtEmail’);
var emailValue = email.value;
window.clipboardData.setData(‘Text’ , emailValue );
}
function GetClipBoard()
{
var emailText = window.clipboardData.getData(‘Text’);
alert(emailText);
}
function ClearClipBoard()
{
window.clipboardData.clearData();
}
</script>
</head>
<body>
<form onreset=”return confirm(‘Do you want to reset the form?’)”>
<textarea name=”txtEmail” id=”txtEmail” rows=”4″ cols=”20″>Default Text</textarea>
<button onclick=”SetClipBoard();”>
Copy to Clipboard</button>
<button onclick=”GetClipBoard();”>
Get Clipboard Data</button>
<button onclick=”ClearClipBoard();”>
Clear Clipboard</button>
<input type=”reset” />
</form>
</body>
</html>

Javascript – Getting drop down values

<select id="ddlViewBy">
<option value="1">test1</option>
<option value="2" selected="selected">test2</option>
<option value="3">test3</option>
</select>

var e = document.getElementById("ddlViewBy");
var strUser = e.options[e.selectedIndex].value;

var e = document.getElementById("ddlViewBy");
var strUser = e.options[e.selectedIndex].text;

JavaScript – Persist state of page using Hidden field

JavaScript – window.close() isn’t working in mozilla firefox

Use the following code instead of “window.close();”
function winClose(){ window.top.opener = null; window.close(); }
or
function closeWindow() { window.open(”,’_parent’,”); window.close(); }
If it doesn’t works
Please set your firefox browser:
1.input “about:config ” to your firefox address bar and enter;
2.make sure your “dom.allow_scripts_to_close_windows” is true

JavaScript – Upload multiple images

JavaScript – encodeURIComponent() Function

Definition and Usage
The encodeURIComponent() function encodes a URI component.
This function encodes special characters. In addition, it encodes the following characters: , / ? : @ & = + $ #
Tip: Use the decodeURIComponent() function to decode an encoded URI component.
Syntax

encodeURIComponent(uri)

Parameter	Description
uri	Required. The URI to be encoded

Browser Support
The encodeURIComponent() function is supported in all major browsers.
Example Encode an URI:

The output of the code above will be:
http%3A%2F%2Fgorav.com%2Fmy%20test.asp%3Fname%3Dst%C3%A5le%26car%3Dsaab

C# – Copy array to collection

// One Array containing securities.
string[] securityArray = new string[5] { “Bond”, “Equity”, “Cash”, “IRS”, “PAS” };
// One Collection containing securities.
Collection<string> securityCollection = new Collection<string>();
securityCollection.Add(“Loan”);
securityCollection.Add(“Options”);
// List to help then adding.
List<string> helperList = new List<string>();
helperList.AddRange(securityCollection);
helperList.AddRange(securityArray);
// Updated list having collection items and array items added to it.
securityCollection = new Collection<string>(helperList);

C# – Convert collection to array

First Way:
static void Main()
{
Collection<int> collectionOfNumbers = new Collection<int>();
collectionOfNumbers.Add(1);
collectionOfNumbers.Add(2);
collectionOfNumbers.Add(3);
collectionOfNumbers.Add(4);
collectionOfNumbers.Add(5);
int[] arrayOfNumbers = ToGenericArray(collectionOfNumbers);
}
public static T[] ToGenericArray<T>(Collection<T> collection)
{
if (collection == null)
{
return new T[] { };
}
return new List<T>(collection).ToArray();
}
Second Way:
int[] numbers = new int[5];
Collection<int> collectionOfNumbers = new Collection<int>();
collectionOfNumbers.Add(1);
collectionOfNumbers.Add(2);
collectionOfNumbers.Add(3);
collectionOfNumbers.Add(4);
collectionOfNumbers.Add(5);
collectionOfNumbers.CopyTo(numbers, 0);

C# – Sorting collection of objects using comparer

class Program
{
static void Main(string[] args)
{
List<Product> products = new List<Product>();
Product p = new Product(3, “B”, 100);
products.Add(p);
p = new Product(2, “A”, 101);
products.Add(p);
p = new Product(1, “C”, 102);
products.Add(p);
products.Sort(Product.PriceComparison);
foreach (Product product in products)
{
Console.WriteLine(“Name:” + product.ProductName);
}
Console.ReadLine();
}
}
public class Product : IComparable<Product>
{
private int id;
private string prodName;
private decimal price;
public static Comparison<Product> PriceComparison = delegate(Product p1, Product p2)
{
return p1.price.CompareTo(p2.price);
};
public static Comparison<Product> IDComparison = delegate(Product p1, Product p2)
{
return p1.id.CompareTo(p2.id);
};
public int ProductID { get { return id; } set { id = value; } }
public string ProductName { get { return prodName; } set { prodName = value; } }
public decimal UnitPrice { get { return price; } set { price = value; } }
public Product(int id, string prodName, decimal price)
{
this.id = id;
this.prodName = prodName;
this.price = price;
}
#region IComparable<Product> Members
public int CompareTo(Product other)
{
return ProductName.CompareTo(other.ProductName);
}
#endregion
public override string ToString()
{
return string.Format(“Id: {0} Name: {1} Price: {2}”, id, prodName, price);
}
}

C# – System.DBNull.Value != null

public string CustomerName(int custId)
{
SqlCommand cmd = new SqlCommand(string.Format(“SELECT Name FROM Customers WHERE idCust = {0}”, custId), sqlConnectionToSchaakBondDB);
object result = cmd.ExecuteScalar();
if (result == null)
return “Customer not found”;
if (result == System.DBNull.Value)
return “Customer found but name is null”;
return (string) result;
}

Speed Test: Switch vs If-Else-If

Raw Results
This table shows the timings for the empty loop, if statement and switch statement for one billion iterations rounded to the nearest millisecond. The two columns show the results for the loops where matching of the comparison value occurs and for the non-matching tests.

	Matching	Non-Matching
Empty Loop	27.3s	5.0s
Switch Statement	43.0s	5.0s
If Statement	48.0s	5.1s

Adjusted Results
This second table shows the results for the two key test types, adjusted to remove the delay created by the looping mechanism.

	Matching	Non-Matching
Switch Statement	15.7s	0.0s
If Statement	20.7s	0.1s

Conclusion
The results show that the switch statement is faster to execute than the if-else-if ladder. This is due to the compiler’s ability to optimise the switch statement. In the case of the if-else-if ladder, the code must process each if statement in the order determined by the programmer. However, because each case within a switch statement does not rely on earlier cases, the compiler is able to re-order the testing in such a way as to provide the fastest execution.

Difference between List and Collection

List<T> inherits from Collection<T>. So they have a lot of the same functionality. List just extends it by adding members and methods to make things easier, like ForEach, TrimExcess, and ConvertAll. Collection<T> is used as the base class for all generic collections in the 2.0 Framework. You would almost always use List. The only time you would use Collection is to make your own class and use Collection as the base.

Extension method (.Net 3.5 and above)

Introduction
Extension method is new to C#.Its allow you to add new method to the existing class.
Description
1.Extension method allows you to add new method to the existing class without modifying the code, recompiling or modifying the original code.
2.Extension method are special kind of static method but they are called using instance method syntax. Their first parameter specify which type the method operate on, and the parameter is precede by “this” modifier.
3.Extension method are only in scope when you explicitly import the namespace into your source code with “using” directive.
For Example in string object there is no method call “Reverse()” function .But if you need to extend the string ….
Sample Code
C# Code
namespace ExtensionMethods
{
public static class ExtensionsClass
{
public static string Reverse(this String strReverse)
{
char[] charArray = new char[strReverse.Length];
int len = strReverse.Length – 1;
for (int i = 0; i <= len; i++)
{
charArray[i] = strReverse[len-i];
}
return new string(charArray);
}
}
}
C# Code
How to use?
string s = “Hello Extension Methods”;
string strReverse = s.Reverse ();
Here “s” is nothing but the parameter of Reverse() method.
Conclusion
Extension method is nothing but the Visitor pattern.
Visitor Pattern:-
Visitor pattern allows us to change the class structure without changing the actual class. Its way of separating the logic and algorithm from the current data structure. Due to this you can add new logic to the current data structure without altering the structure. Second you can alter the structure without touching the logic

C# – Generics and the Where Clause

public static T FindVisualParent<T>(DependencyObject obj)
where T : DependencyObject
{
if (obj == null)
return null;
T correctlyTyped = obj as T;
if (obj != null)
return correctlyTyped;
if (obj is Visual)
{
obj = VisualTreeHelper.GetParent(obj);
}
else
{
FrameworkContentElement fce = obj as FrameworkContentElement;
if (fce != null)
obj = fce.Parent;
else
throw new ArgumentException(
“Cannot Walk Parent Tree of ” + obj.GetType().ToString());
}
return FindVisualParent<T>(obj);
}

C# – Assigning multiple roles with Bitwise Operators

public partial class _Default : System.Web.UI.Page
{
public WebRoles Roles = WebRoles.Admin|WebRoles.Employee|WebRoles.Supervisor;
protected void Page_Load(object sender, EventArgs e)
{
// The user is employee and supervisor both.
WebRoles role = WebRoles.Employee | WebRoles.Supervisor;
bool isEmployee = (role & WebRoles.Employee) == WebRoles.Employee; // true
bool isSupervisor = (role & WebRoles.Supervisor) == WebRoles.Supervisor; // true
if(isEmployee && isSupervisor)
HttpContext.Current.Response.Write(“The Employee is Supervisor.”);
// This will check if the role of user is in the specified roles.
if (Roles != 0 && ((Roles & role) != role))
HttpContext.Current.Response.Write(“<BR>Invalid User.”);
}
}
[Serializable]
[Flags]
public enum WebRoles
{
Customer    = 1 << 0,
Employee    = 1 << 1,
Supervisor = 1 << 2,
Admin       = 2 << 3
}

typeof vs Type.GetType

typeof is resolved at compile time, so the type has to be either in
current assembly or one of the assemblies it reference to. Type.GetType on
the other hand, resolves the full qualified type name (namespace + assembly
information) at runtime, so it can be used to load types defined in any
assembly that can be located at runtime. Another note about Type.GetType is,
when called with a typename without the defining assembly, it only looks for
the type in calling assembly and mscorlib.dll.

C#: Using a System.Threading.Interlocked

I just saw some code which actually takes a lock to do a simple set operation. This is bad because taking locks are expensive and there is an easy alternative. The System.Threading.Interlocked class and its members can get the same job done and much faster.
I wrote the following two methods which increments a variable a million times. The first method does that by taking a lock and the other uses the Interlocked.Increment API.

static int IncrementorLock()
{
    int val = 0;
    object lockObject = new object();
    for (int i = 0; i < 1000000; ++i)
    {
        lock (lockObject)
        {
            val++;
        }
    }

    return val;
}

static int IncrementorInterlocked()
{
    int val = 0;
    for (int i = 0; i < 1000000; ++i)
    {
        System.Threading.Interlocked.Increment(ref val);
    }

    return val;
}

I then used the Visual Studio Team System Profiler (instrumented profiling) and got the following performance data.

Function Name	Elapsed Inclusive Time	Application Inclusive Time
LockedIncrement.Program.IncrementorInterlocked()	1,363.45	134.43
LockedIncrement.Program.IncrementorLock()	4,374.23	388.69

Even though this is a micro benchmark and uses a completely skewed scenario, it still drives the simple point that using the interlocked class is way faster.
The reason the interlocked version is faster is because instead of using .NET locks it directly calls Win32 apis like InterlockedIncrement which ensures atomic updation to variables at the OS scheduler level.
These Interlocked APIs complete depend on the support of the underlying OS. This is the reason you’d see that all the APIs are not available uniformly across all versions of .NET (e.g. there is no uniform coverage over WinCE and Xbox). While implementing these for the Nokia platform (S60) we are hitting some scenarios where there is no corresponding OS API.

C# – Problems using an XML Web service that returns a DataTable

Please go through this link:
http://support.microsoft.com/kb/306134

C# – Null-coalescing operator(??)

object obj = null;
string s = (obj ?? “”).ToString();

C# – Thread Safe List

public sealed class ThreadSafeList<T> : List<T> where T : struct
{
private readonly Object _syncRoot = new object();
public ThreadSafeList()
{
_syncRoot = new object();
}
public Object SyncRoot
{
get
{
return _syncRoot;
}
}
public new void Add(T item)
{
lock (_syncRoot)
{
base.Add(item);
}
}
}

Oracle – Important Queries

– List of tables in DB
SELECT * FROM TABS
SELECT * FROM USER_TABLES
SELECT * FROM user_all_tables;
SELECT * FROM USER_objects where object_type = ‘TABLE’;
SELECT * FROM ALL_ALL_TABLES;
SELECT * FROM DBA_TABLES; — Need Privileges to run this
SELECT * FROM DICT;
SELECT * FROM DICTIONARY;
– List of table spaces
SELECT * FROM USER_TABLESPACES
– List of previlages on tabel
SELECT * FROM TABLE_PRIVILEGES;
SELECT * FROM table_privilege_map;
– List of constraints
SELECT * FROM user_constraints;
– Get Primary key
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = ‘TABLE_NAME’
AND cons.constraint_type = ‘P’
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
– Get Schema names
SELECT * FROM ALL_USERS
– Get segment assigned
– All table columns
SELECT * FROM ALL_TAB_COLS

Oracle – Wildcards

Demo Table And Records
Demo Data	CREATE TABLE wildcard ( test VARCHAR2(25)); INSERT INTO wildcard VALUES (’23%45′); INSERT INTO wildcard VALUES (’2345′); INSERT INTO wildcard VALUES (’2365′); INSERT INTO wildcard VALUES (‘Daniel Morgan’); INSERT INTO wildcard VALUES (‘Washington’); COMMIT;

Wildcard Characters
Single Character	_ (underscore)
	SELECT * FROM wildcard WHERE test LIKE ’23_5′;
Multiple Characters
	SELECT * FROM wildcard WHERE test LIKE ’2%5′;
Mixed Single And Multiple Characters	SELECT * FROM wildcard WHERE test LIKE ‘_3%5′;
Complex Statement	SELECT * FROM wildcard WHERE test LIKE ‘%a%a %‘;

Querying Records Containing Wildcards
Find Records Containing Percentage Sign	ESCAPE ‘<escape_character>’
	SELECT * FROM wildcard WHERE test LIKE ‘%\%%‘ ESCAPE ‘\’;

INSTEAD-OF trigger

A trigger is a database object similar to a stored procedure that executes in response to certain actions that occur in your database environment. SQL Server 2005 is packaged with three flavors of trigger objects: AFTER, data definition language (DDL), and INSTEAD-OF.
AFTER triggers are stored procedures that occur after a data manipulation statement has occurred in the database, such as a delete statement. DDL triggers are new to SQL Server 2005, and allow you to respond to object definition level events that occur in the database engine, such as a DROP TABLE statement. INSTEAD-OF triggers are objects that will execute instead of data manipulation statements in the database engine. For example, attaching an INSTEAD-OF INSERT trigger to a table will tell the database engine to execute that trigger instead of executing the statement that would insert values into that table.

Why use an INSTEAD-OF trigger?

INSTEAD-OF triggers are very powerful objects in SQL Server. They allow the developer to divert the database engine to do something different than what the user is trying to do. An example of this would be to add an INSTEAD-OF trigger to any table in your database that rolls back transactions on tables that you do not want modified. You must be careful when using this method because the INSTEAD-OF trigger will need to be disabled before any specified modifications can occur to this table.
Perhaps a more functional reason to use an INSTEAD-OF trigger would be to add the trigger to a view. Adding an INSTEAD-OF trigger to a view essentially allows you to create updateable views. Updateable views allow you to totally abstract your database schema so you can potentially design a system in such a way that your database developers do not have to worry about the OLTP database schema and instead rely upon a standard set of views for data modifications.

An example

To better illustrate the idea of an updateable view, it’s always great to use an example. In this example, I refer to a fictitious scenario that includes a Products lookup table and a Purchases table, which records those instances where products are purchased. Shown below Listing A contains the script to create these tables. After running the script to create the tables I will use in the example, I will run the script in Listing B to insert some data into the tables.
Now that the sample tables have data in them, I can create a view to join these tables and present the data in a meaningful way. Check out Listing C.
This is a pretty typical production-level view. It joins two tables in the database structure, which greatly simplifies data retrieval. However, the data abstraction provided is not the only advantage of using views. Attaching INSTEAD-OF trigger(s) to this view allows me to modify the underlying tables, so that I may never need to modify the data in the underlying tables directly. I’ll use the script in Listing D to create an INSTEAD-OF trigger on the vw_ProductPurchases view.
Notice that in the trigger declaration I specify the INSTEAD OF clause. Triggers created in SQL Server are AFTER triggers by default, so I must specify the INSTEAD OF clause in the trigger definition.
The first statement in the trigger is a “check” statement. Here I am checking the INSERTED table to ensure that the ProductID is present, and that either the PurchasePrice or the ProductPrice has been provided.
If the necessary data has been inserted into the view via an INSERT statement, the trigger will insert the specified values into the underlying data table. This is what a sample INSERT statement into the view would look like.

INSERT INTO vw_ProductPurchases(ProductID, PurchasePrice) VALUES(1, 700)

This INSERT statement provides a valid ProductID and PurchasePrice, which means a new record will be inserted into the Purchases table.

Conclusion

With a bit of imagination, it is easy to see the power and flexibility provided by INSTEAD-OF triggers. If your system is not extremely large, using a system of views to abstract your underlying database schema can provide a great way to shield your database programmers from modifying the data in the underlying tables directly.
Listing A:
CREATE TABLE Products
(
ProductID SMALLINT IDENTITY(1,1) PRIMARY KEY,
Description VARCHAR(75),
Price MONEY NOT NULL
)
GO
CREATE TABLE Purchases
(
PurchaseID SMALLINT IDENTITY(1,1) PRIMARY KEY,
ProductID SMALLINT REFERENCES Products(ProductID),
PurchasePrice MONEY NOT NULL,
PurchaseDate SMALLDATETIME DEFAULT(GETDATE())
)
Listing B:

INSERT INTO Products(Description, Price) VALUES('Television',500)

INSERT INTO Products(Description, Price) VALUES('VCR',100)

INSERT INTO Products(Description, Price) VALUES('DVD_Player',125)

INSERT INTO Products(Description, Price) VALUES('Alarm_Clock',40)

INSERT INTO Products(Description, Price) VALUES('Camera',325)

INSERT INTO Products(Description, Price) VALUES('Projector',1500)

INSERT INTO Products(Description, Price) VALUES('XBox',400)

GO

INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(1, 500)

INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(5, 325)

INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(1, 525)

GO

Listing C:
CREATE VIEW vw_ProductPurchases

AS
      SELECT
            pr.ProductID,
            pr.Description,
            pr.Price AS ProductPrice,
            pu.PurchasePrice,
            pu.PurchaseDate  
      FROM
            Products pr
            INNER JOIN Purchases pu ON pr.ProductID = pu.ProductID

GOListing D:
CREATE TRIGGER tr_vwProductPurchases ON vw_ProductPurchases

INSTEAD OF INSERT

AS

BEGIN
      IF EXISTS
      (
            SELECT TOP 1 *
            FROM INSERTED
            WHERE
                  ProductID IS NOT NULL AND
                  ISNULL(COALESCE(PurchasePrice, ProductPrice),0)>0
      )
      BEGIN
            INSERT INTO Purchases
            (
                  ProductID, PurchasePrice, PurchaseDate
            )
            SELECT

i.ProductID, COALESCE(PurchasePrice, ProductPrice), ISNULL(PurchaseDate, GETDATE())
            FROM INSERTED i  
      END
      ELSE
      BEGIN
            PRINT 'Adequate data not provided.'
      END

END

Oracle – Connect By Prior

– Populate data
set feedback off
create table test_connect_by (
parent number,
child number,
constraint uq_tcb unique (child)
);
insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);
insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);
insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);
insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);
insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);
insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);
insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);
– Main Query
SELECT LPAD(‘ ‘,2*(level-1)) || TO_CHAR(child) s
FROM test_connect_by
START WITH parent IS NULL
CONNECT BY PRIOR child = parent;

Database – Common Key Terminology

Key. A key is one or more data attributes that uniquely identify an entity. In a physical database a key would be formed of one or more table columns whose value(s) uniquely identifies a row within a relational table.
Composite key. A key that is composed of two or more attributes.
Natural key. A key that is formed of attributes that already exist in the real world. For example, U.S. citizens are issued a Social Security Number (SSN) that is unique to them (this isn’t guaranteed to be true, but it’s pretty darn close in practice). SSN could be used as a natural key, assuming privacy laws allow it, for a Person entity (assuming the scope of your organization is limited to the U.S.).
Surrogate key. A key with no business meaning.
Candidate key. An entity type in a logical data model will have zero or more candidate keys, also referred to simply as unique identifiers (note: some people don’t believe in identifying candidate keys in LDMs, so there’s no hard and fast rules). For example, if we only interact with American citizens then SSN is one candidate key for the Person entity type and the combination of name and phone number (assuming the combination is unique) is potentially a second candidate key. Both of these keys are called candidate keys because they are candidates to be chosen as the primary key, an alternate key or perhaps not even a key at all within a physical data model.
Primary key. The preferred key for an entity type.
Alternate key. Also known as a secondary key, is another unique identifier of a row within a table.
Foreign key. One or more attributes in an entity type that represents a key, either primary or secondary, in another entity type.

SQL Server – Best practices (index optimization,Database Performance)

Do you have a maintenance window to perform database reindexing?
Have you ever performed full set of re-organize and re-index process on a bigger database?
Do you know there are best practices to deploy in this regard?
On large database systems, with large numbers of insert and update commands, the problem of index fragmentation is one of the main causes of performance degradation and a proper index optimization strategy is a must.
Also following are set of counters you need to keep in mind:
· Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on column which are used in JOIN Condition.
· Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of all the insert, updates, deletes to the table.
· Smaller Index Key gives better performance than Index key which covers large data or many columns
· Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
· Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.
Also there are few basic guidelines when you need to create a database from scratch such as,
· Design a normalized database.
· Optimize a database design by denormalizing.
· Optimize data storage.
· Manage concurrency – by selecting the appropriate transaction isolation level.
· Select a locking granularity level.
· Optimize and tune queries for performance.
· Optimize an indexing strategy.
· Decide when cursors are appropriate.
· Identify and resolve performance-limiting problems.
· Be familiar with index structures and index utilization. Specifically, they must understand the interaction between non-clustered indexes, clustered indexes and heaps. A must know why a covering index can improve performance.
· Be able to design a database to third normal form (3NF) and know the trade offs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas.

SQL Server-Find nth highest salary

First Method:
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Second Method:
Select * from ( select rank() over (partition by sal order by sal desc NULLS LAST) rn from tablename)
where rn = &N;
Third Method:
Find 1, 2,3 and nth highest salary
select top 1 salary from (
select distinct top n salary from tab order by salary desc ) a
order by salary asc
Fourth Method(Oracle):
select level, max(‘col_name’) from my_table
where level = ‘&n’
connect by prior (‘col_name’) > ‘col_name’)
group by level;

SQL Server – How to remove duplicate rows

IF OBJECT_ID(‘EmployeeDetails’) IS NOT NULL
DROP TABLE EmployeeDetails
CREATE TABLE [dbo].[EmployeeDetails]
(
[Employee] [varchar](10) NULL,
[JoiningDate] [datetime] NULL,
[DeptID] [int] NULL
)
GO
INSERT INTO EmployeeDetails(Employee, JoiningDate, DeptID)
SELECT ‘Gorav’,’1919-03-18 ‘,1008
UNION ALL
SELECT ‘Maneesh’,’1927-03-18 ‘,91
UNION ALL
SELECT ‘Anant’,’1927-04-01 ‘,139
UNION ALL
SELECT ‘Gorav’,’1919-03-18 ‘,1008
UNION ALL
SELECT ‘Maneesh’,’1927-03-25 ‘,92
UNION ALL
SELECT ‘Anant’,’1927-03-25 ‘,108
UNION ALL
SELECT ‘Gorav’,’1919-04-01 ‘,150
UNION ALL
SELECT ‘Maneesh’,’1927-04-01 ‘, 123
UNION ALL
SELECT ‘Anant’,’1927-04-01 ‘, 139
UNION ALL
SELECT ‘Gorav’,’1919-04-08 ‘, 168
– query to check duplicate rows
SELECT Employee, JoiningDate, DeptID,Ranking = row_number() OVER(PARTITION BY Employee, JoiningDate, DeptID ORDER BY NEWID() ASC)
FROM EmployeeDetails
– query to delete duplicate rows
WITH RemoveDuplicate(Employee, JoiningDate, DeptID, Ranking)
AS(
SELECT Employee, JoiningDate, DeptID,Ranking = row_number() OVER(PARTITION BY Employee, JoiningDate, DeptID ORDER BY NEWID() ASC)
FROM EmployeeDetails
)
DELETE FROM RemoveDuplicate WHERE Ranking > 1

Database best practices

1. Store relevant and necessary information in the database instead of application structure or array.
2. Use normalized tables in the database. Small multiple tables are usually better than one large table.
3. If you use any enumerated field create look up for it in the database itself to maintain database integrity.
4. Keep primary key of lesser chars or integer. It is easier to process small width keys.
5. Store image paths or URLs in database instead of images. It has less overhead.
6. Use proper database types for the fields. If StartDate is database filed use datetime as datatypes instead of VARCHAR(20).
7. Specify column names instead of using * in SELECT statement.
8. Use LIKE clause properly. If you are looking for exact match use “=” instead.
9. Write SQL keyword in capital letters for readability purpose.
10. Using JOIN is better for performance then using sub queries or nested queries.
11. Use stored procedures. They are faster and help in maintainability as well security of the database.
12. Use comments for readability as well guidelines for next developer who comes to modify the same code. Proper documentation of application will also aid help too.
13. Proper indexing will improve the speed of operations in the database.
14. Make sure to test it any of the database programming as well administrative changes.
15. SELECT count(1) from Table1 will be faster then SELECT count(*) from table1

Same SQL Query in 4 different ways

1. With Sub Query
SELECT Name, Region FROM bbc
WHERE Region In(
SELECT Region FROM bbc WHERE Name=’India’ OR Name=’Iran’)
2. With Exists clause
SELECT Name, Region FROM bbc b
WHERE EXISTS(SELECT Region FROM bbc WHERE Region = b.Region
AND (Name=’India’ OR Name=’Iran’))
3. With Old style joins
SELECT b.Name, b.Region FROM bbc b, bbc c
WHERE b.Region = c.Region
AND ((c.Name=’India’) OR (c.Name=’Iran’))
4. With ANSI Joins
SELECT b.Name, b.Region FROM bbc b
INNER JOIN bbc c
ON b.Region = c.Region
AND ((c.Name=’India’) OR (c.Name=’Iran’))

Tuesday, May 10, 2011

Why use an INSTEAD-OF trigger?

An example

Conclusion

Followers

About Me

Blog Archive