A Textbox value broken down into individual values and persisted to Database, one per row.

Guest User

14y4.2k

Hi

I have a Textbox which holds a numeric value from 0-9999 to determine the Quantity of an item.

Rather than persisting a value of say 9999 to the Quantity column of the DB, I would like to iterate through each value and persist to the DB in seperate rows.

I was thinking of going down the lines of getting the quantity and storing in a local variable say totalQuantity.

int totalQuantity = 0;
totalQuantity += item.Quantity;

// result of totalQuantity is now 9999

for(int = 0; i < totalQuantity; i++)
{
// Not sure how to get each [i] value and store?
}

OrderDetail od = new OrderDetail();
od.Quantity = ?

// The ? should persist to Quantity DB field over and over until 9999 is reached.
(My DB fields cannot be Null so id have to use the same data for the other columns)

Im hoping there is an easier way than to create a new OrderDetail instance\object and set the Quantity property over and over, maybe something like:-

od.Quantity += totalQuantity;

Or maybe a list of numeric values which are then persisted back to the DB in one wrapped transaction!?

Thanks

Steven

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
Sorry, Steven, I've misunderstood what was needed :(

Anyway, I think the following will do it. I'm assuming the OrderDetailId column is auto-incrementing so we don't need to do anything about that:

public int SubmitOrder(string userName, out List orderDetails)
{
int NewOrderKey = -1;

using (DCECommerceDBEntities db = new DCECommerceDBEntities())
{
try
{
Order newOrder = new Order();
newOrder.CustomerName = userName;
newOrder.OrderDate = DateTime.Now;

db.Orders.AddObject(newOrder);
db.SaveChanges();

NewOrderKey = (from p in db.Orders select p.OrderID).Max();

String cartId = GetShoppingCartId();
var myCart = (from c in db.ViewCarts where c.CartID == cartId select c);

orderDetails = new List();

int quantity;
int totalQuantity = 0;

foreach (ViewCart item in myCart)
{
quantity = item.Quantity;
if (totalQuantity + quantity > 9999)
{
quantity = 9999 - totalQuantity;
}

totalQuantity += quantity;

for(int i = 0; i < quantity; i++)
{
OrderDetail od = new OrderDetail();
od.OrderID = NewOrderKey;
od.ProductID = item.ProductID;
od.Quantity = 1;
od.UnitCost = item.UnitCost;
orderDetails.Add(od);

db.OrderDetails.AddObject(od);
}

var myItem = (from c in db.ShoppingCarts where c.CartID == item.CartID && c.ProductID == item.ProductID select c).FirstOrDefault();
if (myItem != null)
{
db.DeleteObject(myItem);
}

if (totalQuantity == 9999) break;
}
db.SaveChanges();
}
catch(Exception exp)
{
}
}

return NewOrderKey;
}
Vulpes14y ago
That's right.

Each OrderDetail object will translate to a new record in the OrderDetails table of the database with a Quantity column of 1 and so each record will get a new OrderDetailsID column which is one more than the last one.
Guest User14y ago
Vulpes am I right in saying that whatever the Quantity is, ie 1 to 10 then the following happens...

OrderDetailID          Quantity

1                                 1
2                                 1
3                                 1
4                                 1
5                                 1
6                          1
7                                 1
8                          1
9                          1
10                          1

Thanks
Steven
Guest User14y ago
Oh I see now, so its basically the reverse of what it was doing before ie:-

// How it was

OrderDetailID         Quantity
1                                 3

What it is now

OrderDetailID        Quantity
1                                 1
2                                 1
3                                 1

And yes, thats correct the OrderDetailID is in fact an "Auto-Incrementing" field.

All in all what you have answered with there is absolutely perfect and exactly what I was after!

Huge thanks Vulpes!

Cheers

Steven
Vulpes14y ago
Well, the code is now creating 'quantity' OrderDetail objects, each with a Quantity property of 1, rather than just one OrderDetail object with a Quantity property of 'quantity'.

The index 'i' is not actually needed other than to do the counting.

As you're not setting the OrderDetailID property anywhere, I assumed it must have been set to auto-increment in the database. Is this not the case?
Guest User14y ago
Thanks Vulpes

So what did you do there please?

I can see the for loop which iterates the values in the Quantity variable.

And then 1 is set to the od.Quantity property? should this not be [i]?

Also, how will the Auto-Incrementing work with this please?

Regards
Steven
Guest User14y ago
This is the sort of thing I was aiming to achieve

// How it is now

OrderDetailID         Quantity
1                         3

What it should be

OrderDetailID        Quantity
1                         1
2                         1
3                         1
Vulpes14y ago
OK, this is what I'd suggest to limit the total quantity to 9999:

public int SubmitOrder(string userName, out List orderDetails)
{
int NewOrderKey = -1;

using (DCECommerceDBEntities db = new DCECommerceDBEntities())
{
try
{
Order newOrder = new Order();
newOrder.CustomerName = userName;
newOrder.OrderDate = DateTime.Now;

db.Orders.AddObject(newOrder);
db.SaveChanges();

NewOrderKey = (from p in db.Orders select p.OrderID).Max();

String cartId = GetShoppingCartId();
var myCart = (from c in db.ViewCarts where c.CartID == cartId select c);

orderDetails = new List();

int quantity;
int totalQuantity = 0;

foreach (ViewCart item in myCart)
{
quantity = item.Quantity;
if (totalQuantity + quantity > 9999)
{
quantity = 9999 - totalQuantity;
}
totalQuantity += quantity;
OrderDetail od = new OrderDetail();
od.OrderID = NewOrderKey;
od.ProductID = item.ProductID;
od.Quantity = quantity;
od.UnitCost = item.UnitCost;
orderDetails.Add(od);

db.OrderDetails.AddObject(od);

var myItem = (from c in db.ShoppingCarts where c.CartID == item.CartID && c.ProductID == item.ProductID select c).FirstOrDefault();
if (myItem != null)
{
db.DeleteObject(myItem);
}

if (totalQuantity == 9999) break;
}
db.SaveChanges();
}
catch(Exception exp)
{
}
}

return NewOrderKey;
}

Although I haven't done it to avoid disrupting your other code, you could return totalQuantity as an 'out' parameter rather than orderDetails if you don't need any other information.
Guest User14y ago
Hi Vulpes

Ive added some more info to the thread.

Basically the Quantity text box at the moment cannot take more than 9999 which is fine.

If say the order was for a Quantity of 9999, I want to break these up ie
1, 2, 4, 5 up to 9999 in singles and persist each one back to the DB via the Quantity property...

So in the DB I wont have one order for a Quantity of 9999 but instead 9999 orders. I want this so that each Quantity will have a unique OrderId in the OrderDetails table...

Hope this makes sense?

Thanks
Steven
Vulpes14y ago
I imagine this is related to your previous thread(s) where you calculated the total quantity by summing the Quantity properties of all the OrderDetail objects.

If I've understand it correctly, you're now saying that you don't want the total quantity to exceed 9999 and so, if it does, then you will want to restrict the Quantity of or eliminate altogether some of the OrderDetail objects before you persist them to the database.

If that's the case, then the best way to do it is from within your SubmitOrder method - otherwise you might have to delete some of the OrderDetail records you've already added to the database as they (presumably) can't have a zero quantity.

If you can confirm that this in fact is what you want to do, then I'll see if I can come up with some code.