Blog Archives

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

Advertisements

Bind Control to an Object in Windows Forms

Simple solution for basic binding in Windows Forms app. This would NOT be recommended if you are using ASP .Net, Silverlight, WCF, RIA or any other services to retrieve the data as these project types have much better support for binding to controls.

C#:

static class dataaccess {
static mytype object;
}

//on app load
object = new mytype();

//on form load
tbField1.DataBindings.Add("Text", dataaccess.object.property, "Field1")

Lambda Functions in .Net

Lambda expressions in .Net are denoted by the ‘=>’ symbol.

These powerful operators can be used to create sleek, optimized loops that perform much faster.

The usage below condenses the equivalent of at least 20 lines of two loops.

(Sorry kiddos, no time for VB conversion…C# only this time. ;))

Edit (20101229): Lambda rocks! Simply amazing! Greatest ever!

Ex. 1:

            PaymentMethodCollection visiblePaymentMethods = new PaymentMethodCollection();

            DataSet myPaymentMethods = GlobalDataLoads.PaymentMethods();
            List<DataRow> pmids = myPaymentMethods.Tables[0].Select("Visible = 1").ToList();
            List<int> ids = pmids.Select(i => i["PaymentMethodId"]).Cast<int>().ToList(); 
//.Cast<int>().ToList();
            List<PaymentMethod> pm = availablePaymentMethods.FindAll(i => ids.Contains(i.PaymentMethodId));

            availablePaymentMethods.Clear();
            availablePaymentMethods.AddRange(pm);

Ex. 2:

USER user = tol_dc.USERs.Single(u => u.USER_ID == (int)userid);

Ex. 3:

//DataContext tol_dc;
            List<int> courseids = registrationList.Select(i=>i.MODULE_ID).ToList();
            List<MODULE> modulenames = tol_dc.MODULEs.Where(i=>courseids.Contains(i.MODULE_ID)).Cast<MODULE>().ToList();

Ex. 4: (Get the value of data item inside the child repeater of a parent repeater if the UID is known)

//RepeaterCommandEventArgs e
        if ((stringvalueineed==null) || (stringvalueineed=="")) {
        int uid= (int)((System.Data.DataTable)rptCourses.DataSource).Rows[e.Item.ItemIndex]["UID"];
        stringvalueineed= ((System.Data.DataRow[])(((Repeater)rptOne.Items[e.Item.ItemIndex].FindControl("rptTwo")).DataSource)).Single(i => (int)i["UID"] == uid)["COLUMNNAME"].ToString();
        }

Ex. 5a: (filter a list of available products by removing all excluded products from the list, such as those out of stock)

        private bool Load_AvailableProducts()
        {
            string region = Session["region"];

            lbAvailableProducts.Items.Clear();
            LinqData.DataClassesDataContext dc = new LinqData.DataClassesDataContext();

            List<LinqData.GET_PRODUCT_LISTResult> allproducts = (from p in dc.GET_PRODUCT_LIST(region)
                                       select p).ToList();
            List<int> excproducts = (from ListItem m in lbExcludedProducts.Items
                                       select Convert.ToInt32(m.Value)).ToList();

            List <LinqData.GET_PRODUCT_LISTResult> availproducts = allproducts.Where(p => !excproducts .Contains(p.ProductID)).ToList();

            lbAvailableProducts.DataSource = availproducts;

            lbAvailableProducts.DataTextField = "ProductName";
            lbAvailableProducts.DataValueField = "ProductID";
            lbAvailableMarkets.DataBind();
            return true;
        }

Ex. 5b: (same as above.in this version code is smaller and more load is placed on SQL server rather application (IIS). analyze with SQL profiler and you’ll see the difference)

        private bool Load_AvailableProducts()
        {
            string region = Session["Region"];

            lbAvailableProducts.Items.Clear();
            LinqData.DataClassesDataContext dc = new LinqData.DataClassesDataContext();

            List<LinqData.GET_PRODUCT_LISTResult> availproducts = (from i in dc.GET_PRODUCT_LIST(region)
                                       select i).Where(m => !(from ListItem l in lbExcludedProducts.Items
                                       select Convert.ToInt32(l.Value)).ToList().Contains(m.ProductID)).ToList();

            lbAvailableProducts.DataSource = availproducts;

            lbAvailableProducts.DataTextField = "ProductName";
            lbAvailableProducts.DataValueField = "ProductID";
            lbAvailableProducts.DataBind();
            return true;
        }

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

Ex. 6:

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();
        }
    }

Ex. 7 (foreach)

//make sure list first
List<mydatatype> data = ReturnSomeData().ToList();

//technically long way but helps understand what is happening
Func<mydatatype, string> f = (a) => a.somepropertyname = "somevalue";
                    data.ForEach(i => f(i));

//or simply

data.ForEach(c => c.somepropertyname = "somevalue";

Ex. 8 Lambda Cast to Custom Object (list of objects from one type to another)

//DAL.StoreProcedures is simply a static class with functions that instantiate and dispose of datacontexts and return object collections from data calls

List<geocode> geocodes = DAL.StoredProcedures.LoadGeoCodes_Zip(market, datestart).Select(g=> new geocode {lat=g.Latitude.ToString(),lng=g.Longitude.ToString()}).Cast<geocode>().ToList();

References:
MSDN, “Lambda Expressions (C# Programming Guide)”, http://msdn.microsoft.com/en-us/library/bb397687.aspx
MSDN C# Developer Center, “101 Linq Samples”, http://msdn.microsoft.com/en-us/vcsharp/aa336746
Fraction of The Blogosphere, https://ronniediaz.com/2010/12/21/lambda_functions_in_-net/
StackOverflow, http://stackoverflow.com/questions/1909268/convert-a-list-of-objects-from-one-type-to-another-using-lambda-expression