Different Ways to use Insert Command in Sqlserver

--Simple Insert Command

Insert into Employee values('1','Rajesh');
Insert into Employee values('2','Harish');

--Insert command using Column name which specifies in which column we are going to insert  the data

Insert into Employee (emp_id,emp_name)values('3','lokesh');
Insert into Employee (emp_id,emp_name)values('4','siva');

--Insert command in which we select records from another table

Insert Employee1 select * from Employee;
Insert into Employee1 select * from Employee;

--Insert miltiple records in single Insert Command

Insert into Employee (emp_id,emp_name)
select  1,'Rajesh'
union all
select  2,'Harish';

Insert into Employee (emp_id,emp_name)values
('3','lokesh'),
('4','siva');

Difference between Delete and Truncate Command

Although the Delete and Truncate Commands logically does the same work of deleting the rows from the table but still there are many differences in their working. These differences are given below:-

1) Delete command maintained the logs files of each deleted row but Truncate command do not maintain the logs files for each deleted row but maintains the record for deallocation of the datapages in the log files.The deallocation of the datafiles means that the data rows still exists in the data pages but the extends have marked as empty for reuse.

2) Truncate command is much faster than delete command.

3) You can use Where clause in case of Delete command to delete a particular row but in case of Truncate command you have to delete the data from all the row since Where clause is not work with Truncate command.

4) Triggers is fired in case of Delete command only and they are not fired when Truncate command is used.

5) Truncate command resets the Identity property to its initial value whereas Delete command do not resets the Identity property of the column.

6) Delete is a DML command and Truncate is a DDL command

ACID Rules in Sql Server

ACID Rules

ACID Rules:- It is a concept for evaluation of databases and their architecture.

A:- (Atomicity) – Atomicity  states the principle of  All or none. This means that either all the SQL Statements within the transaction will be executed or no Sql statement will be executed.

C:- Consistency:- Consistency states that only valid data will be written into the database. That means if any Sql transaction violets the rules or constraints define on the database to make it consistent, then all the statements within the transaction will be Rollback. Or in other words the whole transaction will be rolled back.

I:- Isolation :- The Isolation state that if multiple transaction try to work on the database at the same time,then these transaction will not interfere with each other. It means the second transaction will not work unless and until the previous transaction completes its work and the work is commited.

D:- Durability:- Durability states that once the transaction is committed, then the database should not be lost in case of Software failures or hardware failures. Durability is maintained with the help of the  database backups and transaction logs

Date formatting

        format code                                              output
default                                            2011-10-20 06:59:22.590
convert(varchar,getDate(),100)    Oct 20 2011 6:59AM
convert(varchar,getDate(),101)         10/20/2011
convert(varchar,getDate(),102)   2011.10.20
convert(varchar,getDate(),103)  20/10/2011
convert(varchar,getDate(),104)         20.10.2011
convert(varchar,getDate(),105)          20-10-2011
convert(varchar,getDate(),106)          20 Oct 2011
convert(varchar,getDate(),107)         Oct 20, 2011
convert(varchar,getDate(),108)         06:59:00
convert(varchar,getDate(),109)   Oct 20 2011 6:59:00:553AM
convert(varchar,getDate(),110)         10-20-2011
convert(varchar,getDate(),111)         2011/10/20
convert(varchar,getDate(),112)         20111020
convert(varchar,getDate(),113)  20 Oct 2011 06:59:00:553
convert(varchar,getDate(),114)  06:59:00:553
convert(varchar,getDate(),120)         2011-10-20 06:59:00
convert(varchar,getDate(),121)         2011-10-20 06:59:00.553
convert(varchar,getDate(),126)   2011-10-20T06:59:00.553
convert(varchar,getDate(),127)          2011-10-20T06:59:00.553
convert(varchar,getDate(),130)          23 ?? ?????? 1432 6:59:00:553
convert(varchar,getDate(),131)         23/11/1432 6:59:00:553AM

sql query with tag

