Failed to access IIS metabase error when running IIS Server with ASP.Net

This is happening when you install IIS after .Net 2.0 Framework. Just reinstall Asp.Net. c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -i

SQL Create stored procedure script with error handling

Click here to see improved version of this script using try/catch syntax

USE [MyDatabase]
GO
PRINT 'DROP PROCEDURE [usp_mysproc]'
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_mysproc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_mysproc]
GO
PRINT 'CREATE PROCEDURE [usp_mysproc]'
GO
CREATE PROCEDURE [dbo].[usp_mysproc]
AS
BEGIN TRAN
DECLARE @errorNo int, @rowCount int, @errorStr varchar(4000)
DELETE FROM Table1 Where SampleDate < getdate()
SELECT @errorNo = @@ERROR, @rowCount = @@ROWCOUNT
IF @errorNo <> 0 BEGIN
    SET @errorStr = 'Error deleting old load records "load_Master"'
    GOTO ErrHandler
END
COMMIT
RETURN 0
ErrHandler:
    ROLLBACK
    DECLARE @procName sysname
    SELECT @procName = object_name( @@procid ), @errorStr = ISNULL( @errorStr, 'Unknown error' )
    IF @errorNo <> 0 BEGIN
        SELECT @errorStr = @errorStr + ': procedure=%s, error=%i'
        RAISERROR( @errorStr, 16, 1, @procName, @errorNo )
    END
    ELSE BEGIN
        SELECT @errorStr = @errorStr + ': procedure=%s'
        RAISERROR( @errorStr, 16, 1, @procName )
    END
    RETURN 1
 
GO
GRANT  EXECUTE  ON [dbo].[usp_mysproc]  TO [MyDatabaseEditor]
GO
GRANT  EXECUTE  ON [dbo].[usp_mysproc]  TO [MyDatabaseAdmin]
GO

Useful SQL Server security stored procedures

Returns information about the members of a role in the current database.

exec sp_helprolemember

Returns information about user permissions for an object in the current database.

exec sp_helprotect [Table/View/Procedure]

Returns information about the roles in the current database.

exec sp_helprole

For more info check Microsoft MSDN page.

SQL delete with join

This statement deletes data from Table1. This is useful if you have a join with multiple tables or multiple columns.

DELETE Table1
FROM Table1 t1 JOIN Table2 t2 
ON t1.Id1 = t2.Id1
AND t1.Id2 = t2.Id2

SQL Case statement

CASE Column1
  WHEN 1 THEN Column2*1
  WHEN 2 THEN Column2*2
  ELSE Column2
END as MyColumn,
-- or
CASE 
  WHEN max(Column1) = 1 THEN Column2*1
  WHEN max(Column1) = 2 THEN Column2*2
  ELSE Column2
END as MyColumn,

Bloomberg Help

GP - price/volume graph
G - graph templates
HP - historical prices
CFTC - commodity futures traders commitment
BLP - bloomberg launchpad
IAM - user information
FLDS - field finder
FPRP - field info (older)
ALT-D - terminal defaults
ALT-L - list bloomberg keys
PRTL - Client Services Portal (submit a query, technical docs)
CACS - Corporate Action Calendar
POSH - Bloomberg Marketplace (cars etc..)

Copy data from one table to another based on mulitple joins

This sample shows how to copy data from table A to table B based on multiple join columns.

INSERT INTO B(Value1, Value2)
SELECT DISTINCT Value1, Value2 
FROM A t1 
WHERE NOT EXISTS
(SELECT 1 FROM B t2
 WHERE t1.Value1 = t2.Value1
   AND t1.Value2 = t2.Value2)

Insert data to database and get generated key back

Sample table with identity column.

CREATE TABLE [dbo].[Table1](
  [Id] int IDENTITY(1,1) NOT NULL,
  [Value] [varchar](10) NOT NULL,
  CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  )
)

C# code than inserts data to database and gets generated id back.

class TestInsertDao : AdoDaoSupport
{
  private string insertSql = @"
INSERT INTO Table1(Value)
VALUES(@Value)
SET @Id = SCOPE_IDENTITY()
";
  public TestInsertDao(IDbProvider dbProvider)
  {
    this.DbProvider = dbProvider;
  }
  public int Insert(string value)
  {
    IDbParameters parameters = CreateDbParameters();
    parameters.Add("Value", DbType.String).Value = value;
    parameters.AddOut("Id", DbType.Int32);
    AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, parameters);
    return (int)parameters["@Id"].Value;
  }
} 

