Databases
StackOverflow2013
Postsdbo
PODataReader problems getting data from 2 classes
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODataReader problems getting data from 2 classes
    text
    copied!<p>I am very inexperienced with SQL, and have only had one lesson. I am trying to figure out how to get information from 2 classes using a DataReader. </p> <p>My Customer class:</p> <pre><code>public class Customer { private CreditCard _creditCard; private List&lt;Subscription&gt; _subscriptions; private int _customerId; private string _name; private string _address; private int _zipCode; private string _city; private string _email; private string _password; public Customer(int id, string name, string email, string password) { this._customerId = id; this._name = name; this._email = email; this._password = password; this._subscriptions = new List&lt;Subscription&gt;(); } public Customer(int id, string name) { this._customerId = id; this._name = name; _subscriptions = new List&lt;Subscription&gt;(); } public override string ToString() { return _customerId + " " + _name; } public int CustomerId { get { return _customerId; } set { _customerId = value; } } public string Name { get { return _name; } set { _name = value; } } public string Address { get { return _address; } set { _address = value; } } public int ZipCode { get { return _zipCode; } set { _zipCode = value; } } public string City { get { return _city; } set { _city = value; } } public string Email { get { return _email; } set { _email = value; } } public string Password { get { return _password; } set { _password = value; } } public List&lt;Subscription&gt; Subscriptions { get { return _subscriptions; } } public void AddSubscription(Subscription s) { _subscriptions.Add(s); } public Subscription FindSubscription(int sId) { bool found = false; int i = 0; while (!found &amp;&amp; i &lt; _subscriptions.Count) { if (_subscriptions[i].SubscriptionId == sId) found = true; else i++; } if (found) return _subscriptions[i]; else return new Subscription(); } } </code></pre> <p>My Subscription Class:</p> <pre><code>public class Subscription { private SubscriptionType _subscriptionType; private CarModel _carModel; private List&lt;Booking&gt; _bookings; private int _subscriptionId; public Subscription(int subscriptionId, SubscriptionType subscriptionType, CarModel carModel) { this._subscriptionId = subscriptionId; this._subscriptionType = subscriptionType; this._carModel = carModel; _bookings = new List&lt;Booking&gt;(); } public Subscription() { } public int SubscriptionId { get { return _subscriptionId; } set { _subscriptionId = value; } } public SubscriptionType SubscriptionType { get { return _subscriptionType; } } public CarModel CarModel { get { return _carModel; } } public override string ToString() { return this.SubscriptionId + "\t " + this.SubscriptionType + "\t " + this.CarModel; } } </code></pre> <p>My Database class:</p> <pre><code>public class DBCustomer { private static SqlCommand dbCmd = null; public static List&lt;Customer&gt; GetCustomers() { List&lt;Customer&gt; returnList = new List&lt;Customer&gt;(); string sql = "select * from Customer"; dbCmd = AccessDBSQLClient.GetDbCommand(sql); IDataReader dbReader; dbReader = dbCmd.ExecuteReader(); Customer c; while (dbReader.Read()) { c = new Customer(Convert.ToInt32(dbReader["cId"].ToString()), dbReader["cName"].ToString()); returnList.Add(c); } AccessDBSQLClient.Close(); return returnList; } public static List&lt;Customer&gt; GetCustomersByEmail(string email) { List&lt;Customer&gt; returnList = new List&lt;Customer&gt;(); string sql = "select * from Customer where cEmail = '" + email+"'"; dbCmd = AccessDBSQLClient.GetDbCommand(sql); IDataReader dbReader; dbReader = dbCmd.ExecuteReader(); Customer c; while (dbReader.Read()) { c = new Customer(Convert.ToInt32(dbReader["cId"].ToString()), dbReader["cName"].ToString()); returnList.Add(c); } AccessDBSQLClient.Close(); return returnList; } public static List&lt;Subscription&gt; GetCustomerSubscriptions(int cId) { List&lt;Subscription&gt; returnList = new List&lt;Subscription&gt;(); string sql = "select S.sId, ST.stName,CM.cmDescription " + "from Customer C, Subscription S, SubscriptionType ST, CarModel CM " + "where C.cId = S.cId " + "and S.stId = ST.stId " + "and S.cmId = CM.cmId " + "and C.cId = " + cId; dbCmd = AccessDBSQLClient.GetDbCommand(sql); IDataReader dbReader; dbReader = dbCmd.ExecuteReader(); Subscription s; while (dbReader.Read()) { //s = new Subscription(); s = new Subscription((int)(dbReader["sId"]).ToString(), dbReader["stName"], dbReader["cmDescription"]); returnList.Add(s); } AccessDBSQLClient.Close(); return returnList; } </code></pre> <p>Obviously the GetCustomers and GetCustomersByEmail methods are working, but I have no idea how to make the GetCustomerSubscriptions method, as it has to return data fra 2 classes, and I cant just build a Customer to add to the resultset. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload