CODEWITHSUNDEEP
c#sqlsql-server
user2336688
user2336688

Reputation: 15

Save multiple records from a database in a string

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!

Upvotes: 0

Views: 563

Answers (3)

Steve
Steve

Reputation: 216342

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

Upvotes: 1

Ben Fogel
Ben Fogel

Reputation: 561

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.

Upvotes: 0

Amit Singh
Amit Singh

Reputation: 8129

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;

Upvotes: 2

Related Questions