Embedded source file for asp.net custom control

When designing a custom control in asp.net, often we have a client side javascript file for the control. We have two options to include the script file: either declare it in asp.net webform directly or embed it in custom control dll file.

In asp.net 1.1 we could include the javascript in the custom control project's resource file and consume the resource at run time. This elimilated the need of the dependent on the external script file. Or we could create a HTTP Module or Handler to handle the script file name. While this works, it requires some extra coding.

In asp.net 2.0 we have a new and simplified approach. We could embed the script file in the external dll file and consume it using the built-in WebResource.axd http handler.

The following is an example of applying this strategy.

I have created a simple custom control to show alert message in a web form. This control simply call javascript alert function.

First I create a Web Control project, with namespace as MyWebControl. Then I create a javascript file Alert.js in the folder JavaScript. There is only one function in the Alert.js:

function ShowAlert(message)
{
alert(message);
}

Then I open the property of this file, change it's Build Action property to Embedded Resource.

By default, any resource in the dll file is not accessible externally. To enable web form access this resource, we need to define it in the AssemblyInfo.cs file:

[assembly: WebResource("MyWebControl.JavaScript.Alert.js", "text/javascript", PerformSubstitution = true)]

Note I also include the control namespace as prefix to the resource name, this is required.

The resource is available now, what we do is to consume it in the control source code. The following function is an example:

protected override void OnPreRender(EventArgs e)
{
ClientScriptManager cs = Page.ClientScript;
if (!cs.IsClientScriptIncludeRegistered("WebAlert"))
{
cs.RegisterClientScriptInclude("WebAlert", cs.GetWebResourceUrl( this.GetType(),
"MyWebControl.JavaScript.Alert.js" ));

} if (Text != string.Empty)
{
string message = Text.Replace("\r", "").Replace("\"", "\\\"").Replace("\n", @"\r\n");
cs.RegisterStartupScript(this.GetType(), "AlertScript", "ShowAlert(\"" + message + "\");", true);
}
Text = string.Empty; // make sure to clear the message to remove the alert for the next page load.
base.OnPreRender(e);
}

That's all we need. After put this control in a webform, asp.net put this line in the html:


The script is cached in the client side so it's downloaded into user's browser only once.

SQL Server Date and Time Functions

Date Time functions can be found in the online book. I list them here for quick reference.


DATEADD(datepart, integer, datetoadd) Returns a new datetime value based on the parameters.
DATEDIFF(datepart, date1, date2) Returns number of time units between two dates
DATENAME(datepart, datetoadd) Returns the full name of the date part. eg

select DATENAME(month, getdate()) as month_name
month_name
----------------------
July
DATEPART(datepart, date) Returns part of a date
DAY(date), MONTH(date), YEAR(date) Returns specific part of a date.

Date Part abbreviations


year yy, yyyy
quarter q, qq
month m, mm
day of year dy, y
day of month day
week ww, wk
hour hh,
minute mi, n
second ss, s
millisecond ms

Frequently used SQL Server string functions quick reference

These string functions can be found in the SQL Server online book. I list them here to make it easier for me to lookup.

Function Description
LEFT(string, lenght)
RIGHT(string, length) Returns leftmost or rightmost of a string with given length.
LEN(string) Returns the length of a string.
LOWER(string)
UPPER(string) Returns string lower or upper case.
LTRIM(string)
RTRIM(string) Trim a string in left or right side. For a full trim, use LTRIM(RTRIM(string))
PATINDEX(pattern, string) Returns the starting position of the first instance of pattern in the string. If the pattern is not found, then returns zero. eg

select PATINDEX('%CAT%', 'CATALOGUE') as Match
Match
------------------
1

REPLACE(string to replace, for, with) Returns a string in which all occurrences if the second expression within the first expression are replaced with the third expression. eg.
SELECT REPLACE('ABCDE', 'AB', '***') as Result
-------------
***CDE
QUOTENAME(string, quote) Returns a string with quotation mark surrounded. The quote can only be ', ", or [ or ]. eg
SELECT QUOTENAME('hello', '[') as Result
---------------
[hello]
REPLICATE(string, times) Replicate a string with number of times.
SPACE(length) Return blanks with given length.
SUBSTRING(string, start, length)

Custom aggregation in SQL Server - best solution ever (3)

I came across another elegant solution does the same job for custom aggregate. The only disadvantage of this approach is we need to write a function for each table needed. This is not a problem if performance is the key.

The function is quite short:

CREATE FUNCTIONS EmployeeList(@DeptID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000) , @delim VARCHAR (2)

