Blog Archives

asp .net grid control common gridview operations update edit cancel sort paging

There are many ways to accomplish the end result of what is displayed below.

To display multiple records of data on the same page, you may alternatively use Repeater (my favorite of the dataview controls), DataList or ListView (also great).

FormView and DetailsView are similar, but should be limited to smaller sets of data IMO or in a system where limited results are only displayed in this control after selection from a search or query filter.

The control on the HTML design side:

<asp:UpdatePanel ID="upnlContent" runat="server" UpdateMode="Conditional" ChildrenAsTriggers="false">
            <ContentTemplate>
  <div id="modal" style="display: none;" title="Application Message">
                    <asp:Label ID="lblError" runat="server"></asp:Label>
                </div>
<asp:GridView ID="gvDataList" runat="server" AllowPaging="True" OnRowEditing="gvDataList_EditRecord"
                        OnRowCancelingEdit="gvDataList_CancelRecord" OnRowUpdating="gvDataList_UpdateRecord"
                        AutoGenerateColumns="False" AllowSorting="true" PageSize="50" DataKeyNames="TreeID"
                        OnRowCommand="gvDataList_RowCommand" OnSorting="gvDataList_Sorting" OnPageIndexChanging="gvDataList_PageIndexChanging">
                        <AlternatingRowStyle BackColor="#EFEFEF" />
                        <Columns>
                            <asp:BoundField DataField="TreeID" HeaderText="TreeID" InsertVisible="False" SortExpression="TreeID"
                                ReadOnly="True" />
                            <asp:TemplateField HeaderText="ActiveStatus" SortExpression="ActiveStatus">
                                <ItemTemplate>
                                    <asp:Label ID="lblActiveStatus" runat="server" Text='<%# Eval("ActiveStatus")%>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:CheckBox ID="chkActiveStatus" runat="server" Checked='<%# Convert.ToBoolean(Eval("ActiveStatus")) %>'
                                        Enabled='<%# GetEditPermission("activestatus") %>' />
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Name" SortExpression="Name">
                                <ItemTemplate>
                                    <asp:HyperLink ID="Name" runat="server" NavigateUrl='<%# "~/Pages/ExistingOrange.aspx?TreeID=" + Eval("TreeID")%>'>
                        <%# Eval("Name")%>
                                    </asp:HyperLink>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="tbtreename" runat="server" Text='<%# Eval("Name") %>' />
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Description" SortExpression="Description">
                                <ItemTemplate>
                                    <asp:HyperLink ID="hltreedescription" runat="server" NavigateUrl='<%# "~/Pages/ExistingOrange.aspx?TreeID=" + Eval("TreeID")%>'>
                        <%# Eval("Description")%>
                                    </asp:HyperLink>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="tbtreedescription" runat="server" Text='<%# Eval("Description") %>' />
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Country" SortExpression="CountryID" ItemStyle-HorizontalAlign="Center">
                                <ItemTemplate>
                                    <asp:Label ID="lblCountryID" runat="server" Text='<%# getcountryname(Eval("CountryID"))%>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:DropDownList ID="ddlCountryID" runat="server" Enabled='<%# GetEditPermission("allowregion") %>'
                                        DataSource='<%# countriesdropdown %>' DataTextField='Description' DataValueField="CountryID" />
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField DataField="LastUpdated" HeaderText="Last Updated" SortExpression="LastUpdated"
                                ReadOnly="true" />
                            <asp:BoundField DataField="ExpirationDate" HeaderText="Expiration Date" InsertVisible="False"
                                SortExpression="ExpirationDate" ReadOnly="True" />
                            <asp:BoundField DataField="runcount" HeaderText="Pick Count" InsertVisible="False"
                                SortExpression="PickCount" ReadOnly="True" />
                            <asp:CommandField ButtonType="Button" EditText="Edit" UpdateText="Update" CancelText="Cancel"
                                HeaderText="Edit Row" ShowEditButton="true" />
                            <asp:ButtonField CommandName="NewOrange" ButtonType="Button" Text="Create" HeaderText="New Orange" />
                        </Columns>
                    </asp:GridView>
            </ContentTemplate>
        </asp:UpdatePanel>

