How To - Get connection string from web.config in ASP.NET

Sample web.config

Get Connection string at runtime

In order to retrieve a specific connection string from the web.config, first we need a reference to System.Configuration. In .NET 4, the reference has already been added for us, but we still need to add "using System.Configuration;" in our page. In previous .NET versions, you needed to add the reference manually by going to the references folder in the solution explorer, right click and select Add Reference. From the .NET tab, select System.Configuration. After having done the above, we can get the connection string required by using the following code.

private const string CONNECTIONSTRING_NAME = "LocalSqlServer";
string myConnectionString = ConfigurationManager.ConnectionStrings[CONNECTIONSTRING_NAME].ConnectionString;

Using connection string expression syntax

We can bind the connection string directly to the connection string property of the sqldatasource using ASP.NET expressions. This is done by using the following syntax

How To - Cascading dropdowns using AJAX Callbacks

Filtering data for the users is very important for 2 reasons

1. Results in a more user friendly experience
2. Reduces data, therefore reduces load time.  

One of the most common scenarios is to have multiple dropdownlists which filter each other. For example: categories and sub-categories.
The below code uses the MS database adventureworks to retrieve data.

MS AJAX Toolkit

In the following example, we will be using the AJAX Control Toolkit. In order to start using the toolkit, you will first need to add the toolkit reference to the project, then add the following line at the top of the aspx page

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit"  %>

In order to use any control or extender from the toolkit, you will need to drop a scriptmanager in the page (this can be in the masterpage as well)

In the aspx file, copy the following code

   

      
Category
      

          
          
          
   
   

      
Sub Category
      

          
          
      
   

and in the webservice use the following code

 [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {
        [WebMethod]
        public CascadingDropDownNameValue[] GetCategories(string knownCategoryValues, string category)
        {
            DataTable dt = SqlHelper.ExecuteDataset(
            ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString,
            CommandType.Text, "SELECT * FROM [Production].[ProductCategory]").Tables[0];

            List values = new List();

            foreach (DataRow dr in dt.Rows)
            {
                values.Add(new CascadingDropDownNameValue((string)dr["Name"], 
                dr["ProductCategoryID"].ToString()));
            }
            return values.ToArray();
        }

        [WebMethod]
        public CascadingDropDownNameValue[] GetSubCategories(string knownCategoryValues, string category)
        {
            StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

            int categoryId;
            if (!kv.ContainsKey("category") || !Int32.TryParse(kv["category"], out categoryId))
            {
                return null;
            }

            DataTable dt = SqlHelper.ExecuteDataset(
            ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString,
            CommandType.Text, "SELECT * FROM [Production].[ProductSubcategory] WHERE ProductCategoryID = "
             + categoryId).Tables[0];

            List values = new List();

            foreach (DataRow dr in dt.Rows)
            {
                values.Add(new CascadingDropDownNameValue((string)dr["Name"],
                dr["ProductSubcategoryID"].ToString()));
            }
            return values.ToArray();
        }
    }

Do not forget to uncomment the part [System.Web.Script.Services.ScriptService] or else it will not work.

Devexpress

Another way to implement this is to use the devexpress combobox (ASPxCombobox). Aspx:

code behind

protected void ddlSubCategory_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e)
{
      if (string.IsNullOrEmpty(e.Parameter)) return;
      dsSubCategory.SelectParameters["ProductCategoryID"].DefaultValue = e.Parameter;
      ddlSubCategory.DataBind();
}

Personally, I prefer using the DevExpress controls as it involves much less tedious work and you do not need to create a webservice. Obviously this comes with a price tag, so if you have a limited budget, the ASP.net toolkit still offers a very good solution.

XNA Tutorial: Adding sound

Download the following wave file for this tutorial

Adding file to Content Pipeline

We first need to add the sound file to the content pipeline.  Right click Content Pipeline project and add a new folder, name this folder Audio.  Then right click on the newly created folder and select 'Add Existing Item'.  Browse to where you have just downloaded the file and select it.

Loading the sound file

Add the following variable to the Game1 class

SoundEffect soundEffect;

In the LoadContent method, add the following line of code to add the audio file

soundEffect = Content.Load<SoundEffect>(@"Audio\CameraShutter");

