Use Table Valued Parameters instead of IN clause (SQL Server Stack Overflow Issue)

Every SQL batch has to fit in the Batch Size Limit: 65,536 * Network Packet Size. Your query is also limited by run time conditions. It will cause stack overflow because x IN (a,b,c) is nothing but x=a OR x=b OR x=c which creates an expression tree similar to x=a OR (x=b OR (x=c)), so it will become too deep for a large number of OR conditions. SQL 7 would hit a Stack Overflow at about 10k values in the IN, but now a days stacks are much deeper (because of x64), so it can go pretty deep. Still if the IN clause is getting generated dynamically prefer Table Valued Parameters or use XML data type.

Cleared SQL Server 2008 MCITP

Cleared SQL Server 2008 MCITP

IncludeTotalCount Silverlight RIA Service (Server Side Paging)

If you are using Server Side Paging (may be at database or service end) and returning limited set of results you may find it difficult to get the TotalItemCount of your entities.

But the answer is simple enough:

//Enable IncludeTotalCount to true in View/ViewModel before calling the data context to load data

EntityQuery query = _assessmentContext.XQuery(1, this._pageIndex, this._pageSize);
query.IncludeTotalCount = true; // Specify that service need to collect the total item count somehow

//Load your Entity asynchronously here

// Enable your service to set an out parameter for total item count:

[Query]

public IQueryable Xs(int xID, int pageIndex, int pageSize, out int totalCount)

{

        totalCount = 110;
}

That's it you are done you can now get the total item count in the load operation result:

private void XsLoadingCompleted(LoadOperation lo)
        {
            this.BusyContent = ApplicationStrings.LoadingXs;

            if (!lo.HasError)
            {
                this.XItemCount = lo.TotalEntityCount; // total item count that you set through the out parameter //in your domain service.

Cygwin - Pass commands (parameters) at startup

There is a lot of stuff you will find regarding Cygwin but I was willing to pass some commands to Cygwin from Hudson CI server at start up but no body is telling it in simple way. So lets dive into it:
Step 1. Go to Cygwin installation directory
Step 2. Locate Cygwin.bat and right click and select edit (backup before editing :))
Step 3.
Initial Structure:
------------------------
@echo off
C:
chdir C:\cygwin\bin
bash --login -i
------------------------

Add your command as:
bash --login -i -c 'your commands here'
-- To pass more than one command seperate them using ; (semi)
example:
bash --login -i -c 'cd $X;cd $Y;clear;'

You are done :) Oops save the file
Start your Cygwin shell it will pass the commands as expected


If you want to pass some parameters from outside (e.g. Hudson or command prompt) use %1, %2, %3
e.g. from hudson: cygwin 'shashank'
bash --login -i -c 'print %1'

print's shashank in the cygwin shell. Use Hudson Console output window to view.

Java! What the Hell (No Support for Unsigned) (UTF-8 Encoding Bug)

Recently I got some work again in Java and it's around 5 years back when I last developed any application in Java. A lot of changes are here but I got a lot of issues with Java after coming from C#. In two worlds 'Java Sucks' if you are coming from C#. This was not the thought for me when I switched from Java to C#.

Why?

1. I required to deal with a lot with the binary data that has unsigned bytes but woh ho Java doesn't support unsigned int, unsigned byte. On asking one of my Java colleague (he loves java and hates C# as most of the people from Java background do) he said are you programming for some ancient age. Now no body uses unsigned things. What the solution this is? However wrote a tiny library to solve it. If you require it just drop me a mail I can send it.

2. Java Encoding Issue (UTF-8 with BOM): Java has a bug in the bug database from Sep-2001 that has been closed with no-fix and the reasoning behind it is:

http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4508058

"
The assumption we made to implement this RFE is that the change would not
break existing real world application, this assumption is obvious
not true, see #6378911. We decided to back out the change we've made
and closed this RFE as "will not fix", for compatibility reason.
Posted Date : 2006-02-18 02:33:09.0
"
What are you talking about?
I got some C# files that were getting laxically analyzed and parsed using ANTLR 3.2 in a Linux system using Java. And what I got an out of memory exception (heap out of space). What the hell I did. I am loading a 4kb file and its going out of memory. It was the encoding issue. However I managed to get the resolution here:
http://koti.mbnet.fi/akini/java/unicodereader/

Now I think C# is much more richer and friendly language than Java.

Please don't mind it and take personally if you love Java and I am not here to remark on any language capabilities but its my personal choice to work with C#.

Merge Statement SQL Server 2008

MERGE statement is introduced in SQL Server 2008 that can greatly enhance the functionality for Data Warehouse and the staging servers.

The Merge statement allows you to perform DML actions on target table based on whether
or not a row matches information found in source table.

