Search Text in all SQL Server Objects

Many time you come through a situation where you want to Search a text in SQL Objects such as table, stored procedure, views, triggers, etc.., but we don't get when required.

Below SQL Script will search any text assigned to @Search variable in all Tables, Users, Views, Functions, Stored Procedures, Primary Key, Foreign Key etc..


Script to search text in all SQL Server objects:

DECLARE @Search varchar(255)
SET @Search='xsp_get' -- GIVE YOUR SEARCH TEXT
SELECT DISTINCT
LEFT(so.name, 100) AS Object_Name,
LEFT(
CASE so.type
WHEN 'U' THEN 'Table - User'
WHEN 'S' THEN 'Table - System'
WHEN 'P' THEN 'Stored Procedure'
WHEN 'V' THEN 'Table - View'
WHEN 'TR' THEN 'Trigger'
WHEN 'C' THEN 'Constraint - Check'
WHEN 'D' THEN 'Default'
WHEN 'K' THEN 'Key - Primary'
WHEN 'F' THEN 'Key - Foreign'
WHEN 'L' THEN 'Log'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication Filter stp'
ELSE '<<UNKNOWN ' + so.type + '>>'
END
,25) AS Object_Type
FROM syscomments sc
INNER JOIN sysobjects so ON so.id = sc.id
WHERE text Like '%'+@Search+'%'
ORDER BY 2,1

Facade Pattern with C#

Facade Pattern classified into Structural Pattern is unified interface that represents entire by hiding the complexities.

It provides the higher level interface which makes subsystem easier to use. It provides client with a single entry point which eliminates knowing the complexity of many subsystems beneath it.


In this pattern, facade object is aware of the responsibility owned by subsystem classes. It essentially delegates the client request to subsystem classes; whereas sub-system classes perform operation as assigned by facade object and doesn’t holds any reference to it.


Below is the sample class diagram demonstrates Facade pattern where client makes a call to Facade class and the subsystem operations are managed by facade thereby eliminating the client to directly deal with subsystems.

 
Code for Facade Class:

namespace MyProject.FacadePattern
{
public class Facade
{
private IValidateUser _validateuser;
private IAddUser _adduser;
private INotifyService _notifyservice;

public Facade()
{
_validateuser = new ValidateUser();
_adduser = new AddUser();
_notifyservice = new NotifyService();
}

public bool AddUser(User user)
{
if (!_validateuser.ValidateUser(user))
return false;

_adduser.AddUser(user);
_notifyservice.SendEmail(user);
}
}
}


< Back to Design Patterns

Decorator Pattern with C#

Decorator Pattern classified into Structural Pattern which add responsibilities to objects dynamically.

A client application often needs to augment the services provided by methods of some class, by inserting some pre and post-processing tasks before and after the method calls. If distinct pre and post-processing tasks were to be carried out for different clients, the application logic would become complex by conditional statements, leading to a maintenance nightmare.

Consider a class where clients can invoke method to transfer files to and from some remote server. In addition to being able to upload and download files, the client application would also like to log all file transfer requests and perform access checks for each invocation.

An implementation based on the Decorator pattern would derive a class from FileTransfer and override the virtual methods, inserting the additional operations before and after calling the base methods. Client still continue to call the same method; however additional responsibility is added to the class without affecting other clients using the same functionality. Thus, Decorator Pattern allows dynamic and transparent addition and removal of responsibilities without affecting client code.

FileTransfer Class (used by multiple clients):
namespace MyProject.DecoratorPattern
{
public class FileTransfer
{
public virtual bool UploadFile(string url)
{
bool result;
// File Upload Logic
Console.WriteLine("File Uploaded to url :" + url);
result = true;
return result;
}

public virtual bool DownloadFile(string url, string localpath)
{
bool result;
// File Download Logic
Console.WriteLine("File downloaded to '{0}' from url '{1}'", url, localpath);
result = true;
return result;
}
}
}