We have loaded successfully the sound file in our variable and all we need to do now is play it.

Playing the sound file

The simplest way to play the file is to use the Play() method

soundEffect.Play();

If you need more control on the sound file, like looping, you can use the SoundEffectInstance class

SoundEffectInstance soundEffectInstance = soundEffect.CreateInstance();
soundEffectInstance.IsLooped = true;
soundEffectInstance.Play();

Try to put the above code in the LoadContent method just below the loading of the soundEffect variable and run the project.

Adding an MP3 or WMA file

If you try to load an mp3 in a SoundEffect object you will get the error

ContentLoadException: File contains Microsoft.Xna.Framework.Media.Song but trying to load as Microsoft.Xna.Framework.Audio.SoundEffect.


You will need to use the Song class in order to load an MP3 or WMA file and then play it using the MediaPlayer class.

Song mySong = Content.Load<Song>(@"Sound\mySong");
MediaPlayer.Play(mySong);
MediaPlayer.IsRepeating = true;//if you want the song to repeat

Looking for sound files for your game?

Try FreeSound.org. There are a bunch of sound effects you can choose from and most are licensed under the Creative Commons 0 License. Obviously you should still give credit to the person who created the sound file, I'm sure that everyone appreciates that ;)

If that's not enough for you why not try the following product? There are 8000+ sound files included in this package







That's it! Have fun experimenting with audio in your XNA game :)  Remember that good audio is just as important as your game visuals.

ASPxComboBox: Add button to clear selection

The Devexpress ASPxComboBox contains a Buttons collection to which you can add a number of buttons. By default the button will contain ellipsis (...) as Text but you can change this by using the Text property. You can also add an image like in the code below. In order to remove the selection you will need to add the clientsideevent button click and check for the buttonindex. If the buttonindex is 0 (the button will create will be the first on displayed), then set the selectedindex to -1, thus removing selection.

Linq Error - Specified cast is not valid

I was getting this error in the selecting event.  The problem was that for whatever reason, a field in the the LinqToSql file had a different datatype from the actual table in the database.  The field in the table was of type bigint while the same field in the datacontext was of type int.  This could have happened if for example the database schema was altered after having dropped the table in the LinqToSQL file.  Removing the table from the datacontext and re-dropping it solved the issue.

Anyone know how to refresh the LinqToSQL file without having to remove the table and reinserting it?  That would be great!

T-SQL: Get multiple values in CSV format

There are some instances when you need to retrieve a number of values in one column seperated by a comman.  Consider this set of data:

Category	Product
Pedals	LL Mountain Pedal
Pedals	Touring Pedal
Road Bikes	Road-150 Red
Road Bikes	Road-150 Black
Pumps	Mini-pump

And you would like to display it in the following manner:

Category	Product
Pedals	LL Mountain Pedal, Touring Pedal
Road Bikes	Road-150 Red, Road-150 Black
Pumps	Mini-pump

I have found the following 2 methods to be useful:

Create a function

CREATE FUNCTION [dbo].[GetProductsBySubcategoryID]

(

    @ProductSubcategoryID int

)

RETURNS varchar(max)

AS

BEGIN

    declare @output varchar(max)

    select @output = COALESCE(@output + ', ', '') + Name

    from Production.Product

    where ProductSubcategoryID = @ProductSubcategoryID



    return @output

END

Calling the function

SELECT Name, dbo.[GetProductsBySubcategoryID](ProductSubcategoryID)

FROM Production.ProductSubcategory

GROUP BY Name,ProductSubcategoryID

Pros: Neat, Easy to read/run.
Cons: Slower, You need to create a function for each scenario

Using XMLPath

SELECT SC.Name,

(

SELECT Name +','

from Production.Product as P

WHERE P.ProductSubcategoryID = SC.ProductSubcategoryID

ORDER BY P.Name

FOR XML PATH('')

)

from Production.ProductSubcategory as SC

Pros: Faster, No need to create a function
Cons: Not easily understandable

Regarding performace, on average the XML method returned 37 rows in 8ms and the former in 6ms.

Load user control dynamically with parameters

This method can be used to load a user control dynamically in ASP.NET and pass parameters in the user control's constructor.