The Events and functions attached to the control on the code behind side:

 private void LoadData(string sort, bool asc)
        {
            DAL.AC_DataClassesDataContext dc = new DAL.AC_DataClassesDataContext();
            var data = (from Trees in dc.Trees
                        join Oranges in dc.Oranges on new { TreeID = Trees.TreeID } equals new { TreeID = Convert.ToInt32(Oranges.TreeID) } into Oranges_join
                        from Oranges in Oranges_join.DefaultIfEmpty()
                        group new { Oranges, Trees } by new
                        {
                            Trees.TreeID,
                            Trees.ActiveStatus,
                            Trees.Name,
                            Trees.Description,
                            Trees.CountryID,
                            Trees.LastUpdated
                        } into g
                        select new
                        {
                            TreeID = (System.Int32?)g.Key.TreeID,
                            ExpirationDate = (System.DateTime?)g.Max(p => p.Oranges.PickDate),
                            PickCount = g.Count(),
                            ActiveStatus = (System.Byte?)g.Key.ActiveStatus,
                            g.Key.Name,
                            g.Key.Description,
                            g.Key.CountryID,
                            LastUpdated = (System.DateTime?)g.Key.LastUpdated
                        });

            data = data.OrderByField(sort, asc); //OrderByField uses dynamic linq library which is a free additional open source download from MS

                try
                {
                    data = data.Where(g => g.TreeID == Convert.ToInt32(ddlTreeIDs.SelectedValue));
                }
                catch (Exception ex)
                {
                    lblError.Text = ex.ToString();
                    utils.ShowJQueryModal(this, ex); //static custom class I created to show dialog containing lblerror text
                }

            gvDataList.DataSource = data;
            gvDataList.DataBind();
            upnlContent.Update();
        }

        protected void gvDataList_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "NewOrange")
            {
                string redirecturl = "~/Pages/NewOrange.aspx?TreeID={0}"; //this page should be pretty self explanatory
                string TreeID = (((GridView)sender).Rows[Convert.ToInt32(e.CommandArgument)].Cells[0]).Text;
                redirecturl = String.Format(redirecturl, TreeID);
                Response.Redirect(redirecturl);
            }
        }

		//example of a mechanism that can be used to change style or visibility of controls specific to user access
        public bool GetEditPermission(string permtype)
        {
            switch (permtype.ToLower())
            {
                case "other":
                    if (utils.usergroup == "admin")
                    {
                        return true;
                    }
                    else return false;
                case "allowregion":
                    if (utils.usergroup == "admin")
                    {
                        return true;
                    }
                    else return false;
                default:
                    return true;
            }
        }

        protected void gvDataList_UpdateRecord(object sender, GridViewUpdateEventArgs e)
        {
            DAL.AC_DataClassesDataContext dc = new DAL.AC_DataClassesDataContext();

            int TreeID, activestatus, CountryID;
            string Name, Description;

            try
            {
                TreeID = Convert.ToInt32(e.Keys[0]); //should be 0
                activestatus = Convert.ToInt32(((CheckBox)gvDataList.Rows[e.RowIndex].FindControl("chkActiveStatus")).Checked);
                Name = Convert.ToString(((TextBox)gvDataList.Rows[e.RowIndex].FindControl("tbName")).Text);
                Description = Convert.ToString(((TextBox)gvDataList.Rows[e.RowIndex].FindControl("tbDescription")).Text);
                CountryID = Convert.ToInt32(((DropDownList)gvDataList.Rows[e.RowIndex].FindControl("ddlCountryID")).SelectedValue);
            }
            catch (Exception ex)
            {
                //throw new Exception("Error retrieving grid values for update.");
                lblError.Text = "Error retrieving grid values for update. Details: " + ex.ToString();
                utils.ShowJQueryModal(this);
                upnlContent.Update();
                return; //unassigned local variables if this is skipped or exception now thrown
            }

            int result = dc.ExecuteCommand("update Trees set ActiveStatus={1}," +
           "Name={2},Description={3}," +
           "CountryID={4} where TreeID={0}",
                      TreeID, activestatus, Name, Description,
                      CountryID);

            if (result == 1)
            {
            }
            else
            {
                lblError.Text = "Record failed to update.";
                utils.ShowJQueryModal(this); //static custom class I created to show modal dialog containing lblerror text
            }
            gvDataList.EditIndex = -1;
            LoadData();
        }

        protected void gvDataList_EditRecord(object sender, GridViewEditEventArgs e)
        {
            gvDataList.EditIndex = e.NewEditIndex;
            LoadData();
        }

        protected void gvDataList_CancelRecord(object sender, GridViewCancelEditEventArgs e)
        {
            gvDataList.EditIndex = -1;
            LoadData();
        }

        private string ConvertSortDirectionToSql(SortDirection sortDirection)
        {
            string newSortDirection = String.Empty;

            switch (sortDirection)
            {
                case SortDirection.Ascending:
                    newSortDirection = "ASC";
                    break;

                case SortDirection.Descending:
                    newSortDirection = "DESC";
                    break;
            }

            return newSortDirection;
        }

        protected void gvDataList_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvDataList.PageIndex = e.NewPageIndex;
            gvDataList.DataBind();
        }

        protected void gvDataList_Sorting(object sender, GridViewSortEventArgs e)
        {

            bool asc = true; //default to true per usual .net behavior
            if (ViewState[e.SortExpression] != null)
            {
                asc = (!(bool)ViewState[e.SortExpression]);
                ViewState[e.SortExpression] = asc;
            }
            else
            {
                ViewState[e.SortExpression] = true;
            }

            LoadData(e.SortExpression, asc);
        }

        //Show modal dialog using jquery. Requires javascript reference/link to jquery in the page.
        public static void ShowJQueryModal(Page PageInstance, Exception ex)
        {
            ClientScriptManager ClientScript = PageInstance.ClientScript;
            AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(PageInstance, PageInstance.GetType(), "preparemodal",
                "<script type=\"text/javascript\">$('#modal').dialog({autoOpen: false, modal: true });</script>", false);
            AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(PageInstance, PageInstance.GetType(), "loadmodal",
                "<script type=\"text/javascript\">$('#modal').dialog('open');</script>", false);
        }

