动态将数据插入数据库表 [英] Dynamic insertion of data into database table

问题描述

请帮我用C#编写动态插入查询.
我使用的是mssql数据库，在其中动态创建表的列，但是我需要一种根据创建的列数在数据库中插入值的方法.

我有列数，我只需要基于创建的最大列的sql插入语句.

Hi,
Please help me write a dynamic insert query in c#.
I am using a mssql database where in I dynamically create the columns of a table, but I need a way to insert values in the database depending upon the number of columns created.

I have the columns count, I just need the sql insert statement based on the max columns created.

推荐答案

基本的INSERT语句是这样的:
INSERT INTO myTableName(column1, column2, column3...) VALUES(''Data1'', 2, ''Data3''...)

因此，如果您已动态创建表，则需要使用该表的架构来创建插入语句.循环浏览列名称，并使用StringBuilder创建以逗号分隔的列表.然后遍历您要添加的数据，确保根据其数据类型正确设置其格式.

-更新评论-
如果您使用的是数组列表，则可以为每个语句使用循环遍历它. 此处 [此处 [

A basic INSERT statement is like this:
INSERT INTO myTableName(column1, column2, column3...) VALUES(''Data1'', 2, ''Data3''...)

So if you have dynamically created your table you''ll need to use the schema of that table to create the insert statement. Loop through the column names and create a comma separated list of them using a StringBuilder. Then loop through the data you want to add, making sure to format it properly based on it''s DataType.

- Update for comment -
If you are using an array list you can loop through it with a for each statement. Here[^] is an example. And as I suggested above, you may want to look at using a StringBuilder object to concat or append the names of your columns. Here[^] is some information on that.

hi ,
for array[] use below method
string []arrString = new string[] { "col1","col2","col3","col4","col5" };
          string commaSepString = string.Join(",", arrString);

for arraylist use below statement to create commasaperate list
arraylist al=newarraylist();
string commaSepString = string.Join(",", (string[])al.ToArray(Type.GetType("System.String")));

string temp_query = "insert into Table_Name values(" + commaSepString + ")";

这篇关于动态将数据插入数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！