SmartMove

Home
FAQs
Login

Inspecting EntityValidationErrors

try { context.[table name].Add([object]) // Could also be before try if you know the exception occurs in SaveChanges context.SaveChanges(); } catch (DbEntityValidationException e) { foreach (var eve in e.EntityValidationErrors) { Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State); foreach (var ve in eve.ValidationErrors) { Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage); } } throw; }

Delete Dupes

WITH g AS (
 SELECT ROW_NUMBER() OVER(PARTITION BY city, state, zip ORDER BY city, STATE, zip) AS row
 FROM dbo.Geography) 
DELETE FROM g
WHERE row > 1;

SQL Date Queries

SELECT DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()) AS FirstDayOfCurrentMonth

DECLARE @mydate DATETIMESELECT @mydate = GETDATE()

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,'Last Day of Previous Month'

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,'First Day of Current Month' AS Date_Type

UNION

SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,'Last Day of Current Month'

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,'First Day of Next Month'


Reference: http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/

Workaround for Trust Relationship for SSL/TLS error with WCF service

System.Net.ServicePointManager.ServerCertificateValidationCallback = ((sender, certificate, chain, sslPolicyErrors) => true);

Reference: http://www.codemeit.com/wcf/wcf-could-not-establish-trust-relationship-for-the-ssltls-secure-channel-with-authority.html

Using log4net in ASP.NET for capturing exceptions to both email and a log file

Here is how I am using log4net to capture exceptions to both
a log file and an email:

1.
Created the following method:

private void
LogError(Exception ex)

{

ILog loggerFile = LogManager.GetLogger("File");

ILog loggerEmail = LogManager.GetLogger("EmailLog");

loggerFile.Error("Exception in " +
Request.RawUrl, ex);

loggerEmail.Error("Exception in "
+ Request.RawUrl, ex);

1. 
   }
   
   
   Call this method in the “catch” block
   passing the error object instance:

catch (Exception ex)

{

LogError(ex);

}

3.
Added the following in Global.asax:Application_Start():

void Application_Start(object sender, EventArgs
e)

{

// Code that runs on application startup

log4net.Config.XmlConfigurator.Configure();

}

4.
Added the following <section> in web.config:

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,
log4net"/>

5

Added
the following section to web.config:

<log4net>

<appender name="LogFileAppender" type="log4net.Appender.FileAppender">

<!-- Please make
shure the ..\\Logs directory exists! -->

<param name="File" value="Logs\\MyLog4Net.log"/>

<layout type="log4net.Layout.PatternLayout">

<conversionPattern value="%newline%date [%thread] %-5level %logger [%property{NDC}] -
%message%newline"/>

</layout>

</appender>

<appender name="SmtpAppender" type="log4net.Appender.SmtpAppender">

<to value="someone@whatever.com"/&gt;

<from value="application@whatever.com"/&gt;

<subject value="My Application System Error (Log4Net)"/>

<Authentication value="Basic"></Authentication>

<Username value="user@whatever.com"></Username&gt;

<Password value="password"></Password>

<Port value="25"></Port>

<smtpHost value="SMTP.whatever.com"/>

<bufferSize value="512"/>

<lossy value="false"/>

<Priority value="High"></Priority>

<evaluator type="log4net.Core.LevelEvaluator">

<threshold value="WARN"/>

</evaluator>

<layout type="log4net.Layout.PatternLayout,log4net">

<conversionPattern value="%property{log4net:HostName} :: %level :: %message
%newlineLogger: %logger%newlineThread: %thread%newlineDate: %date%newlineNDC:
%property{NDC}%newline%newline"/>

</layout>

</appender>

<logger name="File">

<level value="DEBUG"/>

<appender-ref ref="LogFileAppender"/>

</logger>

<logger name="EmailLog">

<level value="WARN"/>

<appender-ref ref="SmtpAppender"/>

</logger>

</log4net>

Find() in a Generic List

List = new List(id);

Foo myFoo = foos.Find(delegate(Foos e) { return e.PropertyName == "whatever"; });

LINQ for getting unique counts of an item in a DataTable

Here is an efficient way to get a unique count using LINQ from the results of a DataTable:

// DataTable produced by LLBGen stored procedure method DataTable
servicesInOrder = RetrievalProcedures.GetMyServicesWithinGroups(orderId);

// Do a quick count to see whether multiple services are within each service group associated with this order
var result = from row in servicesInOrder.AsEnumerable()
group row by new
{
ServiceGroup = row.Field("PackageGroupName")
} into grp
select new
{
ServiceGroup = grp.Key.ServiceGroup,
Services = grp.Count(),
UniqueServices = (from p in grp
select p.Field("ServiceID")).Distinct().Count()
};

The Results from a runtime watch look like:




I changed it up a bit here to make the query more specific by returning the count for each specific group. I then use the value of this count to determine further processing logic. Below is the revised LINQ query, and the usage of its results. All relevant entries are highlighted in yellow.

if (servicesInOrder != null)
{
// If services are found, find the DataTable column, and append the services and associated values to the DataTable row.
for (int j = 0; j <>
{
DataRow serviceRow = servicesInOrder.Rows[j];
if (serviceRow != null)
{

// Do a quick count to see whether multiple services are within each service group associated with this order
var result = from row in servicesInOrder.AsEnumerable()
where row.Field("PackageGroupName") ==
serviceRow["PackageGroupName"].ToString()
group row by new
{
ServiceGroup = row.Field("PackageGroupName")
} into grp
select new
{
ServiceGroup = grp.Key.ServiceGroup,
Services = grp.Count(),
UniqueServices = (from p in grp
select p.Field("ServiceID")).Distinct().Count()
} ;

if (serviceRow["PackageServiceID"] != DBNull.Value)
{
// Add services within service group if order has it
int thisServiceGroupId = Convert.ToInt32(serviceRow["PackageGroupID"]);
if (thisServiceGroupId == serviceGroupId)
{
if ((result.Count() > 0) && (result.ElementAt(0).UniqueServices > 1))
newDataRow[col.ColumnName] += "
" + serviceRow["ServiceName"].ToString();
else
newDataRow[col.ColumnName] = serviceRow["ServiceName"].ToString();

if (serviceRow["PackageServiceID"] != DBNull.Value)
{
// Add services within service group if order has it
int thisServiceGroupId = Convert.ToInt32(serviceRow["PackageGroupID"]);
if (thisServiceGroupId == serviceGroupId)
{
if ((result.Count() > 0) && (result.ElementAt(0).UniqueServices > 1))
newDataRow[col.ColumnName] += "
" + serviceRow["ServiceName"].ToString();
else
newDataRow[col.ColumnName] = serviceRow["ServiceName"].ToString();

Encrypting Querystring Parameters

I implemented some routines to encrypt/decrypt a querystring parameter using a symmetric key over Base64. This is based off the following article:

http://devcity.net/Articles/47/1/encrypt_querystring.aspx

Below are the specific places where this was implemented.

1. Namespace: Something.Data
   
   using System;
   using System.Collections.Generic;
   using System.IO;
   using System.Text;
   using System.Security.Cryptography;
   
   namespace Something.Web
   {
   public class Encryption64
   {
   //private byte[] key = {};
   //private byte[] IV = {10, 20, 30, 40, 50, 60, 70, 80}; // it can be any byte value
   public string Decrypt(string stringToDecrypt, string sEncryptionKey)
   {
   byte[] key = { };
   byte[] IV = { 10, 20, 30, 40, 50, 60, 70, 80 };
   byte[] inputByteArray = new byte[stringToDecrypt.Length];
   try
   {
   key = Encoding.UTF8.GetBytes(sEncryptionKey.Substring(0, 8));
   DESCryptoServiceProvider des = new DESCryptoServiceProvider();
   inputByteArray = Convert.FromBase64String(stringToDecrypt);
   MemoryStream ms = new MemoryStream();
   CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(key, IV), CryptoStreamMode.Write);
   cs.Write(inputByteArray, 0, inputByteArray.Length);
   cs.FlushFinalBlock();
   Encoding encoding = Encoding.UTF8;
   return encoding.GetString(ms.ToArray());
   }
   catch (System.Exception ex)
   {
   throw ex;
   }
   }
   public string Encrypt(string stringToEncrypt, string sEncryptionKey)
   {
   byte[] key = { };
   byte[] IV = { 10, 20, 30, 40, 50, 60, 70, 80 };
   byte[] inputByteArray; //Convert.ToByte(stringToEncrypt.Length)
   try
   {
   key = Encoding.UTF8.GetBytes(sEncryptionKey.Substring(0, 8));
   DESCryptoServiceProvider des = new DESCryptoServiceProvider();
   inputByteArray = Encoding.UTF8.GetBytes(stringToEncrypt);
   MemoryStream ms = new MemoryStream();
   CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(key, IV), CryptoStreamMode.Write);
   cs.Write(inputByteArray, 0, inputByteArray.Length);
   cs.FlushFinalBlock();
   return Convert.ToBase64String(ms.ToArray());
   }
   catch (System.Exception ex)
   {
   throw ex;
   }
   }
   }
   }
   
   
2. Something.Common.SessionConst class (complied into its own Dll)
   
   public const string IAteByte = "!#$a54?3";
   
   
3. SomethingWebsite.App_Code.QuerystringProtect.cs
   
   using System;
   using System.Web;
   using Immunization.Common;
   using Immunization.Web;
   ///
   
   /// Summary description for QuerystringProtect
   ///
   
   public static class QuerystringProtect
   {
   public static string EncryptQueryString(string strQueryString)
   {
   Encryption64 e64 = new Encryption64();
   return e64.Encrypt(strQueryString, SessionConst.IAteByte);
   }
   public static string DecryptQueryString(string strQueryString)
   {
   Encryption64 e64 = new Encryption64();
   return e64.Decrypt(strQueryString, SessionConst.IAteByte);
   }
   }
   
   
4. Encrypt querystring in URL - ASPX code-behind:
   
   btnDoc.NavigateUrl += QuerystringProtect.EncryptQueryString(someObject.objectId.ToString());
   
   
5. Decrypt querystring - ASPX code-behind Page_Load()
   
   
   if (Request.QueryString["params"] != null)
   {
   string qs = Request.QueryString["params"];
   string deCrypt = QuerystringProtect.DecryptQueryString(qs.Replace(" ", "+"));
   }

Make sure to "return" before assigning an "onclick" event in code-behind

I often find myself forgetting the trivial... especially when it comes to assigning onclick attributes to a control in code-behind processing. A perfect example is in the OnRowDataBound event for a GridView where I want to assign an cleint-side event handler to a Delete button to show a JavaScript Confirm (OK/Cancel) before the data hits the bit-bucket.

GridView Event Handler in code-behind:
protected void gridview_OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// Add a JavaScript warning event handler to the delete button
LinkButton deleteLinkButton = (LinkButton)e.Row.FindControl("btnDelete");
if (deleteLinkButton != null)
deleteLinkButton.Attributes.Add("onclick", "return ConfirmDelete('" + whateverString
+ "');");
}
}


JavaScript Function in ASPX Mark-Up:

function ConfirmDelete(someData)
{
var msg = "Are you sure you wish to delete this item?\n\n("
+ someData + ")";
if (confirm(msg) == true)
return true;
else
return false;
}

Static Connection String Cause of DataReader Failure - SQLDataReader IndexOutOfRange

Background:
For the past four months, I have been performing fixes and enhancements to a data management application that my client had developed by a third-party vendor. It is a relatively data-intensive ASP.NET application that utilizes the ASP.NET Membership Provider, as well as several custom data access classes. All query logic is done through stored procedures deployed on a SQL Server 2005 database. This application is intended to support concurrent users, but unforrtunately we frequently encounter exceptions such as SQLDataReader ... IndexOutOfRange. This error typically occurs if you try to reference columns that were not returned by a query. In our particular case, we verified that the stored procedures always returned the named columns, and that the DataReader.HasRows always evaluated to true. The DataReader logic was nested within a try/catch block like:

try
{
conn.Open();
SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.SingleRow);
string userName = "";
reader.Read();

if (reader.HasRows)
{
userName = GetDBValue(reader["UserName"]);
}
reader.Close();

return userName;
}
catch (SqlException ex)
{
throw new ApplicationException("An error has occured ...");
}
finally
{
conn.Close();
}

I was lucky enough to find a pattern to consistenly reproduce the error condition, which was essentially opening two browsers and executing the same query. This is a sad story given the application fails under the load of two concurrent users. I spent several days tuning the SQL Server 2005 database, and modifying application code to minimize calls to the database such as persisting data into ViewState. After these efforts, we still experienced the dreaded DataReader errors.

Solution:
I came across this large thread on MSDN entitled "SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange" This article offers several suggestions such as handling DataReader errors, disabling connection pooling and avoid persisting a SQL data connection in a static instance. I took the first, and least invasive route by disabling connection pooling in the connection string located in Web.Config. Unfortunately, this not only impacted the performance, but I still observed the same DataReader errors.

I took the next path by determing how the SQL connection as being utilized. Bingo! I saw that a utility class contained the following:

public class DBUtils
{
protected static SqlConnection conn = null;

protected static SqlConnection GetConn()
{
if (ConnectionString == null)
ConnectionString = WebConfigurationManager.ConnectionStrings
["ApplicationConnectionString"].ConnectionString;
return new SqlConnection(ConnectionString);
}
}

Calls were being made to the above function as:

conn = GetConn();

I modified the above to use a more traditional approach as follows:

SqlConnection conn = new SqlConnection(Common.AppConfig.SqlConnectionString);

Note: AppConfig.SQLConnection string is a public accessor in the application's Common namespace to the connnection string stored in Web.Config.

By creating a new connection string instance instead of relying on the static instance, the application is now able to handle concurrent queries without throwing any DataReader exceptions. I can not say that this will be the ultimate solution for this application, but at least it's a proven stop-gap measure for now.

References:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/74a5db87-c63f-4386-b317-e848dedb2cd9).

Reset Identity Column

Script for resetting an identity column in SQL Server:


Declare @identInitValue int
Declare @tableName varchar(200)
set @identInitValue=1
set @tableName = 'WHATEVER'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED('dbo' + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end

SQL Delete from all tables and reset identity

I came across this useful script on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 to delete all data from a SQL server table and re-seed the identity columns:

use [#db name#]
Set NoCount ON

Declare @tableName varchar(200)
Declare @tableOwner varchar(100)
Declare @skipident varchar(3)
Declare @identInitValue int
set @tableName = ''
set @tableOwner = ''
set @skipident = 'NO'
set @identInitValue=1
/*
Step 1: Disable all constraints
*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*
Step 2: Delete the data for all child tables & those which has no relations
*/
While exists
(
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key'
or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments')
and Table_type='BASE TABLE' and T.table_name > @TableName
)

Begin
Select top 1 @tableOwner=T.table_schema,@tableName=T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key'
or TC.constraint_Type is NULL) and
T.table_name not in ('dtproperties','sysconstraints','syssegments')
and Table_type='BASE TABLE' and T.table_name > @TableName
order by t.table_name

--Delete the table
Exec('DELETE FROM '+ @tableOwner + '.' + @tableName)
--Reset identity column
If @skipident = 'NO'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end
checkpoint
End
/*
Step 3: Delete the data for all Parent tables
*/
set @TableName=''
set @tableOwner=''
While exists
(
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key'
and T.table_name <>'dtproperties'and Table_type='BASE TABLE'
and T.table_name > @TableName
)

Begin
Select top 1 @tableOwner=T.table_schema,@tableName=T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key'
and T.table_name <>'dtproperties'and Table_type='BASE TABLE'
and T.table_name > @TableName
order by t.table_name
--Delete the table
Exec('DELETE FROM '+ @tableOwner + '.' + @tableName)
--Reset identity column
If @skipident = 'NO'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end
checkpoint
End
/*
Step 4: Enable all constraints
*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
Set NoCount Off

asp:ImageButton Issues With Form Submission on Enter Key Press

Background:
I had a webform that contained two text fields, an asp:ImageButton and an asp:Button. The ImageButton and Button each had their own event handlers doing very different things. The OnClick event handler for the ImageButton performed an action specific to the Textbox to the left of it, and was only intended to fire when the user clicked the button. The Button control performed an action specific to both Textboxes, and was intended to fire either when clicked OR upon depressing the enter key.

Problem:
The form was always posting-back when the enter key was depressed -- even in the case where the user entered text into the Textbox belonging to the Button control.

Solution:
The first thing that came to mind was to set a property on the ImageButton to prevent submission just as the
UseSubmitBehavior="false" works for asp:Button... you would think! But, nah... this is not a property of asp:Imagebutton. I tried implementing Javascript to listen for a keyclick and disable the submission by the Imagebutton when enter (13) was clicked, but no such luck. When such things happen, who you gonna call... Google!

After doing some searching around, I decided to replace with the ImageButton with a regular asp:Button control and set UseSubmitBehavior="false". But, now this looked like regular button and needed to resemble the previous ImageButton implementation that had a magifying glass image. The problem is that asp:Button does not have a property to assign an image... that's why asp:ImageButton was created!

To get around this, I assigned an inline style (could have also been done in CSS) setting a background:url to the image and setting the width/height to the size of the image. Since the location of the image file is in a different directory than the WebForm, and the root of the URL may change, I did this in the Page_Load of the code-behind by adding it as an attribute to the asp:Button control as follows:

btnGoTo.Attributes.Add("style", "position: relative; background:url(" + Request.ApplicationPath + "/images/expandview.gif) no-repeat; border:none 0px; width:24px; height:24px; vertical-align:top; cursor:pointer;");

This did the trick!

Setting Focus in ASP.NET User Control

When using an ASP.NET user control, there is no tag to set a Javascript event handler for calling a function to set focus to a control. One solution I found was to write a public utility method that accepts a control as an argument, and dynamically builds the Javascript function to set focus to that specific control.

1. Create a public utility class in App_Code containing a public method named "SetFocus":

using System;
using System.Data;
using System.Configuration;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public class PageUtils
{
public static void SetFocus(Control control)
{
StringBuilder sb = new StringBuilder();
sb.Append("\r\n
");

control.Page.RegisterClientScriptBlock("SetFocus", sb.ToString());
}
}

2. Call the above method in the code-behind of the user control's page loading/rendering method passing the control that requires initial focus:

PageUtils.SetFocus(this.txtSearchField);

NowhereLand Filming in LoDo

Just outside my office at 1860 Blake Street, the film crew for the upcoming Eddie Murphy movie, "NeverLand", a prop Mercades was being configured with camera equipment.

A full article about the filming of NowhereLand can be found online:

http://www.denverpost.com/search/ci_7033903

Populating a Password Textbox at Runtime

Having an with a password property (TextMode="Password"), you cannot simply set the textbox's .Text attribute at runtime to populate the control with a value. Rather, you have to do the following:


this.txtPassword.Attributes["value"] = {whatever value from variable or DB}

Many thanks to:
http://aspadvice.com/blogs/joteke/archive/2005/02/03/_4000_ASP.NET-Forums_3A00_-How-to-set-text-to-a-Password-TextBox_3F00_.aspx

Converting Enum instance to string for evaluation

I came across a situation where I needed to get a Querystring value and evaluate it against a Enum used for display modes.

// Display Mode Enum
public enum Mode
{
View,
Add,
Edit
}

// Evalutate Querystring
object oDisplayMode = Request.QueryString["mode"];
if (oDisplayMode != null)
{
displayMode = (Mode)Enum.Parse(typeof(Mode), oDisplayMode.ToString(), true);

// Put into ViewState for next postback
ViewState["DisplayMode"] = displayMode;
}

Get names of all columns for a table in SQL Server

SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('table_name')

Soda Pop Relics

I was headed to REI after work, but managed to miss my exit so I had to go north on I-25. The 58th Ave. Southbound exit was closed due to a semi roll-over (would have been an intersting picture).

In any case, I had to detour back toward Downtown via Washington St. Around 5oth Ave. I came across an old restaurant/bar having a double-sided Pepsi/Dr. Pepper sign. There are not too many of these around now, so I decided to capture the sign.

Enjoy a cold one!