Pivot Table in Sql server

declare @date datetime,@ss varchar(500)='',@ss1 varchar(500)
 declare curs_c cursor for select   Distinct workdate from tblprojecttime
 open curs_c
FETCH NEXT FROM curs_c INTO @date
 while @@FETCH_STATUS=0
 begin
 if(@ss='')
 set @ss='['+cast(@date as varchar(12))+']'
 else
  set @ss=@ss+','+'['+cast(@date as varchar(12))+']'
FETCH NEXT FROM curs_c INTO @date
 end
 close curs_c
 deallocate  curs_c
 print @ss

  set @ss1=N'select * from (select  projectid,taskid,workdate,workhours from tblprojecttime)
 as ss pivot(  Sum(workhours) for workdate in('+@ss +') )as PivotTable'
 
  execute (@ss1)

How to change CSS on classname Click?

$(document).ready(function () {
    GetProduct(2); Getsafetymenu(1);
    $(".ln").on("click", function () {
        $(".ln").removeClass("on");
        $(this).addClass("on");
        $(".ln").css({ cursor: "pointer" });
        $(this).css({ cursor: "default" });
     
       
    });
    $(".mn").on("click", function () {
        $(".mn").removeClass("mn1");
        $(this).addClass("mn1");
        $(".mn").css({ cursor: "pointer" });
        $(this).css({ cursor: "default" });
     
    });
});
function GetProduct(SafetyCatId) {
    $.ajax({
        type: "POST",
        url: "SafetyEnviornment.aspx/Getsafetysheet",
        data: '{SafetyCatId: ' + SafetyCatId + '}',
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: OnSuccess,
        failure: function (response) {          
        },
        error: function (response) {          
        }
    });
    if (SafetyCatId = 2) {
        $('#lcat2').css({ cursor: "default" });
       $('#lcat2').addClass("on");

    }
}
function OnSuccess(response) {
    var xmlDoc = $.parseXML(response.d);
    var xml = $(xmlDoc);
    var customers = xml.find("tblSafetyEnvironment");  
    var sttable = '';
    var name = '';
    var des = '';
    $.each(customers, function () {
        var customer = $(this);
        var pagecaption = $(this).find("pagecaption").text();
        var PageName = $(this).find("PageName").text();
        name = $(this).find("SafetyCatname").text();
        des = $(this).find("SafetyCatDescription").text();
        sttable += '<li> <a href="Safety/' + pagecaption + '">' + PageName + '</a></li>';
    });
    $("#ctl00_ctl00_ContentPlaceHolder1_ContentPlaceHolder1_lblSafetyCategoryDesc").html(des);
    $("#ctl00_ctl00_ContentPlaceHolder1_ContentPlaceHolder1_lblSafetyCategoryName").html(name);
    $("#ctl00_ctl00_ContentPlaceHolder1_ContentPlaceHolder1_rptsafetydatasheet").html(sttable);
};
function Getsafetymenu(SafetyId) {
    $.ajax({
        type: "POST",
        url: "SafetyEnviornment.aspx/Getsafetymenu",
        data: '{SafetyId: ' + SafetyId + '}',
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: OnSuccess1,
        failure: function (response) {},
        error: function (response) {}
    });
    if (SafetyId=1) { $('#b1').css({ cursor: "default" }); }
 
}

function OnSuccess1(response) {
    var sttable = response.d;
    $("#ctl00_ctl00_ContentPlaceHolder1_ContentPlaceHolder1_spmenuDescription").html(sttable);
 
};

JSON with AJAX Example

using System;
using System.IO;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data.SqlClient;
using System.Runtime.Serialization.Json;
[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = 
  AspNetCompatibilityRequirementsMode.Allowed)]
public class ProductService
{ 
 [OperationContract]    
 public string GetProductDetailsByProductID(int productID)
 {
   Product prod = new Product();        
   string connectionString = 
     "server=localhost;uid=sa;pwd=thiru;database=AdventureWorks;";
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
     string sql = "Select Name, ProductNumber from Production.Product " +
       " Where ProductID = " + productID.ToString();
     connection.Open();
     SqlCommand command = new SqlCommand(sql, connection); 
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
       prod.Name = reader["Name"].ToString();
       prod.ProductNumber = reader["ProductNumber"].ToString();
       prod.ProductID = productID;
     }
    }
    MemoryStream stream = new MemoryStream();
    DataContractJsonSerializer serializer = new 
      DataContractJsonSerializer(typeof(Product));
    serializer.WriteObject(stream, prod);
    stream.Position = 0;
    StreamReader streamReader = new StreamReader(stream);
    return streamReader.ReadToEnd();        
  } 
}
[DataContract]
public class Product
{
    [DataMember]
    public int ProductID;
    [DataMember]
    public string Name;
    [DataMember]
    public string ProductNumber;
} 

================

