How to store multiple values in a struct array using SqlDataReader

Question

I'd like to populate an User array with data from my SQL Server database using a SqlDataReader.

This is my code so far:

public struct User
{
    public int id;
    public string log;
    public string password;

    public User (int id1,string s, s2)
    {
        id=id1;
        log =s;
        password=s2;
    }
}

User[] al = new User[50];
int i=0;

using (SqlConnection connection = new SqlConnection("string")
{
    connection.Open();

    SqlCommand command = new SqlCommand("Select [UserName], [Password]. from [TaUser]", connection);

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // populate the al array with the datas from the 3 columns : ID, UserName, Password
        }
    }

    connection.Close();
}

I know that if I had a simple arraylist I could just do al.Add(""), however, I struggle when it comes to struct arrays.

Save it to List<User> with .Add and then call .ToArray() to get array from list — Fabio
– Fabio, Commented May 29, 2018 at 23:16
Your struct doesn't have the same fields as your query. There is no UserName field in the struct, for example; instead it has a mysterious field named log. Is this intentional? Also, your query returns two columns, but the constructor for User requires 3 arguments. What is going on here? — John Wu
– John Wu, Commented May 29, 2018 at 23:26
@JohnWu Not an uncommon case to have different property names in different domains imo — Dan Rayson
– Dan Rayson, Commented May 29, 2018 at 23:31
I'd suggest you make that immutable with read-only properties because muteable structs will cause you many headaches. Better yet just make it a class as it doesn't really make sense as a struct in the first place. — juharr
– juharr, Commented May 29, 2018 at 23:37
@dan Fine if they’re different, but OP needs to specify the mapping, especially if there are more properties than columns. — John Wu
– John Wu, Commented May 30, 2018 at 0:07

user3188639 · Accepted Answer · 2018-05-29 23:36:55Z

There are a lots of errors in your code.

First, your User constructor is invalid, it should be:

public User(int id1, string s, string s2)

Second, your query does not return user id.

Third, it would probably be better to use List instead of array.

With all that, this should work

List<User> userList = new List<User>() ;
using (SqlConnection connection = new SqlConnection("string")
{
connection.Open();

SqlCommand command = new SqlCommand("Select [Id], [UserName], [Password]. from [TaUser]", connection);

using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        var id = reader.GetInt32(0);
        var userName = reader.GetString(1);
        var pwd = reader.GetString(2);
        var user = new User(id, userName, pwd);
        userList.Add(user);
    }
}
connection.Close();

// if you really need an array, do it here
var al = userList.ToArray()

Dan Rayson · Accepted Answer · 2018-05-29 23:29:25Z

0

I'd advise doing something like this:

SqlDataReader dataReader = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(dataReader);

Then read out of that DataTable like this:

string name = dataTable.Rows[0]["UserName"] as string;

Then fill your User struct with the gathered info. Job Done?

answered May 29, 2018 at 23:29

Dan Rayson

1,4471 gold badge16 silver badges41 bronze badges

Collectives™ on Stack Overflow

How to store multiple values in a struct array using SqlDataReader

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related