Dynamic insertion of data into database table

Question

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 5-Jan-12 3:19am

pradeep manne

Updated 5-Jan-12 3:39am

Kschuler

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kschuler · Accepted Answer · 2012-01-05T03:44:00

Solution 1

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.

Posted 5-Jan-12 3:44am

Kschuler

Updated 5-Jan-12 4:45am

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.

pradeep manne · Accepted Answer · 2012-01-11T02:43:00

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 + ")";