Tuesday, August 12, 2008

Using Query String Parameters in ASP.Net Page

You can use query string parameters in a URL request string to pass information into an aspx page. This is very common way to pass values from one page to another page. The URL query string is in the format like:

http://mypage.aspx?para1=value1¶2=value2...


I also use this mechanism to pass in additional data or flags to display some hidden values or to provide additional information from server side codes. For example, in a GridView control, normally we only display a UI table which are readable by clients, such as employee first name, last name and department. The employee ID, which is important information for server side updating, is normally a hidden column in the table.

As in this example, my codes handle some query parameters. One is "displayEmployeeID". If it is true, the employee ID then is visible:

http://mypage.aspx?para1=value1¶2=value2&displayEmployeeID=true


This parameter can be manually added the URL address text box (use & to separate parameters). If it is not available, the default value is false. Here are some codes in my aspx.cs page in the Page_Load() event:

bool display = false;
string sVal = HttpUtility.UrlDecode(Request.QueryString["displayEmployeeID"]);
if (string.IsNullOrEmpty(sVal) == false)
{
if (!bool.TryParse(sVal, out display))
{
display = false;
}
}
DataGridView.Columns[0].Visible = display;


I use the same way to display my business logic layer class's SQL string on my page so that it makes my debugging work very easy. I add a panel to the page in aspx:

<asp:Panel ID="panelDebug" runat="server" Visible="false" width="720px">
<table width="100%"><tr align="left" ><td style="width: 110px" >
<asp:Label ID="lblSQL" runat="server" Text="Create Object SQLs: " /></td><td>
<asp:TextBox ID="txtSQL" runat="server" Wrap="true" Width="600px"></asp:TextBox>
</td></tr><tr align="left"><td style="width: 25%">
<asp:Label ID="lblUpdate" runat="server" Text="Updates: " ></asp:Label></td><td>
<asp:TextBox ID="txtUpdate" runat="server" Width="600px" Wrap="true"></asp:TextBox>
</td></tr>
</table>
</asp:Panel>


Then in the event of Page_Load() and the place when my business class has retrieved data from a SQL DB:

panelDebug.Visible = GetDisplaySQL(); // method to get query parameter "displaySQL" value 
...
txtSQL.Text = bllObj.QuerySQLs; // udpate SQL by BLL class property QuerySQLs
...


Here the text box txtUpdate is as same as query SQL but for update SQL statements.

I call those query parameters as hidden parameters. They provide handy ways to get additional information about what was happened in server side, and they can also be passed in as alternative input values.

Read More...

Tuesday, August 05, 2008

ASP.Net: Add Client Side JavaScript Codes

ASP.Net controls do not support all the control events like window form application. For example, OnKeyUp, OnMouseUp, and OnMouseMove events. It make sense that if these events were supported, there would be too many calls from client side back server, and it would make aspx pages very very slow.

However, there are some cases you would like your aspx page to support these events. For example, I have a asp:TextBox control with an attribute of OnTextChanged event. This even only fires back to the server when you change the text and leave the control to any where on the same page. If you click on another link on the page right after you change the text, this event would not be called at all.

In order to catch this event and set a flag on the page to indicate a change, I have added another asp:TextBox control called MonitorChangeControl. Then I added the following codes in the Page_Load event to insert client side JavaScript function to the aspx page so that on the client side the MonitorChangeControl text will be changed to a text "Some values may have been changed." when my monitored textbox is changed:

string changeScript = "<script language='javascript'> function SomeValueChanged() {" +
"document.getElementById('" + MonitorChangeControl.ClientID +
"').value = 'Some values may have been changed.'; }</script>";
// Add the JavaScript code to the page.
if (!ClientScript.IsClientScriptBlockRegistered("SomeValueChanged"))
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "SomeValueChanged", changeScript);
}


After register the script function, then in the following codes I add an attribute "OnKeyUp" with a script call to the function "SomeValueChanged()":

TextBox myTextBox = WebPageUtil.FindControlRecursive(row, "curr_day"); //row is a GridViewRow
if (currValue != null)
{
myTextBox.Attributes.Add("OnKeyUp", "return SomeValueChanged()");
}


When I run the aspx page in a browser, the TextBox is converted to an Input document element with an attribute like this:

<input name="..." OnKeyUp=""return SomeValueChanged()" .../>


This attribute will cause the client side event fired whenever the input element's text is changed or key-is-up.

So far so good. However, for an asp:CheckBox control, if the attribute is added in a the same way, the attribute is actually added to a span element in the aspx page which is outside of the input element (converted from CheckBox):

<span ... OnKeyUp="" ...><input id=... type="checkbox" .../></span>


Therefore, I have to add the attributes in a different way:

