Insert data in Sql Server using C# Coding using For Loop

Question

I know my question is strange but I want to Insert data in Sql Server using For Loop using C#. I have data like as below :

Insert into tblQuestions(Question, Description, Image, TopicId) values('Urogenital Diaphragm is formed by A/E', NULL, NULL, 1)
Insert into tblOptions(Option, QId, Answer) values('Colle''s fascia', NULL, 0)
Insert into tblOptions(Option, QId, Answer) values('Sphincter urethra', NULL, 1)
Insert into tblOptions(Option, QId, Answer) values('Perineal membrane', NULL, 0)
Insert into tblOptions(Option, QId, Answer) values('Deep perineal muslces', NULL, 0)


Insert into tblQuestions(Question, Description, Image, TopicId) values('The intricately and prodigiously looped system of veins and arteries that lie on the surface of the epididymis is known as:', NULL, NULL, 1)
Insert into tblOptions(Option, QId, Answer) values('Choroid plexus', NULL, 0)
Insert into tblOptions(Option, QId, Answer) values('Tuberal plexus', NULL, 0)
Insert into tblOptions(Option, QId, Answer) values('Pampiniform plexus', NULL, 1)
Insert into tblOptions(Option, QId, Answer) values('Pectiniform septum', NULL, 0)

This way, I have number of data. First line is of Question and next 4 lines contains Options of that Question. After inserting first question id, it will generate question id which will be used in all 4 Options instead of NULL.

Please help me regarding this. I am looking for some ideas or suggestions. I have all these data in notepad file.

Where are stored actually your input data? Do you have a file or you have just a bunch of predefined question to insert in the database tables? — Steve
– Steve, Commented Apr 9, 2014 at 7:46
Create a DataTable, assign all the column values into it and pass it as a single parameter. — thevan
– thevan, Commented Apr 9, 2014 at 7:48
So it is stored in some kind of file on disk. It is already formatted with the INSERT statement or it is just a comma separated list of values that need to be properly formatted before inserting in the database tables? — Steve
– Steve, Commented Apr 9, 2014 at 8:11

sarin · Accepted Answer · 2014-04-09 08:17:17Z

As an alternative, If you are able to create a stored procedure, you can declare a variable and capture the newly created ID after the insert and then use it in each subsequent Insert statement?

DECLARE @QuestionId int

Insert into tblQuestions(Question, Description, Image, TopicId) values('Urogenital Diaphragm is formed by A/E', NULL, NULL, 1)

SELECT @QuestionId = SCOPE_IDENTITY();

Insert into tblOptions(Option, QId, Answer) values('Colle''s fascia', @QuestionId, 0)
Insert into tblOptions(Option, QId, Answer) values('Sphincter urethra', @QuestionId, 1)
Insert into tblOptions(Option, QId, Answer) values('Perineal membrane', @QuestionId, 0)
Insert into tblOptions(Option, QId, Answer) values('Deep perineal muslces', @QuestionId, 0)


Insert into tblQuestions(Question, Description, Image, TopicId) values('The intricately and prodigiously looped system of veins and arteries that lie on the surface of the epididymis is known as:', NULL, NULL, 1)

SELECT @QuestionId = SCOPE_IDENTITY();

Insert into tblOptions(Option, QId, Answer) values('Choroid plexus', @QuestionId, 0)
Insert into tblOptions(Option, QId, Answer) values('Tuberal plexus', @QuestionId, 0)
Insert into tblOptions(Option, QId, Answer) values('Pampiniform plexus', @QuestionId, 1)
Insert into tblOptions(Option, QId, Answer) values('Pectiniform septum', @QuestionId, 0)

Fizor · Accepted Answer · 2014-04-09 07:47:31Z

I'm about to tell you something that will change your life. :) Okay, not really, but it will make it easier when writing queries etc.

Download Data access blocks, here.

Then add it to your project resources and includes.

The only code you will be typing to insert into tables etc would be:

//Using:
using Microsoft.ApplicationBlocks.Data;

//Setting connection
private static string _connection = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

//sending info to a stored proc (Which is better) in my opinion:
SqlHelper.ExecuteNonQuery(_connection, "usp_AddUpdateTargets", week1target, week2target, week3target, week4target, UserID, TerritoryID);

And that's literally all there is to it. Obviously you have to get your variables etc.

Hope this helps.

Ritesh kumar · Accepted Answer · 2014-04-09 07:57:42Z

0

From query performance point of view it bad to call database to insert a single row. I would like to suggest in for loop concatenate query as query string with delimiter ";" and at the end execute whole insert statement at a time. For example:

string query;

For loop(singleInsertQuery){ query = query + ";" + singleInsertQuery }

Execute (query );

answered Apr 9, 2014 at 7:57

Ritesh kumar

2781 silver badge3 bronze badges

Collectives™ on Stack Overflow

Insert data in Sql Server using C# Coding using For Loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related