Collection Filter Function

Let's continue the topic of my previous blog on .Net Generic and Value Data Type with an example of collection filter function for generic value data types. This function takes two collections, the first one as input collection and another one as filter collection. The returned collection will filter out all the items in the filter collection from the first collection. In this case, in order to filter items, the value data type makes sense when comparing equalities for all the items in a collection to an item. For reference data types, however, it is hard to tell equality or not, by reference or property members? You even can expect multiple-levels if some property members are reference types.

public IEnumerable<T> FilerCollections<T>(
  IEnumerable<T> list,
  IEnumerable<T> filterList)
  where T: struct
  {
  if ( list != null )
  {
    foreach (T item in list)
    {
      bool found = false;
      if (filterList != null)
      {
        foreach (T filterItem in filterList)
        {
          if (filterItem.Equals(item))
          {
            found = true;
            break;
          }
        }
      }
      if (!found)
      {
        yield return item;
      }
    }
  }
}

Here is the test program:

Test t = new Test();
IEnumerable<int> list;
List<int> list1 = new List<int>(new int [] {1, 2, 3, 4});
List<int> list2 = new List<int>(new int[] {3});
list = t.FilerCollections<int>(list1, list2);
if (list != null)
{
    foreach (int item in list)
    {
        Console.WriteLine("item: {0}", item);
    }
}
else
{
    Console.WriteLine("List is empty");
}

and the result screen:

Read More...

Collapse...

.Net Generics and Value Data Type

I use .Net Generics a lot. The great benefit is that you can make your codes reusable even you may use it only for one data type. You may extract the generic class or method to a library to a library for reuse. Secondly, it still keeps type safe or strong type integrity. Any attempts to use wrong type will be caught at compile time instead of run time.

The generics can be specified by where clause. In most cases I use generics for reference data types, i.e., classes. If you say a generic as class, that means the generics data type is a reference type. You can use new() to further indicate the class can be instantiated by the default constructor with no parameters. If you specify the class a specified class, that means any class which inherits from the class can be used for the generics class or method.

You cannot use more than one classes in where clause, however, you can use interfaces to specify that the generic class should implement some interfaces. Then in your generic class or method, you can safely call the implemented interfaces or methods.

How about value data types such as int, string or double as examples. You can use struct in the where clause. If you want to use the nullable value data types, you can simply use ? after the generic type, such as T?.

class Test
{
  public T GetValueOrDefault<T>(T? item) 
    where T: struct
  {
    return item.GetValueOrDefault();
  }
  public T? GetValue<T>(T? item) 
    where T: struct
  {
    if (item.HasValue)
    {
      return item.Value;
    }
    else
    {
      return null;
    }
  }
}

here are codes to call the methods:

Test t = new Test();
int? a = 2;
a = t.GetValueOrDefault<int>(a);
//t.GetValueOrDefault<Test>(t); //Error: t is not none-nullable data type
Console.WriteLine("GetValueOrDefault<int>(a) = {0}; GetValue<int>(a) = {1}", 
    t.GetValueOrDefault<int>(a), t.GetValue<int>(a));
a = null;
Console.WriteLine("GetValueOrDefault<int>(a) = {0}; GetValue<int>(a) = {1}", 
    t.GetValueOrDefault<int>(a), t.GetValue<int>(a));
Console.WriteLine("GetValueOrDefault<int>(a) = {0}; GetValue<int>(a) = {1}", 
    t.GetValueOrDefault<int>(3), t.GetValue<int>(3));

 

The result of the above codes in a console is:



Here is an interest posting on .Net Generics and a question about enum data type.

Read More...

Collapse...

SQL Nested SELECT Statement in FROM Clause

You can create a view by using SELECT statement. For example, a View called as AllEmployeeInfo by SQL statement: SELECT * FROM Employees. Employees is a table. That's quite simple. Then you many list all employee names hired after '2005-01-01' from the view:

SELECT FirstName, LastName
  FROM AllEmployeeInfo

Is there any way to use SQL SELECT statement as a subquery directly in FROM clause? For example:

SELECT FirstName, LastName
  FROM SELECT * FROM Employees

This does not work in Microsoft SQL 2005. However, if you name the subquery SELECT as an alias, you can do it. Here is the example:

SELECT FirstName, LastName
  FROM (SELECT * FROM Employees
          WHERE HireDate > '2005-01-01') AS Temp
-- or
SELECT FirstName, LastName
  FROM (SELECT * FROM Employees) AS Temp
  WHERE HireDate > '2005-01-01'

It looks like that SQL will create a temporary or dynamic view for the alias, then you could SELECT columns from there. Quite cool? I tried this in PL/SQL Oracle 9.0 but it does not work. Anyway, I like this quick way to get data. Sometimes you may need this kind of nested SELECTs so that you can filter data by several levels and avoid to create unnecessary views.

More on nested SQL queries, read this blog: Using a Subquery in a T-SQL statement.

Notes and Updates on this blog: Actually, the nested SELECT statement is available in Oracle or PL/SQL as well, but you don't need to AS in the statement. I got this correction from my StackOverFlow Q&A. Thanks for the correction!

Read More...

Collapse...

ASP.Net and Data Source Controls

In an ASP.Net page, many asp controls can be bound with a data source control, such as SqlDataSource and ObjectDataSource. I prefer to use ObjectDataSource than SqlDataSource. Here are my two main reasons.

First SqlDataSource includes a SQL statement in the control. If you change different data DB source from Microsoft SQL to Oracle DB, the SQL statement may be different. Therefore, you may have to make change in the UI aspx page or server side .Net class codes.

ObjectDataSource is binding data source from a a class's methods to get or update data. The class hides the way how data are retrieved or saved. I call it as business logic layer class (BLL). The class can be defined in another library assembly. This will make unit test much easier.

Second reason is that you don't have control how many times to call SqlDataSource to get data. For example, if you have a combo box in a GridView for product types and link it to a SqlDataSource to get product types. Each combo box will call the SqlDataSource to get product types when GridView is initialized. If there are tens or hundreds of rows in the GridView, there will be tens or hundreds call to database to get the same data. Even worse, in case of post-back calls, the GridView will be refreshed again. This may make the front UI very slow if the retrieving data process is slow.

In BLL class, you can easily cache data to avoid unnecessary calls to database. Simple define your data collection as a static List in the class. Only the first time when the page is initialized the static data member is populated with data from database. The subsequent calls will get data from the cache. You may need to define a reset method in the BLL class so that in the Page_Load event, if it is not post-back call, call this reset method to reset the static member to null. Then when the GridView is initialized, the data will be obtained from database for the first time.

Read More...

Collapse...

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...

Collapse...

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...

Collapse...

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...

Collapse...