AngelEyes on .Net

SQL Server , How to search for text in Stored Procedures

AngelEyes — Wed, 16 Sep 2009 11:32:28 GMT

In this example, I searched for any SP containing the text LoadByIP:

SELECT Distinct SO.Name

FROM sysobjects SO (NOLOCK)

INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID

AND SO.Type = 'P'

AND SC.Text LIKE '%LoadByIP%'

ORDER BY SO.Name

Update: this was suggested by nts (see comments):

SELECT routine_name, routine_definition

FROM information_schema.routines

WHERE routine_definition LIKE '%LoadByIP%'

AND routine_type = 'PROCEDURE'

Both work well :-)

Regular expression for inverting "let" statements.

AngelEyes — Sun, 09 Aug 2009 14:09:01 GMT

This regular expression isn't standard, it's Visual Studio syntax-specific, it should invert simple statements such as:

Language.Text = record.Language;
into
record.Language= Language.Text ;

The expression itself is: ^{.+}={.+};

A simple custom control, inheriting from BoundField

AngelEyes — Wed, 01 Jul 2009 08:34:22 GMT

I wanted to display Boolean data in a Gridview and didn't like that disabled checkbox look. After searching online, I found some people solved this with Javascript, including the MS AJAX extentions toolkit which has an extender - but I didn't want to have a lot of checkboxes in my html that got replaced on the client's side with javascript that had to loop over them all. I just wanted the images.

The quick-and-dirty way to solve this would have been to create a template column for my Gridview with an image control in it and override the Grid's data binding method to set the ImageUrl property.

But that would have to be done everytime, for every Gridview I'd want to use.

So I wrote a simple custom control, inheriting from BoundField (System.Web.UI.WebControls.BoundField) and now I can use it in any website, in any Gridview.

I liked it - hope you do too.

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.ComponentModel;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace CustomBoundField

