C# ADO.Net What is CommandBehavior.SingleRow

T.Zacks 3,976 Reputation points
2021-04-29T17:29:50.92+00:00

I read this article https://www.c-sharpcorner.com/blogs/how-to-make-faster-sql-server-search-part-three

they said if i use dbo schema name before table name in select statement then sql server return result faster.....is it true? if yes then why result would return faster?

see this code sample

public static async Task<DataTable> GetDataTableAsync(string cSql, SqlConnection oCnn)  
        {  
            using (var command = new SqlCommand(cSql, oCnn, null))  
            {  
                var source = new TaskCompletionSource<DataTable>();  
                var resultTable = new DataTable(command.CommandText);  

                try  
                {  
                    // CommandBehavior.SingleRow - This is the secret to execute the datareader to return only one row  
                    using (var dataReader = await command.ExecuteReaderAsync(CommandBehavior.SingleRow))  
                    {  
                        resultTable.Load(dataReader);  
                        source.SetResult(resultTable);  
                    }  
                }  
                catch (Exception ex)  
                {  
                    source.SetException(ex);  
                }  
                finally  
                {  
                }  
                return resultTable;  
            }  
        } 

please tell me what CommandBehavior.SingleRow does in above code ? does it return only single row if even select return multiple data ? please tell me when to use CommandBehavior.SingleRow ?

Thanks

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,918 questions
0 comments No comments
{count} votes

Accepted answer
  1. Karen Payne MVP 35,006 Reputation points
    2021-04-29T17:53:55.09+00:00

    As per Microsoft Learn, SingleRow

    • The query is expected to return a single row of the first result set.
    • If your SQL statement is expected to return only a single row, specifying SingleRow can also improve application performance.

    Of course there are other contributing factors ranging from if an index in the database can assist, latency of connection. Also, if only one row is expected a DataTable is overkill, consider create a class with properties to what should be returned and use that instead.

    Awaiting can slow things down to and should only be used in your case if the data operations are more than say two seconds which is an eternity reading data. You could create a cancellationToken, set a time out and pass the token to your connection so if there is a problem connecting this will shorten the time to fail on a connection.

    0 comments No comments

0 additional answers

Sort by: Most helpful