Post
Ask Question

Faster SQL Server Search With ADO.NET and C#

Introducing CommandBehavior
 
ADO.NET allows database classes that let's you speed up your SQL operations including fetching data. If you're new to ADO.NET, I recommend you to start with the basics of ADO.NET here, What is ADO.NET.
 
When executing SELECT command in ADO.NET with the WHERE clause, the query may end up returning multiple rows. But what if you need one row only? We can achieve this by using the CommandBehavior that instructs database to return one row only.
 
CommandBehavior enum is passed in the ExecuteReader method of IDbCommand and depending on its value, the command returns different data. The following is a list of its values: 
  • Default - Returns everything.
  • CloseConnection - When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.
  • KeyInfo - The query returns column and primary key information. The provider appends extra columns to the result set for existing primary key and timestamp columns.
  • SchemaOnly - The query returns column information only. When using SchemaOnly, the .NET Framework Data Provider for SQL Server precedes the statement being executed with SET FMTONLY ON.
  • SequentialAccess - Provides a way for the DataReader to handle rows that contain columns with large binary values. Rather than loading the entire row, SequentialAccess enables the DataReader to load data as a stream. You can then use the GetBytes or GetChars method to specify a byte location to start the read operation, and a limited buffer size for the data being returned.
  • SingleResult - The query returns a single result set.
  • SingleRow - The query is expected to return a single row of the first result set. 

Let me present you the dbo Shema too

 
dbo is also the name of a schema, as discussed in Ownership and User-Schema Separation in SQL Server.
 
Why should I use it? The schema can split into one database one or more tables with the same name. Often used to separate rights to one or another department of the same company.
 
Example:
 
fin.Customers - Finnacial department; 
sel.Customers - Sales department. 

Let's Go

When you execute the ADO.NET reader command.ExecuteReader(), there is an optional parameter called "behavior".
 
To read an exclusive key you can add CommandBehavior.SingleRow, to make the command execute faster. 
 
Here is the C# code with comments: 
  1. using System.Threading.Tasks;  
  2.   
  3. namespace System.Data.SqlClient  
  4. {  
  5.   
  6.     /// <summary>  
  7.     /// Consumer class  
  8.     /// </summary>  
  9.     public static class ReadCustomersId  
  10.     {  
  11.         /// <summary>  
  12.         /// Get the Customer register  
  13.         /// </summary>  
  14.         /// <param name="id"></param>  
  15.         /// <param name="oCnn"></param>  
  16.         /// <returns></returns>  
  17.         public static DataTable Record(in int id, in SqlConnection oCnn)  
  18.         {  
  19.             // Sample 1  
  20.             // use dbo. or other schema before the table name  
  21.             return MyDataTableSql.GetDataTable($"SELECT customerName, customerCityId FROM dbo.Customer WHERE customerId={id};", oCnn);  
  22.   
  23.             // Sample 2  
  24.             // you can make join from a different schema to another  
  25.             return MyDataTableSql.GetDataTable($"SELECT customerName, customerCityId FROM fin.Customer LEFT JOIN dbo.Cities ON Customer.customerCityId=Cities.cityId WHERE customerId={id};", oCnn);  
  26.   
  27.             // Sample 3  
  28.             // you can join one row from the same table name in another schema  
  29.             return MyDataTableSql.GetDataTable($"SELECT CASE WHEN c.customerName IS NULL THEN f.customerName ELSE c.customerName END as customerName, " +  
  30.                                                $"CASE WHEN c.customerCityId IS NULL THEN f.customerCityId ELSE c.customerCityId END as customerCityId" +  
  31.                                                $" FROM dbo.Customer c LEFT JOIN fin.Customer f ON f.customerId=c.customerId WHERE c.customerId={id} OR f.customerId={id};", oCnn);  
  32.   
  33.             // Sample 4  
  34.             // you can apply too an SQL Server instuction: TOP 1  
  35.             // This tell to SQLServer get the first row that accept the where clause  
  36.             return MyDataTableSql.GetDataTable($"SELECT TOP 1 customerName, customerCityId FROM dbo.Customer WHERE customerId={id};", oCnn);  
  37.         }  
  38.     }  
  39.   
  40.     public static class MyDataTableSql  
  41.     {  
  42.   
  43.         /// <summary>  
  44.         /// Read Sql  
  45.         /// </summary>  
  46.         /// <param name="command"></param>  
  47.         /// <param name="oCnn"></param>  
  48.         /// <returns></returns>  
  49.         public static DataTable GetDataTable(string cSql, SqlConnection oCnn)  
  50.         {  
  51.             using (var command = new SqlCommand(cSql, oCnn, null))  
  52.             {  
  53.                 var source = new TaskCompletionSource<DataTable>();  
  54.                 var resultTable = new DataTable(command.CommandText);  
  55.                 SqlDataReader dataReader = null;  
  56.                 try  
  57.                 {  
  58.                     // CommandBehavior.SingleRow - This is the secret to execute the datareader to return only one row  
  59.                     dataReader = command.ExecuteReader(CommandBehavior.SingleRow);  
  60.                     resultTable.Load(dataReader);  
  61.                     source.SetResult(resultTable);  
  62.                 }  
  63.                 catch (Exception ex)  
  64.                 {  
  65.                     source.SetException(ex);  
  66.   
  67.                 }  
  68.                 finally  
  69.                 {  
  70.                     dataReader?.Close();  
  71.                 }  
  72.   
  73.                 return resultTable;  
  74.             }  
  75.         }  
  76.   
  77.         /// <summary>  
  78.         /// Read Sql Async  
  79.         /// </summary>  
  80.         /// <param name="cSql"></param>  
  81.         /// <param name="oCnn"></param>  
  82.         /// <returns></returns>  
  83.         public static async Task<DataTable> GetDataTableAsync(string cSql, SqlConnection oCnn)  
  84.         {  
  85.             using (var command = new SqlCommand(cSql, oCnn, null))  
  86.             {  
  87.                 var source = new TaskCompletionSource<DataTable>();  
  88.                 var resultTable = new DataTable(command.CommandText);  
  89.   
  90.                 try  
  91.                 {  
  92.                     // CommandBehavior.SingleRow - This is the secret to execute the datareader to return only one row  
  93.                     using (var dataReader = await command.ExecuteReaderAsync(CommandBehavior.SingleRow))  
  94.                     {  
  95.                         resultTable.Load(dataReader);  
  96.                         source.SetResult(resultTable);  
  97.                     }  
  98.                 }  
  99.                 catch (Exception ex)  
  100.                 {  
  101.                     source.SetException(ex);  
  102.                 }  
  103.                 finally  
  104.                 {  
  105.                 }  
  106.                 return resultTable;  
  107.             }  
  108.         }  
  109.   
  110.     }  
  111. }  