-- set empty string for delimitor. The comma would be set to it after the first select statement. Excelent!
SELECT @Result = ', ', @delim = ''
SELECT @Result = @Result + @delim + EmpID, @delim = ', '
FROM Emps
WHERE DeptID = @DeptID

RETURN @Result
END

This is the best approach I have seen so far and is very fast. It is about 3 time's faster than cursor approach. I have changed all my functions to follow this approach.

Custom aggregation in SQL Server 2005 with .net class (2)

In my previous post, I discussed how to aggregate detail records into comma separated string using cursor. From what I have observed, cursor has performance issues in a large table.

In Sql Server 2005, we can write our own class in any .net language. The fowllowing article discusses an approach that meets my needs very well. If we use SQL Server 2005 we should consider this approach.
The original URL is here:
http://www32.brinkster.com/srisamp/sqlArticles/article_44.htm

To make it easier to understand. I just copy the code from the article and show how to use it.
The Aggregation function is written is vb.net.


Imports System
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Text
Imports System.Runtime.Serialization

_
IsInvariantToOrder:=False, IsNullIfEmpty:=True, MaxByteSize:=8000)> _
Public Class FormDelimitedString
Implements IBinarySerialize

' Variable to hold the return result
Private returnString As StringBuilder

Public Sub Init()
' Initialize the string builder. This method will be called for each
' group that is accumulated
returnString = New StringBuilder()
End Sub

Public Sub Accumulate(ByVal value As SqlString)
' Accumulate the value into the string builder. If the value is NULL
'do not do anything. This method is called for each row of data
If (value.IsNull) Then
Return
Else
returnString.Append(value.Value).Append(", ")
End If
End Sub

Public Sub Merge(ByVal value As FormDelimitedString)
' Merge the current instance with the other instance.
returnString.Append(value.returnString)
End Sub

Public Function Terminate() As SqlString
' Return the result if the string has some content
If (returnString.Length > 0) Then
Return New SqlString(returnString.ToString(0, returnString.Length - 2))
End If

' Return an empty string otherwise
Return New SqlString("")
End Function

Public Sub Read(ByVal r As System.IO.BinaryReader) Implements System.Data.Sql.IBinarySerialize.Read
returnString = New StringBuilder(r.ReadString())
End Sub

Public Sub Write(ByVal w As System.IO.BinaryWriter) Implements System.Data.Sql.IBinarySerialize.Write
w.Write(returnString.ToString())

End Sub
End Class


To use this class, the dll needs to be deployed to the Sql Server 2005. The following example shows how to use it:

SELECT
ph.PurchaseOrderID, [Items] = dbo.FormDelimitedString (p.ProductNumber)

FROM
Purchasing.PurchaseOrderHeader ph
INNER JOIN Purchasing.PurchaseOrderDetail pd ON ph.PurchaseOrderID = pd.PurchaseOrderID
INNER JOIN Production.Product p ON pd.ProductID = p.ProductID
WHERE
ph.PurchaseOrderID < 10
GROUP BY
ph.PurchaseOrderID


The result would look like:
PurchaseOrderID Items
1 Item1, Item2, Item3
2 Item2, Item4

A weird bug in Visual Studio 2005

I met a weird bug that drove me crazy before I figured it out by a chance. When I open an existing project in Visual Studio, I normally open it from the start page. And the project will be openned automatically. But this doesn't apply to my recent reporting service project. When I click on the project name on the start page, the project would open, but when I open a report in report designer, the IDE failed to load it with hanging there for one minute.

I have re-installed my visual studio, SQL Server 2005 client component but that didn't help. Finally I got it working today by select menu Open Project, and then point to the project file. The bug is gone and report can be loaded into IDE successfully. What a frastracting bug!

Master - Details hierarchical structure in one table

This is a traditional manager-employee hierarchy problem that have many ways of solving it, but I chose implementing it with a SQL function.

We have a need to represent parent - children relationship in one table. The table is relatively simple. In the Employee table we have EmployeeID, Name, DepartmentID, and ManagerID. The ManagerID field links to another EmployeeID in the same table.

The table is easy to implement and development in our application. But when we come to display the data, it's becoming tricky. What we need is to display an Employee and his manager in a report. And a manager could have his upper level manager too. After many hours of search through web, I came up with a function as below:

