How to fetch/ retrieve data in period order (Day,week,Month,Quarter,year) from SQL Server with aggregate function.

CREATE Procedure SP_PaymentToSupplierEmployee_Periodically
@tbl_OrganizationId nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT 
COUNT(tbl_PaymentToSupplierEmployeeId) As TotalTransaction, DateName(wk,PaymentDate)As [Week],DateName(d, PaymentDate)As [days], DateName(dw,PaymentDate) As [DayName], DateName(m,PaymentDate) AS [month],
DateName(qq,PaymentDate) As [Quarter],DateName(yy, PaymentDate) As [Year],
SUM(Total) As TotalRevenue,  case when IsPosted='False' then count(IsPosted)end As pending,
CASE WHEN IsPosted='true' then count(IsPosted)end As Posted
FROM tbl_PaymentToSupplierEmployee
WHERE IsDeleted='False' And tbl_OrganizationId=@tbl_OrganizationId
GROUP BY PaymentDate,IsPosted
END

How to consume wcf service using Jquery.

Data Base:-


CREATE TABLE [dbo].[tbl_registration](
[FormID] [int] IDENTITY(1,1) NOT NULL,
[ModelName] [nvarchar](50) NULL,
[RegFees] [float] NULL,
[RegNo] [nvarchar](50) NOT NULL,
[SmartNo] [nvarchar](50) NULL,
[CustomerName] [nvarchar](50) NULL,
[Address] [nvarchar](150) NULL,
[Location] [nvarchar](50) NULL,
[Contactno] [nvarchar](50) NULL,
[EngineNo] [nvarchar](50) NULL,
[chassisaNo] [nvarchar](50) NULL,
[PurchaseDate] [datetime] NULL,
[validupto] [datetime] NULL,
[RegDate] [datetime] NULL,
[username] [nvarchar](50) NULL,
 CONSTRAINT [PK_tbl_registration] PRIMARY KEY CLUSTERED
(
[RegNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE procedure [dbo].[SP_RegistrationDetails]

@FormID int

AS BEGIN

Select * from tbl_registration 

where FormID= @FormID

END

GO

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

File--New--Project eg- Demo

 Add-New Item -registrationDetails.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace Demo

{

    public class registrationDetails

    {

        public int FormID { get; set; }

        public string ModelName { get; set; }

        public Decimal RegFees { get; set; }

        public string RegNo { get; set; }

        public string SmartNo { get; set; }

        public string CustomerName { get; set; }

        public string Address { get; set; }

        public string Location { get; set; }

        public string Contactno { get; set; }

        public string EngineNo { get; set; }

        public string chassisaNo { get; set; }

        public string PurchaseDate { get; set; }

        public string validupto { get; set; }

        public string RegDate { get; set; }

        public string username { get; set; }

    }

}

Add-New Item-WCF Service(Ajax-Enable) eg-RegistrationServices

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.ServiceModel.Activation;

using System.ServiceModel.Web;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace Demo

{

    [ServiceContract(Namespace = "")]

    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

    public class RegistrationServices

    {

       

        [OperationContract]

       

        public registrationDetails GetRegistrationById(int FormID)

        {

            registrationDetails rd = new registrationDetails();

            string cs = ConfigurationManager.ConnectionStrings["dmo"].ConnectionString;

            using (SqlConnection con = new SqlConnection(cs))

            { SqlCommand cmd = new SqlCommand("SP_RegistrationDetails", con);

                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param = new SqlParameter();

                param.ParameterName = "@FormID";

                param.Value = FormID;

                cmd.Parameters.Add(param);

                con.Open();

                SqlDataReader sdr = cmd.ExecuteReader();

                while (sdr.Read())

                {

                  

                    rd.ModelName = sdr["ModelName"].ToString();

                    rd.RegFees = Convert.ToDecimal(sdr["RegFees"]);

                    rd.RegNo = sdr["RegNo"].ToString();

                    rd.SmartNo = sdr["SmartNo"].ToString();

                    rd.CustomerName = sdr["CustomerName"].ToString();

                    rd.Address = sdr["Address"].ToString();

                    rd.Location= sdr["Location"].ToString();

                    rd.Contactno = sdr["Contactno"].ToString();

                    rd.EngineNo = sdr["EngineNo"].ToString();

                    rd.chassisaNo = sdr["chassisaNo"].ToString();

                    rd.PurchaseDate = sdr["PurchaseDate"].ToString();

                    rd.validupto = Convert.ToString(sdr["validupto"]);

                    rd.RegDate = Convert.ToString(sdr["RegDate"]);

                    rd.username = Convert.ToString(sdr["username"]);                           

                 

                  

                }

            }

            return rd;

        }

        

    }

}

web Config :-

 <connectionStrings>

    <add name="dmo" connectionString="Data Source=AJIT; Database=BBAJAJ; user id=sa; password=admin@123" />

  </connectionStrings>

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

Add-New Item-Web Form egWebForm1.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Demo.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

<script src="Scripts/jquery-1.11.2.js"></script>

<script type="text/javascript">

$(document).ready(function () {

$('#btnGetRegistrationById').click(function () {

var FormId = $('#txtFormId').val();

$.ajax({

url: 'RegistrationServices.svc/GetRegistrationById',

method: 'post',

contentType: 'application/json;charset=utf-8',

data: JSON.stringify({ FormID: FormId }),

dataType: 'json',

success: function (data) {

$('#txtModelName').val(data.d.ModelName);

$('#txtRegFees').val(data.d.RegFees);

$('#txtRegNo').val(data.d.RegNo);

$('#txtSmartNo').val(data.d.SmartNo);

$('#txtCustomerName').val(data.d.CustomerName);

$('#txtAddress').val(data.d.Address);

$('#txtContactno').val(data.d.Contactno);

$('#txtLocation').val(data.d.Location)

$('#txtEngineNo').val(data.d.EngineNo);

$('#txtchassisaNo').val(data.d.chassisaNo);

$('#txtPurchaseDate').val(data.d.PurchaseDate);

$('#txtvalidupto').val(data.d.validupto);

$('#txtRegDate').val(data.d.RegDate);

$('#txtusername').val(data.d.username);

},

error: function (error) {

alert(error);

}

});

});

});

function formatJSONDate(jsonDate) {

var newDate = new dateFormat(jsonDate, "mm/dd/yyyy");

//var newDate = dateFormat(jsonDate, "mm/dd/yyyy");

return newDate;

}

</script>

</head>

<body>

    <form id="form1" runat="server">

        FormId: <input id="txtFormId" type="text" style="width:89px"/>

<input type="button" id="btnGetRegistrationById" value="ResitrartionDetails" />

<br />

<br />

<table border="1" style="border-collapse:collapse">

<tr><td>ModelName :</td><td><input id="txtModelName" type="text" /></td></tr>

<tr><td>RegFees :</td><td><input id="txtRegFees" type="text" /></td></tr>

<tr><td>RegNo :</td><td><input id="txtRegNo" type="text" /></td></tr>

<tr><td>SmartNo :</td><td><input id="txtSmartNo" type="text" /></td></tr>

<tr><td>CustomerName :</td><td><input id="txtCustomerName" type="text" /></td></tr>

<tr><td>Address :</td><td><input id="txtAddress" type="text" /></td></tr>

<tr><td>Location :</td><td><input id="txtLocation" type="text" /></td></tr>

<tr><td>Contactno :</td><td><input id="txtContactno" type="text" /></td></tr>

<tr><td>EngineNo :</td><td><input id="txtEngineNo" type="text" /></td></tr>

<tr><td>chassisaNo:</td><td><input id="txtchassisaNo" type="text" /></td></tr>

<tr><td>PurchaseDate: </td><td><input id="txtPurchaseDate" type="datetime" /></td></tr>

<tr><td>validupto:</td><td><input id="txtvalidupto" type="datetime" /></td></tr>

<tr><td>RegDate: </td><td><input id="txtRegDate" type="datetime" /></td></tr>

<tr><td>username:</td><td><input id="txtusername" type="text" /></td></tr>

</table>

    </form>

</body>

</html>

https://github.com/ajitagarwal89/WCF-USING-JQUERY

Delegate in c#

What is Delegate in C#.

Delegate is an object which holds the  references to a method  within the delegate object.It is  type safe object and invoking the method asynchronously manner.

Advantage of Delegates

1 Improved the performance of Application
2 Call a method asynchronous.

Type of Delegate 
1. Single Delegate and
2 Multicast  Delegate
3 Generic Delegate

Single Delegate :-A delegate is need to pass single parameter as reference to a method with the delegate object.

using System;
using System.Collections.Generic;
using System.Text; 

namespace DelegateTest
{
    public delegate void Calc(int x, int y);

    class A    {
        public void add(int x, int y)
        {
            Console.WriteLine("The Sum is " + (x + y));
        } 

        public void sub(int x, int y)
        {
            Console.WriteLine("The Difference is " + (x - y));
            Console.ReadLine();
        }
    }

    class Program    {
        static void Main(string[] args)
        {
            A obj = new A();
            //make an object of class A         
            Calc objsum= new numtest(obj.add);
            objsum(10, 20);
            Calc objsub= new numtest(obj.sub);
            objsub(10, 20);
        }
    }
}

https://msdn.microsoft.com/en-us/library/system.delegate.aspx


Multicast Delegate: - When we need to pass more than one parameter as reference  to a method within delegate object.

How to find schema changes report in SQL Server ?

How to find schema  changes report in SQL Server ?

Select o.name As [Objcet_Name],
 s.name [Schema_Name],
 o.type_desc [Description],
 o.create_date [Creation_Date],
 o.modify_date [Modified_Date]
 from sys.all_objects o
 Left outer join sys.schemas s
 on o.schema_id=s.schema_id
 where create_date >(GETDATE()-7) or modify_date >(GETDATE()-7)

Or

We can check SSMS  Wizard .

Step 1:- Select Database 

Step 2:-click right mouse button and  Select Report.

Step 3:- Select Standard 

Step 4: finally Select Schema change History . 

How to rest asp.net control in web form.

Protected void Clear()
{
foreach(Control ctrl in Form.Control)
{
//Text Box Control
if(ctr is TextBox)
{
((TextBox )(ctrl)).Text="";
}
// Label Control
else if(ctrl is Label)
{
((Label)(ctrl)).Text = "";
}
//DropDownList controls
else if(ctrl is DropDownList)
{
((DropDownList)(ctrl)).ClearSelection();
or
((DropDownList)(ctrl)).selectedIndex = 0;  
}
//CheckBox controls
  else if (ctrl is CheckBox)
{
 ((CheckBox)(ctrl)).Checked = false;
}
 //RadioButton controls
  else if (ctrl is RadioButton)
        {
     ((RadioButton)(ctrl)).Checked = false;
            }
}
}

ASP.NET Interview Q&A

How to Delete Cookies?

HttpCookie currentUser=HttpContext.Current.Request.Cookies["CookiesValue"];
HttpContext.Current.Response.Cookies.Remove("CookiesValue");
currentUser.Expires=DataTime.Now.AddMinutes(-20);
currentUser.value=null;
HttpContext.Current.Response.SetCookie(currentUser);


Delegate is an object which holds the  references to a method  within the delegate object.It is  type safe object and invoking the method asynchronously manner.

Advantage of Delegates

1 Improved the performance of Application
2 Call a method asynchronous.

Type of Delegate 
1. Single Delegate and
2 Multicast  Delegate
3 Generic Delegate


Single Delegate :-A delegate is need to pass single parameter as reference to a method with the delegate object.


Multicast Delegate: - When we need to pass more than one parameter as reference  to a method within delegate object.


--- String and number Reverse--

namespace ConsoleApp3
{
    class Program
    {
        static void Main(string[] args)
        { //---reverse string ----
            //Console.WriteLine("Enter a number for reverse");
            //int number = Convert.ToInt32(Console.ReadLine());
            //int revers = 0;
            //while (number > 0)
            //{
            //    int rem = number % 10;
            //    revers = (revers * 10) + rem;
            //    number = number / 10;
            //}
            //Console.WriteLine(revers);
            //Console.ReadLine();
            //-- Reverse string--
            Console.WriteLine("enter your name");
            string name = Console.ReadLine();
            var temp = "";
            for (int i = name.Length - 1; i >= 0; i--)
            {
                temp += name[i].ToString();
             
            }
            Console.WriteLine(temp);
            Console.ReadLine();

        }
    }
}

----- Duplicate character in string --
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp4
{
    class Program
    {
        static void Main(string[] args)
        {
          Console.Write("Enter a word");
            string data = Console.ReadLine();

            RemoveDuplicatecharacter(data);
        }
        static string RemoveDuplicatecharacter(string value)
        {
            string result = string.Empty;
            string temp = string.Empty;
            foreach (char data in value)
            {
                if (temp.IndexOf(data) == -1)
                {
                    temp += data;
                    result += data;
                }
            }
            Console.WriteLine(result);
            Console.Read();
            return result;
           
         
           
        }
    }
}

SQL INTERVIEW QUESTION

tbl_employee

 EmployeeId EmployeeName Gender
1                     ABC                          male
2                      YYC                       Female
Question: update all male into female and all female into Male
Ans';-
Update tbl_employee
Set Gender=case Gender When 'Male' Then Female When  'Female' Then 'Male' Else gender End.

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


Different between Store procedure and function are

SNo	Function	Store Procedure
1	Function must return a value.	Stored procedure may or not return values.
2	Will allow only Select statement, it will not allow us to use DML statements.	Can have select statements as well as DML statements such as insert, update, delete etc
3	It will allow only input parameters, doesn’t support output parameters.	It can have both input and output parameters.
4	It will not allow us to use try-catch blocks.	For exception handling, we can use try-catch blocks.
5	Transactions are not allowed within functions.	Can use transactions within Stored procedures.
6	We can use only table variables, it will not allow using temporary tables.	Can use both table variables as well as a temporary table in it.
7	Stored procedures can’t be called from function.	Stored Procedures can call functions.
8	Functions can be called from the select statement.	Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec the statement can be used to call/execute the stored procedure.
9	function can be used in join clause as a result set.	Procedures can’t be used in Join clause

Q- HowTo find out the Nth highest salary (for example: here I am finding 3rd highest salary),

A-

SELECT EmpId, LastName, Salary FROM Employee

EmpId LastName Salary

1 Agarwal 50000

2 Agarwal 50000

4 Kumar 60000

5 Agarwal 75000

6 Agarwal 75000

SELECT EmpId,LastName, Salary FROM Employee a1 WHERE 3-1= (SELECT COUNT(DISTINCT Salary) FROM Employee a2 WHERE a2.Salary > a1.Salary)

EmpId LastName Salary

1 Agarwal 50000

2 Agarwal 50000

SELECT TOP 1 SAL FROM(SELECT DISTINCT TOP 2 SAL FROM EMP ORDER BY SAL DESC) AS TEMP
Order by SAL ASC

----------------------------------------------------------------------------------
-- PAGING---
----------------------------------------------------------------------------------
SELECT * FROM (SELECT ROW_NUMBER() OVER( Order By OrderItems_PKey) AS RowNum,* FROM OrderItems) AS RESULT
WHERE RowNUM  >=10 AND RowNum <=20
order by RowNum

------------------------------------------------------------------------------
select * from InventoryItem-- DELETIG THE DUPLICATE REOCRED
------------------------------------------------------------------------------
DELETE from InventoryItem
where InventoryItem_ID Not in
(select max (InventoryItem_ID)
from InventoryItem
group by Name)
-------------------------------------------------------------------------
SELECT * FROM Orders--- SECOND COSTLY ORDER VALUE

SELECT max(GrandTotal)FROM Orders WHERE GrandTotal NOT IN (SELECT MAX(GrandTotal) FROM Orders);
------------------------------------------------------------------------------------------------
--To get list of primary AND  FOREIGN KEY IN ENTIRE DATABASE------------
--------------------------------------------------------------------------------------------
select distinct
constraint_Name as [ConstraintName],
Table_Name As [Table Name]from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
------------------------------------------------------------------------------

How to find second or nth maximum salary from  Employee table?
Here EMP is Table and SAL Is Salary Filed


SELECT * FROM EMP emp1
WHERE(2)=(SELECT COUNT(DISTINCT(emp2.SAL)) FROM EMP emp2 WHERE emp2.SAL >=emp1.SAL)


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