Beginning IronPython - Creating an update form (Part 4)
In this tutorial, we will create a simple update form. It will work off the class that was built in the last tutorial and
use object oriented principals.
First, we'll need to add an update method to the WorkOrder class. Add the following to WorkOrder.py:
def Update(self):
conn = SqlConnection(self.__ConnectionString)
sql = """UPDATE Production.WorkOrder
SET
ProductID = @ProductID,
OrderQty = @OrderQty,
ScrappedQty = @ScrappedQty,
StartDate = @StartDate,
EndDate = @EndDate,
DueDate = @DueDate,
ScrapReasonID = @ScrapReasonID,
ModifiedDate = @ModifiedDate
WHERE WorkOrderID = @WorkOrderID"""
cmd = SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@WorkOrderID", self.WorkOrderID)
cmd.Parameters.AddWithValue("@ProductID", self.ProductID)
cmd.Parameters.AddWithValue("@OrderQty", self.OrderQty)
cmd.Parameters.AddWithValue("@ScrappedQty", self.ScrappedQty)
cmd.Parameters.AddWithValue("@StartDate", self.StartDate)
cmd.Parameters.AddWithValue("@EndDate", self.EndDate)
cmd.Parameters.AddWithValue("@DueDate", self.DueDate)
cmd.Parameters.AddWithValue("@ScrapReasonID", self.ScrapReasonID)
cmd.Parameters.AddWithValue("@ModifiedDate", self.ModifiedDate)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
A simple update method similar to the get method. Nothing unusual here.
Next, we'll add the textboxes and dropdowns to the aspx page, with a few
validators where needed.
Here is the content on the aspx page between the form tags:
<asp:Label
ID="uxMessage"
runat="server"
/>
<div>
<table>
<tr>
<th>
Work Order ID:
</th>
<td>
<asp:TextBox ID="uxWorkOrderID" runat="server" />
</td>
<td>
</td>
</tr>
<tr>
<th>
Product:
</th>
<td>
<asp:DropDownList ID="uxProductID" runat="server" DataSourceID="dsProductID" DataValueField="ProductId"
DataTextField="Name">
</asp:DropDownList>
<asp:SqlDataSource ID="dsProductID" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
ProviderName="<%$ ConnectionStrings:AdventureWorks.ProviderName %>" SelectCommand="SELECT ProductId, Name FROM Production.Product ORDER BY NAME">
</asp:SqlDataSource>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="uxProductID"
ErrorMessage="Required" />
</td>
</tr>
<tr>
<th>
Order Qty:
</th>
<td>
<asp:TextBox ID="uxOrderQty" runat="server" />
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="uxOrderQty"
ErrorMessage="Required" />
</td>
</tr>
<tr>
<th>
Stocked Qty:
</th>
<td>
<asp:Label ID="uxStockedQty" runat="server" />
</td>
</tr>
<tr>
<th>
Scrapped Qty:
</th>
<td>
<asp:TextBox ID="uxScrappedQty" runat="server" />
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="uxScrappedQty"
ErrorMessage="Required" />
</td>
</tr>
<tr>
<th>
Start Date:
</th>
<td>
<asp:TextBox ID="uxStartDate" runat="server" />
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="uxStartDate"
ErrorMessage="Required" />
</td>
</tr>
<tr>
<th>
End Date:
</th>
<td>
<asp:TextBox ID="uxEndDate" runat="server" />
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="uxEndDate"
ErrorMessage="Required" />
</td>
</tr>
<tr>
<th>
Due Date:
</th>
<td>
<asp:TextBox ID="uxDueDate" runat="server" />
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ControlToValidate="uxDueDate"
ErrorMessage="Required" />
</td>
</tr>
<tr>
<th>
Scrap Reason:
</th>
<td>
<asp:DropDownList ID="uxScrapReasonID" runat="server" DataSourceID="dsScrapReasonID" DataValueField="ScrapReasonID"
DataTextField="Name" AppendDataBoundItems="true">
<asp:ListItem Value="">-- Choose One --</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsScrapReasonID" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
ProviderName="<%$ ConnectionStrings:AdventureWorks.ProviderName %>" SelectCommand="SELECT ScrapReasonId, Name FROM Production.ScrapReason ORDER BY
NAME">
</asp:SqlDataSource>
</td>
<td>
</td>
</tr>
</table>
<br />
<asp:Button ID="btnSubmit" Text="Submit" runat="server" OnClick="btnSubmit_Click" />
</div>
I've prefixed the controls with ux. It's a personal convention I have for input controls. I also keep the ids consistent with the fields in the database. For the dropdowns, the SqlDataSource control is used to make databinding easier. For ScrapReason, I've added a ListItem called "-- Choose One --" as this value can be null.
Next, let's add the code-behind for the fields to get and update the data.
import clr
clr.AddReference('System.Data')
clr.AddReference('System.Configuration')
clr.AddReference('System.Web')
from System import *
from System.Data import *
from System.Configuration import *
from System.Web import *
from System.Web.Security import *
from System.Web.UI import *
from System.Web.UI.WebControls import *
from System.Web.UI.WebControls.WebParts import *
from System.Web.UI.HtmlControls import *
from WorkOrder import *
def Page_Load(sender, e):
if not sender.IsPostBack:
wo = WorkOrder(1)
uxWorkOrderID.Text = str(wo.WorkOrderID)
uxProductID.SelectedValue = str(wo.ProductID)
uxOrderQty.Text = str(wo.OrderQty)
uxStockedQty.Text = str(wo.StockedQty)
uxScrappedQty.Text = str(wo.ScrappedQty)
uxStartDate.Text = wo.StartDate.ToString("M/dd/yyyy")
uxEndDate.Text = wo.EndDate.ToString("M/dd/yyyy")
uxDueDate.Text = wo.DueDate.ToString("M/dd/yyyy")
if wo.ScrapReasonID != DBNull.Value: uxScrapReasonID.SelectedValue = str(wo.ScrapReasonID)
def btnSubmit_Click(sender, e):
if Page.IsValid:
Update()
def Update():
wo = WorkOrder(uxWorkOrderId.Text)
wo.ProductID =
uxProductID.Text
wo.OrderQty =
uxOrderQty.Text
wo.ScrappedQty =
uxScrappedQty.Text
wo.StartDate =
uxStartDate.Text
wo.EndDate =
uxEndDate.Text
wo.DueDate =
uxDueDate.Text
if uxScrapReasonID.SelectedValue == String.Empty: wo.ScrapReasonID = DBNull.Value
else: wo.ScrapReasonID =
uxScrapReasonID.SelectedValue
wo.ModifiedDate = DateTime.Now
wo.Update()
Here is the meat of the form. The Page_Load method gets the database record with id "1" and assigns the
textboxes and dropdowns to the fields in the aspx page. A function you might be unfamiliar with "str", is a python method that converts a variable into a string. It has pretty much the same functionality as the ToString() method in .net. Either method could be used in this situation. For the date fields, I've used the .net method as it has some nice functionality to format
datetime strings. The documentation for string formats is located on
MSDN. For ScrapReasonId, I've added a conditional statement to check if the property is
a database null and only assign it if it isn't. Otherwise, you would get an error that you can't convert a null to a string.
Next is the update statement. It's similar to the Page_Load statement, only going the opposite way (assigning the values of the form back to the object). Unlike c#, you don't need to convert the data types as python doesn't require it and sql server will convert them automatically. Of course, for validation purposes, it would be wise to check if the dates entered are valid date formats (or use a calendar control). The Update() method will update the data in the database.
The form looks similar to this:
In the next tutorial, we'll look at some dynamic features of Python to reduce code and decrease the time it takes to create a form.
You can download both the IronPython and C# source codes below.
IronPython Tutorial4.zip (798.68 kb)
IronPython Tutorial4 (Csharp).zip (5.58 kb)