Multiline query execution

I am the Lead Developer on a web-based corporate tax payment application written in C#.

My boss asked to develop a script to get him some relevant data from our application.

Now he’s asked me to port it to a Desktop Application so he can run the script himself and not have to ask me for the report every time he needs it.

The database is hosted by Azure and one of the restrictions it has is there can be no Stored Procedures.

My problem is that the query written to generate the report is rather complex, requiring a few variable declarations as well as multiple while loops.

The issue I’m having is how can I run the entire script as a single query without access to Stored Procedures? Is it even possible?

using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (var command = new SqlCommand(unificadoCommand, connection))
            {
                var s = command.ExecuteReader();

                while (s.Read())
                {
                    //Process data
                }
            }
        }

I expected to get the results of the query with data about the payments in the website but it fails with a timeout exception on command.ExecuteReader()

EDIT: Someone asked for the query. Whilst I can’t post the query here as it’s protected data from my company, I can explain how I built it.

Basically, I first DECLARE 4 INTs and my output table. Next, I have three while loops set up in a way that I INSERT into my output table the data I SELECT from the actual tables.

At the end of the while loops, I simply SELECT * FROM OutputTable.

Answer

After @Erno suggested the timeout was suspicious and @500 – Internal Server Error said there should be no problems running multi-line queries, I did some research and found that after setting the command timeout to at least 100 seconds, the query runs perfectly.

Thanks so much everyone who helped me out.

Advertisement

Answer