// row is a GridViewRow control and FindControlRecursive is my function.
CheckBox box = WebPageUtil.FindControlRecursive(row, "ValidatedCheckBox");
if (box != null)
{
box.InputAttributes.Add("OnKeyUp", "return SomeValueChanged()");
box.InputAttributes.Add("onmouseup", "return SomeValueChanged()");
}


After that, I tried to view source from the browser. Here is the result of what I expected:

<input id=... type="checkbox" ... OnKeyUp="return SomeValueChanged()" onmouseup="return SomeValueChanged()" />


By the way, the event of OnChange is working for TextBox control but not for CheckBox controls. The OnChange is fired only after you make a change and at the moment whey you leave the control. For complete HTML element, properties/attributes, and events, see the page of HTML Event Attributes and JavaScript Event References.

For the function FindControlRecursive(), see my previous blog on May 29, 2008

Read More...

Monday, August 04, 2008

SqlContext.Pipe.Send not Available in SQL Server Project's Functions

I tried to use SqlContext.Pipe.Send to send a message back from a SQL project's funciton. It does not work at all. When the function is called, it throw an exception at the point SqlContext.Pipe.Send is called. It works fine in a SQL project' Stored Procedure.

Normally, I use this send to debug my program in the development stage. With this limitation, I have to create a stored procedure instead. After everything is fine, I then change it back to a function.

Another related issue about SQL Server Project, if I tried rename the previous deployed stored procedure as a way to back up the previous version of CLR SP, the SP is gone after I deploy my project again. It looks like that the deployment is smart enough to remove the previous version first, no matter you rename it or not. It does make sens since the new CLR assembly will be deployed and replace the old one. Even you rename it, the CLR SP should not be able to work since the assembly has been updated.

Read More...

Thursday, July 31, 2008

Comparing Two Tables By SQL Stored Procedure

I have created a SQL Stored Procedure to compare any two tables based on Microsoft SQL Server 2005 new syntax EXCEPT and INTERCEPT. Basically, I used EXCEPT and UNION to get the result of differences between tables and INTERCEPT to get the same result of two tables.

In addition to that, the SP will compare two tables by specifying column fields and conditions and display the result by optional ORDER BY clause. Here is the SP:

CREATE procedure [dbo].[SP_CompareTables] (
@table1 varchar(100),
@table2 varchar(100),
@table_colList varchar(3000) = NULL,
@whereClause varchar(3000) = NULL,
@orderByClause varchar(3000) = NULL,
@difference0 int = 0
)
AS
DECLARE
@sql varchar(8000);
DECLARE @colList varchar(3000);
BEGIN
if
( @table_colList is null Or @table_colList = '' )
begin
set
@colList = '*';
end
else
begin
set
@colList = @table_colList;
end
if
( @difference0 = 0 )
begin
set
@sql =REPLACE(REPLACE(REPLACE('
SELECT ''@table1'' AS TblName, *
FROM (
SELECT @colList
FROM @table1
EXCEPT (
SELECT @colList
FROM @table2)
) x
UNION ALL
SELECT ''@table2'' AS TblName, *
FROM (
SELECT @colList
FROM @table2
EXCEPT (
SELECT @colList
FROM @table1)
) y'
,
'@table1', @table1),
'@table2', @table2),
'@colList', @colList);
end;
else
begin
set
@sql =REPLACE(REPLACE(REPLACE('
SELECT @colList
FROM @table1
INTERSECT (
SELECT @colList
FROM @table2)'
,
'@table1', @table1),
'@table2', @table2),
'@colList', @colList);
end;
if ( @whereClause is not null And len(@whereClause) > 0 )
begin
set
@sql = REPLACE(REPLACE('
SELECT * FROM (@sql) v
WHERE @whereClause'
,
'@sql', @sql),
'@whereClause', @whereClause);
end
if
( @orderByClause is not null And len(@orderByClause) > 0 )
begin
set
@sql = REPLACE(REPLACE('@sql
ORDER BY @orderByClause'
,
'@sql', @sql),
'@orderByClause', @orderByClause);
end;
print @sql;
exec(@sql);
return 0;
END


To use this SP is very simple. For example, to compare two tables of [Employees] and [Employees_backup], you can just run the following script to compare two whole tables:

EXEC SP_CompareTables 'Employees', 'Employees_backup';


More examples by specifying columns, where clause and order clause:

EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName';
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName',
'FirstName like ''D%'' AND BirthDate Between ''Jan 01, 1990'' AND ''Jul 30, 2008''';
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName',
NULL, 'FirstName, BirthDate' , 1; -- get same results

Read More...

Saturday, July 26, 2008

SQL Server Project (4)

I have to close this series articles on SQL Server Project. The final part will cover some special issues related to SQL Server Project.

The first issue is the connection to SQL server. As I mentioned in SQL Server Project (2), it is recommended to use the context connection since the CLR assembly is already in a SQL server running process. However, this connection can only be created once. You cannot create another context connection for other executions. Normally, you don't need another one in one stored procedure for example. However, if you create several SQL procedures, functions, and triggers in one dll, you might get exceptions if one calls another since only one context connection is allowed. You don't have control who is going to run these stored procedures, functions, or triggers.

