Database

Navin C. Pandit wrote:

Actually I want to know the max. number of possible concurrent users, beyond
that number database may not listen about the user/request.

List<string:gt; parts = new List<string>() ;
List<IDbDataParameter> parms = new List<IDbDataParameter>() ;

cmd.CommandText = "SELECT TOP 100 * FROM Contacts " ;

if ( !System.String.IsNullOrEmpty ( Reference ) )
{
    parts.Add ( "Contacts.Reference = @Reference " ) ;

    IDbDataParameter prm = cmd.CreateParameter() ;
    prm.Name = "@Reference" ;
    prm.Value = Reference ;
    parms.Add ( prm ) ;
} 

...


if ( parts.Count > 0 )
{
    cmd.CommandText += "WHERE " ;

    cmd.CommandText += parts [ 0 ] ;
    cmd.Parameters.Add ( parms [ 0 ] ) ;

    for ( int i = 1 ; i < parts.Count ; i++ )
    {
        cmd.CommandText += "AND " ;

        cmd.CommandText += parts [ i ] ;
        cmd.Parameters.Add ( parms [ i ] ) ;
    }
}

cmd.ExecuteReader() ;

DECLARE @ContactName NVARCHAR(255)
DECLARE @Reference   NVARCHAR(255)
DECLARE @sql         NVARCHAR(255)

SET @sql = 'SELECT TOP 100 FamilyName,Reference FROM Contact C WHERE 1=1'

-- SET @ContactName = 'Smith'
-- SET @Reference   = 'A%'

IF NOT @ContactName IS NULL  
 SET @sql = @sql + ' AND C.ContactName  = @ContactName'
IF NOT @Reference IS NULL 
 SET @sql = @sql + ' AND C.Reference LIKE @Reference'
 
EXECUTE sp_executesql 
 @sql,
 N'@FamilyName NVARCHAR(255), @CoName NVARCHAR(255)',
 @ContactName, @Reference 

Pascal Ganaye wrote:

we can assume it will be optimized and cached

PIEBALDconsult wrote:

So I don't think there's any appreciable savings over the non-stored-procedure way

Mycroft Holmes wrote:

sql strings from the business layer are actually easier to do

SELECT TOP 100 * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBith IS NULL OR Contacts.DateOfBith = @DateOfBith)
AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)

SELECT TOP 100 * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBith IS NULL OR Contacts.DateOfBith = @DateOfBith)
AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)

SELECT TOP 100 * FROM Contacts WHERE
(Reference = @Reference)
AND (DateOfBith = @DateOfBith)

Pascal Ganaye wrote:

I am trying to stay database agnostic

(@ContactType IS NULL OR Contacts.ContactType = @ContactType)

Contacts.ContactType = COALESCE(@ContactType,Contacts.ContactType)

(Not that that is necessarily agnostic either. )