CREATE FUNCTION dbo.GetEmployeeTree
(@DepartmentID int, @EmployeeID int, @depth int)
RETURNS @EmployeeTree TABLE (EmployeeID int, Depth int, Name nvarchar(50))
AS
BEGIN
DECLARE @ParentID int
DECLARE @nextDepth int
SET @nextDepth = @depth + 1
SET @ParentID = (SELECT ManagerID FROM Employee WHERE EmployeeID = @EmployeeID and DepartmentID =@DepartmentID)

IF @ParentID <> 0
/* We stop if a container has no parent */

INSERT INTO @EmployeeTree (EmployeeID, Depth, Name)
SELECT EmployeeID, @depth As Depth, Name
FROM Employee C
Where C.EmployeeID = @ParentID and C.DepartmentID=@DepartmentID
UNION
--Recurseively call the function to append
SELECT EmployeeID, Depth, Name FROM GetContainerTree(@DepartmentID, @ParentID, @nextDepth)
RETURN
END


To use this function for an employee's manager and upper managers, we need to write a stored procedure or just a simple query like this:


CREATE PROCEDURE dbo.GetManagers
(
@EmployeeID int,
@DepartmentID int
)
AS
select EmployeeID, Name, Depth from GetEmployeeTree(@DepartmentID, @EmployeeID, 0)

RETURN

GO

Custom aggregation in SQL Server with cursor (1)

If we have a Customer and a Order table, with one to many relationship, often we need to concatenate details records into master table for displaying. The details could be separated by semi colon into master table. The following is the Function required to get the concatenated value:

CREATE FUNCTION dbo.GetConcatenatedOrderNumbers
(
@CustomerID int
)
RETURNS nvarchar(500)
AS
BEGIN

declare @item nvarchar(200)
declare @result nvarchar(400)

select @item = ''
select @result = ''

declare item_cursor cursor for
select OrderNumber from Order Order.CustomerID=@CustomerID

Open item_cursor
Fetch next from item_cursor into @item

while @@FETCH_STATUS = 0
begin
select @result = @result + @item + '; '
Fetch next from item_cursor into @item
end

CLOSE item_cursor
DEALLOCATE item_cursor

-- remove space in the end
select @result = rtrim(@result)
-- remove the ; at the end
if(Len(@result) > 1)
begin
set @result = substring(@result, 1, len(@result) - 1)
end

RETURN @result
END

Some useful short cut keys in Visual Studio 2005

These are some short cut keys I learnt today:

Alt + Shift + Enter = full screen
Type prop and press Tab twice will trigger property code snippet.
F12 = goto variable definition. Shift + F12 = find refferences
Ctrl + K + C = comment; Ctrl + K + U = remove comment
Ctrl + m + m = collapse or expand a region or function
If a function does not exist and Ctrl + k + m is pressed, the function is implemented by default.

ATLAS UpdatePanel and FileUpload control

It's a known issue that asp.net FileUpload control doesn't work inside a UpdatePanel in a ajax enabled web page. There is a work around to solve this problem if you still need to put FileUpload control inside the UpdatePanel for page layout reasons. The work around is as follows:

In the asp.net page, define a FileUpload and a Button control inside ContentTemplate:

<atlas:UpdatePanel ID="multiViewUpdatePanel" runat="server" Mode="Conditional">
<ContentTemplate>
<asp:FileUpload ID="attachmentFile" runat="server" />
<asp:Button ID="uploadButton" runat="server" Text="Upload" />
</ContentTemplate>
</atlas:UpdatePanel>

Still in the asp.net page, put a "fake" link button outside of UpdatePanel as this:

<asp:LinkButton ID="fakeUploadButton" style="display:none" runat="server" Text="upload" OnClick="fakeUploadButton_Click" />

In the code behind file, put this code in the Page_Load event:

protected void Page_Load(object sender, EventArgs e)
{
this.uploadButton.Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(fakeUploadButton, ""));
}

After this line of code is executed, when a user click on the uploadButton, which is located beside the FileUpload control, the "fake" link button's OnClick event will be triggered because of the JavaScript added to the client side onclick event of uploadButton.

Then in the server side catch the OnClick event of the link button as below:

protected void fakeUploadButton_Click(object sender, EventArgs e)
{
if (attachmentFile.HasFile)
{
string fileName = attachmentFile.FileName;
// ...
}
}

Then when you click on the Upload button, an explict postback is triggerred which will post the file to the server side.

Another note, if you have a control inside UpdatePanel and you write file down load code directly in your asp.net code behind, the file won't be downloaded to client side. What you could do is to change the LinkButton to a HyperLink, which points to another WebForm, with whatever parameter passed with URL. In the second WebForm, you could write code to down load a file because that page has no UpdatePanel. And it works.