Page Layout Help

[primagic]

I have a table in sql which contains the following fields:

dbrders
OrderID (int), TraderID, OrderNumber, InvoiceNumber, InvoiceDate.

Linked to this table via the OrderID is a OrderDetails table which contains items for that order. Field names are

dbrderDetails
OrderDetailsID, OrderID, Product, Quantity, UnitPrice, LineTotal.

I have code setup so the user can add a new order to dbrders however I am trying to figure out how I would then get that inserted order and then allow the users to add items to it.

At the moment, I have a query setup which displays any orders the user added which have no items created with it. These are displayed in gridview.

I am trying to get the user to select the appropriate order which will show a form with fields to allow them to add a new item. I was trying details view for this put can't parse the selected OrderID.

Is there a better/easier way of doing this? Once they add an item I want it to be displayed in a gridview so the user can see what they have and havent entered.

 

[jmeckley]

I wouldn't place the order in the database until the order (header and line items) are complete. then I would insert the header and order lines. for your data access needs I would recommend an ORM (nhibernate, LLBL Gen Pro, maybe entity framework) this will handle all the database actions for you.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[primagic]

Are these ORM just controls or plugins for visual studio?

What database actions do you mean? Inserting records and so on with header and items at once?

 

[jmeckley]

Are these ORM just controls or plugins for visual studio?

No, VS is and IDE. it's not required to create .net applications. It just integrates all the tools in one program. It offers drag'n drop "coding" for those that like to create applications that way too.

ORM is an acronym for Object Relational Mapper. the concept encapsulates the conversion of objects (data and behavior) to relational data (structured schema) and vice versa.

You could build your own, which I do not recommend, or you can use a 3rd party assembly. NHibernate, Castle.ActiveRecord, MS Entity Framework and LLBL Gen Pro are the big ORM's for .Net.

It's just code. You can use these libraries without VS.

What database actions do you mean? Inserting records and so on with header and items at once?

Yes. An order consists of a header and line items. it makes sense to place this in the database in a single atomic action. Using an ORM might look like this

//create orm context. typically when request begins
//start transaction. typically just before an action executes

var customer = theORMContext.Get<Customer>(id); 
var order = customer.CreateNewOrder(orderDate);
foreach(var product in selected products)
{
   order.Add(product, quantity);
}
//the order will automatically be saved when the transaction completes.

//complete transaction. typically just after an action executes
//dispose orm context. typically when request ends

in this example the order would either save or rollback when the transaction completes.

All that said, I'm guessing you are more overwhelmed then before you started ORM's are alot to take in. and for novice developers there are so many new concepts introduced by an ORM that it creates information overload.

the alternative approach is to use ADO.Net and raw sql statements to save the order and order lines in a single transaction. the sql would look something like this

//assumes you are using identity for the order primary key
declare @orderid int
insert into [Order](customerid, orderdate, ...) values(@customer, @date);
select @orderid = @@identity;

//for each order line
insert into [OrderLine](orderid, productid, quantity) values (@orderid, @product @quantity)

and the ado.net command might look like this

using(var connection = new connection(connection string))
using(var tx = connection.begintransaction())
{
   connection.Open();
   object id;
   using(var command = connection.CreateCommand())
   {
      command.commandtext = "insert into [Order](customerid, orderdate, ...) values(@customer, @date)";
      //set parameters
      command.ExecuteNonQuery();
   }
   using(var command = connection.CreateCommand())
   {
      command.commandtext = "select @@identity";
      id = command.ExecuteScalar();
   }
   foreach(var product in selected products with quantity)
   {
      using(var command = connection.CreateCommand())
      {
         command.commandtext = "insert into [OrderLine](orderid, productid, quantity) values (@orderid, @product @quantity)";
         //set parameters
         id = command.ExecuteNonQuery();
      }
   }
   tx.Commit();
}

if your interested on ado.net management in conjunction with ado.net I recommend the FAQ I wrote. (shameless self promotion ). The link is listed in my signature. If you have any questions after that post back.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[primagic]

Hey i appreciate your assistance and help. i have read up about the ORM and you are right, complex for people like me starting out. i am more a designer than developer but looking to improve on development skills. Is Asp.net MVC one of these ORM. That looks easier to implement and would give that a go.

As for the @@identity I know how to use that. I have that working after I insert a record using a stored procedure. But then if I am using that elsewhere, then what if multiple users are using the application. surely that @@identity refreshes with the id that another user created. I guess what I am trying to say is, the @@identity is application variable if you like. So surely whatever value it is, then everyone sees the same value. Could be disastrous!

Or am I wrong?

 

[jmeckley]

As for the @@identity ...

that's why it's wrapped within a transaction. the transaction makes this action autonomous. The default transaction isolation level will prevent other operations from accessing these values until the transaction is committed.

Is Asp.net MVC one of these ORM.

MVC stands for Model View Controller. this concept deals with handling http requests. it has nothing to do with data access. Similar to how an ORM has nothing to do with asp.net (web). the 2 can be used together, but one is not dependent on the other.

That looks easier to implement and would give that a go.

in terms of data access, no. MVC has no impact on data access.
in terms of web development. Yes, MVC is infinitely easier to use than webforms. My opinion anyway MVC is a natural fit for the stateless nature of http. Webforms trys to make http requests behave like a desktop application and offers very little flexibility to customize how webforms works.

Jason Meckley
Programmer

faq855-7190
faq732-7259