Wednesday, December 19, 2007

Fixing Derby Driver error in NetBeans 6.0

I just installed NetBeans IDE 6.0. I also have SUN Java Studio, and NetBeans 5.5.1 already installed. They also seem to have a Derby database for them. When in NetBeans 6.0 I try to connect to any of the sample databases or even one that I create, I get message similar to "....unable to connect. cannot establish connection to using org.apache.derby.jdbc.ClientDriver (Unable to find a suitable driver).
To fix the issue I simply expand the NetBeans IDE | Services tab | Databases | Drivers | Java DB (4 of them) and add the path to the correct Derby database driver location. I just right clicked each of the drivers and chose Customize menu item. Then added the following path (customize it to your system) C:\Documents and Settings\MyProfileHere\.netbeans\6.0\jdbc-drivers\derbyclient.jar.

Wednesday, December 12, 2007

Custom Paging Helper Class

Below is the source I wrote to encapsulate methods needed to implement custom paging. This is useful for GridView or any other time you need to page through things. The class has all functionality accessible via static methods if you prefer. All the functionality that makes sense can also be used by an instance of the class. The advantage of creating an instance of the class is you just pass your parameters once, then all the parameters that you would need to specify in the static version of the methods are taken automatically from the instance of the class. I find the later to be less error prone due to mixing up parameters. Below the class is code that I typically use to implement custom paging for a GridView using System; using System.Collections.Generic; /// <summary> /// Encapsulates the algorithm for paging a list. All indexes are 1-based. /// </summary> public class PagingHelper { int totalNonPagedRows = 0; int pageSize = 0; int totalPageCount; int currentPageIdx = 0; public PagingHelper(int currentPageIdx, int totalNonPagedRows, int pageSize) { this.totalNonPagedRows = totalNonPagedRows; this.pageSize = pageSize; this.currentPageIdx = currentPageIdx; // do some quick basic calculations based on parameters above that we will need later this.totalPageCount = GetTotalPageCount(); } public static int GetTotalPageCount(int totalNonPagedRows, int pageSize) { int totalPageCount = (int)Math.Ceiling((double)totalNonPagedRows / pageSize); return totalPageCount; } public int GetTotalPageCount() { return PagingHelper.GetTotalPageCount(totalNonPagedRows, pageSize); } public static List<int> PagesIndexes(int totalPageCount) { List<int> pageIndexes = new List<int>(); for (int i = 1; i <= totalPageCount; i++) { pageIndexes.Add(i); } return pageIndexes; } public List<int> PagesIndexes() { return PagingHelper.PagesIndexes(totalPageCount); } /// <summary> /// Returns true if there is a page before the current page. Index is 1-based /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public static bool HasPreviousPage(int currentPageIdx) { if (currentPageIdx == 1) return false; else return true; } /// <summary> /// Returns true if there is a page before the current page. Index is 1-based /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public bool HasPreviousPage() { return PagingHelper.HasPreviousPage(currentPageIdx); } /// <summary> /// Returns true if there is a page after the current page. Index is 1-based /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public static bool HasNextPage(int currentPageIdx, int totalPageCount) { if (currentPageIdx == totalPageCount) return false; else return true; } /// <summary> /// Returns true if there is a page after the current page. Index is 1-based /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public bool HasNextPage() { return PagingHelper.HasNextPage(currentPageIdx, totalPageCount); } /// <summary> /// Returns the index of the page before the current page. /// If no page exists, the current page index is returned. Index is 1-based. /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public static int GetPreviousPageIndex(int currentPageIdx) { if (HasPreviousPage(currentPageIdx)) { return currentPageIdx - 1; } else { return currentPageIdx; } } /// <summary> /// Returns the index of the page before the current page. /// If no page exists, the current page index is returned. Index is 1-based. /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public int GetPreviousPageIndex() { return PagingHelper.GetPreviousPageIndex(currentPageIdx); } /// <summary> /// Returns the index of the page after the current page. /// If no page exists, the current page index is returned. Index is 1-based. /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public static int GetNextPageIndex(int currentPageIdx) { return currentPageIdx + 1; } /// <summary> /// Returns the index of the page after the current page. /// If no page exists, the current page index is returned. Index is 1-based. /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public int GetNextPageIndex() { return PagingHelper.GetNextPageIndex(currentPageIdx); } public static bool IsValidPageIdx(int pageIdx, int totalPageCount) { if (pageIdx >= 1 && pageIdx < totalPageCount) return true; else return false; } public bool IsValidPageIdx() { return PagingHelper.IsValidPageIdx(currentPageIdx, totalPageCount); } /// <summary> /// Returns the row index (1-based) for the first row on the specifiec page /// </summary> /// <param name="pageIndex">the index of the page to get the first row index</param> /// <returns>1-based row index</returns> public static int GetStartRowIndex(int pageIndex, int pageSize) { int startRowIndex = (pageIndex - 1) * pageSize + 1; return startRowIndex; } /// <summary> /// Returns the row index (1-based) for the first row on the specifiec page /// </summary> /// <param name="pageIndex">the index of the page to get the first row index</param> /// <returns>1-based row index</returns> public int GetStartRowIndex() { return PagingHelper.GetStartRowIndex(currentPageIdx, pageSize); } public static int GetCurrentPageIndexFromUrl(string queryParameterName) { string idx = System.Web.HttpContext.Current.Request[queryParameterName]; int currentPageIdx = 1; if (!string.IsNullOrEmpty(idx)) { currentPageIdx = Convert.ToInt32(idx); } return currentPageIdx; } public static int GetCurrentPageIndexFromForm(string formParameterName) { string idx = System.Web.HttpContext.Current.Request.Form[formParameterName]; int currentPageIdx = 1; if (!string.IsNullOrEmpty(idx)) { currentPageIdx = Convert.ToInt32(idx); } return currentPageIdx; } } Here is the code I used to populate a previous, next buttons and other labels that show information about the current page, etc. I am using a ObjectDataSource and using my Custom Objects and DAL that I have referenced in some of my other blog entries. protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { int currentPageIdx = PagingHelper.GetCurrentPageIndexFromUrl("PageIdx"); lblCurrentPage.Text = Convert.ToString(currentPageIdx); int startRowIndex = PagingHelper.GetStartRowIndex(currentPageIdx, PAGE_SIZE); e.InputParameters.Clear(); e.InputParameters.Add("startRowIndex", startRowIndex); e.InputParameters.Add("maximumRows", PAGE_SIZE); e.InputParameters.Add("orderBy", e.Arguments.SortExpression); e.InputParameters.Add("totalNonPagedRowCount", e.Arguments.TotalRowCount); } protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e) { // Get Values from form and url lblTotalUnpagedRow.Text = Convert.ToString(e.OutputParameters["totalNonPagedRowCount"]); int totalUnpagedRowCount = Convert.ToInt32(lblTotalUnpagedRow.Text); int currentPageIdx = PagingHelper.GetCurrentPageIndexFromUrl("PageIdx"); // do calculations int totalPageCount = PagingHelper.GetTotalPageCount(totalUnpagedRowCount, PAGE_SIZE); // configure previous and next links linkPrevious.Enabled = PagingHelper.HasPreviousPage(currentPageIdx); linkPrevious.NavigateUrl = string.Format("GridViewPage.aspx?PageIdx={0}", PagingHelper.GetPreviousPageIndex(currentPageIdx)); linkNext.Enabled = PagingHelper.HasNextPage(currentPageIdx, totalPageCount); linkNext.NavigateUrl = string.Format("GridViewPage.aspx?PageIdx={0}", PagingHelper.GetNextPageIndex(currentPageIdx, totalPageCount)); } Here is another example, but using buttons instead of links for paging. The advantage of this is that sorting can be done without doing redirects. The above code doesn't really support sorting of custom paging without redirecting the url due to the fact that the current page index is taken from the url and to change the url we need to redirect. This can make page hit stats a little misleading. The good news is that it is Google friendly. Since it uses url instead of javascript to do the navigation (like LinkButtons or Buttons) Google can follow them. An alternative to this is use javascript and have a page that you tell Google to explicitly go to. This is a page that doesn't have to be an end user page, it just has to have your site map on it. The choice is yours. Here is the code that supports custom paging and sorting. protected void GridView1_Sorting(object sender, GridViewSortEventArgs e) { PageIndex = 1; } protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { int startRowIndex = PagingHelper.GetStartRowIndex(PageIndex, PAGE_SIZE); e.InputParameters.Clear(); e.InputParameters.Add("startRowIndex", startRowIndex); e.InputParameters.Add("maximumRows", PAGE_SIZE); e.InputParameters.Add("orderBy", e.Arguments.SortExpression); e.InputParameters.Add("totalNonPagedRowCount", e.Arguments.TotalRowCount); } protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e) { int totalNonPagedRows = Convert.ToInt32(e.OutputParameters["totalNonPagedRowCount"]); PagingHelper paging = new PagingHelper(PageIndex, totalNonPagedRows, PAGE_SIZE); // populate labels that tell paging information lblCurrentPage.Text = Convert.ToString(PageIndex); lblTotalUnpagedRow.Text = Convert.ToString(totalNonPagedRows); // enable / disable navigation buttons btnNext.Enabled = paging.HasNextPage(); btnPrevious.Enabled = paging.HasPreviousPage(); // populate drop down list of pages ddlPages.DataSource = paging.PagesIndexes(); ddlPages.DataBind(); ddlPages.SelectedValue = Convert.ToString(PageIndex); } protected void ddlPages_SelectedIndexChanged(object sender, EventArgs e) { PageIndex = Convert.ToInt32(ddlPages.SelectedValue); GridView1.DataBind(); } public int PageIndex { get { return ViewState["PageIndex"] == null ? 1 : (int)ViewState["PageIndex"]; } set { ViewState["PageIndex"] = value; } } protected void btnNext_Click(object sender, EventArgs e) { PageIndex = PagingHelper.GetNextPageIndex(PageIndex); GridView1.DataBind(); } protected void btnPrevious_Click(object sender, EventArgs e) { PageIndex = PagingHelper.GetPreviousPageIndex(PageIndex); GridView1.DataBind(); } And finally, below is how you would use similar code (I added a dropdown list for the user to select the page to go to) but doesn't use the ObjectDataSource. I find this code easier to maintain and read since it is pretty linear; unlike the ObjectDataSource that forces you to figure out the correct events to put your code it. Well, that is IF all you are doing is displaying data. If you want the other CRUD operations, or if you want to have the columns automatically created for you then I would use the ObjectDataSource. It is the new way in ASP.Net 2.0 anyway. Might as well go with the flow. :) Again, either one works, it is up to you. public partial class GridViewPage2 : System.Web.UI.Page { int PAGE_SIZE = 3; PagingHelper paging; protected void Page_Load(object sender, EventArgs e) { GridView1.EnableViewState = false; BindUI(); } protected void BindUI() { PersonDAL dal = new PersonDAL(); int startRowIndex = PagingHelper.GetStartRowIndex(PageIndex, PAGE_SIZE); int totalNonPagedRowCount; List<PersonEntity> people = dal.FetchPersonGetAllPaged(startRowIndex, PAGE_SIZE, GridView1.SortExpression, out totalNonPagedRowCount); GridView1.DataSource = people; GridView1.DataBind(); PagingHelper paging = new PagingHelper(PageIndex, totalNonPagedRowCount, PAGE_SIZE); // populate labels that tell paging information lblCurrentPage.Text = Convert.ToString(PageIndex); lblTotalUnpagedRow.Text = Convert.ToString(totalNonPagedRowCount); // enable / disable navigation buttons btnNext.Enabled = paging.HasNextPage(); btnPrevious.Enabled = paging.HasPreviousPage(); // populate drop down list of pages ddlPages.DataSource = paging.PagesIndexes(); ddlPages.DataBind(); ddlPages.SelectedValue = Convert.ToString(PageIndex); } protected void GridView1_Sorting(object sender, GridViewSortEventArgs e) { PageIndex = 1; } protected void ddlPages_SelectedIndexChanged(object sender, EventArgs e) { PageIndex = Convert.ToInt32(ddlPages.SelectedValue); GridView1.DataBind(); } public int PageIndex { get { return ViewState["PageIndex"] == null ? 1 : (int)ViewState["PageIndex"]; } set { ViewState["PageIndex"] = value; } } protected void btnNext_Click(object sender, EventArgs e) { PageIndex = PagingHelper.GetNextPageIndex(PageIndex); BindUI(); } protected void btnPrevious_Click(object sender, EventArgs e) { PageIndex = PagingHelper.GetPreviousPageIndex(PageIndex); BindUI(); } } Tips: Set GridView.AllowSorting = true. If you get "The data source 'ObjectDataSource1' does not support sorting with IEnumerable data. Automatic sorting is only supported with DataView, DataTable, and DataSet.", then you need to specify the ObjectDataSource.SortParameterName to the name of your Select parameter in your DAL.

