LINQ Equivalent to SQL “IN” Operator

This is an example of how you can use the LINQ “Contains” in a similar way to how you use the SQL “IN” operator.

The code example below will use an XDocument object as our data that we would like to query. The XDocument contains a set of books where each book as a unique ID. 

We are going to use the .Contains extension to filter out the books that have an ID of 1, 3, and 5.


void Main()
{
	XDocument doc = GetBooks();
	
	Console.WriteLine(string.Format("Show all books\r\n{0}", doc));
	
	// A string array that will be used to as our Book ID filter. 
	// You can think of this array as the arguments that are used in the SQL "IN" operator
	string[] bookIds = new string[] {"1","3","5"};
	
	// Get books out of the xml object that have an ID of 1, 3, or 5
	var books = from b in doc.Descendants("Book")
				where bookIds.Contains(b.Element("ID").Value)
				select b;
			
	Console.WriteLine("\r\nShow the filtered books");
	foreach(var b in books)
		Console.WriteLine(b);
}

XDocument GetBooks()
{ 
  // Manually create an XML document that contain books
	XDocument doc = new XDocument(
		new XElement("Books",
			new XElement("Book",
				new XElement("Title", "Don Quixote"),
				new XElement("ID", 1),
				new XElement("Author", "Miguel de Cervantes")
				),
			new XElement("Book",
				new XElement("Title", "A Tale of Two Cities"),
				new XElement("ID", 2),
				new XElement("Author", "Charles Dickens")
				),
			new XElement("Book",
				new XElement("Title", "The Little Prince"),
				new XElement("ID", 3),
				new XElement("Author", "Antoine de Saint-Exupéry")
				),
			new XElement("Book",
				new XElement("Title", "The Da Vinci Code"),
				new XElement("ID", 4),
				new XElement("Author", "Dan Brown")
				),
			new XElement("Book",
				new XElement("Title", "The Catcher in the Rye"),
				new XElement("ID", 5),
				new XElement("Author", "J.D. Salinger")
				)));
				
		return doc;
}

This is the output of the code above…

Show all books
<Books>
  <Book>
    <Title>Don Quixote</Title>
    <ID>1</ID>
    <Author>Miguel de Cervantes</Author>
  </Book>
  <Book>
    <Title>A Tale of Two Cities</Title>
    <ID>2</ID>
    <Author>Charles Dickens</Author>
  </Book>
  <Book>
    <Title>The Little Prince</Title>
    <ID>3</ID>
    <Author>Antoine de Saint-Exupéry</Author>
  </Book>
  <Book>
    <Title>The Da Vinci Code</Title>
    <ID>4</ID>
    <Author>Dan Brown</Author>
  </Book>
  <Book>
    <Title>The Catcher in the Rye</Title>
    <ID>5</ID>
    <Author>J.D. Salinger</Author>
  </Book>
</Books>

Show the filtered books

<Book>
  <Title>Don Quixote</Title>
  <ID>1</ID>
  <Author>Miguel de Cervantes</Author>
</Book>
<Book>
  <Title>The Little Prince</Title>
  <ID>3</ID>
  <Author>Antoine de Saint-Exupéry</Author>
</Book>
<Book>
  <Title>The Catcher in the Rye</Title>
  <ID>5</ID>
  <Author>J.D. Salinger</Author>
</Book>

Config File Connection String Syntax for SQL Express Databases

When creating an application that uses a SQL Express database, you may choose to use a database that exists locally within your application (i.e. the SQL Express database resides in the App_Data directory of your application) or you may prefer to use a database that has been registered with the SQLExpress Server (i.e. you have attached the database to your SQLExpress Server from within Microsoft SQL Server Management Studio – aka SSMS).

Depending on where your database exists, you must use the correct Connection String for your application to make a successful connection to your database. Typically (and preferably), I like to place my Connection String information in the App.Config or Web.Config file. Here is the correct Connection String XML tag syntax for the two different database locations that were previously mentioned.

<configuration>
<connectionStrings>
    <!-- This is an example of a connection string for a SQL Express database that lives in the application's App_Data directory -->
    <add name="LocalConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True;User Instance=True" />

    <!-- This is an example of a connection string for a SQL Express database that lives in the SQL Express Server's list of registered (or attached) databases. Attaching a database to the SQL Server is typically done via SQL Server Management Studio (SSMS) -->
    <add name="ServerConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE1;Integrated Security=True" />
</connectionStrings>
</configuration>

C# Code Examples of using the different Connection Strings