Derive from FileTransfer Class:
namespace MyProject.DecoratorPattern
{
class Decorator : FileTransfer
{
//Additional Responsibility added without affecting client code, demonstrate Decorator Pattern.
public override bool UploadFile(string url)
{
bool result = false;
if (!AccessAllowed(url)) return result;
result = base.UploadFile(url);
LogActivity(url);
return result;
}
//Additional Responsibility added without affecting client code, demonstrate Decorator Pattern.
public override bool DownloadFile(string url, string localpath)
{
bool result = false;
if (!AccessAllowed(url)) return result;
result = base.DownloadFile(url, localpath);
LogActivity(url);
return result;
}
private bool AccessAllowed(string url)
{
bool result;
// URL access Logic
Console.WriteLine("Access Allowed to url :" + url);
result = true;
return result;
}
private void LogActivity(string url)
{
// Logic to Log Upload/Download Activity
Console.WriteLine("Activity logged for url: " + url);
}
}
}

< Back to Design Patterns

Strategy Pattern with C#

Strategy Pattern classified under Behavioural Pattern encapsulates an algorithm inside a class.

Strategy Pattern is a class that has to perform strategically depending on user input, platform information and so on.. Depending on user input, a different algorithm will be used; however, the end result is the same.

To explain this through example, I have used three diferrent search algorighm, each with its own performance and accuracy trade-off. One of them is very computation-intensive, but does a more thorough job, while another is faster but generates results that may be inaccurate for very data. My application will ask the user for the desired performance and then invoke the appropriate algorithm. To this end, I will encapsulate my algorithms in classes that demonstrate Strategy Pattern.

iSearch.cs
This interface will be implemented by various classes with different search algorithm

namespace MyProject.StrategyPattern.Search
{
public interface iSearch
{
string Search(string keyword);
}
}


Search.cs
Having implemented all three algorithms in this manner mentioned below, I can now design the client that decouples it from the implementation details of any specific algorithm. The client holds a reference to the interface, and does not need to know anything about the concrete implementation of the interface.

namespace MyProject.StrategyPattern.Search
{

public class ListSearch : iSearch
{
#region iSearch Members

public string Search(string keyword)
{
return "Searched " + keyword + "with List Search Algorithm";
}

#endregion
}


public class TreeSearch : iSearch
{
#region iSearch Members

public string Search(string keyword)
{
return "Searched " + keyword + " with Tree Search Algorithm";
}

#endregion
}


public class BinarySearch : iSearch
{
#region iSearch Members

public string Search(string keyword)
{
return "Searched " + keyword + " Binary Search Algorithm";
}

#endregion
}

}

SearchKeyword.cs
Finally, I create an instance of the SearchKeyword class and, depending on user input, initialize it with the appropriate algorithm. The Test method of the SearchKeyword class invokes the Search method of the iSearch interface that every algorithm implements

namespace MyProject.StrategyPattern
{
public class SearchKeyword
{
private iSearch _search;

public SearchKeyword(iSearch Search)
{
_search = Search;
}

public string SearchString(string Keyword)
{
return _search.Search(Keyword);
}


}
}


< Back to Design Patterns

Singleton Pattern with C#

Singleton Pattern which is classified under Creational Pattern which focuses on having only one instance of object at any given time.

To give an example, windows directory service which has multiple entries but you can only have single instance of it through out the network.

• A singleton is the sole instance of some class.
• It is most often the case that the singleton should also be globally accessible, and this is achieved by making the creation method/property public.
• Note that in the code below, the class constructor is private to avoind creating instance of class.

Creating Class which will return only one instance:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyProject.SingletonSample.Evaluation;

namespace MyProject.SingletonSample
{
public class Singleton
{
// Private Constructor to not allow to creating instance
private Singleton() { }

private static iEvaluation instance;

// Creates and maintains single instance of Evaluation Class
public static iEvaluation Instance
{
get
{
if (instance == null)
{
instance = new Evaluation.Evaluation();
}
return instance;
}
}

}
}

Creating Instance of Singleton class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyProject.SingletonSample.Evaluation;