Links and references to Dynamic LINQ may change over time (no pun intended).

At the time of writing this they can be found on MS and Scott Gu’s blog. If you cannot find it, send me an email and I can send it to you or you can refer to my post on OrderBy Column Name for the exact extension method.

Advertisements

Intro to git

git is essentially decentralized, which seems to be the direction everything is going, so I decided to take a shot at it.

I’ve used cvs, VSS, TFS, SVN and now git and I’ve got to say it is definitely pretty cool (and free).

The main difference I have noticed between git and these other guys is git allows you to make commits to your local machine and push them later. This means faster commits, more commits and therefore more versioning and finer details on revisions, and no need to worry about potential connectivity issues.

Behind the scenes git is also much cleaner on everything from its transmission protocols to its storage mechanism, so overall it is a more evolved product.

I haven’t fully explored all of its features yet, so I’ll keep revisiting this article to keep it updated from time to time.

First of all, you will have to decide upon a remote repository. You could setup your own, but that is currently out of scope of this article. In this scenario I chose projectlocker.

I left all projectlocker settings as default and using ssh as the communication protocol. http(s) is available as well, but ssh rocks :).

If you’re on Windows, download and install Cygwin and make sure you select OpenSSH. Cygwin will give you a nice little linux shell for us to do our work from. If you were hoping for a GUI, try this route first, you might be surprised at the simplicity of the command line.

Open cygwin to create a public key to authenticate to the remote server. Enter ssh-keygen and press enter. Keep pressing enter on all prompts and skip the password.

cd to the ~/.ssh folder where it likely created your key enter cat id_rsa.pub. This will dump the contents of your public key file. Right click on the command prompt title bar of the window and select mark then grab your dumped file and paste in your remote repo service. Make sure the username has no spaces and matches your pc name and is also listed in the file dump.

Save changes in remote service then go back to cygwin. To test you did the above step correctly do “ssh -v [your git without specifying port of \reponame.git]“. It shouldn’t connect fully, but should give you enough status to verify it’s working. If it prompts for password then it was unable to verify public key which the -v output should indicate.

Once the above is good, identify yourself with:
git config –global user.name “yourname”
git config –global user.email “youremail@yourdomain.com”

Then download the remote repository (which may or may not be empty, that’s ok).
git clone [yourauthpart@yourdomainpart:yourreponame.git]

To add in a project I already had in my local, I did the above steps first within C:\git then copied my project folder in C:\git\projectname. cd into git then use this command to add any files within:
git add [filename or wildcard * for all]
git commit -m “my first commit!”

Then to push up to repo at any point simply use:
git push origin master

And that’s it!

(Optional: If you get an error on push origin or skipped clone then first do):
git init
git remote add origin [yourauthpart@yourdomainpart:yourreponame.git]
git pull origin master

So to sum up the steps we have:
1) setup repo on remote service
2) install cygwin and openssh (skip this step on *nix)
3) ssh-keygen (enter on all prompts)
4) cat public key and enter in remote repo service
5) test key setup is correct using ssh
6) identify your user and email to git
7) clone the repo and begin adding, committing and pushing!

Rock and roll!

References
cforcoding.com (blog), http://www.cforcoding.com/2009/09/windows-git-tutorial-cygwin-ssh-and.html
git-svn crash course, http://git.or.cz/course/svn.html
bahrenbugs (blog), http://blog.bahrenburgs.com/2010/01/using-git-with-projectlocker-on-mac.html
projectlocker, http://projectlocker.com/
cygwin, http://www.cygwin.com/

C# ListBox Management Adding Removing Updating Databound Items ASP .Net

The lambda one-liner indicated in the snippet below essentially nests at least two “for/foreach” loops. See my related article for full source.

This code snippet is used to populate a drop down list of all factories containing unregistered robots where are not already marked in the list to be decommissioned.

Upon selecting a factory containing unregistered robots, another listbox is populated with the robots so they can be selected from which moves them into the decommissioned list.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;

