You must Sign In to post a response.
  • Category: ASP.NET

    How Write same function of sql in c#

    Hi,
    How Write same function of sql in c#. Now our software audit has been started. so I want same sql function convert into c#. So when user send username and password to check exist or not. that time password must be send by Encrypt.

    CREATE FUNCTION [dbo].[ufn_EncryptString] ( @pClearString VARCHAR(100) )
    RETURNS NVARCHAR(100) WITH ENCRYPTION AS
    BEGIN

    DECLARE @vEncryptedString NVARCHAR(100)
    DECLARE @vIdx INT
    DECLARE @vBaseIncrement INT

    SET @vIdx = 1
    SET @vBaseIncrement = 128
    SET @vEncryptedString = ''

    WHILE @vIdx <= LEN(@pClearString)
    BEGIN
    SET @vEncryptedString = @vEncryptedString +
    NCHAR(ASCII(SUBSTRING(@pClearString, @vIdx, 1)) +
    @vBaseIncrement + @vIdx - 1)
    SET @vIdx = @vIdx + 1
    END

    RETURN @vEncryptedString

    END
    GO
  • #765308
    There are couple of ways to do it
    1. call SQL function directly from c# using below snippet
    Execute the SQL function using the ExecuteScalar() method. You can then cast the result of this to a boolean.
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand com = new SqlCommand("Execute dbo.usp_MyStoredProc", con);
    return (Boolean)com.ExecuteScalar();

    2. Convert logic to c#
    In your SQL function you have used 3 different function in SQL
    a. ASCII : Returns the ASCII code value of the leftmost character of a character expression.
    b. SUBSTRING : Returns part of a character, binary, text, or image expression in SQL Server.
    c. NCHAR : NCHAR function returns the Unicode character based on the number_code in the Unicode standard.

    see below code to convert char to unicode
    string text = "0 a+…"";
    foreach (char c in text)
    {
    Console.WriteLine("{0} U+{1:x4} {2}", c, (int)c, (int)c);
    }


    You can write it in C#

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765337
    You can use following code snippet of sql in c Sharp

    function ufn_EncryptString string (pClearString string)
    {


    string vEncrypt = string.Empty;
    int vIdx = 1;
    int vBaseIncrement = 128;


    while(vIdx <= pClearString.length){

    vEncrypt = vEncrypt + Char.ConvertFromUtf32(pClearString.Substring(vIdx,1) + vBaseIncrement + vIdx - 1);
    vIdx = vIdx + 1;

    }

    return vEncrypt;
    }


Sign In to post your comments