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.

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.

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

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.

Conditional Compilation to run Windows Service as an application

Conditional Compilation is a concept that has been around since C and C++. The idea is that you can define variables, control flow, etc for what gets compiled and what does not.

This concept can be illustrated by looking at testing of Windows Services in Visual Studio 2005. It is time consuming and just no fun to write code as a Windows Service as it cannot be run directly in Visual Studio. The solution I propose is to use Conditional Compilation to allow the Windows Service to be ran as a Windows Service or Windows Form Application. To do this, see below. // NOTE: Add RunAsApp to Conditional Compiler Symbols under the Build tab on the Project properties. #if (RunAsApp) // To test as an app MyServicee app = new MyService(); Application.Run(app); #else // To test as a Service ServiceBase[] ServicesToRun; ServicesToRun = new ServiceBase[] { new MyService() }; ServiceBase.Run(ServicesToRun);

Very efficient paging in SQL Server 2000 or 2005 without temp table

What I mean by paging in SQL Server is something like this. Assume you have a table that has a million rows. You want to show the user only a portion of those records such as through a web interface. The user could then click next page / previous page to see more results. You may also show the user a list of pages so they can jump to any page without clicking next/previous links. With that said, most solutions for SQL paging stored procedures involve a temp table for SQL 2000 and using ROW_NUMBER() function in SQL 2005. While the SQL 2005 solution is pretty easy it doesn't work in SQL 2000. The articules imply that this solution is better from a performance standpoint, I found it to be the opposite, so your mileage may vary. I recommend if you have SQL 2005, then use the ROW_NUMBER() function as the code clean, and easy to understand. Below is a very technical solution, but it is not difficult to adapt to your situation, and it works on both 2000 and 2005. I am not going to try to explain it here as it is explained in great detail here: http://www.4guysfromrolla.com/webtech/042606-1.shtml. BTW, thank you to author for this solution. The math required to convert a zero-based page index to a value that can be used for the @startRowIndex parameter is as follows, and could be in your .net code, or whatever. int startRowIndex = Math.Ceiling(pageIndex * pageSize); // NOTE: you will need to do some data type conversions, but this is basically it. The parameter @startRowIndex is 1 based. The parameter @maximumRows is essentially how many records you want in a page. CREATE PROCEDURE [dbo].[GetEmployeesPaged] ( @startRowIndex int, @maximumRows int ) AS DECLARE @first_id int, @startRow int -- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that -- Get the first employeeID for our page of records SET ROWCOUNT @startRowIndex SELECT @first_id = employeeID FROM employees ORDER BY employeeid -- Now, set the row count to MaximumRows and get -- all records >= @first_id SET ROWCOUNT @maximumRows SELECT e.Name, e.EmpoyeeID FROM employees e WHERE employeeid >= @first_id ORDER BY e.EmployeeID SET ROWCOUNT 0 GO The solution above is the best performance wise for a SQL 2000 database. I recommend if you have SQL 2005 that you use the ROW_NUMBER() function as shown below. CREATE PROCEDURE [dbo].[GetEmployeesPaged2005] ( @startRowIndex int, @maximumRows int ) AS -- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that SELECT * FROM (SELECT e.Name, e.EmpoyeeID ROW_NUMBER() OVER(ORDER BY employeeid) as RowNum FROM employees e ) as DerivedTableName WHERE RowNum BETWEEN @startRowIndex AND (@maximumRows + @startRowIndex - 1) SET ROWCOUNT 0 GO

Reflection makes partial classes visible again

ASP.Net 1.x didn't use partial classes to implement pages and user controls. This made things a little cluttered. However, it had the advantage of the type being accessible for things like casting the Page object to your own specific page class. You can't do this in ASP.Net 2.x because partial classes are not available until runtime when the two partial classes are joined to make a complete class. This means we can't cast. You might be thinking, why would I want to do that anyway. In most cases you don't have a big need. However, when you want to pass information between a user control a master page or even the page itself it can be difficult. Let's assume you have a user control as defined below. public partial class MyControl : System.Web.UI.UserControl { public string MyProperty { set { myLabel.Text = value; } } } In ASP.Net 1.x (assuming MyControl was defined as a non-partial class because 1.x didn't support partial classes) you do something like the following from the parent Page of this control. The following assumes that the parent page has an instance of the MyControl on the page called myControl. public class MyPage .... ((MyControl)this.myControl).MyProperty = "abcdefg"; .... In ASP.Net 1.x the compiler would know how to find MyControl class and would compile ok. In ASP.Net 2.0 the compiler would NOT know how to find MyControl class and would FAIL to compile. Again, the reason is the class is a partial class and does not exist yet. How do we fix this you ask? Option 1. Convert your Web Site project to Web Application Project under ASP.Net 2.0. Option 2. Convert only this class to a non-partial class. I have not tried this, and don't know if this will work. Option 3. Use reflection to get around the issue. This works well since both reflection and partial classes will be doing their magic at runtime, not compile time. /// <summary> /// Set the value of a property on any class /// </summary> public static void SetProperty(System.Object obj, System.String propertyName, System.Object propertyValue) { if (obj == null) { throw new System.ArgumentException("No target specified"); } if (propertyName == null) { throw new System.ArgumentException("No name specified"); } PropertyInfo pi = obj.GetType().GetProperty(propertyName); if (pi == null) { throw new Exception("Object does not have a property named: " + propertyName); } pi.SetValue(obj, propertyValue, null); } Be warned, reflection can open up the chance for errors that the compiler can no longer check for. For example, don't change MyProperty, to MyProperty2 without changing your string in the SetProperty method above. Now we can do something like:

....

SetProperty(myControl, "MyProperty", "abcdefg");

....

Also, try not to do something like this in a loop as there is a slight performance hit for using reflection.