A common Merge scenario is moving data from one table to another.

Like User and UserHistory

User Table :

CREATE TABLE [dbo].[User]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Is_Active] [bit] NULL,
[CREATE_DATE] [date] NOT NULL
)

GO

ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_CREATE_DATE] DEFAULT (getdate()) FOR [CREATE_DATE]

GO

AND
UserHistory Table is like:

CREATE TABLE UserHistory
(
HistoryId INT IDENTITY(1, 1),
Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Is_Active BIT NULL,
Create_Date DATE NOT NULL
)

------------------------------------------------------------------------------------

Now insert some data in user table and try running this query more than once and look
on the results that are produced by OUTPUT and $action clauses:

MERGE INTO UserHistory AS UH
USING [User] as U
ON U.Id = UH.Id
WHEN MATCHED THEN
UPDATE SET UH.Id = U.Id, UH.Name = U.Name
,UH.Is_Active = U.Is_Active, UH.Create_date = U.Create_date
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Name, Age, Is_Active, Create_Date)
VALUES (Id, Name, Age, Is_Active, Create_Date)
WHEN NOT MATCHED BY SOURCE THEN
Update SET IS_ACTIVE = 0
Output $action, inserted.*;

-------------------------------------------------------------------------------------
You can perform insert, update and delete operation by matching the Source Table (User)
and the Target Table (UserHistory) in a single operation and can have the details about the
action from OUTPUT clause and using $action you can know the operation performed

SQL Server 2008:Output Clause Sample

So with SQL Server 2008 we have output clause available inside the query that means we can access the inserted, deleted or updated value form the output clause inside a Stored Procedure and we need not to write a trigger

Ex.
Suppose we have a user table on inserting the data we need to insert the same value to a table type variable, we can accomplish this task as given below:

DECLARE @User TABLE (ID INT, Name VARCHAR(50), Age INT, Is_Active BIT, CREATE_DATE DATETIME)
INSERT INTO [User] (Name, Age, Is_Active, CREATE_DATE)
OUTPUT inserted.* INTO @User VALUES ('TESTING', '26', 1, GETDATE())

SELECT * FROM @User

Create class variables and properties from database table

Use the below given query to create variables and properties from your schema.

Just replace the @tableName variable with the table name for which you want to generate the variables and properties.

This makes use of systypes and syscolumns system defined views to get the name of columns from the table and their type. Also add more types as per your requirement.

-----------------------------------------------------------------------------

declare @tableName varchar(50)

set @tableName = 'tbTest'

SELECT 'private ' + (

case systypes.name

when 'varchar' then 'string'

when 'decimal' then 'decimal'

when 'datetime' then 'DateTime'

when 'int' then 'int'

else systypes.name end) + ' _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + ';'

FROM syscolumns inner join systypes on

syscolumns.xtype = systypes.xtype

where id = Object_ID(@tableName)

order by colorder

SELECT 'public ' + (

case systypes.name

when 'varchar' then 'string'

when 'decimal' then 'decimal'

when 'datetime' then 'DateTime'

when 'int' then 'int'

else systypes.name end)+ ' ' + syscolumns.name + char(10) +

'{' + char(10) + 'get { return' +

' _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + '; }'

+ char(10) + 'set { _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + ' = value; }' + char(10) + '}'

FROM syscolumns inner join systypes on

syscolumns.xtype = systypes.xtype

where id = Object_ID(@tableName)

order by colorder

-----------------------------------------------------------------------------

SQL Server 2008 Features

What's New in Sql Server 2008

Power of Reflection Emit

Some time ago I came across a situation where the end user of my application was willing to add new types at his own. Obviously I was not having any other solution rather using Reflection Emit.

It was great creating new types, dynamic methods at run time.

So as I did earlier similarly I will produce some code (to create new type (compiled class) with some properties and methods) out here with a little description.

But before working with Emit you should look on the MSIL Disassembler to know how the things looks alike after compilation and before JIT compiler processing.

------------------------------ Creating A Whole New Type ------------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;
using DataFramework;
using System.Data;

