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(" ", "+"));
   }