0

I know that array formats do not exist in stored procedure let alone 2-D array. But I use CSV string to store the values of the array using comma delimiter. My main question is can I store in the same way the values which I need in a 2-D format with the help of another delimiter. If yes, Can anyone guide me in the right way to do that?

The table I have is something like this....

    UserID    |   ItemID
  ---------------------------
    User1         1 
    User1         2 
    User2         3 
    User2         1

What I need is create a two dimensional result where for each item I store the users who requested it. So I want to create a csv string like this....

   "1:User1,User2,2:User1,3:User2"

Something similar to the format is it possible, to create this in stored procedure and split the string into 2-d array in c#.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amalpriya
  • 123
  • 1
  • 14
  • 1
    Don't go there. Use a table to implement array like structure, or 2 tables with a relationship of 1 to many to implement a jagged array like structure. Keeping a delimited string in a database table should be avoided in almost any situation (in my 16 years of programming, only once I've chosen to store values as a delimited string in the database, and that's only because I've used them as delimited strings outside of the database). – Zohar Peled Apr 26 '15 at 13:24
  • I am not trying to store the string in the database. I just need to create a string like this in the stored procedure and pass it to my c# code where I can interpret as 2-d array.How to do that?? – Amalpriya Apr 26 '15 at 13:28
  • Don't go there either. use a select statement to fill a DataTable and convert it to a two dimensional array in you c# code. Doing this kind of thing in c# is much easier and will work mach faster then doing it in t-sql. – Zohar Peled Apr 26 '15 at 13:35

1 Answers1

1

The 2D Array you describe would be nice if you need to access both ways, but bear in mind it will probably be very sparse and waste a lot of memory.

If you want to use the data you describe in C#, I would look at Linq GroupBy first. You can easily project the data into a Dictionary<string, List<int>> that holds all the items id's per Userid.

Community
  • 1
  • 1
gjvdkamp
  • 9,929
  • 3
  • 38
  • 46
  • Actually thank you for pointing me in the right direction I think this will suit my needs but can you show me a sample of how to do that or an article on how to do it step by step since I am new to Linq concept and even after spending hours to try to figure it out, I still can't understand how to implement a Linq query and how to store the results I need in IEnumerable. And since I am using ADO.NET Entity Model do i need to create to classes like mentioned in the post you linked. Truly I am clueless on whats happening please help me out. – Amalpriya Apr 27 '15 at 15:09
  • Hi Amalpriya, I'd love to help you but at the moment I'm quite busy so not going be able to any time soon sorry. Good luck, GJ – gjvdkamp Apr 28 '15 at 11:53