Blog Archives

Query Sql Version Microsoft MSSQL

    set nocount on
    go
    select @@version
    go
    select 'Edition: ' + convert(char(30), serverproperty('Edition'))
    go  
    select 'Product Version: ' + convert(char(20), serverproperty('ProductVersion'))
    go
    select 'Product Level: ' + convert(char(20),serverproperty('ProductLevel'))
    go
    set nocount off
    go

References
https://support.microsoft.com/kb/321185

http://blogs.msdn.com/b/euanga/archive/2008/01/23/confused-by-sql-server-version-numbers.aspx

Custom DAL Class SQL ORM ASP .NET

(common.DataObject may be of your choosing or may simply replace with dynamic)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Data.SqlClient;

using AIS.Common; //this is a common namespace I use in examples throughout my site
using System.Reflection;
using System.Dynamic;

//TODO: consider returning ienumerable in sp return values for lazy eval vs .tolist immediate eval
namespace AIS.DAL.AppName
{
    public static class StoredProcedures
    {
        public delegate void ErrorHandler(Exception ex);
        /// <summary>
        /// If no custom error handling is bound to this event, exceptions will be thrown back up to the calling function.
        /// If custom handling is bound to this event, ensure it does not perform a redirect or kill the thread unless you intend to abort the procedural
        /// steps following the method/function call which threw the error.
        /// </summary>
        public static event ErrorHandler HandleError;

        #region Unique Procedures
        public static List<Common.DataObject> LoadUserSessions_All(dynamic o)
        {
            return ExecuteRead("an_get_db_fn1", o);
        }

        public static List<Common.DataObject> LoadUserSessionsDetails_LiveStream(dynamic o)
        {
            return ExecuteRead("an_get_db_fn2", o);
        }

        public static List<Common.DataObject> LoadUserSessionsDetails_Live(dynamic o)
        {
            return ExecuteRead("an_get_db_fn3", o);
        }

        public static int LogChat()
        {
            return ExecuteScalar("an_get_db_fn4", null);
        }

        public static int LogError()
        {
            return ExecuteScalar("an_get_db_fn5", null);
        }
        #endregion

        //TODO: consider hiding from external assemblies which would require strong mappings above
        #region Execution Logic
        public static List<Common.DataObject> ExecuteRead(string procedurename, dynamic param)
        {
            try
            {
                SqlDataSource sds = new SqlDataSource();
                sds.ConnectionString = ConfigValues.TrainingPortalConnectionString;
                sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
                sds.SelectCommand = procedurename;

                if (param != null)
                {
                    foreach (PropertyInfo pi in param.GetType().GetProperties())
                    {
                        object pval = pi.GetValue(param, null);
                        if (pval != null)
                        {
                            sds.SelectParameters.Add(pi.Name, pval.ToString());
                        }
                    }
                }

                List<Common.DataObject> results = new List<Common.DataObject>();
                //sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<dynamic>(o, ref results));
                sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<Common.DataObject>(o, ref results));

                return results;
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
                return null;
            }
        }

        public static void Load_AddResult<t>(Common.DataObject o, ref List<t> results)
        {
            try
            {
                t r = (t)Activator.CreateInstance(typeof(t));

                foreach (PropertyInfo pi in typeof(t).GetProperties())
                {
                    object v = o[pi.Name].ToString();
                    Type pt = Type.GetType(pi.PropertyType.FullName);
                    //try { pi.SetValue(r, Convert.ChangeType(v, pt), null); }
                    //catch (Exception ex) { HandleError_Condensed(ex); }

                    o.Add(pi.Name, Convert.ChangeType(v, pt));
                }

                results.Add(r);
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
            }
        }

        //public static void Load_AddResult<t>(dynamic o, ref List<t> results)
        //{
        //    try
        //    {
        //        t r = (t)Activator.CreateInstance(typeof(t));

        //        foreach (PropertyInfo pi in typeof(t).GetProperties())
        //        {
        //            object v = o[pi.Name].ToString();
        //            Type pt = Type.GetType(pi.PropertyType.FullName);
        //            try { pi.SetValue(r, Convert.ChangeType(v, pt), null); }
        //            catch (Exception ex) { HandleError_Condensed(ex); }
        //        }

