Eval SQL.NET

Download Count:
EASY TO
USE
Extend SQL with Dynamic Evaluation
-- Evaluate dynamically expression in T-SQL
DECLARE @tableFormula TABLE (
Formula VARCHAR(255), X INT, Y INT, Z INT
)

INSERT INTO @tableFormula VALUES ('x+y*z', 1, 2, 3 ),
('(x+y)*z', 1, 2, 3 )
-- SELECT 7
-- SELECT 9
SELECT SQLNET::New(Formula)
.ValueInt('x', X)
.ValueInt('y', Y)
.ValueInt('z', Z).EvalInt() as Result
FROM @tableFormula

Extend SQL with Dynamic Evaluation at Runtime Using C# Expression.

Amazing performance, outstanding support!

We were very, very pleased with the customer support. There was no question, problem or wish that was not answered AND solved within days! We think that’s very unique!

I’d definitely recommend it as it is a great product with a great performance and reliability.

Evaluate dynamic arithmetic expression in SQL

Make the impossible now possible. Evaluate C# expression in SQL to overcome limitations.

  • Allow trusted users to create report field and filter
  • Consume Web Service
  • Replace text in template with String Interpolation
Dynamic Expression Example
-- Easy to use

-- CREATE test
DECLARE @table TABLE ( X INT, Y INT, Z INT )
INSERT INTO @table VALUES ( 2, 4, 6 ), ( 3, 5, 7 ), ( 4, 6, 8 )

-- Result: 14, 22, 32
DECLARE @sqlnet SQLNET = SQLNET::New('x*y+z')
SELECT @sqlnet.ValueInt('x', X)
.ValueInt('y', Y)
.ValueInt('z', Z)
.EvalInt() as Result
FROM @table

Split text with delimiter

Improve performance and capability for splitting text with an easy to use split function and LINQ expressions.

  • Split text with multiple delimiters
  • Split text using a regular expression
  • Include row index
Split Text Example
-- CREATE test
DECLARE @t TABLE (Id INT , Input VARCHAR(MAX))
INSERT INTO @t VALUES ( 1, '1, 2, 3; 4; 5' ), ( 2, '6;7,8;9,10' )

-- SPLIT with many delimiters: ',' and ';'
DECLARE @sqlnet SQLNET = SQLNET::New('Regex.Split(input, ",|;")')

SELECT *
FROM @t AS A
CROSS APPLY ( SELECT *
FROM dbo.SQLNET_EvalTVF_1(@sqlnet.ValueString('input', Input))
) AS B

Use regular expression in SQL Server

Use Regex flexibility to overcome "LIKE" and "PATHINDEX" limitations.

  • IsMatch
  • Match
  • Matches
  • Replace
  • Split
Regular Expression Example
DECLARE @customer TABLE ( Email VARCHAR(255) )

INSERT INTO @customer
VALUES ( 'info@zzzprojects.com' ),
( 'invalid.com' ),
( 'sales@zzzprojects.com' )

DECLARE @valid_email SQLNET = SQLNET::New('Regex.IsMatch(email,
@"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$")')

-- SELECT 'invalid.com'
SELECT * FROM @customer WHERE @valid_email.ValueString('email', Email).EvalBit() = 0

Replace xp_cmdshell with restrictive alternative

void enabling xp_cmdshell and compromising your SQL Server and use instead a more restrictive solution.

  • Impersonate Context
  • Improve maintainability
  • Improve readability
  • Improve security
Example
-- REQUIRE EXTERNAL_ACCESS permission
DECLARE @sqlnet SQLNET = SQLNET::New('
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

var dir = new DirectoryInfo(path);
return dir.GetFiles("*.*")
.Select(x => new { x.FullName, FileContent = File.ReadAllText(x.FullName) })
.OrderBy(x => x.FullName)')
.Impersonate()

-- SELECT FullName, FileContext FROM DesktopFiles ORDER BY Fullname
EXEC dbo.SQLNET_EvalResultSet @sqlnet