C# How to connect PostgreSql database | C# CRUD | PostGreSql Function

#CSharp, #PostgreSql, #Connect, #CRUD
This tutorial show how to connect C# PostgreSql, using Postgres Function. C# CRUD

CRUD function:

1. Select function

CREATE OR REPLACE FUNCTION public.st_select(
)
RETURNS TABLE(id integer, firstname character varying, midname character varying, lastname character varying)
LANGUAGE ‘plpgsql’

COST 100
VOLATILE
ROWS 1000
AS $BODY$

begin
return query
select st_id, st_firstname, st_midname, st_lastname from Students order by st_id;
end

$BODY$;

2. Insert function:

CREATE OR REPLACE FUNCTION public.st_insert(
_firstname character varying,
_midname character varying,
_lastname character varying)
RETURNS integer
LANGUAGE ‘plpgsql’

COST 100
VOLATILE
AS $BODY$

begin
insert into Students(st_firstname, st_midname, st_lastname)
values(_firstname, _midname, _lastname);
if found then –inserted successfully
return 1;
else return 0; — inserted fail
end if;
end

$BODY$;

3. Update function:

CREATE OR REPLACE FUNCTION public.st_update(
_id integer,
_firstname character varying,
_midname character varying,
_lastname character varying)
RETURNS integer
LANGUAGE ‘plpgsql’

COST 100
VOLATILE
AS $BODY$

begin
update Students
set
st_firstname = _firstname,
st_midname = _midname,
st_lastname = _lastname
where st_id = _id;
if found then –updated successfully
return 1;
else –updated fail
return 0;
end if;
end

$BODY$;

4. Delete function:

CREATE OR REPLACE FUNCTION public.st_delete(
_id integer)
RETURNS integer
LANGUAGE ‘plpgsql’

COST 100
VOLATILE
AS $BODY$

begin
delete from Students
where st_id = _id;
if found then –deleted successfully
return 1;
else
return 0;
end if;
end

$BODY$;