Therefore, I think it is better to create one SQL item (SP, function or trigger) in one dll. You could create a normal SQL connection with catalog for a db table, user name, and password information in a connection string if you have to, and that connections can be created more than once. In a SQL server context, it does not make sense to do that unless you need to connect to another SQL server or Oracle server.

An related issue is that always to handle exceptions in your assembly and close any opened connections. As I mentioned before, the assembly is loaded to SQL server running process, and it would not unloaded automatically when your SP exits. The assembly may still in memory. If you don't handle exceptions, the opened connection will block the same SP being called again.

The second issue is that some assemblies may not work in SQL server project. I tried NHibernate and some other dlls as my references. What I found is that some assembly reflection functions are not working in SQL server. For example, I found that there always exceptions when these dlls try to load another assembly file to get class or property information. All these kind calls cause exceptions. This is very unfortunate and I think this is very bad limitation for SQL server project. I have no idea why and how exceptions would happen.

If you are going to write your SQL server project in C#, all the parameters in a SP have to be specified whey the SP is called, unlike MS Transact SQL SP could have default values for parameters.

The installation and deployment process for a SQL Server Project is a complicated one. As I mentioned in my previous articles, some asymmetric keys, log in permissions have to be created, and dependency assemblies have to be registered. The deployment of SQL server project is one click process if you have source codes and Visual Studio avalailbe. However, that click-only-once deployment may hide some SQL calls to register and set up all SPs, function and triggers. If you want to create an automation process in SQL, you have to keep all the assembly files available somewhere even they are not referenced after the installation and deployment. To uninstall it, the process is reverse. You have to remove all the SPs, functions, and triggers first, and then to delete assemblies, and then other dependency items. Since there are many things involved, anything wrong may cause your assembly not being functional.

I had a case that my CLR assembly SP did not work one day. It says about some permissions to load assembly failure. I tried to remove all the assembly and to re-install again, but I could not remove them as well. I was stuck in the middle. Finally I found it is the case SQL server was in low virtual memory. The error message actually was misleading. I restarted the SQL sever and reloaded files I did in the middle, then everything worked fine. Therefore, be prepared to handle all the uncertainties, document all the procedures in a well organized way and save all the source codes and dependency files in repository.

Read More...

Monday, June 09, 2008

SVN, Its Tools and Checkins

Subversion (SVN) is an open source version control system. I used it for source code control or repository. There are two open source SVN tools available for Windows and Visual Studio: TortoiseSVN and AnkhSVN.

Normally, I use Tortoise to check out source codes, a solution or project, to a folder and then it will create SVN client folders and files (hidden) to mark files as a copy out from a SVN reposity. Then, open the sln or project by VS. AnknSVN will detect this project is from SVN and prompt you AnkhSVN is available for use for the project.

If you make a change to a file, the file then is marked as a changed one with red icon. You can commit the change back to the SVN server or check in to the repository. Ankhn works well for editing files. However, it does not support file renaming. You have to remove the file and add a new one as two steps for renaming.

In many cases, I have to revert some changes or the whole changes by using Ankhn's context menu. Revert means no change to the file in local and re-copy the file from repository to local. In case of removing a file, you will be prompt to remove the file from the repository. If you choose Yes, this actually is commit or change to the repository! You cannot just revert the change to get it back. You have to revert to previous versions. If you have several check-ins in a day, it may be hard to find the correct one restored back. Therefore, if you want to make a trial change in local, do not remove the file from the repository. Then you can simply revert the local project back and use Update to refresh the local files if you do not want to commit the change.

If you removed file from local (not in the repository!), and verified the change is OK and want to commit you changes to the repository. Remember, the removed files are still in the repository after your check-in. Use Tortoise's repo-browser to open the repository and remove the file from the browser. That's another commit change to the repository.

In a sense, those SVN tools do not have a check out session, or SVN does not support check out set or locking a check out set. That means several developers can work on a same source code file at same time. Any developer can drop their change and that's fine for others. However, if one checks in the code file, others may have to merge their changes before check in. Ankh provides diff for comparing repository file to local files and will prompt, I think, you if your check out version has been changed by others.

Read More...

Wednesday, June 04, 2008

Add Colors to Codes

Today I did some change to my blog's template html page with some style sheet definitions from ASP.Net forums' colrcode.css.

To find the css file, you have to view its source codes and find link to css at the top of web page. There are several css files. To find the style definitions for programming codes, you can view the partial codes from the forum web page's program codes.

