Beginning IronPython - Connecting to a database and using the gridview (Part 2)
This tutorial goes over connecting to a database, binding the data to a grid,
then color coding the stocked quantity column. This could be done through sqldatasource, however for this demonstration it will be bound manually to show how it can be done.
The AdventureWorks database will be used.
Create a new web site and replace the code behind with the template from the first article.
import clr
clr.AddReference('System.Data')
clr.AddReference('System.Configuration')
clr.AddReference('System.Web')
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 *
def Page_Load(sender, e):
if not sender.IsPostBack:
pass
Next, add a gridview to the aspx page, then add the SqlClient the namespace reference to the top of the
code-behind file:
from System.Data.SqlClient import *
Replace the Page_Load method with the following code. It will connect to the AdventureWorks database and show the first 100
orders from the Production.WorkOrder table.
def Page_Load(sender, e):
if not sender.IsPostBack:
conn = SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString)
conn.Open()
sql = """SELECT
TOP (100) WorkOrderID, ProductID, OrderQty, StockedQty, ScrappedQty, StartDate,
EndDate, DueDate, ScrapReasonID, ModifiedDate
FROM Production.WorkOrder"""
cmd = SqlCommand(sql, conn)
da = SqlDataAdapter(cmd)
dt = DataTable()
da.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
conn.Close()
For a quick comparison, this is how it would look in C#.
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString);
conn.Open();
string sql = @"SELECT
TOP (100) WorkOrderID, ProductID, OrderQty, StockedQty, ScrappedQty, StartDate,
EndDate, DueDate, ScrapReasonID, ModifiedDate
FROM Production.WorkOrder";
SqlCommand cmd =
new SqlCommand(sql, conn);
SqlDataAdapter
da = new
SqlDataAdapter(cmd);
DataTable
dt = new
DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
conn.Close();
}
As you can see, the code is simliar, with some differences:
- No type definitions in Python. There is no need to declare the datatypes, they are implicitly assigned.
- No semicolons in Python. They are optionally, but not not necessary.
- There is no "new" keyword when creating objects in Python.
- Multiline strings are defined differently in Python. In Python, they are defined using triple quotes.
If you run the page, it should show the first 100 records. Now, lets do some
customization to the gridview. We are going to color code StockedQty
using row processing. Modify the gridview, changing the StockedQty to an item
template as displayed below. Add
OnRowDataBound="GridView1_OnRowDataBound" to the gridview definition.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="WorkOrderID" OnRowDataBound="GridView1_OnRowDataBound">
<Columns>
<asp:BoundField DataField="WorkOrderID" HeaderText="WorkOrderID" InsertVisible="False"
ReadOnly="True" SortExpression="WorkOrderID" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID" SortExpression="ProductID" />
<asp:BoundField DataField="OrderQty" HeaderText="OrderQty" SortExpression="OrderQty" />
<asp:TemplateField HeaderText="StockedQty">
<ItemTemplate>
<asp:Label ID="uxStockedQty" runat="server" Text='<%# Eval("StockedQty") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ScrappedQty" HeaderText="ScrappedQty" SortExpression="ScrappedQty" />
<asp:BoundField DataField="StartDate" HeaderText="StartDate" SortExpression="StartDate" />
<asp:BoundField DataField="EndDate" HeaderText="EndDate" SortExpression="EndDate" />
<asp:BoundField DataField="DueDate" HeaderText="DueDate" SortExpression="DueDate" />
<asp:BoundField DataField="ScrapReasonID" HeaderText="ScrapReasonID" SortExpression="ScrapReasonID" />
<asp:BoundField DataField="ModifiedDate" HeaderText="ModifiedDate" SortExpression="ModifiedDate" />
</Columns>
</asp:GridView>
Add a reference to the drawing namespace to the top of the code-behind
from System.Drawing import * then add the following
event handler:
def GridView1_OnRowDataBound(sender, e):
if e.Row.RowType == DataControlRowType.DataRow:
stockedQty = e.Row.DataItem["StockedQty"]
uxStockedQty = e.Row.FindControl("uxStockedQty")
if stockedQty <= 5:
uxStockedQty.ForeColor = Color.Red
elif stockedQty > 5 and stockedQty <= 10:
uxStockedQty.ForeColor = Color.Gold
else:
uxStockedQty.ForeColor = Color.Green
Again, let's compare it to C#:
protected void GridView1_OnRowDataBound(Object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label uxStockedQty = (Label)e.Row.FindControl("uxStockedQty");
int stockedQty = (int)((DataRowView)e.Row.DataItem)["StockedQty"];
if (stockedQty <= 5)
{
uxStockedQty.ForeColor = Color.Red;
}
else if (stockedQty > 5 && stockedQty < 10)
{
uxStockedQty.ForeColor = Color.Gold;
}
else
{
uxStockedQty.ForeColor = Color.Green;
}
}
}
Here are the differences in syntax:
- No need to cast an object in Python
- "and" instead of "&&" in
conditional statements
- No need to put the condition statement in parenthesis
- "else if" is "elif" in Python
- No annoying curly brackets =)
If you run the page, the output will look similiar to this:
That is it for this tutorial. The source code for both Python and C# can be
viewed by clicking the link below. You will need to modify the connection string
in the web.config file to point to your sql server 2005 AdventureWorks database
if you wish to run the project.
IronPythonTutorial2.zip (798.76 kb)