Database

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. )

1. create table test(name varchar(20),status bit not null)

assume status has value 0 or 1. then fill to a dataset.

Bind a gridview directly to dataset.

Column status shows true or false on gridview which is ok.

To retrieve the status value from gridviewrow.cells[1].text return empty string.

Confirm.

5fingers wrote:

What is the best precise method of converting bit(0 or 1) from sql(row array text cell) to boolean value.

1. create table test(name varchar(20),status bit not null)

assume status has value 0 or 1. then fill to a dataset.

Bind a gridview directly to dataset.

Column status shows true or false on gridview which is ok.

To retrieve the status value from gridviewrow.cells[1].text return empty string.

Confirm.