Aliases in sql server

SQL server aliases are used to temporarily rename a table or a column heading.

SQL Aliases

SQL aliases are used to give a database table, or a column in a table, a temporary name.

Basically aliases are created to make column names more readable.

Alias Syntax for Columns

SELECT column_name AS alias_name
FROM table_name;

Alias Syntax for Tables

SELECT column_name(s)
FROM table_name AS alias_name;

DISTINCT in sql server

In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.

The DISTINCT keyword can be used to return only distinct (different) values.

DISTINCT Syntax

SELECT DISTINCT column_name,column_name
FROM table_name;

DISTINCT Example

SELECT DISTINCT namr FROM tbl_student;

SQL datetime formats with century

SQL datetime formats with century (YYYY or CCYY format)- sql time format

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)

-- Oct 2 2010 11:01AM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

-- Oct 2 2010 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

-- yyyymmdd - ISO date format - international standard - works with any language setting

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

-- 02 Oct 2010 11:02:07:577

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

-- 2010-10-02T10:52:47.513

-- Without century (YY) date / datetime conversion - there are exceptions!

SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(varchar, getdate(), 1) -- mm/dd/yy

SELECT convert(varchar, getdate(), 2) -- yy.mm.dd

SELECT convert(varchar, getdate(), 3) -- dd/mm/yy

SELECT convert(varchar, getdate(), 4) -- dd.mm.yy

SELECT convert(varchar, getdate(), 5) -- dd-mm-yy

SELECT convert(varchar, getdate(), 6) -- dd mon yy

SELECT convert(varchar, getdate(), 7) -- mon dd, yy

SELECT convert(varchar, getdate(), 8) -- hh:mm:ss

SELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 10) -- mm-dd-yy

SELECT convert(varchar, getdate(), 11) -- yy/mm/dd

SELECT convert(varchar, getdate(), 12) -- yymmdd

SELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)

SELECT convert(varchar, getdate(), 23) -- yyyy-mm-dd

SELECT convert(varchar, getdate(), 24) -- hh:mm:ss

SELECT convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm

-- SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd

SELECT convert(varchar(7), getdate(), 126) -- yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyy

SELECT substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm

what is trim() in c#

Trim eliminates leading and trailing whitespace. We need to remove whitespace from the beginning or ending of a string. We use the .NET Framework's Trim method to do this efficiently. This method removes any characters specified.

 

Trim Example input & output

1)      String input:       "   This is an example string. "

      Trim method Output: "This is an example string."

2)       String input:       "This is an example string.\r\n\r\n"

      Trim method Output: "This is an example string."

3)       String input:       "\t, again and again."

      Trim method Output:   "again and again."

Note:  You can remove commas, spaces, and tabs.

Example

This first example removes space characters from the beginning and end of a C# string. Note that when you call Trim on a string, it copies the string and returns a modified version of that copy. The original is not changed.

Example program for trim in c#

using System;

class Program

{

    static void Main()

    {

             // Input string

             string st = "  This is an example string. ";

             // Call Trim instance method.

             // This returns a new string copy.

             st = st.Trim();

             Console.WriteLine(st);

    }

}

Output:-(Spaces were removed.)

This is an example string.

How to find media screen resolution in javascript

<script type="text/javascript">

    function viewOrder() 

    {

      var width = (window.innerWidth > 0) ? window.innerWidth : screen.width;

    }

  </script>

How to showing a video/movie on a web page

<video width="320" height="240" controls>
  <source src="movie.mp4" type="video/mp4">
  <source src="movie.ogg" type="video/ogg">
Your browser does not support the video tag.
</video>

How to get current location(latitude and longitude) in javascript

<script>
var x=document.getElementById("demo");
function getLocation()
  {
  if (navigator.geolocation)
    {
    navigator.geolocation.getCurrentPosition(showPosition);
    }
  else{x.innerHTML="Geolocation is not supported by this browser.";}
  }
function showPosition(position)
  {
  x.innerHTML="Latitude: " + position.coords.latitude + 
  "<br>Longitude: " + position.coords.longitude; 
  }
</script>

Label vs Literal in asp.net

Label:

The Label web server control is used to label other controls on an aspx web page/UserControl. It is used to display text with the ability to change the style (appearing) of the displayed text.

Inheritance Hierarchy:
System.Object
  System.Web.UI.Control
    System.Web.UI.WebControls.WebControl
      System.Web.UI.WebControls.Label

<asp:Label ID="lblFirstName" runat="server" CssClass="myCssClass"Text="First Name" />

It will be rendered as:

<span id="ctl00_ContentPlaceHolder1_lblFirstName" class="myCssClass">First Name</span> 
(NB: the id of the Label is related to the structure of your asp page and if you are using MasterPage or not)

Literal:

Use the System.Web.UI.WebControls.Literal control to reserve a location on the Web page to display text. The Literal control is similar to the Label control, except the Literal control does not allow you to apply a style to the displayed text. You can programmatically control the text displayed in the control by setting the Text property.

Inheritance Hierarchy:
System.Object
  System.Web.UI.Control
    System.Web.UI.WebControls.Literal 

<asp:Literal id="litFirstName" runat="server" Text="First Name" />

Find Duplicate records from a Database Table and Delete them

To Find Out Duplicate records in a Table and Delete them:-

For this operation i am creating a temparory table and insert records in it, with some duplicate rows. First i will find the duplicate rows and insert it in a temparory table, then delete all records from Original table which are related to Duplicate rows.

