CODEWITHSUNDEEP
c#sql
user3165474
user3165474

Reputation: 164

How to get a string of row in SQL?

This is my Table

MY_TABLE

| id | first4 | last4 | reason |
--------------------------------
|  1 |   123  |  456  | Cancel |
|  2 |   123  |  789  | Correct|

Problem

It's go into the loop and get the info but at the end it only show the last row

example:

id = 1 
first4 = 123 
last4 = 456 
reason = cancel

What i try to achieve is a string that render like this :

id = 1,2;
first4 = 123,123;
last4 = 456,789;
reason = Cancel,Correct

IN C# i use 

try
{
    SqlDataReader render = cmd.ExecuteReader();
    while (render.Read())
    {
        while (render.Read())
        {
            column = new Dictionary<string, string>();

            column["id"] = string.Join(",", render["id"]);
            column["first4"] = string.Join(",", render["first4"]);
            column["last4"] = string.Join(",", render["last4"]);
            column["reason"] = string.Join(",", render["reason"]);

            rows.Add(column);
        }
        render.close();
    }
}
catch (Excecption ex)
{
    console.writeline(ex);
}
finally
{
    conn.Close();
}

Call the table

foreach (Dictionary<string, string> column in rows)
{
    interchange.DownloadBlacklist.id = column["id"] ;
    interchange.DownloadBlacklist.first4 = column["first4"];
    interchange.DownloadBlacklist.last4 = column["last4"];
    interchange.DownloadBlacklist.status =  column["status"];
}

What is the simplest way to achieve it? Since i manage to pull out the information but i get only the last row of the table but not how i tried to achieve it.

Upvotes: 1

Views: 138

Answers (3)

Mohit S
Mohit S

Reputation: 14024

This might do the trick for you

SqlDataReader render = cmd.ExecuteReader();
List<string> IdList = new List<string>();
List<string> First4List = new List<string>();
List<string> Last4List = new List<string>();
List<string> ReasonList = new List<string>();
while (render.Read())
{
    IdList.Add(render["id"].ToString());
    First4List.Add(render["first4"].ToString());
    Last4List.Add(render["last4"].ToString());
    ReasonList.Add(render["reason"].ToString());
}
render.close();
string idstr = string.Join(",", IdList);
string first4str = string.Join(",", First4List);
string last4str = string.Join(",", Last4List);
string reasonstr = string.Join(",", ReasonList);

Try using Using for SQL Connection and Commands. That will help you getting rid of final block and getting your connection & command close.

A glimpse on the code.

Created the list of strings i.e. List<string> for all four columns will add all the data read from the sql.

Then reading the data normally in one loop and add all the data in the list respectively.

Once the data has been read and feeded in the list then just join them with , using Join.

Upvotes: 5

Tatranskymedved
Tatranskymedved

Reputation: 4371

You can declare string variable to hold the value during looping. Once done, during each foreach just take & concat the value with ',' .. after You will just append to the existing string (to avoid performance issues, use StringBuilder).

E.g.:

//omitted some code

StringBuilder ResultId = new StringBuilder();

SqlDataReader render = cmd.ExecuteReader();
while (render.Read())
{
    ResultId.Append(ResultId.Length > 0 ? "," : "");
    ResultId.Append(render["id"]);
}
render.close();

ResultId.ToString(); //there is Your result

//omitted some code

Upvotes: 2

Joe Taras
Joe Taras

Reputation: 15379

In your code:

while (render.Read())
{
    while (render.Read())
    {
        column = new Dictionary<string, string>();

        column["id"] = string.Join(",", render["id"]);
        column["first4"] = string.Join(",", render["first4"]);
        column["last4"] = string.Join(",", render["last4"]);
        column["reason"] = string.Join(",", render["reason"]);

        rows.Add(column);
    }
    render.close();
}

You have performed two while loops, but in the inner while you close your SqlDataReader named render.

This can throw an exception when you try to read again (in the external while) so you have as effect a loop.

Move render.close() at the end of the external while and consider to reduce in one loop your code

Upvotes: 1

Related Questions