Keeping up with Microsoft.

Site Web Analytics not updating Sharepoint 2010

2011-02-16T13:33:00.001-05:00

If you facing the issue that the web Analytics Reports in Sharepoint 2010 Central Administration is not updating data.

When you go to your site > site settings > Site Web Analytics reports or Site Collection Analytics reports
You get old data as in the ribbon displayed "Data Last Updated: 12/13/2010 2:00:20 AM"

Please insure that the following things are covered:
Insure that Usage and Data Health Data Collection service is configured correctly.
Log Collection Schedule is configured correctly
Microsoft Sharepoint Foundation Usage Data Import and Microsoft SharePoint Foundation Usage Data Processing Timer jobs are configured to run at regular intervals
One last important Timer job is the Web Analytics Trigger Workflows Timer Job insure that this timer job is enabled and scheduled to run at regular intervals (for each site that you need analytics for).
After you have insured that the web analytics service configuration is working fine and the Usage Data Import job is importing the *.usage files from the ULS LOGS folder into the WSS_Logging database, and that all the required timer jobs are running as expected… wait for a day for the report to get updated… the report gets updated automatically at 2:00 am in the morning… and i could not find a way to control the schedule for this report update job.

So be sure to wait for a day before giving up :)

Change AccountName/LoginName for a SharePoint User (SPUser)

2011-02-04T17:52:00.001-05:00

Consider the following:
We have an account named MYDOMAIN\eholz. This accounts Active Directory Login Name changes to MYDOMAIN\eburrell
Now this user was a active user in a Sharepoint 2010 team Site, and had a userProfile using the Account name MYDOMAIN\eholz.
Since the AD LoginName changed to eburrell hence we need to update the Sharepoint User (SPUser object) as well update the userprofile to reflect the new account name.
To update the Sharepoint User LoginName we can run the following stsadm command on the Server:

STSADM –o migrateuser –oldlogin MYDOMAIN\eholz –newlogin MYDOMAIN\eburrell –ignoresidhistory

However to update the Sharepoint 2010 UserProfile, i first tried running a Incremental/Full Synchronization using the User Profile Synchronization service… this did not work. To enable me to update the AccountName field (which is a read only field) of the UserProfile, I had to first delete the User Profile for MYDOMAIN\eholz and then run a FULL Synchronization using the User Profile Synchronization service which synchronizes the Sharepoint User Profiles with the AD profiles.

Update: if you just run the STSADM –o migrateuser command… the profile also gets updated automatically. so all you need is to run the stsadm –o migrate user command and you dont need to delete and recreate the User Profile

Search All Columns in All Tables using TSQL

2010-06-18T12:51:00.001-04:00

DECLARE @SearchStr nvarchar(100)

SET @SearchStr = 'SEARCH_KEYWORD'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
	SET @ColumnName = ''
	SET @TableName = 
	(
		SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
		FROM 	INFORMATION_SCHEMA.TABLES
		WHERE 		TABLE_TYPE = 'BASE TABLE'
			AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
			AND	OBJECTPROPERTY(
					OBJECT_ID(
						QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
						 ), 'IsMSShipped'
					       ) = 0
	)

	WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
	BEGIN
		SET @ColumnName =
		(
			SELECT MIN(QUOTENAME(COLUMN_NAME))
			FROM 	INFORMATION_SCHEMA.COLUMNS
			WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
				AND	TABLE_NAME	= PARSENAME(@TableName, 1)
				AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
				AND	QUOTENAME(COLUMN_NAME) > @ColumnName
		)

		IF @ColumnName IS NOT NULL
		BEGIN
			INSERT INTO #Results
			EXEC
			(
				'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
				FROM ' + @TableName + ' (NOLOCK) ' +
				' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
			)
		END
	END	
END

SELECT ColumnName, ColumnValue FROM #Results

Unit testing internal methods in a strongly named assembly/project

2010-06-07T18:54:00.001-04:00

If you need create Unit tests for internal methods within a assembly in Visual Studio 2005 or greater, then we need to add an entry in the AssemblyInfo.cs file of the assembly for which you are creating the units tests for. For e.g. if you need to create tests for a assembly named FincadFunctions.dll & this assembly contains internal/friend methods within which need to write unit tests for then we add a entry in the FincadFunctions.dll’s AssemblyInfo.cs file like so :

[assembly: System.Runtime.CompilerServices.InternalsVisibleTo("FincadFunctionsTests")]

where FincadFunctionsTests is the name of the Unit Test project which contains the Unit Tests. However if the fincadFunctions.dll is a strongly named assembly then you will the following error when compiling the FincadFunctions.dll assembly :

Friend assembly reference “FincadFunctionsTests” is invalid. Strong-name assemblies must specify a public key in their InternalsVisibleTo declarations.

Thus to add a public key token to InternalsVisibleTo Declarations do the following: You need the .snk file that was used to strong-name the FincadFunctions.dll assembly. You can extract the public key from this .snk with the sn.exe tool from the .NET SDK. First we extract just the public key from the key pair (.snk) file into another .snk file. sn -p test.snk test.pub Then we ask for the value of that public key (note we need the long hex key not the short public key token): sn -tp test.pub We end up getting a super LONG string of hex, but that's just what we want, the public key value of this key pair. We add it to the strongly named project "FincadFunctions.dll" that we want to expose our internals from. Before what looked like:

[assembly: System.Runtime.CompilerServices.InternalsVisibleTo("FincadFunctionsTests")]

Now looks like.

[assembly: System.Runtime.CompilerServices.InternalsVisibleTo("FincadFunctionsTests, PublicKey=002400000480000094000000060200000024000052534131000400000100010011fdf2e48bb")]

And we're done. hope this helps

LinqToXML removing empty xmlns attributes

2010-05-21T19:40:00.001-04:00

Suppose you need to generate the following XML:

<GenevaLoader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.advent.com/SchemaRevLevel401/Geneva masterschema.xsd" xmlns="http://www.advent.com/SchemaRevLevel401/Geneva">
  <PriceRecords>
    <PriceRecord>
    </PriceRecord>
  </PriceRecords>
</GenevaLoader>

Normally you would write the following C# code to accomplish this:

const string ns = "http://www.advent.com/SchemaRevLevel401/Geneva";
XNamespace xnsp = ns;
XNamespace xsi = XNamespace.Get("http://www.w3.org/2001/XMLSchema-instance");

XElement root = new XElement( xnsp + "GenevaLoader",  
  new XAttribute(XNamespace.Xmlns + "xsi", xsi.NamespaceName), 
  new XAttribute( xsi + "schemaLocation", "http://www.advent.com/SchemaRevLevel401/Geneva masterschema.xsd"));

XElement priceRecords = new XElement("PriceRecords");
root.Add(priceRecords);

	for(int i = 0; i < 3; i++)
	{
	    XElement price = new XElement("PriceRecord");
	   priceRecords.Add(price);
	}

	doc.Save("geneva.xml");

The problem with this approach is that it adds a additional empty xmlns arrtribute on the “PriceRecords” element, like so :

<GenevaLoader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.advent.com/SchemaRevLevel401/Geneva masterschema.xsd" xmlns="http://www.advent.com/SchemaRevLevel401/Geneva">
  <PriceRecords xmlns="">
    <PriceRecord>
    </PriceRecord>
  </PriceRecords>
</GenevaLoader>

The solution is to add the xmlns NameSpace in code to each child and grandchild elements of the root element like so :

XElement priceRecords = new XElement( xnsp + "PriceRecords");
root.Add(priceRecords);

for(int i = 0; i < 3; i++)
{
    XElement price = new XElement(xnsp + "PriceRecord");
   priceRecords.Add(price);
}

Copying files from GAC using xcopy or Windows Explorer

2010-05-21T18:46:00.001-04:00

use this command for copying files using a wildcard from the GAC to a local folder.
xcopy c:\windows\assembly\Microsoft.SqlServer.Smo*.dll c:\gacdll /s/r/y/c

The above command will continue even it encounters any “Access Denied” errors, thus copying over the required files.

To copy files using the Windows explorer just disable the GAC Cache Viewer by adding a entry to the registry:
Browse to “HKEY_LOCALMACHINE\Software\Microsoft\Fusion”
Add a Dword called DisableCacheViewer. Set the value of it to 1.

Entity Framework v1 – tips and Tricks Part 3

2010-05-11T18:50:00.001-04:00

The previous posts on Entity framework are available here :
Entity Framework version 1- Brief Synopsis and Tips – Part 1
Entity Framework v1 … Brief Synopsis and Tips – Part 2

General Tips on Entity Framework v1 & Linq to Entities:

ToTraceString()

If you need to know the underlying SQL that the EF generates for a Linq To Entities query, then use the ToTraceString() method of the ObjectQuery class. (or use LINQPAD)
Note that you need to cast the LINQToEntities query to ObjectQuery before calling TotraceString() as follows:

string efSQL = ((ObjectQuery)from c in ctx.Contact
         where c.Address.Any(a => a.CountryRegion == "US")
         select c.ContactID).ToTraceString();

======================================================================

MARS or MultipleActiveResultSet
When you create a EDM Model (EDMX file) from the database using Visual Studio, it generates a connection string with the same name as the name of the EntityContainer in CSDL. In the ConnectionString so generated it sets the MultipleActiveResultSet attribute to true by default. So if you are running the following query then it streams multiple readers over the same connection:

using (BAEntities context = new BAEntities())
{
  var cons =
      from con in context.Contacts
      where con.FirstName == "Jose"
      select con;
  foreach (var c in cons)
  {
    if (c.AddDate < new System.DateTime(2007, 1, 1))
    {
      c.Addresses.Load();
    }
  }
}

======================================================================
Explicitly opening and closing EntityConnection

When you call ToList() or foreach on a LINQToEntities query the EF automatically closes the connection after all the records from the query have been consumed.

Thus if you need to run many LINQToEntities queries over the same connection then explicitly open and close the connection as follows:

using (BAEntities context = new BAEntities())
{
  context.Connection.Open();
  var cons = from con in context.Contacts where con.FirstName == "Jose"
             select con;
  var conList = cons.ToList();
  var allCustomers =  from con in context.Contacts.OfType<Customer>()
                      select con;
  var allcustList = allCustomers.ToList();
  context.Connection.Close();
}

======================================================================

Dispose ObjectContext only if required

After you retrieve entities using the ObjectContext and you are not explicitly disposing the ObjectContext then insure that your code does consume all the records from the LinqToEntities query by calling .ToList() or foreach statement, otherwise the the database connection will remain open and will be closed by the garbage collector when it gets to dispose the ObjectContext.

Secondly if you are making updates to the entities retrieved using LinqToEntities then insure that you dont inadverdently dispose of the ObjectContext after the entities are retrieved and before calling .SaveChanges() since you need the SAME ObjectContext to keep track of changes made to the Entities (by using ObjectStateEntry objects). So if you do need to explicitly dispose of the ObjectContext do so only after calling SaveChanges() and only if you dont need to change track the entities retrieved any further.

======================================================================

SQL InjectionAttacks under control with EFv1

LinqToEntities and LinqToSQL queries are parameterized before they are sent to the DB hence they are not vulnerable to SQL Injection attacks.

EntitySQL may be slightly vulnerable to attacks since it does not use parameterized queries. However since the EntitySQL demands that the query be valid Entity SQL syntax and valid native SQL syntax at the same time.

So the only way one can do a SQLInjection Attack is by knowing the SSDL of the EDM Model and be able to write the correct EntitySQL (note one cannot append regular SQL since then the query wont be a valid EntitySQL syntax) and append it to a parameter.

======================================================================
Improving Performance
You can convert the EntitySets and AssociationSets in a EDM Model into precompiled Views using the edmgen utility. for e.g. the Customer Entity can be converted into a precompiled view using edmgen and all LinqToEntities query against the contaxt.Customer EntitySet will use the precompiled View instead of the EntitySet itself (the same being true for relationships (EntityReference & EntityCollections of a Entity)). The advantage being that when using precompiled views the performance will be much better.

The syntax for generating precompiled views for a existing EF project is :
edmgen /mode:ViewGeneration /inssdl:BAModel.ssdl /incsdl:BAModel.csdl /inmsl:BAModel.msl /p:Chap14.csproj
Note that this will only generate precompiled views for EntitySets and Associations and not for existing LinqToEntities queries in the project.(for that use CompiledQuery.Compile<>)
Secondly if you have a LinqToEntities query that you need to run multiple times, then one should precompile the query using CompiledQuery.Compile method. The CompiledQuery.Compile<> method accepts a lamda expression as a parameter, which denotes the LinqToEntities query that you need to precompile.

The following is a example of a lamda that we can pass into the CompiledQuery.Compile() method

Expression<Func<BAEntities, string, IQueryable<Customer>>> expr = (BAEntities ctx1, string loc) =>
                    from c in ctx1.Contacts.OfType<Customer>()
                    where c.Reservations.Any(r => r.Trip.Destination.DestinationName == loc)
                    select c;

Then we call the Compiled Query as follows:

var query = CompiledQuery.Compile<BAEntities, string, IQueryable<Customer>>(expr);

using (BAEntities ctx = new BAEntities())
{
    var loc = "Malta";
    IQueryable<Customer> custs = query.Invoke(ctx, loc);
    var custlist = custs.ToList();
    foreach (var item in custlist)
    {
        Console.WriteLine(item.FullName);
    }
}

Note that if you created a ObjectQuery or a Enitity SQL query instead of the LINQToEntities query, you dont need precompilation for e.g.

An Example of EntitySQL query :
string esql = "SELECT VALUE c from Contacts AS c where c is of(BAGA.Customer) and c.LastName = 'Gupta'";
ObjectQuery<Customer> custs = CreateQuery<Customer>(esql);

An Example of ObjectQuery built using ObjectBuilder methods:
from c in Contacts.OfType<Customer>().Where("it.LastName == 'Gupta'")
select c

This is since the Query plan is cached and thus the performance improves a bit, however since the ObjectQuery or EntitySQL query still needs to materialize the results into Entities hence it will take the same amount of performance hit as with LinqToEntities.

However note that not ALL EntitySQL based or QueryBuilder based ObjectQuery plans are cached. So if you are in doubt always create a LinqToEntities compiled query and use that instead

============================================================
GetObjectStateEntry Versus GetObjectByKey

We can get to the Entity being referenced by the ObjectStateEntry via its Entity property and there are helper methods in the ObjectStateManager (osm.TryGetObjectStateEntry) to get the ObjectStateEntry for a entity (for which we know the EntityKey). Similarly The ObjectContext has helper methods to get an Entity i.e. TryGetObjectByKey().

TryGetObjectByKey() uses GetObjectStateEntry method under the covers to find the object, however One important difference between these 2 methods is that TryGetObjectByKey queries the database if it is unable to find the object in the context, whereas TryGetObjectStateEntry only looks in the context for existing entries. It will not make a trip to the database
=============================================================

POCO objects with EFv1:
To create POCO objects that can be used with EFv1. We need to implement 3 key interfaces:

IEntityWithKey
IEntityWithRelationships
IEntityWithChangeTracker

Implementing IEntityWithKey is not mandatory, but if you dont then we need to explicitly provide values for the EntityKey for various functions (for e.g. the functions needed to implement IEntityWithChangeTracker and IEntityWithRelationships).

Implementation of IEntityWithKey involves exposing a property named EntityKey which returns a EntityKey object.
Implementation of IEntityWithChangeTracker involves implementing a method named SetChangeTracker since there can be multiple changetrackers (Object Contexts) existing in memory at the same time.

public void SetChangeTracker(IEntityChangeTracker changeTracker)
{
  _changeTracker = changeTracker;
}