public partial class DecommissionRobots : System.Web.UI.UserControl
{
public event EventHandler SendToDecommission;

    public void Update()
    {
        upnlContainer.Update();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadRegions();
            txtExpDate_CalendarExtender.SelectedDate = DateTime.UtcNow.AddMonths(6);
        }
    }

    protected void LoadRegions()
    {
        using (AndroneticsDataContext db = new TigrentLearningDataContext(
        ConfigurationManager.ConnectionStrings["AndroneticsConnection1"].ConnectionString)) {
        ddlRegions.DataSource = db.UP_GET_REGIONS();
        ddlRegions.DataTextField = "REGION";
        ddlRegions.DataValueField = "REGION_ID";
        ddlRegions.DataBind();

        ListItem li = new ListItem("", "");
        ddlRegions.Items.Insert(0, li);
}
    }

    protected void ClearLists()
    {
        lstUnselectedRobots.Items.Clear();
//you may want to clear other lists in this manner in your app as well
    }

protected void LoadFactories()
    {
        ClearLists();

        if (ddlSegment.SelectedIndex > 0)
        {
            using (AndroneticsDataContext db = new AndroneticsDataContext(
        ConfigurationManager.ConnectionStrings["AndroneticsConnection1"].ConnectionString))
            {
                List<UP_GET_FactoriesResult> allFactories = db.UP_GET_Factories(Convert.ToInt32(ddlRegion.SelectedValue), null).ToList();

                ////this would be approximately how you would approach traditionally
                //foreach (UP_GET_FactoriesResult r in allFactories)
                //{
                //    if (GetUnregisteredRobots(r.FACTORY_ID).Count != 0)
                //    {
                //        //etc, would need another loop here
                //    }
                //}

                //now for the lambda way
                var Factories = allFactories.Where(
                    r => GetUnregisteredRobots(r.FACTORY_ID).Where(
                        ra => !lstDecommissionRobots.Items.Contains(new ListItem(ra.ROBOT_NAME,ra.ROBOT_ID.ToString()))).Count() != 0);

                ddlFactories.DataSource = Factories;
                ddlFactories.DataTextField = "FACOTRY_TITLE";
                ddlFactories.DataValueField = "FACTORY_ID";
                ddlFactories.DataBind();

                ListItem li = new ListItem("", "");
                ddlFactories.Items.Insert(0, li);
            }
        }
    }

    private List<UP_LOAD_COURSE_RobotsResult> GetUnregisteredRobots(int courseid)
    {
        using (AndroneticsDataContext db = new AndroneticsDataContext(
        ConfigurationManager.ConnectionStrings["AndroneticsConnection1"].ConnectionString))
        {
            List<UP_LOAD_COURSE_RobotsResult> allRobots = db.UP_LOAD_COURSE_Robots(courseid).ToList();

            int userID = int.Parse(Request.QueryString["ID"]);
            List<string> excregisteredRobots = (from m in db.UP_GET_REGISTERED_Robots(userID) select m.MODULE_NAME).ToList();

            return allRobots.Where(m => !excregisteredRobots.Contains(m.MODULE_NAME)).ToList();
        }
    }

protected void LoadUnregisteredRobots()
    {
        lstUnselectedRobots.Items.Clear();

        //we need to make sure a re=d-g.and FACTORY were selected.
        if (ddlFactories.SelectedIndex > 0 && ddlRegion.SelectedIndex > 0)
        {
//notice this reuses the same code as above any simple retrieves the results for value selected in the dropdown. this value is updated by autopostback true set on the aspx page side.
            lstUnselectedRobots.DataSource = GetUnregisteredRobots(Convert.ToInt32(ddlFactories.SelectedValue));
            lstUnselectedRobots.DataTextField = "Robot_NAME";
            lstUnselectedRobots.DataValueField = "Robot_ID";

            lstUnselectedRobots.DataBind();
        }
    }

