The file Template.txt as shown in Listing 12.4 contains the template code that's used to generate .aspx files. It contains the UI code to bind a DataGrid to a DataTable , as well as code to populate the DataTable object. It also contains placeholders that are replaced by code in CreatePage.aspx . This file is considered read-only: It's not generated or modified by the code.
Listing 12.4 Template.txt
<%@ Page Language="C#" Debug="true"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script language="C#" runat="server"> //protected System.Web.UI.WebControls.DataGrid resultGrid; protected DataTable dt; private void Page_Load(object sender, System.EventArgs e) { if(Session["dataTable"] == null) { dt = new DataTable("<##DATA_TABLE##>"); SqlConnection cn = new SqlConnection ("<##CONNECTION_STRING##>"); cn.Open(); SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM [<##DATA_TABLE##>]",cn); adapter.Fill(dt); Session["dataTable"] = dt; adapter.Dispose(); cn.Close(); cn.Dispose(); } else { dt = (DataTable)Session["dataTable"]; } if(!IsPostBack) { BindResultGrid(); } } protected void resultGrid_Edit(Object sender, DataGridCommand EventArgs e) { resultGrid.EditItemIndex = e.Item.ItemIndex; BindResultGrid(); } protected void resultGrid_Cancel(Object sender, DataGridCommand EventArgs e) { resultGrid.EditItemIndex = -1; BindResultGrid(); } protected void resultGrid_Update(Object sender, DataGridCommand EventArgs e) { // For bound columns, the edited value is stored in a TextBox. // The TextBox is the 0th element in the column's cell. //If read-only, then simply retrieve from position. //Need to know the column name here <##UPDATE##> adapter.UpdateCommand = cm; adapter.Update(dt); resultGrid.EditItemIndex = -1; BindResultGrid(); } protected void BindResultGrid() { resultGrid.DataSource = dt; resultGrid.DataBind(); } </script> <HTML> <HEAD> <title>result</title> </HEAD> <body> <form id="result" method="post" runat="server"> <asp:DataGrid id="resultGrid" runat="server" BorderColor="black" BorderWidth="1" CellPadding="3" Font-Name="Verdana" Font-Size="8pt" OnEditCommand="resultGrid_Edit" OnCancelCommand="resultGrid_Cancel" OnUpdateCommand="resultGrid_Update" AutoGenerateColumns="false"> <HeaderStyle BackColor="#aaaadd"></HeaderStyle> <EditItemStyle BackColor="yellow"></EditItemStyle> <Columns> <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderText="Edit Command Column"> <ItemStyle Wrap="false"></ItemStyle> <HeaderStyle Wrap="false"></HeaderStyle> </asp:EditCommandColumn> <##BOUND_COLUMNS##> </Columns> </asp:DataGrid> </form> </body> </HTML>
Listing 12.5 contains the code for CreatePage.aspx.cs .
Listing 12.5 CreatePage.aspx.cs
using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Xml; using System.Xml.Xsl; using System.Xml.XPath; using System.Text; using System.IO; namespace pagebuilder.secure { /// <summary> /// Summary description for createpage. /// </summary> public class createpage : System.Web.UI.Page { private void Page_Load(object sender, System.EventArgs e) { FileStream file = new FileStream(Server.MapPath("template.txt"), System.IO.FileMode.Open); StreamReader reader = new StreamReader(file); string contents = reader.ReadToEnd(); reader.Close(); file.Close(); XmlDocument doc = (XmlDocument)Session["doc"]; XmlElement root = doc.DocumentElement; string tableName = root.Attributes["tableName"].Value; string connectionString = root.Attributes["connectionString"].Value; string emailAddress = root.Attributes["emailAddress"].Value; //Replace the DATA_TABLE marker with the name //of the datbase table contents = contents.Replace("<##DATA_TABLE##>", tableName); //Replace the CONNECTION_STRING marker with the // database connection string contents = contents.Replace("<##CONNECTION_STRING##>", connectionString); //Replace the UPDATE marker with the string returned // from the GetUpdate function. contents = contents.Replace("<##UPDATE##>", GetUpdate(root)); contents = contents.Replace("<##BOUND_COLUMNS##>", GetBoundColumns(tableName,connectionString)); System.Web.Mail.MailMessage msg = new System.Web.Mail.MailMessage(); msg.Subject="PageBuilder Generated Code"; msg.To = emailAddress; msg.From = "pagebuilder@xmlandasp.net"; msg.Body = contents; System.Web.Mail.SmtpMail.Send(msg); Response.Write("<html><head></head><body>"); Response.Write("The file has been sent to the email address specified."); Response.Write("</body></html>"); } private string GetUpdate(XmlElement root) { string tableName = root.Attributes["tableName"].Value; string connectionString = root.Attributes["connectionString"].Value; DataTable dt = BuilderLib.GetDataTableSchema(tableName,connectionString); MemoryStream memstream = new MemoryStream(); StreamWriter writer = new StreamWriter(memstream, System.Text.Encoding.Unicode); writer.WriteLine("TextBox tempBox;"); foreach(DataColumn col in dt.Columns) { int cellNum = col.Ordinal +1; if(col.ReadOnly ) { //Read-only columns do not have a corresponding textbox writer.WriteLine("String " + col.ColumnName + "Val = e.Item.Cells[" + cellNum.ToString() + "].Text;"); } else { //Retrieve the value from the user's input for the textbox writer.WriteLine("tempBox = (TextBox)e.Item.Cells[" + cellNum +"].Controls[0];"); writer.WriteLine("String " + col.ColumnName + "Val = tempBox.Text;"); } } //Create a view over the DataTable. writer.WriteLine("DataView dv = new DataView(dt);"); writer.WriteLine("dv.RowStateFilter = System.Data.DataViewRowState.CurrentRows;"); //Write out the code to filter based on primary key(s) writer.Write("dv.RowFilter = \""); WritePKFilter(writer,dt.PrimaryKey); writer.WriteLine("\";"); //Write out the code to update the column value foreach(DataColumn col in dt.Columns) { if(!col.ReadOnly) { writer.WriteLine("dv[0][\"" + col.ColumnName + "\"] = " + col.ColumnName + "Val;"); } } writer.WriteLine(); writer.Write("SqlConnection cn = new SqlConnection(\""); writer.Write(root.Attributes["connectionString"].Value); writer.WriteLine("\");"); writer.WriteLine("cn.Open();"); writer.WriteLine(); writer.WriteLine("SqlDataAdapter adapter = new SqlDataAdapter();"); writer.WriteLine(); writer.WriteLine("SqlCommand cm;"); writer.WriteLine(); writer.Write("cm = new SqlCommand(\"SELECT "); int intLength = dt.Columns.Count -1; for (int i=0;i<=intLength;i++) { writer.Write(dt.Columns[i].ColumnName); //Separate column names with commas if(i<intLength) { writer.Write(", "); } } writer.WriteLine(" FROM " + tableName + "\",cn);"); writer.WriteLine("adapter.SelectCommand =cm;"); writer.WriteLine("SqlParameter parm;"); writer.WriteLine(); writer.Write("cm = new SqlCommand(\"UPDATE " + tableName + "SET "); intLength = dt.Columns.Count - 1; foreach(DataColumn col in dt.Columns) { writer.Write(col.ColumnName + "=@" + col.ColumnName ); if ( col.Ordinal < intLength) { writer.Write(", "); } } writer.Write(" WHERE "); intLength = dt.PrimaryKey.Length; for(int i=0;i < intLength;i++) { writer.Write(dt.PrimaryKey[i].ColumnName + "@" + dt.PrimaryKey[i].ColumnName); if (i<intLength - 1) writer.Write(", "); } writer.WriteLine("\",cn);"); writer.WriteLine(); foreach(DataColumn col in dt.Columns) { WriteParameter(writer,col.ColumnName); } writer.WriteLine(); writer.WriteLine("adapter.SelectCommand = cm;"); writer.WriteLine(); writer.Write("cm = new SqlCommand(\"UPDATE " + tableName + "SET "); intLength = dt.Columns.Count -1; foreach(DataColumn col in dt.Columns) { writer.Write(col.ColumnName + " = @" + col.ColumnName ); if ( col.Ordinal < intLength) { writer.Write(", "); } } writer.Write(" WHERE "); intLength = dt.PrimaryKey.Length; for(int i=0;i < intLength;i++) { writer.Write(dt.PrimaryKey[i].ColumnName + " = @" + if (i < intLength - 1) writer.Write(", "); } writer.WriteLine("\",cn);"); writer.WriteLine(); foreach(DataColumn col in dt.Columns) { WriteParameter(writer,col.ColumnName); } writer.WriteLine(); writer.WriteLine("adapter.UpdateCommand = cm;"); writer.WriteLine(); writer.Flush(); memstream.Position = 0; System.IO.StreamReader reader = new StreamReader(memstream, System.Text.Encoding.Unicode); string results = reader.ReadToEnd(); reader.Close(); writer.Close(); memstream.Close(); return(results); } private string GetBoundColumns(string tableName,string connectionString) { DataTable dt = BuilderLib.GetDataTableSchema(tableName,connectionString); StringBuilder sb = new StringBuilder(); foreach(DataColumn col in dt.Columns) { sb.Append ("<asp:BoundColumn HeaderText=\""); sb.Append(col.ColumnName + "\" "); sb.Append("DataField=\"" + col.ColumnName + "\" "); if(col.ReadOnly) { sb.Append("ReadOnly=\"true\""); } sb.Append(" />"); } return(sb.ToString()); } private void WritePKFilter(StreamWriter writer,DataColumn [] pk) { //Write out the primary keys and their values, //used to filteron. For instance // CustomerID='ALFKI' //Because we are writing this as dynamic code, //the dynamic code will look like: // dv.RowFilter="[CustomerID]='" + CustomerIDVal + "'" string [] filterConditions = new string[pk.Length] ; for(int i=0;i<= pk.Length -1;i++) { filterConditions[i] = "[" + pk[i].ColumnName + "] ='\" + " + pk[i].ColumnName + "Val + \"'"; } //Separate each filter condition with an AND clause writer.Write(string.Join(" AND ",filterConditions)); } private void WriteParameter(StreamWriter writer,string columnName) { System.Data.SqlClient.SqlParameter parm = new System.Data.SqlClient.SqlParameter(); writer.WriteLine("parm = new SqlParameter();"); writer.WriteLine("parm.ParameterName=\"@" + columnName + "\";"); writer.WriteLine("parm.SourceColumn = \"" + columnName + "\";"); writer.WriteLine("cm.Parameters.Add(parm);"); } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion } }