I have several sqlserver tables each representing an application. I need to mantain (view/edit/insert/delete records) all of these tables. All of these application tables have an int primary key column named ID.
In order to accomplish this task, I created some SqlServer configuration tables which are the base to build a page to maintain these applications tables.
dbo.ApplGenerator_Applications: This table maps the application to the real SqlServer table with extra fields to sort data in the view pane of the web application.
CREATE TABLE [dbo].[ApplGenerator_Applications](
[ApplicationName] [varchar](50) NOT NULL,
[RealTable] [varchar](255) NOT NULL,
[OrderBy] [varchar](255) NOT NULL,
[Desc_OrderBy] [varchar](255) NOT NULL
)
As the primary key has always the same name and type, I don’t need to add that information in the applications configuration table.
ApplGenerator_Master: This table is the base to: • Build an header with search capabilities • Build the list view columns • Build the query to retrive data
CREATE TABLE [dbo].[ApplGenerator_Master](
[ApplicationName] [varchar](50) NOT NULL,
[FieldId] [varchar](50) NOT NULL,
[FieldTitle] [varchar](50) NULL,
[FieldName] [varchar](255) NULL,
[FieldAlign] [varchar](50) NULL,
[FieldWidth] [varchar](50) NULL,
[Position] [int] NULL
)
In conjunction, these two configuration tables are the base to build the listview objects (filter and data) and populate the data listview.
Master.aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Master.aspx.cs" Inherits="ApplicationGenerator.Master" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<link href="/Styles/StyleSheet.css" rel="stylesheet" type="text/css" />
<script src="/Scripts/Script.js" type="text/javascript"></script>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<ajax:ToolkitScriptManager ID="MasterScriptManager" runat="server"></ajax:ToolkitScriptManager>
<script type="text/javascript">
document.body.onload = function () { resizeContent('pageContent'); }
document.body.onresize = function () { resizeContent('pageContent'); }
</script>
<asp:UpdatePanel ID="myUpdatePanel" runat="server" UpdateMode="Conditional" ChildrenAsTriggers="true">
<ContentTemplate>
<asp:DataPager ID="Master_Data_DataPager" runat="server"
PagedControlID="Master_Data_ListView"
PageSize="50">
<Fields>
<asp:NextPreviousPagerField ButtonType="Image"
FirstPageImageUrl="/Images/DataPager/first.png"
NextPageImageUrl="/Images/DataPager/next.png"
PreviousPageImageUrl="/Images/DataPager/previous.png"
LastPageImageUrl="/Images/DataPager/last.png"
ShowFirstPageButton="True"
ShowNextPageButton="False" />
<asp:NumericPagerField />
<asp:NextPreviousPagerField ButtonType="Image"
FirstPageImageUrl="/Images/DataPager/first.png"
NextPageImageUrl="/Images/DataPager/next.png"
PreviousPageImageUrl="/Images/DataPager/previous.png"
LastPageImageUrl="/Images/DataPager/last.png"
ShowLastPageButton="True"
ShowPreviousPageButton="False" />
</Fields>
</asp:DataPager>
<asp:ListView ID="Master_Filter_ListView" runat="server">
</asp:ListView>
<div id="pageContent" style="overflow:auto;">
<asp:ListView ID="Master_Data_ListView" runat="server"
DataKeyNames="ID" DataSourceID="Master_Data_SqlDataSource"
OnPagePropertiesChanging="Master_Data_ListView_PagePropertiesChanging">
</asp:ListView>
<asp:SqlDataSource ID="Master_Data_SqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
SelectCommand="dbo.ApplGenerator_Master_Data_GetList" SelectCommandType="StoredProcedure"
DeleteCommand="dbo.ApplGenerator_Master_DeleteRecord" DeleteCommandType="StoredProcedure"
OnSelecting="ApplicationUsersSqlDataSource_Selecting"
OnDeleting="ApplicationUsersSqlDataSource_Deleting">
<DeleteParameters>
<asp:Parameter Direction="Input" Name="ID" />
</DeleteParameters>
</asp:SqlDataSource>
</div>
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>
Master.aspx.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
namespace ApplicationGenerator
{
public partial class Master : System.Web.UI.Page
{
private static string sConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
private static string sApplication = "";
private static DataTable dtApplication = null;
private static DataTable dtFilter = null;
public struct ExternalReferencesStruct
{
public ListView DataListView;
}
public class myClass
{
private ExternalReferencesStruct classObjs;
public myClass(ExternalReferencesStruct paramObjs)
{
classObjs = paramObjs;
}
}
protected void Page_Init(object sender, EventArgs e)
{
sApplication = Request.QueryString["application"];
dtApplication = ApplicationGenerator_Application_Get();
dtFilter = ApplicationGenerator_Master_Filter_GetList();
Build_MasterFilterListView();
Build_MasterDataListView();
Master_Filter_ListView.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
//if (!this.Page.IsPostBack)
//{
Master_Data_ListView.DataBind();
//}
string scriptString = "<script LANGUAGE='javascript'>resizeContent('pageContent');</script>";
ScriptManager.RegisterStartupScript(this, this.GetType(), "myScript_Load", scriptString, false);
}
#region Master_Filtro_ListView
public void Build_MasterFilterListView()
{
ExternalReferencesStruct ExternalObjs = new ExternalReferencesStruct();
ExternalObjs.DataListView = Master_Data_ListView;
Master_Filter_ListView.LayoutTemplate = new MasterFilterLayoutTemplate(ExternalObjs);
Master_Filter_ListView.ItemTemplate = new MasterFilterItemTemplate();
Master_Filter_ListView.EmptyDataTemplate = new MasterFilterLayoutTemplate(ExternalObjs);
}
public class MasterFilterLayoutTemplate : myClass, ITemplate
{
ExternalReferencesStruct ExternalReferences = new ExternalReferencesStruct();
public MasterFilterLayoutTemplate(ExternalReferencesStruct paramExternalReferences)
: base(paramExternalReferences)
{
ExternalReferences = paramExternalReferences;
}
protected void ApplyFilter_Click(object sender, ImageClickEventArgs e)
{
ExternalReferences.DataListView.DataBind();
}
public void InstantiateIn(System.Web.UI.Control container)
{
HtmlTable myTable = new HtmlTable();
myTable.Width = "100%";
myTable.Border = 0;
myTable.CellPadding = 0;
myTable.CellSpacing = 0;
myTable.Style.Add("table-layout", "fixed");
HtmlTableRow row = null;
HtmlTableRow rowF = null;
HtmlTableCell cell = null;
TextBox tbFiltro = null;
row = new HtmlTableRow();
rowF = new HtmlTableRow();
row.ID = "Row_Names";
rowF.ID = "Row_Filters";
ImageButton ib = null;
//New Record
cell = new HtmlTableCell();
cell.Width = "20px";
cell.Align = "center";
ib = new ImageButton();
ib.ID = "NewRecImageButton";
ib.ImageUrl = "/Images/Applications/New.png";
ib.ToolTip = "New Record";
ib.OnClientClick = "javascript:ViewEditDetail('" + sApplication + "', '-1');return false;";
cell.Controls.Add(ib);
row.Cells.Add(cell);
//Filter
cell = new HtmlTableCell();
cell.Width = "20px";
cell.Align = "center";
ib = new ImageButton();
ib.ID = "FilterImageButton";
ib.ImageUrl = "/Images/Applications/Filter.png";
ib.ToolTip = "Apply Filtro";
ib.Click += new ImageClickEventHandler(ApplyFilter_Click);
cell.Controls.Add(ib);
rowF.Cells.Add(cell);
//Field Names
foreach (DataRow dtrow in dtFilter.Rows)
{
//Header - Field Titles
cell = new HtmlTableCell();
cell.Width = dtrow["FieldWidth"].ToString();
cell.Align = "center";
cell.Style.Add("font-weight", "bold");
cell.Controls.Add(new LiteralControl(dtrow["FieldTitle"].ToString()));
row.Cells.Add(cell);
//Header - Filter TextBoxes
cell = new HtmlTableCell();
cell.Width = dtrow["FieldWidth"].ToString();
tbFiltro = new TextBox();
tbFiltro.ID = dtrow["FieldId"].ToString();
tbFiltro.Width = new Unit("99%");
cell.Controls.Add(tbFiltro);
rowF.Cells.Add(cell);
}
myTable.Rows.Add(row);
myTable.Rows.Add(rowF);
//Container para Items
row = new HtmlTableRow();
row.ID = "itemPlaceholder";
myTable.Rows.Add(row);
container.Controls.Add(myTable);
}
}
public class MasterFilterItemTemplate : ITemplate
{
public void InstantiateIn(System.Web.UI.Control container)
{
}
}
#endregion
#region Master_Data_ListView
public void Build_MasterDataListView()
{
ExternalReferencesStruct ExternalObjs = new ExternalReferencesStruct();
ExternalObjs.DataListView = Master_Data_ListView;
Master_Data_ListView.LayoutTemplate = new MasterDataLayoutTemplate(ExternalObjs);
Master_Data_ListView.ItemTemplate = new MasterDataItemTemplate(ExternalObjs);
Master_Data_ListView.EmptyDataTemplate = new MasterDataLayoutTemplate(ExternalObjs);
}
public class MasterDataLayoutTemplate : myClass, ITemplate
{
ExternalReferencesStruct ExternalReferences = new ExternalReferencesStruct();
public MasterDataLayoutTemplate(ExternalReferencesStruct paramExternalReferences)
: base(paramExternalReferences)
{
ExternalReferences = paramExternalReferences;
}
public void InstantiateIn(System.Web.UI.Control container)
{
HtmlTable myTable = new HtmlTable();
myTable.Width = "100%";
myTable.Border = 0;
myTable.CellPadding = 0;
myTable.CellSpacing = 0;
myTable.Style.Add("table-layout", "fixed");
HtmlTableRow row = null;
HtmlTableCell cell = null;
row = new HtmlTableRow();
//Button Delete
cell = new HtmlTableCell();
cell.Width = "20px";
cell.Align = "center";
row.Cells.Add(cell);
//Field Names
foreach (DataRow dtrow in dtFilter.Rows)
{
//Header - Field Names
cell = new HtmlTableCell();
cell.Width = dtrow["FieldWidth"].ToString();
row.Cells.Add(cell);
}
myTable.Rows.Add(row);
//Item Container
row = new HtmlTableRow();
row.ID = "itemPlaceholder";
myTable.Rows.Add(row);
container.Controls.Add(myTable);
}
}
public class MasterDataItemTemplate : myClass, ITemplate
{
ExternalReferencesStruct ExternalReferences = new ExternalReferencesStruct();
public MasterDataItemTemplate(ExternalReferencesStruct paramExternalReferences)
: base(paramExternalReferences)
{
ExternalReferences = paramExternalReferences;
}
public void InstantiateIn(System.Web.UI.Control container)
{
HtmlTableRow row = new HtmlTableRow();
row.DataBinding += new EventHandler(row_DataBinding);
DataRowView dataRowView = ((ListViewDataItem)container).DataItem as DataRowView;
if (dataRowView != null)
{
string sPK = dataRowView[0].ToString();
row.Attributes.Add("onclick", "ViewEditDetail('" + sApplication + "', '" + sPK + "');");
row.Attributes.Add("onmouseout", "MouseOut(this);");
row.Attributes.Add("onmouseover", "MouseOver(this);");
row.Attributes.Add("title", "View/Edit Details");
row.Style.Add("cursor", "pointer");
}
container.Controls.Add(row);
}
protected void row_DataBinding(object sender, EventArgs e)
{
HtmlTableRow row = (HtmlTableRow)sender;
DataRowView dataRowView = ((ListViewDataItem)row.NamingContainer).DataItem as DataRowView;
string sPK = dataRowView[0].ToString();
HtmlTableCell cell = null;
ImageButton ib = null;
//Button Delete
cell = new HtmlTableCell();
cell.Width = "20px";
cell.Align = "center";
ib = new ImageButton();
ib.ID = "DelImageButton";
ib.CommandName = "Delete";
ib.ImageUrl = "/Images/Applications/Delete.png";
ib.ToolTip = "Delete Record";
ib.OnClientClick = "javascript:return myConfirm('Proceed with Record Elimination?');";
cell.Controls.Add(ib);
row.Controls.Add(cell);
int i = 1;
foreach (DataRow dtrow in dtFilter.Rows)
{
cell = new HtmlTableCell();
cell.Width = dtrow["FieldWidth"].ToString();
Literal MyLiteral = new Literal();
MyLiteral.Text = dataRowView[i++].ToString();
cell.Controls.Add(MyLiteral);
row.Controls.Add(cell);
}
}
}
#endregion
#region Data Functions
private static DataTable ApplicationGenerator_Application_Get()
{
string sStrSP = "";
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet ds = null;
sStrSP = "dbo.ApplGenerator_Application_Get";
conn = new SqlConnection(sConnectionString);
cmd = new SqlCommand();
ds = new DataSet();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sStrSP;
cmd.Parameters.Add(new SqlParameter("@ApplicationName", sApplication));
conn.Open();
da = new SqlDataAdapter(cmd);
da.Fill(ds);
DataTable dt = ds.Tables[0];
cmd.Dispose();
da.Dispose();
ds.Dispose();
conn.Close();
return dt;
}
private static DataTable ApplicationGenerator_Master_Filter_GetList()
{
string sStrSP = "";
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet ds = null;
sStrSP = "dbo.ApplGenerator_Master_Filter_GetList";
conn = new SqlConnection(sConnectionString);
cmd = new SqlCommand();
ds = new DataSet();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sStrSP;
cmd.Parameters.Add(new SqlParameter("@ApplicationName", sApplication));
conn.Open();
da = new SqlDataAdapter(cmd);
da.Fill(ds);
DataTable dt = ds.Tables[0];
cmd.Dispose();
da.Dispose();
ds.Dispose();
conn.Close();
return dt;
}
private static DataTable ApplicationGenerator_Master_Data_GetList(ListView FilterListView)
{
DataTable dtWhere = new DataTable();
dtWhere.Clear();
dtWhere.Columns.Add("FieldId");
dtWhere.Columns.Add("FieldText");
string sFilterId = "";
string sFilterText = "";
TextBox tbFilter = null;
foreach (DataRow dtrow in dtFilter.Rows)
{
sFilterId = dtrow["FieldId"].ToString();
tbFilter = (TextBox)FilterListView.Controls[0].FindControl(sFilterId);
if (tbFilter != null)
{
sFilterText = tbFilter.Text;
if (sFilterText != "")
{
DataRow dtWhereRow = dtWhere.NewRow();
dtWhereRow["FieldId"] = sFilterId;
dtWhereRow["FieldText"] = sFilterText;
dtWhere.Rows.Add(dtWhereRow);
}
}
}
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(sConnectionString);
SqlCommand cmd = null;
conn.Open();
try
{
string sStrSP = "dbo.ApplGenerator_Master_Data_GetList";
cmd = new SqlCommand(sStrSP, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ApplicationName", sApplication));
cmd.Parameters.Add(new SqlParameter("@dtWhere", dtWhere));
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
}
finally
{
cmd.Dispose();
if (conn != null)
conn.Close();
}
return dt;
}
#endregion
#region Master_Data_ListView
protected virtual void Master_Data_ListView_PagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)
{
string scriptString = "";
Master_Data_DataPager.SetPageProperties(e.StartRowIndex, e.MaximumRows, false);
Master_Data_ListView.DataBind();
scriptString = "<script LANGUAGE='javascript'>resizeContent('pageContent');</script>";
ScriptManager.RegisterStartupScript(this, this.GetType(), "myScript_PageChanging", scriptString, false);
}
#endregion
#region Master_Data_SqlDataSource
protected void ApplicationUsersSqlDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
DataTable dtWhere = new DataTable();
dtWhere.Clear();
dtWhere.Columns.Add("FieldId");
dtWhere.Columns.Add("FieldText");
string sFilterId = "";
string sFilterText = "";
TextBox tbFilter = null;
foreach (DataRow dtrow in dtFilter.Rows)
{
sFilterId = dtrow["FieldId"].ToString();
tbFilter = (TextBox)Master_Filter_ListView.Controls[0].FindControl(sFilterId);
if (tbFilter != null)
{
sFilterText = tbFilter.Text;
if (sFilterText != "")
{
DataRow dtWhereRow = dtWhere.NewRow();
dtWhereRow["FieldId"] = sFilterId;
dtWhereRow["FieldText"] = sFilterText;
dtWhere.Rows.Add(dtWhereRow);
}
}
}
SqlParameter Param_Aplicacao = new SqlParameter("@ApplicationName", sApplication)
{
Direction = ParameterDirection.Input
};
SqlParameter Param_dtwhere = new SqlParameter("@dtWhere", dtWhere)
{
Direction = ParameterDirection.Input
};
e.Command.Parameters.Add(Param_Aplicacao);
e.Command.Parameters.Add(Param_dtwhere);
}
protected void ApplicationUsersSqlDataSource_Deleting(object sender, SqlDataSourceCommandEventArgs e)
{
SqlParameter Param_Aplicacao = new SqlParameter("@ApplicationName", sApplication)
{
Direction = ParameterDirection.Input
};
e.Command.Parameters.Add(Param_Aplicacao);
}
#endregion
}
}
The application is almost ready, the pager works fine The new and filter button (controls in the filter ListView) works as expected.
The problem is when I click the icon to delete a row (ImageButton Control in data ListView ItemTemplate), neither the code to delete runs, neither the data listview reloads.
I'm new to ASP.NET and for sure I'm doing somethig (very) wrong. I'm out of ideas on how to solve this issue. Can someone help?
Thanks,
Mário Nunes