public class DAL
    {
        public static List&lt;UserModel&gt; GetUsers()
        {
            List<UserModel>; users = new List<UserModel>();
            
            // This example uses the connection string that points to the database that resides in the local App_Data directory of this application
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalConnection"].ToString()))
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM [User]", conn);
                conn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();

                while (sdr.Read())
                {
                    UserModel user = new UserModel();
                    user.EmailAddress = sdr["Email"].ToString();
                    user.Password = sdr["Password"].ToString();
                    user.UserId = (int)sdr["UserId"];
                    user.Username = sdr["Username"].ToString();
                    users.Add(user);
                }
            }

            return users;
        }
    }

How to create a T-SQL Function to parse a delimited list of numbers and return a table of those numbers

This post is related to and a supplement to a previous post “Pass Delimited List to SQL Server Stored Procedure”

Since parsing a delimited string and using the values of a delimited items is a common task in T-SQL, it is a good idea to create a T-SQL Function that will do the work of parsing the delimited string and then return the values in a Table.

Once the Function is created, you can call the Function from a Stored Procedure, Query, or whatever else can call a Function.

For our example, imagine you have a Table of Users. Where the table consists of the Columns: FirstName, LastName, EmailAddress, and ReceiveNewsletter. Our imaginary application has a feature that will send out monthly newsletters to users that would like to receive the monthly newsletters. To keep track of whether or not a user would like to receive the newsletter, we have a column in the Users table that accepts a bit value (True/False).

Now imagine the scenario where you want to create a Store Procedure that will update the ReceiveNewsletter column for multiple users using only one UPDATE statement. To do this we will create a sproc that receives a delimited list of UserId’s and a True/False value that specifies whether or not the specified UserId’s want to receive the newsletter or not.

However, due to T-SQL limitations, we cannot simply create an UPDATE statement in our sproc that looks like:

CREATE PROCEDURE MySproc @ids varchar(50) AS
UPDATE MyTable
SET Col1 = 1
WHERE  ID IN (@ids)

The result of calling the sproc above is an error message that reads something like:

Syntax error converting the varchar value ‘1,2,3’ to a column
of data type int.

