ADO.Net.Npgsql.DataEntity is the common library to do the CRUD operations, by using the library you can perform the INSERT, UPDATE, DELETE, SELECT, etc.
You can get the data tables from just a 1 line code.
Your ⭐ on this repository also helps! Thanks! 🖖🙂
Install Npgsql.ADO.Net.DataEntity with .NET CLI
dotnet add package ADO.Net.Npgsql.DataEntity --version 1.0.1Install Npgsql.ADO.Net.DataEntity with Package Manager
NuGet\Install-Package ADO.Net.Npgsql.DataEntity -Version 1.0.1C#
using Npgsql.ADO.Net.DataEntity;
namespace SampleCode
{
public class Program
{
private static DataEntity dataEntity = new DataEntity();
static void Main(string[] args)
{
YourMainClass();
}
static void YourMainClass()
{
//Declare your PostgreSQL database connection string globally.
//Then you can create a Common Class or use it separately in the classes.
//Database connection string declared in YourMainClass globally.___START
dataEntity.pgConnection = "Server=pg_server_IP;User Id=pg_user_iid;Pwd=pg_passwrd;Database=pg_password";
//Database connection string declared in YourMainClass globally.___END
var data = dataEntity.ExecuteDataSetFN("fn_api_select_YourPostgreSQl_Database_function", "pram1", "pram2");
}
}
}
Best practice to use PostgreSQL with .net
Sample Code 1 How to Insert, update, and delete data to the database.
Create a database function in the PostgreSQL
CREATE OR REPLACE FUNCTION public.fn_insert_MasterEmployeeDetails(_empCode text, _firstName text, _lastName text)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
insert into MasterEmployeeTable(empCode, firstName, lastName)
select _empCode, _firstName, _lastName;
RETURN "true";
END;
$BODY$;
Use the PostgreSQL function in .net using c#
using Npgsql.ADO.Net.DataEntity;
namespace SampleCode
{
public class Program
{
private static DataEntity dataEntity = new DataEntity();
static void Main(string[] args)
{
YourMainClass();
}
static void YourMainClass()
{
//Declare your PostgreSQL database connection string globally.
//Then you can create a Common Class or use it separately in the classes.
//Database connection string declared in YourMainClass globally.___START
dataEntity.pgConnection = "Server=pg_server_IP;User Id=pg_user_iid;Pwd=pg_passwrd;Database=pg_password";
//Database connection string declared in YourMainClass globally.___END
var data = dataEntity.ExecuteDataSetFN("fn_insert_MasterEmployeeDetails", "Value_of__empCode", "Value_of__firstName", "Value_of_lastName");
}
}
}
Sample Code 2 How to select data from the PostgreSQL database table single or multiple.
Create a database function in the PostgreSQL
-----Function with filter
CREATE OR REPLACE FUNCTION public.fn_select_MasterEmployeeDetails(_empCode text)
RETURNS SETOF refcursor
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
_emptableDetails refcursor;
BEGIN
OPEN _emptableDetails FOR select * from MasterEmployeeTable where empCode=_empCode ;
RETURN NEXT _emptableDetails;
END;
$BODY$;
----Function without filter
CREATE OR REPLACE FUNCTION public.fn_select_MasterEmployeeDetails()
RETURNS SETOF refcursor
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
_emptableDetails refcursor;
BEGIN
OPEN _emptableDetails FOR select * from MasterEmployeeTable;
RETURN NEXT _emptableDetails;
END;
$BODY$;
-----Function to return multiple tables
CREATE OR REPLACE FUNCTION public.fn_select_MasterEmployeeDetailsWithSalary()
RETURNS SETOF refcursor
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
_emptableDetails refcursor;
_empSalaryDetails refcursor;
BEGIN
OPEN _emptableDetails FOR select * from MasterEmployeeTable;
RETURN NEXT _emptableDetails;
OPEN _empSalaryDetails FOR select * from MasterEmployeeSalaryTable;
RETURN NEXT _empSalaryDetails;
END;
$BODY$;
Use the PostgreSQL database function in .net using c#
using Npgsql.ADO.Net.DataEntity;
using System.Data;
namespace SampleCode
{
public class Program
{
private static DataEntity dataEntity = new DataEntity();
static void Main(string[] args)
{
YourMainClass();
}
static void YourMainClass()
{
//Declare your PostgreSQL database connection string globally.
//Then you can create a Common Class or use it separately in the classes.
//Database connection string declared in YourMainClass globally.___START
dataEntity.pgConnection = "Server=pg_server_IP;User Id=pg_user_iid;Pwd=pg_passwrd;Database=pg_password";
//Database connection string declared in YourMainClass globally.___END
DataSet dataWithFilter = dataEntity.ExecuteDataSetFN("fn_select_MasterEmployeeDetails", "Value_of__empCode");
DataTable _emp_dataTable = dataWithFilter.Tables[0];
DataSet dataWithOutFilter = dataEntity.ExecuteDataSetFN("fn_select_MasterEmployeeDetails");
DataTable _emp_dataTable_WithoutFilter = dataWithOutFilter.Tables[0];
DataSet _multipleDataTables = dataEntity.ExecuteDataSetFN("fn_select_MasterEmployeeDetailsWithSalary");
DataTable _empDataTable= _multipleDataTables.Tables[0];
DataTable _empSalaryDataTable= _multipleDataTables.Tables[1];
}
}
}