Archives for category: .NET

Let’s face it.  There are times when a stored procedure is going to create a more efficient query, and provide the flexibility you need to return attributes from multiple SQL tables in a non-traditional way.

In my case, I had a number of different log tables that I wanted to combine into a single result.  My log tables contained different field attributes, and so I was not able to simply use the ObjectQuery<T>.Union method to combine my results.  It was much easier to simply create a stored procedure that could translate the information into a nice generic result from the combined tables.

It was this result that I wanted to work with, not the individual tables.  You can accomplish this in three easy steps:

1) Update your Model diagram (edmx) with the stored procedure you want to add.  (Update Model from Database –> Add –> Stored Procedure)

step1

2) Right-click on your diagram and choose Add –> Function Import.  Enter the name you want to use as the Entity function to retrieve the stored procedure.  Select the stored procedure you want to associate with this Entity Function.  Press Get Column Information.  Then select Create New Complex Type.  Enter the name of the custom Complex Type Entity you want to associate with the stored procedure.

step2

3) That’s it!  Now you can use the function in a very elegant way and still have a strongly typed object to work with.

step3

Advertisements

Recently, after a patch was applied to the web server, some of our larger, more intensive applications starting throwing the following fairly cryptic exception:

ERROR: System.Web.HttpException: The URL-encoded form data is not valid.
System.InvalidOperationException:  Operation is not valid due to the current state of the object.

This is due to an update (MS11-100) that was pushed out in December 2011 that allows a web form to accept a limit of 1,000 items.

The fix is easy, simply add an item to your web.config appSettings section.  The following example increases the number of items accepted by a web form from 1,000 to 9,999:

<appSettings>
  <add key=”aspnet:MaxHttpCollectionKeys” value=”9999″ />
</appSettings>

I attempted to create an RSS in an MVC 2.0 application using my typical C# Web forms approach and received the following error:  OutputStream is not available when a custom TextWriter is used.

The main reason for this is that MVC Views assume the request will be in standard HTML and so it switches the TextWriter with its own writer.

The easiest way to get this done, then, is to handle the logic at the Controller level, rather than the View level.

1) First you will need to include a couple of libraries:

using System.Xml;
using System.ServiceModel.Syndication;

2) Then you want to create a custom RssActionResult, which the Controller will return:

public class RssActionResult : ActionResult
{
public SyndicationFeed Feed { get; set; }
public override void ExecuteResult(ControllerContext context)
{
context.HttpContext.Response.ContentType = “application/rss+xml”;

        Rss20FeedFormatter rssFormatter = new Rss20FeedFormatter(Feed);
using (XmlWriter writer = XmlWriter.Create(context.HttpContext.Response.Output))
{
rssFormatter.WriteTo(writer);
}
}
}

3) Then you want to set the Controller response to your custom result:

SyndicationFeed feed = new SyndicationFeed(
“IDEAInbox”,
“IDEAInbox Description”,
new Uri(“
http://IDEAInbox”),
“IDEAInboxSyndicationV1.0”,
DateTime.Now);
List<Idea> _Ideas = _myIdeaInboxservice.GetIdeas(-1, -1, 1);
List<SyndicationItem> items = new List<SyndicationItem>();
foreach (Idea _Idea in _Ideas)
{
SyndicationItem item = new SyndicationItem(_Idea.IdeaSubject,
_Idea.IdeaDescription,
new Uri(_Idea.Link),
“IDEAInboxIdeaID” + _Idea.IdeaID.ToString(),
_Idea.StatusInformation.CreateDateTime);
items.Add(item);
}
feed.Items = items;
return new RssActionResult() { Feed = feed };

I have an auto-generated entity framework Entity Data Model Designer File (edmx).  And while I am using Data Annotations for validation purposes, there are times when retrieving the maxlength of an entity property programmatically would be very useful.

image

In some cases, I may want to simply truncate the value, rather than throw an error if the maxlength of a property is exceeded.  Or I may want to surface the maxlength property to the client for client-side validation purposes.

It turns out to be a simple query, but it took me a while to find it.  You will need to reference the following libraries:

using System.Data.Objects.DataClasses;
using System.Data.Metadata.Edm;

This is the method to retrieve the MaxLength property.  Please note that you may get an exception if the property does not have a MaxLength property specified.

public static int? GetMaxLength(this EntityObject entityObject, string entityProperty)
{
CATDBEntities _context = new CATDBEntities();
int? result = null;
using (_context)
{
var q = from meta in _context.MetadataWorkspace.GetItems(DataSpace.CSpace)
.Where(m => m.BuiltInTypeKind == BuiltInTypeKind.EntityType)
from p in (meta as EntityType).Properties
.Where(p => p.DeclaringType.Name == entityObject.GetType().Name
&& p.Name == entityProperty
&& p.TypeUsage.EdmType.Name == “String”)
select p;

    var queryResult = from meta in _context.MetadataWorkspace.GetItems(DataSpace.CSpace)
.Where(m => m.BuiltInTypeKind == BuiltInTypeKind.EntityType)
from p in (meta as EntityType).Properties
.Where(p => p.DeclaringType.Name == entityObject.GetType().Name
&& p.Name == entityProperty
&& p.TypeUsage.EdmType.Name == “String”)
select p.TypeUsage.Facets[“MaxLength”].Value;
if (queryResult.Count() > 0)
{
result = Convert.ToInt32(queryResult.First());
}
}
return result;
}

To call this method, simply instantiate the entity object and pass in the proper parameters:

project _project = new project();
int? maxLength = DataAnnotation.GetMaxLength(_project, “project_name”);

I struggled a bit attempting to generate dynamic javascript in an MVC Razor page view.  I kept getting the following error:  Too many characters in character literal.

I did a little research and found the answer.  You will need to include
@using System.Text; at the top of your page view.

To build my javascript, I included the following code block within my <script> tags:

@{
StringBuilder sb = new StringBuilder();
foreach (GetBillingCodes_Result billingCode in Model.BillingCodes)
{
sb.Append(“AddBillingCode(‘” + billingCode.billing_code + “’”);
sb.Append(“, ‘” + billingCode.travel + “’”);
sb.Append(“, ‘” + billingCode.billing_code_type + “’”);
sb.Append(“, ‘” + billingCode.billing_rate + “’”);
sb.Append(“, ‘” + billingCode.taxable + “’”);
sb.Append(“, ‘” + billingCode.discount + “’”);
sb.Append(“, ‘” + billingCode.C100_club + “’”);
sb.Append(“, ‘” + billingCode.active + “’”);
sb.Append(“);”);
}
@MvcHtmlString.Create(sb.ToString());
}

I was deploying my MVC3 application into a production that did not have the proper DLLs in the GAC.  So I used the handy little tool in Visual Studio that allows you to include the DLLs in the BIN folder.  Simply right-click on the project in Visual Studio and select Add Deployable Dependencies.

image

But when I deployed the project into production, it kept attempting to redirect me to Account/Login, which does not exist.  And I have not routed those values anywhere.

It turns out that the WebMatrix.Data.dll was copied into my BIN directory and into production.  This DLL is the culprit.  I removed all instances of this DLL from my project, and the issue went away.

image

It is pretty easy to configure SSL on your IIS website with a valid certificate. We have SSL installed and configured on one of our web servers, and we prefer our employees to access these websites over a secure protocol (HTTPS). However, we do not have the luxury of globally enforcing SSL on all sites hosted by the web server, because in some cases it causes an accessibility issue with some of our employees who are working from a remote location with firewall limitations. So we want to be able to allow them to access a web site using the regular HTTP protocol as an exception, not a rule.

So even if you provide a link to a person that specifies HTTPS, you cannot be guaranteed that the person will always access the web site using that protocol. Since SSL is not enforced at the web server level, then you have to enforce it logically, which a piece of code that performs a check. The following is a quick and dirty approach to “Logical SSL Enforcement”:

public void EnforceSSL() {

// Determine SSL Exception – SSL_OFF = “1”
string sslException = “0”;
if (Request.QueryString[“SSL_OFF”] != null) {
sslException = Request.QueryString[“SSL_OFF”].ToString();
}

if (sslException == “1”) {
return;
}

// Do Not Enforce SSL if you’re in a Dev Environment
string currentURL = Request.ServerVariable[“HTTP_REFERER”].ToString().ToLower();
string devURL = “localhost”;
if (currentURL.IndexOf(devURL) != -1) {
return;
}

// Redirect to HTTPS Protocol
if (url.ToLower().IndexOf(“http:”) != -1) {
string newURL = currentURL.Replace(“http:”, “https:”);
Response.Redirect(newURL, true);
}

}

For those of us still actually supporting ADO.NET applications, I thought I would jot this one down. J

I wanted to convert a DataRow[] to a DataTable. While DataRow[] are nice, unfortunately they do not support all of the nice features of a DataTable, such as filtering and sorting. Since I needed to further evaluate and sort the DataRow[], I decided that it would be a good idea to go ahead and convert it to a DataTable. Turns out, there is no straight conversion available, so you basically have to create a DataTable and add the rows from the DataRow[] objects. I initially started with this:

// DataTable: Results
// DataRow[]: FilteredResults
public DataTable ConvertToDataTable(DataRow[]FilteredResults)
{
DataTable dt = new DataTable();
foreach (DataRow dr in FilteredResults)
{
dt.Rows.Add(dr);
}
return dt;    
}

However, when I ran this code, the application threw the following exception: This row already belongs to another table. It turns out I was ALMOST right, but needed to make a couple of modifications: 1) I needed to create a new DataTable that was a clone of the original DataTable; 2) I needed to use the ImportRow() method rather than the Rows.Add() method.

// DataTable: Results
// DataRow[]: FilteredResults
public DataTable ConvertToDataTable(DataTable Results, DataRow[] FilteredResults)
{
DataTable dt = Results.Clone();
foreach (DataRow dr in FilteredResults)
{
dt.ImportRow(dr);
}
return dt;
}

I am creating a Flot Line Chart in .Net based on the following Entity Framework result set.

ef_distinct_sort_rcerda

I want to get a distinct list of the report categories in alphabetical order to use as a control group.

Entity Framework provides an easy Distinct() method to retrieve distinct values from a data set, but it wasn’t completely obvious to me how to make sure that it was given to me in ascending order.

Turns out, it’s pretty easy.

List<string> categories = reportSummary.Select(m => m.CategoryName).Distinct().ToList();

I am creating a report based off of the following result set:

ef-distinct-multi-column

I wanted to get a unique set of weeks from the control group, but I also wanted to retrieve whether the week is an actual vs. a forecast.

Turns out it’s pretty easy:

public List<WeeklyUtilizationHistoryAndForecast> ReportSummary = businessService.GetWeeklyUtilizationHistoryAndForecast(isCache, ParamUserId, ParamStartWeek, ParamEndWeek, ParamBusinessUnitId, ParamIncludeTimeOff);

public List<WeeklyUtilizationHistoryAndForecast> Weeks = ReportSummary.OrderBy(m => m.WeekDisplay).DistinctBy(m => new { m.WeekDisplay, m.IsActual }).ToList();