ASPX Inline Code: |
<%@ Page language="c#" Trace="false" Debug="false" Inherits="Testsite.Trans" Src="default.aspx.cs" %>
<%@ Register TagPrefix="Dlp" TagName="CodeViewer" Src="~/templates/CodeViewer/codeViewer.ascx" %>
<html>
<head>
<title>ADO.Net SqlTransactions</title>
<link rel="stylesheet" href="/Templates/style.css" type="text/css" />
</head>
<body>
<h2>ADO.Net SqlTransaction Examples</h2>
<a href="/testsite/">Back to Test Home</a>
<br />
<form method="post" runat="server">
<asp:literal id="errorDisplay" EnableViewState="false" runat="server" />
<div align="left">
<table id="tblEntry" cellpadding="1" cellspacing="1" runat="server">
<tr>
<td>Father's Name:</td><td><input type="text" size="30" onfocus="select()" id="fatherName" runat="server" /></td>
</tr>
<tr>
<td>Age:</td><td><input type="text" size="5" onfocus="select()" id="fatherAge" runat="server" /></td>
</tr>
<tr>
<td>Mother's Name:</td><td><input type="text" size="30" onfocus="select()" id="motherName" runat="server" /></td>
</tr>
<tr>
<td>Age:</td><td><input type="text" size="5" onfocus="select()" id="motherAge" runat="server" /></td>
</tr>
<tr>
<td>Children:</td><td>Test data looks like below:<br /><textarea rows="4" cols="30" id="childrenInfo" runat="server">Jack,18
Jill,19</textarea></td>
</tr>
<tr>
<td colspan="2" align="center"><asp:button id="btnSubmit" Text="Submit!" OnClick="StoreDetails" runat="server"
/><asp:button id="btnClearTable" Text="Clear Tables!" OnClick="ClearTables" runat="server"
/></td>
</tr>
</table>
<br />
<a href="">Start Fresh</a>
</div>
<div align="left">
<asp:DataGrid id="header" EnableViewState="false" runat="server" />
<asp:DataGrid id="detail" EnableViewState="false" runat="server" />
</div>
</form>
<Dlp:CodeViewer CodeFileName="default.aspx" CodeFileType="Inline" runat="server" />
<Dlp:CodeViewer CodeFileName="default.aspx.cs" CodeFileType="CodeBehind" runat="server" />
</body>
</html>
ASPX CodeBehind: |
using System;
using System.Configuration;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace Testsite
{
public class Trans : System.Web.UI.Page
{
private const System.String INSERT_HEADER = "dbo.TxnParentInsert";
private const System.String INSERT_DETAIL = "dbo.TxnChildrenInsert";
private const System.String SELECT_HEADER = "dbo.TxnParentsSelect";
private const System.String SELECT_DETAIL = "dbo.TxnChildrenSelect";
private const System.String CLEAR_TABLES = "dbo.TxnClearTables";
protected HtmlInputText fatherName;
protected HtmlInputText fatherAge;
protected HtmlInputText motherName;
protected HtmlInputText motherAge;
protected HtmlTextArea childrenInfo;
protected DataGrid header;
protected DataGrid detail;
protected Literal errorDisplay;
private System.String connectionString = ConfigurationSettings.AppSettings["connectionString"];
protected void Page_Load(object sender, System.EventArgs e)
{
}
protected void ClearTables(System.Object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(this.connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(CLEAR_TABLES, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
conn = null;
this.errorDisplay.Text = "Tables Cleared";
}
protected void StoreDetails(System.Object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(this.connectionString);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
System.Int32 id = this.InsertHeader(trans);
this.InsertDetail(id, trans);
trans.Commit();
}
catch(Exception le)
{
trans.Rollback();
this.errorDisplay.Text = le.Message;
}
finally
{
trans = null;
}
conn.Close();
conn = null;
this.DisplayGrid(SELECT_HEADER, this.header);
this.DisplayGrid(SELECT_DETAIL, this.detail);
}
private System.Int32 InsertHeader(SqlTransaction trans)
{
SqlCommand cmd = new SqlCommand(INSERT_HEADER, trans.Connection, trans);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("RETURN", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("@fatherName", SqlDbType.VarChar, 20).Value = Nz(fatherName.Value);
cmd.Parameters.Add("@fatherAge", SqlDbType.TinyInt, 1).Value = Nz(fatherAge.Value);
cmd.Parameters.Add("@motherName", SqlDbType.VarChar, 20).Value = Nz(motherName.Value);
cmd.Parameters.Add("@motherAge", SqlDbType.TinyInt, 1).Value = Nz(motherAge.Value);
cmd.Parameters.Add("@id", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
System.Int32 retVal = Convert.ToInt32(cmd.ExecuteNonQuery());
System.Int32 id = Convert.ToInt32(cmd.Parameters["@id"].Value);
cmd = null;
Trace.Write("Header Record Inserted", retVal.ToString());
return id;
}
private void InsertDetail(System.Int32 id, SqlTransaction trans)
{
System.String[] children = this.childrenInfo.Value.Split('\n');
SqlCommand cmd = new SqlCommand(INSERT_DETAIL, trans.Connection, trans);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("RETURN", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;
// used over and over
cmd.Parameters.Add("@parent_id", SqlDbType.Int, 4).Value = id;
// new values each time
cmd.Parameters.Add("@childName", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@age", SqlDbType.TinyInt, 1);
for(System.Int32 i = 0; i < children.Length; i++)
{
System.String[] detail = children[i].Split(',');
cmd.Parameters["@childName"].Value = detail[0];
cmd.Parameters["@age"].Value = detail[1];
cmd.ExecuteNonQuery();
}
cmd = null;
}
private void DisplayGrid(System.String proc, DataGrid dg)
{
SqlConnection conn = new SqlConnection(this.connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(proc, conn);
cmd.CommandType = CommandType.StoredProcedure;
dg.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dg.DataBind();
}
public static System.Object Nz(System.String s)
{
return ((s != null) && (s.Length != 0)) ? (System.Object)s : System.DBNull.Value;
}
}
}