Click here to Skip to main content
14,699,886 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 5-Jan-12 4:39am
v3

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 + ")";
   
v3
Comments
joti parkash 12-Jan-12 23:09pm
   
thanks
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.
   
v2
Comments
fjdiewornncalwe 5-Jan-12 10:22am
   
Practical and simple to understand. +5.
pradeep manne 5-Jan-12 10:27am
   
if it is a string array then below code if fine but am using arraylist
can u help me

string []arrString = new string[] { "col1","col2","col3","col4","col5" };

string commaSepString = string.Join(",", arrString);
Kschuler 5-Jan-12 10:46am
   
Please see my updated solution.
pradeep manne 5-Jan-12 10:52am
   
hi below code is for arraylist
arraylist al=newarraylist();
string commaSepString = string.Join(",", (string[])al.ToArray(Type.GetType("System.String")));
Kschuler 5-Jan-12 10:59am
   
Okay. I see it now. I've never used the String.Join before so I had to look it up. If you already have your columns in an array list your way is probably much quicker.
pradeep manne 5-Jan-12 11:08am
   
hi this is my insert statement
string insert_query = "insert into ##Mytemp values('"+ commaSepString + "')";
getting error
Column name or number of supplied values does not match table definition
i manually tried based on the values of the above query by using the values like Values('val1','Val2'...) its inserting
any changes in my query
Kschuler 5-Jan-12 11:35am
   
Is the code that sets up your commaSepString taking into account the DataType? If the column is a string value, you have to surround the value in single quotes. If it's a number you just use the value. If that's not the issue, then try debugging and look at what commaSepString contains.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900