/// 
/// This method loads a web user control with parameters
/// 
/// The path of the usercontrol eg: 'Popup.ascx'        /// Any parameters which the user control expects        /// Current page        /// 
public static UserControl LoadControl(string UserControlPath, Page page, params object[] constructorParameters)
{
	UserControl ctl = null;
	try
	{
		List constParamTypes = new List();
		foreach (object constParam in constructorParameters)
		{
			constParamTypes.Add(constParam.GetType());
		}

		ctl = page.LoadControl(UserControlPath) as UserControl;

		// Find the relevant constructor
		System.Reflection.ConstructorInfo constructor = ctl.GetType().BaseType.GetConstructor(constParamTypes.ToArray());

		//And then call the relevant constructor
		if (constructor == null)
		{
			throw new MemberAccessException("The requested constructor was not found on : " + ctl.GetType().BaseType.ToString());
		}
		else
		{
			constructor.Invoke(ctl, constructorParameters);
		}
	}
	catch (Exception ex)
	{
		//Exception Handling
	}

	// Finally return the fully initialized UC
	return ctl;
}

For more details visit this blog entry

T-SQL: Clear transaction logs for all databases

Works only for SQL2005

EXEC sp_MSforeachdb @command1 = "BACKUP LOG ? WITH TRUNCATE_ONLY DBCC SHRINKDATABASE( ? ) "

ASP.NET: Disable Submit Form on Enter Key

By default, ASP.NET submits a form when the enter key is pressed.  When more than one is present, the first submit button in the page is 'clicked'.  You can change the default button which is pressed when the user presses the Enter key by using the following attribute in the form

<form id="form1" runat="server" defaultbutton="btnDefaultButton" >

I used this technique in order to disable this behaviour by using a dummy button at the end of the screen and pointing the defaultbutton to this button.  The dummy button must be invisible and do nothing, so here's the code

At the top:

<form id="form1" runat="server" defaultbutton="btnDisableEnter" >

At the bottom:

<asp:Button ID="btnDisableEnter" runat="server" Text="" OnClientClick="return false;" style="display:none;"/>

It seems to do the job quite well till now.  Let me know if you have a better solution or if you found it useful

XNA Tutorial: Drawing Sprites

After looking at the basics of XNA in the last tutorial, it is time to do some coding and see something on our screen! The first thing we are going to do is to draw a sprite on our screen.  Games are made up of a bunch of moving images that interact with each other and integrate seamlessly into a background.  These images in games are called sprites.

Loading an image

Create a new project by following the steps in the first tutorial. Download the image that we are going to use as a sprite from here.  Right click on the content project and create a folder called 'images'.  I like to categorise the resources in the content project by their type to keep things tidy.  Then right click on the folder and choose Add > Existing Item.

Adding an image to the content project

Now create a global variable of type Texture2D.

Texture2D pacmanTexture;

This type is present in the XNA Framework and will hold the image that we have just added.  In order to load the image, go to the LoadContent method and add the following line of code just under the spriteBatch loading.

spriteBatch = new SpriteBatch(GraphicsDevice);
pacmanTexture = Content.Load<Texture2D>("images/pacman");

The above code loads the image in our project, now all we need to do is to draw the image to the screen using the spriteBatch variable in the Draw method.

Drawing a sprite

We want the background colour to be black as our texture's background is also black. To do this, change the first line of code in the Draw method from Color.CornflowerBlue to Color.Black.

If you run the project, you will see a black background instead of the default colour. Now all we have to do is to draw the image. Add the following lines of code under the GraphicsDevice.Clear line

GraphicsDevice.Clear(Color.Black);

//Draw pacman
spriteBatch.Begin();
spriteBatch.Draw(pacmanTexture, Vector2.Zero, Color.White);
spriteBatch.End();

What the above code does, is to first tell the spriteBatch that we are going to pass something to draw, then we tell the spriteBatch to draw the image by passing the texture variable we have created earlier in the spriteBatch's Draw method. The second parameter is the position where we are going to draw the image, Vector2.Zero is a static variable for position (x:0, y:00. Note that position in XNA is done by using x and y axis, (0,0) being the top-left point of the screen. The last parameter is used to tint the image with another colour. White is passed you do not want to tint the image.

