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!