CODEWITHSUNDEEP
c#sql-server
Jeeten Parmar
Jeeten Parmar

Reputation: 5757

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

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.

Upvotes: 0

Views: 910

Answers (3)

sarin
sarin

Reputation: 5307

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)

Upvotes: 1

Ritesh kumar
Ritesh kumar

Reputation: 278

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

Upvotes: 0

Fizor
Fizor

Reputation: 1530

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.

Upvotes: 0

Related Questions