Blog Archives
MIT Professor Develops NSA-like Email Data Visualization Software
See for yourself:
https://immersion.media.mit.edu/
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
Import OData / XML / RSS / Webservice Feeds into Excel
If you came here looking for some quick and easy code that you can plug right in and dump data in excel format.. You have come to the wrong spot. 🙂
Depending on the scenario you may need this functionality for, you may want to check out a cool tool backed by Microsoft called “Powerpivot“.
This code is not hard to write, but there are many points of error and re-implementation for multiple users and/or clients is likely a larger project than you (or your boss) might realize if you haven’t dealt with data conversion processes before or have multiple complex data relationships which may or may not change data structure over time (addition/removal of columns, mappings, etc).
(For an example on some sample code to help you with this task, see one of my older entries on converting datatable to csv. https://ronniediaz.com/2010/03/02/csv-to-datatable/)
However, if you decide to go the power pivot route, this is a cool little plugin for use with Microsoft Excel which allows you to directly import data from various streams and feeds including OData and SQL.
This plugin basically eliminates the steps involved for an end user to “export to csv, then import into excel”, as well as cuts back on the time and potentially error prone task of assigning a developer to create a data export for this same process.
Check it out.
http://www.powerpivot.com/
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