Then Insert rows from Temparory table to the original table. Now you can find the unique records in your original table.

--Creating Table

Create TAble emp( id int, name Char(30) )

--Inserting Rows

insert into emp (id,name) values (1,'adi')

insert into emp (id,name) values (2,'jc.adi')

insert into emp (id,name) values (1,'adi')

insert into emp (id,name) values (1,'adi')

insert into emp (id,name) values (3,'adi')

insert into emp (id,name) values (2,'adi')

--Now check the table

SELECT * FROM emp

--Create Duplicate Table

Create TAble #DuplicateTable

( DuplicateId int, DuplicateName Char(30) )

--Getting Duplicate Records from Original table

insert into #DuplicateTable (DuplicateId,DuplicateName) (

SELECT id,name FROM emp GROUP BY id,name HAVING COUNT(id)>1)

--Again check Duplicate table

SELECT * FROM #DuplicateTable

--DELETE RECORDS FROM original TABLE

DELETE FROM emp

FROM emp

INNER JOIN #DuplicateTable Duplicate ON Duplicate.DuplicateId = emp.id

--Again check your original table

SELECT * FROM emp

--INSERT Records in Original table from Temparory table

INSERT INTO emp (id,name)

(SELECT DuplicateId,DuplicateName FROM #DuplicateTable)

--Now you can check that the Original table contains only unique records.

SELECT * FROM emp

(OR)

SET ROWCOUNT 1

DELETE emp

FROM emp a

WHERE (SELECT COUNT(*) FROM emp b WHERE b.id =

a.id AND b.name = a.name ) > 1

WHILE @@rowcount > 0

  DELETE emp

  FROM emp a

  WHERE (SELECT COUNT(*) FROM emp b WHERE b.id =

a.id AND b.name = a.name) > 1

SET ROWCOUNT 0

Static Method in C#

Introduction

The methods in C# may or may not take parameters and they may or may not return a value. Also, a custom method that is also known as a user defined method may be declared non-static (instance level) or static (class level). When a method is static then it can be invoked directly from the class level without creating an object. This is the reason for making a main() function/method static. Another example is the WriteLine() method that is called/used without creating any object. Let's explore further using an example.

    class Program
    {
        public static void withoutObj()
        {
            Console.WriteLine("Hello");
        }
         static void Main()
        {
            Program. withoutObj();
            Console.ReadKey();
        }
    }

In the above example, I will be calling a method using the class name itself without an object being used for the WriteLine().

Using Static Method

Usually we define a set of data members for a class and then every object of that class will have a separate copy of each of those data members. Let's have an example.

    class Program
    {
        public int myVar;  //a non-static field
         static void Main()
        {
            Program p1 = new Program();  //a object of class
            p1.myVar = 10;
            Console.WriteLine(p1.myVar);
            Console.ReadKey();
        }
    }

In the above example, myVar is a non-static field so to use this field we first need to create the object of that class. On the other hand, static data is shared among all the objects of that class. That is, for all the objects of a class, there will be only one copy of static data. Let's have an example.

    class Program
    {
        public static int myVar;  //a static field
         static void Main()
        {
            //Program p1 = new Program();  //a object of class
            myVar = 10;
            Console.WriteLine(myVar);
            Console.ReadKey();
        }
    }

In the above we don't have an object of the class to use that field since the field is static.

Notable Points here are:

1. A static method can be invoked directly from the class level

2. A static method not requires any class object

3. Any main() method is shared through entire class scope so it always appears with static keyword.

Thanks for reading.

Coding of Log Out Button in asp.net C#

protected void lnkbtnlogout_Click(object sender, EventArgs e)

{

Session.Abandon();

Session.Clear();

Response.Redirect("~/Default.aspx");

}

Partial Classes in C#

Introduction

A Partial class is one that can be split among multiple physical files. This feature was introduced with the release of C# version 2.0. With C#, we can split the source code for a class into separate files so that we can organize the definition of a large class into smaller, easier to manage pieces. When we split a class across multiple files, we define the parts of the class by using the partial keyword in each file. Each file contains a section of the class definition, and all parts are combined when the application is compiled. Look at the example below:

Original and Single Class File (Calculation.cs)

class ClassRoom
{
    private int boycount;   //field

    public ClassRoom()     //default constructor
    {
        boycount = 30;
    }

    public ClassRoom(int bcount)     //overloaded constructor
    {
        boycount = bcount;
    }

    public double Avg()     //method
    {
        //statements goes here
    }
}

Splitted Class Files into two parts

//Calculation1.cs
partial class ClassRoom
{
    private int boycount;   //field

    public ClassRoom()     //default constructor
    {
        boycount = 30;
    }
}

//Calculation2.cs
partial class ClassRoom
{
    public ClassRoom(int bcount)     //overloaded constructor
    {
        boycount = bcount;
    }

    public double Avg()     //method
    {
        //statements goes here
    }
}

Now, when we compile a class that has been split into separate files, we must provide all the files to the compiler.

Partial Classes Rules and Advantages

To work on large projects, spreading a class over separate files allows multiple programmers to work on it simultaneously.

When working with automatically generated source, code can be added to the class without having to recreate the source file.

To split a class definition, use the partial keyword modifier.

All the parts must have the same accessibility, such as public, private, and so on.

The partial modifier can only appear immediately before the keywords class, struct, or interface.

Benefit of partial classes:

1) More than one developer can simultaneously write the code for the class.

2) You can easily write your code (for extended functionality) for a VS.NET generated class. This will allow you to write the code of your own need without messing with the system generated code.