Get Number of Weekends between two dates

This function will return numbers of weekends between two dates using datepart function in sql.

CREATE FUNCTION NumberOfWeekEnds(@dFrom DATETIME, @dTo   DATETIME)

RETURNS INT AS

BEGIN

--    DECLARE @dFrom DATETIME

--    DECLARE @dTo DATETIME

   Declare @weekends int

   Set @weekends = 0

   While @dFrom <= @dTo Begin

      If ((datepart(dw, @dFrom) = 1) OR (datepart(dw, @dFrom) = 7))    

                  Set @weekends = @weekends + 1

           

                  Set @dFrom = DateAdd(d, 1, @dFrom)

   End

   Return (@weekends)

END

GO

SELECT NumberOfWeekEnds('2/27/2013','3/10/2013')

Datepart function in sql

--Get year part of date use datepart year, yyyy, yy

SELECT DATEPART(year,GETDATE())

--Get quarter part of date use datepart quarter, qq, q

SELECT DATEPART(quarter,GETDATE())

--Get month part of date use datepart month, mm, m

SELECT DATEPART(month,GETDATE())

--Get day of year part of date use date part dayofyear, dy, y

SELECT DATEPART(dayofyear,GETDATE())

--Get day of month part of date use date part day, dd, d

SELECT DATEPART(day,GETDATE())

--Get weak of year part of date use datepart week, wk, ww

SELECT DATEPART(week,GETDATE())

--Get weekday of week part of date use datepart weekday, dw

SELECT DATEPART(weekday,GETDATE())

--Get hour of day in 24 hour format part of date use datepart hour, hh

SELECT DATEPART(hour,GETDATE())

--Get minute of hour part of date use datepart minute, n

SELECT DATEPART(minute,GETDATE())

--Get second of minute part of date use datepart second, ss, s

SELECT DATEPART(ss,GETDATE())

--Get millisecond of second part of date use datepart millisecond, ms

SELECT DATEPART(millisecond,GETDATE())

Bulk Insert into table using User-Defined Table Type

Bulk Insert into table using User-Defined Table Type

User-defined table type is a user-defined type that represents the definition of a table structure is new feature in SQL 2008. We can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function.

Create table tblEmployee

CREATE TABLE [dbo].[tblEmployee](      [EmpID] [int] IDENTITY(1,1) NOT NULL,      [FirstName] [varchar](50) NULL,      [LastName] [varchar](50) NULL,      [Email] [varchar](50) NULL,      [Address] [varchar](100) NULL, )

Create User defined table type typEmployee

CREATE TYPE typEmployee AS TABLE(      FirstName VARCHAR(50),      LastName VARCHAR(50),      Email VARCHAR(50),      Address VARCHAR(100))

Create Store Procedure usp_InserEmployeeDetail

CREATE PROC usp_InserEmployeeDetail@typEmployeeDetail      typEmployee ReadOnlyASBEGIN      INSERT INTO tblEmployee(FirstName,LastName,Email,Address)      SELECT * FROM @typEmployeeDetailEND

Bulk insert into table using user defined table type

--declare typeEmplyee type variable

DECLARE @typEmployee typEmployee 

--insert records into typeEmplyee type variable 

INSERT INTO @typEmployee(FirstName,LastName,Email,Address)VALUES ('Prakash ','Nayal ','prakahn@gmail.com ','Merrut')

INSERT INTO @typEmployee(FirstName,LastName,Email,Address)VALUES ('Rahul ','Porwal ','rahul@gmail.com ','Etawa')

INSERT INTO @typEmployee(FirstName,LastName,Email,Address)VALUES ('Markandy ','Pathak ','markandey@gmail.com ','Gorkhpur')

INSERT INTO @typEmployee(FirstName,LastName,Email,Address)VALUES ('Vishal ','Gupta ','vishalg@gmail.com ','Merrut')

--pass the typeEmplyee type variable to the store procedure as paramerter

EXEC usp_InserEmployeeDetail  @typEmployee

See the result

SELECT  * FROM tblEmployee

Generics in C# .NET

Get Weekend between two dates

C# 2.0 introduces new functionality named Generics. These allow us to create classes and methods decoupled from the data types. This means that using Generics we can create generalized classes and methods.

