execute dynamic query from stored procedure

After realized the crazy requirements of the site locator program, I know a common store procedure won't work. However, because of our database name is different in each enviorment (staging, QA, prod), the dynamic query in the code won't work either. The only solution is to use sp to run dynamic query. the sql syntax looks like this:

EXECUTE SP_EXECUTESQL @sqlStatement

where @sqlstatement is nvarchar (max 4000 charaters). The caveat here is 4000 is really short, and a long/complex query will easily exceed this limit.

MVC study and thoughts

First of all, I think MVC is a big deal. It separate concerns of data layer, business layer and presentation layer. If used well, it will generate much cleaner and maintainable code than regular web form. However, I found MVC falls short in the following areas:

• report. it always seems to be true: a report should just stay simple instead of trying any other advanced approaches such as TDD or MVC. In the end, it's just report which represents data directly from the data layer, and most likely with lots of fancy staff on the UI
• dynamic HTML (with input). if you don't know how many controls you page will have to generate, and also you are expecting inputs from these controls, it's challenging using MVC.
• JQuery in custom while using AJAX, this seems like not a big issue and not directly related to MVC, but since MVC use AJAX and Jquery a lot, so just be aware

Speech server application and Cache

In the speech server application I am creating, I need to load some data and save it into the Cache so every call can share that data in a readonly fashion. The normal way for me to access Cache is to use HttpContext.Current.Cache. However, HttpContext.Current is null in speech server application. I tested in both IIS 5.1 and 6.0 with soft and regular land phone. I then began to use WorkFlow.UserData as the application Cache. It works fine except you can't set its expiration time. After a little research and I found that I can acutally access Cache by HttpRuntime.Cache, and it even performance a little better. A good article to compare these two different way of accessing Cache is here.

speech server running under iis 5.1

It seems like the speech server is only designed to run under iis 6.1 or higher. According to this reference: It "Setup program creates an application pool called Speech Server under which Speech Server applications are intended to run". and by default "The Speech Server processes run as the NETWORK SERVICE account". It should use this default account when accessing local resource and use current windows account to access network resource (such as database and shared file server).
However, the IIS 5.1 doesn't have application pool and web app running under 5.1 is using [machinename]/ASPNET user account by default. It's uncertain if speech server has issue to use this account to access local resource, but it definitively failed to use local windows account when accessing network resource under IIS 5.1. The error developer will see is like: Login failed for user ''. The user is not associated with a trusted SQL Server connection. Please note the user account here is a empty string.
Solution is to update to force impersonation in the machine.config under the currently used .NET framework folder. An example would be like this:
<processmodel password="[password]" username="[domain]\[username]">

here are some other good references helped me regarding this issue:
Understanding ASP.NET Impersonation Security
processModel Element (ASP.NET Settings Schema)

list<> sort or order using limbda expression

If doing an in-place sort (i.e. the list is updated):
people.Sort((x, y) => string.Compare(x.LastName, y.LastName));

If need to create a new list:
var newList = people.OrderBy(x=>x.LastName)

Springboard example, sorting sections on section No.

List

sections = new List

();
//... load sections ...
sections.Sort((x, y) => x.SectionNo.CompareTo(y.SectionNo));

insert from selecting from another database

In one case, I need to import data from one database to another database, and I've figured out the format should be

insert into server.newdatabase.owner.table
from select * from server.olddatabase.owner.table

the key point here is the four parts name follows the pattern server.database.owner.object

using delegate in sorting generic list

   1:  List<Article> allArticles = GetAllArticles();

   2:  allArticles.Sort(delegate(Article x, Article y) { return DateTime.Compare(x.LiveDate, y.LiveDate); });

sharing content between different sites

Constantly we are facing some issues when sharing content of site A with site B. Normally the content to be shared is header/footer, and they are user controls at site A. On the other hand, site B wants an easy yet reliable way to access the content and automatically updated content without changing anything on its own side. During past years of building application to solve this problem, I think these two designs are both good solutions:

Design A

• site A wrap the user control into web service
• site B call web service to get html content
• site B implement both memory cache and cache file to fail back on in case web service call failed. when failing, it trys to load from memory cache first, if there is no content in memory cache, then load it from cache file. when next time a web service call is successful, update memory cache and cache file

pro of Design A:

• very reliable as both memory cache and cache file are available for fall back

cons if Design A:

• site A need to be careful in implementing user control, can't assume some page information like session is available is httprequest
• complex logic in site B to implement fall back workflow

Design B

• Site A wrap user control in a regular aspx page, using javascript document.write to emit content
• Site B consume content by reference to this page as a javascript source

pro of design B:

• easy to implemet on both sides

cons of design B

• no fallback logic, when site A is done, it screw up site B as well

Both wraping solution can't solve the issue if the content need to post back to itself after submit. For example, if a user control has some code behind logic after submit, it won't be available in both wrapped version.

Source code:

Design A at Site A:

                    HeaderControl hc = (HeaderControl)p.LoadControl("~/UserControls/" + ControlName);
                    hc.IsSecure = IsSecure;
                    hc.IsOLSMenu = true;
                    hc.Page_Load(null, null); //call page_load to load mennu
                    hc.RenderControl(htmlWriter);

