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 ?'