Now run the project and you should see the following.

Drawing our sprite

Centering the sprite

In order to display the image in the middle of the screen we need to know the size of the screen.  This can be done by calling GraphicsDevice.Viewport.Width and GraphicsDevice.Viewport.Height.  Therefore we can adjust the position of the sprite to the centre of the screen by amending the position parameter we earlier set as Vector2.Zero.

spriteBatch.Draw(pacmanTexture, new Vector2(GraphicsDevice.Viewport.Width / 2 - pacmanTexture.Width / 2, GraphicsDevice.Viewport.Height / 2 - pacmanTexture.Height / 2), Color.White);

Centering an image

In the next tutorials we will cover how to draw text and animate sprites.

Please feel free to comment if you have any suggestions or queries for this tutorial, they are much appreciated :)

T-SQL Conditional Ordering: CASE ORDER BY

You can conditionally order a query depending on a variable by using the case statement in the order by.

DECLARE @sort SMALLINT
SET @sort = 1

SELECT * FROM   Person.Contact
ORDER BY
CASE WHEN @sort=1 THEN FirstName ELSE LastName END

XNA Tutorial: Getting Started

Introduction

So you want to get started in the game developing world? You have probably already heard about XNA and would like to see what it can do. XNA is a framework, an API, built by Microsoft in order to facilitate the work needed to create a games for Xbox 360, PC and Windows Phone 7.    This doesn't mean that you will be creating games in a few minutes.  In order to create a game using XNA, you will have to have a form of programming background, preferably with C#.   There are a lot of books out there and websites which can give you a thorough understanding of the language, but you will need to get your hands dirty in order to truly learn it.  Then, you will need to master a few skills with the XNA framework.  I recommend to start creating a simple 2D game like Pong, Tetris or Space Invaders.

Requirements

The good news is that everything you need to start learning with XNA is free. In order to start developing games you will need to install the following software:

1. Microsoft XNA Game Studio 4.0.  Download
2. Visual Studio. I recommend using Visual Studio C# Express as this is free  Download

I will be using the C# Language and Visual Studio 2010 during these tutorials.

Creating a new project

After having installed the above software, open your visual studio and create a New Project (File > New > Project). Select Windows Game (current XNA version) Visual C#

Creating a new project

This will create a new blank XNA Project.  As you can see, some of the work has already been done for us. On the right section, the solution explorer, you will find that our solution contains 2 projects; our game and the Content pipeline.  The content pipeline is where we will be storing all of our game assets, that is images, sounds, fonts, 3D models and other stuff.  The pipeline translates these resources into a language which XNA understands, something which we do not have to worry about :). The other project is where we will start creating our game.

Run the project (press F5 or Debug > Start Debugging). This will cause the project to build. The project should build successfully and the game should run (if you have any errors feel free to post a comment). Congratulations, you have just created your first (boring as hell) game! :)

Your first game

Under the hood

Lets see what your first game is made up of.  'A game?' you might ask. Yes, although there is only a blue screen (Microsoft, blue really?), the XNA framework has already built the essentials for us in order to create a game.  The game loop is the heart of every game. A game works just like an animation on a set of papers. If these papers are just left on top of each other, you will only see the top drawing but if you flip fast through the papers, you will see an animation. In this case, you are the game loop. Just like we need time in order to move from one frame to another, the game has a game loop which is used to call a set of methods every so often until the game ends (60 times per second to be exact).  The game loop is made up of 2 major methods, Update and Draw which will be described in more detail below.

Now let's check the code that has been generated for us

public class Game1 : Microsoft.Xna.Framework.Game
    {
        GraphicsDeviceManager graphics;
        SpriteBatch spriteBatch;

        public Game1()
        {
            graphics = new GraphicsDeviceManager(this);
            Content.RootDirectory = "Content";
        }

        protected override void Initialize()
        {
            base.Initialize();
        }

        protected override void LoadContent()
        {
            spriteBatch = new SpriteBatch(GraphicsDevice);
        }

        protected override void UnloadContent()
        {

        }

        protected override void Update(GameTime gameTime)
        {
            if (GamePad.GetState(PlayerIndex.One).Buttons.Back == ButtonState.Pressed)
                this.Exit();

            base.Update(gameTime);
        }

        protected override void Draw(GameTime gameTime)
        {
            GraphicsDevice.Clear(Color.CornflowerBlue);

            base.Draw(gameTime);
        }
    }