Additionally each property in the POCO object needs to notify the changetracker (objContext) that it is updating itself by calling the EntityMemberChanged and EntityMemberChanging methods on the changeTracker. for e.g.:

public EntityKey EntityKey
{
  get { return _entityKey; }
  set
  {
    if (_changeTracker != null)
    {
       _changeTracker.EntityMemberChanging("EntityKey");
       _entityKey = value;
       _changeTracker.EntityMemberChanged("EntityKey");
     }
     else
       _entityKey = value;
  }
}
=====================  Custom Property  ====================================

[EdmScalarPropertyAttribute(IsNullable = false)]
public System.DateTime OrderDate
{
  get { return _orderDate; }
  set
  {
    if (_changeTracker != null)
    {
      _changeTracker.EntityMemberChanging("OrderDate");
      _orderDate = value;
      _changeTracker.EntityMemberChanged("OrderDate");
    }
    else
      _orderDate = value;
  }
}

Finally you also need to create the EntityState property as follows:

public EntityState EntityState
{
  get  { return _changeTracker.EntityState;  }
}

The IEntityWithRelationships involves creating a property that returns RelationshipManager object:

public RelationshipManager RelationshipManager
{
  get
  {
    if (_relManager == null)
      _relManager = RelationshipManager.Create(this);
    return _relManager;
  }
}

============================================================

Tip : ProviderManifestToken – change EDMX File to use SQL 2008 instead of SQL 2005

To use with SQL Server 2008, edit the EDMX file (the raw XML) changing the ProviderManifestToken in the SSDL attributes from "2005" to "2008"
=============================================================
With EFv1 we cannot use Structs to replace a anonymous Type while doing projections in a LINQ to Entities query. While the same is supported with LINQToSQL, it is not with LinqToEntities. For e.g. the following is not supported with LinqToEntities since only parameterless constructors and initializers are supported in LINQ to Entities. (the same works with LINQToSQL)

public struct CompanyInfo
{
    public int ID { get; set; }
    public string Name { get; set; }
}
var companies = (from c in dc.Companies
            where c.CompanyIcon == null
             select new CompanyInfo { Name = c.CompanyName, ID = c.CompanyId }).ToList(); ;

Split SQL 2005 column values into multiple rows

2009-12-14T16:57:00.002-05:00

Suppose that you have a column that stored multiple values in a single row using some delimiter as ‘,’. Now you are tasked with splitting these values into a separate row for each value and you need to do this on multiple rows in the table.

   1: select distinct r.items

   2: from Order o

   3: cross apply dbo.split(o.Products,',') r

Here the Order Table has a Products column that has multiple Products stored in a single column separated with commas like ‘Tea,Coffee’ associated with a single Order. What the above query does is it gives a distinct list of names of all Products stored in all rows in the Order table. The Split() is a UDF which splits the string into one row for each value

Similarly if you need to check if these multiple values stored in the Products column contain a particular string from a list of values, i,e,

   1: select o.* from Company c

   2: join Order o on c.ItemID = o.companyItemID

   3: where (select * from dbo.split(o.Products) in ('TBD','Tea', 'Coffee'))

Since the above would not compile hence the right solution to do something like this would be:

   1: select o.* from Company c

   2: join Order o on c.ItemID = o.companyItemID

   3: cross apply dbo.Split(Products,',') p

   4: where p.Items in ('TBD','Tea','Coffee')

what we are doing here is a inner join on the Split() table valued UDF which returns a table containing one row for each value split and then comparing each Item to see if it matches one of the product names i.e. Tea or Coffee.

Dispose and Finalize methods and .NET Memory optimizations

2009-03-06T17:32:00.001-05:00

I have jotted down a few points on implementing the IDisposable and Finalize patterns in .NET.

Please find it here : Dispose and Finalize methods

I also jotted down some .net Memory Optimization tips: Please find them here : Some Tips on .NET Memory optimizations

Creating dynamically generated logfiles with log4net

2009-03-03T19:57:00.001-05:00

If you need to generate logfile names dynmically when using log4net then we can do this using the log4net Properties.

To learn more on this please visit : creating dynamic logfile names with log4net

Tips on using log4net RollingFileAppender

2009-03-03T18:55:00.001-05:00

I have compiled a list of useful properties that you can set on the RollingFileAppender in the log4net XML config file which can determine the order in which log file backups will be generated, whether they will rollover based on dates or size or both.

For more information please check this link out : Tips on using log4net RollingFileAppender

ConfigSectionHandler for Hierarchical configs

2009-01-28T20:06:00.002-05:00

Here is a quick way to create a ConfigSection Handler for reading Hierarchical configs. For e.g. suppose you need to read a config file which has the following structure:


MySpace
 
  23
   Rohit
  Gupta

For this you would create the 2 classes, one for the Parent Config and another for the chold config like this:


  [XmlRoot("MainConfig")]
   public class MainConfig
   {
       private static readonly MainConfig instance =
 (MainConfig)ConfigurationManager.GetSection("MainConfig");
       public static MainConfig GetInstance()
       {
           if (instance == null)
               throw new ConfigurationErrorsException(
                   "Unable to locate or deserialize the 'MainConfig' section.");

           return instance;
       }

       public string Company { get; set; }

       public SubConfig FriendsConfig { get; set; }
   }

   [XmlRoot("SubConfig")]
   public class SubConfig
   {
       public int ID { get; set; }
       public string Name { get; set; }
       public string LastName { get; set; }
   }

Then you would create a configsectionHandler class which reads the config from the .config file:



public class MainConfigSectionHandler : IConfigurationSectionHandler
   {
       #region IConfigurationSectionHandler Members

       public object Create(object parent, object configContext, System.Xml.XmlNode section)
       {
           MainConfig typedConfig = GetConfig(section);

           if (typedConfig != null)
           {
               #region Get Sub Configs
               foreach (XmlNode node in section.ChildNodes)
               {
                   switch (node.Name)
                   {
                       case "SubConfig":
                           SubConfig friendsConfig = GetConfig(node);
                           typedConfig.FriendsConfig = friendsConfig;
                           break;
                       default:
                           break;
                   }
               }
               #endregion
           }


           return typedConfig;
       }

       public T GetConfig(System.Xml.XmlNode section) where T : class
       {
           T sourcedObject = default(T);
           Type t = typeof(T);
           XmlSerializer ser = new XmlSerializer(typeof(T));
           sourcedObject = ser.Deserialize(new XmlNodeReader(section)) as T;
           return sourcedObject;
       }

       #endregion

   }

After this you would add a entry in the app.config for this configsection Handler as the following:

Finally to read this config, you would write the following:


MainConfig config = MainConfig.GetInstance(); Console.WriteLine(config.Company);
Console.WriteLine(config.FriendsConfig.ID);
Console.WriteLine(config.FriendsConfig.LastName);
Console.WriteLine(config.FriendsConfig.Name);

Lucene: Multifield searches

2009-01-07T21:45:00.004-05:00

We can run multifield searches in Lucene using either the BooleanQuery API or using the MultiFieldQueryParser for parsing the query text. For e.g. If a index has 2 fields FirstName and LastName and if you need to search for "John" in the FirstName field and "Travis" in the LastName field one can use a Boolean Query as such:


BooleanQuery bq = new BooleanQuery();
Query qf = new TermQuery(new Lucene.Net.Index.Term("FirstName", "John"));
Query ql = new TermQuery(new Lucene.Net.Index.Term("LastName", "Travis"));
bq.Add(qf, BooleanClause.Occur.MUST);
bq.Add(ql, BooleanClause.Occur.MUST);
IndexSearcher srchr = new IndexSearcher(@"C:\indexDir");
srchr.Search(bq);

Now if we need to search a single term across either of the FirstName and LastName fields then we can use the MultiFieldQueryParser as follows:


Query query = MultiFieldQueryParser.parse("commonName",
new String[] { "FirstName", "LastName" },
new SimpleAnalyzer());
srchr.Search(query);

Now if you need to search the term that must exist in both the fields then we use the following:


 Query query = MultiFieldQueryParser.Parse("commonName",
new String[] { "FirstName", "LastName" },
new BooleanClause.Occur[] { BooleanClause.Occur.MUST,BooleanClause.Occur.MUST}
, new SimpleAnalyzer());
srchr.Search(query);

Finally if you don’t want a term to occur in one of the Fields (say FirstName) then use:


 Query query = MultiFieldQueryParser.Parse("commonName",
new String[] { "FirstName", "LastName" },
new BooleanClause.Occur[] { BooleanClause.Occur.MUST_NOT,BooleanClause.Occur.MUST},
new SimpleAnalyzer());
srchr.Search(query);

so if you need to search a single term across multiple fields then use MultiFieldQueryParser, if you need to search different terms in different fields then use the BooleanQuery as shown first

Dynamically creating types using reflection and setting properties using Reflection.Emit.

2008-12-01T12:59:00.001-05:00

I came across a requirement where we needed to create types dynamically based on XML Configuration files, so that in the furture new types are required we dont need to update the application again by creating a new class.
The additional requirement was to populate the property names of the class based on the Cml configuration and its values using the Querystring values from the HttpWebRequest.
I earlier thought about using Dynamic methods from .NET Framework 2.0, but that did not fit my purpose since I didn't need to execute methods from the dynamic type that was created.

So here is the code:
I created three helper methods
1. Generates the AssemblyBuilder and Module Builder objects
2. Generates the TypeBuilder object.. which will be used for generating an instance of the dynamic type using Activator.CreateInstance
3. Create properties using Relection.Emit

   1: public class DynamicType

   2: {

3:

   4:     public static AssemblyBuilder asmBuilder = null;

   5:     public static ModuleBuilder modBuilder = null;

   6:     public static void GenerateAssemblyAndModule()

   7:     {

   8:         if (asmBuilder == null)

   9:         {

  10:             AssemblyName assemblyName = new AssemblyName();

  11:             assemblyName.Name = "DWBeacons";

  12:             AppDomain thisDomain = Thread.GetDomain();

  13:             asmBuilder = thisDomain.DefineDynamicAssembly(

  14:                          assemblyName, AssemblyBuilderAccess.Run);

  15:             modBuilder = asmBuilder.DefineDynamicModule(

  16:                          asmBuilder.GetName().Name, false);

  17:         }

  18:     }

19:

  20:     public static TypeBuilder CreateType(ModuleBuilder modBuilder, string typeName)

  21:     {

  22:         TypeBuilder typeBuilder = modBuilder.DefineType(typeName,

  23:                     TypeAttributes.Public |

  24:                     TypeAttributes.Class |

  25:                     TypeAttributes.AutoClass |

  26:                     TypeAttributes.AnsiClass |

  27:                     TypeAttributes.BeforeFieldInit |

  28:                     TypeAttributes.AutoLayout,

  29:                     typeof(object));

  30:         return typeBuilder;

  31:     }

32:

33:

  34:     public static void CreateProperty(TypeBuilder t, string name, Type typ)

  35:     {

  36:         string field = "_" + name.ToLower();

  37:         FieldBuilder fieldBldr = t.DefineField(field, typ, FieldAttributes.Private);

  38:         PropertyBuilder propBldr = t.DefineProperty(name, PropertyAttributes.HasDefault, typ, null);

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

40:

  41:         MethodBuilder getPropBldr = t.DefineMethod("get_" + name, getSetAttr, typ, Type.EmptyTypes);

42:

  43:         ILGenerator getIL = getPropBldr.GetILGenerator();

  44:         getIL.Emit(OpCodes.Ldarg_0);

  45:         getIL.Emit(OpCodes.Ldfld, fieldBldr);

  46:         getIL.Emit(OpCodes.Ret);

47:

  48:         MethodBuilder setPropBldr = t.DefineMethod("set_" + name, getSetAttr, null, new Type[] { typ });

49:

  50:         ILGenerator setIL = setPropBldr.GetILGenerator();

51:

  52:         setIL.Emit(OpCodes.Ldarg_0);

  53:         setIL.Emit(OpCodes.Ldarg_1);

  54:         setIL.Emit(OpCodes.Stfld, fieldBldr);

  55:         setIL.Emit(OpCodes.Ret);

56:

  57:         propBldr.SetGetMethod(getPropBldr);

  58:         propBldr.SetSetMethod(setPropBldr);

59:

  60:     }

61:

  62: }

Then to use these helper methods from the Main program I used the following:

   1: if (DynamicType.asmBuilder == null)

   2:     DynamicType.GenerateAssemblyAndModule();

   3: finalType = DynamicType.modBuilder.GetType("Beacon11");

4:

   5: TypeBuilder tb = DynamicType.CreateType(DynamicType.modBuilder, typeName);

6:

   7: foreach (XElement e in beaconNode.Descendants())

   8: {

   9:     string pname = e.Attribute("qs").Value;

  10:     string ptype = e.Attribute("type").Value;

  11:     DynamicType.CreateProperty(tb, pname, Type.GetType(ptype));

  12: }

  13: finalType = tb.CreateType();

14:

  15: Object obj = Activator.CreateInstance(finalType);

  16: // this sets the properties of the just instantiated class

  17: finalType.InvokeMember("bv", BindingFlags.SetProperty, null, data, new object[] { 1.0 });

  18: finalType.InvokeMember("tp", BindingFlags.SetProperty, null, data, new object[] { 2.0 });

  19: //this sets the properties of the type by using values from the querystring

  20: foreach (XElement e in beaconNode.Descendants())

  21: {

  22:     string pname = e.Attribute("qs").Value;

  23:     object value = context.Request.QueryString[pname];

  24:     finalType.InvokeMember(pname, BindingFlags.SetProperty, null, data, new object[] { value });

  25: }

For more information visit : msdn - PropertyBuilder

Schedule a task using C# code

2008-11-18T13:32:00.001-05:00

I needed to schedule a task to run every day at 9:00 p.m. in the night. I had an addition al requirement that the task be scheduled only if the FileSystemwatcher alerts us of new files being available for processing.

Thus the files could be recieved anytime during the day, but despite that the task should be schduled to run exactly at 9:00 p.m. in the night.

So I used the following code to schedule a task(using System.Threading.Timer and TimeSpan classes)

DateTime d = DateTime.Now;

timer = new Timer(new TimerCallback(Update), null, 
TimeSpan.FromMinutes(21 * 60 - (d.Hour * 60 + d.Minute)), 
TimeSpan.FromMilliseconds(-1));

Note I used TimeSpan.FromMillisecnods(-1) to disable periodic signalling

Compare 2 SQL queries for equality

2008-11-13T14:40:00.001-05:00

DECLARE @check1 bigint
DECLARE @check2 bigint

Select @check1 = CHECKSUM_AGG(CHECKSUM(*))
FROM
(
    SELECT *
    FROM dbo.Orders (nolock)
)
AS Source

Select @check2 = CHECKSUM_AGG(CHECKSUM(*))
FROM
(
    SELECT *
    FROM dbo.Orders (nolock)
    WHERE IsSuccessful = 1
)
As Comparison

IF @check1 = @check2
    PRINT 'Queries are Equal'
ELSE
    PRINT 'Queries are NOT Equal'

Note:
If order of rows is different it will not effect the result
It does not do execution plan comparisons, simply checks if the rows returned by the two queries are the same or not

More info

Converting SQL2005 DBTimeStamp to Long for Comparison (Convert from Hex to long and decimal to Hex)

2008-11-12T16:56:00.001-05:00

If you need to Convert SQL 2005 DB TimeStamp values to Long then follow these steps:
1. Retrieve the DB TimeStamp value in a Byte Array (8 bytes long).
2. Convert the byte array into a Hexadecimal string
3. Convert the hexadecimal string to a long

