Object Reference .NET

ref this.Object

Construct a comma delimited list from table column with SQL

clock January 15, 2009 15:44 by author Magz
How many times have you had to create a comma delimited list from a table column? Do you ever remember how to do it? I don’t. Here is a little snippet I find useful:

-- this will be a comma delimited list of location ids
DECLARE @LocationIds Varchar(Max)
-- set it to an empty string to start with
SET @LocationIds = ''
 
--add commas 
SELECT @LocationIds = COALESCE(@LocationIds + ',','') + CAST(Location_ID AS VARCHAR) FROM Locations_Table
--get rid of the comma at the front of the string
SELECT SUBSTRING(@LocationIds, 2, Len(@LocationIds))


Result: 1234,3445,6778,6789,…

kick it on DotNetKicks.com


RegEx Multiple Matches with Lookahead Assertion

clock November 27, 2008 09:41 by author Magz
This is a quick article where I would like to show how to use regular expression to get all matches of a particular pattern from the string.

Example of a string:
string strHTML = "<span>key1<span>value1
                  <span>key2</span>value2
                  <span>key3</span>value3";

To get the list of key-value pairs we need to parse the above string and return a list of matches, where each match will contain key in Group[1].Value and value in Group[2].Value. The first part of the pattern is clear: (.+?)(.+?) but the tricky bit is to know how to define an end match condition. Here I suggest to use an operator (?=exp) that will match any position preceding a suffix exp, in our case we want to stop the match if we find another occurrence of tag or reach the end of the string ($)

MatchCollection mc = Regex.Matches(strHTML, @"<span>(.+?)</span>(.+?)(?=(<span>|$))");
foreach(Match m in mc)
   Response.Write(m.Groups[1].Value + " " + m.Groups[2].Value + "</br>");

Result:
key1 value1
key2 value2
key3 value3

What will happen if you don’t include (?=exp) operator? You will lose the middle pair and the result will be:

key1 value1
key3 value3

kick it on DotNetKicks.com


SQL: Find last week date range

clock November 24, 2008 08:59 by author Magz
The other day we needed to write a report on online sales for the last week. SQL doesn’t offer developers many predefined functions to work with date ranges unlike the C# programming language. Here is a little example how to query SQL for some data between the dates for the last week.

By default the first day of the week is set to Sunday (default value 7), so if you need it to be Monday run the following command:

SET DATEFIRST 1
SQL Last Week Date Range
DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
 
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())

Now we can use above expressions in our query:

SELECT …. FROMWHERE Sale_Date BETWEEN 
CONVERT(VARCHAR, @StartOfPrevWeek,7)
AND
CONVERT(VARCHAR, @EndOfPrevWeek+1,7)

Note that we had to convert dates in order to reset minutes/hours/seconds to 00:00:00, but it also means that if we want to include the whole Sunday into our week report we need to set the dates BETWEEN Monday AND Monday 00:00:00.

kick it on DotNetKicks.com


ListBox Extension Method for Get and Set Selected Values

clock August 11, 2008 11:01 by author Magz
ASP.Net ListBox control allows two types of selection mode: single and multiple. Working in single selection mode is similar to working with DropDown list control. Use ListBox.SelectedValue to get the value that was selected by a user. In order to set a selection SelectedIndex property can be used or Selected property of a particular item should be set to true.

Example:
string selectedValue = MyListBox.SelectedValue; //get selected value
        
MyListBox.SelectedIndex = 0; //set the first item to  be selected
MyListBox.Items.FindByValue("val2").Selected = true; //select the item with value="val2"

However when using ListBox with SelectionMode="Multiple" ListBox.SelectedValue will return only the first selected item, not all of them. The code below shows how to create a simple extension method for a ListBox that will allow to get and set multiple selected items.

public static class ListBoxExtensions
{
    //return ArrayList of selected values
    public static ArrayList SelectedValues(this ListBox lbox)
    {
        ArrayList selectedValues = new ArrayList();
 
        int[] selectedIndeces = lbox.GetSelectedIndices();
        foreach (int i in selectedIndeces)
           selectedValues.Add(lbox.Items[i].Value);
        return selectedValues;
    }
    
    /// <summary>
    /// Select multiple items in a ListBox
    /// </summary>
    /// <param name="values">Values of the items that need to appear selected</param>
    public static void SelectedValues(this ListBox lbox, string[] values)
    {
        foreach (string value in values)
        {
            ListItem item = lbox.Items.FindByValue(value);
            if (item != null)
                item.Selected = true;
        }        
    }
}

