Timeout exception when running SQL query in C#

Question

Trying to populate a List with the following code:

string sql = ";WITH getUniqueParams AS (" +
                               "SELECT DISTINCT [a] AS 'param' FROM table " +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [b] AS 'param' FROM table " + 
                               "UNION ALL " + 
                               "SELECT DISTINCT [c] AS 'param' FROM table " +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [d] AS 'param' FROM table " + 
                               "UNION ALL " + 
                               "SELECT DISTINCT [e] AS 'param' FROM table " +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [f] AS 'param' FROM table " +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [g] AS 'param' FROM table " + 
                               "UNION ALL " + 
                               "SELECT DISTINCT [h] AS 'param' FROM table " +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [i] AS 'param' FROM table " +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [j] AS 'param' FROM table " +
                               "UNION ALL " + 
                               "SELECT DISTINCT [k] AS 'param' FROM table) " + 
                               "SELECT DISTINCT [param] FROM getUniqueParams ORDER BY [param]";   //the result of this statement to be stored in a string

            List<string> lUniqueParams = new List<string>();

            // set up SQL connection and command
            using (SqlConnection conn = new SqlConnection(@"Data Source=server;Initial Catalog=db;Integrated Security=SSPI"))
            using (SqlCommand cmd = new SqlCommand(sqlGetUniqueParams, conn))
            {
                conn.Open();

                // get a SqlDataReader to read multiple rows
                using (SqlDataReader rdr = cmd.ExecuteReader()) //getting exception here when debugging
                {
                    // while there are more result rows.....
                    while (rdr.Read())
                    {
                        // grab the 0-index value from the result row
                        lUniqueParams.Add(rdr.GetString(0));
                    }
                }

                conn.Close();
                conn.Dispose();
            }

Im getting the exception at the following line of code:

using (SqlDataReader rdr = cmd.ExecuteReader())

Is my query not syntactically correct? The query does not perform well, does the rdr only read so long with no results and then give an exception? Am I missing something?

How many rows are there in the table? Try to remove distinct keywords, use union instead of union all and remove ordering. — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Oct 28, 2015 at 6:15
Then your table may be locked. Try to use nolock and see. 260 rows is nothing... — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Oct 28, 2015 at 6:18
whats the exception? what happens when you run the query directly in management studio? — Nick.Mc
– Nick.Mc, Commented Oct 28, 2015 at 6:20

Amit Tiwari · Accepted Answer · 2015-10-28 08:44:35Z

this is working for me please check this: as change table to [table] in your query and also change using (SqlCommand cmd = new SqlCommand(sqlGetUniqueParams, conn)) to using (SqlCommand cmd = new SqlCommand(sql, conn))

string sql = ";WITH getUniqueParams AS (" +
                               "SELECT DISTINCT [a] AS 'param' FROM [table]" +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [b] AS 'param' FROM [table]" + 
                               "UNION ALL " + 
                               "SELECT DISTINCT [c] AS 'param' FROM [table]" +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [d] AS 'param' FROM [table]" + 
                               "UNION ALL " + 
                               "SELECT DISTINCT [e] AS 'param' FROM [table]" +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [f] AS 'param' FROM [table]" +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [g] AS 'param' FROM [table]" + 
                               "UNION ALL " + 
                               "SELECT DISTINCT [h] AS 'param' FROM [table]" +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [i] AS 'param' FROM [table]" +  
                               "UNION ALL " + 
                               "SELECT DISTINCT [j] AS 'param' FROM [table]" +
                               "UNION ALL " + 
                               "SELECT DISTINCT [k] AS 'param' FROM [table]) " + 
                               "SELECT DISTINCT [param] FROM getUniqueParams ORDER BY [param]";   //the result of this statement to be stored in a string

            List<string> lUniqueParams = new List<string>();

            // set up SQL connection and command
            using (SqlConnection conn = new SqlConnection(@"Data Source=server;Initial Catalog=db;Integrated Security=SSPI"))
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                conn.Open();

                // get a SqlDataReader to read multiple rows
                using (SqlDataReader rdr = cmd.ExecuteReader()) //getting exception here when debugging
                {
                    // while there are more result rows.....
                    while (rdr.Read())
                    {
                        // grab the 0-index value from the result row
                        lUniqueParams.Add(rdr.GetString(0));
                    }
                }

                conn.Close();
                conn.Dispose();
            }

Thanks for your response. I fixed it yesterday per @ullas comment to my question: "Add cmd.CommandTimeout= 0; and check"; changing table to [table] wouldn't of mattered; table was just a dummy name for SO purposes ;) +1 because that could've been an issue, but you didn't know

Collectives™ on Stack Overflow

Timeout exception when running SQL query in C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related