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
Hey I get the error at the connection.Open() which says “System.Net.Sockets.SocketException: Connection refused”.
Any help please?
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.
Same error here
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.
Because your postgresql database stays local if you publish your postgresql database to a server there will not be a problem.
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
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.
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.
Please try to change .net version Also , you can read this issues they take same problem
https://github.com/npgsql/npgsql/issues/1604
Any other solution idea?
Sorry for my bad English.
At the moment version 4.08 is not working(TreadingTaskException). But version 4.00 works well.
Thanks for the article!)
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.