Lets break down the above methods

Constructor
Used to initialize basic variables that are needed at the beginning of the game.  In the constructor you will find the class level variable GraphicsDeviceManager being initialised.  This variable is used to retrieve data from the GraphicsDevice which could be from an XBox, PC or Windows Phone 7

Initialize
Used to initialize the game objects (Sprites, Fonts, etc..)

LoadContent
Used to load information from the content pipeline.  Example: Retrieving image and loading in a variable of type Texture2D.  In the generated code, the LoadContent is used to initialize the class level variable spriteBatch.  This object (of type SpriteBatch), will be used in the method in order to load Sprites.

UnloadContent
Used to Dispose of any objects created in the game.  At first you can ignore this method as the .NET Framework has a function called GarbageCollector which will dispose of any unneeded objects.

Update
One of the most important methods.  This is where all the game logic will occur.  In order to have a moving sprite in our game, we first need to update its location and this is the place to do it.   The Update (together with the Draw) is part of the game loop and is an essential part of XNA game development.  When creating a new project, you will find a few lines of code that are used in order to check if the player has pressed the back button on the controller and if yes, exit the game.

Draw

Also a very important method.  As explained above, in order to have a moving sprite we will need to update its location but in order to see it move, we will then need to draw it to the screen each time the game loop is called.  This is what happens in the Draw method.  It is important for the sake of code readability and robustness to keep only drawing code in the Draw method (kind of obvious isn't it?).  In the auto-generated code, you will find a line of code which fills the screen with the color blue using the GraphicsDevice object described earlier.

That's it for the basics now.  In the next tutorial we will start drawing some stuff :)

Please let me know if you found out this tutorial helpful :)

iTextSharp: Disable PDF Printing

It took me ages to find out how to disable printing on a PDF file. You need to encrypt the file in order set copying and printing settings.  Here's how:

System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
writer.SetEncryption(null, encoding.GetBytes("12345678"), PdfWriter.ALLOW_COPY, PdfWriter.STRENGTH40BITS);

And this is how to enable printing if you still want the pdf to be encrypted.

System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
writer.SetEncryption(null, encoding.GetBytes("12345678"), PdfWriter.ALLOW_COPY | PdfWriter.ALLOW_PRINTING, PdfWriter.STRENGTH40BITS);

ASPxGridView: How To - Creating custom delete column with image at runtime

It took me a while to find a way to a add a delete column with an embedded image so I thought it would be better in this blog for easy reference. Naturally, I wanted the confirm message before deleting as well. There seems to be 2 ways to do this:

Using the in-built Delete button: GridViewCommandColumn.DeleteButton

GridViewCommandColumn commandColumn = new GridViewCommandColumn();
commandColumn.Width = new Unit("40");
commandColumn.ButtonType = ButtonType.Image;
commandColumn.DeleteButton.Image.IsResourcePng = true;
commandColumn.DeleteButton.Image.Url = Page.ClientScript.GetWebResourceUrl(typeof(xxx.Controls.DynamicGrid), @"xxx.Controls.DeleteBin.png");
commandColumn.DeleteButton.Visible = true;
commandColumn.DeleteButton.Text = "Delete"; //Tooltip
                    
gvMyDataGrid.Columns.Add(commandColumn); // add custom column to grid
gvMyDataGrid.SettingsBehavior.ConfirmDelete = true; // display popup confirmation upon clicking delete button
gvMyDataGrid.SettingsText.ConfirmDelete = "Are you sure you want to delete this item?"; //Text you want to be displayed.  This can also be retrieved from the resource file

Or creating your own button from scratch

GridViewCommandColumn commandColumn = new GridViewCommandColumn();
commandColumn.Width = new Unit("40");
commandColumn.ButtonType = ButtonType.Image;

