How to search a value in entire database

Question: How to search a value in entire database

Answer:
We created on Stored Procedure SearchAllTables
The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)


Create this procedure in the required database and here is how you run it:


Here is the complete stored procedure code:

CREATE PROC SearchAllTables

(

               @SearchStr nvarchar(100)

)

AS

BEGIN

               CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

               SET NOCOUNT ON

               DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

               SET  @TableName = ''

               SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

               WHILE @TableName IS NOT NULL

               BEGIN

                               SET @ColumnName = ''

                               SET @TableName = 

                               (

                                      SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

                                          FROM       INFORMATION_SCHEMA.TABLES

                                       WHERE                     TABLE_TYPE = 'BASE TABLE'

                     AND          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

                     AND          OBJECTPROPERTY(

                                OBJECT_ID(

                                 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                                        ), 'IsMSShipped'

                                 ) = 0

                               )

                               WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

                               BEGIN

                                              SET @ColumnName =

                                              (

                                                             SELECT MIN(QUOTENAME(COLUMN_NAME))

                                                             FROM       INFORMATION_SCHEMA.COLUMNS

                                     WHERE     TABLE_SCHEMA          = PARSENAME(@TableName, 2)

           AND          TABLE_NAME              = PARSENAME(@TableName, 1)

             AND          DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

                AND          QUOTENAME(COLUMN_NAME) > @ColumnName

                                              )

                                              IF @ColumnName IS NOT NULL

                                              BEGIN

                                                             INSERT INTO #Results

                                                             EXEC

                                                             (

            'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 

                 FROM ' + @TableName + ' (NOLOCK) ' +

                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

                                                             )

                                              END

                               END          

               END

               SELECT ColumnName, ColumnValue FROM #Results

END

How to execute

EXEC SearchAllTables ‘Sujeet’

GO

If you have any doubt please reply to this post else mail me on sujeet.bhujbal@gmail.com

Regards

Sujeet 

Per Call Instance in WCF with example

Per Call Instance

In WCF there are 3 types of instancing we can do

1. Per Call
2. per Session
3. Single

Per Call

When we don configuration in WCF service as per call, every call method new service instances are created

In the below example I have created on service for percall

Below example shows the details.

namespace PerCall
{
   [ServiceContract(Namespace=" http://sujitbhujbal.blogspot.com")]
   //Created PerCall Interface
   public interface IPerCall   {
      [OperationContract]
      int IncrementCounter();
   }

   //PerCall session created
   [ServiceBehavior(InstanceContextMode=InstanceContextMode.PerCall)]
   public class CounterServicePerCall: IPerCall   {
      private int m_counter;
      int ICounterServicePerCall.IncrementCounter()
      {
         m_counter++;
         MessageBox.Show(String.Format("Incrementing PerCall counter to {0}.\r\nSession Id: {1}", m_counter, OperationContext.Current.SessionId));
         return m_counter;
      }    }
}
In the above example i created one service that creates PerCall instance and on the every instacne Counter is incremented

Below screen shot shows the different settings and Per call instances

When we click on PerCall button below screenshots shows the details what happen in PerCall

When again we press perCall session counter remains the same

Above screenshots and example shows that when we use per call instance in WCF every time call is created and counter remains same.

When should we use per call mode?

Per call

When you want a stateless services

When your WCF functions are called in a per call

References

MSDN link for WCF instances http://msdn.microsoft.com/en-us/library/ms733040.aspx

Migrating ASP.NET Web Services to WCF

Hi friends,

Again new WCF article

We are currently migrating a small web services built on top of ASP.NET in .NET 2.0 (commonly referred to as ASMX Web Services) over to the Windows Communication Foundation (WCF) platform available in .NET 3.0.
The reason behind this is we want speed which WCF provides.
The primary reason we want to do that is because we would like to take advantage of the binary message serialization support available in WCF to speed up communication between the services and their clients. This same technique was possible in earlier versions of the .NET Framework thanks to a technology called Remoting, which is now superseded by WCF. In both cases it requires that both the client and server run on .NET in order to work. But I digress.
Since the ASMX model has been for a long time the primary Microsoft technology for building web services on the .NET platform, I saw Microsoft has made nice setting in app.config of WCF migration path to bring all those web services to the new world of WCF.


The quick way
If you built your ASMX web services with code separation (that is, all programming logic resided in a separate code-behind file instead of being embedded in the ASMX file) it is possible to get an ASMX web service up and running in WCF pretty quickly by going through a few easy steps:

• Your WCF web service class no longer inherits from
  the  System.Web.Services.WebService class so remove it.
• Change the System.Web.Services.WebService attribute on the web service class to the System.ServiceModel.ServiceContract attribute.
• Change the System.Web.Services.WebMethod attribute on each web service method to the System.ServiceModel.OperationContract attribute.
• Substitute the .ASMX file with a new .SVC file with the following header:

Here is a summary of the changes you’ll have to make to your ASMX web service:

Where the change applies	ASMX	WCF
Web service class inheritance	WebService	-
Web service class attribute	WebServiceAttribute	ServiceContractAttribute
Web service method attribute	WebMethodAttribute	OperationContractAttribute
Data class attribute	XmlRootAttribute	DataContractAttribute
Data class field attribute	XmlElementAttribute	DataMemberAttribute
HTTP endpoint resource	.ASMX	.SVC

As a side note, if you are using .NET 3.5 SP1 the porting process gets a little easier

• WCF services are not bound to the HTTP protocol as ASMX web services are. This means you can host them in any process you like, whether it be a Windows Service or a console application. In other words using Microsoft IIS is no longer a requirement, although it is a valid possibility in many situations.
• Even if WCF services are executed by the ASP.NET worker process when hosted in IIS, they do not participate in the ASP.NET HTTP Pipeline. This means that you have no longer access to the ASP.NET infrastructure services such as HttpContext, HttpSessionState, HttpApplicationState, ASP.NET Authorization and Impersonation in your WCF services.

So what if your ASMX web services are making extensive use of the ASP.NET session store or employ the ASP.NET security model? Is it a show-stopper?
Luckily enough, no. There is a solution to keep all that ASP.NET goodness working in WCF. It is called ASP.NET Compatibility Mode.


Backwards compatibility
Running WCF services with the ASP.NET Compatibility Mode enabled will integrate them in the ASP.NET HTTP Pipeline, which of course means all ASP.NET infrastructure will be back in place and available from WCF at runtime.
You can enable this mighty mode from WCF by following these steps:

• Decorate your web service class with the System.ServiceModel.Activation.AspNetCompatibilityRequirements attribute as following:

[AspNetCompatibilityRequirements(

    RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]

public class MyService : IMyService

{

    // Service implementation

}

• Add the following setting to the application configuration file:

<system.serviceModel>

    <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />

</system.serviceModel>

Remember that this will effectively tie the WCF runtime to the HTTP protocol, just like with ASMX web services, which means it becomes prohibited to add any non-HTTP endpoints for your WCF services.


Good luck and please, let me know your feedback!

Regards,

Sujeet Bhujbal

C# 3.5 Extension Methods

In C# 3.5 introduces many new features like anonymous method ,extension method and var keyword
In this we will introduce Extension Methods

What is Extension Methods



1.Extension methods allow an existing type to be extended with new methods without altering the definition of the original type. 
2.An extension method is a static method of a static class, where the this modifier is applied to the first parameter. 
3.The type of the first parameter will be the type that is extended.


Example:



public static class StringHelper
{
public static bool IsCapitalized (this string s)
{
if (string.IsNullOrEmpty(s)) return false;
return char.IsUpper (s[0]);
}
}





4.The IsCapitalized extension method can be called as though it were an instance method on a string.


String s = "Perth“;
Console.WriteLine (s.IsCapitalized());


5.An extension method call, when compiled, is translated back into an ordinary static method call.
Console.WriteLine (StringHelper.IsCapitalized (s));




Ambiguity and Resolution about Extension Methods

1.An extension method cannot be accessed unless the namespace is in scope.

2.Any compatible instance method will always take precedence over an extension method.

3. If two extension methods have the same signature, the extension method must be called as an ordinary static method to disambiguate the method to call. 

4.If one extension method has more specific arguments, however, the more specific method takes precedence.

Example :-

static class StringHelper

{

public static bool IsCapitalized (this string s) {...}

}

static class ObjectHelper

{

public static bool IsCapitalized (this object s) {...}

}

bool test1 = "Perth".IsCapitalized();

bool test2 = (ObjectHelper.IsCapitalized ("Perth"));

Calling WCF Services using jQuery

Hi friends,

In this article you will learn how to call WCF Service using Jquery

But before this article you must know following

1. How to Create WCF Service
2. How to Host WCF Service
3. Jquery Basics

Step 1:

         Use of

 [AspNetCompatibilityRequirements(RequirementsMode 

    = AspNetCompatibilityRequirementsMode.Allowed)]

Attribute.

you need to specify the attributes on the server type class for ASP.NET compatibility mode, so that the WCF service works as a normal ASMX service and supports all existing ASP.NET features

eg :

[AspNetCompatibilityRequirements(RequirementsMode 

    = AspNetCompatibilityRequirementsMode.Allowed)]

public class MyService : IMyService

{

    public string[] GetSupplier(string Id)

    { 

        return new User().GetSupplier (Convert.ToInt32(Id));

    }

}

public class User

{

    Dictionary<int, string> Supplier = null;

    public User()

    {

        Supplier= new Dictionary<int, string>();

        Supplier.Add(1, "Sujeet");

        Supplier.Add(2, "Ajay");

        Supplier.Add(3, "Sanjay");

        Supplier.Add(4, "Amit");

    }

    public string[] GetUser(int Id)

    {

        var Supplier = from s in Supplier

                   where s.Key == Id

                   select s.Value;

        return Supplier.ToArray<string>();

    }

}

In the above example I have shown you how to create myService which return names of suppliers.

Step 2:-  

                        Operation contract setting

[OperationContract]

[OperationContract]

[WebInvoke(Method = "POST", 

 BodyStyle = WebMessageBodyStyle.Wrapped,

 ResponseFormat = WebMessageFormat.Json)]

string[] GetSupplier(string Id);

In the above code ResponseFormat = WebMessageFormat.Json indicates that data is returned in JSON format.

Step 3:-    App.config Setting

You have to use weHttp for endpoints

<system.serviceModel>

  <behaviors>

   <serviceBehaviors>

    <behavior name="ServiceBehavior">

     <serviceMetadata httpGetEnabled="true"/>

     <serviceDebug includeExceptionDetailInFaults="true"/>

    </behavior>

   </serviceBehaviors>

   <endpointBehaviors>

    <behavior name="EndpBehavior">

     <webHttp/>

</behavior>

   </endpointBehaviors>

  </behaviors>

  <services>

   <service behaviorConfiguration="ServiceBehavior" name="Service">

    <endpoint address="" binding="webHttpBinding" 

        contract="IService" behaviorConfiguration="EndpBehavior"/>

   </service>

  </services>

</system.serviceModel>

Step 4 : Ajax call with Jquery

    $.ajax({

        type: "POST",

        contentType: "application/json; charset=utf-8",

        url: ‘Service.svc/GetSupplier’,

        data: parameters,

        dataType: "json",

        async: true,

        success: function (data) {

resultObject = data.GetSupplierResult;

       

        for (i = 0; i < resultObject.length; i++) {

            alert(resultObject[i]);

        }

    });

In this way you can learn Calling WCF Services using jquery

If you have any query mail me to Sujeet.bhujbal@gmail.com     

Regards

Sujeet Bhujbal

RESTful WCF Services

RESTful WCF Services

Hi friends,

I have already  discussed REST ful services in earlier Post .But today in this article I am discussing details of RESTful Services

I am covering following topics in this article

1. Introduction

2. Differnce between normal WCF service and RESTful WCF Service

3. Where to Use RESTful WCF Service

4. Creation of RESTful WCF Service

5. Testing RESTful WCF Service

6. Summary

1. Introduction

WCF means Window communication Foundation which is introduced in .net framework 3.0. Before WCF there is webservice is famous for communication.

RESTful Service means Representational State Transfer (REST),

2. Differnce between normal WCF service and RESTful WCF Service

There is no more difference between WCF service and REST service only main difference is that How Client accesses our Service.

 Normal WCF service runs on the SOAP format but when we create REST service then client can access your service in different architecture style like JSON

REST uses4 HTTP methods to insert/delete/update/retrieve information which is below:

1. GET - Retrive a specific representation of a resource
2. PUT - Creates or updates a resource with the supplied representation
3. DELETE - Deletes the specified resource
4. POST - Submits data to be processed by the identified resource

3. Where to Use RESTful WCF Service

There are some scenario’s where we can use RESTful service like

1. Less Overhead because there is no use of SOAP object
2. Testing is very easy
3. Standardization is very nice

4. Creation of RESTful WCF Service

Step1.

Create a new WCF project in VS 2008/20010

Step 2:

Delete a Service Model node from Web.config

Step 3.

Build the Solution then   Right click on the svc file and Click on View Markup button

And add following code to the markup

Factory="System.ServiceModel.Activation.WebServiceHostFactory"

Step 4

Add following references to the WCF Service Application project, if you are using VS2010

Microsoft.Http.dll

Microsoft.Http.Extension.dll

System.ServiceModel.Web.dll

Step 5

  [ServiceContract]

    public interface IService1

    {

        [OperationContract(Name = " AddTwoNumber ")]

        [WebInvoke(UriTemplate = "/", Method = "POST")]

         int AddTwoNumber(int i, int j);

       

        [OperationContract(Name = " SubTwoNumber")]

        [WebGet(UriTemplate = "/")]

        int SubTwoNumber(int i, int j);

      

    }

Add your code in your service like here I have created 2 methods like AddTwonumber which takes two parameter I and j

In the above code we use

WebInvoke :- means invoke web method

Webinvoke takes Two parameters like

1. Method Type there are 4 types of method

a.Post      C.Invoke

b.Get       d.Delete

2. UriTemplate = "/ADD” tells  what would be URI for this particular method

Like this we can create RESTful service .

In the above article we studied all the basic details of restful service .

In the next article I will tell you details for Restful service

Thanks friends.