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

Saturday, November 17, 2007

Friday, November 16, 2007

Stopping form from submitting

Let's assume you have a form on a web page. It has an textarea and a form submit button. You want to alert the user that the textarea is required before submitting the form. You don't want the form to submit if validation fails, otherwise submit the form as normal. Here is the sample of how to handle this. NOTE: This works for ASP.Net also. Besure to include the return keyword in the onclick of the button. <html> ... <body> <form ... > function validate() { var myTextArea = document.getElementById('MyTextArea'); var val = myTextArea.value; if (val == "") { alert("The text area is required. Please enter text and try again."); return false; } else { return true; } } <textarea id="MyTextArea" id="MyTextArea" cols="20" rows="2"></textarea> <input id="Submit1" type="submit" value="submit" onclick="return validate();"/> </form> </html>

Thursday, November 8, 2007

Query by date (no time) in SQL Server

This is a simple one. This technique can be used to search for rows where the MyDateColumn has the date specified (as a string) and the time doesn't matter. select * from MyTable where Convert(VARCHAR, MyDateColumn, 101) = '10/31/2007' It can also be used in the select statement to format the output of MyDateColumn without the time portion. select Convert(VARCHAR, MyDateColumn, 101) = '10/31/2007' from MyTable

Tuesday, November 6, 2007

Highlighting matches using Regular Expressions in C#

