VB.NET, Two SQL command, build a string

Question

Im coding a programm with VB.NET (2010), which works with a local database(.sdf - SQL Server CE).

Now, I need to execute two SELECT command. Here an example to understand what I want:

SQL command example 1:

SELECT A FROM tbl_Name

If I read this result with SqlCeDataReader, I will get following:

James

Tom

Mike

SQL command example 2:

SELECT B FROM tbl_Age

If I read this result with SqlCeDataReader, I will get following:

14

15

16

---

The problem is, how can I build a string which contains finally a value like this ->

James;14

Tom;15

Mike;16

How can I do that? I know that I have to use a StringBuilder and then AppendLine(), but the problem/question is more: How can I bring together the NAME and the AGE in one line? How can I solve this problem elegant? I hope you can help me. Thanks! BK_

Update:

No, they havent any relation. Im working with Sql Server CE.

Otherwhise it would be very nice, if there is a table update possible. That means, if I can update these both tables to one table like:

Tbl_personality Column Name and Column Age

And than read it at once with SQL * FROM tbl_personality

Answer 1

The question is how these tables are related, do you have a foreign-key in table_age that leads to tbl_name? Then you can use a JOIN and a single query.

You could fill a List<User> where User is a custom class with both properties:

public class User
{
    public string Name { get; set; }
    public int Age { get; set; }
}

Now use an INNER JOIN to link both tables on the FK and you get the correct age for every name:

string sql = @"SELECT n.Name,a.Age 
               FROM tbl_Name n 
               INNER JOIN tbl_Age a 
                  ON n.NameID=a.NameID
               ORDER BY Name, Age";

You can add instances of User in while you read all records:

List<User> users = new List<User>();
using (var con = new System.Data.SqlClient.SqlCeConnection("Connection-String"))
using (var cmd = new SqlCeCommand(sql, con))
{
    con.Open();
    using (var rd = cmd.ExecuteReader())
    {
        while (rd.Read())
        {
            string name = rd.GetString(0);
            int age = rd.GetInt32(1);
            users.Add(new User { Name = name, Age = age });
        }
    }
}

if you want to output all:

foreach (User u in users)
    Console.WriteLine("{0};{1}", u.Name, u.Age);

Edit: Here the VB.NET version:

Dim users As New List(Of User)()
Using con = New System.Data.SqlClient.SqlCeConnection("Connection-String")
    Using cmd = New SqlCeCommand(sql, con)
        con.Open()
        Using rd = cmd.ExecuteReader()
            While rd.Read()
                Dim name As String = rd.GetString(0)
                Dim age As Integer = rd.GetInt32(1)
                users.Add(New User() With { _
                    Key .Name = name, _
                    Key .Age = age _
                })
            End While
        End Using
    End Using
End Using

Answer 2

a primary/foreign key would be nice yes but if you just wanna fool around and make string concat u can just do this its way easier...

as you already stated you did SQLCEReader on both so u have both data.

To Combine them just use the one reader line with the other reader line like

sqlDataReader1("Name") & ";" & sqlDataReader2("Age")

the output would be

James;14

as u wanted

If u have them in different subs u can combine them into 1 or just use some variables and then combine the variables

dim namestr as string = string.empty
dim agestr as string = string.empty

then u hand it over to your string variable

namestr = sqlDataReader1("Name")
agestr = sqlDataReader2("Age")

then combine

namestr & ";" & agestr

output would also be

James;14

The Answer from Tim Schmelter is nice and very detailed but i think its to much for the things you want.