High-Performance .NET Application Development & Architecture

1. You want to retrieve one single value or item:
   
   Use the command class's ExecuteScalar method.
   
   

   object dbValue = objCommand.ExecuteScalar();

   
   Remember, this method always returns an object that you have to convert/cast to your specific type.
   
   
2. You need to retrieve just a single row of data instead:
   
   Use the ExecuteReader's SingleRow Command Behavior
   
   

3. objDataReader = objCommand.ExecuteReader(CommandBehavior.SingleRow);

   
   
   
4. You want to retrieve multiple database rows and automatically close your connection:
   
   Use a data reader.
   
   

   objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);

   
   
   Not fast enough, then read all data sequentially as a data stream via ExecuteReader's SequentialAccess Command Behavior. Note that although obvious, I should point out all fields are to be read and access in sequential order starting from 0, dependent on your SQL query columns order.
   
   
   

   objDataReader = objCommand.ExecuteReader(CommandBehavior.SequentialAccess);

   
   
   Still not enough? Read all fields by index position or strongly-typed accessor method, instead of field name
   
   
   

   string value = objDataReader(0); or try string value = objDataReader.GetString(0);

   
   
   *The GetString is one of the Datareaders type specific members to read columns thus reducing any runtime conversions.
   
   And get your results with your preferred method of datareader field reads:
   
   
   

   while (objDataReader.Read() == true) { Response.Write (objDataReader(0)); }

   
   
   
5. You'd like to open a database connection, read your data, then close it all pretty quickly without too much code
   
   This can be accomplished only in C#, with the using statement.
   
   

   using (SqlConnection string) {     // Do database work   } // Now connection is automatically closed

   
   
   
6. Best method to retrieve values from a Stored Procedure, instead of looping through rows with a datareader:
   
   

   objCommand.ExecuteNonQuery();

   
   
   
7. Want even better functionality when working with SQL Server? Then use Microsoft's new Data Access Application Block (DAAB) 2.0 . .NET introduces the SqlHelper class that dramatically cut's down development time by allowing you to execute commands against a SQL database, with Sprocs, all within a few lines of code (about 75% less code than you would typically need)!
   
   The example below demonstrates how incredibly easy it is to implement a Datareader to connect to and query a SQL database, whether using simple SQL syntax or a SQL Parameter, and all with one line of code!
   
   

   <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="Microsoft.ApplicationBlocks.Data" %>   <script language="C#" runat="server">   void Page_Load (Object Source, EventArgs E) { string strConn = "server=(local);uid=sa;pwd=;database=northwind;"; int categoryID = 1;   //Using a SQL Query //Connection String, Command Type (Text), and SQL Query SqlDataReader objDataReader = SqlHelper.ExecuteReader_ (strConn, CommandType.Text, "SELECT * FROM Products");     //Using a Sproc //Connection String, Command Type (Stored Procedure), and Parameter SqlDataReader objDataReader = SqlHelper.ExecuteReader_ (strConn, "getProductsByCategory", categoryID);   while (objDataReader.Read() == true) { Response.Write (objDataReader[1].ToString() + "<BR>"); }   //Close and clear our object objDataReader.Close(); objDataReader = null; } </script>

   
   
   That's amazing, considering this would usually be accomplished with roughly more or less 10 lines of code!
   
   The SQLHelper class used here provides the same amazing robustness for the Dataset. Not only that, but you're allowed transactional and Dataset database updating, as well as exception management!
   
   Now to implement this class into your project, you can do this one of two ways. One, as a private assembly by simply xcopying the Microsoft.ApplicationBlocks.Data.dll into your project's bin folder, or two, as a Shared Assembly whereby you install the dll into the Global Assembly Cache using .NET's gacutil.exe command line utility while noting that it has to be assigned a Strong Name. This can be accommodated using the Assembly Linker (Al.exe) utility program. Finally, within Visual Studio.NET, it's all a matter of simply referencing the DLL, by importing into your project.
   
   This sure is a cool new class, huh? I think so too. Download the Data Access Application Block for .NET v2 here.
   
   
   Finally, the DataGrid. Now, we've all use the DataGrid and probably more so than any other .NET control, but it can be cumbersome when binding and dealing with large sets of data, so every little bit of optimization helps, and one such technique involves to avoid using the Eval runtime reflection DataBinder.Eval(Container.DataItem ("Item")) expression in the Template Columns when data binding your DataGrid.
   
   Instead, convert the DataGrid's DataItem to its specific type which is a DataRowView (if your binding with a DataReader then the item type would be IDataRecord - check out Dynamic DataGrid Paging and Sorting Using A DataReader for DataReader/DataGrid binding), and take away some of the runtime burden, like so:
   
   
   
   

   <%# CType(Container.DataItem, DataRowView) ("Item") %>

   
   Still not satisfied? Then an even better method of data binding is accomplished by ordinal position, like in the aforementioned DataReader example #3:
   
   
   

   <%# CType(Container.DataItem, DataRowView) (0).ToString() %>

   
   
   This last method would be the fastest, but you have to be careful that in modifying your query fields and or order, you will most likely break your app. So you have to be sure what your query contains and is doing.
   
   
   
   

So there you have most ADO.NET data access scenarios that you'll run into while creating your application. Pretty sure you've got enough now to get you moving.

Now that we've gotten our Tiers taken care of, we'll discuss methods that'll ensure us from having our application blow up in our faces - Error Trapping and Handling.