Tuesday, December 11, 2007

Getting the output parameter after executing your stored procedure.

If you have a stored procedure that has an OUTPUT parameter it, it requires different syntax to execute and check the output parameter when you are using Query Analyzer or SQL Management Studio. Let's assume you have a very simple stored procedure that select 5 rows and just returns the number of rows in a table as an output parameter. create PROCEDURE [dbo].GetRows @RowCount int OUTPUT AS select top 5 * from Person select @RowCount = count(1) from Person GO Here is the syntax to test it from Query Analyzer or SQL Management Studio. Declare @TotalCount int exec GetRows @TotalCount OUTPUT select @TotalCount If you want to do this from ADO.Net, here is the syntax. Once again, there is a trick. You must close the reader before checking the value of the output parameter. In this example we are using a SQL Helper class similar to one of the early MS DAAB and custom objects, but this is not the important piece of code. The important thing here is that when using a reader, you must close the reader before accessing the output parameter. If you use a DataAdapter to load results into a heavy like DataTable or DataSet. public List<PersonEntity> FetchRows(out int rowCount) { SqlDataReader reader = null; List<PersonEntity> entities = new List<PersonEntity>();

try { _sqlServer = new SqlServer(_sqlConnStr);

SqlParameter[] parameters = new SqlParameter[1]; parameters[0] = _sqlServer.MakeOutParam("@RowCount", SqlDbType.Int, -1);

reader = _sqlServer.ExecuteReader("GetRows", parameters);

while (reader.Read()) { entities.Add(FillEntityFromReader(reader)); }

// we must close the reader before we can read an output parameter reader.Close(); rowCount= (int)parameters[3].Value;

} catch (Exception ex) { throw ex; } finally { // a little overkill, but just for clarity, close everything explicitly if (reader != null && !reader.IsClosed) { reader.Close(); }

if (_sqlServer != null) { _sqlServer.Close(); _sqlServer.Dispose(); } }