select c.CarID, c.SellerID, c.Car_Name,'Car For :'+ c.Car_For +'<br /> Condition :'+ c.Condition as cardetail, c.Price,'Currency Type :'+ c.Currency_Type +'<br /> Car Type :'+ c.[Type] +'<br /> Car Make :'+ c.Make  as car_type,
 'Registered Year :'+ c.RegistrationYear +'<br /> Milage :'+ c.Milage+'<br /> Terminal :'+ c.Terminal+'<br />Engine Capacity :'+ c.Engine_Capacity+'<br /> Colour :'+c.Colour as  CarData, c.ColourTye,  c.Description,
 c.datetime, c.Region, c.Sales_Type
 from dbo.Cars_Details_Master c where SellerID=@id

Substring() in SqlServer

SubString function:

This function is used to get the specified length of string based on specification of start position and required length of string. Generally SubString function will take 3 arguments.

Declaration of SubString function:

SUBSTRING(string, startIndex, length)

In this function

1st Argument specifies the string value

2nd Argument specifies the start position of string from where part of string begins

3rd Argument specifies number of characters to be returned from the specified string

ex:

SELECT OUT = SUBSTRING('abcdefgh', 2, 3)
The output will be "bcd".

SELECT SUBSTRING('STRING FUNCTION', 1, 6)
Result : STRING
SELECT SUBSTRING('STRING FUNCTION', 8, 8)
 Result : FUNCTION

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



substring();

substring function return some part of string from the string

Syntax:

SUBSTRING(expression,start,length)


DECLARE @ImportDate VARCHAR(8) = '10122012'

SELECT CAST(SUBSTRING(@ImportDate, 5, 4) + SUBSTRING(@ImportDate, 3, 2) + SUBSTRING(@ImportDate, 1, 2) AS DATETIME)
       
   Result:
       
2012-12-10 00:00:00.000


you also write queries like this.....


SET DATEFORMAT DMY
DECLARE @ImportDate VARCHAR(8) = '10122012'

SELECT CAST(STUFF(STUFF(@ImportDate, 5, 0, '-'), 3, 0, '-') AS DATETIME)

SET DATEFORMAT MDY

Result:

2012-12-10 00:00:00.000


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

SET DATEFORMAT DMY

SELECT CAST(STUFF(STUFF('10122012', 5, 0, '-'), 3, 0, '-') AS DATETIME)

SET DATEFORMAT MDY


Result:2012-12-10 00:00:00.000
----------------------------------------------------------



Substring syntax


Substring (expression,startpoint,lenght)

99 represents thee Length of the expression
99 reflects the potential length of a string between the 1st comma and the second comma


declare @string varchar(20)
set @string='A,B,C'

Select substring( SUBSTRING(@string,charindex(',',@string)+1,99),0,charindex(',',SUBSTRING(@string,charindex(',',@string)+1,99)))

Result:
B

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

create table col (col varchar(255))

insert into col
 select 'A,B,C'
 union all
select 'AA,BBBB,CCC'
union all
select 'AAA,BBBBBBBBBBBBBBBBBBBBBBBBBBBBB,CCC'
union all
select 'AAAAAAA,BBBBBBBB,CCC'

Select substring( SUBSTRING(col,charindex(',',col)+1,99),0,charindex(',',SUBSTRING(col,charindex(',',col)+1,99))) from col

drop table col

Result:
B
BBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBB

Polymorphism

Definition 1: The ability to take more than one form.
Definition 2: Same operation may work in different way on other classes.

Basically polimorphism are two types:

1. Compile time polymorphism: for example method overloading
2. Run time polymorphism: for example method overriding

Compile time polymorphism: Method Overloading

In this, calss is this having same method name and different arguments. This is check at compile time only. So we can call it as compile time polimorphism




class Compile
{
public string method(string first)
{
     return first;
}

public string method()
{
      return "no argument";}
}
}


Run Time Polymorphism:Overriding

Method overriding occurs&nbsp;When the child class is having same method name with arguments count and type by one of its superclass

class BaseClass
{
    public virtual void OverrideExample()
    {
        Console.WriteLine("base method");
    }
}

