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