How can i compare an array with sql query in c# code?

Question

How can I compare an array with a SQL query in C#?

I have tried below code to resolve my problem but it was not worked.

In the below code data is the name of array which holds some types of data.

DataTable dt = context.getData("Select * from emp_detail where EState = any(" +data+ ")", CommandType.Text);

Does this answer your question? [Compare a Datatable and string array in c#](https://stackoverflow.com/questions/21529534/compare-a-datatable-and-string-array-in-c-sharp) — SMor, Nov 10 '20 at 16:34

score 0 · Answer 1 · answered Nov 10 '20 at 16:33

0

You can serialize the data array into a properly formatted string and change select command to something like that:

Select * from emp_detail where EState in ('a', 'b', ...)

This solution is only suitable for arrays with few items.

answered Nov 10 '20 at 16:33

v m

195
5

And how would you do that in a way that prevents SQL injection? – Johnathan Barclay Nov 10 '20 at 16:52

Aleksandr Kurilov · Answer 2 · 2020-11-10T18:46:37.623

I think you could do this to get a list of strings to compare with:

using System.Linq;

namespace ExampleNamespase
{
    public class SimpleClass
    {
        private class TypeExample
        {
            public string Property { get; }
        }

        private void CompareData(TypeExample[] exampleArray)
        {
            var items = string.Join(", ", exampleArray.Select(t => t.Property));
            var sql = $"Select * from emp_detail where EState in ({items}) ";
        }
    }
}

Or so if you are using a simple list of strings:

private void CompareData(string[] exampleArray)
{
    var items = string.Join(", ", exampleArray);
    var sql = $"Select * from emp_detail where EState in ({items}) ";
}

To prevent injections, use the Dapper library:

using System.Data;
using Dapper;

namespace ExampleNamespase
{
    public class SimpleClass
    {
        private void CompareData(string[] exampleArray, IDbConnection connection)
        {
            var sql = "Select * from emp_detail where EState in @items ";

            var result = connection.Query<YourTypeClass>(sql, new { items = exampleArray });
        }
    }
}

Explanation: The first example uses the Linq library to retrieve the properties of an object using the Select method, and the string.Join method to concatenate the resulting list into a comma-separated string.

The second example demonstrates the same approach, only with an array of strings, the same string.Join method is used to join.

In the third example, the Dapper library is used to execute the request, I believe that this method should be preferred, since helps to avoid SQL injection, and also simplifies the work with the database. To execute a query, we can pass a list of objects with which we want to compare values in the database, and Dapper will be able to handle this itself. We just need to define variables with the @ symbol in the request, and pass the object for processing, which will have attributes with the same names.

P.S. I apologize for my English))

Upd:

Try this:

private void CampareData(string[] exampleArray, IDbConnection connection)
{
    var clearItems = exampleArray.Select(s => $"'{s}'").ToArray();

    var sql = "Select * from emp_detail where EState in @items ";

    var result = connection.Query<YourTypeClass>(sql, new { items = clearItems });
}

In this variant, we will wrap each item in quotes that are used by sql, and the query should contain something like: ... in ('item1', 'item2' ...)

not working perfectly actually this code sends data like "a,b,c" but array send data like this "a","b","c" — kamlesh jha, Nov 10 '20 at 18:28
To better understand what should happen in the end, describe the data type that contains the "data" array, these are strings or some kind of class or something else. And if it's possible then write an example SQL query, I think it will help — Aleksandr Kurilov, Nov 10 '20 at 18:56

How can i compare an array with sql query in c# code?

2 Answers2