One of the things that we need to do every now and then is be able to select from a table based on a specific list of primary key IDs. For instance, we may have a UI with checkboxes that allow users to select specific products. We would then retrieve the ids for the selected products and build this into a list that we would want to make use of the SQL query, something like:
SELECT * FROM Products WHERE ProductID IN (1, 2, 3, 4)
There are a few ways to do this, including
- building dynamic SQL in code
- building dynamic SQL in a sproc (and using the 'exec' command)
- using T-SQL functions (like COALESCE or ISNULL)
The first instance, building dynamic SQL in code, is pretty ugly, because it involved hard-coding portions of the SQL itself, like this:
public List<Product> GetProducts(List<int> productIDs)
{
string SQL = "SELECT * FROM Products WHERE ProductID IN (";
foreach (int productID in productIDs)
{
SQL += productID + ", ";
}
if (SQL.EndsWith(", ")) SQL = SQL.Substring(0, SQL.Length - 2);
SQL += ")";
//...Execute the sql...
}
The 2nd approach can be solved within a stored procedure using the Exec or sp_executesql commands, like so:
Create Procedure Search
@strIDs VarChar(100)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'
Exec ( @SQL)
GO
But this is still quite ugly, because we still need to build a string of SQL, we need certain security rights, and we're limited to the length of string that Exec or sp_executesql allow.
The third approach is probably the cleanest, most secure, and most structured. Here is an example:
Create Procedure sp_EmployeeSelect_Coalesce
@EmployeeName NVarchar(100),
@Department NVarchar(50),
@Designation NVarchar(50),
@StartDate DateTime,
@EndDate DateTime,
@Salary Decimal(10,2)
AS
Set NoCount ON
Select * From tblEmployees
where EmployeeName = Coalesce(@EmployeeName, EmployeeName) AND
Department = Coalesce(@Department, Department ) AND
Designation = Coalesce(@Designation, Designation) AND
JoiningDate >= Coalesce(@StartDate, JoiningDate) AND
JoiningDate <= Coalesce(@EndDate, JoiningDate) AND
Salary >= Coalesce(@Salary, Salary)
If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)
ErrorHandler:
Return(@@ERROR)
GO
(the example is from this CodeProject article, and here's another similar technique from SQLTeam.com). The problems with this approach though are that you have to code for each field by hand, and it also won't do a dynamic list, which is what we're trying to achieve.
The LINQ To SQL Where In Version
In LINQ To SQL, we now have a new approach to solving this problem. We can make use of the list parameter directly, and LINQ To SQL will generate the SQL for us. This means we can write the following:
public List<Product> GetProducts(List<int> productIDs)
{
using (NorthwindDataContext dc = new NorthwindDataContext())
{
var products = from p in dc.Products
where productIDs.Contains(p.ProductID)
select p;
return products.ToList();
}
}
And have it generate the following SQL:
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID
], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnO
rder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductID] IN (@p0, @p1, @p2, @p3)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
It's a little bit different in terms of the structuring because we're used to specifying the table's column first, then the list, whereas here we need to specify the list.Contains(), but I guess we're already getting used to the From .. Where .. Select ;->
I was just reading a blog post this morning and I noticed that the code sample was doing a string.Split, but that instead of passing a Char datatype directly, the guy was passing a single-character string, then using the ToCharArray() method to convert it to a one-element array of Chars. I've seen this a couple of times before and I think it's because people aren't aware of the syntax to denote a char directly. To embed a string into code we all know:
string helloWorld = "Hello, World";
so, to get a char, the original code looked something like this:
Char c = "a".ToCharArray()[0];
however, to code a Char directly, you just need to use single quotation marks instead of doubles, as follows:
Char c = 'a';
While we're touching on this topic, there are also shorthand ways to denote a specific numeric type as well. Most of them can be found here (the only one I can see if doesn't discuss if double, which is denoted by a 'd' following the number, e.g 715d. For a more detailed look, and some more syntax for declaring chars (amongst a bunch of other stuff), check this C# primer, and to see some tips on formatting numeric strings, see this msdn link.
In case you haven't heard about the awesome new gallery of Visual Studio extensions (http://www.visualstudiogallery.com/), check it out cos there is some great stuff up there. One of these is the new PowerCommands for Visual Studio 2008, which is from MS. It's a list of additional useful functionality that MS might even include in future versions of Studio. They sound like great tools -
Collapse Projects
This command collapses a hierarchy in the solution explorer starting from the root selected node. It can be executed from three different places: solution, solution folders and project nodes respectively.
Copy Class
This command copies a selected class entire content to the clipboard. It can be executed from a single project item or a project item with dependent sub items.
Paste Class
This command pastes a class entire content from the clipboard. It can be executed from a project or folder node.
Copy References
This command copies a reference or set of references to the clipboard. It can be executed from the references node, a single reference node or set of reference nodes.
Paste References
This command pastes a reference or set of references from the clipboard. It can be executed from different places depending on the type of project. For CSharp projects it can be executed from the references node. For Visual Basic and Website projects it can be executed from the project node.
Copy As Project Reference
This command copies a project as a project reference to the clipboard. It can be executed from a project node.
Edit Project File
This command opens the MSBuild project file for a selected project inside Visual Studio. It can be executed from a project node.
Open Containing Folder
This command opens a Windows Explorer window pointing to the physical path of a selected item. It can be executed from a project item node
Open Command Prompt
This command opens a Visual Studio command prompt pointing to the physical path of a selected item. It can be executed from four different places: solution, project, folder and project item nodes respectively.
Unload Projects
This command unloads all projects in a solution. It can be executed from the solution node.
Reload Projects
This command reloads all unloaded projects in a solution. It can be executed from the solution node.
Remove and Sort Usings
This command removes and sort using statements for all classes given a project. It can be executed from a solution node or a single project node.
Note: The Remove and Sort Usings feature is only available for C# projects since the C# editor implements this feature as a command in the C# editor (which this command calls for each .cs file in the project). The Visual Basic IDE implements this functionality for Imports in an interactive way: Project properties, go to the References tab, then click the Unused References... button, then select which references you want removed via a listbox.
Extract Constant
This command creates a constant definition statement for a selected text. It can be executed from the code window over a selected text.
Clear Recent File List
This command clears the Visual Studio recent file list.
Clear Recent Project List
This command clears the Visual Studio recent project list.
Transform Templates
This command executes the associated custom tool with text templates items. It can be executed from a DSL project node or a folder node.
Close All
This command closes all documents. It can be executed from a document tab.