CODEWITHSUNDEEP
c#sqlpostgresqlnpgsql
A Petrov
A Petrov

Reputation: 474

How to get the value of selected row from PostgreSQL in C#?

I am using PostgreSQL database with C# and the Npgsql library.

Right now I can select the last row in my table, but I can not figure out how to assign a C# variable to it. I know that my selection works, because I have successfully edited my last entry before.

You can find my code below. Note that I have not pasted the rest of the methods as I think they are irrelevant.

public void myMethod()
{
    this.OpenConn(); //opens the connection

    string sql = "SELECT id FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'customers' ORDER BY id DESC, LIMIT 1";

    using (NpgsqlCommand command = new NpgsqlCommand(sql, conn))
    {
        int id = 0; //instead of '0' I want it to be equal to the ID value from the row
        //something like "int id = sqlSelection.id;" -- this obviously doesn't work

        this.CloseConn(); //close the current connection
    }
}

Upvotes: 1

Views: 27255

Answers (3)

Anzeem S N
Anzeem S N

Reputation: 151

You can use ExecuteScalarSync method.

public void myMethod()
{
    this.OpenConn(); //opens the connection

    string sql = "SELECT id FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'customers' ORDER BY id DESC, LIMIT 1";

    using (NpgsqlCommand command = new NpgsqlCommand(sql, conn))
     {
       int id= (int)DBHelperRepository.ExecuteScalarSync(sqlString, CommandType.Text);

       this.CloseConn(); //close the current connection
     }
}

Upvotes: 0

Baqer Naqvi
Baqer Naqvi

Reputation: 6504

Use can use following code variation too;

 using (var command = new NpgsqlCommand(sql, conn))
 {
        int id = 0; 
        var reader = command.ExecuteReader();
        while(reader.Read())
        { 
           var id = Int32.Parse(reader["id"].ToString());
        }
        this.CloseConn(); 
 }

Upvotes: 1

Gabe
Gabe

Reputation: 971

You could achieve this goal by using the specific DataReader:

public void myMethod()
{
    this.OpenConn(); //opens the connection

    string sql = "SELECT id FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'customers' ORDER BY id DESC, LIMIT 1";

    using (NpgsqlCommand command = new NpgsqlCommand(sql, conn))
    {
        int val;
        NpgsqlDataReader reader = command.ExecuteReader();
        while(reader.Read()){
           val = Int32.Parse(reader[0].ToString());
           //do whatever you like
        }

        this.CloseConn(); //close the current connection
    }
}

Useful notes

Upvotes: 9

Related Questions