Translate Sql Query to Clr

  • meryemkurs072

    SSChasing Mays

    Points: 611

    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);    }}

  • Solomon Rutzky

    SSCoach

    Points: 16129

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • meryemkurs072

    SSChasing Mays

    Points: 611

    Thank you

    how to translate t-sql into clr

  • Solomon Rutzky

    SSCoach

    Points: 16129

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • meryemkurs072

    SSChasing Mays

    Points: 611

    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: 994677

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • meryemkurs072

    SSChasing Mays

    Points: 611

    Thank you Jeff

    Is there an application that translates the following code

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

  • Jeff Moden

    SSC Guru

    Points: 994677

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Solomon Rutzky

    SSCoach

    Points: 16129

    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):

    1. 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".
    2. 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.
    3. 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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Solomon Rutzky

    SSCoach

    Points: 16129

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Jeff Moden

    SSC Guru

    Points: 994677

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • meryemkurs072

    SSChasing Mays

    Points: 611

    Thank you Jeff, Solomon

    Are there other solutions for query encryption?

  • Jeff Moden

    SSC Guru

    Points: 994677

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Solomon Rutzky

    SSCoach

    Points: 16129

    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:

    1. Innovate better than your competition: Find more / better ways of making life easier for your customers.
    2. Provide a quality product: test as much as possible so that there are as few bugs as possible (reducing customer frustration)
    3. 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.
    4. 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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • meryemkurs072

    SSChasing Mays

    Points: 611

    Thank you so much.

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

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

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