Thursday 23 February 2012

Choosing binding for WCF service


Choosing binding for WCF service


There are different types of bindings supported by WCF. Now question is which binding is suitable for your service.Following is list of frequently used bindings in WCF:
*   BasicHttpBinding: If clients can be non-WCF. This exposes service to the outside world as if it was an ASMX (webservice). You cannot take advantages of modern WS* protocol on this binding. This is unsecure binding by default.
*   NetMsmqBinding: If client is WCF application and requires offline or disconnected interaction choose NetMsmqBinding which uses MSMQ for transporting the messages.
*   NetTcpBinding: If client requires connected communication and no problem on communicating between firewalls as it requires port to open for communication across firewalls. This is WCF to WCF communication.This is secure and optimized binding that is suitable for on machine communications between WCF applications.
*   NetNamedPipeBinding (IPC Binding): It is similar to TCP protocol but if requirement is to communicate on same machine then you can consider it.
*   WSHttpBinding: If you want a secure and interoperable binding that is suitable for non-duplex service contracts.
*   WSDualHttpBinding (WS dual binding): It is similar to WS binding but it can also support duplex communication from service to client.It it nothing more than two WsHttpBinding bindins wired up against each other to support callbacks.
Important:
*       Select a binding that has security enabled. By default, all bindings, except the BasicHttpBinding binding, have security enabled. If you do not select a secure binding, or if you disable security, be sure your network exchanges are protected in some other manner, such as being in a secured data center or on an isolated network.
*       Do not use duplex contracts with bindings that do not support security, or that have security disabled, unless the network exchange is secured by some other means.
Binding Features
This is matrix of high level features of bindings. This is excerpt from MSDN.
Binding
Interoperability
Mode of Security (Default)
Session (Default)
Transactions
Duplex
BasicHttpBinding
Basic Profile 1.1
(None), Transport, Message, Mixed
None, (None)
(None)
n/a
WSHttpBinding
WS
None, Transport, (Message), Mixed
(None), Transport, Reliable Session
(None), Yes
n/a
WS2007HttpBinding
WS-Security, WS-Trust, WS-SecureConversation, WS-SecurityPolicy
None, Transport, (Message), Mixed
(None), Transport, Reliable Session
(None), Yes
n/a
WSDualHttpBinding
WS
None, (Message)
(Reliable Session)
(None), Yes
Yes
WSFederationHttpBinding
WS-Federation
None, (Message), Mixed
(None), Reliable Session
(None), Yes
No
WS2007FederationHttpBinding
WS-Federation
None, (Message), Mixed
(None), Reliable Session
(None), Yes
No
NetTcpBinding
.NET
None, (Transport), Message,
Mixed
Reliable Session, (Transport)
(None), Yes
Yes
NetNamedPipeBinding
.NET
None,
(Transport)
None, (Transport)
(None), Yes
Yes
NetMsmqBinding
.NET
None, Message, (Transport), Both
(None)
(None), Yes
No
NetPeerTcpBinding
Peer
None, Message, (Transport), Mixed
(None)
(None)
Yes
MsmqIntegrationBinding
MSMQ
None, (Transport)
(None)
(None), Yes
n/a


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


Regards
Sujeet 

Monday 13 February 2012

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