To get more info on C# spring setup refer to this post Using Spring.NET to load data to DataTable

C# Create Guid

This is a short form 32 characters GUID

System.Guid.NewGuid().ToString("N")

SQL Server monitoring rogue processes

Run one of these queries to check if there are any SPIDs with Blocked Status or High CPU time. It also shows the hostname, loginname, logintime info.

SELECT * FROM sys.sysprocesses
-- or this one
Sp_who2 

Run this command to get the SQL for a SPID

DBCC INPUTBUFFER (<SPID>)

Run this command to get more info about the lock

EXEC SP_LOCK <SPID>

C# overloading index operator

class MyClass
{
  private readonly Dictionary<string,object> data = new Dictionary<string,object>();
  public object this [string index]
  {
    set{ data[index] = value; }
    get{ return data[index]; }
  }
  
  public static void Main()
  {
    MyClass me = new MyClass();
    me["test"] = "sample value";
    Console.WriteLine(me["test"]);
  }
}

C# params keyword

public class MyClass 
{
  public static void UseParams(params int[] list) 
  {
    for ( int i = 0 ; i < list.Length ; i++ )
    {
      Console.Write(string.format("{0},",list[i]));
    }
  }

  public static void Main()
  {
    UseParams(1); //1,
    UseParams(1,2,3); //1,2,3,
  }
}

c# directory separator character

Directory separator character can be found in Path class.

Console.WriteLine("Path.AltDirectorySeparatorChar={0}", Path.AltDirectorySeparatorChar)
Console.WriteLine("Path.DirectorySeparatorChar={0}", Path.DirectorySeparatorChar)
Console.WriteLine("Path.PathSeparator={0}", Path.PathSeparator)
Console.WriteLine("Path.VolumeSeparatorChar={0}", Path.VolumeSeparatorChar)

' Path.AltDirectorySeparatorChar=/
' Path.DirectorySeparatorChar=\
' Path.PathSeparator=;
' Path.VolumeSeparatorChar=:

C# get application directory path

To find the application directory (were exe file is) during runtime use this:

AppDomain.CurrentDomain.BaseDirectory

ASP .NET Charting Control

SctottGu's Blog about the new ASP.NET 3.5 Charting Control

Open command line window in windows XP from the context menu

This is a very useful tool for Windows XP that enables to you open command line window from context menu. Just right click on any folder and select Open Command Window Here. You can download it from the PowerToys Windows XP page.
This works only for x86 system, to add context menu item to x64 machine follow these steps.

ASP.NET vs. J2EE Discussion

I think this page has a decent discussion about pros and cons of both technologies.

C# Log4Net configuration

- Add referece to log4net.dll file - Add log4net configuration to App.config file 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
  </configSections>
  
  <log4net>
   <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <param name="File" value="Log\\MyProject.log" />
      <param name="AppendToFile" value="true" />
      <param name="RollingStyle" value="Size" />
      <param name="MaxSizeRollBackups" value="10" />
      <param name="MaximumFileSize" value="500KB" />
      <param name="StaticLogFileName" value="true" />
      <param name="Threshold" value="INFO"/>
      <layout type="log4net.Layout.PatternLayout">
        <param name="ConversionPattern" value="%d [%t] %-5p %c.%M():%L - %m%n" />
      </layout>
    </appender>
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
      <param name="Threshold" value="DEBUG"/>
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%d [%t] %-5p %c.%M():%L - %m%n" />
      </layout>
    </appender>
    <logger name="MyCompany">
      <appender-ref ref="RollingFileAppender" />      
      <appender-ref ref="ConsoleAppender" /> 
    </logger>
  </log4net>
</configuration>

Please make sure than <logger name="MyCompany"> is the same as your project namespace. - Add the following line to AssemblyInfo.cs to execute log4net configuration

[assembly: log4net.Config.XmlConfigurator()]

Test Program

public class Program
{
  static private readonly ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  static void Main(string[] args)
  {            
    log.Info("test");
  }
}

C# allowing Unit Test project to access internal members of another project

Assuming that you have 2 projects in your solution

MySolution
+ MyProject
+ MyProjectTest

Add the following line to AssemblyInfo.cs in MyProject

[assembly: InternalsVisibleTo("MyProjectTest")]

This will allow you to access internal classes from MyProjectTest(unittests) project