(Assuming that the caller of the sproc sent “1,2,3” as the @ids value.

To work around this limitation, we will create a Function that does the work of parsing the delimited list of ids and then return the values in a Table. The caller of the Function can then simply SELECT the values from the returned table.

For example:

-- The caller of the sproc will send a delimited list of MyTable ID's such as "1,23,456,789"

-- Stored Procedure
CREATE PROCEDURE MySproc
@DelimitedList nvarchar(MAX)
AS
UPDATE MyTable
SET Column1 = 1
WHERE ID IN (SELECT * FROM MyFunction)
RETURN

Using Microsoft SQL Server Management Studio, this post will show you how to create the Function that will return a Table that consists of one column (named “Numbers”). The values/records of the “Numbers” column will consist of integer values that were parsed from a delimited string.

  1. Open Microsoft SQL Server Management Studio
  2. Open the database that you would like to add the Function to
  3. Add a table to the database named Users. You can add the table manually or just run this script on your database:
    CREATE TABLE [dbo].[Users](
    	[UserId] [int] IDENTITY(1,1) NOT NULL,
    	[FirstName] [nvarchar](50) NULL,
    	[LastName] [nvarchar](50) NULL,
    	[EmailAddress] [nvarchar](50) NULL,
    	[ReceiveNewsletter] [bit] NULL
    ) ON [PRIMARY]
    
    GO
    
    INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Tom', 'Araya', 'tom@slayer.com', 'False')
    INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Frank', 'Zappa', 'frank@zappa.com','False')
    INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Johann', 'Bach', 'js@bach.com','False')
    INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Mikael', 'Akerfeldt', 'growler@opeth.com','False')
    INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Sonny', 'Stitt', 'sonny@stitt.com','False')
    
  4. Navigate to the “Programmability” folder of your database
  5. Right-click the “Functions” folder and select “New < Multi-Statement Table-Valued Function”
  6. Add the following T-SQL code to your new Function:
    -- The name of our Function will be "ParseNumbersFromDelimitedList" and will take two Parameters: 1. the delimited list and 2. the delimiter
    CREATE FUNCTION [dbo].[ParseNumbersFromDelimitedList](@DelimitedList nvarchar(max),@Delimiter varchar(1))
    RETURNS
    
    -- Create the schema of the Table that we will return to the caller
    @table TABLE
    (Number int)
    AS
    BEGIN
    
    -- Remove white space from the list
    SET @DelimitedList = REPLACE(@DelimitedList, ' ','');
    
    -- Var that will hold the value of the delimited item during the while-loop
    DECLARE @ItemInScope VARCHAR(MAX)
    
    WHILE(LEN(@DelimitedList) > 0)
    BEGIN
    	-- If a delimiter exists in the string/list, then get the value to the left of the delimter.
    	-- Otherwise, just get the value of the string/list. This means that there is only one item in the string/list
    	IF(CHARINDEX(@Delimiter, @DelimitedList) > 0)
    		SET @ItemInScope = LEFT(@DelimitedList, CHARINDEX(@Delimiter, @DelimitedList))
    	ELSE
    		SET @ItemInScope = @DelimitedList
    
    	-- Remove the @NumberInScope value from the @NumericList
    	SET @DelimitedList = RIGHT(@DelimitedList, LEN(@DelimitedList) - LEN(@ItemInScope))
    
    	-- Remove the delimiter from the @NumberInScope
    	SET @ItemInScope = REPLACE(@ItemInScope,@Delimiter,'')
    
    	-- Print only the integer values
    	IF(ISNUMERIC(@ItemInScope) = 1)
    	BEGIN
    		-- Fill the table variable with the rows for your result set
    		INSERT INTO @table
    		(NUMBER)
    		VALUES
    		(@ItemInScope)
    	END
    END
    RETURN
    END
    
  7. Execute/Save the Function
  8. Now, we will create a Stored Procedure that will update the Users table. Specifically, we will create a sproc that will SET the “ReceiveNewsletter” column to ‘TRUE’ or ‘FALSE’ for records that have a matching UserId value that exists in a delimited string.

    In SSMS, add the following sproc:

    CREATE PROCEDURE SetReceiveNewsletter
    	@UserIdList NVARCHAR(MAX), -- The caller will pass a delimited string of UserId's
    	@Delimiter NVARCHAR(1) = ',', -- The caller can specify what the delimiter is. The default delimiter is a comma.
    	@ReceiveNewsletter bit = 'FALSE' -- The caller can specify 'TRUE' or 'FALSE' as the value that will update the records. Default is 'FALSE'.
    AS
    BEGIN
            -- Update User records where the UserId is in the @UserIdList
    	UPDATE dbo.Users
    	SET ReceiveNewsletter = @ReceiveNewsletter
    	WHERE UserId IN
                    -- Here is where we call the Function. The Function returns a Table of Numbers that existed in the @UserIdList
    		(SELECT * FROM dbo.ParseNumbersFromDelimitedList(@UserIdList,@Delimiter))
    END
    GO
    
  9. We now have a Function that does the dirty work of parsing a delimited string and returning any integers that were in the string (the UserId’s) in a Table that has a column named “Numbers”. All we need to do now is call the Stored Procedure …
  10. Using SSMS, call the sproc:
    EXEC	[dbo].[SetReceiveNewsletter]
    	@UserIdList = N'1,4,5', -- Here's a delimited string of UserId's that the caller passes to the sproc
    	@Delimiter = N',', -- The caller can specify the delimiter character
    	@ReceiveNewsletter = TRUE -- Set the ReceiveNewsletter column to TRUE for all UserId's that exist in the @UserIdList
    GO
    

    Here are the results:

    UserId FirstName LastName EmailAddress ReceiveNewsletter
    1 Tom Araya tom@slayer.com True
    2 Frank Zappa frank@zappa.com False
    3 Johann Bach js@bach.com False
    4 Mikael Akerfeldt growler@opeth.com True
    5 Sonny Stitt sonny@stitt.com True
  11. Conclusion
    Since T-SQL doesn’t parse a delimited string when using the IN clause in a T-SQL statement, we need to first parse the values out of the delimited string variable then add those values to a T-SQL Table. Then, we can use that Table to select the values within our statement.
    And since this is a common problem when SELECT’ing, UPDATE’ing, etc, we built a function that does the work of parsing the delimited string and adding those values to a Table. Callers of the Function simply have to pass the delimited string to the Function then work off of the returned Table.

If you are interested in learning more about working with lists and arrays in SQL Server, do yourself a favor and check out what Erland Sommarskog has to say.

Searching for a bracket ‘[‘ in a T-SQL string

In a Stored Procedure that I was working on, I needed to error trap a string variable to make sure that the value of the variable started with an opening bracket ‘[‘ and ended with a closing bracket ‘]’. My initial SQL looked something like this:

-- SQL example to prefix/suffix a string with brackets if they don't already exist
DECLARE @myString nvarchar(20)
SET @myString = '[ABC'
IF(@myString NOT LIKE '[%') -- If @myString doesn't start with '[' then prefix @myString with a '['
BEGIN
SET @myString = '[' + @myString
END

IF(@myString NOT LIKE '%]') -- If @myString doesn't end with ']' then suffix @myString with a ']'
BEGIN
SET @myString = @myString + ']'
END

PRINT @myString

To my surprise and confusion, the code example above did not result in the way that I thought it would. The output of the PRINT function resulted with:
[[ABC]

Note that the initial value of @myString was “[ABC”. I was expecting the first IF condition to return FALSE since @myString IS LIKE the expression ‘[%’. However, you’ll notice that the final output is “[[ABC”. Which means the first IF condition returned TRUE. Why?

The reason for this apparent bug is due to the fact that the brackets ‘[‘ and ‘]’ are special wildcard characters that can be used in conjunction with the LIKE Operator. In T-SQL you can use brackets to search a string for

Any single character within the specified range ([a-f]) or set ([abcdef]).

For more information on the bracket wildcard, go here.

Since the brackets are wildcards for the LIKE Operator, I needed to ESCAPE the bracket characters so that T-SQL would treat them as regular text. For more information on SQL Escape, go here.

With the help of my friends over at Stack Overflow, I learned that once I ESCAPE’d the brackets, my T-SQL code worked exactly how I thought it should. Here is the updated SQL that include the ESCAPE sequence:

-- SQL example to prefix/suffix a string with brackets if they don't already exist (this time with the ESCAPE sequence)
DECLARE @myString nvarchar(20)
SET @myString = '[ABC'
IF(@myString NOT LIKE '![%' ESCAPE '!') -- If @myString doesn't start with '[' then prefix @myString with a '['
BEGIN
SET @myString = '[' + @myString
END

IF(@myString NOT LIKE '%!]' ESCAPE '!') -- If @myString doesn't end with ']' then suffix @myString with a ']'
BEGIN
SET @myString = @myString + ']'
END

PRINT @myString

The output of the PRINT function resulted with:
[ABC]
Just as I expected.

Pass Delimited List to SQL Server Stored Procedure

This Stored Procedure shows how to accept a comma delimited list of ID’s and return records from a table that match the items of the delimited list:

-- =============================================
-- Description:	Returns one or more user records from the Users table.
-- This sproc allows clients to have to make only one call to retrieve more than one record from a table (as opposed to calling the sproc for each record).
-- The parameter is a varchar that can be a comma delimited list of UserId's.
---- For example: A parameter of '1,8,9,45' will return four users of which have a UserId
---- of 1, 8, 9, or 45
-- =============================================
ALTER PROCEDURE [dbo].[GetUsers]
	-- Declare the SP Parameter.
	@UserIdList Varchar(MAX) --The UserIdList is a comma delimited list of UserId's
AS
	--Create a var that defines the char that is used for the UserIdList delimiter
	--We need to do this because a list such as (1,2,4,99) returns true in the T-SQL IsNumeric function - which screws up the IsNumeric condition later on in this SP
	DECLARE @ListDelimiter VARCHAR(1)
	SET @ListDelimiter = ','

	--Remove white spaces from the parameter string
	SET @UserIdList=REPLACE(@UserIdList,' ','')

	--Create the temporary table that will hold rows of ID's that were passed in the parameter
	CREATE TABLE #tmpUserList (UserId INT)

	DECLARE @UserId INT --This UserId var will hold the UserId value that was derived from the UserIdList

	--Loop until the UserIdList var has a length of zero
	WHILE(LEN(@UserIdList) > 0)
		BEGIN
			print (@UserIdList)

			--Set the UserId to the first value in the UserIdList
			IF(CHARINDEX(@ListDelimiter, @UserIdList) > 0)
				SET @UserId = LEFT(@UserIdList, CHARINDEX(@ListDelimiter, @UserIdList) - 1)
			ELSE
				SET @UserId = @UserIdList

			--Remove the first value from the UserIdList
			SET @UserIdList = RIGHT(@UserIdList, LEN(@UserIdList) - LEN(@UserId))

			--Insert the UserId into the temporary table
			IF(ISNUMERIC(@UserId) = 1)
			INSERT INTO #tmpUserList (UserId) SELECT @UserId
		END
	BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Get all the users that have a matching UserId in the tmpUserList table
	SELECT DISTINCT U.UserId, U.UserName,U.Email,U.FirstName, U.LastName, U.Password, U.MobilePhone, U.IsActive, U.DateAdded
	FROM Users U JOIN #tmpUserList UL ON U.UserId = UL.UserId

	BEGIN
		DROP TABLE #tmpUserList
	END
	END

Thanks to Erland Sommarskog for this solution.