Microsoft Visual C#: Logical Conjunction and Disjunction

For a real estate company, suppose you have a customer who is considering purchasing a single family house around Silver Spring, Maryland, you would check the listing of the properties and find out what you have. To respond to this request, you must examine two conditions for each property:

• The property must be a single family
• The property must be located in Silver Spring

When preparing your listing prior to seeing the customer, you can start by building a query that lists only the single family houses:

The other condition requires that the house be located in Silver Spring:

From these two results, notice that there is no relationship between the fact that a property is a single family that it is located in Silver Spring.

A logical conjunction is a Boolean operation that combines at least two conditions. For example our customer wants to purchase a property only if two conditions are met: The property must be a single family AND the property must be located in Silver Spring. This type of condition is referred to as logical conjunction.

To create a logical conjunction, if you are working on a table in Microsoft SQL Server Management Studio or in Microsoft Visual Studio, under Filter, type each condition as seen above. Here is an example:

If you are working in an ADO.NET graphical application, you can access the Properties window for the binding source and, in the Filter field, type the expression. Alternatively, you can access the variable of the binding source in your code and assign it the AND expression. In both cases, the expression must follow the Transact-SQL rule,

To create a logical conjunction in SQL, you use the AND operator. To write the statement, you use the the following formula:

SELECT WhatColumn(s)
FROM WhatObject
WHERE Condition1 AND Condition2

The WhatColumn(s) and the WhatObject factors are the same we have used in previous WHERE conditions so far. The AND keyword is the operator that joins the conditions. Each condition is written as a SQL operation using the formula:

Column operator Value

In this case, the WHERE operator resembles the C#'s if conditional statement. The Condition1 is the first that would be examined. If the first condition is false, the whole statement is false and there is no reason to examine the second condition. If the first condition is true, then the second condition would be examined.

Based on this, to get a list of properties that satisfies our customer, that is, to get a list that includes the single family properties in Silver Spring, we would create a SQL statement as follows:

SELECT PropertyNumber,
       City,
       State,
       ZIPCode,
       PropertyType,
       Condition,
       Bedrooms,
       Bathrooms,
       Stories,
       YearBuilt,
       MarketValue
FROM   Properties
WHERE  PropertyType = N'Single Family' AND City = N'Silver Spring';

In your Windows application, you can assign this expression to the binding source. Here is an example:

private void btnAnalyze_Click(object sender, EventArgs e)
{
    bsProperties.Filter = 
	"PropertyType = N'Single Family' AND City = N'Silver Spring'";
}

This would produce:

We stated that each condition was separately evaluated. For this reason, to make the conjunction statement easier to read, each condition should be included in parentheses. Therefore, the above SQL statement can be written as follows:

SELECT PropertyType, City, Bedrooms, Stories, MarketValue
FROM   Properties
WHERE  (PropertyType = N'Single Family') AND (City = N'Silver Spring');

This would produce:

When creating a logical conjunction, you can apply all the other features we have reviewed so far:

• Because SQL is not case sensitive, you can write AND in either uppercase or lowercase
• Because SQL is not case sensitive, you can write the conditions in uppercase, lowercase, or a combination of cases
• You can negate a condition by preceding it with the NOT operator
• You can create alias names for the columns. Here are examples:
  

  SELECT PropertyNumber AS [Prop #],
         PropertyType AS Type,
         YearBuilt AS [Year Built],
         City,
         State,
         ZIPCode AS [ZIP Code],
         Bedrooms AS Beds,
         Bathrooms AS Baths,
         MarketValue AS Value
  FROM Properties
  WHERE (PropertyType = N'Single Family') AND (City = N'Silver Spring');
  GO

• You can use an alias name for the table:
  

  SELECT prop.PropertyNumber AS [Prop #],
         prop.PropertyType AS Type,
         prop.YearBuilt AS [Year Built],
         prop.City,
         prop.State,
         prop.ZIPCode AS [ZIP Code],
         prop.Bedrooms AS Beds,
         prop.Bathrooms AS Baths,
         prop.MarketValue AS Value
  FROM Properties prop
  WHERE (prop.PropertyType = N'Townhouse') AND (prop.MarketValue < 400000)
  GO

Instead of one logical conjunction, you can create as many conjunctions as you want. To do this, use as many combinations of AND operators as necessary. For example, suppose a customer wants to purchase a single family in MD but she is not willing to sp} over 500000. To get this list, you would need two AND conditions. You would create the condition as follows:

SELECT PropertyNumber, PropertyType, City, Bedrooms, MarketValue
FROM   Properties
WHERE  PropertyType = N'Single Family' AND
       State = N'MD' AND
       MarketValue < 500000

To make the WHERE condition easier to read, each expression should be included in its own parentheses. Here is an example:

SELECT PropertyNumber,
       City,
       State,
       ZIPCode,
       PropertyType,
       Condition,
       Bedrooms,
       Bathrooms,
       Stories,
       YearBuilt,
       MarketValue
FROM   Properties
FROM   Properties
WHERE  (PropertyType = N'Single Family') AND
       (State = N'MD') AND
       (MarketValue < 500000)

If you are working in a Windows application, you can pass the expression to the binding source. Here is an example:

private void btnAnalyze_Click(object sender, EventArgs e)
{
    bsProperties.Filter = "(PropertyType = N'Single Family') AND " +
			  "(State = N'MD') AND " +
			  "(MarketValue < 500000)";
}

This would produce:

Suppose a customer is considering purchasing either a single family house or a townhouse. To prepare the list or properties, you must create a list that includes only these two options. Before building the query, you can state the following:

• The property is a single-family
• The property is a townhouse

When creating the list, you would want to include a property only if it is either a single family or a townhouse:

1. If the property is a single family, our statement is true and we don't need to check the second condition
2. If the property is not a townhouse, then we consider the second condition. If the property is a townhouse, our statement becomes true

This type of statement is referred to as logical disjunction.

To create a logical disjunction, if you are working on a table:

1. Under the first Or column, click the box that corresponds to one of the columns that will be involved in the OR condition, and type the necessary value or expression
2. Under the second Or column, access the box that corresponds to the same column as previously and type the appropriate value or expression

Here is an example:

If you are working in a Windows application, you can assign the logical disjunction to the Filter property of the binding source.

To create a logical disjunction in SQL, you use the OR operator. To do this, after the WHERE operator, type two conditions separated by an OR operator. The formula to use would be:

SELECT WhatColumn(s)
FROM   WhatObject
WHERE  Condition1 OR Condition2

Each condition must be complete; that is, it can consist of a Column=Value expression. Here is an example:

SELECT PropertyType, City, Bedrooms, Stories, MarketValue
FROM   Properties
WHERE  PropertyType = N'Single Family' OR PropertyType = N'Townhouse'; 

To make the disjunction easier to read, each expression should be included in parentheses. Here is an example:

SELECT PropertyType, City, Bedrooms, Stories, MarketValue
FROM   Properties
WHERE  (PropertyType = N'Single Family') OR (PropertyType = N'Townhouse'); 

This would produce:

When creating a disjunction, you can apply any of the other SQL features we have reviewed so far.

Instead of two disjunctions, you can create three or more if you want. To add one more disjunction if you are working on a table, in the third Or column, click the box that corresponding to the same column used for the previous Or condition, and type the desired value or expression.