asmx return json example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web.Script.Serialization;  

namespace MYTEST
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    // [System.Web.Script.Services.ScriptService]
    [ScriptService]
    public class MYTESTDATA : System.Web.Services.WebService
    {

        SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
        SqlCommand cmd;    
        SqlDataAdapter adp;
        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public void GetLevel()
        {
            DataTable ds = new DataTable("Kamal");
            con.Open();
            cmd = new SqlCommand("Proc_APP_GETLEVEL", con);
            cmd.CommandType = CommandType.StoredProcedure;
            adp = new SqlDataAdapter(cmd);
            adp.Fill(ds);    
            this.Context.Response.ContentType = "application/json; charset=utf-8";
            this.Context.Response.Write(CreateJsonParameters(ds));
        }
        [WebMethod]
      [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public void GetLevelWiseData(int LevelId)
        {
            DataTable ds = new DataTable("Kamal");
            con.Open();
            cmd = new SqlCommand("Proc_APP_GETLEVELWISEDATA", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@LevelId", SqlDbType.DateTime).Value = LevelId;//State-code
            adp = new SqlDataAdapter(cmd);
            adp.Fill(ds);
            this.Context.Response.ContentType = "application/json; charset=utf-8";
            this.Context.Response.Write(CreateJsonParameters(ds));


        }
        private string CreateJsonParameters(DataTable dt)
        {
     
            StringBuilder JsonString = new StringBuilder();
            //Exception Handling
            if (dt != null)//&& dt.Rows.Count > 0
            {
                JsonString.Append("{ ");
                JsonString.Append("\"Result\":[ ");

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    JsonString.Append("{ ");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j < dt.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() +
                                  "\":" + "\"" +
                                  dt.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == dt.Columns.Count - 1)
                        {
                            JsonString.Append("\"" +
                               dt.Columns[j].ColumnName.ToString() + "\":" +
                               "\"" + dt.Rows[i][j].ToString() + "\"");
                        }
                    }
                                    
                    if (i == dt.Rows.Count - 1)
                    {
                        JsonString.Append("} ");
                    }
                    else
                    {
                        JsonString.Append("}, ");
                    }
                }

                JsonString.Append("]}");
                return JsonString.ToString();
            }
            else
            {
                return null;
            }
        }
    
  
      
    }
}

Find duplicate row in excel

Format >> Conditional formatting


Formula -  =COUNTIF($A$1:$A$11,$A1)>1

Filtered records as below -

Refresh Page Issue in ASP.Net

Introduction

In Web Programming, the refresh click or postback is the big problem which generally developers face. So in order to avoid the refresh page issues like, if user refreshes the page after executing any button click event/method, the same method gets executed again on refresh. But this should not happen, so here is the code to avoid such issues.

Using the Code

Here the page's PreRender event and ViewState variable helps to differentate between the Button click event call or the Refresh page event call. For example, We have a web page having button to display text entered in text box to the Label.
So when page is first time loaded, then a session["update"] object is assigned by some unique value, and then the PreRender event is being called, where that session is assigned to the viewstate variable.
And on button click event, the session assigning code from page load will never called, as it is postback, so it directly calls the button click event where there is check whether the session and viewstate variables have same value. Here both values will be same. At the end of the click event method, the session variable is assigned with new unique value. Then always after click event, the PreRender event gets called where that newly assigned session value is assigned to viewstate variable.
So whenever the page is being refreshed, viewstate value will become previous value (previous value is taken from viewstate hidden control) which will never match with current session value. So whenever the control goes in button click event, the match condition never gets satisfied hence code related to button click never gets executed.

Points of Interest

Here we can understand the page events flow as well as Viewstate variable's workflow easily.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack) // If page loads for first time
    {
        // Assign the Session["update"] with unique value
        Session["update"] = Server.UrlEncode(System.DateTime.Now.ToString()); 
        //=============== Page load code =========================




        //============== End of Page load code ===================
    }
}

protected void btnDisplay_Click(object sender, EventArgs e)
{ 
    // If page not Refreshed
    if (Session["update"].ToString() == ViewState["update"].ToString())
    {
        //=============== On click event code ========================= 

        lblDisplayAddedName.Text = txtName.Text;

        //=============== End of On click event code ==================

        // After the event/ method, again update the session 

        Session["update"] = Server.UrlEncode(System.DateTime.Now.ToString()); 
    }
    else // If Page Refreshed
    {
        // Do nothing 
    }
}

protected override void OnPreRender(EventArgs e)
{
    ViewState["update"] = Session["update"];

 }

Get Last Running Query Based on SPID

Query 1-

To know which sessions are running currently, run the following command:

SELECT @@SPID
GO

get the latest run query in our input buffer

DBCC INPUTBUFFER(61)--61 is the output of above query
GO

Query 2- 

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61 -- get from SELECT @@SPID 

SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO 

Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.

SQL SERVER (Using DMV)– Find Most Expensive Queries

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC
 

Sql nest CTE properly

 --First CTE

;with CTE as (
select  
 C.Id,C.App_DepartmentCode,C.SenttoLevelCD ,C.ComplaintCode,D.DepartmentName_U,
 (Case When isnull(D.parentdepartment,'0') ='0' Then C.App_DepartmentCode

 when isnull(D.parentdepartment,'0')<>'0' 

Then D.parentdepartment end) As parent
 from tbl_Complaints_CompiledStatus C inner join tbl_Department D On

 D.DepartmentCode=C.App_DepartmentCode
 and  C.IsFirstForwording='Yes' and  C.CompType=1 
 and C.ComplaintCount=1 and C.IsFinalStatus_FirstLevel='ATRS'
 and C.App_DepartmentCode  <> 0
 ),

--- Second CTE

  CTE1 as(
 select  CTE.id,App_DepartmentCode,SenttoLevelCD,ComplaintCode,

CTE.DepartmentName_U,ISNULL(ORD.Userid,0) Userid

,username,parent from CTE
 inner join tbl_Department D ON CTE.parent=D.departmentcode
  left join tbl_orderby ORD ON ORD.Userid=D.ParbhariAdhikariCD
   where ORD.Userid <> 0
 
   )

--UPDATE USING ABOVE CTE

 UPDATE A   
  SET A.ParbhariAdhikari = B.username,   A.ParbhariAdhikariCD = B.userid,

   A.Parentdepartment=B.parent from tbl_Complaints_CompiledStatus  A
  INNER JOIN CTE1  B
    ON A.ID = B.ID

Get Missing Index In database

SELECT TOP 25

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')

+ CASE

WHEN dm_mid.equality_columns IS NOT NULL

AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns

IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

GO