Question How to write c# function convert text input to sql server statement return string ?

[ahmedaziz]

I work on some ui web tool on asp.net core blazor

i need to write csharp function convert this conditions to sql statement so

when i filter two columns as databasename and remarks

(databaseName == null ? "" : databaseName).ToLower().Contains("db_".ToLower()) and (remarks == null ? "" : remarks).ToLower().Contains("adc".ToLower())

so csharp function will convert text received to sql statement

databaseName like '%db_%' and Remarks like '%adc%'

so csharp function will return string and it will take only one parameters text

as

(databaseName == null ? "" : databaseName).ToLower().Contains("db_".ToLower()) and (remarks == null ? "" : remarks).ToLower().Contains("adc".ToLower())

and it will return

databaseName like '%db_%' and Remarks like '%adc%'

I need to do it with dynamic way so may be it have one column filter may be 2 columns or 3 column filter

then convert it to sql statement



what i try but not give me exact result i need

     {
         var pairs = new List<string>();
         string[] substrings = input.Split(new[] { "Contains(" }, StringSplitOptions.None);

         foreach (string substring in substrings)
         {
             int index = substring.IndexOf(".ToLower()");

             if (index != -1)
             {
                 string key = substring.Substring(0, index).Trim().Split(' ')[0];
                 string value = substring.Substring(index + ".ToLower()".Length).Trim().TrimEnd(')');

                 pairs.Add($"{key} like '{value}'");
             }
         }

         return string.Join(" and ", pairs);
     }

 

[cjard]

EF core team spend months writing code that does this. I think you're asking too much of people on a forum

 

[Skydiver]

And the EF core team also had the luxury of build off of the Expression tree to build the SQL. You are are asking to write a compiler that will first parse the C# like string into an expression tree, and then generate the SQL from the expression tree.

 

[cjard]

I suppose you could always leverage it by getting EF to do it!

 

[gulshan212]

Please try below code once:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

public class Program
{
    public static void Main()
    {
        string filterCondition = "(databaseName == null ? \"\" : databaseName).ToLower().Contains(\"db_\".ToLower()) and (remarks == null ? \"\" : remarks).ToLower().Contains(\"adc\".ToLower())";
        string sqlStatement = ConvertToSql(filterCondition);
        Console.WriteLine(sqlStatement);
    }

    public static string ConvertToSql(string input)
    {
        var conditions = ParseConditions(input);
        var sqlConditions = conditions.Select(c => ConvertConditionToSql(c)).ToList();
        return string.Join(" and ", sqlConditions);
    }

    public static List<string> ParseConditions(string input)
    {
        var matches = Regex.Matches(input, @"(\w+)\s*==\s*null\s*\?\s*""""\s*:\s*(\w+).*?\.ToLower\(\)\.Contains\(""(.*?)""\s*\.\ToLower\(\)\)");

        var conditions = new List<string>();
        foreach (Match match in matches)
        {
            string columnName = match.Groups[1].Value;
            string valueName = match.Groups[2].Value;
            string filterValue = match.Groups[3].Value;
            conditions.Add($"{columnName} like '%{filterValue}%'");
        }

        return conditions;
    }

    public static string ConvertConditionToSql(string condition)
    {
        return $"({condition})";
    }
}

Thanks

 

[Skydiver]

What happened to the % wildcards around the value?

Also how will that handle these perfectly legal C# query?

(@123 == null ? "" : @123).ToLower().Contains("abc".ToLower())
(@and == null ? "" : @and).ToLower().Contains("%".ToLower())