0

I'm porting an app from firebird to postgres and there is a bunch of nested sql commands. The first command is necessary for the second one to run, there are some functions that have 5 or 6 nested commands and they are all using the same connection. I just wanted to know if there is a way to do all this on one connection without having to rewrite the whole thing.

    static void NestedCommandsOnOneConnection()
    {
        using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
        {
            using (NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM tableA", connection))
            {
                using (NpgsqlDataReader reader = command.ExecuteReader())
                {
                    using (NpgsqlCommand command2 = new NpgsqlCommand("SELECT * FROM tableB where column1 = @column1", connection))
                    {
                        command2.Parameters.AddWithValue("@column1", reader["column1"]);

                        using (NpgsqlDataReader reader2 = command2.ExecuteReader())
                        {
                            while (reader2.Read())
                            {
                                //Do things
                            }
                        }
                    }
                }
            }
        }
    }

//Edit: Would it be better if i do something like this?

    static void NestedCommandsOnOneConnection()
    {
        using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
        {
            var column1 = "";
            using (NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM tableA LIMIT 1", connection))
            {
                using (NpgsqlDataReader reader = command.ExecuteReader())
                {
                    while(reader.Read())
                    {
                       column1 = reader["column1"].ToString();
                    }

                }
            }
            using (NpgsqlCommand command2 = new NpgsqlCommand("SELECT * FROM tableB where column1 = @column1", connection))
            {
                command2.Parameters.AddWithValue("@column1", column1);

                using (NpgsqlDataReader reader2 = command2.ExecuteReader())
                {
                    while (reader2.Read())
                    {
                        //Do things
                    }
                }
            }
        }
    }

When the second command gets executed I get the error msg "a command is already in progress: SELECT * FROM tableA" So is there any way to do this without having to make a connection for each command?

Improve this question
12
  • Those aren't nested commands. ADO.NET has no such thing. Nothing prevents you from executing multiple commands using the same connection. Your code is trying to read from multiple open readers though. Even in the databases that support this (SQL Server with MARS), this requires special treatment Commented May 29, 2019 at 9:35
  • What are you trying to do? It would be a lot faster to write a single query that joins multiple tables together and returns only the results you need Commented May 29, 2019 at 9:39
  • This code could be replaced by a single command that executes SELECT * from TableB where column1 in (SELECT column1 from TableA). Performance will probably be multiple times better than the slow client-side loop Commented May 29, 2019 at 9:43
  • The code snippet was just an example, dont want to just post the code im working with as it would be really confusing for you guys. I just wanted to know if it would be possible to do something like the snip using one connection Commented May 29, 2019 at 9:50
  • 1
    This code shows a bad data access pattern. Why do you want to keep two readers open at the same time? Those are supposed to be forward-only firehose (ie fast) readers, not loops. Whatever the reason, the code should modified so there's no need for this Commented May 29, 2019 at 9:51

2 Answers 2

Reset to default
2

The issue is that the when you are on the context on the first command/reader, the connection is taken for that. Until you release the connection from the first command/reader, you can't run the second command, and so on.

The solution for that, is to load the data you are getting to a List, and iterate per that list and run the second command/reader. On the second command/reader you also load the result in another List, and iterate over it.

At the end, my recommendation would be for you to evaluate the data you want from the database and the querys you are doing. The way you are thinking, is right, but it adds an huge overload on the database that will create problems in other concurrent users trying to get data from the same tables.

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

2 Comments

It's the reader that causes the problem, not the command. The OP's code is trying to create and use multiple readers at the same time. Multiple ExecuteNonQuery or ExecuteScalar calls wouldn't cause any issues
In fact, the code looks like an attempt to emulate a join between tableA and tableB. All this could be replaced by a JOIN or a SELECT * from TableB where column1 in (SELECT column1 from TableA)
0

To add to @paulo-correia's response above, opening a second connection may be fine, depending on your usecase. Assuming you're using pooling and you're not in shortage of connections (i.e. a massively concurrent scenario), there's nothing wrong with doing that and it would save on the client-side memory needed to buffer the first query's results. However, note that if the two queries need to be in a transaction this is not possible.

Improve this answer

1 Comment

The problem is caused by the readers, not the commands. There's no reason to open multiple connections, unless the OP wants to read different results at the same time. This wouldn't improve performance though, as the code would have to read one row at a time from each reader.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.