only for RuBoard |
Listing 12.1 contains all code necessary for BuilderLib.cs .
using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Xml; namespace pagebuilder { public class BuilderLib { public static System.Data.DataTable GetDataTable(string commandText, string connectionString) { //Retrieve a DataTable object based on the command string passed SqlConnection cn = new SqlConnection(connectionString); SqlDataAdapter adapter = new SqlDataAdapter(); //Remove semicolons to avoid possibility of multiple commands commandText = commandText.Replace(";", ""); adapter.SelectCommand = new SqlCommand(commandText, cn); adapter.SelectCommand.CommandType = CommandType.Text; DataTable dt = new DataTable(); adapter.Fill(dt); adapter.Dispose(); cn.Close(); cn.Dispose(); return (dt); } public static System.Xml.XmlNode GetNode(XmlNodeType nodeType, string nodeName, XmlNode parentNode) { XmlNode node = null; switch(nodeType) { case XmlNodeType.Element: node = parentNode.SelectSingleNode(nodeName); if(node==null) { node = parentNode.OwnerDocument.CreateElement (nodeName); parentNode.AppendChild(node); } break; case XmlNodeType.Attribute: node = parentNode.Attributes[nodeName]; if(node==null) { node = parentNode.OwnerDocument.CreateAttribute (nodeName); parentNode.Attributes.SetNamedItem(node); } break; } return(node); } public static System.Xml.XmlNode GetNode(XmlNodeType nodeType, string nodeName, XmlNode parentNode, string nodeValue) { XmlAttribute newAttrib = null; XmlElement newElem = null; XmlNode returnNode = null; XmlDocument parentDoc = parentNode.OwnerDocument; switch(nodeType) { case XmlNodeType.Element: //SelectSingleNode returns an XmlNode: the XmlNode //must be cast as an XmlElement. newElem = (XmlElement)parentNode.SelectSingleNode (nodeName); if(newElem==null) { newElem = parentDoc.CreateElement(nodeName); } //remove any existing child nodes //of the current element newElem.RemoveAll(); XmlText textNode = parentDoc.CreateTextNode (nodeValue); textNode.Value = nodeValue; newElem.AppendChild(textNode); returnNode = newElem; break; case XmlNodeType.Attribute: //SelectSingleNode returns an XmlNode: the XmlNode //must be cast as an XmlElement. newAttrib = parentDoc.CreateAttribute(nodeName); newAttrib.Value = nodeValue; returnNode = parentNode.Attributes.Set NamedItem(newAttrib); break; } return(returnNode); } } }
Listing 12.2 contains all the code necessary for the CreateForm.aspx web form. This page is set as the startup page in the web application.
<%@ Import Namespace="pagebuilder"%> <%@ Import Namespace="System.Xml"%> <%@ Import Namespace="System.Data.SqlClient"%> <%@ Import Namespace="System.Data"%> <HTML> <HEAD> <title>Page Builder Sample Application</title> <script language="C#" runat="server"> private void Button1_Click(object sender, System.EventArgs e) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append ("Data Source=" + dataSource.Text + ";"); sb.Append ("Initial Catalog=" + dataBase.Text + ";"); sb.Append ("User ID=" + userID.Text + ";"); sb.Append ("Password=" + password.Text + ";"); //Try to connect to the user's database SqlConnection cn = new SqlConnection(sb.ToString()); Boolean hasError = false; try { cn.Open(); } catch(System.Exception errOops) { messageUtility.InnerText = "Connection error:<br>" + Server.HtmlEncode(errOops.ToString()); hasError = true; } finally { if (!hasError) cn.Close(); cn.Dispose(); } if (!hasError) { //The database connection is valid. //Save the settings from the form. XmlDocument doc = new XmlDocument(); XmlElement root = (XmlElement)doc.AppendChild(doc.CreateElement ("root")); BuilderLib.GetNode(System.Xml.XmlNodeType.Attribute, "formName", root, formName.Text); BuilderLib.GetNode(System.Xml.XmlNodeType.Attribute, "emailAddress", root, email.Text); BuilderLib.GetNode(System.Xml.XmlNodeType.Attribute, "connectionString", root, sb.ToString()); Session["doc"] = doc; Response.Redirect("ChooseTable.aspx"); } } </script> </HEAD> <body> <form id="Form1" method="post" runat="server"> <span id="messageUtility" runat="server" class="errorClass"> <table border="1"> <tr> <td>Form Name:</td> <td> <asp:TextBox id="formName" runat="server" Width="421px" Height="25px"> </asp:TextBox> </td> </tr> <tr> <td>Data Source:</td> <td> <asp:TextBox id="dataSource" runat="server" Width="421px" Height="25px">localhost </asp:TextBox> </td> </tr> <tr> <td>Initial Catalog:</td> <td> <asp:TextBox id="dataBase" runat="server" Width="421px" Height="25px">Northwind </asp:TextBox> </td> </tr> <tr> <td>User ID:</td> <td> <asp:TextBox id="userID" runat="server" Width="421px" Height="25px">sa </asp:TextBox> </td> </tr> <tr> <td>Password:</td> <td> <asp:TextBox id="password" runat="server" Width="421px" Height="25px"> </asp:TextBox> </td> </tr> <TR> <TD>Email Address:</TD> <TD> <asp:TextBox id="email" runat="server" Width="419px"> </asp:TextBox> </TD> </TR> <tr> <td></td> <td> <asp:Button id="Button1" runat="server" Width="112px" Height="31px" Text="Next >>" OnClick="Button1_Click"> </asp:Button> </td> </tr> </table> </form> </SPAN> </body> </HTML>
Listing 12.3 contains all code necessary for the ChooseTable.aspx web form.
<%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <%@ Import Namespace="System.Xml"%> <%@ Import Namespace="pagebuilder"%> <script language="C#" runat="server" > private void Page_Load(object sender, System.EventArgs e) { if(!IsPostBack) { XmlDocument doc = (XmlDocument)Session["doc"]; XmlElement root = doc.DocumentElement; string connection = root.Attributes["connectionString"].Value; DataView dv = GetTableNames(connection); DropDownList1.DataSource = dv; DropDownList1.DataTextField = "TABLE_NAME"; DropDownList1.DataValueField = "TABLE_NAME"; DropDownList1.DataBind(); } } private System.Data.DataView GetTableNames(string connectionString) { DataTable table = BuilderLib.GetDataTable("sp_tables", connectionString); DataView dv = new DataView(table); dv.RowFilter = "TABLE_TYPE IN ('TABLE','VIEW')"; return(dv); } private void Button1_Click(object sender, System.EventArgs e) { //Get the values from the page XmlDocument doc = (XmlDocument)Session["doc"]; XmlNode node = doc.DocumentElement; BuilderLib.GetNode(System.Xml.XmlNodeType.Attribute, "tableName", node, this.DropDownList1.SelectedItem.Value); Session["doc"] = doc; Response.Redirect("CreatePage.aspx"); } </script> <HTML> <HEAD> <title>Page Builder Sample Application - Choose Table</title> <LINK rel="stylesheet" type="text/css" href="PageBuilder.css"> </HEAD> <body > <form id="Form1" method="post" runat="server"> <table border="1"> <tr> <td>Table Source:</td> <td> <asp:DropDownList id="DropDownList1" runat="server" Width="198px" Height="26px"/> </td> </tr> <tr> <td></td> <td> <asp:Button id="Button1" runat="server" Width="112px" Height="31px" Text="Next >>" OnClick="Button1_Click"/> </td> </tr> </table> </form> </body> </HTML>
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.
<%@ 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 .
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 } }
only for RuBoard |