ABOUT THIS SAMPLE
 
Sample 1
 
"MyDataTableSql.GetDataTable($"SELECT customerName, customerCityId FROM dbo.Customer WHERE customerId={id};", oCnn);"
 
This uses the schema naming: dbo. before the table named "Customer".
 
Sample 2
 
"MyDataTableSql.GetDataTable($"SELECT customerName, customerCityId FROM fin.Customer LEFT JOIN dbo.Cities ON Customer.customerCityId=Cities.cityId WHERE customerId={id};", oCnn);"
 
Shows how to make a join with two schemas.
 
Sample 3
 
"MyDataTableSql.GetDataTable($"SELECT CASE WHEN c.customerName IS NULL THEN f.customerName ELSE c.customerName END as customerName, CASE WHEN c.customerCityId IS NULL THEN f.customerCityId ELSE c.customerCityId END as customerCityId FROM dbo.Customer c LEFT JOIN fin.Customer f ON f.customerId=c.customerId WHERE c.customerId={id} OR f.customerId={id};", oCnn);"
 
Shows how to join the same table on two schemas.
 
"MyDataTableSql.GetDataTable($"SELECT TOP 1 customerName, customerCityId FROM dbo.Customer WHERE customerId={id};", oCnn);"
 
Shows how to use the clause "TOP", in this case only one record: "1", "TOP 1".
 

Class Sample

 
public static class MyDataTableSql
 
It demonstrates how to use "CommandBehavior.SingleRow", with async sample too.
 
PRACTICE
 
 
Using the schema dbo improves the execution timing and makes SQLServer returns results faster.
 
To create a new schema you need to use the command:
 
CREATE SCHEMA [nameOfShema];
 
 
When you declare " TOP (1) " in the query statement, it makes faster, because the SQL Server will ignore else rows.
Use TOP (1) at your UPDATE queries when you are updating a single row, besides performing it makes a secure update that avoids a missing/wrong WHERE clause.
 
 
To put in practice just add the command behavior to existing ExecuteReader:
 
ExecuteReader(CommandBehavior.SingleRow).
 
The DataTable and ExecuteReader samples make the database queries run as smooth as possible.
 

CONCLUSION

 
Even if you are not working with a large databases, you can still perform these tips. In code, every second counts. Think performance, think speed and keep improving. 
 
Happy coding.
Next Recommended Reading How To Join Two DataTables Using C# In ADO.NET