namespace Generator
{
public class ClassGenerator
{
public ClassGenerator()
{

}

public void GenerateClass(string assemblyName, string className, List fieldWithType)
{
AssemblyName an = new AssemblyName(assemblyName);
AppDomain ad = AppDomain.CurrentDomain; // Use same app domain otherwise to use
// the generated classes you need to use
// remoting like - object handle unwrap
AssemblyBuilder ab = ad.DefineDynamicAssembly(an, AssemblyBuilderAccess.RunAndSave, AppDomain.CurrentDomain.BaseDirectory);

ModuleBuilder mb = ab.DefineDynamicModule(an.Name, "CustomClasses.dll");

TypeBuilder tb = mb.DefineType(className, TypeAttributes.Class | TypeAttributes.Public);

ConstructorInfo ci = tb.DefineDefaultConstructor(MethodAttributes.Public);

foreach (FieldStructure fieldName in fieldWithType)
{
Type type = GetType(fieldName.type);// Type.GetType(fieldWithType[fieldName], false, true);

FieldBuilder fb = tb.DefineField(fieldName.name, type, FieldAttributes.Private);

CreateProperty(tb, type, fb, fieldName.name);
}



Type generatedType = tb.CreateType();
ab.Save("CustomClasses." + className + ".dll");
}

#region Create Property for field
private void CreateProperty(TypeBuilder tb, Type fieldType, FieldBuilder fb, string key)
{
PropertyBuilder pb = tb.DefineProperty(SystemConstants.GetPropertyName(key), System.Reflection.PropertyAttributes.HasDefault, fieldType, Type.EmptyTypes);

MethodAttributes getSetAttr = MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig;

MethodBuilder mbGet = tb.DefineMethod(SystemConstants.getPrefix + key, getSetAttr, fieldType, Type.EmptyTypes);

ILGenerator getIL = mbGet.GetILGenerator();
// For an instance property, argument zero is the instance. Load the
// instance, then load the private field and return, leaving the
// field value on the stack.
getIL.Emit(OpCodes.Ldarg_0);
getIL.Emit(OpCodes.Ldfld, fb);
getIL.Emit(OpCodes.Ret);

MethodBuilder mbSet = tb.DefineMethod(SystemConstants.setPrefix + key, getSetAttr, null, new Type[] { fieldType });

ILGenerator setIL = mbSet.GetILGenerator();
// Load the instance and then the numeric argument, then store the
// argument in the field.
setIL.Emit(OpCodes.Ldarg_0);
setIL.Emit(OpCodes.Ldarg_1);
setIL.Emit(OpCodes.Stfld, fb);
setIL.Emit(OpCodes.Ret);

// Last, map the "get" and "set" accessor methods to the
// PropertyBuilder. The property is now complete.
pb.SetGetMethod(mbGet);
pb.SetSetMethod(mbSet);
}
#endregion

#region Get type based on string representation
private Type GetType(string typeName)
{
switch (typeName.ToLower())
{
case "string":
return typeof(String);
case "int32":
return typeof(Int32);
case "long":
return typeof(long);
case "float":
return typeof(float);
case "decimal":
return typeof(decimal);
default:
return typeof(object);
}
}
#endregion
}
}



----------------------- Helper Classes (SystemConstants)-----------------------------------
using System;
using System.Collections.Generic;
using System.Text;

namespace Generator
{
public class SystemConstants
{
public const string getPrefix = "get_";
public const string setPrefix = "set_";
public const string variablePrefix = "m_";
public const string idVariableForClasses = "ID";
public const string idVariableType = "int32";

private static bool IsFieldDicContainsId(Dictionary fieldWithType)
{
foreach (string fieldName in fieldWithType.Keys)
{
if (fieldName.ToUpper() == idVariableForClasses)
{
return true;
}
}
return false;
}

public static Dictionary GetFinalDictionary(Dictionary fieldWithType)
{
if (IsFieldDicContainsId(fieldWithType))
{
if (fieldWithType.ContainsKey(idVariableForClasses))
{
fieldWithType.Remove(idVariableForClasses);
}
}

return fieldWithType;
}

internal static string GetPropertyName(string key)
{
// Implement Logic to create property like m_name ==> Name right now its NAME
return key.ToUpper();
}
}
}

----------------------- Helper Structure (FieldStructure)-----------------------------------

using System;
using System.Collections.Generic;
using System.Text;

namespace Generator
{
public struct FieldStructure
{
public string name;
public string type;
public bool isIdentity;
public bool isReference;
public string referenceClassName;
public string referenceColumnName;

}
}



--------------------------------- Code to be called from UI ------------------------------------

ClassGenerator cg = new ClassGenerator();

FieldStructure fStructA = new FieldStructure();
fStructA.name = "name";
fStructA.type = "string";
fStructA.isIdentity = false;

FieldStructure fStructB = new FieldStructure();
fStructB.name = "id";
fStructB.type = "int32";
fStructB.isIdentity = true;

List allFields = new List();
allFields.Add(fStructA);
allFields.Add(fStructB);

cg.GenerateClass("test", "Test", allFields);
}


Thats it. Try to use it, a .dll file will be saved in your application root directory, but beware if you are using web application and you intended to save any .dll file in the bin folder the application domain will reload and all your sessions will be lost as one of the application domain reloading is any change in the bin directory is.

For more reading you can look at this msdn link.

Let me know for your queries.
Shashank.... enjoy coding ....