Search SQL Server for stored procedures in all databases containing text

Search SQL Server for stored procedures in all databases containing text

USE [master]
GO

CREATE PROCEDURE sp_SearchProcs  
(  
 @SearchText VARCHAR(1000)  
)  
AS  
BEGIN  
 DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR    
    SELECT [name]    
    FROM master..sysdatabases   
    WHERE [name] NOT IN ('model', 'tempdb')   
    ORDER BY [name]   
        
 DECLARE @DB sysname    
  
 OPEN curDB    
 FETCH NEXT FROM curDB INTO @DB    
 WHILE @@FETCH_STATUS = 0    
    BEGIN    
     DECLARE @SQL NVARCHAR(MAX) = 'USE [' + @DB +'] SELECT Distinct SO.Name, SC.Text, SS.Name AS [Schema], ''' + @DB + ''' AS [Database] FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID INNER JOIN sys.objects SO2 (NOLOCK) ON SO.id = SO2.object_id INNER JOIN sys.schemas SS ON SO2.schema_id = SS.schema_id WHERE SO.Type = ''P'' AND SC.Text LIKE ''%' + REPLACE(@SearchText, '''', '''''') + '%'' ORDER BY SO.Name'  
     BEGIN TRY  
       exec sp_executesql @SQL  
     END TRY  
     BEGIN CATCH  
     END CATCH  
     FETCH NEXT FROM curDB INTO @DB    
    END    
       
 CLOSE curDB    
 DEALLOCATE curDB  
END

Here's how to use it:

sp_SearchProcs 'insert%into%mytable'

Wildcard session cookies

Some browsers use .domain.com as wildcard, others use domain.com
It is common practice for a user to forget the www and most servers will return the same page for www or non-www.
If a user vists the non-www version of a website & the server returns a sessionID cookie, the browser may consider it a wildcard cookie, thus it will use that same cookie for ALL request to ANY subdomain of that site. Normally, each subdomain would have a separate set of cookies, so the server would generate a different sessionId for each.

Here's a method you can call in the Application_EndRequest method of the global.asax.cs to prevent this from happening.

        public static void RemoveWildcardSessionCookies(this HttpApplication application)
        {
            if (System.Web.HttpContext.Current.Request.IsLocal)
                return;

            string host = System.Web.HttpContext.Current.Request.Url.Host;
            int countOfPeriods = host.Count(x => x == '.');

            HttpCookie sessionCookie = application.Request.Cookies["ASP.NET_SessionId"];
            if (sessionCookie != null && countOfPeriods <= 1)
            {
                sessionCookie.Expires = DateTime.Now.AddDays(-1);
                sessionCookie.Value = "";
                if (System.Web.HttpContext.Current.Session != null)
                    System.Web.HttpContext.Current.Session.Abandon();
            }
        }

Dynamically compile code at runtime

Compile Lambda or Code String to MethodInfo or Action or Func:

using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;

namespace CodeCompiler
{
  public class CompilationException : Exception
  {
    public List<CompilerError> Errors { get; private set; }
    public CompilationException(CompilerErrorCollection errors) : base("Error compiling expression")
    {
      Errors = new List<CompilerError>();
      foreach (CompilerError error in errors)
        Errors.Add(error);
    }
  }

  public class Compiler
  {
    public List<string> ReferenceDLLs { get; private set; }
    public List<Assembly> ReferenceAssemblies { get; private set; }

    public Compiler()
    {
      ReferenceDLLs = new List<string> { "System.dll", "System.Core.dll", "System.Data.dll", "System.Data.DataSetExtensions.dll", "System.Xml.dll", "System.Xml.Linq.dll" };
      //ReferenceDLLs = AppDomain.CurrentDomain.GetAssemblies().Where(x => !x.IsDynamic).Select(x => x.Location).ToList();
      ReferenceAssemblies = new List<Assembly>();
    }

    public Assembly CompileCSFile(string csFilePath)
    {
      using (StreamReader reader = new StreamReader(csFilePath))
        return compileString(reader.ReadToEnd());
    }

    private MethodInfo compileStringToMethodInfo(string code)
    {
      Assembly assembly = compileString(code);
      return assembly.GetType("FakeNamespace.FakeClass").GetMethod("MethodResult", BindingFlags.Static | BindingFlags.Public);
    }

    private Assembly compileString(string code)
    {
      CodeDomProvider compiler = CodeDomProvider.CreateProvider("CSharp");
      CompilerResults result = compiler.CompileAssemblyFromSource(new CompilerParameters(ReferenceDLLs.Union(ReferenceAssemblies.ConvertAll(x => x.Location)).ToArray()), code);
      if (result.Errors.Count > 0)
        throw new CompilationException(result.Errors);
      return result.CompiledAssembly;
    }

    private List<string> defaultUsingStatements()
    {
      return new List<string>()
                       {
                            "using System;",
                            "using System.Collections.Generic;",
                            "using System.Linq;",
                            "using System.IO;"
                       };
    }

    private string surroundLambaWithFakeClass<X, Y>(string text, List<string> usingStatements = null)
    {
      usingStatements = usingStatements ?? defaultUsingStatements();
      string code = usingStatements.Aggregate((x, y) => x + "\r\n" + y) + @"

                namespace FakeNamespace
                {
                  public class FakeClass
                  {
                    public static CLASS2 MethodResult(CLASS1 param1)
                    {
                       Func<CLASS1, CLASS2> lambda = " + text + @";
                       return lambda(param1);
                    }
                  }
          }";

      code = code.Replace("CLASS1", typeof(X).ToString()).Replace("CLASS2", typeof(Y).ToString()).Replace("`1[", "<").Replace("]", ">");
      return code;
    }

    private string surroundLambaWithFakeClass<X>(string text, List<string> usingStatements = null)
    {
      usingStatements = usingStatements ?? defaultUsingStatements();
      string code = usingStatements.Aggregate((x, y) => x + "\r\n" + y) + @"

                namespace FakeNamespace
                {
                  public class FakeClass
                  {
                    public static void MethodResult(CLASS1 param1)
                    {
                       Action<CLASS1> lambda = " + text + @";
                       return lambda(param1);
                    }
                  }
          }";

      code = code.Replace("CLASS1", typeof(X).ToString()).Replace("`1[", "<").Replace("]", ">");
      return code;
    }

    private string surroundMethodWithFakeClass<X>(string text, List<string> usingStatements = null)
    {
      usingStatements = usingStatements ?? defaultUsingStatements();
      string code = usingStatements.Aggregate((x, y) => x + "\r\n" + y) + @"

                namespace FakeNamespace
                {
                  public class FakeClass
                  {
                    public static void MethodResult(CLASS1 param1)
                    {
                       " + text + @"
                    }
                  }
          }";

      code = code.Replace("CLASS1", typeof(X).ToString()).Replace("`1[", "<").Replace("]", ">");
      return code;
    }

    private string surroundMethodWithFakeClass<X, Y>(string text, List<string> usingStatements = null)
    {
      usingStatements = usingStatements ?? defaultUsingStatements();
      string code = usingStatements.Aggregate((x, y) => x + "\r\n" + y) + @"

                namespace FakeNamespace
                {
                  public class FakeClass
                  {
                    public static CLASS2 MethodResult(CLASS1 param1)
                    {
                       " + text + @"
                    }
                  }
          }";

      code = code.Replace("CLASS1", typeof(X).ToString()).Replace("CLASS2", typeof(Y).ToString()).Replace("`1[", "<").Replace("]", ">");
      return code;
    }

    public Func<X, Y> ConvertMethodStringToMethodInfo<X, Y>(string text, List<string> usingStatements = null)
    {
      string code = surroundMethodWithFakeClass<X, Y>(text, usingStatements);
      MethodInfo method = compileStringToMethodInfo(code);
      return x => (Y)method.Invoke(null, new object[] { x });
    }

    public Action<X> ConvertMethodStringToMethodInfo<X>(string text, List<string> usingStatements = null)
    {
      string code = surroundMethodWithFakeClass<X>(text, usingStatements);
      MethodInfo method = compileStringToMethodInfo(code);
      return x => method.Invoke(null, new object[] { x });
    }

    public Func<X, Y> ConvertLambaStringToMethodInfo<X, Y>(string text, List<string> usingStatements = null)
    {
      string code = surroundLambaWithFakeClass<X, Y>(text, usingStatements);
      MethodInfo methodInfo = compileStringToMethodInfo(code);
      Func<X, Y> result = x => (Y)methodInfo.Invoke(null, new object[] { x });
      return result;
    }

    public Action<X> ConvertLambaStringToMethodInfo<X>(string text, List<string> usingStatements = null)
    {
      string code = surroundLambaWithFakeClass<X>(text, usingStatements);
      MethodInfo methodInfo = compileStringToMethodInfo(code);
      return x => methodInfo.Invoke(null, new object[] { x });
    }

    public static string EscapeToProtectFromCodeInjection(string searchText)
    {
      return "\"" + searchText.Replace("\\", "\\\\").Replace("\"", "\\\"") + "\"";
    }
  }
}

Here's how to use it:

          Func<string, IEnumerable<string>> stringSplitFunction = Compiler.ConvertLambaStringToMethodInfo<String, IEnumerable<String>>("x => x.Split(new string[] { \",\" }, StringSplitOptions.RemoveEmptyEntries).AsEnumerable()");
          IEnumerable<string> result = stringSplitFunction("val1,val2");

Mutation Testing in C#

I found a cool tool today for doing mutation testing in C#. Basically, it runs your unit tests to analyze your code coverage, then it purposefully modifies the sections of your code that are covered, to see if the tests will still pass. The theory is, if the tests pass, then even though you have coverage, you're not asserting the right things. So, it helps you analyze the quality of your unit tests.
Check it out here: http://galera.ii.pw.edu.pl/~adr/CREAM/index.php

Linq Recursive Extensions

Here are some extension methods to allow you to recurse through a class hierarchy:

    public static class LinqExtensions
    {
        public static IEnumerable<T> Recurse<T>(this T source, Func<T, IEnumerable<T>> getChildren) where T : class
        {
            if (source == null)
                return new List<T>();
 
            List<T> result = getChildren(source).Recurse<T>(getChildren).ToList();
            result.Insert(0, source);
             
            return result;
        }
 
        public static IEnumerable<T> Recurse<T>(this T source, Func<T, T> getChild) where T : class
        {
            if (source != null)
                yield return source;
 
            T child = getChild(source);
            while (child != null)
            {
                yield return child;
                child = getChild(child);
            }
        }
 
        public static IEnumerable<T> Recurse<T>(this IEnumerable<T> source, Func<T, T> getChild) where T : class
        {
            List<T> result = new List<T>();
            if (source != null)
            {
                foreach (T child in source)
                    result.AddRange(child.Recurse<T>(getChild));
            }
             
            return result;
        }
 
        public static IEnumerable<T> Recurse<T>(this IEnumerable<T> source, Func<T, IEnumerable<T>> getChildren) where T : class
        {
            if (source != null)
            {
                foreach (T child in source)
                {
                    if (child != null)
                        yield return child;
 
                    IEnumerable<T> subChildren = getChildren(child);
                    if (subChildren != null)
                    {
                        foreach (T subChild in subChildren.Recurse<T>(getChildren))
                        {
                            if (subChild != null)
                                yield return subChild;
                        }
                    }
                }
            }
        }
    }

Here's an example of how to use them:

    public class Node
    {
      public List<Node> Children = new List<Node>();
    }

    Node parent = new Node();
    IEnumerable<Node> children = parent.Recurse(x => x.Children);

Lambda Equality Comparer Extension Method

Here's an extension method that will convert a lambda to an equality comparer:

public static class FuncExtension
{
    private class GenericComparer<T> : EqualityComparer<T>
    {
        private readonly Func<T, T, bool> _lambda;
        private readonly bool _passThroughNulls;
        
        public GenericComparer(Func<T, T, bool> lambda, bool passThroughNulls)
        {
            _lambda = lambda; _passThroughNulls = passThroughNulls;
        }

        public override bool Equals(T x, T y)
        {
            if (!_passThroughNulls)
            {
                if (System.Object.Equals(x, default(T)) || System.Object.Equals(y, default(T)))
                {
                    return System.Object.Equals(x, default(T)) && System.Object.Equals(y, default(T));
                }
            }
            
            return _lambda(x, y);
        }
    
        public override int GetHashCode(T obj)
        {
            return 0;
        }
    }
    
    public static IEqualityComparer<T> ToEqualityComparer<T>(this Func<T, T, bool> lambda, bool passThroughNulls = false)
    {
        return new GenericComparer<T>(lambda, passThroughNulls);
    }
}

Here's an example of how to use it:

Func<SomeClass, SomeClass, bool> compareFunction = (x, y) => x.SomeProperty == y.SomeProperty && x.SomeProperty2 == y.SomeProperty2;
EqualityComparer<SomeClass> comparer = compareFunction.ToEqualityComparer();

TF14098: Access Denied creating branch

When creating a branch from main to release you may get an error like this:

Branch from Main

TF14098: Access Denied: User CTAC\dgarner needs Read permissions(s)

for $/RxInfoInquiry/Main/*.

 

Even though you're able to get latest on main & build/run the app, there is some random file/folder underneath the main branch that you don't have permissions to. This is causing you to not be able to make a new release branch.

 

You'll need to find the file & add read permissions to [DefaultCollection]\Contributors (assuming you're a developer & in that group).

 

There is a command-line utility called "TF" that you can use to interact with TFS. You can access it through the

"Visual Studio Command Prompt" found on your start menu.

To find the problem file, you'll need to recurse through all the items in the project & check their permissions.

 

An explanation can be found at: http://msdn.microsoft.com/en-us/library/0dsd05ft.aspx

 

Here's the basic steps I used. The program I had an issue with was RxInfoInquiry.

This will tell you the permissions of each folder in your project:

tf permission c:\source\RxInfoInquiry /recursive > allPermissions.txt

I then opened the file in an editor supporting regex's & I searched for any folder where Contributors didn't have read permissions.

Once the file is found & the read permissions have been granted, you should be able to create the new release branch.

If you're unlucky & it's a file, rather than a whole folder with the missing permissions, you'll need to check individual files instead of folders.

To get a list of all items in the project:

tf dir c:\source\RxInfoInquiry /recursive > c:\temp\allFiles.txt

You could take this list & convert each filename to a "tf permission" command & run it as a batch file. Then, search for the file with the missing permissions.