How do you implement the equivalent of SQL IN() using .net
using System;
using System.Linq;
static class SqlStyleExtensions
{
public static bool In(this string me, params string[] set)
{
return set.Contains(me);
}
}
Usage:
if (Variable.In("AC", "BC", "EA"))
{
}
SQL Server in equivalent in .Net
Threre is no keyword, however you can use the following construct:
using System.Linq;
...
(new [] {12, 23, 45}).Contains(customerID)
Equivalent of SQL IN in VB.NET
After a bit of playing around, I have managed to find a nice solution:
Select Case True
Case Array.IndexOf(New Integer() {2, 3}, SectionID) > -1 : SecondLineHolder.Attributes("style") = "color:#21720B"
Case Array.IndexOf(New Integer() {8, 12}, PageID) > -1 : SecondLineHolder.Attributes("style") = "color:#1B45C2"
End Select
C# Version Of SQL LIKE
Regular expressions allow for everything that LIKE
allows for, and much more, but have a completely different syntax. However, since the rules for LIKE
are so simple(where %
means zero-or-more characters and _
means one character), and both LIKE
arguments and regular expressions are expressed in strings, we can create a regular expression that takes a LIKE
argument (e.g. abc_ef% *usd
) and turn it into the equivalent regular expression (e.g. \Aabc.ef.* \*usd\z
):
@"\A" + new Regex(@"\.|\$|\^|\{|\[|\(|\||\)|\*|\+|\?|\\").Replace(toFind, ch => @"\" + ch).Replace('_', '.').Replace("%", ".*") + @"\z"
From that we can build a Like()
method:
public static class MyStringExtensions
{
public static bool Like(this string toSearch, string toFind)
{
return new Regex(@"\A" + new Regex(@"\.|\$|\^|\{|\[|\(|\||\)|\*|\+|\?|\\").Replace(toFind, ch => @"\" + ch).Replace('_', '.').Replace("%", ".*") + @"\z", RegexOptions.Singleline).IsMatch(toSearch);
}
}
And hence:
bool willBeTrue = "abcdefg".Like("abcd_fg");
bool willAlsoBeTrue = "abcdefg".Like("ab%f%");
bool willBeFalse = "abcdefghi".Like("abcd_fg");
Is there a .NET equivalent to SQL Server's newsequentialid()
It should be possible to create a sequential GUID in c# or vb.net using an API call to UuidCreateSequential. The API declaration (C#) below has been taken from Pinvoke.net where you can also find a full example of how to call the function.
[DllImport("rpcrt4.dll", SetLastError=true)]
static extern int UuidCreateSequential(out Guid guid);
The MSDN article related to the UuidCreateSequential function can be found here which includes the prerequisites for use.
linq equivalent sql query not in (select query)
my first try using LiNQ
in C#
var result = from y in aspnet_Users
where !(
from x in tblUser
where x.active == "true"
select x.UsersID
).Contains(y.UserId)
select y;
-- OR // select new { y.UserId, y.UserName};
SOURCE
- The NOT IN clause in LINQ to SQL
LINQ equivalent of SQL LIKE for text field with digits
This worked.
.Where(p => EF.Functions.Like(p.Value, "100[0-9][0-9][0-9][0-9]"))
Thanks to all the advice in the comments!
C# Equivalent of SQL Server DataTypes
This is for SQL Server 2005. There are updated versions of the table for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014.
SQL Server Data Types and Their .NET Framework Equivalents
The following table lists Microsoft SQL Server data types, their equivalents in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the Microsoft .NET Framework.
SQL Server data type | CLR data type (SQL Server) | CLR data type (.NET Framework) |
---|---|---|
varbinary | SqlBytes, SqlBinary | Byte[] |
binary | SqlBytes, SqlBinary | Byte[] |
varbinary(1), binary(1) | SqlBytes, SqlBinary | byte, Byte[] |
image | None | None |
varchar | None | None |
char | None | None |
nvarchar(1), nchar(1) | SqlChars, SqlString | Char, String, Char[] |
nvarchar | SqlChars, SqlString | String, Char[] |
nchar | SqlChars, SqlString | String, Char[] |
text | None | None |
ntext | None | None |
uniqueidentifier | SqlGuid | Guid |
rowversion | None | Byte[] |
bit | SqlBoolean | Boolean |
tinyint | SqlByte | Byte |
smallint | SqlInt16 | Int16 |
int | SqlInt32 | Int32 |
bigint | SqlInt64 | Int64 |
smallmoney | SqlMoney | Decimal |
money | SqlMoney | Decimal |
numeric | SqlDecimal | Decimal |
decimal | SqlDecimal | Decimal |
real | SqlSingle | Single |
float | SqlDouble | Double |
smalldatetime | SqlDateTime | DateTime |
datetime | SqlDateTime | DateTime |
sql_variant | None | Object |
User-defined type(UDT) | None | user-defined type |
table | None | None |
cursor | None | None |
timestamp | None | None |
xml | SqlXml | None |
How to do SQL Like % in Linq?
.Where(oh => oh.Hierarchy.Contains("/12/"))
You can also use .StartsWith()
or .EndsWith()
.
What is the linq equivalent to the SQL IN operator
.Contains
var resultset = from x in collection where new[] {2,3,4,5}.Contains(x) select x
Of course, with your simple problem, you could have something like:
var resultset = from x in collection where x >= 2 && x <= 5 select x
Related Topics
Getting Db Connection Through Singleton Class
Executing a Cygwin Process from .Net
Enter Key Pressed Event Handler
Calling a JavaScript Function in The C# Webbrowser Control
Interop.Word Documents.Open Is Null
Aes Encryption in Java and Decryption in C#
SQL Connection String for Microsoft Access 2010 .Accdb
Maxlength Attribute Not Generating Client-Side Validation Attributes
How Does Java's Use-Site Variance Compare to C#'s Declaration Site Variance
How to Develop iOS App Using Xamarin Studio on Windows
Getters, Setters, and Properties Best Practices. Java VS. C#
.Net Vs Java Garbage Collector
Method Overloading. Can You Overuse It
Creating Safe SQL Statements as Strings