ASP.Net forums provide on-line posting editor with a tool bar to insert source codes with formatted HTML tags around programming source codes, such as C#, VB, SQL, XML, ASP.Net and others. That's why I choose ASP.Net forums' css. I can use its posting editor to get some formatted HTML codes and then post them to my blog. Since I use the same css file in my template, my source codes are marked with colors!

One color is missing in ASP.NET page: color for class, interface and intrinsic or predefined types. I added a new tag as classInterface in my template for these cases. You will see my example codes with colors!

Read More...

Calculate Number of Months from a Date

Here is a simple function to calculate number of months from a date to now:

public int NumberOfMonths(DateTime fromDate)
{
int months;
DateTime dt = DateTime.Now;
months = dt.Month + dt.Year * 12 -
(fromDate.Month + fromDate.Year * 12);
return months;
}


It is simple and no need to add any comments. However, when I first tried to google it, I found some very complex codes to get number of months and they are not right. Finally, based those codes, I figured out that the calculation is simple.

You may use this function twice to get difference between two dates.

One note about this function is that if the input date value is a future time, the number of months is a negative value.

Read More...

Friday, May 30, 2008

Tools and Utilities

Tools and utility programs are essential helpers for programming. I learned a lots of tools from web, blogs and other people. I remembered that when I was working at one SCADA company about 7 years ago. One developer showed me Total Commander tool with his passion. He told me it is much better than Windows File Explorer and he cannot work without it.

Since then, I tried this tool and I fall in love with it. It helps a lot for file exploring, management, and software development. When I work any where, I always bring it with me.

Many other tools are also very good. Here is a list of tools Jean-Paul and Scott Handselman recommend:



I like to read JP's blog. Some of his blogs recommended some really good tools.

Read More...

Thursday, May 29, 2008

Find a Control in ASP.Net Page

One ASP.NET page is composed of one aspx or xml file for front-end UI design and a class either in C# or VB as server side codes related to the page and control events.

Unlike window form application, the code-behind class does not know controls on the page directly. You cannot directly access control instances. For example, a label or text box control within a GridView control's template.

Here is one function I use to get a control by id:

using System.Web.UI;
...
public class WebPageUtil
{
...
public static T FindControlRecursive<T>(Control root, string id) where T: Control
{
T found = null;
if (root != null)
{
found = root.FindControl(id) as T;
if (found == null)
{
if (root.ID == id)
{
found = root as T;
}
else if (root.Controls != null)
{
foreach (Control ctr in root.Controls)
{

found = FindControlRecursive(ctr, id);
if (found != null)
{
break;
}
}
}
}
}

return found;
}
...
}


Where Control is a System.Web.UI.Control. It has FindControl() method. It can only find control within a container control, or root in this case. For example, in a GridView control named as GridView1, a TableCell control in a selected row (GridView1.Rows[0]) may contain some Label or TextBox controls, which are defined within aspx page GridView1 control's template.

However, this call only search for controls directly placed within the current control. If the control is within the next or even deep level, you have to loop its children Controls to call recursively.

I use generic type method call to make the method very simple to use. Here are some examples:

GridViewRow row = GridView1.Rows[e.RowIndex] // e is GridViewUpdateEventArgs object
LinkButton btnSave = WebPageUtil.FindControlRecursive<LinkButton>(row, "btnSave");
if (btnSave != null)
btnSave.Visible = false;

Read More...

Thursday, May 08, 2008

SQL Server Project (3)

In a SQL Server Project, all the SQL Server Objects (SSO), such as stored procedures (SP), must be defined as public static methods. All these methods are marked with Microsoft.SqlServer.Server Attributes, so that they can be deployed as SQL Server Objects.

Let's see how a SSO is called in SQL Sever. For example, when a SP is called first time, the SQL server will load the assembly library from the database, as I mentioned that all the assemblies have to be registered in a DB, into memory. Then the specified static method is called. After the SP finishes its job, however, the assembly stays in the memory forever. I tested this feature with a simple SP and a private static integer counter in its class. The counter increases by 1 for each call. The counter stays in the memory with its last call increment for several days.

This is a quite interesting feature of the deployed SSOs. Actually, if you think it in the context of SQL service process, it is not hard to understand it. The SQL service process loads the assembly into memory. Since static methods are global available, they will stays as long as the SQL service process stays.

However, this posts a problem most developers do not realize. They assume that when a call is finished, all the related resources should be released. If the assembly is not well designed, it may cause memory leaks in a SQL server. For example, if some resources are not cleaned, these resources are left in the memory for each call. You can imagine that if the SSOs were called constantly, it would cause memory leak. Another issue is that if some resources are static and not cleaned, these resources are occupied in memory. As a result, the first call is fine, but the next call may get exception since they cannot access these resources.

Therefore, you have to pay attention to all the cleaning jobs. Make sure that all the resources are freed after the execution, including the case of the execution being interrupted by clients. For example, you have to handle ThreadAbortException exception.