private long BytesToLong(byte[] bytes)
{
    string ts = null;
    foreach (byte b in bytes)
        ts += b.ToString("X").PadLeft(2,Convert.ToChar("0"));
    return Convert.ToInt64(ts, 16);
}

There is another method to convert the DBTimeStamp to Long, but I dont prefer this method since it can return negative values. The method is:

1. Retrieve the DB TimeStamp value in a Byte Array (8 bytes long).
2. Use BitConverter class to convert the byte array into long

long result = BitConverter.ToInt64(dbTimestamp, 0);

Inorder to remedy this use the following to generate the same long value as in step 1:

public static long FromDbTimestamp(byte[] dbTimestamp)
{
    long result = 0;
    if (dbTimestamp != null)
    {
        Array.Reverse(dbTimestamp);
        result = BitConverter.ToInt64(dbTimestamp, 0);
    }

    return result;
}

Finally to convert a long into the correct DBTimeStamp use this:

public static byte[] ToDbTimestamp(long timestamp)
{
    byte[] result = BitConverter.GetBytes(timestamp);
    Array.Reverse(result);
    return result;
}

Passing state to threads via anonymous calls

2008-10-13T13:23:00.001-04:00

We already know that we can pass state into to Threads in 2 ways:

1. using ParameterizedThreadStart delegate

2.using global variables(reference or value types) accessible to the main thread as well the instantiated thread. This is also the way to share data amonst multiple threads. Infact the most common way to share data between threads is using static variables where application wide scope is desired.

3.using Thread.QueueUserWorkItem ... This differs from Parameterized ThreadStart delegate since it uses threads from a preconfigured ThreadPool instead of manually creating threads

There is another cool method to pass information to threads is via anonymous methods. For e.g.

static void Main
{
    string mainMessage = "Hello from Main !!!";
    Thread t = new Thread(delegate() { Print(mainMessage);});
    t.Start();
}
static void Print(string message)
{
    Console.Writeline(message);
}

Advantage of using this technique is that, it provides strongly typed access to the parameters passed to the thread. The Target Method can accept any number of arguments, and no casting is required (unlike Parameterized Start where the passed state is an “object”) . The flip side, though, is that you must keep outer-variable semantics in mind

Method Hiding... Polymorphism in C#

2008-09-26T20:57:00.004-04:00

In the following code:

interface IBand
 {
     int ID {get;set;}
     string Name {get;set;}
     void GetStatus();
 }
 public class Band : IBand
 {
     public int ID { get; set; }
     public string Name { get; set; }

     public Band()
     {
         GetStatus();
     }
     public void GetStatus()
     {
         ID = 555;
         Name = "Ring";
         Console.WriteLine("Base Class Called");
     }
 }

 public class ABand : Band, IBand
 {
     public string Update { get; set; }

     public ABand() : base() {}

     public new void GetStatus()
     {
         ID = 655;
         Name = "TEsst";
         Update = "ddd";
         Console.WriteLine("Derived class Called");
     }
 }
 class Program
 {
     static void Main(string[] args)
     {
         IBand band = new ABand();
         band.GetStatus();
     }

 }

We have 2 classes, Band and ABand which implement the IBand interface explicitly.

Now the call to band.GetStatus in Main will display "Derived class called". This will only happen if the Derived class (ABand) also implements the interface IBand explicitly (not in the literal sense in that it does not implement the methods using the IBand.GetStatus syntax)

If the ABand class does not implement the IBand interface explicitly (but implicitly since it inherits from Band which implements IBand) then the above code will output "Base class called"

However note one thing, that when the CTor for ABand is invoked it will invoke the ctor Band class and which will call GetStatus() method from within Band class hence this will output "Base class called" even though the ABand class has implemented the IBand interface explicilty.

Linq To XML : Check for existence of an element

2008-09-09T20:23:00.002-04:00

var bookQuery = 
from book in bookXml.Descendants("Item")

let attributes = book.Element("ItemAttributes")

let price = Decimal.Parse((
  book.Elements("OfferSummary").Any() 
  && book.Element("OfferSummary").Elements("LowestNewPrice").Any()
  ? book.Element("OfferSummary").Element("LowestNewPrice").Element("Amount").Value
  : (attributes.Elements("ListPrice").Any()
      ? attributes.Element("ListPrice").Element("Amount").Value 
      : "0"))) / 100

select new {Price = price};

The trick is to use the Elements(“node”) instead of Element(“node”) and then use the Any() function which will return true if an element of that name exists.

Some other LINQ tips:

Some of the extensions that one can use are Intersect,Union && Except

var list1 = new List<int> { 2, 4, 9, 11, 3, 6 };
var list2 = new List<int> { 3, 8, 4, 30, 9, 16 };
var newlist = list1.Intersect(list2);

This returns : 3,4,9
newlist = list1.Union(list2);
this returns : 2,3,4,6,8,9,11,16,30. finally
newlist = list1.Except(list2);
returns : 2,11,6

Convert Array of Ints into bytes

2008-08-26T14:57:00.001-04:00

Here is the code to convert array of Ints to bytes:

int[] source = ids.ToArray();
int length = source.Length * 4;
byte[] dest = new byte[length];
System.Buffer.BlockCopy(source, 0, dest, 0, dest.Length);

Alternating styles in ListView- without AlternatingItemTemplate

2008-07-25T12:44:00.001-04:00

From within any template, you have access to the current index of the row within the whole data set, using Container.DataItemIndex, and within the currently displayed items, using Container.DisplayIndex. This gives us an easy way to alternate styles:

<ItemTemplate>
  <li class="<%# Container.DisplayIndex % 2 == 0 ? "even" : "odd" %>">
    <%# Eval("Name") %>
  </li>
</ItemTemplate>

Just define the even and odd classes in your stylesheet and you're pretty much done.

Batch Inserts to SQL Server- Stored procedure method

2008-07-24T22:53:00.001-04:00

To do batch Updates to SQL server

Create DataTable or DataSet and populate it with required rows
Next Create a SqlCommand and SqlDataAdapter using that SqlCommand
Remember to set the UpdateRowSource property on the Command the the appropriate value
Set the UpdateBatchSize Property on the SqlDataAdapter
then call SqlDataAdapter.Update(dt) to push the updates to SQL server

DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("asin"));
dt.Columns.Add(new DataColumn("trackasin"));
dt.Columns.Add(new DataColumn("isrc"));
for(int i =0; i < 2000;i++)
{
    DataRow dr = dt.NewRow();
    dr["asin"] = asin;
    dr["trackasin"] = trackasin;
    dr["isrc"] = isrc;
    //dr.RowState = DataRowState.Added;
    dt.Rows.Add(dr);
}
------------------------------------------------
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MySpaceMusic"].ConnectionString))
{
    SqlCommand command = new SqlCommand(INSERT_TRACK, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.UpdatedRowSource = UpdateRowSource.None;
    command.CommandTimeout = commandTimeout;

    command.Parameters.Add("@asin", SqlDbType.VarChar, 255, dt.Columns[0].ColumnName);
    command.Parameters.Add("@trackAsin", SqlDbType.VarChar, 255, dt.Columns[1].ColumnName);
    command.Parameters.Add("@isrc", SqlDbType.VarChar, 600, dt.Columns[2].ColumnName);

    SqlDataAdapter adpt = new SqlDataAdapter();

    adpt.InsertCommand = command;
    adpt.UpdateBatchSize = batchSize;
    try
    {
        connection.Open();
        int recordsInserted = adpt.Update(dt);
    }
    finally
    {
        adpt.Dispose();
    }
}

क्यूट सोंग .. नानी तेरी मोरनी को मोर ले गए

2008-07-03T01:42:00.002-04:00

Naani Teri Morni Ko Mor Le Gaye
Baaki Jo Bacha Tha Kaale Chor Le Gaye

Khaake Peeke Mote Hoke,
Chor Baithe Rail Mein
Choron Vaala Dibba Kat Ke, Pahuncha Seedhe Jail Mein

Naani Teri Morni Ko...

Un Choron Ki Khoob Khabar Li,
Mote Thaanedaar Ne
Moron Ko Bhi Khoob Nachaaya,
Jungal Ki Sarkaar Ne

Naani Teri Morni Ko...
Achhi Naani Pyaari Naani,
Roosa-Roosi Chhod De Jaldi Se Ek Paisa De De,
Tu Kanjoosi chod de...

Nani teri morni ko chor le gaye
baaki jo bacha tha kaale chor le gaye

Unity Application block and Generic Singleton

2008-05-06T01:56:00.001-04:00

Here are some features of the Unity Application block. (Note there is overhead in using this block… i.e. overhead while creating instances of objects that have dependent objects)

http://msdn.microsoft.com/en-us/library/cc440954.aspx

Highlights of the Unity Application Block

The Unity Application Block includes the following features:

· It provides a mechanism for building (or assembling) instances of objects, which may contain other dependent object instances.

· It exposes RegisterType methods that support configuring the container with type mappings and objects (including singleton instances) and Resolve methods that return instances of built objects that can contain any dependent objects.

· It provides inversion of control (IoC) functionality by allowing injection of preconfigured objects into classes built by the application block. Developers can specify an interface or class type in the constructor (constructor injection) or apply to properties and methods attributes to initiate property injection and method call injection.

· It supports a hierarchy for containers. A container may have child container(s), allowing object location queries to pass from the child out through the parent container(s).

· It can read configuration information from standard configuration systems, such as XML files, and use it to configure the container( The Unity Container which hold references to the objects built using Resolve method).

· It makes no demands on the object class definition. There is no requirement to apply attributes to classes (except when using property or method call injection), and there are no limitations on the class declaration.

· It supports custom container extensions that developers can implement; for example, methods to allow additional object construction and container features such as caching.

And here is the code for the Generic Singleton:

/// <summary>
/// Provides a Singleton implementation using Generics.
/// </summary>
/// <typeparam name="T">Type of singleton instance</typeparam>
    public sealed class Singleton<T> where T : new()
        {
            Singleton() { }
            public static T Instance
            {
                get
                { return Nested.instance; }
            }

             class Nested
            {
                // Explicit static constructor to tell C# compiler
                // not to mark type as beforefieldinit
                static Nested() { }
                internal static readonly T instance = new T();
            }
        }

The code for returning the instance then becomes:

        public static BlogSettings Instance
        {
            get
            {
                return Utils.Singleton<BlogSettings>.Instance;
            }
        }

You have to also change the constructor for BlogSettings to public to allow this this to work

Debugging Win Service

2008-04-30T17:03:00.001-04:00

For debugging a Startup of Windows Service add the call to the Method Debugger.Launch or Debugger.Break. This call can be placed in the Constructor of the Service.cs or in the first line of the OnStart() method.

Calling Debugger.Launch() or Debugger.Break() in your code allow you to debug such problems.

For debugging an already running WindowService, simply attach the Debugger to to an existing process using "Debug\Attach to Process" menu item in VS 2005/VS 2008.

Visual Linq is a tool to visually create your Linq to SQL queries: http://code.msdn.microsoft.com/vlinq

A Primer on WCF :

It is all about Service Contracts, Operation Contracts, Data Contracts and Fault Contracts and other type of Contracts

Contracts usually enforced on Interfaces and the Classes then implement these interfaces.

If Service Contract, Operation Contract attributes are applied to classes then we have an additional advantage that the methods can be marked private and can still be accessed by WCF clients.

DataContracts are used to create .NET objects that can be sent to/fro between the client and the WCF Service. thus the DataMember attribute tells the runtime which properties need to be serialized/deserialized by the framework before being passed onto the network.

We dont need DataContracts for Intrinsic objects like Int32, string etc since they are natively serializable

FaultContracts can be applied to methods and can be used by the Client to determine what exceptions occurred at the server during processing of the request. Thus .NET exceptions are converted to FaultExceptions which are then sent over the wire as SOAP faults and then the client can reconstruct them to extract the Fault Detail from the FaultException

A good introduction to WCF can be found here: David Chappell's intro to WCF

Bindings and endpoints form the heart of WCF, we can have different kinds of bindings,

BasicHTTPBinding which allows Http and HTTPS access.
WSHttpBinding which adds the WS specifications to the binding to provide for reliability, transactions, security and other features.
WSDualHttpBinding: use this for two-way communication between client and service, only HTTP is supported
FederatedBinding: which means that if authenticated by one of the services, then the same ticket can be used to authenticate the client to other WCF/Java services
NETTCPBinding : used when communicating using TCP protocol as the transport
NamedPipeBinding: used within the local machine for inter process communication on the same machine.

Endpoints define how the service can be accessed by the client. Each WCF Service exposes endpoints to clients to publicize their services. Specify a different endpoint for each binding used by your service, thus the same machine can expose the service over HTTP and TCP using basicHttpBinding and NetTcpBinding respoectively and thus exposing endpoints for each of them. Endpoints consist of address, behavior etc

John Sharp's WCF Step by Step from Microsoft Press is an excellent book to get started.

Get back almost 1GB of free disk space after Vista SP1 installation

2008-04-06T19:31:00.001-04:00

If you have installed SP1 of Vista you certainly recognized that your drive lost around 1Gb of free space.

The solution is to use a tool that comes with the SP1 to recover your free space at the price of not being able to come back and uninstall the SP1.

This tool is vsp1cln.exe that you can run like this:

Add Windows Explorer to your Visual Studio tools menu

2008-03-27T14:18:00.000-04:00

.NET Tip of The Day: Add Windows Explorer to your Visual Studio tools menu

Concurrency and Co-ordination Runtime - CCR

2008-03-03T20:41:00.001-05:00

Pretty cool Library from Microsoft for performing concurrent operations like multithreading operations and async tasks. Essenitally you get a guide to migrate all your APM ( Asynchronous Programming Model) tasks to the CCR tasks so that synchronization and co-ordination issues are automatically handled for you behind the scenes.

Some of the good links on CCR are:

ASP.NET State Server and Load Balancer

2008-02-08T11:29:00.000-05:00

These are great links:
ASP.NET load balancing and ASP.NET state server (aspnet_state)

Session state ...State Server

How to check email works with no SMTP

2008-01-09T15:40:00.000-05:00

There is a way to send e-mails with no SMTP server set up. Just configure your .NET application to drop e-mails into a specified folder instead of sending them via SMTP server

  <system.net>

    <mailSettings>

      <smtp deliveryMethod="SpecifiedPickupDirectory">

        <specifiedPickupDirectory pickupDirectoryLocation="c:\Rohit\" />

      </smtp>

    </mailSettings>

  </system.net>

This will instruct SmtpClient class to generate mail message, save it as .eml file and drop it into c:\Test\ folder

Using CompareValidator for just Data Validation

2008-01-08T10:20:00.000-05:00

A simple method to validate an integer, double, date, or currency values entered into a textbox on ASP.NET page

Normally the CompareValidator is used to check one value against another on ASP.NET page. However it also can be used to ensure user has entered data in a correct format. (Data Validation)

Here's how it works:

Set the ControlToValidate property to the ID of the TextBox.
Set the Operator property to DataTypeCheck.
Assign a ValidationDataType enumeration member to the Type property, which includes either one of the following : String, Integer, Double, Date, and Currency.

using FOR XMLPATH to concat multiple row values

2008-01-03T09:36:00.000-05:00

The need is to concatenate multiple row values from a table
The following TSQL in SQL 2005 can assist in returning values

Following is the input table
ID Value
---------
1 '-'
1 'C'
1 'B'
1 'A'
2 '='
2 'G'
2 'D'
=======
Expected output :
ID Concat
1 '-CBA'
2 '=GD'
=================

TSQL for this :
select Distinct ID, o.List
from
#t1 A
CROSS APPLY
(
select Val as [text()] from #t1 B
where B.ID = A.ID
FOR XML PATH('')
) o (List)

SQL Server (user) instances and AutoAttach DB

2007-12-25T17:39:00.000-05:00

Developers often need to attach databases on demand and then start using them immediately. However before developers can start working with DB's they need to first attach the DB, then create a SQL login and then add that login to the DB users list. with SQL Express this is no longer a requirement.

With SQL Express we can use Auto Attach DB's and User instances.
SQL 2005 Express and Full editions both support autoAttachment of DB's, however User instancing is only supported with SQL Express.

To enable AutoAttachment of DB's use : AttachDbFilename=db.mdf in connection string.
When you use "AttachDbFilename=|DataDirectory|db.mdf" the connection string automatically looks for the DB in the AppData folder of the Web Application.

However there is a limitation that if the user is not a administrator, he would still not be able to AutoAttach the DB. we can solve this by using "User instances".
User instance is another SQL server instance and runs under the credentials of the currently logged in user, thus no need to give the user administrative privileges.
==================================================
User Instances is a feature that makes SQL Server 2005 Express different from other SQL Server editions. Before I explain User Instances, you need to understand that a SQL Server instance is essentially an in-memory occurrence of the sqlservr.exe executable program. Different SQL Server editions support different numbers of instances. For example, the SQL Server 2005 Enterprise Edition supports 50 instances, and the SQL Server 2005 Standard, Workgroup, and Express editions each support 16 instances. Each instance runs separately and has its own set of databases that aren't shared by any other instance. Client applications connect to each instance by using the instance name.

Typically, the first SQL Server instance you install becomes the "default" instance. The default instance uses the name of the computer on which it's installed. You can assign a name to subsequent instance installations, so they're called "named" instances. During the installation process, you can assign any name to a named instance. Client applications that want to connect to an instance use the \ convention. For example, if the default instance name is SQLServer1 and the instance name is MyInstance, the client application would connect to the named instance by using the server name SQLServer1\MyInstance.

As with the other SQL Server editions, SQL Server Express supports the default instance and named instances, but SQL Server Express uses SQLExpress as the default instance name rather than the name of the computer system.

In addition to regular SQL Server instances, SQL Server Express also supports User Instances. User instances are similar to named instances, but SQL Server Express creates user instances dynamically, and these instances have different limitations. When you install SQL Server Express, you have the option of enabling User Instances. By default, User Instances aren't enabled. After installation, you can enter the sp_configure command in SQL Server Management Studio Express (SSMSE) or the sqlcmd tool by using the following syntax:

sp_configure 'user instances enabled','1'

To disable User Instance support, replace 1 with a 0 in the sp_configure command.

User Instances were designed to make deploying databases along with applications easier. User Instances let users create a database instance on demand even if they don't have administrative rights. To utilize User Instances, the application's connection string needs to use the attachdbfilename and user instance keywords as follows:

Data Source=.\SQLExpress;integrated security=true;
attachdbfilename=\MyDatabase.mdf;user instance=true;"

When an application opens a connection to a SQL Server Express database in which User Instances are enabled and the application uses the attachdbfilename and user instance keywords, SQL Server Express copies the master and msdb databases to the user's directory. SQL Server Express starts a new instance of the sqlserver.exe program and SQL Server Express attaches the database named in the attachdbfilename keyword to the new instance.

Unlike common SQL Server instances, SQL Server Express User Instances have some limitations. User Instances don't allow network connections, only local connections. As you might expect with the network-access restriction, User Instances don't support replication or distributed queries to remote databases. In addition, Windows integrated authentication is required. For more information about SQL Server Express and User Instances you can read the Microsoft article "SQL Server 2005 Express Edition User Instances" at
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp

SQL Tips

2007-12-09T20:10:00.000-05:00

Difference between truncate and delete

delete operations are logged and thus can be restored using transaction log backups (if not using Simple recovery model - the default)
truncate operations are not logged, thus cannot be rolled back.
delete can be used with WHERE clause to selectively delete rows
truncate cannot selectively delete rows.
truncate reseeds the identity column , delete does not reseed
truncate cannot be used on tables with foreign key constraints
since truncate is not logged, it does not activate triggers, delete does activate triggers

=====================================================
Difference between temp table and table variables:

table variables are not logged, i.e. they don't use transaction logs, thus cannot participate in transactions
indexes cannot be created on table variables
foreign key constraints cannot be created either on table variables or temp tables
table variables cannot be nested, i.e. they cannot be reused within the nested subquery, whereas temp tables can be accessed from within nested subqueries
SP with table variables require fewer recompilation than SP with temp tables.
table variables dont make use of parallelism since they reside in memory, temp table do make use of multiple processors since they reside in tempdb.
table variables used for small amounts of data since they reside in memory.

=====================================================
Types of Data Integrity
Entity Integrity: Primary Key and Unique key constraints are used to maintain entity integrity
Domain Integrity : Check and Not NULL constraints are used to maintain domain integrity
Referential integrity : Foreign key constraints are used to maintain referential integrity
=====================================================
Local Temp tables & variables : exist for the duration of session, thus not shared by different client sessions. If Dynamic sql creates the local temp table then it falls out of scope as soon as it exits the EXEC statement used to execute the dynamic SQL.
Global Temp tables & variables : shared by all client sessions and are destroyed when the last client session exits
===========================================================
Types of UDFs:
Scalar valued udf : returns a single value, can be used anywhere an expression is used, just like subqueries.
table valued udf : returns a table.
2 types of table valued udf's :

inline table valued udf : uses a single statement to return a table
multi-statement table valued udf : multiple statements are issued in the udf before returning the table

UDF's can be used within select/having/where clauses and table valued udf's return rowsets/table and can be used in joins.
Cannot use temp tables in UDF's, only table variables.
UDFs are analogous to views that accept input parameters
================================================
Indexed Views are views that use disk space to store data.
Created with SCHEMABINDING option
have restrictions on the columns that the Indexed view can contain
have restrictions on the columns that can be used within the index created for the view
represent views that have a unique clustered index
Must have ansi_nulls, quoted_identifier, arithabort etc session options set to ON
If using standard edition than cannot be used implicitly by queries, must use the NOEXPAND optimizer HINT or reference with view with the VIEW name.
If using Group by then count_big(*) must be used
Must not be used in OLTP environments, suitable for less frequently updated base tables.

============================================================
ANSI_NULLS : default OFF, when ON then returns 0 rows when col = null or col <> null used even though the column has both null and not null values.
Thus we need to insure that we use the col is null, col is not null syntax for comparisons

ANSI_WARNINGS : default OFF: when ON then shows warnings when one of the following situations arises:
sum, max, min, avg used against a column that contains nulls
Divide by Zero exception occurs
Overflow exception occurs (string may be truncated)

QUOTED_IDENTIFIER : default OFF : when ON then allows object identifiers to use double quotes for distinguishing from reserved words and allows string literals to use single quotes

Indexed views need all the above sessions options to be set to ON

Concat_NULL_YIELDS NULL : default OFF

ANSI_DEFAULTS : default OFF: When ON then sets the above and the following session options to ON:
ANSI_PADDING

More on MSMQ

2007-12-05T16:42:00.000-05:00

Recoverable messages:

MSMQ messages are stored in memory by default, hence there is a possibility of loosing them if the appdomain exits or the application crashes.
To create messages that get persisted to disk we use Recoverable messages.

Method 1:
Set the Recoverable property on the Message itself before sending it.

Message recoverableMessage = new Message();
recoverableMessage.Body = "Sample Recoverable Message";
recoverableMessage.Recoverable = true;

Method 2:
Use the "DefaultPropertiesToSend.Recoverable" property on MessageQueue itself to mark all messages sent via this queue as recoverable:

MessageQueue msgQ = new MessageQueue(@".\private$\Orders");
msgQ.DefaultPropertiesToSend.Recoverable = true;
msgQ.Send("This message will be marked as Recoverable");

=====================================================
Transactional Messages (Internal transactions)

MessageQueueTransaction msgTx = new MessageQueueTransaction();
MessageQueue msgQ = new MessageQueue(@".\private$\Orders");
msgTx.Begin();
try
{
  msgQ.Send("First Message", msgTx);
  msgQ.Send("Second Message", msgTx);
  msgTx.Commit();
 
  msgTx.Begin();
  Message msg = msgQ.Receive(msgTx);
  msgTx.Commit();
 }
 catch
 {
   msgTx.Abort();
 }
 finally
 {
   msgQ.Close();
 }
================================================================
External Transactions (using MSDTC and COM+)
====================================================================
  
using System.EnterpriseServices;
using System.Messaging;
 
[assembly: ApplicationName("MSMQ_Example")]
[assembly: ApplicationActivation(ActivationOption.Server)]
 
[Transaction(TransactionOption.Required)]
public class Message : ServicedComponent
  
{
    [AutoComplete()]
    public void DoTransaction()
    {
        //Create the queue
        MessageQueue msgQ = new MessageQueue(@".\private$\Orders");
        Order incomingOrder;
        //Configure the formatter with the expected type(s)
        Type[] targetTypes = new Type[1];
        targetTypes[0] = typeof(Order);
        msgQ.Formatter = new XmlMessageFormatter(targetTypes);
        //DB Connection setup code removed
        SqlConnection sqlConn = new SqlConnection(connectionString);
        SqlCommand insertLog = new SqlCommand(sqlString, sqlConn);
        //Command setup code removed
        try
        {
            //Receive new message, casting the result to the Order type
            incomingOrder = (Order)(msgQ.Receive(new TimeSpan(0, 0, 30)).Body);
            //Use ID from message to setup parameters for database insert
            orderIDParam.Value = incomingOrder.ID;
            receiveParam.Value = DateTime.Now;
            insertLog.Parameters.Add(orderIDParam);
            insertLog.Parameters.Add(receiveParam);
            sqlConn.Open();
            //Insert a new entry into the Log table
            insertLog.ExecuteNonQuery();
            //Mark the transaction as completing successfully... can be done using ContextUtil.SetComplete
        }
        catch (Exception ex)
        {
            //Remember to throw the exception, only then will AutoComplete() abort the transaction
            // transaction can be aborted using ContextUtil.Abort if AutoComplete() atribute is not used
            throw ex;
        }
        finally
        {
            //Always close the connection, regardless of success/failure
            sqlConn.Close();
        }
    }
}

After you create the serviced component, it must be strongly named and then deployed to the GAC so as to work correctly as a COM+ application.

After the component has been installed into the COM+ application, clients can create and call your component just like any other .NET class, and your component will automatically run inside COM+ and act as part of a transaction.

MSMQ Primer

2007-12-05T11:06:00.000-05:00

Creating Queues and Managing Queues is a breeze with .NET
Queuenames in .NET:

public queues : .\qname
private : .\private$\qname
journal: .\qname\journal$

Create queues using the MessageQueue class and specify if you need to use Journal queues with the message queue. the Journal queues hold the messages after that have been "received" from the main message queue.
The third type of queues are the AdministrationQueues /Acnowledgement queues and we use the Message.RecieveByCorrelationId method or PeekByCorrelationId to receive admin/ack messages from these queues after a message has been received from the main message queue.
the PeekByCorrelationId method takes in the message id of the message in the main message queue.

    public class QueueMgmt
    {
        public static MessageQueue CreateQueue(string path)
        {
            MessageQueue mq = null;
            if (MessageQueue.Exists(path))
            {
                mq = new MessageQueue(path);
                Console.WriteLine("Connected to existing queue " + path);
            }
            else
            {
                mq = MessageQueue.Create(path);
                Console.WriteLine("Created to new queue " + path);
            }
            Console.WriteLine("Message Queue format is " + mq.FormatName);
            return mq;
        }
    }

=========================================================

        private void btnCreateQueue_Click(object sender, EventArgs e)
        {
            // Create a public queue and configure it for journaling.
            using (MessageQueue mq = QueueMgmt.CreateQueue(@".\DotNet"))
            {
                mq.Label = "DotNet Message Queue";
                mq.UseJournalQueue = true;
            } // mq disposed.
            // Create a private queue for acknowledgments.
            using (MessageQueue mq = QueueMgmt.CreateQueue(@".\Private$\DotNetAdmin"))
            {
                mq.Label = "DotNet Acknowledge Queue";
            } // mq disposed.
        }

=======================================
After creating the queues, you can send messages to the queue using mq.Send method, note we can send any XML Serializable types in the message body. Remember to set the mq.Formatter.TargetTypes property with the TYPES that it is supposed to deserialize when receiving them.

Note we are also setting the Administration queue which will receive the acknowledgment messages once they are read from the Queue.

        private void btnSendMessage_Click(object sender, EventArgs e)
        {
            using (MessageQueue mq = new MessageQueue(@".\dotnet"))
            {
                using (System.Messaging.Message msg = new System.Messaging.Message())
                {
                    msg.Label = "dotnetmessage";
                    msg.Body = "Message : " + DateTime.Now.ToString();
                    msg.AdministrationQueue = new MessageQueue(@".\private$\dotnetadmin");
                    msg.AcknowledgeType = AcknowledgeTypes.FullReceive;
                    mq.Send(msg);
                    Console.WriteLine("Message sent with ID: " + msg.Id);
                }
            }
        }

==============================================
Once the message is sent it arrives in the public or the private queue where it was sent.
Then while receiving the messages we read the message from the public/private queue, once it has been received a copy is stored in the Journal queue if one has been setup.
Also admin messages are sent to the Admin queue depending on the AcknowledgementTypes setup during the sending of the message.
Note we can get mutiple ack/admin messages for a single message by ORing different ackTypes in the message.AcknowledgeType enumneration.

       private void btnReceive_Click(object sender, EventArgs e)
        {
            Type[] targetTypes = { typeof(string) };
            string msgId = string.Empty;
 
            using (MessageQueue mq = new MessageQueue(@".\dotnet"))
            {
                ((XmlMessageFormatter)mq.Formatter).TargetTypes = targetTypes;
                using (System.Messaging.Message msg = mq.Receive())
                {
                    Console.WriteLine("Received Message ID {0} : \n\t Body = {1}", msg.Id, msg.Body);
                    msgId = msg.Id;
                }
            }
 
            using (MessageQueue mq = new MessageQueue(@".\dotnet\Journal$"))
            {
                ((XmlMessageFormatter)mq.Formatter).TargetTypes = targetTypes;
                using (System.Messaging.Message msg = mq.PeekById(msgId))
                {
                    Console.WriteLine("Peeked message from Journal Queue {0}:\n\t Body = {1}", msg.Id, msg.Body);
                }
            }
            using (MessageQueue mq = new MessageQueue(@".\private$\dotnetadmin"))
            {
                using (System.Messaging.Message msg = mq.PeekByCorrelationId(msgId))
                {
                    Console.WriteLine("Peeked Acknowledgement queue {0} : \n\t Acknowledgement is {1}", msg.Id, msg.Acknowledgment);
                }
            }
        }

DBCC Commands

2007-12-01T23:29:00.001-05:00

Some useful DBCC commands are:

DBCC CHECKIDENT ('authors',RESEED,0)

DBCC DROPCLEANBUFFERS --- Removes all clean buffers from the buffer pool
DBCC FREEPROCCACHE -- this clears the proc cache on all db's on the server.
DBCC FLUSHPROCINDB ---- clears the cache for a paricular DB

You can create PROXY users in SQL 2005 using 2 methods
1 CREATE USER greg WITHOUT LOGIN

2. create certificate al_cert with subject='certificate for al'
create user al FOR CERTIFICATE al_cert.

Conditional TSQL sorting using CASE statement

2007-11-26T09:30:00.001-05:00

We can do conditional sorting in 2 ways.
1. using dynamic SQL
2. using CASE statements.

1. using Dynamic SQL
DECLARE @sql nvarchar(400), @input int, @paramdef nvarchar(100)
SELECT @input = 2, @paramdef = N'@input int'

set @sql = N'if @input = 1 select * from acctlist order by email ASC; if @input = 2 select * from acctlist order by email DESC'

exec sp_executesql @sql, @paramdef, @input

2. using CASE statements:
select * from authors
order by
CASE WHEN @col = 'firstname' AND @order = 'ASC' THEN firstname END ASC,
CASE WHEN @col = 'firstname' AND @order = 'DESC' THEN firstname END DESC,
CASE WHEN @col = 'email' AND @order = 'ASC' THEN email END ASC,
CASE WHEN @col = 'email' AND @order = 'DESC' THEN email END DESC,
----Default sort by lastname
CASE WHEN @order = 'ASC' THEN lastname END ASC,
CASE WHEN @order = 'DESC' THEN lastname END DESC

Creating ServerTraces using SQL Profiler for background profiling

2007-11-25T18:35:00.000-05:00

Follow these steps to create a server trace for either SQL 2000 or SQL 2005.
Server Traces are used for running traces in the background so that SQL server does not have to pump profiling results back to the SQL 2005 Profiler, it can log to a file instead.

Note you can create server trace script files for either the SQL 2000 or SQL 2005 using the SQL 2005 Profiler tool:

First use Profiler to define the events, columns, and filters needed. Some Events are : SQL:BatchCompleted and RPC:Completed, SP:StmtCompleted. Important columns are : Duration, CPU, Reads and Writes. Some advanced events are SP:Recompile and Scan:Started to check for table and index scans
Click the Run button. Immediately stop the trace
Click the File menu, expand the Export option, and then expand the Script Trace Definition option. Choose For SQL Server 2005 (or SQL 2000 if creating script for older SQL Server) and select a filename to save the script.
Once the script has been saved, open it for editing in SQL Server Management Studio.
The following line of the script must be edited, and a valid path must be specified, including a filename:
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
@maxfilesize, NULL. The @maxfilesize is 5MB by default
Run the script. The generated script will also select back a @traceID
Once you are done use the @traceID to stop and close the trace:

The fn_trace_gettable function can be used to read the data from the trace file :

SELECT * FROM ::fn_trace_gettable('C:\Traces\myTrace.trc', 999) where 999 is the number of rollover trace files to read

Limitationsof table variables

2007-11-11T18:25:00.000-05:00

Refer this linkTable Variables

DECLARE @people TABLE
(
id INT,
name VARCHAR(32)
)

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function. You can create a table variable within a UDF, and modify the data using one of the above statements. For example, you could do this:

CREATE FUNCTION dbo.example1
(
)
RETURNS INT
AS
BEGIN
DECLARE @t1 TABLE (i INT)
INSERT @t1 VALUES(1)
INSERT @t1 VALUES(2)
UPDATE @t1 SET i = i + 5
DELETE @t1 WHERE i < 7

DECLARE @max INT
SELECT @max = MAX(i) FROM @t1
RETURN @max
END
GO

However, try that with a #temp table:

CREATE FUNCTION dbo.example2
(
)
RETURNS INT
AS
BEGIN
CREATE TABLE #t1 (i INT)
INSERT #t1 VALUES(1)
INSERT #t1 VALUES(2)
UPDATE #t1 SET i = i + 5
DELETE #t1 WHERE i < 7

DECLARE @max INT
SELECT @max = MAX(i) FROM #t1
RETURN @max
END
GO

Results:

Server: Msg 2772, Level 16, State 1, Procedure example2, Line 7
Cannot access temporary tables from within a function.

Or try accessing a permanent table:

CREATE TABLE table1
(
id INT IDENTITY,
name VARCHAR(32)
)
GO

CREATE FUNCTION dbo.example3
(
)
RETURNS INT
AS
BEGIN
INSERT table1(name) VALUES('aaron')
RETURN SCOPE_IDENTITY()
END
GO

Results:

Server: Msg 443, Level 16, State 2, Procedure example3, Line 8
Invalid use of 'INSERT' within a function.

Table variables can lead to fewer stored procedure recompilations than temporary tables (see KB #243586 and KB #305977), and — since they cannot be rolled back — do not bother with the transaction log.

So, why not use table variables all the time? Well, when something sounds too good to be true, it probably is. Let's visit some of the limitations of table variables (part of this list was derived from KB #305977):

* Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.

* You cannot use a table variable in either of the following situations:

INSERT @table EXEC sp_someProcedure

SELECT * INTO @table FROM someTable

* You cannot truncate a table variable.

* Table variables cannot be altered after they have been declared.

* You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does with them is another story. One thing to note is that you cannot explicitly name your constraints, e.g.:

DECLARE @myTable TABLE
(
CPK1 int,
CPK2 int,
CONSTRAINT myPK PRIMARY KEY (CPK1, CPK2)
)

-- yields:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'CONSTRAINT'.

-- yet the following works:
DECLARE @myTable TABLE
(
CPK1 int,
CPK2 int,
PRIMARY KEY (CPK1, CPK2)
)

* You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column, or DEFAULT CONSTRAINT.

* You cannot use a user-defined type (UDT) in a column definition.

* Unlike a #temp table, you cannot drop a table variable when it is no longer necessary—you just need to let it go out of scope.

* You cannot generate a table variable's column list dynamically, e.g. you can't do this:

SELECT * INTO @tableVariable

-- yields:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@tableVariable'.

You also can't build the table variable inside dynamic SQL, and expect to use it outside that scope, e.g.:

DECLARE @colList VARCHAR(8000), @sql VARCHAR(8000)
SET @colList = 'a INT,b INT,c INT'
SET @sql = 'DECLARE @foo TABLE('+@colList+')'
EXEC(@sql)
INSERT @foo SELECT 1,2,3

-- this last line fails:

Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@foo'.

This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.

* The system will not generate automatic statistics on table variables. Likewise, you cannot manually create statistics (statistics are used to help the optimizer pick the best possible query plan).

* An INSERT into a table variable will not take advantage of parallelism.

* A table variable will always have a cardinality of 1, because the table doesn't exist at compile time.

* Table variables must be referenced by an alias, except in the FROM clause. Consider the following two scripts:

CREATE TABLE #foo(id INT)
DECLARE @foo TABLE(id INT)
INSERT #foo VALUES(1)
INSERT #foo VALUES(2)
INSERT #foo VALUES(3)
INSERT @foo SELECT * FROM #foo

SELECT id
FROM @foo
INNER JOIN #foo
ON @foo.id = #foo.id

DROP TABLE #foo

The above fails with the following error:

Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@foo'.

This query, on the other hand, works fine:

SELECT id
FROM @foo f
INNER JOIN #foo
ON f.id = #foo.id

* Table variables are not visible to the calling procedure in the case of nested procs. The following is legal with #temp tables:

CREATE PROCEDURE faq_outer
AS
BEGIN
CREATE TABLE #outer
(
letter CHAR(1)
)

EXEC faq_inner

SELECT letter FROM #outer

DROP TABLE #outer
END
GO

CREATE PROCEDURE faq_inner
AS
BEGIN
INSERT #outer VALUES('a')
END
GO

EXEC faq_outer

Results:

letter
------
a

(1 row(s) affected)

However, you cannot do this with table variables. The parser will find the error before you can even create it:

CREATE PROCEDURE faq_outer
AS
BEGIN
DECLARE @outer TABLE
(
letter CHAR(1)
)

EXEC faq_inner

SELECT letter FROM @outer
END
GO

CREATE PROCEDURE faq_inner
AS
BEGIN
INSERT @outer VALUES('a')
END
GO

Results:

Server: Msg 137, Level 15, State 2, Procedure faq_inner, Line 4
Must declare the variable '@outer'.

For more information about sharing data between stored procedures, please see this article by Erland Sommarskog.

TSQL: Parsing delimited string into table

2007-10-31T12:15:00.000-04:00

CREATE FUNCTION dbo.udf_ItemParse (
@Input VARCHAR(8000), @Delimeter char(1)='|'
)
RETURNS @ItemList TABLE (
Item VARCHAR(50) ,
Pos int
)
AS
BEGIN

DECLARE @Item varchar(50)
DECLARE @StartPos int, @Length int
DECLARE @Pos int
SET @Pos = 0
WHILE LEN(@Input) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @Input)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@Input) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @Pos = @Pos + 1
SET @Item = SUBSTRING(@Input, 1, @StartPos - 1)
SET @Input = SUBSTRING(@Input, @StartPos + 1, LEN(@Input) - @StartPos)
END
ELSE
BEGIN
SET @Pos = @Pos+1
SET @Item = @Input
SET @Input = ''
END
INSERT @ItemList (Item, Pos) VALUES(@Item, @Pos)
END
RETURN
END