        //        results.Add(r);
        //    }
        //    catch (Exception ex)
        //    {
        //        HandleError_Condensed(ex);
        //    }
        //}

        public static void ExecuteNonScalar(string procedurename, dynamic param)
        {
            try
            {
                ExecuteScalar(procedurename, param);
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
            }
        }

        public static int ExecuteScalar(string procedurename, dynamic param)
        {
            try
            {
                SqlDataSource sds = new SqlDataSource();
                sds.ConnectionString = ConfigValues.TrainingPortalConnectionString;
                sds.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
                sds.UpdateCommand = procedurename;

                if (param != null)
                {
                    foreach (PropertyInfo pi in param.GetType().GetProperties())
                    {
                        object pval = pi.GetValue(param, null);
                        if (pval != null)
                        {
                            sds.SelectParameters.Add(pi.Name, pval.ToString());
                        }
                    }
                }

                return sds.Update();
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
                return 1; //1 signifies error in tsql
            }
        }
        #endregion

        private static void HandleError_Condensed(Exception ex)
        {
            if (HandleError != null) { HandleError(ex); } else { throw new Exception(ex.Message, ex); } 
        }
    }
}

MSSQL User Defined Functions vs Stored Procedures

I received this question earlier today, and thought it was a valid question often misunderstood, and deserving of a small write-up:
“Should a User Defined Function be your first choice instead of a Stored Procedure?”

While there are many pros and cons of each not covered in this write-up (review your versions on MSDN for details), including some features which may not be apparent until you have an issue to troubleshoot (such as sp_who filtering), you can generally ask yourself a single question up front that can help you determine which you should use.

Simply, if the db functionality you need to implement in the function/procedure requires
any DML (insert/update/delete), then go with a stored procedure. Advanced selects and/or filters are best left up to views/table valued functions.

Additionally, do not be afraid to use a combination of functions and procedures especially if there is a goal of re-usability, in accordance with the design considering the planned growth of your db as your software & db architecture permits. On that note, consider and test the performance differences of these implementations, as a compiled/cached function/procedure containing more logic internally may outperform one utilizing logic that is spread throughout.

References
SP_who filtering UDF vs SP, http://stackoverflow.com/questions/2567141/use-sql-to-filter-the-results-of-a-stored-procedure
Data Manipulation Language, http://en.wikipedia.org/wiki/Data_manipulation_language
(Some) Differences (about.com), http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm
(Some) Differences (stackoverflow), http://stackoverflow.com/questions/2039936/difference-between-stored-procedures-and-user-defined-functions

mssql aes function encrypt decrypt in microsoft sql

Simple symmetric encryption in MSSQL. Copied from MS references below:

USE AdventureWorks2012;
--If there is no master key, create one now. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE Sales09
   WITH SUBJECT = 'Customer Credit Card Numbers';
GO

CREATE SYMMETRIC KEY CreditCards_Key11
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Sales09;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard 
    ADD CardNumber_Encrypted varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;

-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.  
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
    , CreditCardID)));
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.

