Tuesday, August 12, 2014

cursor

cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row.
Declare Cursor
A cursor is declared by defining the SQL statement that returns a result set.
Open
A Cursor is opened and populated by executing the SQL statement defined by the cursor.
Fetch
When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
Close
After data manipulation, we should close the cursor explicitly.
Deallocate
Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

DECLARE @name VARCHAR(50) -- database name
DECLARE 
@path VARCHAR(256) -- path for backup files
DECLARE 
@fileName VARCHAR(256) -- filename for backup
DECLARE 
@fileDate VARCHAR(20) -- used for file name 

SET 
@path = 'C:\Backup\'

SELECT 
@fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE 
db_cursor CURSOR FOR
SELECT 
name 
FROM MASTER
.dbo.sysdatabases 
WHERE 
name NOT IN ('master','model','msdb','tempdb')

OPEN 
db_cursor   
FETCH 
NEXT FROM db_cursor INTO @name   

WHILE 
@@FETCH_STATUS = 0   
BEGIN   
       SET 
@fileName = @path + @name + '_' + @fileDate + '.BAK'
       
BACKUP DATABASE @name TO DISK = @fileName

       
FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE 
db_cursor   
DEALLOCATE 
db_cursor



SQL Server Cursor Components

Based on the example above, cursors include these components:
  • DECLARE statements - Declare variables used in the code block
  • SET\SELECT statements - Initialize the variables to a specific value
  • DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
    • NOTE - There are an equal number of variables in the DECLARE <cursor_name> CURSOR FOR statement as there are in the SELECT statement.  This could be 1 or many variables and associated columns.
  • OPEN statement - Open the cursor to begin data processing
  • FETCH NEXT statements - Assign the specific values from the cursor to the variables
    • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
  • WHILE statement - Condition to begin and continue data processing
  • BEGIN...END statement - Start and end of the code block
    • NOTE - Based on the data processing multiple BEGIN...END statements can be used
  • Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
  • CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
  • DEALLOCATE statement - Destroys the cursor


Syntax to Deallocate Cursor
Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:
 DEALLOCATE cursor_name --after deallocation it can't be reopen 
SQL SERVER – Simple Examples of Cursors
 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee 
 
 SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
1. END
2. CLOSE cur_emp
3. DEALLOCATE cur_emp
4. SET NOCOUNT OFF 

Message In Pop Up Window

ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record saved successfully');", true);

Java Script Validations

                                   :::::: Validations::::::


--------------------------------ONLY CHARECTERS---------------------ONLY CHARECTERS


function ValidateAlpha1(evt) {
        var keyCode = (evt.which) ? evt.which : event.keyCode
        if ((keyCode < 65 || keyCode > 90) && (keyCode < 97 || keyCode > 123) && (keyCode != 32) || ((document.getElementById("<%=TextBox5.ClientID%>").value == "") && (keyCode == 32))) {
            alert("Enter only Characters");
            return false;
        }
    }

---------------------------------ONLY NUMBERS--------------------------------------

    function isNumberKey(evt) {
        var charCode = (evt.which) ? evt.which : event.keyCode
        if (charCode > 31 && charCode != 45 && charCode != 40 && charCode != 41 && (charCode < 48 || charCode > 57)) {
            alert("Enter Numerics Only");
            return false;
        }
    }

