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

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 !