Create a Collection in C#
-
I have a table in a database containing a number of records. The records have an ID number i.e. H1, H2, H3. Each record also contains a field called C1 which contains a value. There can be multiple records with the same ID. I want to read all the values of fields C1 for records with the ID H1 into an array. I don't know exactly how many records there are with the ID H1. I don't want to do this in SQL. Does anyone know how I would create a collection holding the values?
-
I have a table in a database containing a number of records. The records have an ID number i.e. H1, H2, H3. Each record also contains a field called C1 which contains a value. There can be multiple records with the same ID. I want to read all the values of fields C1 for records with the ID H1 into an array. I don't know exactly how many records there are with the ID H1. I don't want to do this in SQL. Does anyone know how I would create a collection holding the values?
Read the data from the Db using a trivial SELECT:
SELECT C1 FROM MyTable WHERE ID='H1'
You can read the values using a DataReader, or a DataAdapter - whichever you are comfortable with. For a reader, it's really simple:
List<MyTypeOfData> collection = new List<MyTypeOfData>();
while (myReader.Read())
{
collection.Add((MyTypeOfData) myReader["C1"]);
}For an adapter, just use the table data in a Linq query:
var collection = myDataTable.AsEnumerable().Select(row => (MyTypeOfData) row["C1"]);
[edit]forgot the brackets... :doh: [/edit]
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
Read the data from the Db using a trivial SELECT:
SELECT C1 FROM MyTable WHERE ID='H1'
You can read the values using a DataReader, or a DataAdapter - whichever you are comfortable with. For a reader, it's really simple:
List<MyTypeOfData> collection = new List<MyTypeOfData>();
while (myReader.Read())
{
collection.Add((MyTypeOfData) myReader["C1"]);
}For an adapter, just use the table data in a Linq query:
var collection = myDataTable.AsEnumerable().Select(row => (MyTypeOfData) row["C1"]);
[edit]forgot the brackets... :doh: [/edit]
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
I have a table in a database containing a number of records. The records have an ID number i.e. H1, H2, H3. Each record also contains a field called C1 which contains a value. There can be multiple records with the same ID. I want to read all the values of fields C1 for records with the ID H1 into an array. I don't know exactly how many records there are with the ID H1. I don't want to do this in SQL. Does anyone know how I would create a collection holding the values?
It sounds to me like you're looking at having a Dictionary of Dictionaries like this:
private Dictionary> dictionary = new Dictionary>();
public void Add(string id, string fieldValue, ClassRepresentingOtherItems items)
{
if (!dictionary.ContainsKey(id))
{
Dictionary childDictionary = new Dictionary();
dictionary.Add(id, childDictionary);
}
dictionary[id].Values.Add(fieldValue, items);
}This allows you to associate many fields with the same ID.
-
Thanks for the reply OriginalGriff. What i want to do is add together all the values for each occurence of C1 and then to write this to a field. Can I do this with what you have suggested?
To add them together (assuming they are numeric), you don't even need a collection:
SELECT SUM(C1) FROM MyTable WHERE ID = 'H1'
Will return the total for you - and you can use ExecuteScalar to fetch the value directly without a reader or adapter.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
To add them together (assuming they are numeric), you don't even need a collection:
SELECT SUM(C1) FROM MyTable WHERE ID = 'H1'
Will return the total for you - and you can use ExecuteScalar to fetch the value directly without a reader or adapter.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
It sounds to me like you're looking at having a Dictionary of Dictionaries like this:
private Dictionary> dictionary = new Dictionary>();
public void Add(string id, string fieldValue, ClassRepresentingOtherItems items)
{
if (!dictionary.ContainsKey(id))
{
Dictionary childDictionary = new Dictionary();
dictionary.Add(id, childDictionary);
}
dictionary[id].Values.Add(fieldValue, items);
}This allows you to associate many fields with the same ID.
I have no code written yet as I'm just looking for ideas. I am just reading the values in a field in records in a database table. There are multiple records with the value H1 and I want to get each value C1 in each record with ID H1. And then I would count the number of occurences of C1 from the records with ID H1. I am looking how to do this using C#
-
The just add the collection items together! Use a foreach loop, or even a Linq method:
var sum = collection.Sum(item => item);
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
The just add the collection items together! Use a foreach loop, or even a Linq method:
var sum = collection.Sum(item => item);
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
Linq Method Would probably be the best as I have to write it out to a field. I'll put together some code and see what you think?
Sounds good!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
Sounds good!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
Im trying to do something like this: var complexQuery = from e in orgContext.CreateQuery("Eval") join c in orgContext.CreateQuery("Contract") on e["contractid"] equals c["contractnameid"] I want to do a Count of the complexQuery and whatever the count is I want to add this to a variable so I can write this out to a field.
-
Im trying to do something like this: var complexQuery = from e in orgContext.CreateQuery("Eval") join c in orgContext.CreateQuery("Contract") on e["contractid"] equals c["contractnameid"] I want to do a Count of the complexQuery and whatever the count is I want to add this to a variable so I can write this out to a field.
Um...you do realize that a Collection has a Count method?[^] So to get all the elements in your total sequence:
var complexQuery = from e in orgContext.CreateQuery("Eval")
join c in orgContext.CreateQuery("Contract")
on e["contractid"] equals c["contractnameid"];
int count = complexQuery.Count();If you are trying to get the count of elements with matching ids (i.e. a collection of counts) then just Group the linq query and select the count of each group.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
Um...you do realize that a Collection has a Count method?[^] So to get all the elements in your total sequence:
var complexQuery = from e in orgContext.CreateQuery("Eval")
join c in orgContext.CreateQuery("Contract")
on e["contractid"] equals c["contractnameid"];
int count = complexQuery.Count();If you are trying to get the count of elements with matching ids (i.e. a collection of counts) then just Group the linq query and select the count of each group.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
You're welcome!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
I have a table in a database containing a number of records. The records have an ID number i.e. H1, H2, H3. Each record also contains a field called C1 which contains a value. There can be multiple records with the same ID. I want to read all the values of fields C1 for records with the ID H1 into an array. I don't know exactly how many records there are with the ID H1. I don't want to do this in SQL. Does anyone know how I would create a collection holding the values?
macca24 wrote:
I don't want to do this in SQL.
Why not? If the set is small, like say less than 100,000 than a query each time is likely to be best. If the set is large, like 10 billion, then dragging all of the records across the wire probably isn't a good idea. Which suggests you basic assumption of a solution is wrong and again SQL would probably be better for a real solution.
-
macca24 wrote:
I don't want to do this in SQL.
Why not? If the set is small, like say less than 100,000 than a query each time is likely to be best. If the set is large, like 10 billion, then dragging all of the records across the wire probably isn't a good idea. Which suggests you basic assumption of a solution is wrong and again SQL would probably be better for a real solution.