protected void btnSelect_Click(object sender, ImageClickEventArgs e)
    {
        List<ListItem> selectedItems = new List<ListItem>();

        //add all the selected items to a list
        foreach (ListItem li in lstSelectItems.Items)
        {
            if (li.Selected)
            {
                selectedItems.Add(li);
            }
        }

        //add the selected items to the selected box
        lstSelectedItems.Items.AddRange(selectedItems.ToArray());

        //remove the selected items from the original list
        foreach (ListItem items in selectedItems)
        {
            lstUnselectedRobots.Items.Remove(items);
        }

        LoadFactories();
    }

    protected void btnRemove_Click(object sender, ImageClickEventArgs e)
    {
        List<ListItem> selectedItems = new List<ListItem>();

        //add all the selected items to a list
        foreach (ListItem li in lstSelectedItems.Items)
        {
            if (li.Selected)
            {
                lstDecommissionRobots.Add(li);
            }
        }

        //add the selected items back to the initial selection box
        lstUnselectedRobots.Items.AddRange(selectedItems.ToArray());

        //remove the selected items from the selected list
        foreach (ListItem items in selectedItems)
        {
            lstDecommissionRobots.Items.Remove(items);
        }

        LoadFactories();
    }

    protected void btnSend_Click(object sender, ImageClickEventArgs e)
    {
        List<USER_ACCESS> FactoryItems = new List<USER_ACCESS>();
        int userid= int.Parse(Session["userid"].ToString());

        try
        {
            if (lstDecommissionRobots.Items.Count > 0)
            {
                //for each Factory item that's selected, add it to the Factory list
                foreach (ListItem li in lstDecommissionRobots.Items)
                {
                    USER_ACCESS regFactory = new USER_ACCESS()
                    {
                        USER_ID = studentID,
                        Robot_ID = Convert.ToInt32(li.Value),
                        EXPIRATION_DATE = Convert.ToDateTime(txtExpDate.Text),
                        CREATED_BY = Users.CurrentUser.UserID.ToString()
                    };

                    FactoryItems.Add(regFactory);
                }

                //Make sure the list got filled and then insert the Factories into the db.
                if (FactoryItems.Count > 0)
                {
                    AndroneticsDAL dalInstance = new AndroneticsDAL();
                    dalInstance.EnrollStudent(FactoryItems);

                    //TODO: replace with update event handle
                    //PopulateFactories(userid);
                    ClearLists();
                    lstDecommissionRobots.Items.Clear();
                    ddlSegment.ClearSelection();
                    ddlFactories.ClearSelection();

                    if (SendToDecommission != null)
                    {
                        SendToDecommission(sender,e);
                    }
                }
            }
            else
            {
                //PopulateFactories(userid);
            }
        }
        catch (Exception ex)
        {
//Utility.HandleError(ex); //send to email, handle, etc or handle in global.asax
        }
    }

    protected void ddlRegion_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadFactories();
        ddlFactories.SelectedItem.Selected = false;
    }

    protected void ddlFactories_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadRobots();
    }
}

HTML/ASPX Side:

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="DecommissionRobots.ascx.cs" Inherits="DecommissionRobots" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<asp:UpdatePanel ID="upnlContent" runat="server" ChildrenAsTriggers="false"
            UpdateMode="Conditional">
            <ContentTemplate>
                <asp:Panel runat="server" ID="pnlFactorieselection" GroupingText="Decommission Robots">
                    <div class="panelPadding paddingTop10 tableSize">
                        <div>
                            <div class="displayInlineLeftFloat labelSize">
                                <asp:Label runat="server" ID="lblRegion" AssociatedControlID="ddlRegion" Text="Region Name:"
                                    CssClass="labelSize"></asp:Label></div>
                            <div class="displayInlineLeftFloat FactoryDropDowns">
                                <asp:DropDownList runat="server" ID="ddlRegion" AutoPostBack="true" OnSelectedIndexChanged="ddlRegion_SelectedIndexChanged">
                                    <asp:ListItem Text="United States" Value="UnitedStats"></asp:ListItem>
                                    <asp:ListItem Text="United Kingdom" Value="UnitedKingdom"></asp:ListItem>
                                </asp:DropDownList>
                            </div>
                        </div>
                        <div class="clearFloat paddingTop10">
                            <div class="displayInlineLeftFloat labelSize">
                                <asp:Label runat="server" ID="lblFactories" AssociatedControlID="ddlFactories" Text="Factory Name:"
                                    CssClass="labelSize"></asp:Label></div>
                            <div class="displayInlineLeftFloat FactoryDropDowns">
                                <asp:DropDownList runat="server" ID="ddlFactories" AutoPostBack="true" OnSelectedIndexChanged="ddlFactories_SelectedIndexChanged">
                                </asp:DropDownList>
                            </div>
                        </div>
                        <div class="clearFloat paddingTop10">
                            <div class="displayInlineLeftFloat labelSize">
                                <asp:Label runat="server" ID="lblExpDate" AssociatedControlID="txtExpDate" Text="Expiration Date:"
                                    CssClass="labelSize"></asp:Label></div>
                            <div class="displayInlineLeftFloat">
                                <asp:TextBox runat="server" ID="txtExpDate"></asp:TextBox>
                                <asp:CalendarExtender ID="txtExpDate_CalendarExtender" runat="server" DefaultView="Months"
                                    Enabled="True" TargetControlID="txtExpDate">
                                </asp:CalendarExtender>
                            </div>
                        </div>
                        <div class="clearFloat">
                            <br />
                        </div>
                        <div>
                            <div>
                                <span class="listWidth displayInline CenterElement">Unregistered Robots</span><span
                                    id="selectedCaption" class="listWidth displayInline CenterElement">Selected
                                    Robots</span></div>
                            <div id="RegSection" class="displayInlineLeftFloat">
                                <asp:ListBox ID="lstUnregisteredRobots" runat="server" CssClass="FloatLeft listWidth" Rows="10"
                                    SelectionMode="Multiple"></asp:ListBox>
                                <div id="selectButtons" class="floatContainer">
                                    <div>
                                        <asp:ImageButton runat="server" ID="btnSelect" AlternateText="select" OnClick="btnSelect_Click"
                                            ImageUrl="~/Images/special_right.gif" /></div>
                                    <div class="buttonTopMargin5">
                                        <asp:ImageButton runat="server" ID="btnRemove" AlternateText="remove" ImageUrl="~/Images/special_left.gif"
                                            OnClick="btnRemove_Click" /></div>
                                </div>
                                <asp:ListBox runat="server" ID="lstDecommissionRobots" Rows="10" SelectionMode="Multiple"
                                    CssClass="listWidth"></asp:ListBox>
                            </div>
                            <div id="sendButton">
                                <asp:ImageButton runat="server" ID="btnSend" AlternateText="Enroll" ImageUrl="~/Images/btn_send.gif"
                                    OnClick="btnSend_Click" /></div>
                        </div>
                    </div>
                </asp:Panel>
            </ContentTemplate>
            <Triggers>
                <asp:AsyncPostBackTrigger ControlID="ddlRegion" EventName="SelectedIndexChanged" />
                <asp:AsyncPostBackTrigger ControlID="ddlFactories" EventName="SelectedIndexChanged" />
                <asp:AsyncPostBackTrigger ControlID="btnSelect" EventName="Click" />
                <asp:AsyncPostBackTrigger ControlID="btnRemove" EventName="Click" />
                <asp:AsyncPostBackTrigger ControlID="btnSend" EventName="Click" />
            </Triggers>
        </asp:UpdatePanel>