SELECT CardNumber, CardNumber_Encrypted 
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByKey(CardNumber_Encrypted, 1 , 
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
    AS 'Decrypted card number' FROM Sales.CreditCard;
GO

References:

http://msdn.microsoft.com/en-us/library/bb669072.aspx

http://msdn.microsoft.com/en-us/library/ms179331.aspx

Multiple Stored Procedures with same return type in LINQ

Previously you may have achieved this functionality using datareader. Now that you are using LINQ, you may be wondering how to achieve the same with lists of strongly typed objects.

Quite a few articles out there have reproduced DataReader functionality with LINQ. This article suggests a new approach using the SqlDataSource to create an almost identical code pattern as you previously used with DataReader.

So instead of directly recreating DataReader using LINQ, I’ve replaced it with the same code pattern using a different base class altogether.

old datareader code pattern:
set reader
set connection
set reader parameters
reader loop
get/set reader data
end reader loop

new sqldatasource code pattern:
set sqldatasource
set connection
set sqldatasource parameters
lambda foreach loop calling external delegate function for each item in result

The new approach encapsulates the loop within a LINQ lambda ForEach statement that gets/sets the values and loops through each item inherently. (sourcecode examples below)

Why the hassle?

Sometimes it is useful to load a list of objects from a stored procedure, view or table without immediately binding it to a grid or other data control. The old .NET 2.0 datareader offered a means to easily do this while allowing you the flexibility to do additional work within the reader loop so I sought to reproduce this.

You may be surprised to find the below example does not utilize the LINQ dbml assistance you would normally use in a LINQ to SQL scenario. There is good reason for this as I would like to create a function that allows full control over the list of return values without auto-generating a strange new hybrid return type for every stored procedure.

In addition, I like to fully prototype my applications prior to linking them to the database (that’s how you know you’ve been coding a long time) and this approach makes this much easier.

Simple example:

public class Person {
public int Id {get;set;}
public string Name {get;set;}
public DateTime DateOfBirth {get;set;}
public decimal Age {get;set;} //this value isn't actually stored in db and is calculated on load
}
        public static List<Person> LoadPersonsByFilter(string Filter, char Country)
        {
            SqlDataSource sds = new SqlDataSource();
            sds.ConnectionString = ConfigValues.ConnectionString; //encapsulates ConfigurationManager.ConnectionStrings
            sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
            sds.SelectCommand = "get_persons";
            sds.SelectParameters.Add("Filter", Filter);
            sds.SelectParameters.Add("CountryID", Country.ToString());
            //sds.Selecting += new SqlDataSourceSelectingEventHandler(sds_Selecting);

            List<TrainingEvent> results = new List<TrainingEvent>();
            sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => LoadPersons_AddResult(o, ref results));

            return results;
        }

 private static void LoadPersons_AddResult(dynamic o, ref List<Person> results)
        {
            results.Add(new Person
            {
                Id = Convert.ToInt32(o["PersonID"]),
                Name = Convert.ToString(o["Name"]),
                DateOfBirth = Convert.ToDateTime(o["DateOfBirth"]),
                Age = CalcAge(Convert.ToDateTime(o["DateOfBirth"]))
            });
        }

public decimal CalcAge(DateTime DOB) {
TimeSpan ts = DateTime.Now-DOB;
return Convert.ToDecimal(ts.TotalDays/365);
}

At this point, if you are familiar with Linq, you may be thinking of how the same can be accomplished without much effort by simply returning “new” within your linq query. (PM me if you are unsure what I mean by this).

This is true, you could do this, but as complexity increases, you quickly have to look to other alternatives or your LINQ will increase in complexity and create more room for error.

Consider the following strongly typed example:

public List<Computer> Computers {get;set;}
//the memory module, cpu and harddrive classes should be self explanatory
public class Computer {
public int Id {get;set;}
public List<MemoryModule> MemoryModules  {get;set;}
public List<CPU> CPUs {get;set;}
public List<HardDrive> HardDrives {get;set;}
}

 public static List<Computers> LoadComputersByFilter(string Filter, char Country)
        {
            SqlDataSource sds = new SqlDataSource();
            sds.ConnectionString = ConfigValues.ConnectionString; //encapsulates ConfigurationManager.ConnectionStrings
            sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
            sds.SelectCommand = "get_computers";
            sds.SelectParameters.Add("Filter", Filter);
            sds.SelectParameters.Add("CountryID", Country.ToString());
            //sds.Selecting += new SqlDataSourceSelectingEventHandler(sds_Selecting);

            List<Computers> results = new List<Computers>();
            sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => LoadComputersByFilter_AddResult(o, ref results));

//results might look something like
//compid,mmid,cpuid,hdid
//1,1,1,1 - denotes first stick
//1,2,1,1 - denotes second stick
//1,1,1,2 - denotes second hd
//1,1,2,1 - denotes second cpu
//this is just an example, and would be structured slightly different in a production scenario but the concept remains the same

//you can now bind each sub list to its own nested repeater, etc

            return results;
        }

private static void LoadPersons_AddResult(dynamic o, ref List<Computer> results)
        {
            MemoryModule mm = new MemoryModule()
            {
                //load info and determine specs or other complex results etc
            };

            CPU cpu = new CPU()
            {
                //load info and determine specs or other complex results etc
            };

            HardDrive hd = new HardDrive ()
            {
                //load info and determine specs or other complex results etc
            };

            results.Add(new Computer
            {
                Id=Convert.ToInt32(o["compid"]),
                MemoryModule = mm,
                CPU = cpu,
                HardDrive = hd
            });
        }

