Saturday, December 27, 2014

Debian 7 & Postgres post installation changes

Check if PgSql is running: 
                    Check PgSQL Is Running:/etc/init.d/postgresql status
Restart:service postgresql restart
Start:service postgresql start
Stop:service postgresql stop
Doing First Login:
su -l root
su - postgres
psql -d template1 -U postgres
Lists databases:
SELECT datname FROM pg_database WHERE datistemplate = false;
or
\list
List Tables:
SELECT table_schema,table_name FROM information_schema.tables
ORDER BY table_schema,table_name;
           or
\dt
List User:
                     \du
List Roles:
           SELECT rolname FROM pg_roles;
CREATE user & associate database to it:
CREATE USER san WITH PASSWORD 'urpwd';
CREATE DATABASE pgDb;
                 GRANT ALL PRIVILEGES ON DATABASE pgdb to san;
Create User In Debian using BASH:
useradd san
passwd san
Delete User in Debian:
userdel san
Login into PgSql terminal as new user:
       su - san
       psql -d pgDb -U san
Connect Using PgAdmin using following details
Server:localhost
port:5432 (Default)
User:san
password:urpasswd

How to send Json data using C#:


      suppose we have to send certain data as json post over wire using c#  for that we have construct corresponding poco class as follows where each property correspond with field to post. 
   Other option is to create json string on your own.

POCO CLASS:

public class PocoJsonData
{
public string CellNo
{
get; set;
}
public string Brand
{
get;set;
}
public string Seller
{
get;set;
}
public string Region
{
get;set;
}
public string ContentType
{
get;set;
}
public string ItemCode
{
get;set;
}
public string ItemName
{
get;set;
}
public string ContentPrice
{
get;set;
}
public string DiscountedPrice
{
get;set;
}
public string BillDate
{
get; set;
}
public string Address
{
get; set;
}
}

public const string token="ffghfgfgjfjfjfjfjhfjfgfgkuhjhhkhj";

public string DoJsonPost(
string CellNo, string Brand, string Seller, string Region,
string ContentType, string ItemCode, string ItemName, string ContentPrice, string
DiscountedPrice, string BillDate,string Address)
{
string result = "";
ServicePointManager.ServerCertificateValidationCallback += (sender, certificate, chain, sslPolicyErrors) => true;
//action to which data to post
string url = "https://abc.com/pqr/lmn";


PocoJsonData data = new PocoJsonData
{
CellNo = Convert.ToString(CellNo).Trim(),
Brand = Convert.ToString(Brand).Trim(),
Seller = Convert.ToString(Seller).Trim(),
Region = Convert.ToString(Region).Trim(),
ContentType = Convert.ToString(ContentType).Trim(),
ItemName = Convert.ToString(ItemName).Trim(),
ContentPrice = Convert.ToString(ContentPrice).Trim(),
DiscountedPrice = Convert.ToString(DiscountedPrice).Trim(),
BillDate = Convert.ToString(BillDate).Trim(),
ItemCode = Convert.ToString(ItemCode).Trim() ,
Address = Convert.ToString(Address).Trim()
}; 

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
request.Headers.Add("Authorization", "Bearer " + token);
request.Method = "POST";
request.ContentType = "application/json; charset=utf-8";
DataContractJsonSerializer ser = new DataContractJsonSerializer(data.GetType());
MemoryStream ms = new MemoryStream();
ser.WriteObject(ms, data);
String json = Encoding.UTF8.GetString(ms.ToArray());
StreamWriter writer = new StreamWriter(request.GetRequestStream());
writer.Write(json);
writer.Close();
System.Net.HttpWebResponse webresponse = (HttpWebResponse)request.GetResponse();
using (StreamReader reader = new StreamReader(webresponse.GetResponseStream(), Encoding.Default))
{
result = reader.ReadToEnd();
}
webresponse.Close();
return result;
}

    Here we are setting up content type as application/json to tell receiver of data , data received is of format json.

    Data to be posted has been assigned to poco class object instance properties,then the instance of object has been serialized to json string then using HttpWebRequest class we will post data and get response from other side

Saturday, August 2, 2014

Sql Server -Group Concat using STUFF & For XML



Let’s first create a table to demonstrate the concept as follows    

create table #Temp
(
  id int,
  fname varchar(50),
  lname varchar(50)
)

Now I will add data into it in specific format as follows
insert into #Temp(id,fname,lname)
select '70','sangram',null
union
select '70',null,null
union
select '70',null,'desai'


insert into #Temp(id,fname,lname)
select '60','sachin',null
union
select '60',null,null
union
select '60',null,'desai'














In all now our #Temp table is having 6 records in it.


We aim to convert this data into two rows by doing group concatenation over string in fname & lname columns respectively 

    Here is my take over it.

    select
        distinct id ,
      STUFF(
           (
            Select ','+fname
                from #Temp T1
                where T1.id=T2.id


              FOR XML PATH('')
          ),1,1,''),
          STUFF((Select ','+lname
                from #Temp T1
                where T1.id=T2.id
            FOR XML PATH('')),1,1,'')
    from
       #Temp T2


This will collect value from first row of fname & append it with value from second row fname over rowset under consideration,value concatenation is done with comma as separator that can be visible if multiple non-null values occur in fname column in given row-set

Output:
 
 
     Thanks & Happy Coding !