References
Fraction Of The Blogosphere, https://ronniediaz.com/2010/12/21/lambda_functions_in_-net/

Copy File With Update Progress in .Net

The process of copying a file in .Net is of course very simple and straightforward.

//in a real world solution, use Environment.SpecialFolders instead of hard coding c drive letter
string inputpath = "C:\\in\\";
string outpath = "C:\\out\\";
string filename = "foo.txt";
System.IO.File.Copy(inputpath + filename, outputpath + filename, true);
//file copied!.. but if foo was very large, this block of code following would be delayed execution until the copy completes..

If you want to copy multiple files and update the end user on the progress of a large file copy, or copy files asynchronously while performing other tasks in your application, you’re getting into slightly hairy territory.

There are many ways to achieve this with varying results, and you will often see many application installers and various other large file copy processes inaccurately estimate remaining time and incorrectly report progress, or lock up close to the end.

To correct this, my advice would be to go back to basics – copying bytes using FileStream. See example below.

(applies to winform since update progress is displayed to user, but can easily be adapted to console or web)

using System.IO;

 public class ThreadedFormFileCopy : Form
    {

        // Class to report exception {
        private class UIError
        {
            public UIError(Exception ex, string path_)
            {
                msg = ex.Message; path = path_; result = DialogResult.Cancel;
            }
            public string msg;
            public string path;
            public DialogResult result;
        }

        #region "winforms specific"
        public ThreadedFormFileCopy()
        {
            InitializeComponent();
        }

        private void btnCopy_Click(object sender, EventArgs e)
        {
            StartCopy();
        }
        #endregion

        #region "variable declarations"
        FileInfo currentfile { get; set; } //keep track of current file being copied
        long maxbytes { get; set; } //shared with file agent, background worker and form thread to keep track of bytes

        private System.Timers.Timer fileagent; //we'll be using this thread to update the progress bar as bytes are copied

        //would also declare OnChange delegate and progresschange object here as well if byte level progress not needed and just want to update UI for each file copied
        private BackgroundWorker mCopier;
        private delegate void CopyError(UIError err);
        private CopyError OnError;
        #endregion


        public void StartCopy()
        {
            LoadFileAgent(); //instantiate and update file agent parameters such as internal and elapsed handler
            PrepareBackgroundWorker(); //instantiate and update background worker parameters and handlers
            UpdateFormLabels(false); //change labels on form to indicate copy process has begun
            ToggleBackgroundWorker(); //start background worker, can be used with a button or UI element to cancel as well
        }

        private void LoadFileAgent()
        {
            fileagent = new System.Timers.Timer();
            fileagent.Interval = 100; //1ss
            fileagent.Elapsed += new System.Timers.ElapsedEventHandler(FileAgent_Process);
        }

        private void FileAgent_Process(object source, System.Timers.ElapsedEventArgs e)
        {
            try
            {
                label1.Text = currentfile.Name;
                progressBar1.Value = (int)(100 * maxbytes / currentfile.Length);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString()); //may want to include in debug only
            }
        }

        private void Copier_DoWork(object sender, DoWorkEventArgs e)
        {
            string[] allowextensions = { ".wmv", ".flv", ".f4v", ".mov", ".mp4", ".mpeg", ".mpg", ".mp3", ".wma" }; //etc, videos can get big and might want progress..
            List<FileInfo> files = new List<FileInfo>();

            //don't forget your Environment.SpecialFolder
            string path = "\\path_containing_files\\";
            DirectoryInfo dir = new DirectoryInfo(path);
            foreach (string ext in theExtensions)
            {
                FileInfo[] folder = dir.GetFiles(ext, SearchOption.AllDirectories);
                foreach (FileInfo file in folder)
                {
                    if ((file.Attributes & FileAttributes.Directory) != 0) continue;
                    files.Add(file);
                    maxbytes += file.Length;
                }
            }

            //and now for the good stuff
            int bytesread = 0;
            foreach (FileInfo file in files)
            {
                try
                {
                    currentfile = file;
                    fileagent.Start();

                    string outputpath =  "\\destination_path\\";

                    FileInfo destfile = new FileInfo(outputpath + file.Name);

                    byte[] buffer = new byte[4096]; //4MB buffer
                    FileStream fsread = file.Open(FileMode.Open, FileAccess.Read);
                    FileStream fswrite = destfile.Open(FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    maxbytes = 0;

                    while ((bytesread = fsread.Read(buffer, 0, buffer.Length)) > 0)
                    {
                        fswrite.Write(buffer, 0, bytesread);
                        maxbytes = maxbytes + bytesread;
                    }
                    fsread.Flush();
                    fswrite.Flush();
                    fsread.Close();
                    fswrite.Close();
                    fsread.Dispose();
                    fswrite.Dispose();
                    //-------------------
                    System.IO.File.SetAttributes(outputpath + file.Name, FileAttributes.Normal);
                    fileagent.Stop();

                }
                catch (Exception ex)
                {
                    UIError err = new UIError(ex, file.FullName);
                    this.Invoke(OnError, new object[] { err });
                    if (err.result == DialogResult.Cancel) break;
                }
                //could update bytes here also
            }

        }

        private void PrepareBackgroundWorker()
        {
            mCopier = new BackgroundWorker();
            mCopier.DoWork += Copier_DoWork; //all the heavy lifting is done here
            mCopier.RunWorkerCompleted += Copier_RunWorkerCompleted;
            mCopier.WorkerSupportsCancellation = true;
            //if you wanted to add an onchange event for the background worker, you could do this here and it would fire after each complete copy, though not necessary this is viable alternative for medium file sizes
            OnError += Copier_Error;
        }

        private void UpdateFormLabels(bool copying)
        {
            label1.Visible = copying;
            progressBar1.Visible = copying;
            label1.Text = "Starting copy...";
            progressBar1.Value = 0;
        }

        private void ToggleBackgroundWorker()
        {
            bool copying = true;
            UpdateFormLabels(copying);
            if (copying)
            {
                mCopier.RunWorkerAsync();
            }
            else
            {
                mCopier.CancelAsync();
            }
        }

        private void Copier_Error(UIError err)
        {
            string msg = string.Format("Error copying file. Details: " + err.Message);
            err.result = MessageBox.Show(msg, "Copy error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            Environment.ExitCode = 1;
            Application.Exit(); //log error, do something, close, or continue if it's not critical or used for long unattended copies
        }

        private void Copier_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            label1.Text = "Complete!";
            progressBar1.Value = 100;
            this.Invoke(new MethodInvoker(delegate { FinishedCopying(); }));
            this.Close();
        }

        private void FinishedCopying()
        {
            //execute something when worker finished
        }

    }