As illustrated by the above example, you can easily nest lists within one another without worrying about having to recode a casting mechanism from the custom return type from autogenerated LINQ dbml, or without having to modify the dbml file directly (which resets on updates btw).

The above can be accomplished using LINQ to SQL and lambda expressions, but it will require more practice on your part and is not as explicit IMO.

You also may be wondering why the database call was made by a single stored procedure rather than three separate calls.. If you are unsure, consider the math. Using 1 call to return 4 rows is faster and less work on the database than using 3 calls to return 4 rows.

I should mention there is an alternative approach of creating a custom class that inherits from IEnumerable and can intercept the lazy loading that occurs, but this actually takes more time in my opinion, and may be more prone to error as there are more steps involved.

Enjoy. ;)

meta: Linq DataReader Load Nested List of Objects and Complex Data Results from SqlDataSource

Arithmetic overflow error converting type to data type numeric

This error usually occurs when the type you are trying to convert to a decimal or numeric is larger than the column or casted type you are converting to.

Ex.

insert into tablename (decnumber) values (cast(1000.00 as decimal(5,2)))

Since the number specifed is 7 digits long include after the decimal and the converted type can only contain a maximum of 5 digits, you will receive this error.

Changing the table to hold 7,2 and/or changing this in the cast will resolve it.

References
devshed (forums), http://forums.devshed.com/ms-sql-development-95/arithmetic-overflow-error-converting-numeric-to-data-type-numeric-89157.html

DLL in Stored Procedure MSSQL

Extended Stored Procedures (MSDN), http://support.microsoft.com/kb/190987

Thread Local Storage in an extended stored procedure (MSDN), http://support.microsoft.com/kb/163449

C# MySQL On Windows Named Pipes Development Mode

While outlining the scope for a new project I’ve considered the possibility of using MySQL for budgeting purposes with respect to the potential size and growth rate of the database.

I downloaded and ran through the latest setup for MySQL on Windows (x64 although I opted for the 32 bit binaries) and popped in the MySql Connector DLL (ADO not ODBC) into my project and began whipping up some code in no time.

However, this little trick seemed to be not well known after some google searching on why MySQL Workbench (great tool btw) can connect, but my app could not.

Being a security conscious developer, when installing SQL I selected developer mode and opted for a safer named pipes connection for local development vs socket network connection looping through my localhost via TCP.

If you also selected this option, you may have had trouble getting your app to connect as well.

After searching through various (not necessarily directly related) sites, I found the answer.

See simple connection string snippet below.

Pipe=MySQL;Protocol=pipe;Host=.;Database=[yourdb];User ID=[youruser];Password=[yourpass]

To my surprise, the little note about specifying a period “.” as the host for named pipe connections was not easily found in the references below..

References
PhalangerMySQL (Java2s), https://www.java2s.com/Open-Source/CSharp/Database/PhalangerMySQL/MySql/Data/CatalogData.htm
.Net MySQL Connector Documentation (MySql), http://dev.mysql.com/doc/refman/5.1/en/connector-net.html
Network Traffic Filter Documentation (wireshark), http://wiki.wireshark.org/DisplayFilters
Connecting MySQL via CLI (MySQL), http://dev.mysql.com/doc/refman/5.1/en/connecting.html
Connection String Reference (MySQL), http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html
MySQL Workbench Documentation (huihoo), http://docs.huihoo.com/mysql/refman-5.5-en/wb-intro.html
Connectionstrings.com, http://www.connectionstrings.com/mysql
codemaker.co.uk, http://www.codemaker.co.uk/it/tips/ado_conn.htm#MySQLNETNativeProvider
MySqlConnection in C# (bitdaddys)http://bitdaddys.com/MySQL-ConnectorNet.html
PHP MySQL Reference, http://php.net/manual/en/function.mysql-connect.php
MySqlCommand in C# (stackoverflow) http://stackoverflow.com/questions/2775692/c-and-mysql-net-connector-any-way-of-preventing-sql-injection-attacks-in-a-ge

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, http://ronniediaz.com/2010/12/21/lambda_functions_in_-net/

Follow

Get every new post delivered to your Inbox.