---------------------------------------------ALPHANUMERIC--------------------------ALPHANUMERIC
    function alphanumeric_only(e) {
        var keycode;
        if (window.event)
            keycode = window.event.keyCode;
        else if (event)
            keycode = event.keyCode;
        else if (e)
            keycode = e.which;
        else
            return true;

        if ((keycode == 32) || (keycode >= 47 && keycode <= 57) || (keycode >= 65 && keycode <= 90) || (keycode >= 97 && keycode <= 122)) {
            return true;
        }
        else {
            return false;
        }
        return true;

--------------------------------------DROP DOWNLIST-----------------------------------------


Validation for Dropdownlist::::::

function validate() {
            if (document.getElementById('<%=DropDownList1.ClientID%>').selectedIndex == 0) {
                alert("Please select Class");
                return false;
            }
}

In Button Click:OnClientClick=" return validate()"


--------------------------------------DATE WITHOUT AJAX---------------------------------------



function checkDt(fld) {
            var mo, day, yr;
            var entry = fld.value;
            var reLong = /\b\d{1,2}[\/-]\d{1,2}[\/-]\d{4}\b/;
            var reShort = /\b\d{1,2}[\/-]\d{1,2}[\/-]\d{2}\b/;
            var valid = (reLong.test(entry)) || (reShort.test(entry));
            if (valid) {
               var delimChar = (entry.indexOf("/") != -1) ? "/" : "-";
                var delim1 = entry.indexOf(delimChar);
                var delim2 = entry.lastIndexOf(delimChar);
                mo = parseInt(entry.substring(0, delim1), 10);
                day = parseInt(entry.substring(delim1 + 1, delim2), 10);
                yr = parseInt(entry.substring(delim2 + 1), 10);
                // handle two-digit year
                if (yr < 100) {
                    var today = new Date();
                    // get current century floor (e.g., 2000)
                    var currCent = parseInt(today.getFullYear() / 100) * 100;
                    // two digits up to this year + 15 expands to current century
                    var threshold = (today.getFullYear() + 15) - currCent;
                   if (yr > threshold) {
                        yr += currCent - 100;
                    } else {
                        yr += currCent;
                    }
                }
                var testDate = new Date(yr, mo - 1, day);
                if (testDate.getDate() == day) {
                    if (testDate.getMonth() + 1 == mo) {
                        if (testDate.getFullYear() == yr) {
                            // fill field with database-friendly format
                            fld.value = mo + "/" + day + "/" + yr;
                            return true;
                        } else {
                            alert("Check the year entry.");                         
                        }
                    } else {
                    alert("Check the month entry.");                  
                    }
                } else {
               alert("Check the date entry.");               
                }
            } else {
            alert("Invalid date format. Enter as mm/dd/yyyy.");           
            }
            entry.focus();
            return false;

      
__________________________________________Mobile number starting with 7,8,9____________________________


Validation for Mobile number starting with 7,8,9::::

function validateInput13() {
            var txtCode = document.getElementById("<%=TextBox25.ClientID%>");
            var filter = /^[7-9][0-9]{9}$/;
            if (!filter.test(txtCode.value)) {
                alert('Enter correct mobile number starting with 7,8,9');
                txtCode.focus();
                return false;
            }
        }

_______________________________________Pin Code____________________________________________________


validation for Pin Code::: It contains 6 characters

 function validateInput10() {
            var txtCode = document.getElementById("<%=TextBox20.ClientID%>");
            var filter = /^[0-9]{6}$/;
            if (!filter.test(txtCode.value)) {
                alert('Enter 6 Characters');
                txtCode.focus();
                return false;
            }
        }

___________________________________________Vehicle Number_____________________________________________


Validation for Vehicle Number:::::like AP11AX1111

function Validpavan() {
            var TextBox4 = document.getElementById("<%=TextBox1.ClientID%>");
            var filter = /^[a-zA-z]{2}\d{2}[a-zA-Z]{2,}\d{4}$/;
            if (!filter.test(TextBox4.value)) {
                alert('Enter Correct Vehicle Number');
                TextBox4.focus();
                return false;
            }
        }

__________________________________________alphanumeric starting with alphabets________________________

validations for alphanumeric starting with alphabets:::

function Validpavan1() {
            var txtCode = document.getElementById("<%=TextBox4.ClientID%>");
            var filter = /^[a-zA-Z][a-zA-Z0-9 ]{2,25}$/;
        if (!filter.test(txtCode.value)) {            
            alert('Enter data in proper format starting with character');
            txtCode.focus();
                return false;
            }
        }

___________________________________________


Validation for integers::Allowing 1 t0 4 Characters

function ValidateMobileNumber() 
  {
            var txtCode = document.getElementById("<%=TextBox3.ClientID%>");
            var filter = /^[0-9]{1,4}$/
            if (!filter.test(txtCode.value))
            {
                alert('Invalid Number. Must Contain Maximum 4 Numbers');
                txtCode.focus();
                return false;
            }
  }
In Textbox:: onblur = "return ValidateMobileNumber()"
----------------------------------------------------------------------------------------------------
Validation for alphabets:: Range 1 to 15 Characters,not allowing spaces

function validateInput() 
      {
            var txtCode = document.getElementById("<%=TextBox1.ClientID%>");
            var filter = /^[a-zA-Z]{3,15}$/;
            if (!filter.test(txtCode.value)) {
                alert('Enter 3 to 15 Characters');
                txtCode.focus();
                return false;
            }

      }
In Textbox:: onblur = "return validateInput() "
------------------------------------------------------------------------

Validation for alphabets:: Range 1 to 15 Characters, allowing spaces,some special Characters

function validateInput() 
      {
            var txtCode = document.getElementById("<%=TextBox1.ClientID%>");
            var filter = /^[a-zA-Z .,;\/@#]{3,15}$/;
            if (!filter.test(txtCode.value)) {
                alert('Enter 3 to 15 Characters');
                txtCode.focus();
                return false;
            }

      }
In Textbox:: onblur = "return validateInput() "


Auto Increment Text Box

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            width: 35%;
            height: 162px;
        }
        .style2
        {
            font-size: x-large;
        }
        .style3
        {
            width: 131px;
        }
        .style4
        {
            width: 103px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <br />
        <br />
        <br />
        <table align="center" border="1" style="height: 178px; width: 323px">
            <tr>
                <td class="style2" colspan="2" style="text-align: center">
                    Student Information Register
                </td>
            </tr>
            <tr>
                <td class="style4">
                    Student roll no:-
                </td>
                <td class="style3">
                    <asp:TextBox ID="txtrollid" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style4">
                    Student name:-
                </td>
                <td class="style3">
                    <asp:TextBox ID="txtname" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style4">
                    Student age:-
                </td>
                <td class="style3">
                    <asp:TextBox ID="txtage" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style4">
                    Student mail id:-
                </td>
                <td class="style3">
                    <asp:TextBox ID="txtmailid" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="2" style="text-align: center">
                    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Style="font-weight: 700"
                        Text="Save" />
                </td>
            </tr>
            <tr>
                <td colspan="2" style="text-align: center">
                    <asp:Label ID="Label1" runat="server" Visible="False"></asp:Label>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient; //don't forget to add
using System.Data;// don't forget to add

public partial class Default2 : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(@"Data Source=naresh-PC\SQLEXPRESS;Initial Catalog=naresh;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
    {
        txtincr();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (con.State == 0)
        {
            con.Open();
        }
        string sqlstr = "insert into student values(" + Convert.ToInt32(txtrollid.Text) + ",'" + txtname.Text + "'," + Convert.ToInt32(txtage.Text) + ",'" + txtmailid.Text + "')";
        SqlCommand sqlcmd = new SqlCommand();
        sqlcmd.Connection = con;
        sqlcmd.CommandText = sqlstr;
        sqlcmd.ExecuteNonQuery();
        Label1.Visible = true;
        Label1.Text = "Record stored";
        cleartext();
        con.Close();
    }
    private void txtincr()
    {
        int a;
        if (txtrollid.Text == "")
        {
            if (con.State == 0)
            {
                con.Open();
            }
            string sqlstr = "select * from student";
            SqlDataAdapter sda = new SqlDataAdapter(sqlstr, con);
            DataSet dst = new DataSet();
            sda.Fill(dst);
            if (dst.Tables[0].Rows.Count != 0)
            {
                a = dst.Tables[0].Rows.Count;
                a = a + 1;
                txtrollid.Text = Convert.ToString(a);
                txtname.Focus();
            }
            else
            {
                txtrollid.Text = "1";
                txtname.Focus();
            }
            con.Close();
        }
    }

    private void cleartext()
    {
        txtrollid.Text = "";
        txtname.Text = "";
        txtage.Text = "";
        txtmailid.Text = "";
        txtincr();
    }
}


Thursday, August 7, 2014

Differences between Clustered and Non-Clustered Indexes

Both Clustered and Nonclustered Indexes have same physical structure in SQL Server. Both are stored as a B-Tree structure in SQL Server.
Below are some characteristics of Clustered Indexes and Nonclustered Indexes in SQL Server.
Clustered Index:
1. The leaf node of a Clustered Index contains data pages of the table on which it is created
2. Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key
3. If the table does not have Clustered Index it is referred to as a "Heap"
4. A Clustered Index always has Index Id of 1
5. A Table can have ONLY 1 Clustered Index
6. A Primary Key constraint creates a Clustered Index by default *
* A Primary Key constraint can also be enforced by Nonclustered Index, You can specify the index type while creating Primary Key

Nonclustered Index:
1. The leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row *
* When Clustered Index is not present leaf node points to Physical Location of the row this is referred to as RID. When a Clustered Index is present this points to Clustering Key (Key column on which Clustered Index is created)
2. Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.
3. A table may not have any Nonclustered Indexes
4. Nonclustered Indexes have Index Id > 1
5. Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created
6. A Unique Key constraint created a Nonclustered Index by default *
* A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key