Generic classes are available in System.Collections.Generic namespace.

Example of generic methods

using System;

using System.Collections.Generic;

namespace GenericsDemo

{

    class Program

    {

        static void Main(string[] args)

        {

            //Equal method for numbers only

            EqualNumber objEqualNumber = new EqualNumber();

            Console.WriteLine("Number Equals: " + objEqualNumber.IsEqual(5, 6));

            //Equal method for string only

            EqualString objEqualString = new EqualString();

            Console.WriteLine("String Equals: " + objEqualString.IsEqual("Hello", "Hello"));

            //Equal method for both number and string using object parameter

            //This method use boxing and unboxing

            EqualObject objEqualObject = new EqualObject();

            Console.WriteLine("Number Equals: " + objEqualObject.IsEqual(10, 10));

            Console.WriteLine("String Equals: " + objEqualObject.IsEqual("Hello","Hello"));

            //This code will compile and execute successfully it is not type safety

            Console.WriteLine("String Equals: " + objEqualObject.IsEqual("Hello", 10));

            //This use Generics methods to compare equallity using strong type safety

            //This method dose not use boxing and unboxing

            EqualGenerics objEqualGenerics = new EqualGenerics();

            Console.WriteLine("Number Equals: " + objEqualGenerics.IsEqual<int>(10, 10));

Console.WriteLine("String Equals: " + objEqualGenerics.IsEqual<string>("Hello","Hello"));

           

            Console.ReadLine();

        }

    }

    //objects

    public class EqualObject

    {

        public bool IsEqual(object value1, object value2)

        {

            return value1.Equals(value2);

        }

    }

    //number

    public class EqualNumber

    {

        public bool IsEqual(int value1, int value2)

        {

            return value1.Equals(value2);

        }

    }

    //string

    public class EqualString

    {

        public bool IsEqual(string value1, string value2)

        {

            return value1.Equals(value2);

        }

    }

    //Generics

    public class EqualGenerics

    {

        public bool IsEqual<T>(T value1, T value2)

        {

           return value1.Equals(value2);

        }

    }

}

Generic Interface

      IInterface.cs

    using System.Collections.Generic;

namespace ConsoleApplication1

{

     interface IInterface<T>

    {

        List<T> Display(List<T> lst);

    }

    class ClassA : IInterface<Person>

    {

        public List<Person> Display(List<Person> lstPerson)

        {

            List<Person> lstPerson1 = new List<Person>();

            Person objPersion = new Person();

            objPersion.Firstname = "Rahul";

            lstPerson1.Add(objPersion);

            return lstPerson1;

        }

    }

     class ClassB : IInterface<Employee>

    {

        public List<Employee> Display(List<Employee> lstEmployee)

        {

            List<Employee> lstEmployee1 = new List<Employee>();

            Employee objEmployee = new Employee();

            objEmployee.EmployeeName = "Markandey";

            lstEmployee1.Add(objEmployee);

            return lstEmployee1;

        }

    }

    public class Person

    {

        public string Firstname { get; set; }

    }

    public class Employee

    {

        public string EmployeeName { get; set; }

    }

}

Program.cs

using System;

using System.Collections.Generic;

namespace ConsoleApplication1

{

    class Program

    {

        static void Main(string[] args)

        {

            List<Person> lstPerson = new List<Person>();

            ClassA objA = new ClassA();

            lstPerson = objA.Display(lstPerson);

            List<Employee> lstEmployee = new List<Employee>();

            ClassB objB = new ClassB();

            lstEmployee = objB.Display(lstEmployee);

            Console.ReadLine();

        }

    }

}

Advantage of Generics

·         Use generic types to maximize code reuse, type safety, and performance.

·         The most common use of generics is to create collection classes.

·         The .NET Framework class library contains several new generic collection classes in the System.Collections.Generic namespace. These should be used whenever possible in place of classes such as ArrayList in the System.Collections namespace.

·         You can create your own generic interfaces, classes, methods, events and delegates.

·         Generic classes may be constrained to enable access to methods on particular data types.

·         Information on the types used in a generic data type may be obtained at run-time by means of reflection.