Below is sample code on how to find matches to a particular regular expression in some body of text and surround each match with some html tags. For example, if the body of text is: string body = "This is just a test and is the body of text that we are trying to search"; After calling HighlightExpression(body); body will be This is just a <span class="highlight">test</span> and is the body of text that we are trying to search public string HighlightExpression(string body) { try { string expression = "test"; body = Regex.Replace(body, expression, new MatchEvaluator(HighlightUrls)); } catch (ArgumentException ex) { // Syntax error in the regular expression } } // this is a delegate that gets called for each match public string HighlightMatch(Match m) { return "<span class=\"highlight\">" + m.Value + "</span>"; }

Friday, November 2, 2007

The coolest zip tool ever!

7Zip is the coolest compression tool I have seen, and the best part is that it is FREE! Check it out at www.7-zip.org. The interface is pretty nice, but the format support is just awesome. It even supports .iso images.
  • Packing / unpacking: 7z, ZIP, GZIP, BZIP2 and TAR
  • Unpacking only: RAR, CAB, ISO, ARJ, LZH, CHM, Z, CPIO, RPM, DEB and NSIS
  • It is even integrated into Windows Explorer.

    Opening up Scheduled Tasks for remote servers from the command line

    Imagine you have Scheduled Tasks on many servers. While it is easy enough to open a Window in Windows Explorer and type something like file:////myserver/Scheduled to open the Scheduled Tasks window for a specified server, this does not work if you type this into the command prompt or in a batch file. What I found is that you can open a shortcut from the command line though. So, open the Scheduled Tasks as described above, and then drag the icon in the left part of the address bar to your desktop (or other directory). This will create a shortcut. You can then type this into a command line or batch file and the Scheduled Tasks Window will open for the specified server. This is not a huge time saver, but it is convenient to have a list of icons to click instead of trying to remember all the servers, type them in, etc. If you have lots of servers that need to be updated it can be very slow waiting for the Scheduled Tasks windows to appear. I recommend putting them in a batch file with the name of the shortcut (you will need to enclose in double quotes if the name has spaces in it) on each line. Here is an example of a batch file that opens 3 servers one after another. "Scheduled Tasks on server1.lnk" "Scheduled Tasks on server2.lnk" "Scheduled Tasks on server3.lnk" Now when you need to update the scheduled tasks all you do is run this batch file, take a few minute break, come back and you will have all your Scheduled Tasks windows open and ready for editing. This beats doing it one at a time if you ask me. :) TIP: You really only need to create one shortcut. Then make a copy of it, and open it in notepad. It is fairly cryptic looking. Just look for the server name and change it to the server you want it to point to. Save the text file. That is it. No you have a shortcut that works, and you didn't have to wait to connect to Scheduled Tasks to create it.

    Thursday, October 25, 2007

    Getting to the result that was returned when selecting in an ObjectDataSource

    ASP.NET 2.0 has some really nice drag and drop features. Let's pretend you have a GridView and and ObjectDataSource that you have configured on the page. No you want to do something interesting with the results that the ObjectDataSource returns before it is passed on to the GridView. This could be any number of things. For example, maybe timezone conversion, rounding, etc. The ObjectDataSource provides a Selected event that you can hook into. Here is an example that

    protected void BLL_Selected(object sender, ObjectDataSourceStatusEventArgs e) { DataTable dt = e.ReturnValue as DataTable; // do interesting stuff here... }

    Select distinct rows from a DataTable

    It is probably best to describe a scenario to understand what I am trying to describe. Let's assume you have a DataTable called myDataTable that has three columns (Col1, Col2, Col3). You want to get a distinct list based on Col1 and Col2. In SQL you could do something like: Select distinct Col1, Col2 from MyTable; Believe it or not, we can do the same thing with the in memory table known as a DataTable in .Net. // populate the DataTable DataTable myDataTable = DAL.doSomeQuery();

    bool distinct = true;

    DataTable distinctRows = myDataTable.DefaultView.ToTable(distinct, new string[] { "Col1", "Col2" }); That is it, but it is only available in .Net 2.0.

    Tuesday, October 23, 2007

    ASP.NET and EventLog: Event ID issues when writing to Event Log

    If you get the following message (except your application name): The description for Event ID ( 234 ) in Source ( dotNET Sample App ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Sample Event. The best part of this is that the existing logs no longer have this message either once you follow the steps below. What it is trying to tell you is that when it tries to look up Event ID 234 in the Source Called dotNET Sample App, it can't figure out what 234 is supposed to represent because it can't find the dll that maps the event ids to localized messages. What it wants is an entry in the Registry that points to the dll. For example, if you are writing to the Application event log and using the source called dotNET Sample App, the key needs to be at: HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Application\dotNET Sample App\EventMessageFile. The value needs to point to a .dll file that has been compiled specially for this purpose using. It appears that if this key does not exist, you can add the above key (Expandable String Value), and point the value to (slightly different path if v1.x): C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\EventLogMessages.dll If you want user friendly messages in the event log instead of number for the event id, you will need to do the following. If the key is not already there, you can add a new Expandable String Value with the name Event MessageFile and the value of the path (including the .dll) to the dll. The following url is a good start: http://msdn2.microsoft.com/en-us/library/system.diagnostics.eventinstance.aspx Here is how to use Message Compiler (to create the event id dll): http://msdn2.microsoft.com/en-us/library/aa385638.aspx If you want to fix this the quick way and have a generic dll get used automatically then just do the following. This step may or may not be necessary if you are running a Windows application, but is necessary if you want an ASP.NET application to log events. 1. Launch RegEdit 2. Navigate to Delete the key at HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Application\dotNET Sample App 3. From the Edit menu select Permissions. 4. Add the ASPNET user (or whatever user your application is running under or impersonating if using ASP.NET) and give it Read and Write permission. Verify that the Application and Security Keys now have these permissions also. NOTE: Under IIS 6.0 the user isn't ASPNET, it is Network Service Tip: If you need help debugging permission, you can always add Everyone with Full Control and reduce permissions until you figure out what permissions you really need. Be sure to not leave it this way though. It is a small security hole. NOTE: You are probably not writing to System, but if you have code like the following in your application, the SourceExists() method will throw an exception. To avoid this, you need to grant permissions to it also (at least until the CreateEventSource()) method successfully creates the key. After that, you should not get the error when SourceExists() is called. There are other solutions as well. For example, you can always create a standalone Windows application with the same name as your web application and have it run the CreateEventSource() method. This will get you past the SourceExists() call, and this will create the source. You will still need to adjust permissions for your application to log properly though. You can also create an event log installer. I have never done this, and sounds like a complicated solution to me. Permissions is much simpler and falls under the general configuration knowledge that can be used for any web application. if (!EventLog.SourceExists(sourceName)) { EventLog.CreateEventSource(sourceName, "Application"); } 5. Now run your application, and make sure the CreateEventSource() is called. This should create the key: HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Application\dotNET Sample App NOTE: You may need to run your application twice as it takes a short time for the new Event Source to be synchronized with other parts of Windows. 6. Verify that the EventMessageFile key was created properly. NOTE: If you specify the category id in your code, you will also need to do something similar. I have not tested the solution, but I expect if you create custom message then you will have a file that you can't point to in the registry in much the same way. I know the key for the categories does not automatically get created, but using mc.exe as you would have done for event ids (if you needed custom messages) should work. I welcome feedback on this. I recommend setting the category id to 0 in your code if you don't want to deal with this issue. Another Symptom of this is that MS LogParser will generate errors like the following Task aborted.Too many parse errors - abortingParse errors:Unable to map Event Message from Event Source "dotNET Sample App"Unable to map Event Message from Event Source "dotNET Sample App" when a query like LogParser.exe "select distinct message from application where message like '%display messages from a remote computer%'" -msgErrorMode:ERROR -e 10 is executed to find all the troubled errors in the first place. To fix this, make sure registry key and path to dll exists for source as described above.

    Literally you can copy and paste from one SQL Server to another

    Let's say you want to copy data from one server to another. Assume you have a table called MyTable on ServerA and the exact same table on ServerB. Basically all you have to do is simply copy and paste the rows from one screen to another. Here are the details. Here are step by step instructions: 1. Open MS SQL Server Management Studio 2. Browse to your favorite table using the Object Explorer. 3. Right click on the table and select "Open Table" 4. Shift or control click the rows you want to copy. 5. Type control-C. Repeat steps 1 - 4 on the other server that has this same table (no data though). UPDATE: Be sure to select the last row that has all nulls, otherwise the paste may not work as expected. Lastly, type Control-V to paste the data. This tip does not work in Enterprise Manager that comes with SQL Server 2000 (at least that I know of).

    Easy way to restart your ASP.NET web site

    If you don't want to use IIS to recycle your application pool, or don't have access to it or iisreset command then there is an quick and easy way to force your ASP.NET web site to recycle. This includes the application variables that you may have defined and use in your application. Simply edit your web.config. Any change should work. That is it. NOTE: I have tried changing other source code such as .cs files and it doesn't seem to trigger a restart of the application. I imagine it would have to be a real code change that causes the compiled dll to be regenerated, not just adding a space at the end of a line or something simple like I tried.

    Wednesday, October 10, 2007

    Image alignment in HTML / CSS

    Let's assume you have a line that starts with an image with text to the right of it. By default the image and text are vertically aligned to the bottom of the image and the baseline (bottom except for letters like g, j, y, etc) of the text. To make the image be centered on the line of text you do the following.

    <img border="0" src="myImage.gif" style="vertical-align:middle"/>my text that is to the right of the image.

    Finding column references in SQL Server

    Here is a stored procedure that will generate a text formatted report that details what views, stored procedures, functions and triggers use the specified column. This is useful to know when you make a change to a column. This information is available from the UI, but this is a nice SQL way of doing it. This solution was copied from http://www.sqlservercentral.com/scripts/Miscellaneous/31963/. Thank you!
    if exists (select * from dbo.sysobjects
     where id = object_id(N'[dbo].[usp_FindColumnUsage]')
      and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[usp_FindColumnUsage]
    GO
    CREATE PROCEDURE [dbo].[usp_FindColumnUsage]
     @vcTableName varchar(100),
     @vcColumnName varchar(100)
    AS
    /************************************************************************************************
    DESCRIPTION: Creates prinatable report of all stored procedures, views, triggers
      and user-defined functions that reference  the
      table/column passed into the proc.
    
    PARAMETERS:
      @vcTableName - table containing searched column
      @vcColumnName - column being searched for
    REMARKS:
      To print the output of this report in Query Analyzer/Management 
      Studio select the execute mode to be file and you will
      be prompted for a file name to save as. Alternately
      you can select the execute mode to be text, run the query, set
      the focus on the results pane and then select File/Save from
      the menu.
      This procedure must be installed in the database where it will
      be run due to it's use of database system tables.
    USAGE:
      
      usp_FindColumnUsage 'jct_contract_element_card_sigs', 'contract_element_id'
    
    AUTHOR: Karen Gayda
    DATE: 07/19/2007
    MODIFICATION HISTORY:
    WHO  DATE  DESCRIPTION
    ---  ---------- -------------------------------------------
    *************************************************************************************************/
    SET NOCOUNT ON
     
    PRINT ''
    PRINT 'REPORT FOR DEPENDENCIES FOR TABLE/COLUMN:'
    PRINT '-----------------------------------------'
    PRINT  @vcTableName + '.' +@vcColumnName
    PRINT ''
    PRINT ''
    PRINT 'STORED PROCEDURES:'
    PRINT ''
    SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Procedure Name]
      FROM sysobjects o
      INNER JOIN syscomments c
       ON o.ID = c.ID
      WHERE  o.XTYPE = 'P'
       AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
      
      
     ORDER BY  [Procedure Name]
    PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent stored procedures for column "' + @vcTableName + '.' +@vcColumnName +  '".'
     
    PRINT''
    PRINT''
    PRINT 'VIEWS:'
    PRINT''
    SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [View Name]
      FROM sysobjects o
      INNER JOIN syscomments c
       ON o.ID = c.ID
      WHERE  o.XTYPE = 'V'
       AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
      
      
     ORDER BY  [View Name]
    PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent views for column "' + @vcTableName + '.' +@vcColumnName +  '".'
    PRINT ''
    PRINT ''
    PRINT 'FUNCTIONS:'
    PRINT ''
    SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Function Name],
      CASE WHEN o.XTYPE = 'FN' THEN 'Scalar'
       WHEN o.XTYPE = 'IF' THEN 'Inline'
       WHEN o.XTYPE = 'TF' THEN 'Table'
       ELSE '?'
      END
      as [Function Type]
      FROM sysobjects o
      INNER JOIN syscomments c
       ON o.ID = c.ID
      WHERE  o.XTYPE IN ('FN','IF','TF')
       AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
      
      
     ORDER BY  [Function Name]
    PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent functions for column "' + @vcTableName + '.' +@vcColumnName +  '".'
    PRINT''
    PRINT''
    PRINT 'TRIGGERS:'
    PRINT''
    SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Trigger Name]
      FROM sysobjects o
      INNER JOIN syscomments c
       ON o.ID = c.ID
      WHERE  o.XTYPE = 'TR'
       AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
      
      
     ORDER BY  [Trigger Name]
    PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent triggers for column "' + @vcTableName + '.' +@vcColumnName +  '".'
    GO

    Tuesday, October 9, 2007

    Adding AJAX.Net to existing ASP.Net Web Site

    If you are like me you have web sites in ASP.Net that don't use AJAX.Net and when the web site was created there was no such thing as an AJAX-enabled project in Visual Studio 2005. With the release of AJAX.Net v1.0 of AJAX is a compelling set of functionality that I want to take advantage of. It is trivial when it comes to creating a new web site in Visual Studio 2005. Just select the AJAX enabled web site when you create your web site. However, the question I needed an answer to was how do I AJAX enable my existing web sites that are not AJAX enabled. The easy answer I found is create a new web site in Visual Studio 2005. Open the Web.config. Here you will see a lot of new sections and tags. Just copy all the new stuff (which is all except a couple of tags like assemblies and compilation tags) to your web config. That should do it. You can now play with AJAX.Net 1.0 in your existing web site. As a bit of background, I tried just dragging the ScriptManager and UpdatePanel controls to my page. The behavior was that it posted back still and also refreshed the entire page. Weird I thought to myself. Then I realized there was a Javascript error that said 'Sys' is undefined Doing a quick search on Google I quickly realized this was because needed to tell my web site about AJAX.Net so the AJAX.Net control would have the client side javascript they were expecting. This is the simplest solution I know. It has the advantage of always referencing the correct assemblies and versions, etc.