Talking about exception handling, it is not recommended to handle or hide all the exceptions. For example, if you design a Trigger, you may want some exceptions thrown to SQL server so that any related failure would cause the SQL server to roll back transactions. Therefore, if you know how to handle some exceptions, you can handle them, otherwise, leave them alone.

There is a way to clean assembly from memory in SQL server. Run the following command to clean all the unused cache and free up memory:

DBCC FREESYSTEMCACHE ('ALL')

I run this command in a daily job for cleaning memory used during a day.

Read More...

Wednesday, April 30, 2008

Python

Recently, I started to learn Python script language through Python Tutorial. It is a very interesting programming language. It is very powerful and unique. The reason I have interest in Python is that I read an article about Google's open source project. It mentioned that Google promotes this open source language. After reading several sections in the tutorial, I really like Python.

Another reason is that Apple's OS installs Python by default. It is a script language used as Apple's script language. I am going to learn and write some scripts. Python may be a good choice.

I just read a section about Class in Python. Within a Class, there are two types of members, one for attributes and another for methods. You don't need to define all the methods in a class. You can add or delete attributes to a class dynamically. It is really convenient. However, if you defines a attribute, you cannot dynamically to delete it.

Regarding del statement, you can use it to delete a definition of a class as well. If you create an instance of a class and then delete the class, the instance still works For example, you can still call the instance's attributes or methods. I think that the instance is created in memory like an object. In Python, every thing is object.

Read More...

Saturday, April 12, 2008

SQL Server Project (2)

As mentioned in the links in the previous article, SQL Server Project provides some basics to connect to a SQL server and communicate with the server.

Here is a brief of these basics:

  • Connect to SQL server by connection string "Context Connection=true" so that no need to specify database and authentication information for most cases, since the project is deployed to SQL debase and all the stored procedures, functions and others run in the context.

  • Send messages back to SQL server by SqlContext.Pipe.Send() method. The method call has two overloads, one taking a string as its parameter, and another one IDataReader. To call this method, no connection is needed.

  • Execute SQL statements through IDbCommand.ExecuteXXX() methods: ExecuteReader(), ExecuteNoneQuery(), and ExecuteScalar(). By using these methods, you can all SQL query and none query statements.

With all these basics, it looks that you can go ahead to create any SQL server database objects (stored procedure, function, triggers …). However, when I first time rolled my sleeves on a project, I encountered a problem. There was so limited number of references available in my Visual Studio project, about 5 only. There is no browse button for adding references neither. How can I use other Microsoft .Net libraries, as well as libraries I created?

To solve the problem, I finally realized that I have to understand the way how .Net assemblies are loaded and executed in a SQL server database. All the assemblies, including dependent assemblies, have to be registered in the server database. For example, if a stored procedure is defined in a .Net assembly, the assembly and its dependent assemblies have to be registered in a SQL server database. When the stored procedure is executed, the SQL server database will load the assembly and dependent ones from the database, not from local or remote file systems.

Therefore, if you want to add new references to your project, you have to register these libraries to the SQL server database first. The SQL server project’s reference dialog window lists only the libraries from the destination SQL server database.

To register a library to the SQL server database, you have to understand a few of additional concepts. First is the Permission set. To register an assembly, a Permission set has to be specified. There are three types: SAFE, EXTERNAL ACCESS, and UNSAFE. Try to register your libraries from SAFE mode first. If you get any error messages, you may have to try the next level till you can get them registered.

Permission sets are for security and reliability purposes. However, in most cases, you have no choice to use the least secured permission level. My understanding of those modes is that, if you run pure .Net managed codes and only for internal access, you should use SAFE mode. If you have to access to external resources such as file system, you have to use EXTERNAL ACCESS. If the libraries contain codes to access dynamical resources such as reflection load and web services or unmanaged codes, you have to no choice to UNSAFE. As a result, if you use some third party libraries, including many of Microsoft ones, you may end up with UNSAFE in most cases.

The second concept is that your assembly must be a strong named one if the permission mode is not SAFE. SQL server enforces this mandatory requirement for all none SAFE assemblies. If the assembly is not a strong named one, you have to sign it with a key by using the tool of sn.exe from Microsoft.

With these understandings, you are ready to register your dependency assemblies to a SQL server database. The followings are some SQL commands I used:

Add a key to assembly files
Signer.exe -k ..\..\myCompany_key.snk -outdir .\build -a *.dll

Add a strong name key and login permission for the key in SQL Server
Use master
IF EXISTS(SELECT * FROM sys.syslogins WHERE NAME = 'Assembly1_Login')
BEGIN
DROP LOGIN Assembly1_Login;
END
IF EXISTS(SELECT * FROM sys.asymmetric_keys WHERE NAME = 'Assembly1_key')
BEGIN
DROP ASYMMETRIC KEY Assembly1_key;
END
CREATE ASYMMETRIC KEY Assembly1_key FROM EXECUTABLE FILE = 'C:\Temp\bin\Assenbly1.dll';
CREATE LOGIN Assembly1_Login FROM ASYMMETRIC KEY Assembly1_key;
GRANT UNSAFE ASSEMBLY TO Assembly1_Login;