GridViewCommandColumnCustomButton deleteButton = new GridViewCommandColumnCustomButton();
deleteButton.Image.IsResourcePng = true;
deleteButton.Image.Url = Page.ClientScript.GetWebResourceUrl(typeof(xxx.Controls.DynamicGrid), @"xxx.Controls.DeleteBin.png");
deleteButton.Visibility = GridViewCustomButtonVisibility.AllDataRows;
deleteButton.Text = "Delete";
deleteButton.ID = "cmdDelete";

commandColumn.CustomButtons.Add(deleteButton); // add custom button to new command column
gvMyDataGrid.Columns.Add(commandColumn);
//We need the next line of code to add the confirmation popup ourselves.  Notice the 'cmdDelete' parameter which is the custom delete button's ID above.
gvMyDataGrid.ClientSideEvents.CustomButtonClick = "function (s, e) { if (e.buttonID == 'cmdDelete'){ e.processOnServer = confirm('Are you sure you want to delete this item?'); }}";

//confirm() method will return the user's selection:  true if OK, false if cancel.  Therefore if return this value and set it to e.processOnServer, this will tell the button to not commit a callback if the user selects 'cancel'

In both examples I am using an embedded image as I wanted the grid to be used by just adding a reference to the DLL and not having to add the image to every project.

In order to embed the image:

1. Go to the image's Properties.  
2. Set Build Action to Embedded Resource
3. Go to AssemblyInfo.cs
4. Add using System.Web.UI; 
5. Add [assembly: WebResource("YourNamespace.ImageFileName.png", "image/png")]
6. Get the image url as described above Page.ClientScript.GetWebResourceUrl(typeof(xxx.Controls.DynamicGrid), @"xxx.Controls.DeleteBin.png");

Conditional Filtering: Using conditions in T-SQL WHERE clauses

Many of us have some time or another come across the scenario where we would like to use if statements in a where clause.

Here are 3 ways (that I know of) on how you can conditionally filter your where clause in T-SQL.

Using multiple queries seperated by IF Clauses

DECLARE @ContactID int 
SET @ContactID = 5

IF(@ContactID IS NULL)
BEGIN
 SELECT * FROM [AdventureWorks].[Person].[Contact]
END
ELSE
BEGIN
 SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = @ContactID
END

This must be one of the easiest ways to retrieve data depending on a variable and can also be the fastest way also.  Unfortunately, if you have a lot of variables, this could become a maintenance nightmare.  Imagine if you have to add another variable, the query will have to be repeated again, not to mention that if the query has to be amended, this needs to be done for every occurrence.

Using the Case expression

DECLARE @ContactID int
SET @ContactID = 5

SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = CASE WHEN @ContactID IS NULL THEN ContactID ELSE @ContactID END

Although not so straightforward, many developers opt to use this as it is much more readable and you only need one query.  I have used this for a number of years and found it extremely useful until I found out about the last option

Using boolean logic

DECLARE @ContactID int

SET @ContactID = NULL

SELECT * FROM [AdventureWorks].[Person].[Contact] 
WHERE @ContactID IS NULL OR ContactID = @ContactID

This method only uses Ands & Ors in order to get the result.  It takes a while to get used to but I highly recommend this method as it is much faster due to the conversion to simple boolean logics and it is also readable and easily maintained.

For more information visit http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

List of useful Regular expressions (regex)

Positive integers

^[1-9]+[0-9]*$

Positive decimal values

(^\d*\.?\d*[0-9]+\d*$)|(^[0-9]+\d*\.\d*$)

Percentage (2 decimal places)

^-?[0-9]{0,2}(\.[0-9]{1,2})?$|^-?(100)(\.[0]{1,2})?$

List of semi-colon seperated email addresses

^([\w\.*\-*]+@([\w]\.*\-*)+[a-zA-Z]{2,9}(\s*;\s*[\w\.*\-*]+@([\w]\.*\-*)+[a-zA-Z]{2,9})*)$

German Date (dots instead of slashes)

^(((0[1-9]|[12]\d|3[01]).(0[13578]|1[02]).(\d{2}))|((0[1-9]|[12]\d|30).(0[13456789]|1[012]).(\d{2}))|((0[1-9]|1\d|2[0-8]).02.(\d{2}))|(29.02.((0[48]|[2468][048]|[13579][26])|(00))))$

