How to populate collection with values from dataset

Question

This is a newbie question, I am trying to build a class in C# that is going to set the UserOrgs property for a user (each user can have more than 1)

I have this thus far:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for clsRepUser
/// </summary>
public class clsUser
{
    private string userid;
    private List<string> userorgs;

    public string UserID
    {
        get
        {
            return userid;
        }
        set
        {
            userid = value;
        }
    }

    public List<string> UserOrgs
    {
        get
        {
            return userorgs;
        }
        set
        {
            userorgs = value;
        }
    }

    clsConn cCon = new clsConn();
    String connStr = "";
    public clsUser()
    {

    }

    public DataSet GetUserOrg(string UserID)
    {
        DataSet ds = new DataSet();
        SqlConnection conn = new SqlConnection(cCon.getConn());

        SqlCommand cmd = new SqlCommand("sp_getUserOrgs", conn);

        // 2. set the command object so it knows
        // to execute a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;

        // 3. add parameter to command, which
        // will be passed to the stored procedure
        cmd.Parameters.Add(
            new SqlParameter("@UserID", UserID));

        try 
        {
            // Open the connection and execute the Command
            conn.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();

            sqlDA.SelectCommand = cmd;
            sqlDA.Fill(ds);
        } 
        catch (Exception ex) 
        {
        } 
        finally 
        {
            conn.Close();
        }

        return ds;    
    }
}

How do I now populate the UserOrgs property of that user from the GetUserOrg function? Or am I way off on this?

This is really bad code: catch (Exception ex) {} It's like disconnecting the smoke detectors in your children's bedrooms. — Steve Wellens, Jan 28 '13 at 16:04
@SteveWellens I'm not sure I'd equate silently ignoring a database error in a homework assignment to endangering your childrens' lives... — itsme86, Jan 28 '13 at 16:36
@itsme86 If it was a medical database being used to diagnose an illness of your child you would. I used exaggeration to make a point. Let's try to stay on topic. — Steve Wellens, Jan 28 '13 at 17:53
possible duplicate of [Reading DataSet](http://stackoverflow.com/questions/6409839/reading-dataset) — Fedor, Apr 14 '14 at 10:05

score 0 · Answer 1 · answered Jan 28 '13 at 16:04

I would use an orm, linqtosql is easy for newbies. If you are stuck on using datatables and datareaders

You will need to iterate through the returned results and stuff the return values into your objects.

This is a pretty good primer to start of with.

Alexey Ripenko · Answer 2 · 2013-01-28T16:34:58.563

0

I think there are 3 ways:

You can use generic Lazy<>
Replace property to method GetUserOrganizations(). It's more clearly. If you want to get "fresh" organizations.
Move getting logic to another layer (BI, DAL). Then user type is like data contract.

edited Jan 28 '13 at 16:34

answered Jan 28 '13 at 16:09

Alexey Ripenko

139
1
2
7

Bee · Accepted Answer · 2013-01-28T20:48:40.087

0

You are using a stored procedure so it's hard to give you an exact code, but the idea is to go through all rows of your DataSet, and add the element of that row to your list using .Add() method. Something like this:

foreach (DataRow dr in ds.Tables[0].Rows) {  
  userorgs.Add(dr["orgs_column"].ToString()); 
}

edited Jan 28 '13 at 20:48

answered Jan 28 '13 at 16:16

Bee

2,472
20
26

Bee ds is dataset, you can add Tables property in order to access table property, and fetch around rows – Aghilas Yakoub Jan 28 '13 at 16:42
I'am happy to help you Bee – Aghilas Yakoub Jan 29 '13 at 09:17

score 0 · Answer 4 · answered Jan 28 '13 at 16:39

You can try with this code - based on DataSet.Tables[0].Rows

using(var conn = new SqlConnection(cCon.getConn())
{
        using(var cmd = new SqlCommand("sp_getUserOrgs", conn))
        {
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(new SqlParameter("@UserID", UserID));

          try 
          {
            conn.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();

            sqlDA.SelectCommand = cmd;
            sqlDA.Fill(ds);

            foreach (DataRow dr in ds.Tables[0].Rows) 
            {  
               userorgs.Add(dr["orgs_column"].ToString()); 
            }
          } 
          catch (Exception ex) 
          {
            //treat your exception
          } 
      }       
  }

How to populate collection with values from dataset

4 Answers4