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

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 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();
    }
}

Friday, April 29, 2016

ASP .Net Validation Sample(s) with Client Side Custom Validation Sample(s)

Required Field Validation With Initial value
<asp:RequiredFieldValidator ID="rfvtxtOther" runat="server" InitialValue="Other" ValidationGroup="vgSubmit" Enabled="false" ControlToValidate="txtOther" Display="Dynamic" CssClass="visitErrormsg otherErrormsg" ErrorMessage="Please provide other details" />



String Validation
<asp:RegularExpressionValidator ID="revtxtCity" runat="server" ValidationGroup="vgSubmit" ControlToValidate="txtCity" Display="Dynamic" CssClass="visitErrormsg" ErrorMessage="Please provide valid city name" ValidationExpression="^[a-zA-Z ]{2,30}$" />


Email Validation
<asp:RegularExpressionValidator ID="regtxtEmail" runat="server" ControlToValidate="txtEmail" Display="Dynamic" ErrorMessage="Please provided valid email address" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" ValidationGroup="vgSubmit" CssClass="visitErrormsg"></asp:RegularExpressionValidator>


Mobile Number Validation
<asp:RegularExpressionValidator ID="revtxtMobile" runat="server" ValidationGroup="vgSubmit" ControlToValidate="txtMobile" Display="Dynamic" CssClass="visitErrormsg" ErrorMessage="Please provide valid mobile number"                                   ValidationExpression="^[1-9]{1}[0-9]{9}$" />

Address Validation
<asp:RegularExpressionValidator ID="revtxtAddress" runat="server" ValidationGroup="vgSubmit" ControlToValidate="txtAddress" Display="Dynamic" CssClass="visitErrormsg" ErrorMessage="Please provide valid address"
                                    ValidationExpression="^[a-zA-Z ,./~!@#$%^&*()_+{}\|:']{5,50}$" />

Custom Validation to Upload at least single file
<asp:CustomValidator ID="cvCommon" runat="server" ValidationGroup="vgSubmit" ClientValidationFunction="file_ValidationSingle" CssClass="visitErrormsg" ErrorMessage="Please provided at least 1 file in image format." />

function file_ValidationSingle(sender, args) {
            var f1 = document.getElementById('<%=file_one.ClientID%>').value;
            var f2 = document.getElementById('<%=file_two.ClientID%>').value;
            var f3 = document.getElementById('<%=file_three.ClientID%>').value;
            if (f1 == "" && f2 == "" && f3 == "") {
                args.IsValid = false;  // field is empty
            } else {
                args.IsValid = true;
            }
        }

File Upload Control Validation with max 1 MB and only image format
 <asp:CustomValidator ID="cvfile_one" runat="server" ValidationGroup="vgSubmit" ControlToValidate="file_one" ClientValidationFunction="file_Validation" CssClass="visitErrormsg" ErrorMessage="Please provided file in image format with max 1 MB." />

  function file_Validation(sender, args) {
            var xyz = document.getElementById(sender.controltovalidate);
           // var array = ['pdf', 'doc', 'docx', 'txt', 'xlsx', 'ppt', 'zip'];
            var array = ['png', 'jpg', 'jpeg', 'bmp', 'gif'];
            var xyz = v.value;
            var Extension = xyz.value.substring(xyz.value.lastIndexOf('.') + 1).toLowerCase();
            if (array.indexOf(Extension) <= -1) {
                //alert("Please Upload only pdf,doc,zip,txt.xlsx and ppt extension flle");
                args.IsValid = false;
            } else {
                //alert(xyz.files[0].size);
                if (xyz.files[0].size > 1048576) {  //1MB
                    args.IsValid = false;
                }
            }
        }

Enable Validators from Client Site
ValidatorEnable(document.getElementById("rfvtxtOther"), true);

Disable Validator from Client Side
ValidatorEnable(document.getElementById("rfvtxtOther"), false);

Check From Validation from client side without any Validation Group
if (Page_ClientValidate()){
 //Form Valid
}else{
 //Invalid Form
}

Check From Validation from client side with Validation Group

if (Page_ClientValidate("ValidationGroupName")){
 //Form Valid
}else{
 //Invalid Form
}