2 arrays to serialize

I have the following code which works well:

Dim dept As New ArrayList
Dim forename As New ArrayList

objJSONStringBuilder = New StringBuilder()
objSQLConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("connString"))

objSQLCommand = New SqlCommand("select dept, forename from table1", objSQLConnection)

objSQLCommand.Connection.Open()
objSQLDataReader = objSQLCommand.ExecuteReader()

While objSQLDataReader.Read()
    dept.Add(New With {Key .dept = objSQLDataReader("dept")})
    forename.Add(New With {Key .forename = objSQLDataReader("forename")})
End While

objSQLDataReader.Close()
objSQLCommand.Connection.Close()

Dim serializer As New JavaScriptSerializer()
Dim arrayJson As Stri开发者_运维知识库ng = serializer.Serialize(dept)

Return arrayJson

Which gives me:

[
    {
        "dept": "dept1"
    },
    {
        "dept": "dept2"
    },
    {
        "dept": "dept3"
    }
]

How do I add the second column forename in the json data too so it returns:

[
    {
        "dept": "dept1",
        "forename": "adam"
    },
    {
        "dept": "dept2",
        "forename": "joe"
    },
    {
        "dept": "dept3",
        "forename": "smith"
    }
]

Eventually, I will want to add more columns, i.e. surname.

You need to make a single object with two properties:

dept.Add(New With {Key .dept = objSQLDataReader("dept"), .forename = objSQLDataReader("forename")})

Why are you storing the dept and forename of the same table/object in different lists? I think the best way you could go about this is creating an object that represents the 'table1' like so:

public class Table1Object {
    public string Dept { get; set; }
    public string Forename { get; set; }
}

and then in your loop through data reader do it like this:

List<Table1Object> table1List = new List<Table1Object>()

while (objSQLDataReader.Read())
{
    table1List.Add(new Table1Object 
                       { 
                           Dept = objSQLDataReader("dept"), 
                           Forename = objSQLDataReader("forename") 
                       });
}

After that

string arrayJson = serializer.Serialize(table1List)

Should give you the desired json

PS. My VB is a little rusty, but if I made any mistakes in the code, I hope you get the gist of it :)