Here is a runnable solution for you to sample and try out.
It's broken up this way to make it easier to test, and re-use handy string and list functions.
namespace ConsoleApp01
{
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
public class Program
{
public static void Main()
{
List<DataItems> dataList1 = DataQuery1();
string insertSQL = dataList1.GetInsertSQL("Table1");
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddRange(dataList1.SQLParameters());
Console.WriteLine(insertSQL);
foreach (SqlParameter sqlP in cmd.Parameters)
{
string
paramName = sqlP.ParameterName,
paramValue = Convert.ToString(sqlP.Value);
Console.WriteLine(paramName + ": " + paramValue);
}
}
public static List<DataItems> DataQuery1()
{
return new List<DataItems>
{
new DataItems { Data = "ABC", DataColumn = "StringColumn1" },
new DataItems { Data = "XYZ", DataColumn = "StringColumn2" },
new DataItems { Data = "12/01/2017", DataColumn = "DateColumn1" }
};
}
}
public class DataItems
{
public string Data { get; set; }
public string DataColumn { get; set; }
}
public static class DataItemsExtensions
{
public static SqlParameter[] SQLParameters(this List<DataItems> items)
{
return items.Select(x => new SqlParameter("@" + x.DataColumn, x.Data)).ToArray();
}
public static List<string> ColumnNames(this List<DataItems> items)
{
return items.Select(x => x.DataColumn).ToList();
}
public static List<string> ParameterNames(this SqlParameter[] items)
{
return items.Select(x => x.ParameterName).ToList();
}
public static string GetInsertSQL(this List<DataItems> items, string tableName)
{
string query = "INSERT INTO {0} ({1}) VALUES({2})";
string columns = string.Join(",", items.ColumnNames());
string values = string.Join(",", items.SQLParameters().ParameterNames());
return string.Format(query, tableName, columns, values);
}
}
}