parameterize IN clause

Suppose you need to write a query that uses IN clause but you don't want to create a Stored Procedure. Using inline query is a possibility but not recommended. The only viable option is to use parameterized query. But it is a little tricky for IN clause.
Here is one work around you can apply.

            string sql = "SELECT * FROM dbo.TestTable A WHERE A.Name1 IN (__Keys__)";
            List values = new List() { "Name1", "Name2" };
            string parameterTemplate = "@name{0}";
            List parameters = new List();
            using (var conn = new SqlConnection(@"connString"))
            {
                using (var cmd = new SqlCommand())
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    int i = 0;
                    foreach(string val in values)
                    {
                        string paramName = string.Format(parameterTemplate, i);
                        parameters.Add(paramName);
                        cmd.Parameters.Add(new SqlParameter(paramName, values[i]));
                        i++;
                    }
                    sql = sql.Replace("__Keys__", string.Join(",", parameters));
                    cmd.CommandText = sql;
                    
                    cmd.Connection = conn;
                    conn.Open();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while(reader.Read())
                        {
                            Console.WriteLine(string.Format("{0},{1},{2}", reader[0], reader[1], reader[2]));
                        }
                    }
                }
            }


Hope it helps.

Is Reflection that bad?

There is this generic notion that using reflection in code is bad. Main argument is that this kind of code is very slow. In fact, whenever you use Reflection in your code, you are bound to get a review comment to avoid reflection. However, there are scenarios where you need to use reflection - mainly to reduce the complexity of writing lot of repeated code.

Whenever I hear such things, my first reaction is - Have you measured how slow your code is? Performance is always relative and is always bound by a range. If your code responds within acceptable limits, there is no reason why you should rewrite code (painfully) just because it had reflection in it.

Let us take an example.

1. I have a base class and there are couple of class that inherit from it. Like following:

    public class A
    {
        public Guid Id { get; set; }
    }

    public class B : A
    {
    }

    public class C : A
    {

    }

2. Imagine I have a class that uses these like following:

    public interface IGenericInterface
    {
        object Get(string id) where T : A;
    }

    public class GenericInterfaceClass : IGenericInterface
    {
        public object Get(string id) where T : A
        {
            return default(T);
        }

    }

3. Let us make calls and measure.

GenericInterfaceClass genericInterfaceClass = new GenericInterfaceClass();
            int loopLength = 100000;
            Stopwatch sw = new Stopwatch();
            sw.Start();
            for (int i = 0; i < loopLength; i++)
            {
                var getGenericEntity = typeof(IGenericInterface).GetMethod("Get");
                Type t = null;
                if (i % 3 == 0)
                {
                    t = typeof(B);
                }
                else if (i % 3 == 1)
                {
                    t = typeof(A);
                }
                else if (i % 3 == 2)
                {
                    t = typeof(C);
                }

                var getGenericEntityReference = getGenericEntity.MakeGenericMethod(t);
                var originalEntity = getGenericEntityReference.Invoke(genericInterfaceClass, new object[] { default(Guid).ToString() });
            }
            sw.Stop();
            Console.WriteLine(sw.ElapsedMilliseconds);
            Console.ReadLine();

4. Above code runs the reflection code to invoke a method 100,000 times. Time taken : ~280 ms (in release mode).

5. That is not too slow actually.
6. If you are too much concerned with that, you can always optimize it further.

            var getGenericEntity1 = typeof(IGenericInterface).GetMethod("Get");
            ConcurrentDictionary dict = new ConcurrentDictionary();
            sw.Reset();
            sw.Start();
            for (int i = 0; i < loopLength; i++)
            {
                MethodInfo obj = null;
                if (i % 3 == 0)
                {
                    if(!dict.TryGetValue(typeof(B), out obj))
                    {
                        obj = getGenericEntity1.MakeGenericMethod(typeof(B));
                        dict.TryAdd(typeof(B), obj);
                    }
                }
                else if (i % 3 == 1)
                {
                    if (!dict.TryGetValue(typeof(A), out obj))
                    {
                        obj = getGenericEntity1.MakeGenericMethod(typeof(A));
                        dict.TryAdd(typeof(A), obj);
                    }
                }
                else if (i % 3 == 2)
                {
                    if (!dict.TryGetValue(typeof(C), out obj))
                    {
                        obj = getGenericEntity1.MakeGenericMethod(typeof(C));
                        dict.TryAdd(typeof(C), obj);
                    }
                }
                
                var originalEntity = obj.Invoke(genericInterfaceClass, new object[] { default(Guid).ToString() });
            }
            sw.Stop();
            Console.WriteLine(sw.ElapsedMilliseconds);

            Console.ReadLine();
7. Time taken: ~100 ms (in release mode)
8. Remove the generics like following:

sw.Reset();
            sw.Start();
            for (int i = 0; i < loopLength; i++)
            {
                object originalEntity = null;
                if (i % 3 == 0)
                {
                    originalEntity = genericInterfaceClass.Get(default(Guid).ToString());
                }
                else if (i % 3 == 1)
                {
                    originalEntity = genericInterfaceClass.Get(default(Guid).ToString());
                }
                else if (i % 3 == 2)
                {
                    originalEntity = genericInterfaceClass.Get(default(Guid).ToString());
                }
            }
            sw.Stop();
            Console.WriteLine(sw.ElapsedMilliseconds);
            Console.ReadLine();

            return;
8. Time taken: ~15 ms (in release mode)

So, as you can see there are so many level of optimizations one can do depending on the kind of requirement you have. Specialized code will always run faster than generalized code. However, reflection itself is not that slow unless you are looking for extremely optimized code.