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

The question is hown 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. — Tim Schmelter, Jan 28 '14 at 20:47
Is there something that tells you the age 14 belongs to James? Or are you just going to stick them together based on the order they are returned by your queries? Keep in mind that SQL does not guarantee a sort order, your example might say James is 14 one time and 16 the next time. — Rozwel, Jan 28 '14 at 20:50
Hi guys, thank you for your answer. TimSchmelter I updated my question. Rozwel Thank you for your reminder. Maybe an update could be the better solouting. Please see my question. I updated it. — Bk_, Jan 28 '14 at 21:06

Tim Schmelter · Accepted Answer · 2014-07-26T12:32:21.300

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

Thank you for your answer Tim. But as Rozwel has already mentioned, SQL does not guarantee a sort order. Thats why its maybe the better solution to update this two tables in one and than to read it. But how can I udpate these entries? — Bk_, Jan 28 '14 at 21:12
@Bk_: SQL _does_ guarantee a sort oder, if you provide an `ORDER BY` (as shown in my answer). But the order is not important in this case, you need a way to link both tables via `JOIN`(see above) or use a single table alone. Of course you can use an `UPDATE tbl_personality` or `INSERT INTO tbl_personality` but it's not clear what age belongs to which name. Remember, there is no _implicit_ order in the database. — Tim Schmelter, Jan 28 '14 at 22:15

score 1 · Answer 2 · answered Jan 29 '14 at 00:09

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.

VB.NET, Two SQL command, build a string

2 Answers2