References
StackOverflow, “Read/Write bytes in Chunks”, http://stackoverflow.com/questions/5654298/filestream-read-write-methods-limitation
Java2s, “Write Bytes using Filestream”, http://www.java2s.com/Tutorial/VB/0240__Stream-File/WriteBytesusingFileStream.htm
MSDN, “FileStream.WriteByte”,
MSDN, “FileStream.ReadByte”,
http://msdn.microsoft.com/en-us/library/system.io.filestream.readbyte.aspx
extremevbtalk.com, “Read Binary Files into a Buffer”, http://www.xtremevbtalk.com/showthread.php?t=259085
Java2s, “Read into a Buffer”, http://www.java2s.com/Tutorial/VB/0240__Stream-File/Readintoabuffer.htm
StackOverflow, http://stackoverflow.com/questions/1261570/c-filestream-read-doesnt-read-the-file-up-to-the-end-but-returns-0
StackOverflow, “Asynchronous stream read/write”, http://stackoverflow.com/questions/1540658/net-asynchronous-stream-read-write
xtremevbtalk.com, “Background Worker Progress Changed and Progress Bar”, http://www.xtremevbtalk.com/showthread.php?t=294040
StackOverflow, “Copy Stream to byte array”, http://stackoverflow.com/questions/950513/how-to-copy-one-stream-to-a-byte-array-with-the-smallest-c-code