class DerivedClass : BaseClass
{
    public override void OverrideExample()
    {
        Console.WriteLine("derived method");
    }
}

class Test
{
    static void Main(string[] args)
    {
        BaseClass a;
        DerivedClass b;

        a = new BaseClass();
        b = new DerivedClass();
        a.OverrideExample();  // output --> "base method"
        b.OverrideExample();  // output --> "derived method"

        a = new DerivedClass();
        a.OverrideExample();  // output --> "derived method"
        Console.ReadLine();
    }
}

Explanation and Comparison of NULLIF and ISNULL in SQLSERVER

Explanation of NULLIF
Syntax:
NULLIF ( expression , expression )

Returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

Following is good example of NULLIF and CASE from BOL:
USE AdventureWorks;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO

Explanation of ISNULL
Syntax:
ISNULL ( check_expression , replacement_value )

Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

Following is good example of ISNULL from BOL:
USE AdventureWorks;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Observation:
Interesting observation is NULLIF returns null if it comparison is successful, where as ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.

Row Data Bound in Gridview

 protected void gvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
    {

           if (e.Row.RowType == DataControlRowType.DataRow && (e.Row.RowState ==                      DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate))
        {
            LinkButton lnkEdit = (LinkButton)(e.Row.FindControl("lnkEdit"));
            Label lbllogin_status = (Label)(e.Row.FindControl("lbllogin_status"));
            if (lbllogin_status.Text == "offline" || lbllogin_status.Text == "")
                lnkEdit.Visible = false;
            else
                lnkEdit.Visible = true;
        }
}

Interfaces in C#

INTERFACES

• An interface is a way to define a set of methods that we implement by a derived class or multiple derived classes.
• Two classes can implement the same interface in a different way.
• It is basically a logical structure that describes the functionality (methods) but not its implementation( body).
• A class must provide the bodies of the methods described in an interface to implement the methods.

We can declare an interface in the following way:
interface name {

return-type method-name1( parameter list);

return-type method-name2( parameter list);

}

We declare an interface with the keyword interface. Here "name" is the name of the interface. In an interface all the methods are public, so there is no requirement for explicit access specifiers.

Now we implement an interface in the following way:
class class-name:interface-name{

// body of the class  }

A class can implement more than one interface. We can declare each interface in a comma-separated list. In this case, the name of the base class is always first.

EX:

Complete Program

using System;

public interface ICalc

{

int add(int a,int b);

int sub(int x,int y);

}

class Calc:ICalc

{

                public int add(int a,int b)

                {

                                return a+b;

                }

public int sub(int x,int y)

                {

                                return x-y;

                }

}

class MainCalc

{

                Calc c=new Clac();

                Console.WriteLine("The Addition Is:" + c.add(2,3));

                                Console.WriteLine("The Subtraction Is:" + c.sub(3,2));

}

The output will be:

The Addition is:5
The Subtraction is:1

Inheritance in the Interfaces

Interfaces can be inherited. Now we look at the example of how to easily inherit an interface:

After inheritance, "second" has the methods of the first interface and it adds printC() to it. Now the class MyClass can access all the three methods:

EX:

Complete Program

using System;

public interface first

{

                void printA();

                                void printB();

}

public interface second:first

{

void printC();

}

class MyClass:second

{

public void printA()

                {

                Console.WriteLine(" Print A");

                }

               

public void printB()

                {

                Console.WriteLine(" Print B");

                }

               

public void printC()

                {

                Console.WriteLine(" Print C");

                }

}

class MyMainClass

{

                public static void main()

