T-SQL into C#

Need to convert the following T-SQL code into C#

ALTER FUNCTION [dbo].[charcheck]
(@hs1 nvarchar(100),@hs2  nvarchar(100))

RETURNS numeric(5,2)

AS
BEGIN
      declare @l1 as int,@l2 as int,@p1 int
      declare @r1 as nvarchar(100),@r2 as nvarchar(100)
            
      set @l1=len(@hs1)
      set @l2=len(@hs2)
      
      set @r1=@hs1
      set @r2=@hs2
      
      declare @i as int
      declare @rst as int
      set @i=1
        
      while @i <= @l2
      begin
                  set @p1=charindex(substring(@hs2,@i,1),@r1)
                  if @p1>0
                        set @r1=stuff(@r1,@p1,1,'')
            set @i=@i+1
      end
      
      set @rst=len(@r1)
      
      set @i=1
      while @i <= @l1
      begin
            set @p1=charindex(substring(@hs1,@i,1),@r2)
                  if @p1>0
                        set @r2=stuff(@r2,@p1,1,'')
            set @i=@i+1
      end
      set @rst=@rst+len(@r2)
      
      declare @retval as numeric(5,2)
      declare @som2 as numeric(5,2)
      
      
      set @som2=cast((@l1+@l2) as numeric(5,2))
      set @retval=((@l1+@l2-@rst)/@som2)*100
      return @Retval
end

Open in new window

EasyCodeAsked:
Who is Participating?
 
gery128Connect With a Mentor Commented:
Hello EasyCode,

Refer this code, I by mistake initialized counter variable i as 1 in last code. You can change following
lines and run again.

int l1, l2, p1,i=1,rst; with this line: int l1, l2, p1,i=0,rst;  and
i=1; with i=0;  

I have pasted full code for your reference.

using System;
using System.Collections.Generic;
using System.Text;

namespace SQLFunctionTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Program pr = new Program();
            pr.GetValues("12345", "123");
        }


        public decimal GetValues(string val1, string val2)
        {

            int l1, l2, p1,i=0,rst;
            string r1 = val1, r2 = val2, s1 = String.Empty, s2 = String.Empty;
            l1 = val1.Length; l2 = val2.Length;

            Char[]  myCharArray;

            while (i < l2)
            {
                p1 = Convert.ToInt32(r1.IndexOf(val2.Substring(i, 1)));
                if (p1 >= 0)
                {
                    myCharArray = r1.ToCharArray();
                    myCharArray[p1] = ' ';
                    r1 = new string(myCharArray);
                }   
                i++;
            }
            foreach (char c in r1)
            {
                if (Convert.ToInt32(c) != 32)
                    s1 += c;
            }

            rst = s1.Length;
            i = 0;
            while (i < l1)
            {
                p1 = Convert.ToInt32(r2.IndexOf(val1.Substring(i, 1)));
                if (p1 >= 0)
                {
                    myCharArray = r2.ToCharArray();
                    myCharArray[p1] = ' ';

                    r2 = new string(myCharArray);
                }                
                i++;
            }
            foreach (char c in r2)
            {
                if (Convert.ToInt32(c) != 32)
                    s2 += c;
            }

            rst = rst + s2.Length;

            decimal som2 = Convert.ToDecimal(l1 + l2);
            decimal retVal = ((l1 + l2 - rst) / som2) * 100;

            return retVal;
        }
    }
}

Open in new window

0
 
lalitgadaCommented:
first tell me what you want to acheive through this code.
0
 
EasyCodeAuthor Commented:
Check performance between C# and TSQL. This TSQL function does a lot of string-manipulation and that is a weak spot in TSQL compared to C#.

This functions matches 2 strings and returns value on a scale of 100. 100 means exact match.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
EasyCodeAuthor Commented:

Lalitgada,

This code is part of the complete TSQL function and does individual character matching between 2 strings.
0
 
Gene_CypCommented:
Are you allowed to use LINQ (C# 3)?
0
 
EasyCodeAuthor Commented:
Sure I'm
0
 
gery128Commented:
Hello EasyCode:

Find C# code below to get started: Let me know the exact logic so I can post better code.
class Program
    {
        static void Main(string[] args)
        {
            Program pr = new Program();
            pr.GetValues("12", "123");
        }


        public decimal GetValues(string val1, string val2)
        {

            int l1, l2, p1,i=1,rst;
            string r1 = val1, r2 = val2, s1 = String.Empty, s2 = String.Empty;
            l1 = val1.Length; l2 = val2.Length;

            Char[]  myCharArray;

            while (i < l2)
            {
                p1 = Convert.ToInt32(r1.IndexOf(val2.Substring(i, 1)));
                if (p1 > 0)
                {
                    myCharArray = r1.ToCharArray();
                    myCharArray[p1] = ' ';
                    r1 = new string(myCharArray);
                }   
                i++;
            }
            foreach (char c in r1)
            {
                if (Convert.ToInt32(c) != 32)
                    s1 += c;
            }

            rst = s1.Length;
            i = 1;
            while (i < l1)
            {
                p1 = Convert.ToInt32(r2.IndexOf(val1.Substring(i, 1)));
                if (p1 > 0)
                {
                    myCharArray = r2.ToCharArray();
                    myCharArray[p1] = ' ';

                    r2 = new string(myCharArray);
                }                
                i++;
            }
            foreach (char c in r2)
            {
                if (Convert.ToInt32(c) != 32)
                    s2 += c;
            }

            rst = rst + s2.Length;

            decimal som2 = Convert.ToDecimal(l1 + l2);
            decimal retVal = ((l1 + l2 - rst) / som2) * 100;

            return retVal;
        }
    }

Open in new window

0
 
EasyCodeAuthor Commented:
Thx for your code gery128
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.