SoapExtension for Logging Soap Exceptions

2007-10-26T13:17:00.000-04:00

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.IO;
using System.Xml;
using MIT.BizServices.MCUtil.Misc;

namespace MIT.Common.ExceptionHandlingSoapExtension
{
//

// SOAP extension to transfer full exception information from
// server to client in the event of an exception being thrown
// from a webmethod
//

//
// In the event of an exception being thrown during the execution
// of a webmethod on the server, this class steps in and writes detailed
// exception information to the output stream.
//
// Client side, if an exception is detected on the response from a
// webmethod call, this class retrieves the detailed exception
// information from the input stream and throws an exception
// containing the detailed information
//
public class ExceptionHandlingSoapExtension : SoapExtension
{

region "Initialisation methods - not used because this class has no state to maintain"
public override object GetInitializer(System.Type serviceType) {
return null;
}

public override object GetInitializer(System.Web.Services.Protocols.LogicalMethodInfo methodInfo, System.Web.Services.Protocols.SoapExtensionAttribute attribute) {
return null;
}

public override void Initialize(object initializer)
{
}
endregion

region "Stream chaining code"
private Stream oldStream;
private Stream newStream;

public override Stream ChainStream(Stream stream) {
oldStream = stream;
newStream = new MemoryStream();
return newStream;
}

//

// Copies the contents of one stream to another
//

private void StreamCopy(Stream source, Stream dest) {
StreamReader Reader = new StreamReader(source);
StreamWriter Writer = new StreamWriter(dest);
Writer.WriteLine(Reader.ReadToEnd());
Writer.Flush();
}
endregion

public override void ProcessMessage(System.Web.Services.Protocols.SoapMessage message)
{
switch (message.Stage)
{
case SoapMessageStage.BeforeSerialize:
return;

case SoapMessageStage.AfterSerialize:
//If exception present in message, write details
//to the new stream
if (message.Exception != null)
{
InsertExceptionDetails(message.Exception);
}

//Copy new stream to old stream
newStream.Position = 0;
StreamCopy(newStream, oldStream);
return;

case SoapMessageStage.BeforeDeserialize:
//Copy old stream to new stream
StreamCopy(oldStream, newStream);
newStream.Position = 0;
return;

case SoapMessageStage.AfterDeserialize:
//If exception present in message,
//get details from stream and throw to caller
if (message.Exception != null)
{
CheckExceptionDetails();
}
return;

default:
throw new ArgumentException("Invalid message stage");

}
}

//

// Insert details of the specified exception into the output stream

// <param name="ex">Exception to write details for
private void InsertExceptionDetails(Exception ex) {
//Read output stream into XML document
newStream.Position = 0;
XmlTextReader Reader = new XmlTextReader(newStream);
XmlDocument MessageDoc = new XmlDocument();
MessageDoc.Load(Reader);

XmlNamespaceManager NsMgr = new XmlNamespaceManager(MessageDoc.NameTable);
NsMgr.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/");

//Construct string describing exception
string ErrorInfo;
if (ex.InnerException != null)
{
ErrorInfo = ex.InnerException.Message;
}
else
{
ErrorInfo = ex.Message;
}
//log exception to flat file
Helper.LogException(string.Empty, ex);

//Find existing soap:Fault node describing exception
XmlNode ExceptionNode;
ExceptionNode = MessageDoc.SelectSingleNode("//soap:Fault", NsMgr);

//Add extended exception detail node to Fault node
XmlElement ExceptionDetail;
ExceptionDetail = MessageDoc.CreateElement("ExtendedExceptionDetails");

ExceptionDetail.InnerText = ErrorInfo;

ExceptionNode.AppendChild(ExceptionDetail);

//Write XML document back to output stream
newStream = new MemoryStream();
MessageDoc.Save(newStream);
}

//

// Reads extra exception information from stream

// Details of any exception detail found in the input stream
private void CheckExceptionDetails()
{
//Read input stream into XML document
newStream.Position = 0;
XmlTextReader Reader = new XmlTextReader(newStream);
XmlDocument MessageDoc = new XmlDocument();
MessageDoc.Load(Reader);

XmlNamespaceManager NsMgr = new XmlNamespaceManager(MessageDoc.NameTable);
NsMgr.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/");

//Find extended exception detail node
XmlNode ExceptionDetailNode;
ExceptionDetailNode = MessageDoc.SelectSingleNode("//soap:Fault/ExtendedExceptionDetails", NsMgr);
XmlNode exception = MessageDoc.SelectSingleNode("//soap:Fault/faultstring", NsMgr);

//Return detail text if found, empty string otherwise
if (ExceptionDetailNode != null)
{
if (exception != null)
{
if (exception.InnerText.Contains("WebServiceException"))
{
throw new WebServiceException(ExceptionDetailNode.InnerText);
}
else
{
throw new Exception(ExceptionDetailNode.InnerText);
}
}
}
}
}
}

Exception and Logging Application Block

2007-10-26T13:06:00.000-04:00

Add reference to the following DLL's:
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Logging
If you intend to log information to a database, you also need to reference the Microsoft.Practices.EnterpriseLibrary.Data.dll

For adding tracing in the method call use the following:
public DataSet GetPricingDataSet(string loanNumber)
{
using Tracer("Trace")
{
DoSomething();
//For additional logging use:
Logger.Write("message")
//or use:
Logger.Write(new LogEntry("message"))
}
}

Add a Trace listener in the Logging Application Block via the Enterprise Library Configuration tool and then specify a Category called "Trace" which logs to this newly configured Trace Listener.

For more info:
http://www.devx.com/dotnet/Article/31463/1954?pf=true

http://www.devx.com/dotnet/Article/35736?trk=DXRSS_DOTNET
http://www.devx.com/dotnet/Article/36184?trk=DXRSS_DOTNET - Enterprise Library 3.0
======================================================
For Exception Handling:
======================================================
Add references to the following DLL's:
ExceptionHandling.dll
ExceptionHandling.Logging.dll

public static void LogException(string policyName, Exception ex)
{
if (string.IsNullOrEmpty(policyName))
policyName = ConfigurationManager.AppSettings["ExceptionPolicy"];

ExceptionPolicy.HandleException(ex, policyName);
}

TSQL to concatenate column values into a single string

2007-10-03T17:37:00.000-04:00

DECLARE @cols AS varchar(3000)
;
WITH YearsCTE
AS
(SELECT DISTINCT YEAR(OrderDate) as [Year] FROM Sales.SalesOrderHeader)

SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([YEAR] AS varchar(4)) + ']'
FROM YearsCTE
ORDER BY [YEAR]

Note the use of ISNULL. This adds a additional "," to separate records. Note the isnull helps to add the additonal "," only to records numbered 2 or greater.

Create and deploy a Sharepoint Feature

2007-09-23T10:43:00.000-04:00

Suppose you need to replace the SmallSearchInputBox on the sharepoint site with a new feature. Follow these steps:

step1 : create a copy of existing feature e.g. controlLightUp
Thus copy this folder from 12\Template\Features\CustomLightUp to 12\Template\Features\NewSearchArea

