Faster SQL Server Search With ADO.NET and C#

This post will teach you how to read a single line from SQL Server using CommandBehavior.CommandBehavior allows us to instruct the command to read one row only even if there are more than one rows there in the WHERE clause.

Introducing CommandBehavior
ADO.NET allows database classes that let’s you speed up your SQL operations including fetching data.
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:

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.