Selman ALPDÜNDAR

How to connect and read data from PostgreSQL in Xamarin

Before starting coding in Xamarin your database must be prepared for connection. First you must grant permission to your user name for database and tables. To grant permission your user name for database please read this  and to grant permission your table please use below in your terminal;

GRANT ALL PRIVILEGES ON TABLE people TO admin; 

Not: do not forget semicolon  if you do not put semicolon your code will not work.

We do not need to do anything in PostgreSQL. Now we can write code in Xamarin let’s start.

First create new project and  add Npgsql nuget package.

Connection string is for PostgreSQL.

string ConnectionString = "Server=localhost; Port=5432; User Id=admin; Password=123456;
Database=demo";

To connect database

try{ 
    NpgsqlConnection connection = new NpgsqlConnection(ConnectionString);
    connection.Open();
}catch(Exception ex){ 
  Console.WriteLine(ex.ToString()); 
}

 

To read table from database

NpgsqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM people";
try{
    NpgsqlDataReader reader = command.ExecuteReader();

while (reader.Read()) {
    Id.Text = reader[0].ToString();
    Name.Text = reader[1].ToString();
 }
    connection.Close();

 }catch(Exception ex){
    Console.WriteLine(ex.ToString());
 }



12 comments

  1. Hey I get the error at the connection.Open() which says “System.Net.Sockets.SocketException: Connection refused”.

    Any help please?

    1. if you use postgresql in android at your local device please change your server address to android local server which is 10.0.2.2 then it should work.

  2. its working fine now in emulator, but I got this new problem that when used in mobile data in release mode, it won’t work while it works fine in a stable wifi.

    1. Because your postgresql database stays local if you publish your postgresql database to a server there will not be a problem.

  3. I am getting this error trying to install npgsql into the Xamarin PCL project in VS 2017:
    “Error Could not install package ‘Npgsql 3.2.5’. You are trying to install this package into a project that targets ‘.NETPortable,Version=v4.5,Profile=Profile111’, but the package does not contain any assembly references or content files that are compatible with that framework.”

    Any ideas or workarounds

    1. Did you try to install a different version? please clean all project and try to install again with different version add npsql or try to update all package and try to install again. I have faced this problem while I was coding I solved it with updating packages.

  4. I have the same problem as Phil McIntosh. It did not work cleaning the project, nor installing other versions, nor changing the framework in the properties of the project.

  5. At the moment version 4.08 is not working(TreadingTaskException). But version 4.00 works well.
    Thanks for the article!)

  6. string ConnectionString = “Server=10.0.2.2 Port=5432;User Id=postgres; Password=123456; Database=Accounting”;
    try
    {
    NpgsqlConnection connection = new NpgsqlConnection(ConnectionString);
    connection.Open();
    NpgsqlCommand command = connection.CreateCommand();
    command.CommandText = “INSERT INTO public Account(‘Username’, ‘Password’, ‘Email’, ‘Phonenumber’) VALUES ( ‘” + EntryUsername.Text + “‘, ‘” + EntryPassword.Text + “‘,'” + EntrEmail.Text + “‘,'” + EntryPhoneNumber.Text + “‘)”;
    try
    {
    NpgsqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
    EntryUsername.Text = reader[0].ToString();
    EntryPassword.Text = reader[1].ToString();
    EntrEmail.Text = reader[2].ToString();
    EntryPhoneNumber.Text = reader[3].ToString();

    }

    connection.Close();

    }
    catch (Exception ex)
    {
    Console.WriteLine(ex.ToString());
    }
    }
    catch (Exception ex)
    {
    Console.WriteLine(ex.ToString());

    }

    i get this error

    02-05 11:19:46.671 I/mono-stdout( 2791): at Npgsql.NpgsqlConnector.Open (Npgsql.NpgsqlTimeout timeout, System.Boolean async, System.Threading.CancellationToken cancellationToken) [0x003da] in C:\projects
    pgsql\src\Npgsql\NpgsqlConnector.cs:423
    at Accounting.Views.registration.Handle_Clicked (System.Object sender, System.EventArgs e) [0x0000f] in C:\Users\Kevin Trinidad\source\repos\Accounting\Accounting\Accounting\Views\registration.xaml.cs:27
    02-05 11:19:46.671 I/mono-stdout( 2791): at Npgsql.ConnectorPool.AllocateLong (Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTimeout timeout, System.Boolean async, System.Threading.CancellationToken cancellationToken) [0x00156] in C:\projects
    pgsql\src\Npgsql\ConnectorPool.cs:249
    02-05 11:19:46.671 I/mono-stdout( 2791): at System.Threading.Tasks.ValueTask`1[TResult].get_Result () [0x0001b] in /Users/builder/jenkins/workspace/archive-mono/2019-08/android/release/external/corefx/src/Common/src/CoreLib/System/Threading/Tasks/ValueTask.cs:813
    02-05 11:19:46.672 I/mono-stdout( 2791): at Npgsql.NpgsqlConnection+c__DisplayClass32_0.g__OpenLong|0 () [0x0047f] in C:\projects
    pgsql\src\Npgsql\NpgsqlConnection.cs:297
    02-05 11:19:46.672 I/mono-stdout( 2791): at Npgsql.NpgsqlConnection.Open () [0x00000] in C:\projects
    pgsql\src\Npgsql\NpgsqlConnection.cs:119
    02-05 11:19:46.672 I/mono-stdout( 2791): at Accounting.Views.registration.Handle_Clicked (System.Object sender, System.EventArgs e) [0x0000f] in C:\Users\Kevin Trinidad\source\repos\Accounting\Accounting\Accounting\Views\registration.xaml.cs:27
    02-05 11:19:46.672 I/Choreographer( 2791): Skipped 40 frames! The application may be doing too much work on its main thread.

Leave a Reply to Phil McIntosh Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.