Register an assembly
DECLARE @asm VARCHAR(1024);
SET @asm = 'C:\Temp\bin\MyAssembly.dll';
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyAssembly')
DROP ASSEMBLY [MyAssembly]
CREATE ASSEMBLY [MyAssembly] FROM @asm WITH PERMISSION_SET = unsafe;

After your registration process, open your project in VS again. You should be able to add references to you project. I always save my registration process in a script. In case I have to reload my assemblies, or deploy the same one to another production database, I can use the script to do the job.

Read More...

Tuesday, March 18, 2008

SQL Server Project (1)

Visual Studio 2005 provides a template for creating SQL Server Project, which you can create stored procedures, functions, triggers and more. When the project is deployed to a SQL server, all the SQL database objects defined in the project are created on the server. The project is a class library. Therefore, the assembly is also created on the SQL server database. All the database objects defined in the project are dependent on the assembly.

There are some articles on this issue on the web. The following are introduction ones good for beginners.



However, if you want to get into depth of real implementation for your business cases, you will encounter many issues and there are not any good resources available, at least I have not found out yet. I started to work on SQL server project from the idea to call .Net assembly classes from SQL server stored procedure. When I googled this topic. I found the above links. That's actually what I want to do. It looks very attractive. I jumped in the ocean and that has been quite good experience.

Any way, I found out many issues I have to resolve in order to create SQL database objects for real business cases. The links above give you very good start point and some very basic skills such as to create connection to SQL server, to get data, to execute SQL commands, to insert or to delete data, and to send result back to SQL server.

The cool thing of using .Net to create SQL database objects (stored procedure or SP, function and triggers) is that you can use the current context connection to a SQL server since the SP is running in the SQL server. Then you can execute almost any SQL commands. It is very fast. All of these can be done in .Net, for example, c#.

I tried to query data and to insert data with SQL database tables, and compared to the same simple SQL SP scripts as well. For a query of repeating 1000 times, the .Net SP is a little bit faster than the SQL scripts, 00:12:00 compared to 00:12:17. For the insert statement, the SQP scripts is faster than .Net SP, by a very little difference (just seconds for 1000 rows). However if you think in more perspective view, .Net objects are much more attractive than SQL scripts.


  • .Net SQL Sever Project is very similar to PL/SQL's package. You can deploy only the SPs you want to expose, for example, and to hide all the other classes or related methods data in the library. While SQL SP scripts are public only. If you write several SPs and they have dependent relationship, you cannot hide the dependent SPs in a SQL server database. I wrote a calculation SP. Then I separate many codes to other SPs and user defined functions. As a result, the calculation SP is dependent on several other SPs and functions. Those dependent SPs and Function are not supposed to be used for public directly, but I have no way to hide them. This makes the management and usage of SPs and functions very difficult.

  • .Net SQL Server Project separates source codes from SQL Server. What you have to do is to deploy your assembly and dependent assemblies to a SQL Server Database. All the assemblies, SPs and functions are in binary forms. This avoids other people to change them directly. All the source codes can be saved in a source code repository such as SVN.

  • Since .Net SQL Server Project is written in .Net managed codes such as C#, the performance of SPs could be improved. For example, in SQL scripts, there are very limited way to cache data. One way I found to share a group of data between SPs is to use temporary table. I read some articles on this technique. It is not recommended by many people. A temporary table is created in local hard disk. If you constantly access to it such as querying, deleting and saving to the table, your script's performance would be affected. However, in a .Net project, you can easily cache data in memory. For some small amount of data, which will be used constantly during the process, I can run one query to get the data and save them in cache as a class object. Then it can be used by other classes. You don't need to do query again or read from a temporary table. That's a great improvement in terms of performance since less query is needed.

  • By using .Net Server Project, some of existing libraries can be reused. Those reused resources are hidden in assembly form or referenced to, so you can improve or update those resources without changing the public interface of deployed SPs. However, those dependent resources or library assembly files if referenced have to be registered on the SQL server database. I'll continue to discuss on this issue in later articles.

  • The .Net Server Project should provide very simple interface for deploying SQL server database objects such as SPs, Functions and Triggers. Those codes should contain SQL server related classes and objects. All the other business logic, services, and resources should be in another assembly files. In this way, the same codes can be reused in other projects such as Windows applications, console applications, and generic libraries.



I have mentioned so many advantages about SQL Server Project. However, when you get your hands dirty or do it for a real business case, you may find out it's not so easy as you do in other .Net projects. There is a difference how these assemblies are used or executed in a SQL server database or just an application in Windows. There are many issues to be resolved as well before you can fully take the advantages of .Net. For example, you may find out that there are very little references available you can add to your project at the first time. How can you reuse your libraries, third party libraries, even many other Microsoft libraries? That's the next topic for this series.