Step2: modify feature.xml to contain the new GUID and add a reference to the element.xml (within a subdirectory or the same directory as feature.xml, whichever you prefer) which describes the feature.
Specify if the feature should be HIDDEN or displayed in the Sharepoint site settings
Specify Title and Description if this feature is NOT Hidden

Step 3: then modify the elements.xml file in controls folder to have the correct settings. Scope can take one of the following values: FARM, Webapplication, Web and Site
Site is SiteCollection
Web is Site
Web Application is parent of Sitecollection
FARM is server wide enabling of the feature

Step4: suppose CustomLightUp feature is installed at FARM level snd it is referenced through a delagate control in the master page and its control id "SmallSearchInputBox", and you want to create a new feature at the Site scope and for the same delegate control
Then there are 2 ways to achioeve this

1: rename the Delegate control id from "SmallSearchInput" to "newSearch" then then make the modification in elements.xml to contain this new control id. then install the feature at Site level using stsadm utility

2. you can keep the control id the same i.e. "SmallSearchInput" but then modify the Sequence value in elements.xml to have a value less than 100.
(for more on this refer to my previous blog on delegate control) Then install the feature at the site level using stsadm
command is stsadm -o installfeature -filename NewSearchArea\feature.xml
stsadm -o activatefeature -filename NewSearch\feature.xml -url http://w.c.com

For automatic deployment of the feature read this:
http://www.codeplex.com/wspbuilder
Or
http://msdn.microsoft.com/msdnmag/issues/07/05/OfficeSpace/
and
http://msdn.microsoft.com/msdnmag/issues/07/08/OfficeSpace/

if you want to list all existing features then you need to extend the stsadm utility:
http://sharepointsolutions.blogspot.com/2006/09/extending-stsadmexe-with-custom.html
and
http://msdn2.microsoft.com/en-us/library/bb417382.aspx
and
http://www.andrewconnell.com/blog/articles/MossStsadmWcmCommands.aspx
and
http://stsadm.blogspot.com/2007/08/enumerate-features.html

Automating Dev, QA, Staging, and Production Web.Config Settings with VS 2005

2007-09-21T12:58:00.000-04:00

One of the questions I get asked fairly regularly is: "how can I can easily change different configuration settings in my web.config file based on whether my application is in a dev, qa, staging or production mode?" The most common scenario for this is one where an application uses different database connection-strings for testing and production purposes.

It turns out you can easily automate this configuration process within the Visual Studio build environment (and do so in a way that works both within the IDE, as well as with command-line/automated builds). Below are the high-level steps you take to do this. They work with both VS 2005 and VS 2008.

Use ASP.NET Web Application Projects (which have MSBuild based project files)
Open the VS Configuration Manager and create new "Dev", "QA", "Staging" build configurations for your project and solution
Add new "web.config.dev", "web.config.qa", and "web.config.staging" files in your project and customize them to contain the app's mode specific configuration settings
Add a new "pre-build event" command to your project file that can automatically copy over the web.config file in your project with the appropriate mode specific version each time you build the project (for example: if your solution was in the "Dev" configuration, it would copy the web.config.dev settings to the main web.config file).

Once you follow these steps, you can then just pick the mode your solution is in using the configuration drop-down in the VS standard toolbar:

The next time you build/run after changing the configuration mode, VS will automatically modify your application's web.config file to pick up and use the web.config settings specific to that build configuration (so if you select QA it will use the QA settings, if you select Deploy it will use the Deploy settings).

The benefit with this approach is that it works well in a source control environment (everyone can sync and build locally without having to make any manual changes on their local machines). It also works on a build server - including with scenarios where you are doing automated command-line solution builds.

To learn more about the exact steps to set this up, please read the Managing Multiple Configuration File Environments with Pre-Build Events post that Scott Hanselman published earlier tonight. Also check out ScottGu's ASP.NET Tips, Tricks, and Gotchas page for other ASP.NET Tips/Tricks recommendations.

multi column sorting of a typed collection

2007-09-18T17:29:00.000-04:00

Use the following One line code for multi-column or single column sorting of a typed collection:

List<Liability> liabList = new List<Liability>();

liabList.Sort(delegate(Liability l1, Liability l2)
{ int r = l1.Type.CompareTo(l2.Type);
if (r == 0 && l1.HolderName != null)
r = l1.HolderName.CompareTo(l2.HolderName);
if (r == 0)
r = l1.Amount.CompareTo(l2.Amount);
return r;
} );

Nant Intellisense with Nant Contrib tasks and VS2005 nant Addin

2007-06-27T10:28:00.000-04:00

Nant Intellisense with Nant Contrib tasks and VS2005 nant Addin.

First task is to generate the XSD (schema) file that needs to be added inorder to enable intellisense.
use the following Nant .build script to generate the nant.xsd file:

<project name="nant" default="go">
<property name="NAntContrib" value="c:\rohit\nantcontrib-0.85\bin" />
<target name="go">
<loadtasks assembly="${NAntContrib}\NAnt.Contrib.Tasks.dll" />
<nantschema output="nant.xsd" target-ns="http://nant.sf.net/nightly/2006-03-08-0.85/nant.xsd"/>
</target>
</project>

Build this script using nant:
nant /f:nantxsd.build
This will create the nant.xsd file.

Copy this file nant.xsd to 'C:\Program Files\Microsoft Visual Studio .NET 2003\Common7\Packages\schemas\xml' on your system, or if you are using VS2005 then copy to "C:\Program Files\Microsoft Visual Studio 8\Xml\Schemas".

In your NAnt build file (I make my with the extension .build) include this xmlns attribute:
<project xmlns="http://nant.sf.net/nightly/2006-03-08-0.85/nant.xsd" name="test" default="build">

Now you will get intellisense for all nant tasks as well nant contrib tasks.
==============================================================
To add the Nant Addin for VS2005.
Download Nant addin from:
http://sourceforge.net/projects/nantaddin

Create a directory for Visual Studio Addins ex : "%Program Files%/Microsoft Visual Studio 8/Addins" Copy the NAntAddin directory along with the "NAntAddin.AddIn" file into it.

open VS2005. click Tools\Options.
Click on Envrironment\"Add-in/Macros Security"
Then click "Add" to add the "%Program Files%/Microsoft Visual Studio 8/Addins" directory.

Restart VS 2005.
Then click Tools\Add-in Manager.
Then check the checkbox against "NantAddin".

Now you will be able to run nant scripts or part of it directly from within VS2005.

Turning an ascx user control into a redistributable custom control

2007-06-27T09:54:00.000-04:00

This is possible using the new binary compilation feature of ASP.NET 2.0 and also the availability of the ClassName property on the user control.
The ClassName property allows to specify the Namespace that is needed when consuming the usercontrol from another application.

We can publish multiple controls within a single assembly but we have to insure that the Web project contains just UserControls only (ascx along with the associated code behind files).
But if you use the "use fixed naming and single page assemblies" option then a separate assembly for each user control will be generated. However the advantage of using this option is that if you were to make changes and deploy a newer version of the user control, then you need not modify the consuming application.

This is since the assembly NAME will remain the same on each compilation of the user control project and thus we do not need to update the reference to the user control assembly from within the consuming application.

There is one more option if you decide to use the "use fixed naming and single page assemblies".
After separate assemblies are created using the Publish Website option (or using the aspnet_compiler.exe tool) we can then merge these assemblies into a single assembly using the aspnet_merge.exe tool. Another option to combine multiple user controls into a single assembly is by using the Web deployment projects (WDP).

Read this excellent MSDN magazine article by Fritz Onion on Web Deployment projects (WDP) and for info about packaging user controls into assembly for multi application use:
http://msdn.microsoft.com/msdnmag/issues/07/04/ExtremeASPNET/

So the steps for converting a user control into a redistributable custom control are:
1. Create the User control in a Website project in VS2005.
The main restriction is that the User Control needs to be self contained. That is, it cannot be dependent on app global things like App_Code or global.asax. If you do need to use the App_Code folder then you could use WDP or aspnet_merge.exe to combine mutiple assemblies into a single assembly.

When you create the User Control (say MyTestUC.ascx), VS creates it with the a @control directive that looks like this:

<%@ Control Language="C#" ClassName="MyTestUC" %>

This is fine, except for one thing: we want the class to live within a namespace of our choice (instead of 'ASP', which is what's used by default). To do this, we simply modify the ClassName attribute to include the namespace (this is a new feature in 2.0). e.g.

<%@ Control Language="C#" ClassName="Acme.MyTestUC" %>

2. Publish the website to compile the user control into a assembly.
Note you need to "uncheck" 'Allow this precompiled site to be updatable' option within "Publish Website", or you can use aspnet_compiler along with -u switch to create a binary deployment of the user controls.

3. Add reference to the generated assembly in step 2 from within the consuming web application.
First, add a Register directive to your page. It should look something like this:

<%@ Register TagPrefix="Acme" Namespace="Acme" Assembly="App_Web_mytestuc.ascx.cdcab7d2" %>
Note here that the assembly name is "App_web_mytestuc.ascx.cdcab7d2"
This assembly was generated using the "use fixed naming and single page assemblies"

the reference the user control within the page of the consuming application like this:
<Acme:MyTestUC id="MyUC" runat="server" />

If you had multiple user controls within that single assembly you could then reference the second control from that assembly likewise:
<Acme:MyTestUC2 id="MyUC2" runat="server" />

That is all that is there to it.

File Compress and File Upload

2007-04-29T21:57:00.000-04:00

/// Upload a file......
string strName;
strName = System.IO.Path.GetFileName(fileUpload.PostedFile.FileName);

try {
fileUpload.PostedFile.SaveAs(Server.MapPath(strName));
this.lblResult.Text = strName + " was uploaded successfully.";
}
catch (Exception ex)
{ this.lblResult.Text = "An Error Occured While Uploading File.";}

============================================

/// Compress and Upload the file ...
string strName = Path.GetFileName(fileUpload.PostedFile.FileName);

//Create a stream object to read the file.
Stream myStream = fileUpload.PostedFile.InputStream;
byte[] myBuffer = new byte[myStream.length];

//Read the file using the Stream object and fill the buffer.
myStream.Read(myBuffer, 0, myBuffer.Length);
myStream.Close();
FileStream compresFile;
compresFile = File.Create(Server.MapPath(Path.ChangeExtension(strName, "gz")));

// Write from the buffer containing the file contents to the gzip file using the GZipStream object
GZipStream myStreamZip = new GZipStream(compresFile, CompressionMode.Compress);
myStreamZip.Write(myBuffer, 0, myBuffer.Length);
myStreamZip.Close();

Encryption/Decryption in .NET 2.0

2007-04-24T16:48:00.000-04:00

using System;
using System.IO;
using System.Security;
using System.Security.Cryptography;
using System.Runtime.InteropServices;
using System.Text;

namespace Demo
{
public enum CryptoAlgo
{
DES,
RC2,
Rijndael,
TripleDES
}

public class Crypto : IDisposable
{
// Call this function to remove the key from memory after use for security

[System.Runtime.InteropServices.DllImport("KERNEL32.DLL", EntryPoint= "RtlZeroMemory")]
private static extern bool ZeroMemory(IntPtr Destination, int Length);

private SymmetricAlgorithm cAlgo;
private readonly int BufSize = 1024;

public Crypto(CryptoAlgo algo) : this(algo, null, null)
{
}
public Crypto(CryptoAlgo algo, string key, string iv)
{
switch (algo)
{
case CryptoAlgo.DES:
cAlgo = DES.Create();
break;
case CryptoAlgo.RC2:
cAlgo = RC2.Create();
break;
case CryptoAlgo.Rijndael:
cAlgo = Rijndael.Create();
break;
case CryptoAlgo.TripleDES:
cAlgo = TripleDES.Create();
break;
default:
throw new ArgumentOutOfRangeException("algo");
}

if (key != null)
{
cAlgo.Key = Convert.FromBase64String(key);
cAlgo.IV = Convert.FromBase64String(iv);
}
}

///

/// Gets the key for the algorithm as a Base64 string.
///

public string Key
{
get
{
return Convert.ToBase64String(cAlgo.Key);
}
}

///

/// Gets the IV for the algorithm as a Base64 string.
///

public string IV
{
get { return Convert.ToBase64String(cAlgo.IV); }
}

public void EncryptFile(string inPath, string outPath)
{
using (FileStream inStream = File.OpenRead(inPath))
using (FileStream outStream = new FileStream(outPath, FileMode.Create, FileAccess.Write))
{
EncryptStream(inStream, outStream);
}
}

public void DecryptFile(string inPath, string outPath)
{
using (FileStream inStream = File.OpenRead(inPath))
using (FileStream outStream = new FileStream(outPath, FileMode.Create, FileAccess.Write))
{
DecryptStream(inStream, outStream);
}
}

public byte[] EncryptBytes(byte[] buffer)
{
using (MemoryStream inStream = new MemoryStream(buffer))
using (MemoryStream outStream = new MemoryStream())
{
EncryptStream(inStream, outStream);
return outStream.ToArray();
}
}

public byte[] DecryptBytes(byte[] buffer)
{
using (MemoryStream inStream = new MemoryStream(buffer))
using (MemoryStream outStream = new MemoryStream())
{
DecryptStream(inStream, outStream);
return outStream.ToArray();
}
}

public void EncryptStream(Stream inStream, Stream outStream)
{
using (ICryptoTransform encryptor = cAlgo.CreateEncryptor())
using (CryptoStream cryptoStream = new CryptoStream(outStream, encryptor, CryptoStreamMode.Write))
{
// Read from in file until EOF and write to crypto stream.
byte[] buf = new byte[BufSize];
int read = 0;
while ((read = inStream.Read(buf, 0, buf.Length)) > 0)
{
cryptoStream.Write(buf, 0, read);
}
cryptoStream.Flush();
outStream.Flush();
}
}

public void DecryptStream(Stream inStream, Stream outStream)
{
using (ICryptoTransform decryptor = cAlgo.CreateDecryptor())
using (CryptoStream cryptoStream = new CryptoStream(inStream, decryptor, CryptoStreamMode.Read))
{
// Read from the cryptoStream until EOF and write decrypted bytes to outFile.
byte[] ba = new byte[BufSize];
int read = 0;
while ((read = cryptoStream.Read(ba, 0, ba.Length)) > 0)
{
outStream.Write(ba, 0, read);
}
outStream.Flush();
}
}

public static void Test()
{
Crypto crypto = new Crypto(CryptoAlgo.DES);
string key = crypto.Key;
string iv = crypto.IV;
crypto.EncryptFile("c:\\mydata.txt", "c:\\encrypted.txt");
crypto.DecryptFile("c:\\encrypted.txt", "c:\\decrypted.txt");
Console.WriteLine("Decrypted.txt:");
Console.WriteLine(File.ReadAllText("c:\\decrypted.txt")); // Will work for text files.

// Test decrypting with stored key.
crypto = new Crypto(CryptoAlgo.DES, key, iv);
crypto.DecryptFile("c:\\encrypted.txt", "c:\\decrypted.txt");
Console.WriteLine("Decrypted.txt:");
Console.WriteLine(File.ReadAllText("c:\\decrypted.txt")); // Will work for text files.

}

public void Dispose()
{
this.cAlgo.Clear();
}
}

}

Convert string to Stream

2007-04-20T15:53:00.000-04:00

   private void AddAttachmentFromStream(MailMessage message, String data, String attachmentName)
  {
    MemoryStream stream = new MemoryStream(UTF32Encoding.Default.GetBytes(data));
    stream.Position = 0;
  // Create a new attachment, add the attachment to the supplied message.
    Attachment att = new Attachment(stream, attachmentName);
    message.Attachments.Add(att);
  }

PropertyComparer for SORTING a Generic List

2007-04-19T16:26:00.000-04:00

public class PropertyComparer<T>: IComparer<T>
{
private readonly System.Reflection.PropertyInfo _propertyInfo;
private readonly string _sortDirection;

public PropertyComparer(string sortDirection, string propertyToSort)
{
_sortDirection = sortDirection;
_propertyInfo = typeof(T).GetProperty(propertyToSort, System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.IgnoreCase);
}

public int Compare(T x, T y)
{
object xValue = _propertyInfo.GetValue(x, null);
object yValue = _propertyInfo.GetValue(y, null);
if ((_sortDirection == "Ascending"))
{
return System.Collections.Comparer.Default.Compare(xValue, yValue);
}
else
{
return System.Collections.Comparer.Default.Compare(yValue, xValue);
}
}
}

======================
protected void Page_Load(object sender, EventArgs e)
{
List<Task> test = new List<Task>();
Task ta = new Task();
ta.TaskName = "Scheduling";
ta.Description = "Start of the Scheduling process to schedule processing of transcripts and press releases");
test.Add(ta);
test.Sort(new PropertyComparer<Task>("Ascending","TaskName"));
}

