New Learners: Understanding Normalization in SQL Server

What is Normalization?

When we want to store the some piece of information, we store it in database. Now storing a data in database mean, it should be stored properly so that while retrieving it, it should be easy. So we say store it in Normalized way. In short, Normalization can be defined as the process of organizing the data in database efficiently. The result of normalization is a logical database design and is called as Normal Form.

Why Normalization?

Goals of Normalization process are:

 -  It helps you to eliminate the redundant data from same table.

 -  It ensures the data dependencies between the tables are proper.

 -  A Normalized database design makes it easy to change in modification is required.

Advantages of Normalization

 - Data redundancy is removed

 - Faster update as redundant columns from a tables are removed.

 - Easy understanding of structure

 - Improvement in Index as be achieved

 - Long term maintainability of database get easier

Disadvantages of Normalization

 - Query to some extent get complicated

 - Performance may degrade due to multiple joins

 - Suppose I have some data with me, say

As you can see above, table is not properly managed

Normalization process is mainly divided in to stages which we call as Normal Form. Let’s talk about each Normal Form one by one with an example. Basically a database can be normalized into various normal forms such as-

1. First Normal Form (1NF)

2. Second Normal Form (2NF)

3. Third Normal Form (3NF)

4. Boyce Codd Normal Form (BCNF)

and Fourth Normal Form (4NF) and so on.

But today I would like to talk about only up to Boyce Codd Normal Form because Fourth Normal Form and others is rarely used in the database design.

First Normal Form (1NF)

It says,

 - Eliminate all repeating groups in individual table

 - One cell should contain only one data

 - Create a separate table for each set of related data

 - Identify each set of related data with a primary key

As you can see above,

 -  Our UnNormalized table does not have repeating group. So this is not applicable over here.

 - We have eliminated the comma separated values and shifted to another table Skill. Each employee in Employee table is related to Skill table through EmployeeId column (Note: It is not a foreign key relationship.  -  We are just replacing the comma separated values in each cell in to rows and creating separate table for it).

 -  Each cell is containing single value.

 -  Skills of table is identified with primary key.

Second Normal Form (2NF)

It says,

 - Tables should be in First Normal Form (1NF)

 - Eliminate partial primary key dependencies

 - Non key column must depend on the entire composite primary key and create separate tables for sets of       values that apply to multiple records.

 - Relate these tables with a foreign key

As you can see above,

 - We have eliminated the partial primary key dependency of EmployeeId and created a new table which will contain the relation of EmployeeId and SkillId. This both columns will be the respective foreign key for Employee and Skill table.

Third Normal Form (3NF)

- Table should be in Second Normal Form

- Eliminate fields that are not dependent on key i.e. - Eliminate Transitive Dependencies. Create a separate table for it.

- Let’s suppose I want to add new columns in Employee Table Manager and Project.

Now, adding this 2 column violates the third Normal Form because the non key column Project is dependent on another not key column i.e. - Manager.

So we will normalize the table by separating nondependent column to another table. This way we can achieve third Normal Form.

Boyce Codd Normal Form (BCNF)

It says,

 - BCNF is based on the concept of a determinant.

 - A determinant is any attribute (simple or composite) on which some other attribute is fully functionally dependent.

 - A relation is in BCNF is, and only if, every determinant is a candidate key.

 - The same as 3NF except in 3NF we only worry about non-key attributes

Note: If there is only one candidate key then 3NF and BCNF are the same.

Consider another column in below table say ProjectTechnology

- As shown above, each manager will be handling unique project, so we can say particular project is determined by particular manger where Manager and Project depicts a candidate key.

- If we delete the entry of Manager Ronnie from the table we lose not only information of Project BCF but also the fact that project was developed in Asp.Net technology. We cannot make the entry of the fact that BCF project was developed using Asp.Net.

So let’s break this into separate tables

Fourth Normal Form (4NF)

It says,

 - Database design should follow the 1NF, 2NF, 3NF and BCNF if possible

 - There must not be more than one multivalued dependencies other than a candidate key.

 - I hope you got it what actually is the process of Normalization.

Conclusion

Thus concluding it, Normalization is a process which is a must to design any database. Hope you like this article. Please share your comments whether it’s good or bad. Your comments are valuable to me to get better. 

Learners: Exceptions Handling in C#.Net

Exceptions:
--------------

An Exception is an object delivered by the Exception class. This Exception class is exposed by the System.Exception namespace. Exceptions are used to avoid system failure in an unexpected manner. Exception handles the failure situation that may arise. All the exceptions in the .NET Framework are derived from the System.Exception class.

To understand exception, we need to know two basic things:

1. Somebody sees a failure situation that happened and throws an exception by packing the valid information.

2. Somebody who knows that a failure may happen catches the exception thrown. We call it Exception Handler.

In other words, Exception objects that describe an error are created and then thrown with the throw keyword. The runtime then searches for the most compatible exception handler.

Programmers should throw exceptions when:

- The method cannot complete its defined functionality. For example, if a parameter to a method has an invalid value:

static void CopyObject(SampleClass original)
{
    if (original == null)
    {
        throw new System.ArgumentException("Parameter cannot be null", "original");
    }

}

This type of Exception is thrown as ArgumentNullException.

- An inappropriate call to an object is made, based on the object state. For example, trying to write to a read-only file. In cases where an object state does not allow an operation, throw an instance of InvalidOperationException or an object based on a derivation of this class. This is an example of a method that throws an InvalidOperationException object:

class ProgramLog

{

    System.IO.FileStream logFile = null;

    void OpenLog(System.IO.FileInfo fileName, System.IO.FileMode mode) {}

    void WriteLog()

    {

        if (!this.logFile.CanWrite)

        {

            throw new System.InvalidOperationException("Logfile cannot be read-only");

        }

        // Else write data to the log and return.

    }

}

- When an argument to a methord causes an exception. In this case, the original exception should be caught and an ArgumentException instance should be created. The original exception should be passed to the constructor of the ArgumentException as the InnerException parameter:

static int GetValueFromArray(int[] array, int index)

{

    try

    {

        return array[index];

    }

    catch (System.IndexOutOfRangeException ex)

    {

        System.ArgumentException argEx = new System.ArgumentException("Index is out of range", "index", ex);

        throw argEx;

    }

}

try and catch blocks

- When exceptions are thrown, you need to be able to handle them. This is done by implementing a try/catch block. Code that could throw an exception is put in the try block and exception handling code goes in the catch block. Below Example shows how to implement a try/catch block. Since an OpenRead() method could throw one of several exceptions, it is placed in the try block. If an exception is thrown, it will be caught in the catch block. The code in  below will print message and stack trace information out to the console if an exception is raised.

using System;

using System.IO;

class tryCatchDemo

{

    static void Main(string[] args)

    {

        try

        {

            File.OpenRead("NonExistentFile");

        }

        catch(Exception ex)

        {

            Console.WriteLine(ex.ToString());

        }

    }

}

finally block

The purpose of a finally statement is to ensure that the necessary cleanup of objects, usually objects that are holding external resources, happens immediately, even if an exception is thrown. One example of such cleanup is calling Close on a FileStream immediately after use instead of waiting for the object to be garbage collected by the common language runtime, as follows:

static void CodeWithoutCleanup()

{

    System.IO.FileStream file = null;

    System.IO.FileInfo fileInfo = new System.IO.FileInfo("C:\\file.txt");

    file = fileInfo.OpenWrite();

    file.WriteByte(0xF);

    file.Close();

}

To turn the previous code into a try-catch-finally statement, the cleanup code is separated from the working code, as follows.

static void CodeWithCleanup()

{

    System.IO.FileStream file = null;

    System.IO.FileInfo fileInfo = null;

    try

    {

        fileInfo = new System.IO.FileInfo("C:\\file.txt");

        file = fileInfo.OpenWrite();

        file.WriteByte(0xF);

    }

    catch(System.Exception e)

    {

        System.Console.WriteLine(e.Message);

    }

    finally

    {

        if (file != null)

        {

            file.Close();

        }

    }

}

Because an exception can happen at any time within the try block before the OpenWrite() call, or the OpenWrite() call itself could fail, we are not guaranteed that the file is open when we try to close it. The finally block adds a check to make sure the FileStream object is not null before calling the Close method. Without the null check, the finally block could throw its own NullReferenceException, but throwing exceptions in finally blocks should be avoided if possible.

Summary

This has been an introduction to handling exceptions. By now, you should have a good understanding of what an exception is. You can implement algorithms within try/catch blocks that handle exceptions. Additionally, you know how to clean up resources by implementing a finally block whose code is always executed before leaving a method.

Note:
 - Do you like this post, do you want to know more interesting concepts in .net, then subscribe to this blog.
 - You can also mail to dotnetcircle@gmail.com for any queries or issues in .net, we will try to solve and will post in this blog.

For Learners: How to show User Name in Welcome screen after user login in Asp.net MVC using Linq to Entities

Basic Knowledge in Asp.net MVC to show user name in Welcome screen

Create table with name "tblUsrs" for login users in UserDB in database.

Run below Sql Query in yOur database

Create database UserDB

USE UserDB
CREATE TABLE tblUsers
(
ID  INT PRIMARY KEY IDENTITY(1,1) ON,
USER_NAME VARCHAR(25),
USER_EMAIL VARCHAR(25),
USER_PASSWORD VARCHAR(25)
)

INSERT INTO tblUsers(USER_NAME,USER_EMAIL,USER_PASSWORD)
VALUES("SANJAY","abc@gmail.com","dotnetcircle")

Now, create a controller with name "AccountController" and Change "Index" Action Method to "Login" Action method.


Create a ADO Entity Data Model in Model Folder with name "UserDbEntities" and specify connection settings then Add table " tblUsers";

Add a class "UserModel" in Model class and write the below properties

public string USER_NAME {get;set;}
public string PASSWORD{get;set;}
public string EMAIL{get;set;}
public string Message{get;set;}

Add  View to "Login" action method with strong type model "UserModel " class.

To Design Login Form- write the below html design in your view

@using Html.BeginForm("Login","Account",FormMethod.POST)
{
<table>
<tr>
<td>
@Html.TextBoxFor(m=>m.EMAIL)
</td>
<td>@Html.TextBoxFor(m=>m.PASSWORD)</td>
</tr>
<tr><td><input type="submit" value="Submit" name="btn">
<table>
@Html.DisplayFor(m=>m.Message)
}

and Add another two Action method like below

[HTTPPOST]
public ActionMethod Login(UserModel model,string btn)
{
if(btn=="Submit")
{
 UserDbEntities entites=new UserDbEntities();
var userDetails=entites.tblUser.Where(m=>m.EMAIL==model.EMAIL && m.PASSWORD==model.PASSWORD).FirstOrDefault();

if(userDetails!=null)
{
model.USER_NAME=userDetails.USER_NAME;

//or we can use session like Session["username"]
return RedirecttoAction("Welcome","Account",model);
}
 model.Message="Invalid Username/Password";
}

return View(model);
}
public ActionResult Welcome(UserModel model)
{

return View(model);
}

Add View to Welcome with strong type class "UserModel"

Write the HTML below design

<div>
<h2>Welcome, @Html.DisplayFor(m=>m.USER_NAME")

or @Session["username"]// if we use session
</div>

then run it and type useername and password which in table. That's it.

Do you like this post, then subscribe to this blog for Interesting concepts.
Do you articles on .net , then mail to dotnetcircle@gmail.com to publish in the bog

Shortcut keys for Visual studio 2008,2010,2012,2013

Some of useful Coding Shortcut keys  while developing in Visual Studio..

Collapse Items
Ctrl+M+M		Collapse / un-collapse current preset area (e.g. method)
Ctrl+M+H		Collpase / hide current selection
Ctrl+M+O		Collapse declaration bodies
Ctrl+M+A		Collapse all
Ctrl+M+X		Uncollapse all
Ctrl+m, ctrl+T		Collapse Html tag

---

Edit Code
Ctrl+L		Delete current line or selection of lines to and add to clipboard
Ctrl+Shift+L		Delete current line or selection of lines
Ctrl+Delete		Delete word to right of cursor
Ctrl+Backspace		Delete word to left of cursor
Ctrl+Enter		Enter blank line above cursor
Ctrl+Shift+Enter		Enter blank line below cursor
Ctrl+Shift+U		Make uppercase
Ctrl+U		Make lowercase (reverse upercase)
Ctrl+K+C		Comment selected text
Ctrl+K+U		Uncomment selected text
Ctrl+K+\		Remove white space and tabs in selection or around current cursor position
Ctrl+K+D		Format document tostyle="width: 10px;" code formatting settings
Ctrl+K+F		Format selection to code formatting settings
Ctrl+Shift+Space		Display parameter required for selected method
Ctrl+Shift+8		Visualize whitespace (or press Ctrl+r, then Ctrl+w)
Ctrl+K+D		Format document to code formatting settings
Ctrl+K+F		Format selection to code formatting settings
Ctrl+Shift+T		Transpose word to right of cursor; makes b=a out of a=b if cursor was in front of a
Ctrl+T		Transpose character left and right of cursor; cursor between ab would make ba
Shift+Alt+T		Transpose line: Move line below cursor up and current line down.

---

IntelliSense and Code Helper
Ctrl+Space		Autocomplete word from completion list (or alt+right arrow)
Ctrl+Shift+Space		Show parameter info
Ctrl+F12		Display symbol definition
F12		Display symbol declaration
Ctrl+J		Open IntelliSense completion list

---

Build and Debug
F6	Build solution (or Ctrl+shift+b)
Ctrl+Alt+F7	Rebuild solution
Ctrl+Break	Cancel build process
Ctrl+\+E	Show error list
F9	Toggle breakpoint
Ctrl+B	Insert new function breakpoint
F5	Start debugging
F11	Debug / step into
F10	Debug / step over
Shift+F11	Debug / step out
Ctrl+F10	Debug / run to cursor
Ctrl+Alt+Q	Show Quickwatch window
Ctrl+Shift+F10	Set current statement to be the next executed
Alt+* (on numeric keyboard)	Show nexst statement
Ctrl+Alt+E	Show Exception dialog box
Ctrl+F11	Toggle between disassembly and user code view
Shift+F5	Stop Debugging
Ctrl+F5	Bypass debugger
Ctrl+Alt+P	Show attach to process window
Ctrl+Alt+break	Break all executing threads

---

Tool Windows
Ctrl+/		Put cursor in the find/command box in toolbar
Ctrl+K+B		Open code snippet manager window
Alt+F11		Open macro IDE window
Ctrl+K+W		Open bookmark window
Ctrl+Alt+K		Open call hierarchy window
Ctrl+Shift+C		Open class view window
Ctrl+Alt+A		Open Command window
Ctrl+Shift+O		Open Output window
Ctrl+Shift+E		Open Resource view window
Ctrl+Alt+S		Open Server explorer window
Ctrl+Shift+L		Open Solution explorer window
Shift+Esc		Close Find & Replace Window

Generating Random Password in Asp.Net

Sometimes we may required to generate random password to send the user after subscription , for that simply call  a static method as below:

public class static Password
{

publicstatic string CreateRandomPassword(int passwordLength)
{
 string allowedChars = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789!@$?_-";
 char[] chars = new char[passwordLength];
 Random rd = new Random();

 for (int i = 0; i < passwordLength; i++)
 {
  chars[i] = allowedChars[rd.Next(0, allowedChars.Length)];
 }

 return new string(chars);
}
}
call ans

string getPassword=Password.CreateRandomPassword(5); // Gets the Random password.

Do you like this blog posts?
Then Subscribe and Follow this blog to know latest Dot net concepts.
You can also send articles to dotnetcircle@gmail.com

About Triggers in SQL

Triggers
---------

Triggers are the stored sub programs that will automatically invoked based on specified event. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. 

Types of Triggers

----------------------

Based on Events specified for a trigger, triggers are classified into DDL Triggers and DML Triggers.

 - DDL Triggers are the Triggers that are created by specifying a DDL command as Event.

 - DML Triggers are the Triggers that are created by specifying DML command as Event.

DDL triggers are introduced in SQL Server 2005. In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.

Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.
We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.

DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.

DML triggers have the following three main purposes:

- Create procedural integrity constraints.

- Record Auditing information of the table.

- Allow insert,update & delete on complex views.

DML triggers are of two types:

1. After Triggers: 

- This Trigger fires after triggering action.  The INSERT,UPDATE,DELETE statements, causes after Inserting,Updating,Deleting Actions.

-  Mainly this are used for Maintaining Inserted, updated, Deleted actions in Audit tables.

Ex:

Table "tblEmployee"

CREATE TRIGGER trig_tblEmployee_Insert/Update/Delete

ON tblEmployee 

FOR INSERT /DELETE

AS 

BEGIN

DECLARE @id int

select @id=Id from inserted // or deleted // inserted and deleted are called Magic Tables, see below for Info,

insert into tblEmployeeAudit(Audit) values("New Employee with Id=cast(@Id as nvarchar(5) is Inserted")// or  Delete query.

END

// A Inserted Information is Added to "tblEmployeeAudit " table in Column "Audit".

Magic Tables:

Related to Triggers there are two table inserted and deleted which were called Magic Tables. This tables are accessible only withiin triggers only. The structure of this tables will be same as currently executing tables within triggers. Main Purpose of this tables is to provide access to new and old values of current row that is INSERTED,UPDATE,DELETED within the Trigger

2. Instead of Triggers.

-  This Triggers will be executed 'Instead of' Executing INSERT,UPDATE,DELETE statements.

-   Main purpose of Instead of Trigger is to allow insert,update,delete on complex views, In Oracle, "Instead of" Triggers can be created only on views, but on SQL Server, "Instead of" Triggers can be on Views as well as Tables.

Example: Create a Trigger on Dept Table on automatically convert Name and Location to Upper Case.

CREATE TRIGGER changecase

on Dept INSTEAD OF  as

BEGIN

Insert Dept select deptId,UPPER(dname),UPPER(location) from inserted

Difference between Triggers and Stored Procedures -

1. Stored procedures must be called by the user only whesreas triggers will be invoked automatically and use cannot invoke manually.
2. Stored procedure can take arguments whereas triggers cannot take arguments because they are automatically invoke.
2. Stored procedure can return a value within ouput parameter. But, trigger cannot return a value either without parameter or return statement

Validating Form using jQuery in .Net

This tutorial will show you how to setup front-end form validation using jQuery in just a few minutes. I’ve kept this tutorial very basic with simple clear instructions so that anyone can implement some validation on their webpage forms. There is a live demo and also a complete download package at the end of the post.

 User Registration Form:

Validating Fom

This is what your form will look like when a user tries to submit an empty form.



How to Implement :

Step 1: Design your form in HTML

<!-- HTML form for validation demo -->
<form action="" method="post" id="register-form" novalidate="novalidate">

    <h2>User Registration</h2>

    <div id="form-content">
        <fieldset>

            <div class="fieldgroup">
                <label for="firstname">First Name</label>
                <input type="text" name="firstname"/>
            </div>

            <div class="fieldgroup">
                <label for="lastname">Last Name</label>
                <input type="text" name="lastname"/>
            </div>

            <div class="fieldgroup">
                <label for="email">Email</label>
                <input type="text" name="email"/>
            </div>

            <div class="fieldgroup">
                <label for="password">Password</label>
                <input type="password" name="password"/>
            </div>

            <div class="fieldgroup">
                <p class="right">By clicking register you agree to our <a target="_blank" href="/policy">policy</a>.</p>
                <input type="submit" value="Register" class="submit"/>
            </div>

        </fieldset>
    </div>

        <div class="fieldgroup">
            <p>Already registered? <a href="/login">Sign in</a>.</p>
        </div>

</form>


Step 2: Install jquery plugins or we can add reference jquery plugins to View page.

//hosted by Microsoft Ajax CDN
<script src="http://ajax.aspnetcdn.com/ajax/jquery.validate/1.9/jquery.validate.min.js">

//hosted by Google API

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>

                                             (or)

You can Install jQuery Plugins and Add reference files here

(Ex:
   <script src="jquery.js"></script>
    <script src="jquery.validate.js"></script>     )

<script>


  $(document).ready(function(){
            //form validation rules
            $("#register-form").validate({
                rules: {
                    firstname: "required",
                    lastname: "required",
                    email: {
                        required: true,
                        email: true
                    },
                    password: {
                        required: true,
                        minlength: 5
                    },
                    agree: "required"
                },
                messages: {
                    firstname: "Please enter your firstname",
                    lastname: "Please enter your lastname",
                    password: {
                        required: "Please provide a password",
                        minlength: "Your password must be at least 5 characters long"
                    },
                    email: "Please enter a valid email address",
                    agree: "Please accept our policy"
                },
                submitHandler: function(form) {
                    form.submit();
                }
            });
        }
    }

   

});
</script>

Summary:

 - Do you like this post, want to know more interesting concepts, Just Subscribe to this Blog.

 - Do you have Interesting Concepts like this , then just mail to dotnetcircle@gmail.com to publish in this blog with your name.

 -