namespace MyProject.SingletonSample
{
class Program
{
static void Main(string[] args)
{
EvalData ed1=new EvalData();
ed1.Name = "Aaaa";
ed1.Comment = "Added with ED1";
ed1.DateSubmitted = DateTime.Now;

EvalData ed2 = new EvalData();
ed2.Name = "Bbbb";
ed2.Comment = "Added with ED2";
ed2.DateSubmitted = DateTime.Now;

Singleton.Instance.AddEval(ed1);
Singleton.Instance.AddEval(ed2);

Console.ReadKey();
}
}
}
< Back to Design Patterns

Design Patterns

Design Patterns are recurring solution to recurring problems in software architecture. A design pattern can solve many problems by providing a framework for building an application.

Design patterns which make the design process cleaner and more efficient, are especially well-suited for object-oriented languages such as C#. Existing design patterns make good templates for the objects, allowing you to build software faster, re-use the code, and a pattern which is acceptable organisation wide.

Design patterns can improve the extensibility of your applications and the reuse of your objects. Software design and architecture without a rudimentary understanding of design patterns is unthinkable. Most software applications and systems incorporate one or more design patterns which we all might have come across, but don't know them as patterns.

A design pattern is a description of a set of interacting classes that provide a framework for a solution to a generalized problem in a specific context or environment. In other words, a pattern suggests a solution to a particular problem or issue in object-oriented software development

There are three basic classification of patters:

~ Creational

1. Singleton Pattern

~ Structural, and

1. Facade Pattern
2. Decorator Pattern

~ Behavioural pattern

1. Strategy Pattern

Simple Way To Output XML to Browser

I have seen many Blogs and Posts where people do lot of hard work just to display the XML onto the web page. Well its very easy but people dont know which TAG do this

When you paste the XML on a .htm file, browser renders the XML Attributes as HTML attributes and displays the output. It wont display the way you see in a notepad file or in Visual Studio.

Some time during XML programming (I mean reading/writing/parsing/traversing), we get an error saying some issue with XML, but we can't see the extire XML because browser renders like a html file.

Without wasting more time, in order to print or show the XML content on browser, put the entire XML content in <XMP></XMP> tag and see the magic.


Below is the XML which I had put in a web page with and following 2 images shows the difference.


Output without <XMP></XMP> Tag:




Output with <XMP></XMP> Tag:





Similar Tags:
Display XML to Browser,
Print XML to Browser,
Output XML to Browser,
Viewing XML file in Browser.

Free Open source Encryption Software- TrueCrypt

This is an amazing open source on-the-fly disk encryption software for Windows, Mac and Linux. You can encrypt entire disk (internal/external/USBs) as well as encrypt any files/folders into encrypted file which can be mounted later.

TrueCrypt has an fantastic feature of two levels of encryption which helps to protect the volume in case if you have to reveal the password. In this case he creates a Hidden Volume which will never be known to others and opened with another password. It uses Encryption Algorithm such as AES-256, Serpent, Twofish and mode of operation is XTS

Advantage is you don't need to decrypt every files/folders to view it. You just have to mount with correct password and it operates as normal volume.

Only disadvantage is that IF YOU FORGET THE PASSWORD, YOU WILL LOOSE ALL DATA.

READ THE DOCUMENTATION AND TUTORIAL BEFORE INSTALLING...

URL to site: http://www.truecrypt.org/
Download: http://www.truecrypt.org/downloads
Docs: http://www.truecrypt.org/docs/

Deleting Services Forcefully from Services MMC

Few days back I modified my Windows Service .NET Application and compiled to MSI to be re-installed. Somehow during un-install process, service was not removed from Services Microsoft Management Console (MMC) and it came to my notice during re-installation of MSI.

As there is no Delete option available in Services MMC, it was becoming a nightmare to re-install it. Some how I found SC.EXE which is a command line program to communicate with Service Control Manager and Services.

There are many available options but what interested me is you can Create, Start, Stop, Delete a service with a Command Line.

Creating a Service:

sc.exe create PayCalcService binPath= "C:\Program Files\PaymentCalculation\paycalc.exe" DisplayName= "PaymentCalculationService"

Starting a Service:
sc.exe start PaymentCalculationService

Stopping a Service:
sc.exe stop PaymentCalculationService

Deleting a Service:
sc.exe delete PaymentCalculationService

Hope this helps for similar problems...