SQL Server , How to search for text in Stored Procedures

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.

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

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

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

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

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:

<script type="text/javascript">

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

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

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

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

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

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

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

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

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

(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

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

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.

 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

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

 

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

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

Javascript Debugging

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

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

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

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

You can find it here

ASP .NET and ATLAS videos

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.

«November»
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345