How to store a result of type integer array from database to an array in C#?

Question

I'm using postgresql as a database and I have a column called mealsIds it's type is array of integers.

I'm making the following SQL query.

        // Specify connection
        NpgsqlConnection conn = new NpgsqlConnection(
            "Server=127.0.0.1;" +
            "User Id=username;" +
            "Password=password;" +
            "Database=db;" +
            "Port=3500");
        conn.Open();

        // Define a query
        NpgsqlCommand cmd = new NpgsqlCommand($"SELECT catsids FROM restaurants WHERE id = {Properties.Settings.Default.ResId.ToString()}", conn);

        // Execute a query
        NpgsqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {

            /*

           I WANT TO INSERT CODE HERE

            */

        }

        // Close connection
        conn.Close();

now I know that the result is an array of integers, and I want to store it in an array so I can use the values in it.

any idea how to do that??

JGH · Accepted Answer · 2020-04-21 12:52:06Z

1

There is a direct mapping from PG array to C# array. You can simply cast the returned object to an array

        int[] result;

        if (dr.Read())
        {
            result = (int[])dr.GetValue(0);
        }

answered Apr 21, 2020 at 12:52

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

elvira.genkel · Accepted Answer · 2020-04-20 20:47:09Z

1

Try something like this:

List<int> myList = new List<int>();
while(dr.Read())
    {

        int newInt = int.Parse(dr["catsids"].ToString());
        //push to list or array here e.g. 
        myList.Add(newInt);

    }

edited Apr 20, 2020 at 20:47

answered Apr 20, 2020 at 20:38

elvira.genkel

1,3331 gold badge6 silver badges12 bronze badges

5 Comments

John Smith Over a year ago

I'm getting this exception Can't cast database type integer[] to Int32

elvira.genkel Over a year ago

and what if try to parse int from strings? int.Parse(dr["catsids"].ToString()) @JohnSmith

John Smith Over a year ago

no it won't work, you see the result is on field and it's value is this [1,2,3,4]

elvira.genkel Over a year ago

The dirty solution may look like

dr["catsids"].ToString().Replace("[",string.Empty).Replace("]", string.Empty).Split(",").Select(s => int.Parse(s)).ToArray();

John Smith Over a year ago

thank you very much for trying so hard to solve my problem, but I'm sorry it didn't work, appreciate it bro

Collectives™ on Stack Overflow

How to store a result of type integer array from database to an array in C#?

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related