Mapping a Column Value Object Array to SQL Formatted Types String

아티클
1/17/2024

Introduction

Let say we have a value of columns and values in an array which says Column1, ColumnValue1, Column2, ColumnValue2, etc. Now if we want to create a mapping of column with values with specified types as a SQL formatted string. For e.g. preparing an Update statement of a Where condition in select statement. This requires to map the .Net types to SQL types while creating the SQL expression.

The Solution

Below is the example of how to map the values in a string with each column considering the types information:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
  
namespace ConsoleForAnything
{
    public class  Program
    {
        static void  Main(string[] args)
        {
            // Test the logic
  
            // Sample array of columns and corresponding values
            var columnValueArray = new  object[] {"Column1", "Value1",  "Column2", 8,  "Column3", true, "Column4", DateTime.Now };
  
            Console.WriteLine(ParametersAsSqlColumnValueMap(columnValueArray));
             
            Console.Read();
        }
  
        /// <summary>
        /// The parameters map for sql statements, for e.g. column1 = "value1, column2 = 8 etc.
        /// </summary>
        /// <param name="csvColumnsValues">
        /// The csv columns values in ordered manner {"Column1","value1","column2",8, "column3", true"} .
        /// </param>
        /// <returns>
        /// The <see cref="string"/>.
        /// </returns>
        private static  string ParametersAsSqlColumnValueMap(params object[] csvColumnsValues)
        {
  
            Dictionary<string, object> columnsAndValuesMap = new  Dictionary<string, object>();
  
            for (int i = 0; i < (csvColumnsValues.Count() - 1); i++, i++)
            {
                columnsAndValuesMap.Add(csvColumnsValues[i].ToString(), csvColumnsValues[i + 1]);
            }
  
            dynamic sql = new  ExpandoObject();
  
            StringBuilder columnValueMapperBuilder = new  StringBuilder();
  
            List<string> tempOrderedKeys = new  List<string>();
  
            tempOrderedKeys.AddRange(columnsAndValuesMap.Keys.ToArray());
  
            foreach (var item in tempOrderedKeys)
            {
                var objectValue = columnsAndValuesMap[item];
                var innerType = objectValue.GetType();
  
                columnValueMapperBuilder.Append(string.Format("{0}={1}", item, TypeMapperToSqlValues(innerType, objectValue)));
  
                // This is to prevent the last , to be added in the columnValueMapperBuilder string
                if ((columnsAndValuesMap.Count - 1) != tempOrderedKeys.IndexOf(item))
                {
                    columnValueMapperBuilder.Append(", ");
                    columnValueMapperBuilder.Append(Environment.NewLine); // this is just for formatting.
                }
            }
  
            return columnValueMapperBuilder.ToString();
        }
  
        /// <summary>
        /// The type mapper to sql values by predicting the type in the object value.
        /// </summary>
        private static  string TypeMapperToSqlValues(Type type,  object  value)
        {
            if (type.IsValueType && !type.IsAutoLayout)
            {
                return string.Format("{0}", value);
            }
            else
            {
                return string.Format("'{0}'", value);
            }
        }
    }
}

다음을 통해 공유

Mapping a Column Value Object Array to SQL Formatted Types String

Introduction

The Solution

See Also

추가 리소스