Read More...

Thursday, February 07, 2008

Use Temporary Table to Pass Global Variables

One limitation of stored procedures (SP) in Microsoft SQL 2005 is that there is no global variables you can define. You can pass values by parameters; however, if you want to add additional information between SPs which are already in use, it will be difficult to make change since the signature of SPs are already defined. Oracle's package is much more convenient. All the global variables can be defined in a package and you can even define private SPs. Off course, .Net assemblies developed by managed codes as C# offer much more choices.

Any way, I find a way to pass values between SPs without changing parameters: using a temporary table. A temporary table can be created in a caller SP and then all the callee SPs can access to it. I have implement this strategy in a SQL application and it works very well.

The structure is that a temporary table, for example #calculationInfo_CachedForCalc, is created in the starting SP. The table contains only one row of data, global variables. Some values are updated in the caller SP (they can be updated in any other SPs). Then the callee SPs will get the value for use. One problem is that the callee SPs may be called or executed in other cases, since they are public accessible. I have created a utility function to check if the temporary table exists or not. Here is the function:

CREATE FUNCTION [dbo].[udf_IsCachedDataAvailable](
@p_cachedDataType int = 0)
RETURNS int
AS
BEGIN
DECLARE @v_ret int;
SET @v_ret = CASE @p_cachedDataType
WHEN 1 THEN
WHEN OBJECT_ID( 'tempdb..#calculationInfo_CachedForCalc') IS NULL
THEN 1
ELSE 0
END
-- ... Check for other objects by other possible parameter values
END
RETURN @v_ret;
END

