How to process the data in the database into arrays?

Question

Currently i am developing an WPF application using C# and Linq and i got an uneditable database(as what display in the image below). Does anyone can teach me how to store the data of the "Statistic" into four arrays.

1. First array: "HomeShot"
2. Second array: "HomeScore"
3. Third array: "AwayShot"
4. Fourth array: "AwayScore"

Answer 1

As I mentioned in my comment, and similar to Derek's answer, you probably want a single type that represents a row in your datagrid. (Frequently, datagrids are bound to business object types, so it's worth mentioning that you might have a type with more properties than you need for this datagrid, if you use the type elsewhere in your program.) For these examples, I'll use Derek's Statistics type, with some modifications and a name change, and assume that you're binding the datagrid to a collection that implements ICollection<Statistic>:

public class Statistic
{
    public Statistic(int id, int homeshot, int homescore, int awayshot, int awayscore)
    {
        ID = id;
        HomeShot = homeshot;
        HomeScore = homescore;
        AwayShot = awayshot;
        AwayScore = awayscore;
    }

    public int ID { get; set; }
    public int HomeShot { get; set; }
    public int HomeScore { get; set; }
    public int AwayShot { get; set; }
    public int AwayScore { get; set; }
}

You haven't given any information about how you are retrieving the information from the database, so I will assume that you are using a DbDataReader. If you're using linq to SQL, this example would look somewhat different:

while (dataReader.Read())
{
    int id = (int)dataReader["ID"];
    string statisticSource = (string)dataReader["Statistic"];
    int[] data = ExtractData(statisticSource);
    Statistic statistic = new Statistic(id, data[0], data[1], data[2], data[3]);
    statCollection.Add(statistic);
}

There are many options for the implementation of the ExtractData method, depending on how consistent the format of the Statistic string is guaranteed to be. There are even several options for the method signature. I've made the assumption that it returns an int array containing the four values in order; other implementations might return a string-indexed collection or something else.

Also, you could invoke the string parsing logic in the Statistic constructor. To do so, create a constructor with this signature:

public Statistic(int id, string statisticSource)
{
    ID = id;
    int[] data = ExtractData(statisticSource);
    HomeShot = data[0];
    HomeScore = data[1];
    AwayShot = data[2];
    AwayScore = data[3];
}

In this case, the client code would look like this:

while (dataReader.Read())
{
    int id = (int)dataReader["ID"];
    string statisticSource = (string)dataReader["Statistic"];
    Statistic statistic = new Statistic(id, statisticSource);
    statCollection.Add(statistic);
}

Or, in fact, if you prefer fewer lines with more complex expressions:

while (dataReader.Read())
    statCollection.Add(new Statistic((int)dataReader["ID"], (string)dataReader["Statistic"]));

Oops, I just noticed the linq to SQL tag. I'll assume that Linq to SQL is giving you the table as a sequence of DbStatistic instances. In that case, your client code would look something like this:

foreach (var dbStatistic in dbStatistics)
{
    int[] data = ExtractData(dbStatistic.Statistic);
    Statistic statistic = new Statistic(dbStatistic.ID, data[0], data[1], data[2], data[3]);
    statCollection.Add(statistic);
}

or this:

foreach (var dbStatistic in dbStatistics)
{
    Statistic statistic = new Statistic(dbStatistic.ID, dbStatistic.Statistic);
    statCollection.Add(statistic);
}

or this:

foreach (var dbStatistic in dbStatistics)
    statCollection.Add(new Statistic(dbStatistic.ID, dbStatistic.Statistic));

Answer 2

I think a better way of approaching something like this is too first learn how to manipulate the string so that you can break it down into your 4 section.

Easiest way i can think of would be to simply grab the numbers :-

public List<int> statValues = new List<int>();

string input = "HOME SHOT 5 SCORE 2, AWAY SHOT 3 SCORE 0";
string result = Regex.Replace(input, @"[^\d]", "");

This would give you 5230. You could then split this into an Integers :-

foreach (var c in result)
            {
                statValues.Add((int)c);
            }

Once you have this worked out, I would look at Building a Class that represents your data like this :-

public class Statistics {

public Statistics(int homeshot, int homescore, int awayshot, int awayscore)
{

    HomeShot = homeshot;
    HomeScore = homescore;
    AwayShot = awayshot;
    AwayScore = awayscore;
}

public int HomeShot { get; set; }
public int HomeScore { get; set; }
public int AwayShot { get; set; }
public int AwayScore { get; set; }

}

You then need to look at creating a List Object of Statistics :-

List<Statistics> listofStats = new List<Statistics>();

When you manipulate each entry in you Database add a new item to your list like this :-

listofStats.Add(new Statistics(statValues[0],statValues[1],statValues[2],statValues[3]));

Once you have built this List from all entries in your database, you can use this as the DataSource for your DataGridView.

I've done this off the top of my head pretty much, hope it helps. I'm sure others will have cleaner ways of doing this.