Design B at site A:

                    StringWriter sw = new StringWriter();
                    Page p = new Page();
                    HtmlForm f = new HtmlForm();
                    p.Controls.Add(f);
                    Header h = (Header)p.LoadControl("~/controls/header.ascx");
                    p.Controls[0].Controls.Add(h);
                    Server.Execute(p, sw, false);

Design B at site B:

<script language="javascript" src="http://ta-homecorporate.progressive.com/scripts/headerwrapperjs.aspx"></script>

asp.net remote debugging

(1) make sure The Remote Debugging Monitor (Msvsmon.exe) is installed on the server,
(2) if not, you can share your local Msvsmon.exe on "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Remote Debugger" folder, and run it from the remote server
(3) make sure dll and pdb files are the same on both local and server ( copy from local if doubt)
(4) attach process from vs, use default transport (not remote transport), and type server name (not ip address), make sure it attach to "managed code" (not "native code")
(5) attach to process w3wp.exe, if multiple process of w3wp.exe exist, use this tool (systemroot\system32\iisapp.vbs) to find out which process match your application pool
(6) put a break point on local and open browser to request server page
(7) debugging ...

warning user when leaving page without submitting it

There are many cases that user leaves a page intended or accidently and of-course all the changes he made won't be saved. In these cases user has to go back and re-make his change, and it causes frustrations, therefore it's not the best user experience we want to provide in our web applications. Luckly, the solution is pretty simple and it could save user's time and also enhance our web applications' usability.
When user leaves the page, we can show an confirmation javascript window, if user click "No", he will stay on the page. This works on every links on the page, the back button and even the close window action can be canceled. The key part of this solution is tie "window.onbeforeunload" event with a javascript function. The following function shows how to emit the javascript function from asp.net code behind.

