У меня есть несколько таблиц sqlserver, каждая из которых представляет приложение. Мне нужно поддерживать (просматривать/редактировать/вставлять/удалять записи) все эти таблицы. Все эти таблицы приложений имеют столбец первичного ключа int с именем ID.
Чтобы выполнить эту задачу, я создал несколько таблиц конфигурации SqlServer, которые являются основой для создания страницы для обслуживания этих таблиц приложений.
dbo.ApplGenerator_Applications: эта таблица сопоставляет приложение с реальной таблицей SqlServer с дополнительными полями для сортировки данных в области просмотра веб-приложения.
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
)
Поскольку первичный ключ всегда имеет одно и то же имя и тип, мне не нужно добавлять эту информацию в таблицу конфигурации приложений.
ApplGenerator_Master: эта таблица является основой для: • Построения заголовка с возможностями поиска • Построения столбцов представления списка • Построения запроса для извлечения данных
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
)
Вместе эти две таблицы конфигурации являются основой для создания объектов списка (фильтр и данные) и заполнения списка данных.
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
}
}
Приложение почти готово, пейджер работает нормально Новая кнопка и фильтр (элементы управления в фильтре ListView) работают как положено.
Проблема заключается в том, что когда я нажимаю значок, чтобы удалить строку (элемент управления ImageButton в Data ListView ItemTemplate), ни код для удаления не запускается, ни список данных не перезагружается.
Я новичок в ASP.NET и наверняка делаю что-то (очень) неправильно. У меня нет идей о том, как решить эту проблему. Кто-нибудь может помочь?
Спасибо,
Марио Нуньес