                {

                                MyClass a=new MyClass();

                                a.printA();

                                a.printB();

                                a.printC();

                }

}

upload mp3 file in asp.net using validation Expression

<asp:FileUpload ID="mp3UploadFile" runat="server" />
<asp:RegularExpressionValidator ID="revFluUploadFile" ControlToValidate="mp3UploadFile"
    ValidationExpression="^.+(.mp3|.MP3)$"
    ErrorMessage="Not MP3 type file" runat="server">
</asp:RegularExpressionValidator

String Vs StringBuilder

String :

ex:

string returnNumber = "";
for(int i = 0; i<1000; i++)
{
    returnNumber = returnNumber + i.ToString();
}


1.String object is immutable
2.Data value maynot be Changed and Variable value may be changed.


StringBuilder:


ex:
StringBuilder returnNumber = new StringBuilder(10000);
for(int i = 0; i<1000; i++)
{
    returnNumber.Append(i.ToString());
}

1.StringBuilder objects are mutable 

2.StringBuilder performs is faster than Strings

Advantage of StringBuilder:

StringBuilder  is the best way to concatenate string.it doesnt take any time to concatenate string compare to normal string concatenate.you can concanate a string by using Append()  as follows

example:

           StringBuilder sb1 = new StringBuilder("Shakeer ");

           sb1.Append  ("Hussain ");

           Console.WriteLine(sb1.ToString());

output :ShakeerHussain 

web url validation in javascript

if(document.getElementById("<%=txtWebURL.ClientID %>").value=="")
    {
               alert("Web URL can not be blank");
               document.getElementById("<%=txtWebURL.ClientID %>").value="http://"
               document.getElementById("<%=txtWebURL.ClientID %>").focus();
               return false;
    }
    var Url="^[A-Za-z]+://[A-Za-z0-9-_]+\\.[A-Za-z0-9-_%&\?\/.=]+$"
    var tempURL=document.getElementById("<%=txtWebURL.ClientID%>").value;
    var matchURL=tempURL.match(Url);
     if(matchURL==null)
     {
               alert("Web URL does not look valid");
               document.getElementById("<%=txtWebURL.ClientID %>").focus();
               return false;
     }


........weburl macting...........

var Url = "^[A-Za-z]+://[A-Za-z0-9-_]+\\.[A-Za-z0-9-_%&\?\/.=]+$";
var matchURL = WebUrl.match(Url);
if (matchURL == null) {
   alert("Web URL does not look valid");
   document.getElementById("<%=txtURL.ClientID %>").focus();
   return false;
}

.....Removing the HTTP Protocal from TextBox.......


 if (txtUrl.Text.Contains("http://"))
        {
            txtUrl.Text = txtUrl.Text.Replace("http://", string.Empty);
        }
        Response.Write(txtUrl.Text);

Cast() Function In Sql Server

The Cast() Function is used to change the data type of a column. We can use the cast() function for various purpose.

Syntax:
Cast(Original_Expression as Desired_DataType)

We can use this function for the following purpose:
1. To concatenate string
2.. Joining two columns
3. Performing Unions of Table
4. Performing mathematical operation on columns
Example: For example we have an employee Table.



ex:

Select Name, WorkingHours = Cast(WorkingHours as char(2)) + 'hours working a day'
From Employee

Generate Random Password

Description :

In the previous article I discussed how to generate random password using Cryptography concept. But here I will show you how to generate random password using character array

Now Design your aspx page like this.

<%@ Page Language="C#"AutoEventWireup="true"CodeFile="Default.aspx.cs"Inherits="_Default"%>
<!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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click"
            Text="Click Here To Generate Random Password" />
        <br />
        <br />
        Your Password is&nbsp;&nbsp;&nbsp;
        <asp:Label ID="Label1" runat="server"></asp:Label>
    </div>
    </form>
</body>
</html>
After that write the following code in code behind.

    protected void Button1_Click(object sender, EventArgse)
    {
        Label1.Text =GenerateRandomPassword(7);
    }

    private string GenerateRandomPassword(int length)
    {
        stringchars = "abcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        char[]password = new char[length];
        Randomrnd=new Random();
        for (int i = 0; i < length; i++)
        {
            password[i] = chars[rnd.Next(0,chars.Length)];

        }
        return new string(password);
    }
OUTPUT

WaterMarkImage in Your Project

<h2>
        Welcome To GrayLogic!
    </h2>
    <p>
        please Select a Image File:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:FileUpload ID="FileUpload1" runat="server" />
    </p>
    <p>
        <asp:Button ID="btnUpload" runat="server" Text="ImageUpload"
            onclick="btnUpload_Click" />
    </p>
    <p>
        <asp:Image ID="Image1" runat="server" Width="250px" Height="250px" BorderColor="Black"
            BorderStyle="Solid" BorderWidth="1" />
    </p>