protected void EmitJsConfirm()
{
    string jScript = String.Empty;
    if(!Page.IsClientScriptBlockRegistered("jsConfirm"))
    {
        jScript = String.Format(@"<SCRIPT language='javascript'>var needToConfirm = true;function confirmExit()
            {{if (needToConfirm){{return '{0}';}}}}
            window.onbeforeunload = confirmExit;</SCRIPT>",_jsConfirmMsg);
        Page.RegisterClientScriptBlock("jsConfirm",jScript);
    }
}

However, this confirmation message will show up even the page postback to itself, such as user click button to sumbit or a dropdown list auto postback. So we need a way to prevent these control's events from showing the confirmation message. Following function will do the trick. Note that we treat control differently, if it's dropdown we use "onChange", otherwise we use "onClick".

protected void ExcludeControlFromConfirm(params WebControl[] controls)
{
    foreach(WebControl c in controls)
    {
        if(c is DropDownList)
            c.Attributes.Add("onChange","needToConfirm = false;");
        else
            c.Attributes.Add("onClick","needToConfirm = false;");
    }
}

Prameter in web service is not LOCAL

for functions if you keep the name as the same, all passed by value parameters should be like local variables. therefore you can change its name without affecting outside calls for this function. but it's not the case for web service, for example if you have an old web method
void ReturnContent(bool Secure)
and you changed it to void ReturnContent(bool IsSecure) , then it won't work as expected, and the web method will always default the value "IsSecure" to false since it failed to get the node from the soap message.

A ideal solution for managing site url mess

In the recent project I've been working on we were running into 2 issues related to the URLs: first we have links all over on the pages which link to each other, if we rename a page or move it to a different folder, we need to change every place where links to this page. Secondly, we have couple pages that need to be secured (i.e. https is needed). but all the urls on the site are relative (which is good), so once user is redirected to the secure page, he will stay on secure pages because all the links on the page start with https.

An ideal solution would be:
(1) all the urls of the site should be saved in a config file (xml formatted) or resource file
(2) create a custom control to generate <a> tag. which we can put more properties like (bool)IsSecure,

Reference variable in Applicate State

Most of us might think that when you save something to session, cache or other application state, ASP.NET save a copy of the variable to it. Take a cache for example, if you save a integer like this, we all know the cache value won't change

   1:  int i = 1;

   2:  Cache["Number"] = i;

   3:  i++;

   4:  //Cache["Number"] will still be 1

However, if you save a reference value, the cache object value changes as the reference object changes in the memory. like the exampel below the tc object in the cache is also changed when the tc in the memory changes. the Number property becomes 2.

        private void TestCache()
        {
            TestClass tc;
            if (Cache["TestClass"] != null)
                tc = Cache["TestClass"] as TestClass;
            else
            {
                tc = new TestClass();
                Cache["TestClass"] = tc;
            }
            tc.Number = 2;
        }

    public class TestClass
    {
        public int Number = 0;
    }

Another thing we should be aware of is: if you set the tc to null (tc=null), the object is still there in cache. This is because the tc=null state just breaks the reference of the tc and object allocated in the heap memory. so conside the following example, the Number property of the object in the cache is still 1 even if the tc.Number set to 2 later.

        private void TestCache()
        {
            TestClass tc;
            if (Cache["TestClass"] != null)
                tc = Cache["TestClass"] as TestClass;
            else
            {
                tc = new TestClass();
                Cache["TestClass"] = tc;
            }
            
            tc = new TestClass();
            tc.Number = 2;
        }

HtmlGeneric Control to render

control won't render as html , and in order to do that from the code behind, you need to use HtmlGeneric control with "Label" as the tag. code example as below

   1:        HtmlGenericControl lblProduct = new HtmlGenericControl("label"); 

   2:        lblProduct.Attributes.Add("for", "Product"); 

   3:        lblProduct.Attributes.Add("class", "banner"); 

Relative path and menu highlight

One of the beatiful thing about Asp.Net is you can use ~ to make a relative path, and .NET will figure out the full path at the run time no matter what environment the application sits on. (For example, your app might not be on the root folder on the QA box as it in production). If you use .NET built-in menu the highlight functions is alreay there. meaning the a menu item can be highlighted if you are on this target page. However, if you are building a menu by yourself, you have to implement it by yourself, and this is how you can do it:
(1) in the menu source file, normally a xml file, you put the app relative path with ~ in it, for example use ~/myfolder/mypage.aspx instead of /myfolder/mypage.aspx
(2) in the load event of the menu control, whether custom or user control, check the if current Page.AppRelVirtualPath is equal to a particular menu item, if so highlight that menu item

that's it

efficient way to reload database

I have to reload a huge database by a program regularily. There are two datasources for this database, but there are some user inputed information in the database too, like comments. here are some experiences I gain from doing this:

(1) selected the user inputed information into a hashtable or arraylist with database primary key, a simple struct or class will serve the purpose
(2) in database table, have backup indicator column, when insert, only insert as backup data
(3) keep count the error rate when inserting
(4) at the end, if everything is fine, error rate is low, delete the no-backup data and flipover backup data to live
(5) make sure all queries to the database only get live data, not backup data. Or to create a view for this

Search dataset/dataview using primary key

If you have a huge dataset, the performance of searching a record using dataset.Select won't be efficient. The best way to do this is to make primary key(s) of the dataset/dateview.

following line add two primary keys to the dataview
DataView dv = new DataView(dt,null,"pol_nbr,pol_sfx",DataViewRowState.CurrentRows);
then you can search a row by these keys

object[] keys = new object[2];
keys[0] = "0000"+p.Number;
keys[1] = p.Suffix;
DataRowView[] drv = dv.FindRows(keys);

Simply Ajax Solution

No need any AJAX framework or fancy update panels, just by using some simple javascript you can get some cool Ajax effect, plus web service is not needed.

        function GetSLEmailByID(controlID, pol_nbr)
          {
              var xmlHttp = new ActiveXObject("Msxml2.XMLHTTP");
                xmlHttp.onreadystatechange=function()
                  {
                      if(xmlHttp.readyState==4)
                    {
                        try
                        {
                            document.getElementById(controlID).innerHTML = xmlHttp.responseText;
                        }
                        catch(e)
                        {
                            alert(controlID+"\n"+e.message);
                        }
                    }
                  }
                xmlHttp.open("GET","slgetemail.aspx?pol=" + pol_nbr,true);
                xmlHttp.send(null);
        }    

In this function, xmlHttp.onreadystatechange set a callback function, while xmlHttp.open("GET","slgetemail.aspx?pol=" + pol_nbr,true); initial the call of another page to get emails using a query string. So the whole content of the page will be parsed in the callback function and display the email.

Business layer sorting

Sometime, you need to sort a list of business object, for example you have a list of SLPolicy object in an arraylist, and you want to sort it in different ways, like by policynumber, expiration date, or agent code. You want to do this as easy as this:
ArrayList Policies = CreateListofPolicies() ;
SLPolicy.SortBy = "PolicyNumber";
Policies.Sort();
if that's what you want, you need to implement some class like below:

    [Serializable]
    public class SLPolicy : IComparable
    {

        #region IComparable Members

        public int CompareTo(object obj)
        {
            switch (_container.Container.SortKind)
            {
                case SLSortableKind.AgentCodeAsc:
                    return _agentCode.CompareTo(((SLPolicy) obj).AgentCode);

                case SLSortableKind.AgentCodeDesc:
                    return ((SLPolicy) obj).AgentCode.CompareTo(_agentCode);
                 
                case SLSortableKind.InsuredNameAsc:
                case SLSortableKind.InsuredNameDesc:
                case SLSortableKind.ExpDateAsc:
                    return _expDate.CompareTo(((SLPolicy) obj).ExpDate);
             
                case SLSortableKind.ExpDateDesc:
                    return ((SLPolicy) obj).ExpDate.CompareTo(_expDate);
             
                default:
                    return 0;
            }
        }

        #endregion

    }

Crazy business requirements

I am going to organize all the good parts of the recent projects. But before that, I'd like to write down these nosense business requirements I've ever heard in this company. I believe it will fun to read them even couple years later.

(1) one business people asked if we can program the web application so that we can tell if customers are using keyboard or mouse, and if they are using keyboard, how fast they can type

(2) another business people wants to find out if user is visiting a page purposely or just accidentally click the wrong link.