return entities; } To call this method from C#, you would do something like: int count; FetchRows(out count); // use count here

Friday, December 7, 2007

Sorting and paging in SQL 2005 the easy and efficient way

The example below shows how to write an efficient, easy to maintain, and easy to understand way of using SQL 2005 features to implement sorting and paging of data. @StartRowIndex is 1 based, and is the row number (NOT the PAGE number). The @StartRowIndex can be calculated by int startRowIndex = Math.Ceiling(pageIndex * pageSize); though some type conversion will be needed in C#. The Order by CASE statements could be one long case statement with logs of when as long as the types of each column in the CASE statement is the same. I just created a new case statement for simplicity. However, you could create two in this case. One case statement that could have two when clauses (NAME, SEARCH_CODE), and another CASE statement with only one when clause since it is of a different data type that the other two columns. I have not found any performance issue with this. This includes using SQL Profiler to examine read, writes, duration. The number of case statements doesn't seem to have any recordable impact.
create proc GetPersonPage2005 @StartRowIndex int, @MaximumRows int, @Orderby varchar(50) as
SELECT [NAME], SEARCH_CODE, CREATED FROM (SELECT [NAME], SEARCH_CODE, CREATED, ROW_NUMBER() OVER ( ORDER BY -- add columns to sort by here CASE @Orderby WHEN 'NAME DESC' THEN [NAME] END DESC, CASE @Orderby WHEN 'NAME ASC' THEN [NAME] END ASC, CASE @Orderby WHEN 'SEARCH_CODE DESC' THEN SEARCH_CODE END DESC, CASE @Orderby WHEN 'SEARCH_CODE ASC' THEN SEARCH_CODE END ASC, CASE @Orderby WHEN 'CREATED DESC' THEN CREATED END DESC, CASE @Orderby WHEN 'CREATED ASC' THEN CREATED END ASC ) as RowNum FROM ALL_PERSON e ) as Tbl WHERE RowNum BETWEEN @StartRowIndex AND (@MaximumRows + @StartRowIndex - 1)
Order by RowNum ASC go
An example of using this stored procedure would be:
To sort by SEARCH_CODE in Ascending Order use:

GetPersonPage2005 3, 6, 'SEARCH_CODE ASC'

or to sort by SEARCH_CODE in Descending Order: GetPersonPage2005 3, 6, 'SEARCH_CODE DESC'

Thursday, December 6, 2007

Refresh Schema on GridView doesn't work with Custom Objects

Let's say you have a custom object (instead of a DataSet or DataTable). You use this custom object for an ObjectDataSource and bind it to a GridView. You try to autogenerate the columns or Refresh Schema menu item and it doesn't work either. It says it can't do it. What do you do. Chances are that in your Get / Select method in your custom object you are returning something like an array. The easiest way is to instead use the List generic collection. In most cases it is a better performer and better choice than an ArrayList, but is type safe, and have the same basic features. For more details, see http://msdn2.microsoft.com/en-us/library/6sh2ey19.aspx on which one you should use. In our case, we need to use the List generic collection so that the GridView can get the columns from it. If you wanted to create your own collection (why I don't know) you could probably do something like is described in this blog (http://blogs.msdn.com/msdnts/archive/2007/01/19/how-to-bind-a-datagridview-column-to-a-second-level-property-of-a-data-source.aspx). Please note, I have not tried this nor do I want to. It looks way to complicated for something that should be so basic! Granted the problem they are trying to solve is a more complex, but I am willing to bet the needed interfaces are the same. With that said, here is an example of how you might use a generic List instead of an ArrayList. public List<PersonEntity> FetchPersonGetAll() { SqlDataReader reader = null; List<PersonEntity> entities = new List<PersonEntity>(); try { reader = daab.ExecuteReader("up_FetchPersonGetAll"); while (reader.Read()) { entities.Add(FillEntityFromReader(reader)); } } catch (Exception ex) { throw ex; } finally { // close reader and connection here } return entities; }

Make your FormView handle nulls better

The FormView is a very powerful control that is very nice in additional in ASP.Net 2.0. It handles nulls fairly well. See the ADO.Net QuickStart (http://dotnetjunkies.com/QuickStartv20/aspnet/doc/data/advanced.aspx#nulls) for details on this. If you are using custom objects depending on how your custom objects are designed you can just modify the parameters as described on this blog. http://www.livingincode.com/web/blog/post/FormView-and-ObjectDataSource-with-nullable-types.aspx. However, if you have custom objects that for example don't use individual properties as parameters to the CRUD methods and instead pass entity classes, then the trick discussed in this blog won't work. However, someone commented that they added a few lines of code to solve the issue. I found that the few lines of code is the only way that works under my scenario (the one that I just described that didn't work). Here are the two places I had to add a small amount of code that loops through all the values being inserted or updated and if the string from the form is an empty string, then it is converted to a null. The null is then automatically bound to my custom object. The reason I can bind null to int, long, DateTime, etc is because I am using the Nullable versions of these. For example, int?, long?, and DateTime?. In my custom object I then convert nulls to DBNull when I do database inserts and updates. It is clean and easy to implement. protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e) { foreach (DictionaryEntry entry in e.Values) { if (string.IsNullOrEmpty(entry.Value as string)) { e.Values[entry.Key] = null; } } }
protected void FormView1_ItemUpdating(object sender, FormViewInsertEventArgs e) { foreach (DictionaryEntry entry in e.NewValues)
{ if (string.IsNullOrEmpty(entry.Value as string)) { e.NewValues[entry.Key] = null; } } }
Here is the idea of what my custom entity looks like. Note this is a very light weight object. It doesn't really do anything other than hold the table data. In my real object, I have properties that expose the protected variables shown here, but I have not shown here to reduce the amount of clutter here.

public class PersonEntity { protected int? _personID; protected string _firstName; protected string _lastName; protected string _street1; protected string _street2; protected string _city; protected string _state; protected string _zipCode; protected string _createdBy; protected DateTime? _createdDate; protected int? _age; protected int? _height; protected int? _weight; // NOTE: Properties that expose these member variables have been removed to reduce clutter. }

I have a DAL object that actually does all the CRUD operations on the entity. Here is a the method that is used when I need the load the data.

protected PersonEntity FillEntityFromReader(SqlDataReader reader) { PersonEntity entity = null;

try { if (reader != null && !reader.IsClosed) { entity = new PersonEntity(); entity.PersonID = reader[0] as int?; entity.FirstName = reader[1] as string; entity.LastName = reader[2] as string; entity.Street1 = reader[3] as string; entity.Street2 = reader[4] as string; entity.City = reader[5] as string; entity.State = reader[6] as string; entity.ZipCode = reader[7] as string; entity.CreatedBy = reader[8] as string; entity.CreatedDate = reader[9] as DateTime?; entity.Age = reader[10] as int?; entity.Height = reader[11] as int?; entity.Weight = reader[12] as int?; } } catch (Exception ex) { throw ex; }

return entity; } Here is how I check the properties for null so I know whether to convert the parameter to DBNull or not when I am doing update or insert operations. I have just pasted a some example for different data types. I am using customized version of the MS DAAB. In my version the last parameter is what determines if the DAAB inserts DBNull or the value passed. You'll notice string is different animal. It already can be null and does not use the HasValue, instead it uses string.IsNullOrEmpty() method. parameters[1] = _sqlServer.MakeInParam("@FirstName", System.Data.SqlDbType.NVarChar, 50, entity.FirstName, string.IsNullOrEmpty(entity.FirstName)); parameters[9] = _sqlServer.MakeInParam("@CreatedDate", System.Data.SqlDbType.DateTime, 0, entity.CreatedDate, !entity.CreatedDate.HasValue); parameters[10] = _sqlServer.MakeInParam("@Age", System.Data.SqlDbType.Int, 0, entity.Age, !entity.Age.HasValue);

Tuesday, December 4, 2007

ADO.NET performance unleashed

Introduction It seems that everyone has a different opinion about the best way to access data in a database and present it to the user for viewing or editing. Some say a DataTable or DataSet offer the most flexibility, less code, etc. Other people say no roll your own custom objects since they are the fastests and most flexible. There are certainly pros and cons of each, and I will highlight some of the them. This blog will not get into which is better since every situation is different. Sometimes it makes sense to choose one over the other. What I will cover is raw performance and generally why you may use one over the other. It is up to you decide if it makes sense in your situation which method to use. Below I will explore the performance of four different ways of accessing data from a SQL Server database. These include: Custom (objects that I used a code generator to create), Doodads (a framework that also uses code generator to create specific custom objects), DataTable, and finally DataSet. DataTable DataTable and a DataAdapter to load data from the database is a very common way to manage the access of your data. DataTables are disconnected objects meaning that they give the connection back after the object is loaded with data. This is important to handle many connections a second. You don't want your connections staying open very long because this is a limited resource. Once the limit is reached your application will fail. At the very least, it will get sluggish. DataTables require little coding (virtually none if using Designer in Visual Studio 2005), so they are nice for RAD. DataSet DataSet works very much the same as DataTable except a DataSet potentially contains multiple DataTables. This means that any performance DataTables has, DataSet will be a little slower just due to the overhead of creating the DataSet. Doodads Doodads is a framework that is open source and included with My Generation ( a code generation tool). Its biggest strength is that it handles complex transactions between tables at the application level, and that you can perform adhoc queries against a single table without any stored procedures or writing any SQL. This is a powerful feature to have when building some query pages. Doodads uses DataAdapters and DataTables under the hood so they are prone to the same performance issues as DataTables. Custom Objects Custom Objects can take a variety of forms. Typically they are created with My Generation or CodeSmith. If coded properly, these objects can extremely light weight and fast. This comes at the cost of limited functionality or at least functionality that you need to implement. In many cases, especially for read only data these objects perform the best. This assumes that your custom objects do not use DataTables and DataAdapters. If you do, they will perform no better than a DataTable and probably somewhere between a DataTable and a DataSet due to the overhead. With that said, the choice is yours. Here are some real numbers that I recorded from my tests. Results are taken from a SQL Server 2005 Developer Edition, and the database table had six rows to virtually guarantee each query comes back in the same amount of time regardless of SQL Server caching, etc. I ran two tests. In the first test, I loaded a record by primary key. In the second test, I loaded multiple records (select all rows). Here is what I found. Units for the numbers are ticks. Single Record Load Test Custom Objects (which use DataReader) is the fastests. They are approximately 30% faster than DataAdapter / DataTable / DataSet. Doodads does not perform as well in comparison because over its additional overhead.
Custom ObjectsDoodadsDataTableDataSet
Average 1403207517711825
Minimum1083156314471471
Maximum2717714633252909
Standard Deviation 298721227233
Below you can see that Custom Objects do much better overall that any of the other methods. Doodads is the worst as expected due to basically being DataTables with additional overhead. 6 Record Load Test

As you might expect DataReader is the best performer when loading 6 records into array of entities. The standard deviation is even the least which means less volitility. .Net does not give an API to determine how much memory each class uses, but I used Red Gate Ants profiling tool to see that the number of objects created by DataTable and DataSet is MUCH greater than a Custom Object. This was not a surprise to me since DataTable and DataSet have much more functionility for tracking changes, etc than my Custom Object has. This is important when caching data.

Custom ObjectsDoodadsDataTableDataSet
Average 1374198818331841
Minimum1179170015941644
Maximum2174498627802937
Standard Deviation 177364209186

Below you can see that Custom Objects out performs the other Methods in nearly all cases. The gap increases between Custom Objects and the other methods now that we are loading multiple records. You can see at the end of the tests the graph becomes more volatile. I believe this is because of contention for the connections. At that point, we are starting to run out of connections and waiting is starting to occur. Some of the spikes are most likely due to garbage collection and memory allocation, but I can't prove that theory. These tests are performed in a loop that loops for 100 times, so garbage collection is sure play a part.

The big picture While Custom Objects are the best performer in general, its limited feature set may require more features be added or a framework be used that supplies the additional functionality. It is important to remember that the biggest bottleneck on most websites is database connectivity. This is fairly obvious in my opinion, but can also be seen if you profile your existing application (no matter how you connect to the database) the slowests methods will be the ones that connect to the database. It makes sense to spend your time optimizing here. It is important to remember that the best way to optimize database connectivity is to limit the times it is executed. One way to do this is to cache results, perhaps use ViewState and only load data on page load the first time, cache pages, etc. This is the largest reward. However, this is not always an option. In those cases, it is good to know which method here is going to give you the best performance. References These references more or less mirror what I am saying, and also provide some other solutions that may be helpful in some cases. If you have long queries and lots of users, you may want to keep those worker threads free by using Asynchronoous queries against the database. http://msdn.microsoft.com/msdnmag/issues/07/03/WickedCode/ Are you scared of using ViewState, this article may change your mind (if you're using ASP.Net 2.0 or greater). http://msdn.microsoft.com/msdnmag/issues/04/10/ViewState/ This article does a similar comparison to what I did, but focuses on Requests per second and Response time when the application is put under different loads. This comes to very similar conclusions, but from a different angle. http://msdn2.microsoft.com/en-us/library/ms978388.aspx More of the same, but also takes OLEDB into account. Think twice before using OLEDB. http://www.devx.com/vb2themax/Article/19887/1954?pf=true

Best Practices from TechEd http://www.theserverside.net/news/thread.tss?thread_id=26210

Saturday, December 1, 2007

Dynamic Sql Tricks

Dynamic SQL is easy enough, but is often susceptible to SQL Injection. One way around SQL Injection is to use parameters always. This is easier said than done. Here are some examples that show how this can be done. They get more complex with each example. This example shows how to use sp_executesql with parameters. create PROCEDURE Test3 @MinNumberOfGuests INT, @MaxNumberOfGuests INT AS DECLARE @Sql NVARCHAR(MAX) SET @Sql = 'select * from Site where MinNumberOfGuests = @MinNumberOfGuests and MaxNumberOfGuests = @MaxNumberOfGuests' EXEC sp_executesql @Sql, N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT', @MinNumberOfGuests, @MaxNumberOfGuests GO Test3 25, 300 This shows how to handle parameters that are null which in this case signifies that the parameter is not to be in the where clause. The important thing to note is that "select top 0 " followed by all the parameters. This allows the for the value parameters of sp_executesql to always be included since there is no way to conditionally include them. It adds a very small amount of overhead to select variables, and top 0. create PROCEDURE Test5 @MinNumberOfGuests INT, @MaxNumberOfGuests INT AS DECLARE @Sql NVARCHAR(MAX) SET @Sql = 'select * from Site where MinNumberOfGuests = @MinNumberOfGuests and MaxNumberOfGuests = @MaxNumberOfGuests; select top mailto:0@MaxNumberOfGuests EXEC sp_executesql @Sql, N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT', @MinNumberOfGuests, @MaxNumberOfGuests GO Test5 25, 300 This is a more robust implementation of the above. create PROCEDURE Test4 @MinNumberOfGuests INT = null, @MaxNumberOfGuests INT = null AS DECLARE @Sql NVARCHAR(MAX) DECLARE @AllSql NVARCHAR(MAX) SET @Sql = 'select * from Site WHERE 1 = 1 ' IF @MinNumberOfGuests is not null BEGIN Set @Sql = @Sql + ' AND MinNumberOfGuests = @MinNumberOfGuests' END IF @MaxNumberOfGuests is not null BEGIN Set @Sql = @Sql + ' AND MaxNumberOfGuests = @MaxNumberOfGuests' END Set @AllSql = @Sql + N'; Select top 0 @MinNumberOfGuests, @MaxNumberOfGuests' EXEC sp_executesql @AllSql, N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT', @MinNumberOfGuests, @MaxNumberOfGuests GO Test4 25, null Test4 null, 300 Test4 25, 300 Test4 null, null