Monday, November 27, 2017

Find a specific text in a SQL Server Stored Procedure, Function, View

Declare @SearchString  varchar(50) = 'kotak_CMS..sf_subscriber'

Get Sp name with type and defination from sys.sql_modules and sys.objects tables
SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type, m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like '%'+@SearchString+'%'

Get Sp name from syscomment and sysobjects table
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%'+@SearchString+'%'

Get Sp name from sys.procedures table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@SearchString+'%'
GO

Find Column name in database tables
SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%Branch%'
ORDER BY    TableName ,ColumnName;
GO

Find Text in All Store Procedure
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%(7)%' 
    AND ROUTINE_TYPE='PROCEDURE'
GO

Wednesday, November 1, 2017

Below Query for fetch most trafic consuming query list. Most expensive, time consuming queries

SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
    (CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
Where qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

Tuesday, August 8, 2017

Removing X-Powered-By ASP.Net and other version headers

Removing X-AspNet-Version

In web.config add this:
<system.web>
<httpRuntime enableVersionHeader="false"/>


Removing X-AspNetMvc-Version

In Global.asax.cs add this line:

protected void Application_Start()
{
             MvcHandler.DisableMvcResponseHeader = true;
}

Removing or changing Server

In Global.asax.cs add this:

protected void Application_PreSendRequestHeaders()
{
           //Response.Headers.Remove("Server");
          Response.Headers.Set("Server","Server");
          Response.Headers.Remove("X-AspNet-Version"); //alternative to above solution
          Response.Headers.Remove("X-AspNetMvc-Version"); //alternative to above solution
}

Removing or changing X-Powered-By

IIS 7 - You can also remove the X-Powered-By header by including these lines to the element:






or manually go to IIS7 Management Console and open HTTP Response Headers

Thursday, March 16, 2017

Get Top 1,2,3,... Nth Row From SQL, Database,


To Get the nth row from your table to the following steps
  • Find all the distinct values count
  • Write a self query that test the grater then condition
  • Now you will get the same row that you requested
For Ex:

Create table Employee(Id int identity(1,1),Name varchar(10),Salary int);

Insert into Employee values ('A',10000)
Insert into Employee values ('B',10001)
Insert into Employee values ('C',10002)
Insert into Employee values ('D',10003)
Insert into Employee values ('E',10004)
Insert into Employee values ('F',10005)
Insert into Employee values ('G',10006)
Insert into Employee values ('H',10007)
Insert into Employee values ('I',10008)
Insert into Employee values ('J',10009)


Declare @N int = 9
SELECT * FROM Employee E1
WHERE (@N-1) =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary < E1.Salary)

Wednesday, February 8, 2017

Ways to send data using POST Method and Get/Read response in .NET, Using HTTPWebRequest and WebClient

Way 1:
 
NameSpace:
using System.Net;
using System.Collections.Specialized;
 
Source Code: 
To Post Form fields to respective site your must be pass the input fields with 
request page fields name with required values as per my below example first_name, 
last_name,gender.... these all are the request parameters/fields of target site 
and FName,LName, Gender... these are the my page input fields value using web client 
we can eassly call the service and capture the response from their end.
                
In my response i show how do we get the response id from the received JSON data. 
here unique id is my respose data that is received from the requested api using 
post method


using (WebClient client = new WebClient())
{
    client.UseDefaultCredentials = true;
    client.Headers.Add("Authorization", "BASIC " + AuthKey);

    byte[] response =
    client.UploadValues(URI, new NameValueCollection()
    {
        { "first_name", FName },
        { "last_name", LName },
        { "gender", Gender },
        { "date_of_birth",DOB },
        { "email", Email },
        { "variable_six", Var6 },
        { "variable_three", Var3 },
        { "variable_four", Var4 },
        { "marketing", Marketing},
        { "variable_two", Var2 },
        {"terms_and_conditions", TNC},
        { "validation_token", Token},
        { "variable_five", Var5 }                       
    });

    string result = System.Text.Encoding.UTF8.GetString(response);
    if (result.Length > 0) {
        var dict = new System.Web.Script.Serialization.JavaScriptSerializer()
                     .Deserialize>(result);
        var MyId = UniqueId = dict["id"].ToString();
    }
}   
 
Way 2:
 
Namespace:
using System.Net;
using System.Text;
using System.IO;

Source Code: 
To Post Form fields to respective site your must be pass the input fields with  
request page fields name with required values as per my below example first_name, 
 last_name,gender.... these all are the request parameters/fields of target site  
and FName,LName, Gender... these are the my page input fields value using 
HttpWebRequest we can call the service and capture the response from their
 end.
                
In my response i show how do we get the response id from the received JSON data. 
 here unique id is my response data that is received from the requested api using  
post method
 
string postString = string.Format("first_name:{0},last_name:{1},gender:{2},date_of_birth:{3},email:{4},variable_six:{5},variable_three:{6},variable_four:{7},marketing:{8},variable_two:{9},terms_and_conditions:{10},validation_token: {11},variable_five: {12}"
    , FName, LName, Gender, DOB, Email, Var6, Var3, Var4, Marketing, Var2, TNC, Token, Var5);

HttpWebRequest httpRequest =
(HttpWebRequest)WebRequest.Create(URI);

httpRequest.Method = "POST";
httpRequest.ContentType = "application/x-www-form-urlencoded";              
httpRequest.Headers.Add("Authorization", "BASIC " + AuthKey);
httpRequest.UseDefaultCredentials = true;

byte[] bytedata = Encoding.UTF8.GetBytes(postString);
httpRequest.ContentLength = bytedata.Length;

Stream requestStream = httpRequest.GetRequestStream();
requestStream.Write(bytedata, 0, bytedata.Length);
requestStream.Close();


HttpWebResponse httpWebResponse =
(HttpWebResponse)httpRequest.GetResponse();
Stream responseStream = httpWebResponse.GetResponseStream();

StringBuilder sb = new StringBuilder();

using (StreamReader reader =
new StreamReader(responseStream, System.Text.Encoding.UTF8))
{
    string line;
    while ((line = reader.ReadLine()) != null)
    {
        sb.Append(line);

        var dict = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize>(line);
        var  UniqueId = dict["id"].ToString();
    }
}