Translate Sql Query to Clr

Question

Translate Sql Query to Clr

meryemkurs072

SSC Eights!

Points: 945
More actions
January 5, 2019 at 11:43 am

#381116

Hello,
How can I write the following query in c# clr ?
CREATE FUNCTION SQL_HT ( @param1 INT, @param2 INT)RETURNS@tablo TABLE( colon1 VARCHAR(15), colon2 INT)AS BEGIN INSERT INTO @tablo (colon1,colon2) SELECT 'D','1' RETURNEND
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions{ [Microsoft.SqlServer.Server.SqlFunction] public static SqlString SqlFunction2() { ??? return new SqlString (string.Empty); }}

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 1

What are you trying to accomplish? The T-SQL function doesn't even do anything. Is the goal just to learn more about SQLCLR? If so, you can take a look at the series I am writing on this topic, here on SQL Server Central: Stairway to SQLCLR

You can also visit: SQLCLR Info

Take care,
Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

meryemkurs072 SSC Eights! Points: 945 More actions · Answer 2

meryemkurs072

SSC Eights!

Points: 945

January 5, 2019 at 9:58 pm

#2018081

Thank you

how to translate t-sql into clr

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 3

You don't really translate T-SQL into SQLCLR. In SQLCLR, you still use T-SQL to interact with SQL Server. So there are different ways of accomplishing certain goals using SQLCLR, and there are restrictions, and best-practices, etc that apply to different situations. So please give more details so that I / we know how to provide real help rather than just provide info that might help or might appear to work yet actually make things worse.

Take care, Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

meryemkurs072 SSC Eights! Points: 945 More actions · Answer 4

Thank you for your attention.

I want to translate query security to sqlclr.

I need this c # clr version of this query

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

meryemkurs072 - Sunday, January 6, 2019 12:16 PM
Thank you for your attention.
I want to translate query security to sqlclr.
I need this c # clr version of this query

Security? Is the kind of "security" that you're talking about center around some odd notion that you have proprietary code that you don't want anyone to be able to figure out or steal? If so, have you ever heard of a thing called a "decompiler", which will lay waste to that notion?

If you talking about some form of access security, then you're going about thing the hard way. It's easy to grant someone exec privs for someone to execute a stored procedure without give them privs to see any of the underlying objects, need any privs other than PUBLIC and the privs to execute the proc, or even view the code for the stored procedure.

I'm thinking that converting T-SQL to an SQLCLR is serious overkill here that will cause a fair bit of downstream pain especially when you run into someone like me...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meryemkurs072 SSC Eights! Points: 945 More actions · Answer 6

Thank you Jeff

Is there an application that translates the following code

CREATE ASSEMBLY [Database8] AUTHORIZATION [dbo] FROM 0x4D5A9000030000000400

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

meryemkurs072 - Sunday, January 6, 2019 12:38 PM
Thank you Jeff
Is there an application that translates the following code
CREATE ASSEMBLY [Database8] AUTHORIZATION [dbo] FROM 0x4D5A9000030000{redacted for safety}0000000000000000000000

That's just the binary for code (DLL, IIRC). Anyone with a decompiler could convert it to something more readable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 8

meryemkurs072 - Sunday, January 6, 2019 12:38 PM
Is there an application that translates the following code
CREATE ASSEMBLY [Database8] AUTHORIZATION [dbo] FROM 0x4D5A90000300...redacted...000

There are probably several decompilers that can easily read that Assembly. The one I use is ILSpy ( http://www.ilspy.net/ ). In fact, I have already imported, exported, and decompiled your assembly. To prove it, without giving up any of your intellectual property, I will point out 3 mistakes you have made (outside of trying to do this in SQLCLR):

You should not use the "string" type for input parameter or return types. Use "SqlString" instead. And get the .NET string out of it by using the "Value" property. For example: "paramName.Value".
You should probably not be using global temporary tables as they will conflict with any concurrent use of this stored procedure. Use local temporary tables unless you have a specific reason to need global temp tables. But if you do use global temp tables, you need to build in logic to make this thread safe.
You need to wrap the SqlCommand and SqlDataReader instantiations in "using()" blocks because they are both disposable resources.

AND, not only was I able to easily enough get the .NET code, but all T-SQL still executes against SQL Server and is thereby visible to SQL Server Profiler and Extended Events. So even if you were able to hide some of the .NET business logic, you still can't hide the T-SQL stuff.

By the way: THIS is exactly why I asked for more details. Simply answering the initial question would have allowed you to write a TVF, but it wouldn't have solved your problem. Not only would it have given you a false sense of accomplishing the security part, it wouldn't have allowed you to copy this particular logic (if that is what you are wanting to do) since this stored procedure inserts data into the temp table, and that can only be done by either a stored procedure OR a function IF it connects using a regular / external connection, not using the context connection (which this stored proc is using). Which means that the Assembly couldn't stay marked as SAFE. As I said before, there are lots of considerations to make and they require knowing the entire problem / project.

Take care, Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 9

meryemkurs072 - Sunday, January 6, 2019 12:16 PM
I want to translate query security to sqlclr.

If you are truly concerned at all about security, I highly recommend that you edit your post here where you have the CREATE ASSEMBLY statement and cut out the middle part like I did when I replied.

I also recommend to Jeff to do the same (i.e. update his reply which quotes that code block to redact the middle section -- all but the first few and last few bytes).

Otherwise, why claim to care about security if you are going to post your company's code for the whole world to see?

Take care, Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 10

Solomon Rutzky - Sunday, January 6, 2019 2:59 PM
I also recommend to Jeff to do the same (i.e. update his reply which quotes that code block to redact the middle section -- all but the first few and last few bytes).

Done. And good idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meryemkurs072 SSC Eights! Points: 945 More actions · Answer 11

Thank you Jeff, Solomon

Are there other solutions for query encryption?

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 12

meryemkurs072 - Monday, January 7, 2019 11:49 AM
Thank you Jeff, Solomon
Are there other solutions for query encryption?

Nothing that a good DBA can't eventually crack. If SQL Server can interpret it, so can the DBA. This is why some people elect to do this stuff in an app. They think their proprietary stuff is safe there. It's not for the same reasons. And a line sniffer can ferret out the rest. About the best you're going to do is keep the honest man honest.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 13

meryemkurs072 - Monday, January 7, 2019 11:49 AM
Are there other solutions for query encryption?

No, not that I am aware of. Any tricks you can do will only prevent those who are just curious from getting your code. Anyone who is skilled and serious about getting code will eventually get the code. Large companies and governments are far more secure than you and still have their secrets stolen.

If you want to protect your company / efforts, then here is what I have recommended to others:

Innovate better than your competition: Find more / better ways of making life easier for your customers.
Provide a quality product: test as much as possible so that there are as few bugs as possible (reducing customer frustration)
Provide documentation: so that customers know how to use your product without having to contact you, OR not contact you and then either make mistakes or not use the product effectively.
Provide quality support: people always have questions or run into problems no matter how much testing you do or documentation you provide. respond quickly, clearly, and politely.

Good luck and take care,
Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

meryemkurs072 SSC Eights! Points: 945 More actions · Answer 14

Thank you so much.

I'm still not understanding how you read the ASSEMBLY code