<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
 <title>Invoking the Product Service through the AJAX based Client</title>
 <script type="text/javascript">    
   function pageLoad() {
   }
   function OnGetProductDetailsByProductID() {                 
     ProductService.GetProductDetailsByProductID($get("txtProductID").value, 
       OnGetProductDetailsByProductIDComplete, OnError);         
   } 
   function OnGetProductDetailsByProductIDComplete(result){ 
     var prod = eval("(" + result + ")");                
     $get("spnProductID").innerText = prod.ProductID;
     $get("spnProductName").innerText = prod.Name;
     $get("spnProductNumber").innerText = prod.ProductNumber;                
   } 
   function OnError(errorMessage) {
     alert(errorMessage.get_message()); 
   } 
  </script>
</head>
<body>
 <form id="form1" runat="server">
  <div>
   Enter Product ID: 
   <input type="text" id="txtProductID" name="txtProductID" />        
   <input type="button" value="Get Product Details" id="btnInvokeWebService" 
     onclick="OnGetProductDetailsByProductID()" />
   <asp:ScriptManager ID="ScriptManager1" runat="server">
     <Services>
       <asp:ServiceReference Path="~/ProductService.svc" />
     </Services>
   </asp:ScriptManager>
   <br /><br />        
   Product ID : <span id="spnProductID"></span> <br /><br />
   Name :<span id="spnProductName"></span> <br /><br />
   Product Number :<span id="spnProductNumber"></span> <br /><br />
  </div>
 </form>
</body>
</html> 
Before looking at the code required to implement the client, let us understand the steps involved in utilizing the ASP.NET AJAX extensions for invoking the WCF Web service.


The client declares a <asp:ScriptManager> element to specify the location of the Web service. Inside this element, you declare the path of the Web service using the Path attribute of the <asp:ServiceReference> element. This enables the client side code to dynamically create the proxy for the Web service. 
You invoke a client-side method that will use declared proxy to invoke the Web service. 
The proxy invokes the Web service passing in the required arguments to the service, as well as the callback method to invoke after the Web service execution is completed. In addition, you also pass in a separate callback method that will be invoked in case an exception is generated during the server side processing. 
The service receives the request, processes the request by invoking the appropriate methods on the server side. Then it returns the results back to the proxy on the client side. 
The proxy receives the server response and invokes a callback method located on the client-side. If there is an error during the server side processing, the callback manager invokes a separate callback method. 
The client processes the results returned from the service call. 

Append in Jquery.

$( "p" ).append( $( "strong" ) );

$( "p" ).append( document.createTextNode( "Hello" ) );

$( "p" ).append( "<strong>Hello</strong>" );

$( ".inner" ).append( "<p>Test</p>" );

$( ".container" ).append( $( "h2" ) );

Formates Data Objects

--Cursor
Declare MyCursor CURSOR FOR    
select Categoryid,subcategoryid,subsubcategoryid from #temp    
OPEN MyCursor    
     
FETCH NEXT FROM MyCursor    
INTO @QCategoryid, @QSubcategoryId, @QSubSubcategoryId    
     
WHILE @@FETCH_STATUS = 0    
BEGIN
-- for Work
 
FETCH NEXT FROM MyCursor    
INTO @QCategoryid, @QSubcategoryId, @QSubSubcategoryId    
END    
CLOSE MyCursor    
DEALLOCATE MyCursor

--Function

CREATE FUNCTION [dbo].[AllSubCategory](@ProductId bigint )
RETURNS varchar(8000)
AS
BEGIN
declare @return varchar(8000)
 RETURN @return
END

--View
CrEATE view [dbo].[vw_rptBlog]
as
SELECT *  from mlm_achiever


--Trigger

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2012 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

--Index
CREATE INDEX index_name
ON table_name (column_name,column_name,..)

CREATE CLUSTERED INDEX IX_TestTable_TestCol1
    ON dbo.TestTable (TestCol1);
----
CREATE UNIQUE INDEX index_name
on table_name (column_name);

How to remove html tag in description?

 public static string RemoveHTMLTags(string content)
    {
        var cleaned = string.Empty;
        try
        {
            StringBuilder textOnly = new StringBuilder();
            using (var reader = XmlNodeReader.Create(new System.IO.StringReader("<xml>" + content + "</xml>")))
            {
                while (reader.Read())
                {
                    if (reader.NodeType == XmlNodeType.Text)
                        textOnly.Append(reader.ReadContentAsString());
                }
            }
            cleaned = textOnly.ToString();
        }
        catch
        {
            //A tag is probably not closed. fallback to regex string clean.
            string textOnly = string.Empty;
            Regex tagRemove = new Regex(@"<[^>]*(>|$)");
            Regex compressSpaces = new Regex(@"[\s\r\n]+");
            textOnly = tagRemove.Replace(content, string.Empty);
            textOnly = compressSpaces.Replace(textOnly, " ");
            cleaned = textOnly;
        }

        return cleaned;
    }

First DNN Setting in Web.config.

<system.webServer>
<security>
<requestFiltering>
<requestLimits maxAllowedContentLength="100000000" />
</requestFiltering>
</security>
</system.webServer>


Change connection string:

<add name="SiteSqlServer" connectionString="Data Source= Abhishek-pc\sql2008;Initial Catalog=dnnT; uid=sa; password=2008;" providerName="System.Data.SqlClient" />


and also  if u don't want to by wizard then set to false

<add key="UseInstallWizard" value="false" />

First set the all permission on project folder and deploy In IIS