News
Microsoft Open Sources .NET 5 C# Language Extension for SQL Server
Microsoft has open sourced a .NET 5 C# Language Extension for SQL Server, allowing developers to work with relational data in the company's flagship programming language.
The new tool joins similar extensions the company has open sourced over the past year or so for other languages, all taking advantage of a new kind of Language Extensions architecture, a SQL Server feature used for executing external code. While such code execution is isolated from the core engine processes, it is fully integrated with SQL Server query execution.
[Click on image for larger view.] Component Architecture (source: Microsoft).
Microsoft says Language Extensions provides advantages such as:
- Data security. Bringing external language execution closer to the source of data avoids insecure data movement.
- Speed. Databases are optimized for set-based operations.
- Ease of deployment and integration. SQL Server is the central point of operations for many other data management tasks and applications. By using data in the database, you ensure that the data used by the language extension is consistent and up-to-date.
After previously open sourcing tools for Java and R/Python last year, the .NET Core CSharp Language Extension is now on GitHub.
In the SQL Server Language Extensions scheme, any supported external language can be run by calling a stored procedure, with the results being returned as tabular data directly to SQL Server so it can easily use the external language from any application that can send a SQL query and handle the results. In the new tool, that's done with a DataFrame, which stores data as a collection of columns.
"This extension is yet another example of using the developed programming language extensibility architecture which provides integration with a new type of language extension," said Nikita Takru, program manager, Azure Data Team, in a Sept. 8 blog post. "This latest architecture allows customers to have the freedom to use existing SQL Server tables which they can pass to a C# application as a DataFrame.
"Then, they can perform operations in C#, use the rich libraries, and obtain back a result set. One of the reasons to use C# is to reuse existing customer C# code, calculations, logic, or extensive libraries that provide functionality which you cannot get in T-SQL. We encourage developers to use the .NET 5 C# Language Extension and build on it."
Possible use case advantages include:
- Data cleaning.
- Fast data querying.
- Any processing in C# can now occur through a DataFrame.
- Customers are not limited to the T-SQL language surface area.
- C# application development teams that leverage SQL Server as backend storage can now even embed C# code in stored procedures which enables pushing business logic down into the database for better performance.
- Avoid unnecessary data movement and latency when data must be retrieved from SQL Server and moved into the app tier to do the business logic processing.
By studying the publicly documented Extensibility Framework API for SQL Server and the tool's source code on the above-linked GitHub repo, Takru said community developers can continue to develop and contribute additional programming language extensions.
Other resources to help learn about the new offering and such language extensions in general include a Regex Sample available on GitHub that shows how to create a .NET Core C# program that receives two columns (ID and text) from SQL Server and a regular expression as an input parameter. The class returns two columns back to SQL Server (ID and text).
About the Author
David Ramel is an editor and writer at Converge 360.