Example:
<asp:ListBox ID="MyListBox" runat="server" SelectionMode="Multiple">
        <asp:ListItem Text="ItemText1" Value="val1"></asp:ListItem>
        <asp:ListItem Text="ItemText2" Value="val2"></asp:ListItem>
        <asp:ListItem Text="ItemText3" Value="val3"></asp:ListItem>
        <asp:ListItem Text="ItemText4" Value="val4"></asp:ListItem>
        <asp:ListItem Text="ItemText5" Value="val5"></asp:ListItem>
</asp:ListBox>    

List Box Example
To set items to be selected use

MyListBox.SelectedValues(new string[] {"val1", "val3"});

To get selected values call

ArrayList selectedValues = MyListBox.SelectedValues();

kick it on DotNetKicks.com


Get Checked Repeater Items Extension Method

clock June 16, 2008 17:24 by author Magz
Imagine you have a list of some items and checkboxes next to each item providing the ability for a user to make multiple choices and submit a result in one go. Weather you are implementing a news groups subscription, user survey or online products catalogue - the code behind is the same: we bind some object list to a data control like Repeater, add a checkbox to every item, set checkbox value to item ID.

Here is an example with list of books available for order

Repeater with checkboxes
<table>
    <asp:Repeater ID="rptBooks" runat="server"> 
        <ItemTemplate>
            <tr>     
                <td><input type="checkbox" runat="server" value='<%# Eval("BookID")%>' ID="chkBox" /></td>
                <td><%# Eval("Title")%></td>
                <td><%# Eval("Author")%> </td>
            </tr>
        </ItemTemplate>  
    </asp:Repeater>
</table>
<asp:Button ID="btnOrder" OnClick="btnOrder_Click" runat="server" Text="Order" />

When “Order” button is clicked we need get all selected book IDs (we store them in a value field of each checkbox). I suggest that we create an extension method that will do all the work and when we need to get selected items in any repeater in our code we will have to call only one method GetSelectedItems

public static class RepeaterExt
{
    /// <summary>
    /// Returns selected items array list in a repeater rpt
    /// </summary>
    /// <param name="chkBoxId">ID of a checkbox used in repeater to store the value of each item</param>
    public static ArrayList GetSelectedItems(this Repeater rpt, string chkBoxId)
    {
        var selectedValues = new ArrayList();
        for (int i = 0; i < rpt.Items.Count; i++)
        {
            var chkBox = rpt.Items[i].FindControl(chkBoxId) as HtmlInputCheckBox;
 
            if (chkBox != null && chkBox.Checked)
                selectedValues.Add(chkBox.Value);
        }
        return selectedValues;
    }
}

Now if we go back to our page with books catalog we can right the following:

protected void btnOrder_Click(object sender, EventArgs e)
{
using extenstion method to get checked items
selectedValues array will contain BookIDs that were ordered and you can store them in the database or process as required by your code.

Note that this example assumes that you need to use <input runat=”server”…  to be able to pre-polulate checked values or keep the state of the checkboxes after the postback. If none of this functionality is needed, the simplest way to get selected values is to use standard html

<input type="checkbox" value='<%# Eval("BookID")%>' name="chkBox" />

When the “Order” button is clicked Request["chkBox"] will return comma delimited list of selected BookIDs.

kick it on DotNetKicks.com


Enum TryParse Extension Method

clock April 21, 2008 14:43 by author Magz
TryParse method is very helpful if you need to convert string representation of a value to a value itself. TryParse is better than Parse method because it doesn’t throw any exceptions and just returns a Boolean value to indicate weather the parsing was successful. Surprisingly there is no TryParse method available to use for Enum and this is where extension method can be extremely useful.

public static bool TryParse<T>(this Enum theEnum, string valueToParse, out T returnValue)
{
    returnValue = default(T);
    int intEnumValue;
    if (Int32.TryParse(valueToParse, out intEnumValue))
    {
        if (Enum.IsDefined(typeof(T), intEnumValue))
        {
            returnValue = (T)(object)intEnumValue;
            return true;
        }
    }
    return false;
}

Now it’s time to test our extension method! Here I have a simple UserType Enumeration
public enum UserType
{
    None = 0,
    Administrator = 1,
    Manager = 2,
    Consultant = 3
}

I want to parse QueryString parameter usertype and store the result in currentUserType variable.

currentUserType.TryParse(Request.QueryString["usertype"], out currentUserType);

if Request.QueryString["usertype"] is invalid UserType then currentUserType variable will be set to None (0).

kick it on DotNetKicks.com 



RecentComments

Comment RSS

Sign in