How to store selected data from database in an array in C#

Question

With this code I'm retrieving the data from the database:

public static string[] SelectData(string data)
{
    SqlConnection connection = Connection();
    switch (connection.Equals(null))
    {
        case false:
            var cmd = "SELECT command, properties FROM ai WHERE command = @command"
            using (SqlCommand command = new SqlCommand(cmd, connection))
            {
                command.Parameters.AddWithValue("@command", data);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    switch (reader.Read())
                    {
                        case true:
                            connection.Close();
                            return reader["properties"].toString();
                        default:
                            connection.Close();
                            return null;
                    }
                }
            }
        default:
            connection.Close();
            return null;
    }
}

How do I store data stored in an database table like this:

property1,property2,property3,property4

In an array like this:

string[] propertyArray = { "property1", "property2", "property3", "property4" };

With invinite possible properties.

Best regards, Bradley Methorst

I assume you want to retrieve data from the database, first you need to show us the part of your code that retrieves data, that way we can help you to find out which part needs to be changed — Mehrdad Dowlatabadi
– Mehrdad Dowlatabadi, Commented Sep 17, 2021 at 0:24
So the value of reader["properties"] is property1, property2, property3, property4, and you want to split that into an array? That's poor schema design: never put delimited data into a column. — Joel Coehoorn
– Joel Coehoorn, Commented Sep 17, 2021 at 4:50

Nigel · Accepted Answer · 2021-09-17 21:58:32Z

Updated answer for your updated question:

The simple answer to your question is that you can use string.Split to separate that string at the commas. But the fact that you have to do this is indicative of a larger problem with your database schema.

Right now I'm inferring that your table looks something like this:

ai

command	properties
command1	property1,property2,property3,property4
command2	property1,property2

You should never put comma delimited values into a database. Try something like this:

ai

command	property
command1	property1
command1	property2
command1	property3
command1	property4
command2	property1
command2	property2

Your query becomes: SELECT property FROM ai WHERE command = @command

I would however like to add that even this improved schema is problematic. You don't want to duplicate strings and use them as id's. It's prone to typos and problems when renaming. Instead do something like this:

command

id (int)	name (varchar)
1	command1
2	command2

property

id (int)	name (varchar)
1	property1
2	property2
3	property3
4	property4

commandproperty

commandID (int)	propertyID (int)
1	1
1	2
1	3
1	4
2	1
2	2

Your query roughly becomes: SELECT command.name as command, property.name as property from commandProperty LEFT JOIN command ON command.id = commandID LEFT JOIN property ON property.id = propertyID WHERE commandID = (SELECT TOP 1 id ROM command WHERE name = @command)

There might be typos in that query. I haven't actually executed it. Also, it would be best practice to turn these tables into a view that looks like my second example.

My old answer:

There seems to be something missing in the question.

Is the problem that the array can not be expanded beyond property4? If so try using a List<string>.

Is the proplem that you want to associate column values with column names? In that case try using a Dictionary<string,object> (or Dictionary<string,T> where T is a datatype common to all the columns).

Alternatively, you can try using C#'s built in DataTable. I find them to be a bit verbose to use, but they will probably work for your needs.

basically: string[] result = reader["properties"].toString().Split(','); return result; right?

Nutzs · Accepted Answer · 2021-09-17 04:43:45Z

1

I'm editing the answer based on the new information. I'd still consider using my Dapper wrapper package.

https://www.nuget.org/packages/Cworth.DapperExtensions/#

Create a model class that matches the filed returned in your select.

public class MyModel
{
    public string Command { get; set; }
    public string properties { get; set; }
}

Use nugget package manager to install package referenced above. Update your data access class to add using statement;

using Cworth.DapperExtensions;

Update your method

public async static string[] SelectData(string data)
    {
        var sqlRepo = new SqlRepo(_connectionString);
        var results = await sqlRepo.GetList<MyModel>("MyStoredProc", new { command = data });
        return results.Select(r => r.Properties).ToArray();
    }

Note the above assumes you have created a stored procedure in SQL name "MyStoredProc" that that match your select with parameter "command".

edited Sep 17, 2021 at 4:43

answered Sep 17, 2021 at 1:00

Nutzs

1015 bronze badges

1 Comment

Bradley Methorst Over a year ago

No, it's just 1 row, inside an properties column.

Collectives™ on Stack Overflow

How to store selected data from database in an array in C#

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related