MSSQL Quick Reference

Snippets below have been condensed from their original sources for brevity. See references for original articles.

Great reference on joins: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
Visual_SQL_JOINS_V2

Show all tables in specific database: (MSSQL equivalent of MySql “show tables”)

select name from <database name>..sysobjects where xtype = 'U'

Insert into:

INSERT INTO MusicArtists (FirstName, LastName, Instrument)
VALUES ('Bobby', 'Lee', 'fiddle');

INSERT INTO Duos (Member1) 
    SELECT FirstName + ' ' + LastName FROM MusicArtists;

INSERT INTO Duos (Member1) 
    SELECT FirstName + ' ' + LastName FROM MusicArtists 
    WHERE MusicianID > 3;

INSERT INTO Residents (Name, Occupation)
    SELECT Name, Occupation FROM Immigration 
    WHERE Residency = 'granted';

INSERT INTO Insurance (Name) 
    SELECT Employee.Username FROM Employee 
    INNER JOIN Project ON Employee.EmployeeID = Project.EmployeeID 
    WHERE Project.ProjectName = 'Hardwork';

Insert if not exists:

IF NOT EXISTS (SELECT * FROM dbo.Applications WHERE Username = Tom ANDApplication = Calculator) BEGIN
 INSERT INTO dbo.Applications 
 (Date, Username, Application, Version) 
 VALUES
 ('3/10/2009', 'Tom', 'Calculator', '2.0') 
END

Trigger Syntax Structure:

CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS 
[ { IF UPDATE ( column ) 
[ { AND | OR } UPDATE ( column ) ] 
[ ...n ] 
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
{ comparison_operator } column_bitmask [ ...n ] 
} ] 
sql_statement [ ...n ] 
} 
} 

Basic If, Then , Else and case:

--longer
create proc sp_generic (@cust int, @type int)
if @type = 1
 select * from customer where customerid = @cust
else
 Select * from deleted_customers where customerid = @cust

--shorter
create proc sp_generic (@deptid int )
select * from employees where departmentid = case when @dept >0 then @dept else departmentid end
order by departmentid

Insert Trigger:

CREATE TRIGGER trig_addAuthor 

ON authors 

FOR INSERT 

AS

-- Get the first and last name of new author 

DECLARE @newName VARCHAR(100) 

SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

-- Print the name of the new author 

PRINT 'New author "' + @newName + '" added.'

Update Trigger:

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.objects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER Person.reminder;
GO
CREATE TRIGGER reminder
ON Person.Address
AFTER UPDATE 
AS 
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;
GO
-- Test the trigger.
UPDATE Person.Address
SET PostalCode = 99999
WHERE PostalCode = '12345';
GO

Get and/or compare today’s date:

convert(varchar,CURRENT_TIMESTAMP,101)
convert(varchar(12),getdate(),101)

Insert or Update with case statements:

USE [mydatabasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_update_manufacturedrobots_count]
	@mfgid int,
	@robotid int,
	@paintbotcount int=null,
	@drillbotcount int=null,
	@hammerbotcount int=null
AS
BEGIN

	SET NOCOUNT ON;

declare @id int
select @id = id from manufacturedrobots where mfgid=@mfgid and robotid=@robotid
if @id is not null
update manufacturedrobots set 
paintbotcount=case
when @paintbotcount is null then paintbotcount 
else @paintbotcount end, 
favorite=case
when @drillbotcount is null then drillbotcount 
else @drillbotcount end, 
checkedin=case
when @hammerbotcount is null then hammerbotcount 
else @hammerbotcount end
where id=@id
else
insert into manufacturedrobots (paintbotcount,drillbotcount,hammerbotcount) values  (@paintbotcount,@drillbotcount,@hammerbotcount)

END

Cursor:

USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
SELECT ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID
DEALLOCATE @getProductID
GO

References:
MSDN, http://msdn.microsoft.com/en-us/library/ms187326.aspx
DevGuru, http://www.devguru.com/technologies/t-sql/7124.asp
DevArticleshttp://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/1/
MSDN, “How to debug stored procedures in Visual Studio .NET”, http://support.microsoft.com/kb/316549
CodeGuru, http://www.codeguru.com/forum/showthread.php?t=473102
DaniWeb, http://www.daniweb.com/forums/thread43719.html
SqlAuthority, http://blog.sqlauthority.com/2008/03/05/sql-server-simple-example-of-cursor-sample-cursor-part-2/