Blog Archives
c# .net split strings with math divrem using lambda linq
Posted by Ronnie Diaz
Recently I came across a nice alternative to loops using linq for evenly splitting a string using Math.DivRem.
The following example illustrates how this can be used to parse a sequence of numbers based on a time series which may give variable results in a custom messaging protocol.
(inspired by R. Prestol)
//not the complete classes but satisfies the below example
class message {
string series {get;set;}
}
string[] series = message.series.Split(' '); //will NOT throw an exception if series string is empty
int Rem = 0;
int d = Math.DivRem(name.Length, 2, out Rem); //hardcoded two for simplicity in this example
//valueB will contain the same value as valueA if there is no second value in the sequence
string valueA = string.Join(" ", sentence.Take(Math.Max(d, 1)).ToArray());
string valueB = string.Join(" ", sentence.Skip(d).Take(d + Rem).ToArray());
int average = (Convert.ToInt32(valueA) + Convert.ToInt32(valueB)) / 2; //2 would also need to be dynamic here
Console.WriteLine(average.ToString());
//potential input
//100 101
//100
//output for average
//first input: 100
//second input: 100
A significant figure is lost (.5) on first input since valueA and valueB are converted to int. Conversion to decimal, double etc would of course maintain this detail depending on the rounding you are looking for.
linq to csv extension method
Posted by Ronnie Diaz
Can be used to convert list of strongly typed objects to csv output.
public static class extensionmethods
{
#region LinqToCSV
public static string ToCsv<T>(this IEnumerable<T> items)
where T : class
{
var csvBuilder = new StringBuilder();
var properties = typeof(T).GetProperties();
string header = string.Join(",", properties.Select(p => p.Name.ToCsvValue()).ToArray());
csvBuilder.AppendLine(header);
foreach (T item in items)
{
string line = string.Join(",", properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
csvBuilder.AppendLine(line);
}
return csvBuilder.ToString();
}
private static string ToCsvValue<T>(this T item)
{
if (item == null) return "\"\"";
if (item is string)
{
return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
}
double dummy;
if (double.TryParse(item.ToString(), out dummy))
{
return string.Format("{0}", item);
}
return string.Format("\"{0}\"", item);
}
#endregion
Multiple Stored Procedures with same return type in LINQ
Posted by Ronnie Diaz
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
Posted in Programming & Development
Tags: add result, bind, complex loading, data, databind, datareader, datasource, dynamic, linq, linq datareader, linq to sql, list, list of objects, load, load list, loading complex, loading lists, nested, nested list, nested lists, result, sql, sqldatasource, stored procedure, strongly type, type
C# ListBox Management Adding Removing Updating Databound Items ASP .Net
Posted by Ronnie Diaz
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/
OrderBy Column Name as String using Linq C# .Net Dynamic Sorting of Anonymous Types
Posted by Ronnie Diaz
If you’re familiar with Linq, you have undoubtedly used the popular “OrderBy” extension method. Unfortunately, this method does not accept a string value with the column name.
var data = from i in db.tablename
select i;
repeatername.datasource = data.OrderBy(i=>i.columnname); //this works
repeatername.datasource = data.OrderBy("columnname"); //this does not
To resolve, you can add the following small class or just the method to a generic DLL you use in your web application projects or create a new class or project for your extension methods.
Kudos to R. Prestol for help researching this one.
using System.Linq;
using System.Linq.Expressions;
using System;
namespace YourAppName.Web
{
public static class extensionmethods
{
public static IQueryable<T> OrderByField<T>(this IQueryable<T> q, string SortField, bool Ascending)
{
var param = Expression.Parameter(typeof(T), "p");
var prop = Expression.Property(param, SortField);
var exp = Expression.Lambda(prop, param);
string method = Ascending ? "OrderBy" : "OrderByDescending";
Type[] types = new Type[] { q.ElementType, exp.Body.Type };
var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);
return q.Provider.CreateQuery<T>(mce);
}
}
}
Returning to our first code snippet, you can now do:
repeater.datasource = data.OrderByField("columnname");
Since this is an extension method, and exists in a class within the same namespace as your project or other referenced assembly, you can call the method directly from the IQueryable Linq data object without requiring any other inherits; this works similar to override functionality.
Read up about extension methods on MSDN or Google for other cool tricks you can do. Enjoy. 😉
References
Extension Methods (MSDN), http://msdn.microsoft.com/en-us/library/bb383977.aspx
Convert SQL to Linq
Posted by Ronnie Diaz
Was looking to convert a complex SQL query and short on time, so came across this nifty little tool to automate the conversion! It even outputs and autobuilds necessary classes!
At the time of this article supports all versions of Linq in .Net up to 4.0.
Linqer
References
Linqer, http://www.sqltolinq.com/
Out of Office Reply 