 {
        //Code For WaterMarking
        // Create an Image Object and fill it with the stream of the selected image.
        System.Drawing.Image image = System.Drawing.Image.FromStream(FileUpload1.PostedFile.InputStream);
        // Get the height and width of an image.
        int Height = image.Height;
        int Width = image.Width;
        // Creating the Bitmap Object and assigning the width and height of the image.
        // It internally creates a graphical image with the same dimension.
        System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(Width, Height);
        // Creating a Graphics Object from the Bitmap Object
        System.Drawing.Graphics graphics1 = System.Drawing.Graphics.FromImage((System.Drawing.Image)bmp);
        // Assigning few properties to the Graphics Object, to maintain the quality of the image.
        graphics1.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High;
        graphics1.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
        graphics1.Clear(System.Drawing.Color.Transparent);
        graphics1.DrawImage(image, 0, 0, Width, Height);
        // Create the Font and the Brush Object and assign appropriate parameters inorder to add watermark to the image.
        System.Drawing.Font font = new System.Drawing.Font("Arial", 20);
        System.Drawing.SolidBrush brush = new System.Drawing.SolidBrush(System.Drawing.Color.Aqua);

        // Drawstring actually writes / watermarks the image with the specified content.
        // Parameters contain :- Watermark Content, font style, brush used, x and y position for the string to be written
        graphics1.DrawString("GrayLogic", font, brush, 25F, 115F);
        // Create the image object from the bitmap and then use it to save the watermarked image in the folder.
        System.Drawing.Image newImage = (System.Drawing.Image)bmp;
        if (FileUpload1.HasFile)
        {
            //Get the Guid
            string myguid = System.Guid.NewGuid().ToString();
            //Remove The Hyphens
            myguid = myguid.Replace("-", string.Empty);
            //Decresing the myguid
            myguid = myguid.Substring(0, myguid.Length-20);
            // Getting the file name of the selected image
            string FileName = Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName);
            // Getting the file extension of the selected image
            string FileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Trim();
            // Checking the format of the Image file.
            if (FileExtension != ".jpg" && FileExtension != ".jpeg" && FileExtension != ".png" && FileExtension != ".gif" && FileExtension != ".bmp")
            {
            string alert="alert('File Format Not Supproted,Only .jpg,.jpeg,.png,.gif,.bmp file Formats are Supported');";
            ScriptManager.RegisterStartupScript(this, GetType(), "JScript", alert, true);
            }
            // Checking if the Width or height is greater than 400px
            else if (image.PhysicalDimension.Width > 600 || image.PhysicalDimension.Height > 600)
            {
                string alert = "alert('Image File should be Exactly 400*400 dimesions.Your Current Image Width is " + image.PhysicalDimension.Width + "and Height is" + image.PhysicalDimension.Height + ".');";
                ScriptManager.RegisterStartupScript(this, GetType(), "JScript", alert, true);
            }
            else
            {
                // Creating a complete relative path for storing the image. And also attaching the datetime stamp with the image name.
                string path = "~/waterMarkedImages/" + myguid + System.DateTime.Now.ToString("yyyy-MM-dd HHmmtt") + FileExtension;
                // Saving the Image.
                FileUpload1.SaveAs(Server.MapPath(path));
                // Saving the Watermarked Image in the specified folder
                newImage.Save(Server.MapPath(path));
                // Assigning the uploaded image url to the Image control.
                Image1.ImageUrl = path;
                graphics1.Dispose();
                if (!string.IsNullOrEmpty(Image1.ImageUrl))
                {
                    // Showing a notification of success after uploading.
                    string alert = "alert('Image Uploaded Successfully');";
                    ScriptManager.RegisterStartupScript(this, GetType(), "JScript", alert, true);

                }
            }
        }

    }