{

///<summary>

/// Summary description for BoundCheckImageField

///</summary>

public class BoundCheckImageField : System.Web.UI.WebControls.BoundField

{

#region Properties

///<summary>

/// Gets or sets the url of the image to display for a 'true' value

///</summary>

[DefaultValue("")]

public string TrueValueImageUrl

{

get

{

object val = base.ViewState["TrueValueImageUrl"];

if (val != null)

{

return (string)val;

}

return string.Empty;

}

set

{

if (!object.Equals(value, base.ViewState["TrueValueImageUrl"]))

{

base.ViewState["TrueValueImageUrl"] = value;

this.OnFieldChanged();

}

///<summary>

/// Gets or sets the url of the image to display for a 'false' value

///</summary>

[DefaultValue("")]

public string FalseValueImageUrl

{

get

{

object val = base.ViewState["FalseValueImageUrl"];

if (val != null)

{

return (string)val;

}

return string.Empty;

}

set

{

if (!object.Equals(value, base.ViewState["FalseValueImageUrl"]))

{

base.ViewState["FalseValueImageUrl"] = value;

this.OnFieldChanged();

}

#endregion

protected override void InitializeDataCell(DataControlFieldCell cell, DataControlRowState rowState)

{

//base.InitializeDataCell(cell, rowState);

if (!string.IsNullOrEmpty(this.DataField))

{

Image img = new Image();

img.DataBinding += new EventHandler(this.OnImageDataBind);

cell.Controls.Add(img);

}

protected void OnImageDataBind(object sender, EventArgs e)

{

Control control = (Control)sender;

Control namingContainer = control.NamingContainer;

object dataValue = this.GetValue(namingContainer);

if (control is Image)

{

if ((Boolean)dataValue)

{

(control as Image).ImageUrl = this.TrueValueImageUrl;

}

else

{

(control as Image).ImageUrl = this.FalseValueImageUrl;

}

else

{

base.OnDataBindField(sender, e);

}

keyboard shortcut collapse all nodes in the Solution Explorer

AngelEyes — Mon, 29 Jun 2009 04:26:17 GMT

How to quickly collapse all nodes in the Solution Explorer (To my knowledge, this works with Visual Studio 2005 as well as Visual Studio 2008):

1. Create a macro (code taken from Jay Harris's blog. Update: I cannot find that post anymore. Good thing I copied it here!)

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics

Public Module CollapseAll
    Sub CollapseAll()
        ' Get the the Solution Explorer tree
        Dim UIHSolutionExplorer As UIHierarchy
        UIHSolutionExplorer = DTE.Windows.Item(Constants.vsext_wk_SProjectWindow).Object()
        ' Check if there is any open solution
        If (UIHSolutionExplorer.UIHierarchyItems.Count = 0) Then
            ' MsgBox("Nothing to collapse. You must have an open solution.")
            Return
        End If
        ' Get the top node (the name of the solution)
        Dim UIHSolutionRootNode As UIHierarchyItem
        UIHSolutionRootNode = UIHSolutionExplorer.UIHierarchyItems.Item(1)
        UIHSolutionExplorer = Nothing
        UIHSolutionRootNode.DTE.SuppressUI = True
        ' Collapse each project node
        Dim UIHItem As UIHierarchyItem
        For Each UIHItem In UIHSolutionRootNode.UIHierarchyItems
            'UIHItem.UIHierarchyItems.Expanded = False
            If UIHItem.UIHierarchyItems.Expanded Then
                Collapse(UIHItem)
            End If
        Next
        ' Select the solution node, or else when you click
        ' on the solution window
        ' scrollbar, it will synchronize the open document
        ' with the tree and pop
        ' out the corresponding node which is probably not what you want.
        UIHSolutionRootNode.Select(vsUISelectionType.vsUISelectionTypeSelect)
        UIHSolutionRootNode.DTE.SuppressUI = False
        UIHSolutionRootNode = Nothing
    End Sub

    Private Sub Collapse(ByVal item As UIHierarchyItem)
        For Each eitem As UIHierarchyItem In item.UIHierarchyItems
            If eitem.UIHierarchyItems.Expanded AndAlso eitem.UIHierarchyItems.Count > 0 Then
                Collapse(eitem)
            End If
        Next
        item.UIHierarchyItems.Expanded = False
    End Sub

End Module

2. As shown here, create a new keyboard shortcut for your new macro:

•    Choose Tools/Options and select Environment/Keyboard.
•    Type in something into "Show commands containing" to get a list of matching commands. If there is already a shortcut for the selected command, it’ll be displayed in "Shortcuts for selected command".
•    To assign a new shortcut to the selected command, put the cursor in "Press shortcut keys" and press the shortcut key or key combinations desired.

Setting up Right-to-Left menu, with ASP .Net CSS friendly control adapters

AngelEyes — Mon, 29 Jun 2009 01:15:49 GMT

A great article, albeit in Hebrew, explains How to align RTL (Right-to-left) ASP.Net 2.0 Menu with CSS Control Adapters.

Note, in particular, this change:

  /* Tier 3+ */
.SimpleEntertainmentMenu ul.AspNet-Menu ul ul
{
    top: -0.5em;    
    left: 6em;
}

Changed to:

  /* Tier 3+ */
.SimpleEntertainmentMenu ul.AspNet-Menu ul ul
{
    top: -0.5em;    
    right: 6em;
}

Manually postback

AngelEyes — Sat, 27 Jun 2009 12:16:26 GMT

I wanted to post back after an auto complete extender did its job. This was the first version (the auto complete extender showed student names).

Server-side:

protected override void OnPreRender(EventArgs e)

{

base.OnPreRender(e);

if (!Page.ClientScript.IsClientScriptBlockRegistered("postBackStudentFound"))

{

Page.ClientScript.RegisterClientScriptBlock(this.Page.GetType(), "postBackStudentFound",

@"<script type=""text/javascript"">function postBackStudentFound() {" +

Page.ClientScript.GetPostBackEventReference(this.Page, StudentFoundEventArg) + "; } </script>");

}

if (Page.IsPostBack)

{

if (Request["__EVENTARGUMENT"] == StudentFoundEventArg)

{

OnStudentFound();

}

Client-side:

function itemSelected( source, eventArgs)

{

var fullString = eventArgs.get_value();

var valuesArray = fullString.split("|");

var id = valuesArray[0];

var key = valuesArray[2];

$get('<%= txtID.ClientID %>').value = id;

postBackStudentFound();

}

</script>

Example: how to sort and get top iNumOfRecords from a DataTable, using a DataView

AngelEyes — Wed, 30 Jan 2008 16:49:40 GMT

DataView dvSortTotalUpdates = new DataView();

dvSortTotalUpdates = dtAllUpdates.DefaultView;

dvSortTotalUpdates.Sort = "UploadDate";

DataTable dtGetBySize = dtAllUpdates.Clone();

for( int i = 0; i < iNumOfRecords; i++ )

{

dtGetBySize.ImportRow(dvSortTotalUpdates[i].Row);

}

return (dtGetBySize);

Using a MemoryStream with GZipStream

AngelEyes — Thu, 24 Jan 2008 16:44:25 GMT

This is a very subtle yet annoying bug. And it's hard to find.
Fortunately, I found help on the BCL Team Blog.

Just in case (I've had posts that I linked to disappear in the past) I'll copy here what they say:

We’ve seen cases where our customers have run into issues when using a MemoryStream with GZip compression. The problem can be frustrating to debug and I thought I’ll blog about it in the hope that others would avoid a similar issue. The code for this looks like this;

Byte[] compressedBuffer;

MemoryStream stream = new MemoryStream();

using (GZipStream zip = new GZipStream(stream, CompressionMode.Compress))

{

//compress data

...

//Dont get the MemoryStream data before the GZipStream is closed since it doesn’t yet contain complete compressed data.

//GZipStream writes additional data including footer information when its been disposed

compressedBuffer = stream.ToArray(); //WRONG

}

// CORRECT CODE: call compressedBuffer = stream.ToArray() here after the GZipStream is disposed

The problem arises because the data in MemoryStream is not complete when ToArray is called before the GZipStream is closed. We will write any remaining compressed data and footer information to GZipStream when its being closed. The data in the MemoryStream is still accessible even after its been closed. Both ToArray and GetBuffer methods will return valid data after the MemoryStream has being disposed. This is not so much an issue when another stream like FileStream is used in compression since there is generally time before decompression when a file is used and its ok for the file to be re-opened when that happens.

SQL Server aggregated report example

AngelEyes — Sun, 16 Sep 2007 14:16:31 GMT

An example of how to make an aggregated report, grouping together by values:

select count(*) as total ,

count(case WHEN others = 1 THEN 1 END) as [one url],

count(case WHEN others = 2 THEN 1 END) as [two urls],

count(case WHEN others = 3 THEN 1 END) as [three urls],

count(case WHEN others > 3 THEN 1 END) as [all the rest]

from #temp2

Moving the viewstate to the bottom

AngelEyes — Fri, 01 Jun 2007 09:48:11 GMT

I was looking for the code I once used to put the viewstate tag at the bottom of the form tag, instead of the top. I found some code, used an automated VB-to-C# converter, and the results were not good.

I'm not sure if this is a difference between C# and VB or just a bug posted on Scott's blog, but the insertion place was wrong. So I changed that (removed the -1) and also changed the way he checked if the viewstate object was found, so that if not, we'd still have the original html, before removing the tag.

Edit: I now just saw that someone commented on the -1 issue on that page. Anyway, here's my code:

        /// <summary>
        /// This method overrides the Render() method for the page and moves the ViewState
        /// from its default location at the top of the page to the bottom of the page. This
        /// results in better search engine spidering.
        /// </summary>
        /// <param name="writer"></param>
        protected override void Render(System.Web.UI.HtmlTextWriter writer)
        {
            System.IO.StringWriter stringWriter = new System.IO.StringWriter();
            HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
            base.Render(htmlWriter);
            string html = stringWriter.ToString();
            string newHtml = string.Empty;

            int StartPoint = html.IndexOf("<input type=\"hidden\" name=\"__VIEWSTATE\"");
            if ((StartPoint >= 0))
            {
                // does __VIEWSTATE exist?
                int EndPoint = (html.IndexOf("/>", StartPoint) + 2);
                string ViewStateInput = html.Substring(StartPoint, (EndPoint - StartPoint));

                //I'm using newHtml so that if we cannot find the viewsource's right location, we still have the original html
                newHtml = html.Remove(StartPoint, (EndPoint - StartPoint));
                int FormEndStart = (newHtml.IndexOf("</form>"));
                if ((FormEndStart >= 0))
                {
                    //success - so replace the string, putting the viewstate at the end.
                    html = newHtml.Insert(FormEndStart, ViewStateInput);
                }
            }
            writer.Write(html);
        }

Put multiple table data into one and add special data plus identity column - SQL Server

AngelEyes — Thu, 12 Apr 2007 06:11:00 GMT

Say you want to combine the data from 3 tables - a1, a2, and a3.

Now, you also want to add a special field, let's say it's the year, and it's 2001 for a1 2002 for a2 etc'

In addition, you want the resluting table to have an identity column. What do you do?

This is a real - life scenario which came up today, and here's the solution:

select IDENTITY(int, 1,1) AS id_num , *
into a
from
(
select 2001 as year ,* from a1
union
select 2002,* from a2
union
select 2003,* from a3
) as t

select * from a

SQL Server split function

AngelEyes — Thu, 12 Apr 2007 03:54:00 GMT

Credits due: look at the second post on http://searchvb.techtarget.com/tip/0,289483,sid8_gci932171,00.html

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##')  --note second item has embedded #
select * from fnSplit('1 22 333 444  5555 666', ' ')

Making sense of ASP.Net Paths

AngelEyes — Thu, 22 Mar 2007 05:05:00 GMT

Making sense of ASP.Net Paths - this is a link to remember!

How to choose a class for reading and/or writing XML

AngelEyes — Sun, 11 Mar 2007 23:23:00 GMT

(taken from http://msdn2.microsoft.com/en-us/library/ms998559.aspx)

Choose the Appropriate XML Class for the Job

To help you choose the appropriate .NET Framework class to process XML, consider the following guidelines:

· Use XmlTextReader to process XML data quickly in a forward, read-only manner without using validation, XPath, and XSLT services.

· Use XmlValidatingReader to process and to validate XML data. Process and validate the XML data in a forward, read-only manner according to an XML schema or a DTD.

· Use XPathNavigator to obtain read-only, random access to XML data and to use XPath queries. To create an XPathNavigator object over an XML document, you must call the XPathDocument.CreateNavigator method.

· Use XmlTextWriter to write XML documents. You cannot use XmlTextWriter to update XML documents.

· Use the XmlTextReader and XmlTextWriter, in combination, for simple transformations rather than resorting to loading an XmlDocument or using XSLT. For example, updating all the price element values in a document can be achieved by reading with the XmlTextReader, updating the value and then writing to the XmlTextWriter, typically by using the WriteNode method.

· Use the XmlDocument class to update existing XML documents, or to perform XPath queries and updates in combination. To use XPath queries on the XmlDocument, use the Select method.

· If possible, use client-side XML processing to improve performance and to reduce bandwidth.

OpenSSL for Windows XP

AngelEyes — Thu, 08 Mar 2007 01:22:00 GMT

Just follow this link: http://www.slproweb.com/products/Win32OpenSSL.html

Currently, the latest version for the OpenSSL is 0.98e

Edit: I just saw that now, the recommended version is v0.9.8g

A good explanation on how to use VeryByCustom Caching

AngelEyes — Thu, 15 Feb 2007 05:49:00 GMT

Right there: http://www.aprogrammersjournal.com/article.aspx?id=58

The important part is this:

application level parameters

Application level parameters such as session variables or client side cookies are parameters that are page independent. To cach multiple versions of a page based on application level parameters you can use VarByCustom and the global.asax file.

In my case the problem I was faced with was using Forms.Authentication in combination with output caching. If user A logged into my site I did not want it to "Welcome User A" to User B.

he first thing we will need to do is set the location of the output cache to "client or "server, this ensures that the page will not be cached by a proxy server. To learn more about caching location please visit: This Link

Secondly you will need to set up an override function in your global.asax file

<Script language="VB" runat="server">

Public Overrides Function GetVaryByCustomString( _
ByVal context As System.Web.HttpContext, _
ByVal custom As String) As String

Select custom
     Case "username"
               Return Context.User.Identity.Name
     Case Else
               Return MyBase.GetVaryByCustomString(context, custom)
End Select

End Function

</script>

And lastly specify your custom variable:

<%@ OutputCache Duration = "1200" VaryByParam="*" Location="Server" VaryByCustom="username" %>

So now if a user logs in he will receive his own custom cached page, as he browses around the site and hits the same pages, he will receive the pages that were cached for him.

Error code 0x8013134b - unable to attach to process.

AngelEyes — Thu, 11 Jan 2007 03:37:00 GMT

Error code 0x8013134b - unable to attach to process.

The website was configured to use ASP .Net 2.0 instead of 1.something.

check it out

ELMAH for ASP.net 2.0released - recommended

AngelEyes — Thu, 01 Jun 2006 12:52:00 GMT

My first post, here's a recommendation:

Go and download the .Net 2.0 version of ELMAH.

Very easy and quick to include in your projects, and very useful!

The GotDotNet project for ELMAH is here

Oh, yeah, ELMAH stands for “Error Logging Modules and Handlers”

Update: since GotDotNet was phased out, the project moved to http://www.elmah.org/

.Net Framework 3.0

AngelEyes — Wed, 29 Nov 2006 22:37:00 GMT

I started looking at the .Net Framework 3.0 and I know I would like MS to come with how-to videos for it, like they did for ASP .Net 2.0

also, I'm wondering about the changes it brings for the use of Microsoft Atlas (annoyingly changed to Microsoft Ajax extensions) and the Friendly CSS providers.

Javascript debugging tool

AngelEyes — Thu, 19 Oct 2006 22:27:00 GMT

It's a firefox extention, but hey, it helps!

Javascript Debugging

Attribute '(whatever)' is considered outdated. A newer construct is recommended.

AngelEyes — Thu, 19 Oct 2006 21:10:00 GMT

VS doesn't tell you what the newer construct is, which is annoying. But here's a useful post to the asp.net forum.

Yeppee! MicroSoft will use my code... CSS friendly GridView problems showing selected rows

AngelEyes — Fri, 15 Sep 2006 10:54:00 GMT

I found a problem in some MS written code within the CSS Friendly adapters. Wasn't lazy, went and found the specific problem, and fixed it. The result - my code will be incorporated in the next version.

This is it: http://forums.asp.net/thread/1399881.aspx (CSS friendly GridView problems showing selected rows)

Yeah, it's a small thing, but still!

A silly bug in ASP .NET 2.0 Wizard control

AngelEyes — Sun, 10 Sep 2006 22:16:00 GMT

If you have the property OnFinishButtonClick in your wizard, something like this:

OnFinishButtonClick=”Wizard1_FinishButtonClick”

and the sub FinishButtonClick is set to:

Handles Wizard1.FinishButtonClick

Then the sub will be called twice!

CSS friendly adapters Beta 2.0 is out

AngelEyes — Sat, 09 Sep 2006 17:45:00 GMT

You can find it here

ASP .NET and ATLAS videos

AngelEyes — Tue, 05 Sep 2006 22:07:00 GMT

This is something I have to google everytime, so it's about time to have a link for it:

ASP .NET videos

Hope the link doesn't change. If it does though, this one might work.