E-mail address

^[\w-]+(\.[\w-]+)*@([a-z0-9-]+(\.[a-z0-9-]+)*?\.[a-z]{2,6}|(\d{1,3}\.){3}\d{1,3})(:\d{4})?$

Restoring database: Exclusive access could not be obtained because the database is in use

USE [master]
ALTER DATABASE [DatabaseName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Restore database here.. (hopefully) At first it didn't work, then i refreshed databases, logged out and in again and tried again. Then it worked.

USE [master]
ALTER DATABASE [DatabaseName]
SET MULTI_USER;

How to get table row count for all tables in a database in T-SQL

The following sql query displays all the tables in a database sorted by their rowcount (largest first)

SELECT o.name, 
 ddps.row_count 
FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 
 AND o.is_ms_shipped = 0 
ORDER BY row_count DESC

The elapsed time for the above query on the Adventure Works database was only 3ms!! While if we use the COUNT function on the largest table in the database

SELECT COUNT(*) FROM Sales.SalesOrderDetail

The above query took 12ms to get the count for just one table. Obviously, the biggest limitation on using the former approach is that you can't filter data using a where clause but if you need to count all the records, i suggest trying the first query :)

For more details, check this detailed blog entry

Useful Devexpress links

These are just some links which I use regularly:

From Mehul Harry's Blog
Top 7 ASPxGridView Help Links
http://community.devexpress.com/blogs/aspnet/archive/2007/10/26/aspxgridview-how-to-find-the-answers.aspx

ASPxGridView Documentation
http://documentation.devexpress.com/#AspNet/DevExpressWebASPxGridViewASPxGridViewMembersTopicAll

Client-side ASPxGridView Documentation (ASPxClientGridView)
http://documentation.devexpress.com/#AspNet/DevExpressWebASPxGridViewScriptsASPxClientGridViewClassMethodsTopic

ASPxGridView Demo site
http://demos.devexpress.com/ASPxGridViewDemos/

Search Devexpress
http://search.devexpress.com

Error: List<> does not contain a definition for 'Sum'

Error 'System.Collections.Generic.List<>' does not contain a definition for 'Sum' and no extension method 'Sum' accepting a first argument of type 'System.Collections.Generic.List<>' could be found (are you missing a using directive or an assembly reference?)

This error cropped up when converting a solution from .NET 2 to .NET 4.  Just add

using System.Linq;

Create Simple T-SQL Cursor [example]

DECLARE curName CURSOR FOR SELECT     pk FROM tb_Table
      OPEN curName 
      
      FETCH NEXT FROM curName INTO @pk
      
      WHILE @@FETCH_STATUS = 0
      BEGIN
      
            --insert code here
            FETCH NEXT FROM curName INTO @pk --do not forget this line.. will cause an infinite loop
      END
      
CLOSE curName 
DEALLOCATE curName 

Could not load file or assembly System.Data.Linq, Version=4.0.0.0

1. Make sure .NET Framework 4 is installed on IIS Server
2. IIS5: You need to changed ASP.NET version to 4 by right clicking on the website and click properties > ASP.NET tab.
3. IIS7: Check that Application pool is running ASP.NET 4.0 and that the website is running on that application pool

Validate Devexpress controls at client-side using javascript

In order to validate devexpress controls in the page using javascript, use the following code:

ASPxClientEdit.ValidateGroup(null); or ASPxClientEdit.ValidateGroup('validationgroup');

This method returns true if all controls on the current page with the specified validationgroup pass validation and false if they don't

Example:

<dx:ASPxButton ID="btnAdd" runat="server" Text="Add" AutoPostBack="false">
     <ClientSideEvents Click="function(s,e){ if(ASPxClientEdit.ValidateGroup(null)) { alert('Validation OK'); } }" />
</dx:ASPxButton>

or using a normal button

<asp:Button ID="btnAdd" runat="server" Text="Add" OnClientClick="if(ASPxClientEdit.ValidateGroup(null)) { alert('Validation OK'); return false; } " />

Don't forget to use return false; OR e.processOnServer=false;  OR AutoPostBack=false; if you do not want the button to cause a postback.

Check Online Documentation for more details

.NET Framework 4: a potentially dangerous request.form value was detected from the client .

Put

<pages validateRequest="false" /> 

inside  <system.web> or encode the user's input by using

Server.HtmlEncode( TextBox1.Text );

and if you are running on .NET 4, insert the following

<httpRuntime requestValidationMode="2.0" />

in web.config

Unlocking user in T-SQL

If a user gets locked after a number of failed attempts, just run the following t-sql.

ALTER LOGIN userToUnlock WITH PASSWORD = 'themightypassword' UNLOCK ;

TRUNCATE all tables in a database

Use the stored procedure sp_MSForEachTable

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

You can use this stored procedure to execute any sql code for each table in a database. For example, you could use it to get the row count for each table in the database

EXEC sp_MSForEachTable 'SELECT COUNT(*) as ''?'' FROM ?'

Finding text or field in T-SQL Stored Procedures, Tables, Views, etc..

I highly recommend to install this addon developed by Red Gate to SQL Management Studio.  You can search any text in all database objects (stored procedures, tables, views, triggers, functions and others).  I find it especially useful to find a stored procedure instead of using the inbuilt Management Studio filtering function. You can then double click it, and you will be redirected to the stored procedure.  I also use it when I have to rename a field from a table and have to find which stored procedures are currently listing that field in order to rename it.

I use it all the time, it's a great time-saver and it's free!

http://www.red-gate.com/products/sql-development/sql-search/

Here's how it looks like:

If you don't have the time to install, I used this stored procedure which would list the objects which match text that i am looking for together with the number of times found

CREATE PROCEDURE [dbo].[spr_FindText]
 @text nvarchar(max)
AS

select object_name(id) AS [Object], count(*) AS [Count]
from syscomments
where text like '%' + @text + '%'
GROUP BY object_name(id)
ORDER BY Object

Simple Pong Game in XNA C#

I wanted to try out XNA and what better way than to try it out on one of the earliest arcade video games ever?

You can download the game from here

The game still needs some polishing but it's quite playable. I guess in the next version I will add a menu in order to pause the game and maybe tweak the gameplay a bit :)

What enhancements would you do to the original Pong? Please comment if you find any problems with the setup or with the game :)