In the start SP, or caller SP, here are some codes:
IF dbo.udf_IsCachedDataAvailable(1) = 1
BEGIN
CREATE TABLE #calculationInfo_CachedForCalc(
[id] [int] IDENTITY(1, 1) NOT NULL,
[calcName] [varchar](50) NULL,
[logFlags] [varchar](50) NULL,
[calcCumulativeType] [varchar](10) NULL)
INSERT INTO #calculationInfo_CachedForCalc
([logFlags] VALUES(@p_parameter); -- log flags passed in by SP parameter
END

-- FETCH LOOP read a row from calculation table
-- ....
UPDATE #calculationInfo_CachedForCalc SET
[calcName] = @v_calcNam, -- Update calculation name variable
[calcCumulativeType] = @v_calcCumulativeType; -- Update calculation type var

EXEC sp_CalcFormula;
-- ....

In other callee SPs, such as sp_CalcFormula(), the temporary table is accessed to get a global variable such as log flags:
IF dbo.udf_IsCachedDataAvailable(1) = 0
BEGIN
SET @v_flags = (SELECT [logFlags] FROM #calculationInfo_CachedForCalc);
-- ....
END

Read More...

Tuesday, February 05, 2008

GridView and AJAX PopupCalendar Issue

I tried to use ASP.NET AJAX CalendarExtender control in GridView's edit template for a date field in the way just as the example show on How Do I: Configure the ASP.NET AJAX Calendar Control? That is, I added a text box binding to a field in the gridview as date, a image button next to it as a button, and a CalendarExtender control which is AJAX control. The extender control's TargetControlID is the text box, and PopupButtonID is the image control.

However, it does not work. Actually, the pop-up calendar does pop up, but it disappears right away, and the whole page is refreshed. It looks like that the lick on image button caused a call to server to send a post-back call. The example works fine, but it is too simple(a text box, a image button and a CalendarExtender on an aspx page). I am not sure why this extender does not work when it is embedded in the gridview. I think I have to find a way to prevent the post-back call so that the pop-up calendar will stay.

Read More...

Friday, February 01, 2008

SQL's NULL Marker and Its Propagation

NULL is a SQL's marker to indicate missing information or unknown status. It is essential part of relational database and SQL. One of NULL feature is that it can propagate the result in many cases and they do make sense.

For example, 1 + NULL is NULL, or NULL in any mathematical expression will result NULL. That's OK. However, it further propagates to SQL internal functions, like LEN and CHARINDEX. It is very difficult to argue why not the result is NULL. I have to say it will depend on your case.

Recently, I have been working on a project with Microsoft SQL 2005. I did not realize that LEN and CHARINDEX functions return NULL if its parameters are NULL. That caused problem in my stored procedures. What I need these functions is to find out if a specified string exists in an expression. The expression is a value retrieved from db table. For my case, if the value is NULL, the result should be either LEN = 0 or CHARINDEX = 0.

As a result, I have to overwrite these internal functions as user defined functions such as udf_LEN() and udf_CHARINDEX. Then will handle NULL parameters and return the correct result. For example, I use the following CASE statement to check an input parameter:

DECLARE @v_ret int;
SET @v_ret = CASE
WHEN @p_string IS NULL THEN 0
ELSE LEN(@p_string);

When you use internal functions, you have to aware NULL's implication or propagation in your result.

Read More...

Saturday, January 05, 2008

Initialize Castle.AR and NHiberate

I tried to use Castle Project's ActiveRecord to map a .Net class to an Oracle DB in an ASP project. I followed the instruction to create an xml configuration file, to load the configuration and then to initialize ActiveRecord like this:

  XmlConfigurationSource source = new XmlConfigurationSource(
"C:\\temp\\ActiveRecords.xml");

ActiveRecordStarter.Initialize(source, typeof(Employee));

I got a failure exception when Initialize() method is called. I struggled for almost a whole day to find out why, and downloaded Castle's example project as well. I got the same error message (System.TypeInitializationException).

Finally, I found out in the detail information in debug mode that the inner exception was caused by "could not find log4net.dll file". When I checked the path for this file in the project references, I realized that it points to a wrong dll file in the MBUnit folder. After I removed and re-added the correct dll file, the problem was solved!

Read More...

Wednesday, December 05, 2007

PageFlow, WorkFlow classes and Designer

Widnows WorkFlow Foundation provides an in-process workflow engine for many classes with visual work flow process in design mode in VS, such as Sequential WorkFlow, Machine WorkFlow, Activities, PageFlow, and so on.

By using the visual designer, you can easily define work flow, process item relationships and constrains as a work flow class. In addition to WWF core WorkFlow classes, other third parties or organizations or open source groups (such as PnP) adds other useful WorkFlow classes. The APS.NET solution I have been working on includes a project with PageFlow class.

The PageFlow class is defined within Microsoft.Practices.PageFlow.WorkFlowFoundation.dll by Microsoft Patterns and Practices group. The problem I encountered is that after I created the project by using Guidance Package Manger Recipe of Add PageFlow Project, I got an error message when I tried to open PageFlow1.cs in design mode in VS, but I don't have problem to compile the project. It looks like that its related designer class is not available.

Actually, the designer class is Microsoft.Workflow.VSDesigner.dll. I have to search for this file first. Then I copy this file to the folder where ...WorkFlowFoundation.dll file sits. After making the designer class directly available to the owner assembly, I can see the class in design mode in VS after I re-open the VS again.

Read More...

Tuesday, December 04, 2007

Add Reference to Visual Studio 2005

I have installed .Net Framework 3.5 in my box (not 3.0) and WCSF for a ASP.NET project based on WCSF. When I tried to use Guidance Package Manger's Add Page Flow Project recipe I got an error and failed to add the project.

I wondered why it failed. It must use some kind template to add project. I then found the template project which has the following references but I cannot see them in my VS Add Reference Dialog window:

<Reference Include="System.Workflow.Activities" />
<Reference Include="System.Workflow.ComponentModel" />
<Reference Include="System.Workflow.Runtime" />

I think that is the reason I get the failure. My direct reaction was that I thought .Net 3.5 may not include or install 3.0 assembly files. That's wrong. From VS Add Reference list, I can see that System.WorkFlowServices.dll is located in C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\, and others which are not in VS's Add Reference window are in ...\v.30 folder.

It must be some settings such as environment or registry settings which make these assembly files visible in VS. I can find where those settings are. Anyway, cut the story in short, I found a msdn link about How to: Add ore Remove Reference in Visual Studio 2005. This web page does provide information on how to add customized assemblies in VS by setting windows registries. I followed the instruction I found that the NUnit and MbUnit assembly files I installed are set in the registry and they are in VS Add Reference window. Here are examples of NUnit 2.4.3 and MbUnit in the current user registry:
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\.NETFramework\AssemblyFoldersEx\NUnit 2.4.3]@="c:\Program Files\NUnit 2.4.3\bin\"
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\.NETFramework\v2.0.50727\AssemblyFoldersEx\MbUnit]@="c:\Program Files\MbUnit\"

Notice that NUnit is not related to any version while MbUnit is under .Net v2.0.50727. According to the msdn, if you add assembly location folders in HKEY_LOCAL_MACHINE in the same way, any user in the pc then can see these assemblies in VS.

Those are settings for third party to add their assemblies to VS 2005's Add Reference window. However, I still can not find any thing bout Microsoft assembly settings. I know where assembly files are, but I don't know what settings to cause them visible in the VS 2005's Add Reference window. Maybe Microsoft does not want people to mess up third party programs with Microsoft ones.

Anyway, I finally find a way to get them visible in VS 2005. I installed Visual Studio 2005 extensions for .NET Framework 3.0 (Windows Workflow Foundation). The installation did some magic settings and I can add Page Flow Project without any failure and I do see these v3.0 WowkFlow assembly files in VS 2005.

Read More...