Save multiple records from a database in a string

Question

I have this table in my DB (record ID is the same for 1 Student, it increments by 1 automatically for different students):

id  | firstName | lastName | subject | grade | recordID |
----+-----------+----------+---------+-------+----------+
 1  | John      | Doe      | 1       | A     | 1        |
 1  | John      | Doe      | 2       | B     | 1        |
 3  | Max       | Smith    | 1       | C     | 2        |

using C# I want to save data for id = 1 into a string in this format:

Name: John Doe
Details: 1A; 2B
Name: Max Smith
Details: 1C

what I've done so far is:

SqlCommand cmd = _connection.CreateCommand();
string res = null;
cmd.CommandText = "SELECT COUNT(DISTINCT recordID) FROM table1";
int numb = Convert.ToInt32(cmd.ExecuteScalar().ToString());
int currentRecord = 1;

for (int i = 0; i < numb; i++)
{
cmd.CommandText = "SELECT firstname, lastname FROM table1 WHERE recordID="+currentRecord+";";
res += "Name: " + cmd.ExecuteScalar().ToString() + "\n Details: ";

cmd.CommandText = "SELECT subject, grade FROM table1 WHERE recordID="+currentRecord+";";
res += "Details: " + cmd.ExecuteScalar().ToString() + "\n";

currentRecord++
}

This always saves the first record in a string, like this

Name: John
Details: 1
Name: Max
Details: 1

though I need to save multiple rows and columns. please help!

Two roundtrip to the database to read the same record? Not very efficient — Steve
– Steve, Commented May 9, 2013 at 14:48

Amit Singh · Accepted Answer · 2013-05-09 14:45:28Z

2

Change your Command Text Like this...

cmd.CommandText = "SELECT firstname+' '+lastname FROM table1 WHERE recordID="+currentRecord;



cmd.CommandText = "SELECT subject+grade FROM table1 WHERE recordID="+currentRecord;

answered May 9, 2013 at 14:45

Amit Singh

8,12922 silver badges30 bronze badges

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

Comments

Steve · Accepted Answer · 2013-05-09 14:56:39Z

I will try to retrieve all the records in a single call to the database (supposing that are not in the thousands) ordered by recordID, then enter a loop and extract the information one record at time.

This could be done with a loop like this (Not tested, just to give an example)

string sqlCommand = "SELECT id ,firstName, lastName,subject,grade,recordID " + 
                    "from table ORDER BY recordID";

 int prevID = -1;
 int curID = -1;
 StringBUilder res = new StringBuilder()
 SqlCommand cmd = _connection.CreateCommand();
 cmd.CommandText = sqlCommand;
 SqlDataReader reader = cmd.ExecuteReader()
 while(reader.Read()
 {
     curID = Convert.ToInt32(reader[5]);
     if(curID != prevID)
     {
         prevID = curID;
         res.AppendLine("\r\nName: " + reader[1].ToString() + " " + reader[2].ToString());
         res.Append("Details:");
     }
     res.Append(reader[3].ToString() + reader[4].ToString() + ";";
 }
 Console.WriteLine(res.ToString());

Ben Fogel · Accepted Answer · 2013-05-09 14:51:45Z

0

Your using cmd.ExecuteScalar which returns the first cell of the first row of the result. You need to use cmd.ExecuteReader to get more data than the first cell.

answered May 9, 2013 at 14:51

Ben Fogel

5515 silver badges13 bronze badges

Collectives™ on Stack Overflow

Save multiple records from a database in a string

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