Category Archives: Programming & Development
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
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.
an exception was encountered while constructing the content of this frame can’t see solution files
This issue occurred in my VS after installing some third party Visual Studio plugin tools. To resolve, I uninstalled the tools and from command line launched Visual Studio (just copy paste the path and exe from the shortcut) and added /ResetSettings to the launch params. Make sure you have a backup of your settings first then after resetting you can reload them
Classic ASP VB Filter File Name Extensions From String EndsWith
Came across a classic ASP VB site recently in my adventures. There was a feature request to filter out some file extensions from existing code logic, and I discovered quickly most of my .Net methods were unavailable, so I came up with this little snippet and had a great blast from the past. 
function FilterExtensions(fn)
FilterExtensions=true
a_ext = Array(".db",".db") 'place additional extensions here
for each ext in a_ext
i = InStrRev(fn,ext)
if i>0 then
FilterExtensions=false
end if
next
end function
If FilterExtensions returns true then there were no matches (extension of filename successfully passed all filters).
How to Create a new Java Applet in Netbeans
First create a new project. Make sure you select “Java Application” not Desktop Application or other types.
On the left hand of the screen you should see “Source Packages” and your project name in lower case underneath. If you don’t see this in the little window on the left, expand the coffee icon with your project name.
Right click your package (mine is javafileuploader in the screenshot above) and select New->Java Class.
Make sure your new class is selected, and then referencing my code snippet below, import java.applet.* and java.awt.* then add new functions for “paint” and “init”.
(code snippet courtesy of ehow link in references)
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package javafileuploader;
import java.applet.*;
import java.awt.*;
/**
*
* @author fedora
*/
public class NewClass extends Applet {
int m_height, m_width;
public void paint(Graphics m) {
m.setColor(Color.black);
for (int i=0; i<10; ++i)m.drawLine(m_width,m_height,i*m_width/10,0);
}
public void init() {
m_width=getSize().width;
m_height=getSize().height;
setBackground(Color.green);
}
}
Once you have this typed/pasted in, select run->file and voila! you should see a little applet window appear with a green background and oblique lines. You’re now ready to begin. Enjoy!
References
ehow.comhttp://www.ehow.com/how_6210608_create-java-applet-netbeans.html
IBM RSA rational software architect eclipse plugin rename change project location
This one evaded me at first and is very simple. Experienced Eclipse users are already familiar with its’ small intricacies, but for a Microsoft Visual Studio Developer who is not on Eclipse often, it is easy to forget your way weaving through the maze of perspectives, windows and views.
Lets say you created a new model, and accidentally placed it in the wrong location.
I’ve used Eclipse quite a bit in the past with PHP, so my first thought was to simply “refactor”/”move”. However, this would not solve my problem.
The resolution is quite simple. First, right click on the project, close it, then delete it.
After deleting. Select file->import->existing projects into workspace->(next).
Browse to the new root location and then continue through (finish).
That’s it! Hope this helped! 8)
lastChild is null in FireFox works in IE invalid nodeType javascript c# asp .net
This issue alluded me at first as it works in IE but not in FF. See code below.
//pass in table, last cell number and style to apply to it. call this on hover and blur for cell highlight effects. alternatively you can determine last cell number as well and this function could be rewritten to work solely for the purpose of modifying specific cells rather than last cell
function ChangeTableCellStyle(tableid,cellnumber,mystyle) {
if (document.getElementById)
{
var selectedElement = document.getElementById(tableid);
selectedElement.className = style;
//change style on end cell by drilling into table. this will become deprecated by css3.
if (selectedElement.tagName.toLowerCase()=="table")
{
var tbody = selectedElement.lastChild;
if (tbody!=null)
{
var tr = tbody.lastChild;
if (tbody !=null)
{
var tr = tbody.lastChild; //BUGGED IN FF!
//nodetype should be 1 for element type. in FF it is 3. see reference link at bottom for list of types.
if (tr.nodeType!=1) {
tr.tbody.getElementsByTagName("td");
tr[cellnumber].className+= ' ' + mystyle;
} else {
tr.lastChild.className+=' ' + mystyle;
}
}
}
}
}
}
//example usage
ChangeTableCellStyle("table1",3,"cellend"); //will append the class cellend to the last cell in table1 if table1 only has 4 cells per row
In you’re interested in reviewing other approaches to styling your table cells, see my similar article here.
change last row cell style in css and javascript cross browser compatibility issues
There are various approaches to achieve this functionality with equally different levels of compatibility across browsers. Here are some of the ways.
Javascript Approach (most compatible. you may recognize this code from another one of my articles.):
function ChangeTableCellStyle(tableid,cellnumber,mystyle) {
if (document.getElementById)
{
var selectedElement = document.getElementById(tableid);
selectedElement.className = style;
//change style on end cell by drilling into table. this will eventually become completely replaced by css3
if (selectedElement.tagName.toLowerCase()=="table")
{
var tbody = selectedElement.lastChild;
if (tbody!=null)
{
if (tr.nodeType!=1) {
tr.tbody.getElementsByTagName("td");
tr[cellnumber].className+= ' ' + mystyle;
} } } } }
HTML col tags and hardcoded inline style overrides (easy, but messy IMO. also definitely not a good approach for styling lots of similar looking pages)
<table> <col style="background-color: #6374AB; color: #ffffff" /> <col span="2" style="background-color: #07B133; color: #ffffff;" /> <tr> <td style="background-color: #000000">First TD of first TR</td> [... etc ...] <tr style="background-color: #6374AB;"> // second TR
CSS3 last child (super clean and awesome but unfortunately not well supported by browsers)
p:last-child {
⋮ declarations
}
jQuery Approach (this also has good compatibility and much cleaner than your own JS function, but requires an external jquery library reference which may be overkill unless you’re already using it):
$(document).ready(function() {
$('table.class tr:last-child').addClass('ClassName'); //can also speciy firstchild etc
});
Some of the snippets above are quoted snippets directly from references below. See the reference links for more information on a specific approach.
As the above examples have illustrated there are many ways to achieve what you need each varying depending on your implementation. Hope this article helped! Enjoy.
References
CSS fixed table approachhttp://stackoverflow.com/questions/359821/styling-the-last-td-in-a-table-with-css
HTML col tags approach, http://www.quirksmode.org/css/columns.html
css3 approach, http://reference.sitepoint.com/css/pseudoclass-lastchild
jQuery approach, http://stackoverflow.com/questions/5850835/jquery-selector-to-last-row-first-column
system.windows.resourcedictionary. source threw an exception wpf c#
In general this issue is caused when a referenced resource cannot be found.
<Application x:Class="AppName.App"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
StartupUri="MainWindow.xaml">
<Application.Resources>
<ResourceDictionary>
<ResourceDictionary.MergedDictionaries>
<ResourceDictionary Source="/PresentationFramework.Aero;component/themes/Aero.NormalColor.xaml"/>
</ResourceDictionary.MergedDictionaries>
</ResourceDictionary>
</Application.Resources>
</Application>
In my specific circumstance, as the xaml above details, this issue was caused by a reference to PresentationFramework.Aero DLL that was not contained in the output bin folder of the compiled project. Under references, setting Copy Local to true did the trick for me.