Apple's work ethics

Interesting.. and I thought that Microsoft was more 'evil' than Apple.  The 2011 WORLD’S MOST ETHICAL COMPANY actually says the contrary.  There's no mention of Apple or Google in the list


Apple - Beast File from Duncan Elms on Vimeo.

The original post can be found here http://thecuriousbrain.com/?p=22685

Unknown Return Type

Unknown Return Type: The return types for the following stored procedures could not be detected.  Set the return type for each stored procedure in the properties window.

The error was displayed when trying to drag a stored procedure to the LINQ designer file. (DBML).

The cause of the error was the use of a temporary table inside the stored procedure (#tmpTable)

Changing the temporary table to table variable solved the problem

A more detailed explanation can be found here
Ritesh Kesharwani: Error: Unknown Return Type, The return types for the following stored procedures could not be detected….(LINQ).

LinqToSQL - Single method missing

'System.Data.Linq.Table< >' does not contain a definition for 'Single' and no extension method 'Single' accepting a first argument of type 'System.Data.Linq.Table< >' could be found (are you missing a using directive or an assembly reference?)

Just add

using System.Linq;

Arithmetic overflow error converting numeric to data type numeric

Today I came across the above error. Unfortunately for me, it was nested inside a cursor inside a very long stored procedure and had to debug to find the cause.  The error seems a bit strange at first as both the datatypes mentioned are numeric. It turned out to be the precision of a decimal variable.  Let me give a practical example:

DECLARE @a decimal(18,6)

DECLARE @b decimal(18,12)

SET @a = 1336200.000000

SET @b = @a -- gives ‘Arithmetic overflow error converting numeric to data type numeric’

I think that unconsciously i assumed that 18, 12 would be bigger than 18,6. The first digit represents the number of digits before and after the decimal point, while the second digit represents the number of digits after the decimal point. Thus, (18, 12) stores less digits before the decimal in this case, which results in the above error