using a while loop to insert multiple rows in C# and ado.net

Question

i want to assign classes to teachers within certain class : i have a variables and i want all variables to be loop and inserted with many rows in database as you can see i put a numbers from 1 to 8 i want each group of number to be inserted into database in multiple rows and not to insert only with number 1 .

int count = 0;
string classid = DropDownList1.SelectedValue.ToString();
string period_1 = period1.Text;
string time_1 = time1.Text;
string weekday1 = "1";
string course_1 = Scourses1.SelectedValue.ToString();
string teach_1 = Steacher1.SelectedValue.ToString();
string time_2 = time2.Text;
string weekday2 = "2";
string course_2 = Scourses2.SelectedValue.ToString();
string teach_2 = Steacher2.SelectedValue.ToString();
string time_3 = time3.Text;
string weekday3 = "3";
string course_3 = Scourses3.SelectedValue.ToString();
string teach_3 = Steacher3.SelectedValue.ToString();
string time_4 = time4.Text;
string weekday4 = "4";
string course_4 = Scourses4.SelectedValue.ToString();
string teach_4 = Steacher4.SelectedValue.ToString();
string time_5 = time5.Text;
// string weekday5 = weekd5.Text;
string course_5 = Scourses5.SelectedValue.ToString();
string teach_5 = Steacher5.SelectedValue.ToString();
string period_2 = period2.Text;
string time_6 = time6.Text;
//string weekday6 = weekd1.Text;
string course_6 = Scourses6.SelectedValue.ToString();
string teach_6 = Steacher6.SelectedValue.ToString();
string time_7 = time7.Text;
//string weekday7 = weekd2.Text;
string course_7 = Scourses7.SelectedValue.ToString();
string teach_7 = Steacher7.SelectedValue.ToString();
string time_8 = time8.Text;
// string weekday8 = weekd3.Text;
string course_8 = Scourses8.SelectedValue.ToString();
string teach_8 = Steacher8.SelectedValue.ToString();
string time_9 = time9.Text;
string weekday9 = weekd4.Text;
string course_9 = Scourses9.SelectedValue.ToString();
string teach_9 = Steacher9.SelectedValue.ToString();
string time_10 = time10.Text;
string weekday10 = weekd5.Text;
string course_10 = Scourses10.SelectedValue.ToString();
string teach_10 = Steacher10.SelectedValue.ToString();

//and the query to insert into database is :
string query = "INSERT INTO dbo.teacher_classes (period, weekday, course, teach_id, time, class_id) " +
               "VALUES (@Period, @Weekday, @Course, @Teach_id, @Time, @Class) ";

SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Period", period_1);
cmd.Parameters.AddWithValue("@Weekday", weekday1);
cmd.Parameters.AddWithValue("@Course", course_1);
cmd.Parameters.AddWithValue("@Teach_id", teach_1);
cmd.Parameters.AddWithValue("@Time", time_1);
cmd.Parameters.AddWithValue("@Class", classid);
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
count = cmd.ExecuteNonQuery();

con.Close();

//the only variables inserted is:
string classid = DropDownList1.SelectedValue.ToString();
string period_1 = period1.Text;
string time_1 = time1.Text;
string weekday1 = "1";
string course_1 = Scourses1.SelectedValue.ToString();
string teach_1 = Steacher1.SelectedValue.ToString();

i want to insert them as a while loop to be inserted in many rows that is ?

Define a class to contain your value, add an instance of the class to a list of that class, loop over the list instances and for each instance call the insert — Steve
– Steve, Commented May 11, 2013 at 19:31
I am new to c# and not completely know all thing in it, i will try to do what you mention but iam not sure to write it right way . thanks Steve for your comment — Hassan Mostafa
– Hassan Mostafa, Commented May 11, 2013 at 19:34
Working on an example to show a pseudo code to adapt to your requirements — Steve
– Steve, Commented May 11, 2013 at 19:35

Steve · Accepted Answer · 2013-05-11 19:54:48Z

3

First of all define a class to contain your values (We are using an Object Oriented Language, right?)

public class Lesson
{
   pulic int LessonID {get; set;}          
   public string Period {get; set;}          
   public string  StartTime  {get; set;}          
   public string weekday {get; set;}          
   public int CourseID  {get; set;}          
   public int TeacherID  {get; set;}          
}

Now in your code define a List(of MyLesson) where you add the Lessons

public List<Lesson> myLessons = new List<Lesson>();

The next step is to extract the values from your controls, add to a Lesson object and insert the lesson object in the previous list

Lesson aLesson = new Lesson();
aLesson.LessonID = Convert.ToInt32(DropDownList1.SelectedValue);
aLesson.Period = period1.Text;
aLesson.StartTime = time1.Text;
aLesson.Weekday = "1";
aLesson.CourseID = Convert.ToInt32(Scourses1.SelectedValue);
aLesson.TeacherID = Convert.ToInt32(Steacher1.SelectedValue);
myLessons.Add(aLesson); 
// Repeat for the next block of fields

The last step is to loop over the List of Lesson and call the insert to the database, but first create all the parameters needed in the insert with dummy values, then enter the loop, assign the real values and execute the query

string query = "INSERT INTO dbo.teacher_classes (period, weekday, course, teach_id, " + 
               "time, class_id) VALUES (@Period, @Weekday, @Course, @Teach_id, @Time, @Class) ";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Period", "");
cmd.Parameters.AddWithValue("@Weekday", "");
cmd.Parameters.AddWithValue("@Course", 0);
cmd.Parameters.AddWithValue("@Teach_id", 0);
cmd.Parameters.AddWithValue("@Time", "");
cmd.Parameters.AddWithValue("@Class", 0);
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;

foreach(Lesson aLesson in myLessons)
{
   cmd["@Period"].Value = aLesson.Period;
   cmd["@Weekday"].Value = aLesson.Weekday;
   cmd["@Course"].Value = aLesson.CourseID;
   cmd["@Teach_id"].Value = aLesson.TeacherID;
   cmd["@Time"].Value = aLesson.StartTime;
   cmd["@Class"].Value = aLesson.ClassID;
   count = cmd.ExecuteNonQuery();
}

Please, take note that this is pseudocode, just to give you the direction to follow.
It is up to you to render it a working code.

edited May 11, 2013 at 19:54

answered May 11, 2013 at 19:48

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Hassan Mostafa Over a year ago

thanks Steve i will try to use your beautiful answer and i hope that i can complete it in right way..

Steve Over a year ago

Fixing a little error (EndTime doesn't exist, it the time for the next block)

Hassan Mostafa Over a year ago

i see that dont worry bro .

Hassan Mostafa Over a year ago

The code work with me and the loop work but it loop on first block, the first block inserted many times and the other not inserted, may be the wrong from me or something needed to be added on code.

Hassan Mostafa Over a year ago

Thx for your patience and for your support at all

|

Collectives™ on Stack Overflow

using a while loop to insert multiple rows in C# and ado.net

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related