Recursive Find Control Code

2007-04-13T15:36:00.001-04:00

  1 public T FindControl<T>(string id) where T : Control
  2 {
  3     return FindControl<T>(Page, id);
  4 }
  5
  6 public static T FindControl<T>(Control startingControl, string id) where T : Control
  7 {
  8     // this is null by default
  9     T found = default(T);
 10
 11     int controlCount = startingControl.Controls.Count;
 12
 13     if (controlCount > 0)
 14     {
 15         for (int i = 0; i < controlCount; i++)
 16         {
 17             Control activeControl = startingControl.Controls[i];
 18             if (activeControl is T)
 19             {
 20                 found = startingControl.Controls[i] as T;
 21                 if (string.Compare(id, found.ID, true) == 0) break;
 22                 else found = null;
 23             }
 24             else
 25             {
 26                 found = FindControl<T>(activeControl, id);
 27                 if (found != null) break;
 28             }
 29         }
 30     }
 31     return found;
 32 }

Enterprise Library 3.0

2007-04-12T14:46:00.000-04:00

This release of Enterprise Library includes: Caching Application Block, Cryptography Application Block, Data Access Application Block, Exception Handling Application Block, Logging Application Block, Policy Injection Application Block, Security Application Block and Validation Application Block.

Download here...

dynamically hookup (hard code in global.asax) HTTP Modules

2007-04-07T20:38:00.001-04:00

one issue that has come up a few times is that the root site defines an HTTP module. The child virtual (an off the root virtual directory) when created by default inherits that module entry in web.config and usually fails because the module isn't available. Now it's easy to use a remove entry in your virtuals:

add the following to your web.config file
remove name="TopLevelModule"

and that can usually take care of it. However, if you have many sub-virtuals you need to touch this can get tedious.
So rather than fixing the Web.config in each subapplication I've removed the module definition in web.config and instead load the module via code. HttpModules hook up to the HttpApplication object of an ASP.NET application which is represented by your global.asax file in a Web project. HttpModules hook up to the events of this HttpApplication object, and since all a module really does is attach to the appropriate event handler in its Init() method there's no reason that you can't do this in code as well.
There's one little gotcha though: It has to be done at just the right time in the HttpApplication life cycle which is when the HttpApplication object initializes (multiple times, once for each instance of HttpApplication). The only method where this works correct is HttpApplication Init().
To hook up a module via code you can run code like the following instead of the HttpModule definition in web.config:

public class Global : System.Web.HttpApplication
{
public static xrnsToashxMappingModule Module = new xrnsToashxMappingModule();

public override void Init()
{
base.Init();
Module.Init(this);
}
}

All you do is override the HttpApplication's Init() method and then access the static instance's Init method. Init() of the module hooks up the event and off you go.
Note the use of HttpApplication Init; you might be tempted to use Application_Start, but that event is more like a static constructor that fires only once per Web application. Init() fires everytime a new application instance is initialized. Remember there are multiple HttpApplication instances executing side by side to handle simultaneous requests and each instance initializes separately.
Using web.config is the preferred way of hooking up handles usually though for sure. But there are situations where you might not want to allow the module hookup to be dynamic. For example, if you have an application where the module is crucial to operation, performs some security feature, or version check, you might not want to allow removal of the module - this way it's a lot more difficult to disconnect the module. If you have a packaged application it can also be nice to have the set up hard coded in this fashion - one less thing to screw up during installation or when users start poking around in configuration files

Encrypt/Decrypt connection strings in web.config

2007-03-22T10:51:00.000-04:00

const string PROVIDER = "DataProtectionConfigurationProvider";
const string PROVIDER = "RSAProtectedConfigurationProvider";

Configuration ObjConfiguration =
WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);

ConnectionStringsSection cnStrSettings = ObjConfiguration.ConnectionStrings;

//Encrypt the Connection Strings Section
cnStrSettings.SectionInformation.ProtectSection(PROVIDER);

//Decrypt the Connection Strings Section
if (cnStrSettings.SectionInformation.IsProtected)
{
cnStrSettings.SectionInformation.UnprotectSection();
}
ObjConfiguration.Save();

aspnet_regsql with SQLExpress database

2007-03-21T11:13:00.000-04:00

Creating a new DB in VisualStudio.NET 2005 is as simple as "Select APP_DATA node -> Add New Item -> Sql Database" and wah-lah you have a new aspnet.mdf file located in your APP_DATA folder. However, when you run the tool ASPNET_REGSQL.exe in Wizard Mode (E.g. using the "-W" switch) there is no way to specify a SQLEXPRESS attached database - it only seems to support SQL Server 2005 (and earlier) database servers.

So, after several attempts, I finally figured-out the "right" way to do this:

aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\MyProject\APP_DATA\aspnetdb.mdf"

This will connect to the local SQLEXPRESS engine and attach the MDF file passed in the "-d" switch then create the appropriate objects in the DB.
================================================================

aspnet_regsql.exe -S server -d database -E -A all

While this concept still applies for SQL Server 2005 Express Edition, it can be a little harder to get the server and database names right. What database server is SQL Server Express installed on? And what's the database name for a .MDF file in the App_Data folder?

Assuming you are working on an ASP.NET application locally, the server name will be: localhost\SQLExpress

The database name is (and here's it can get a bit tricky), is the path to the MDF file when it was created. So, say that you have an ASP.NET application created in the classroom lab at C:\Labs\Website\App_Data\MessageBoard.mdf. The name of the database is C:\Labs\Website\App_Data\MessageBoard.mdf, meaning you could install the membership services from the command-line using:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Labs\Website\App_Data\MessageBoard.mdf” -E -A all

Now, imagine that you zip up your files onto a USB keychain drive, go home, and copy your project files to C:\Home\Website. Now, if you wanted to create the services, you'd think you'd just type in:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Home\Website\App_Data\MessageBoard.mdf” -E -A all

Ah, but the database name is C:\Labs\Website\App_Data\MessageBoard.mdf. Eep. So when you run the above command the database can't be found and cryptic error messages abound. Essentially, it can't find the database C:\Home\Website\App_Data\MessageBoard.mdf so it tries to create a database file in the default directory (%PROGRAM FILES%\Microsoft SQL Server\MSSQL.1\DATA) with the filename C:\Home\Website\App_Data\MessageBoard.mdf. This, of course, causes problems since that's not a valid filename. Ick.

So how do we fix this? There are a couple optios. The easiest is probably to download the (free) SQL Server 2005 Management Studio Express program and attach the database file. Then, from the Properties pane you can see the database name. You can then use this with aspnet_regsql.exe. (You could also rename the database at this point...)

If you want to be 3l33t you can use sqlcmd, attach the database (sp_attach_db) giving it a friendly name, which you can then use to run the aspnet_regsql.exe command line program against. Something like:

sqlcmd -S localhost\SQLExpress -Q “EXEC sp_attach_db 'Foobar', N'pathToDBfile'”

And then:

aspnet_regsql.exe -S localhost\SQLExpress -d Foobar -E -A all

Adding Asc/Desc Icon on Table Headers for Sorting

2007-03-13T11:17:00.000-04:00

ListItemType itemType = (ListItemType)e.Item.ItemType;
// Don't bother too much for the footer, and separator items

if(itemType == ListItemType.Separator || itemType == ListItemType.Footer)
{
}
else if (itemType == ListItemType.Header)
{

TableCell TCell;
if(TCell != null)
{
// make a new label
Label sortSymbol = new Label();
sortSymbol.Font.Name = "Webdings";
sortSymbol.Font.Size = 10;

if(this.SortAscend == "Asc")
{
// Ascending
sortSymbol.Text = "5";
sortSymbol.ForeColor = System.Drawing.Color.Red;
theCell.Controls.AddAt(0, sortSymbol);
}
else
{
// Descending
sortSymbol.Text = "6";
sortSymbol.ForeColor = System.Drawing.Color.Blue;
theCell.Controls.Add(sortSymbol);
}

}

SPLIT function - TSQL

2007-03-09T10:37:00.001-05:00

CREATE FUNCTION SPLIT (
@str_in VARCHAR(8000),
@separator VARCHAR(4) )
RETURNS @strtable TABLE (strval VARCHAR(8000))
AS
BEGIN

DECLARE
@Occurrences INT,
@Counter INT,
@tmpStr VARCHAR(8000)

SET @Counter = 0
IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator
SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences
BEGIN
SET @Counter = @Counter + 1
INSERT INTO @strtable
VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

IF DATALENGTH(@tmpStr) = 0
BREAK

END
RETURN
END

Encryption in SQL2005

2007-03-08T11:45:00.000-05:00

I found this nice article on Encryption in SQL2005.

http://www.databasejournal.com/features/mssql/article.php/3461471 - Authentication
http://www.databasejournal.com/features/mssql/article.php/3481751 - Authorization

http://www.databasejournal.com/features/mssql/article.php/3483931 - Encryption

http://www.databasejournal.com/features/mssql/article.php/3488046 - Security

http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/3058/

As a matter of fact we already have password hashing capability in SQL2000 itself, the PWDENCRYPT function is available to create a one-way hash.

In 2005 there are the EncryptByKey, EncryptByCert, EncryptByAsmKey and EncryptByPassPhrase methods that can be used for encrypting data.

For passwords the encryptByCert / EncryptByAsmKey or EncryptByPassPhrase can used which provide enough security.

EncryptByKey is symmetric key encryption which is usually combined with Certificates to encrypt the Symmetric key used for encypting large amounts data.

At the root level is the Service Master key which is automatically created during SQL2005 installation.

Then we have a Database Master Key which needs to be created using DDL statements for each Database on the SQL Server Instance. The database master key is created using the service master key along with an optional password.

create master key encryption by password = 'SecretPassword' ----this creates the DB master key
create certificate rbg_cert with subject = 'cert for rbg' ----creates a certificate using the DB Master key
create certificate rbg_cert encryption by password = 'secret' with subject = 'cert for rohit' ----- this creates a certificate using password as the Private Key instead of the DB master key.
create user rbg for certificate rbg_cert ----- creates the PROXY user
create login rbg_login from certificate rbg_cert --- creates a SQL login -- note logins are created in master DB, thus a rbg_cert should exist in master DB before you can run this command

Important system tables are

sys.symmetric_keys - stores master and symmetric keys
sys.databases --- list of all db's in the server
sys.certificates -- lists certificates in a particular DB
sysusers --- lists users in a particular DB, syslogins --- all logins

Then Certificates and Asymmetric keys are created using the database Master key and then we use the Encryption functions to encrypt and decrypt data/passwords. The EncryptByKey and EncryptByPassPhrase are symmetric encryption mechanisms which do not involve using the Database master key

Script to Temporarily disable and enable constraints

2007-03-01T11:02:00.000-05:00

http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm#s4

/*To disable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To disable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To disable constraints on tables starting with a particular pattern*/
GO

/*To enable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To enable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To enable constraints on tables starting with a particular pattern*/
GO

Quick Search within all stored procedures

2007-03-01T10:58:00.000-05:00

/* Method 1 :: For Master Database */

Select
distinct(SO.[name]) as 'Stored Procedure Name'
From
SysComments SC,
SysObjects SO
Where
SO.ID = SC.ID and
cateGory <> 2 and
PatIndex( @searchString, text) > 0
Order By
[name]
End
Else
Begin

/* Method 2 :: For Individual Database
Double check whether you have removed the prefix "sp_" from the SP name.
*/

Select
Routine_Name as 'Stored Procedure Name'
From
InformatiOn_Schema.Routines
Where
Routine_type = 'procedure' and
ObjectProperty(Object_Id(Routine_Name),'IsMsShipped') = 0 and
PatIndex( @searchString, Routine_DefinitiOn) > 0
End

If @@Error <> 0
Return (1)
Return 0
Go

sp_executesql for dynamic stored procedures with parameters

2007-03-01T10:53:00.000-05:00

DECLARE @sql nvarchar(2000)

DECLARE @param nvarchar(400)

SET @param = N' @ProductID INT, @ProductInventoryStatusID INT, @ManagedByPersonID INT'

SET @sql = N' INSERT INTO #tbRangesStart
SELECT DISTINCT t1.serialnumber
FROM productinventory t1
LEFT OUTER JOIN productinventory t2 ON t1.serialnumber - 1 = t2.serialnumber AND t1.productid=t2.productid
WHERE '

DECLARE @where nvarchar(1000)

IF @ProductID IS NOT NULL
SET @where = N' t1.productid = @ProductID AND'
ELSE
SET @where = N' '

IF @ProductInventoryStatusID IS NULL and @ManagedByPersonID IS NULL
SET @where = @where + N' t2.serialnumber is null ORDER BY t1.serialnumber'

IF @ProductInventoryStatusID IS NOT NULL and @ManagedByPersonID IS NULL
SET @where = @where + N' t1.productinventorystatusid = @ProductInventoryStatusID AND (t2.serialnumber is null OR (t1.productinventorystatusid = @ProductInventoryStatusID AND t2.productinventorystatusid <> @ProductInventoryStatusID)) ORDER BY t1.serialnumber'

IF @ProductInventoryStatusID IS NULL and @ManagedByPersonID IS NOT NULL
SET @where = @where + N' t1.ManagedByPersonID = @ManagedByPersonID AND (t2.serialnumber is null OR (t1.ManagedByPersonID = @ManagedByPersonID AND t2.ManagedByPersonID <> @ManagedByPersonID)) ORDER BY t1.serialnumber'

IF @ProductInventoryStatusID IS NOT NULL and @ManagedByPersonID IS NOT NULL
SET @where = @where + N' t1.productinventorystatusid = @ProductInventoryStatusID AND t1.ManagedByPersonID = @ManagedByPersonID AND (t2.serialnumber is null OR (t1.productinventorystatusid = @ProductInventoryStatusID AND t2.productinventorystatusid <> @ProductInventoryStatusID) OR (t1.ManagedByPersonID = @ManagedByPersonID AND t2.ManagedByPersonID <> @ManagedByPersonID)) ORDER BY t1.serialnumber'

SET @sql = @sql + @where
EXEC sp_executesql @sql, @PARAM, @ProductID, @ProductInventoryStatusID, @ManagedByPersonID

Insert Rows Generator

2007-03-01T10:45:00.000-05:00

This stored proc is used to generate INSERT STATEMENTS for inserting rows into a Table:

CREATE PROC sp_InsertGenerator
(@tableName varchar(100)) as
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(4000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(4000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
print @query
--exec sp_executesql @query
CLOSE cursCol
DEALLOCATE cursCol
GO

Generating a range of Serial numbers in TSQL

2007-03-01T10:41:00.000-05:00

The following helps in automatically generating a range of SerialNumbers in sequence:

DECLARE @SerialNumberStart INT
DECLARE @SerialNumberEnd INT
SELECT SerialNumberStart = 99900, SerialNumberEnd= 99999

DECLARE @tbNumberBase TABLE
(
Seq INT
)
INSERT INTO @tbNumberBase
SELECT digits FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)
Dig (digits)
INSERT INTO ProductInventory
(
ProductID,
SerialNumber,
Quantity
)
SELECT
@ProductID,
SerialNumber,
@Quantity
FROM
(SELECT
@SerialNumberStart
+ HundredThousands.Seq * 100000
+ TenThousands.Seq * 10000
+ Thousands.Seq * 1000
+ Hundreds.Seq * 100
+ Tens.Seq * 10
+ Units.Seq Num
FROM
@tbNumberBase HundredThousands
CROSS JOIN @tbNumberBase TenThousands
CROSS JOIN @tbNumberBase Thousands
CROSS JOIN @tbNumberBase Hundreds
CROSS JOIN @tbNumberBase Tens
CROSS JOIN @tbNumberBase Units)
TableAlias (SerialNumber)
WHERE SerialNumber <= @SerialNumberEnd
ORDER BY SerialNumber

Parameterized Properties in C#

2007-03-01T10:33:00.000-05:00

To create Parameterized Properties in C# one can only use a Indexer (the default parameterized property... this[]). However if we wanted to store an array of a particular type one could use the following which would be a parameterized property itself.:

public string[] CardsDescription
{
get
{
if (this.ViewState["CardsDescription"] == null)
{
this.ViewState["CardsDescription"] = new System.String[3];
return (string[])this.ViewState["CardsDescription"];
}
else
return (string[])this.ViewState["CardsDescription"];
}
set
{
this.ViewState["CardsDescription"] = value;
}
}

Thus when setting values you would use

int index = 0;
foreach(System.Data.DataRow dr in productDT.Rows)
{
int productID = int.Parse(dr["ProductID"].ToString());

this.CardsDescription[index] = dr["Description"].ToString();

index += 1;
}

ASP.NET 2.0 Tips and Tricks and ASP.NET, IIS7 and ASP.NET AJAX End to End Talks

2007-02-06T12:37:00.000-05:00

ASP.NET Tips and Tricks:

This talk covered ASP.NET UI, AJAX, Caching and Deployment Tips and Tricks, and Visual Studio 2005 tips/tricks. The samples include updated versions of ASP.NET AJAX that all work with ASP.NET AJAX 1.0. You can download the slides+samples here.

You can learn more about the Visual Studio build performance optimization suggestions by reading this past post of mine on improving build performance. You can also find several dozen more ASP.NET and Visual Studio Tips/Tricks of mine on my Tips and Tricks summary page.

Building an End-to-end Web Application from scratch using ASP.NET 2.0, ASP.NET AJAX, IIS7 and Visual Studio:

This talk walked through building a rich data-driven application from scratch using ASP.NET 2.0, ASP.NET AJAX 1.0, IIS7 and Visual Studio (all of which are now officially shipping products). You can download the deck + completed sample here.

This talk demonstrates the following features: IIS7 distributed configuration, Master Pages, Building a DAL using DataSets, Building Data Driven Edit Pages, Building Catalogue Browsing Pages, Site Navigation, Url Rewriting, Site Navigation, CSS Control Adapters, ASP.NET AJAX, ASP.NET AJAX Control Toolkit, Membership/Roles, IIS7 Admin UI, SQL Cache Invalidation, and WebPart portal support.

transaction isolation levels

2007-02-02T10:38:00.000-05:00

SQL Server concepts - transaction isolation levels

Introduction

While preparing some stuff around SQL Server 2005, I thought it was the ideal occasion to do some blog posts about the SQL Server 2000/2005 architecture and several concepts. In this post I'll cover transaction isolation levels, later I'll post more about transactions while talking about the new System.Transactions namespace. However, let's start by taking a look at transaction stuff inside the database itself.

Transactions: a short definition

Tons of people have given a definition of a transaction before me, so I won't cover this in much detail. I'll just try to give a definition for a transaction in order to create some context for further elaboration. Basically, a transaction is a set of statements (in our case database statements) that have to be executed in an atomic fashion (either all of the statements succeed, or all of them fail together). Next, there's the consistency property, which means that a transaction should bring the system from one consistent state to another consistent state. During the transaction, isolation has to be maintained. This means a transaction is out there on its own, it can't see what others are doing. Locking will be used to accomplish these requirements, as I'll explain in a moment. Last but not least, there's the durability property. A transaction can finish either by a commit (changes are allowed) or a rollback (changes are rolled back to the last consistent state). Durability means that when a transaction is committed, the changes made to the database should be persistent even a disaster occurs one processor cycle after the transaction commit.

The grouping of these four properties is abbreviated as the ACID properties. Transactions play a central role in database technology, especially in multi-user environments. A classic example of a transaction is the work that has to be done to transfer money from one bank account to the other. It's atomic, because the money has to be deleted from account number one and has to be added to account number two in an atomic way. It's consistent because we don't want to loose or create any money (the total sum of money aften and before the transfer has to be the same). It's isolated because others should not see an intermediate state when the money has left account number one and is not on the second account yet. Last, it's durable because we want to keep the committed state forever (on a database restart, the money should not be back on the old account).

SQL Server 2000 transaction isolation modes

In my definition of transactions, I mentioned the isolation property. The fact that transactions can't see anything from each other is simply an overstatement (or maybe it can even be called overengineering) in a lot of scenarios. Basically, there is one big tradeoff to be made: the level of concurrency versus the level of isolation. The higher the throughput you want to reach, the lower the level of isolation should be. Why is this? In one word, locking. In order to make sure others can't perform certain operations on certain objects (I'm keeping this vague intentionally for the moment) you should get a certain lock on these certain objects. Examples are shared locks and exclusive locks in database technology.

In SQL Server, a shared lock is acquired when data is read. More than one party can get a shared lock on the data, but nobody can get an exclusive lock on the data while there's still a shared lock on it. An exclusive lock is the result of a data modification (e.g. insert, update, delete). What's in a name? An exclusive lock is granted exclusively to one party (one process). Other parties can't get any type of lock on the data while there is such an exclusive lock on it. When the transaction completes, the lock can be released.

One other thing before we jump into transaction isolation modes. The SQL Server database engine contains a transaction manager which is responsible for everything around transactions, including cross-db transactions, distributed transactions, the support of savepoints, nested transactions, isolation level support, etc. A last concept you should have in mind is the concept of write-ahead logging. Simply stated, write-ahead logging ensures that rolling back work in process and rolling forward committed work to be written to the physical data pages is always possible. The idea is that the transaction log is populated with the changes that a transaction has made before the transaction is acknowledges as being committed. The system can't simply continue until the transaction's work has been logged to the transaction log (that is, synchronously).

Now back to the isolation modes. SQL Server 2000 has four different lock modi, as I'll explain now.

Uncommitted read

The weakest transaction isolation level is called uncommitted read. This means a transaction can read any data that's currently living on a data page, being committed or not. In fact, there is no isolation at all. There are simply no locks whatsoever when running in this mode, even no shared locks when reading data. So, you don't issue locks that can affect others and you don't have to wait for locks yourself either. Although this behavior affects concurrency positively, it's not the desired behavior for e.g. the financial application (money can get lost or can be created, because transactions can read intermediate states of other transactions, breaking the isolation). In data analysis scenarios this can be useful nevertheless.

Committed read

This is the default level. Data which has been changed can't ever be read by another transaction when it's not yet committed. During the data change (before the commit) an exclusive lock is held on the data. Other transactions have to wait for this lock to be released (and to acquire a shared lock) in order to continue. Also, data that is being read needs to hold a shared lock, because this blocks others from changing that data till the lock is released. However, shared locks can be released before the transaction is committed, when the read of the data has completed. This introduces the concept of non-repeatable reads. Basically this means that a transaction that is reading the same data more than once during the transaction can see different data on every read (because the shared lock doesn't need to be held in between two reads of the same data). Another concept is that of phantoms, which are new rows that appear in a table (or view) during a transaction. So, when you issue the same query more than once, new rows may have slipped in.

Repeatable read

As the name already tells us, this level ensures no non-repeatable reads can occur. The idea is that a shared lock is held till the transaction comes to an end, which negatively affects the overall performance because of possible lock contention (data which you are locking by a shared lock during a long transaction can't be modified by another transactions/user for the duration of your transaction; so it's key to reduce the length of transactions as much as you can). Therefore you can issue the same query multiple times in the context of the same transaction without having to worry about possible changes to the data you retrieved. However, phantoms still can occur.

Serializable

The summum of transaction isolation is called serializable. Basically serializable means that the result of running more than one serializable transaction concurrently is the same as running those transactions in a serialized fashion, one at a time. Next to the properties of repeatable read, phantoms can't occur too. This is done by locking on a higher level than the row/record level, e.g. the table level. The reason for this approach is the fact that we need to make sure no new rows can be added to the result sets we're using for the duration of our transaction. Assume you're querying a table for all registered members born between 1983 and 1985. If you do query this data for a second time in a serialized transaction, you don't want any phantom row to slip in. To accomplish this, SQL Server will acquire a key-range lock on the data(range) so that's impossible for another transaction to add rows to this data set until the lock is released. There are nine subtypes of key-range locks in SQL Server 2000, more info can be found in the Books Online.

How to set the transaction isolation level?

Of course, that's the last thing you should know in order to start playing around with transaction isolation levels. To set the isolation level, use the SET TRANSACTION ISOLATION LEVEL T-SQL statement. You can specify SERIALIZABLE, READ COMMITTED, REPEATABLE READ and READ UNCOMMITTED. More information can be found on MSDN.

What about SQL Server 2005?

The answer is simple: there are a couple of new modi concerning transaction isolation levels, called snapshot isolation. I won't cover this myself, but rather I want to point to an extract of the book "A First Look at SQL Server 2005 for Developers" which is available on http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1.

Page records in SQL server

2007-01-26T09:05:00.000-05:00

http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html - How do I page through a recordset?

http://databases.aspfaq.com/database/how-do-i-handle-alphabetic-paging.html ---- How do I handle alphabetic paging?

http://msdn2.microsoft.com/en-us/library/ms979197.aspx --- Microsoft Solution

http://www.sqlteam.com/item.asp?ItemID=26455 - Server Side Paging using SQL Server 2005
=========================================
http://www.aspfaq.com/show.asp?id=2120. --- Good site on SQL and this link explains SQL paging
========================================

SQL 2005 books online :: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

SQL 2000 books online:::: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Error When Installing Visual Studio 2005 SP1: "The installation source for this product is not available.

2007-01-21T20:02:00.000-05:00

Some Googling turned up a fix for a very common error message when installing SP1 - “Error 1718. File FileName was rejected by digital signature policy” - but it took a bit more searching before I found this bug report in which user rlasker provided the following helpful comment:

I had this issue. When I looked in the event log I found this:

Event Type: Error
Event Source: MsiInstaller
Event Category: None
Event ID: 1008
Date: 12/18/2006
Time: 12:18:31 PM

Description:
The installation of C:\DOCUME~1\...\LOCALS~1\Temp\ZNWA0\VS80sp1-KB926601-X86-ENU.msp is not permitted due to an error in software restriction policy processing. The object cannot be trusted.

I followed the instructions at this link:
http://support.microsoft.com/default.aspx/kb/925336

And it no longer gave me the error descibed above.

Looking in my Event Log I found the same details and dutifully applied the workaround described at the KB (http://support.microsoft.com/default.aspx/kb/925336). These steps remedied the problem, and after rebooting I was able to successfully install SP1. Hopefully this post makes it to the first page of Google's results so future developers running into the same problem who are Googling the error message can more quickly find a fix.

For more VS 2005 SP1 info, see:

Handling Errors in an ASP.NET Web Application

2007-01-21T19:49:00.000-05:00

Using custom, human-friendly error pages (avoid the Yellow Screen of Death!)
Techniques for automatically logging unhandled exceptions
Ways to automatically notify developers in the face of an unhandled exception
How to handle exceptions that arise from the depths of ASP.NET 2.0's data source controls. That is, we'll see how to handle exceptions that occur when you are working with data in an ASP.NET 2.0 page using the SqlDataSource control and a GridView and an exception is raised from the database layer.

If you can't make the talk, you can download the PowerPoint slides and code demos here. Hope to see you tomorrow!

Data Driven Query Task in DTS for updating data from Excel into SQL Server table

2007-01-19T12:23:00.000-05:00

This is a very common issue we face.

There is a Excel sheet with data that needs to be imported into SQL Server table. The SQL Server table has unique key/primary key constraints on a key column (e.g. InspectorID). However the Excel spreadsheet can contain duplicates since this either being generated manually or coming from a different data source (typically a data warehouse). To be able to create a DTS script that uploads data from the Excel spreadsheet into Inspector table :

I divided the Inspector table upload process into 2 steps. In the first step I inserted all the distinct InspectorID’s from Excel spreadsheet into the Inspector Table using the TransformData Task step in DTS, and for other columns that did not accept nulls, I used default values for these fields in the insert statement.

After this I used a Data driven query Task to update the remaining fields of the Inspector table from the spreadsheet. Working with this task was quite tricky, since at first glance it would appear as if you are updating the Inspector table with the fields from the Inspector table itself. However, once you create a one-one mapping between the fields in the Excel spreadsheet and the Inspector table using the ActiveX Script in Transformations tab and set the query type to DTSTransformstat_UpdateQuery instead of DTSTransformstat_InsertQuery, it automatically behind the scenes maps the source (Excel) fields to the destination (Inspector table) fields.

FYI, the various options available in the Transformations tab include: ActiveX Script, Copy Column, DateTime String, Lowercase String etc..

First in the Source you need to select the Connection1 (Excel) and then choose the Excel Spreadsheet from where the data is retrieved.

Second in the Bindings tab select the Inspector table from Connection2 ( SQL Server connection)

Now if the column in the Excel Spreadsheet does not Map exactly with the column in Inspector table, then you can make use of the Lookups tab in either the Transform Data Task or the Data Driven Query Task.

Thus you can create a lookup query such as : select InspectorTypeID from InspectorType where Type = ?. If this Lookup was named TypeLookup.

Then you will need to modify the ActiveX Script in Transformations tab as follows:

Function Main()
DTSDestination("InspectorID") = DTSSource("ID")
DTSDestination("InspectorName") = DTSSource("Name")
DTSDestination("InspectorTypeID") = DTSLookups("TypeLookup").Execute(DTSSource("InspectorType"))
Main = DTSTransformstat_UpdateQuery
End Function

If it is a TransformData Task then the Function Main should be set to the value : DTSTransformStat_OK

Then in the Queries Tab, select Update query and use the following query to update Inspector table:
update Inspector
set InspectorName = ?, InspectorType = ?
where InspectorID = ?

Then click on Parse/Show Parameters then Map Parameter1 to InspectorName, Parameter2 to InspectorType and Parameter3 to InspectorID.

Note that the dropdown for Parameters displays the columns from the Inspector table and not from the Excel spreadsheet. Thus you may wonder how would we get the values from the Excel spreadsheet to update the Inspector table. The trick is that since we have one-one mapping between the columns in the Excel spreadsheet and the Inspector table as we defined in the ActiveX Script in the Transformations tab, thus we get the values from the excel spreadsheet behind the scenes.

Thus this task updates the InspectorName and InspectorType number fields from the Excel spread sheet to Inspector table for all the unique/distinct InspectorID’s that were inserted in the previous “Transform Data Task” step.