如何从项列表中创建SQL查询 [英] How do I create a SQL query from list of items

问题描述

我的目的是从列表创建一个查询，其中列表包含列名和数据。



下面是我的列表。列表标题名称是[Data，DataColumnType]

[ABC，StringColumn1]

[XYZ，StringColumn2]

[12/07/2017，DataColumn1]

[INR，StringColumn3]

[1000，FloatColumn1]



我需要从列表中建立一个查询，如下所示

string sqlquery =INSERT INTO tablename（StringColumn1，StringColumn2，DataColumn1，StringColumn3，FloatColumn1 ）价值（'ABC'，'XYZ'，'12 / 07/2017'，'INR'，1000）



这里列表的数量可能会有所不同可能不是静态的。所以我无法从列表中分配值并静态执行。



先谢谢。



我尝试过：

My purpose id to create a query from list where list contains column name and data.

Below is my list. List heading name is [Data,DataColumnType]
["ABC","StringColumn1"]
["XYZ","StringColumn2"]
["12/07/2017","DataColumn1"]
["INR","StringColumn3"]
["1000","FloatColumn1"]

I need to build up a query from list as below
string sqlquery = "INSERT INTO tablename (StringColumn1,StringColumn2,DataColumn1,StringColumn3,FloatColumn1) VALUES ('ABC','XYZ','12/07/2017','INR',1000)

Here the count of list may vary and it may not be static. so i cannot able to assign values from list and do statically.

Thanks in Advance.

What I have tried:

List<dataitems> lst = new List<dataitems>();
            DataItems dt = new DataItems();
            dt.Data = "ABC";dt.DataColumn = "StringColumn1";
            dt.Data = "XYZ"; dt.DataColumn = "StringColumn2";
            dt.Data = "12/01/2017"; dt.DataColumn = "DateColumn1";
            lst.Add(dt);

            string query = "INSERT INTO tableName";
            int i = 0;
            foreach(var l in lst)
            {
                // 
            }

推荐答案

您需要使用参数化插入语句 - 阅读给我参数化的SQL，或者给我死亡 [ ^ ]

you need to use a parameterized insert statement - read this Give me parameterized SQL, or give me death[^]

SqlCommand command1 = new SqlCommand("INSERT INTO tablename VALUES(@StringColumn1, ...)", SQLConnection);

command1.Parameters.AddWithValue("@StringColumn1", "ABC");
...
command1.ExecuteNonQuery();

每次插入一行数据iirc you做一个command1.Reset（）来重置变量，但是请查阅它，它已经有一段时间了

Every time you insert a row of data iirc you do a command1.Reset() to reset the variables but please look it up, its been a while

List<DataItems> lst = new List<DataItems>();
           lst.Add(new DataItems() { Data = "ABC", DataColumn = "StringColumn1" });
           lst.Add(new DataItems() { Data = "XYZ", DataColumn = "StringColumn2" });
           lst.Add(new DataItems() { Data = "12/07/2017", DataColumn = "DateColumn1" });
           lst.Add(new DataItems() { Data = "INR", DataColumn = "StringColumn3" });
           lst.Add(new DataItems() { Data = "1000", DataColumn = "FloatColumn1" });

           string query = "INSERT INTO tableName ({0}) values ({1})";
           List< string> columnNames= new List<string>();
           List< string> columnValues= new List<string>();

           SqlCommand cmd = new SqlCommand();
           foreach (var item in lst)
           {
               cmd.Parameters.Add("@" + item.DataColumn, item.Data);
               columnValues.Add("@" + item.DataColumn);
               columnNames.Add(item.DataColumn);
           }
           string cols = string.Join(",", columnNames);
           string datas = string.Join(",", columnValues);
           query = string.Format(query, cols, datas);

这是一个可运行的解决方案，供您进行抽样和试用。

它以这种方式分解它更容易测试，并重复使用方便的字符串和列表函数。

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);
        }
    }
}

这篇关于如何从项列表中创建SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！