Generating a dynamic GridView
I might be from my league here, but for the last couple of days I've tried to develop the ultimate dynamic gridview generation engine .. then I got stuck. :)
So why finally?
Well, I have 2 tables where I map tables as a list (item) in the application (e.g. types of people, types of addresses, types of customers, countries, countries, etc.).
Instead of creating a gridview for each and one of these tables, I want to create a mechanism that will generate each of these tables dynamically in a gridview with all CRUD functionality enabled. This means I will need both an EditItemTemplate and a FooterTemplate to work.
Here: Base "config" tables for this mechanism:
CREATE TABLE EXP_LIST_COLUMN_TYPE
(
COLUMN_TYPE_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT IDX_PK_COLUMN_TYPE_ID PRIMARY KEY CLUSTERED,
COLUMN_TYPE NVARCHAR(255) NOT NULL
)
GO
CREATE TABLE EXP_LIST_HEADER
(
LIST_HEADER_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT IDX_PK_LIST_HEADER_ID PRIMARY KEY CLUSTERED,
DISPLAY_NAME NVARCHAR(255) NOT NULL,
TABLE_NAME NVARCHAR(255) NOT NULL,
TABLE_PRIMARY_KEY NVARCHAR(255) NOT NULL,
ACTIVE INT NOT NULL
)
GO
CREATE TABLE EXP_LIST_DETAILS
(
LIST_DETAIL_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT IDX_PK_LIST_DETAIL_ID PRIMARY KEY CLUSTERED,
LIST_HEADER_ID INT NOT NULL CONSTRAINT FK_LIST_HEADER_ID FOREIGN KEY REFERENCES EXP_LIST_HEADER(LIST_HEADER_ID),
DISPLAY_COLUMN_NAME NVARCHAR(255) NOT NULL,
DISPLAY_COLUMN_TYPE_ID INT NOT NULL CONSTRAINT FK_COL_TYPE_ID FOREIGN KEY REFERENCES EXP_LIST_COLUMN_TYPE(COLUMN_TYPE_ID),
DISPLAY_COLUMN_LENGTH INT NOT NULL,
VISIBLE INT NOT NULL,
DDL_LIST_DETAIL_ID INT NULL CONSTRAINT DDL_LIST_DETAIL_ID FOREIGN KEY REFERENCES EXP_LIST_DETAILS(LIST_DETAIL_ID),
COLUMN_NAME NVARCHAR(255) NOT NULL
)
GO
INSERT INTO EXP_LIST_COLUMN_TYPE
(COLUMN_TYPE)
SELECT 'Date'
UNION ALL
SELECT 'TextBox'
UNION ALL
SELECT 'Label'
UNION ALL
SELECT 'CheckBox'
UNION ALL
SELECT 'DropDownList'
GO
INSERT INTO EXP_LIST_HEADER
(DISPLAY_NAME,TABLE_NAME,TABLE_PRIMARY_KEY,ACTIVE)
SELECT 'Person Types','PERSON_TYPE','PERSON_TYPE_ID',1
UNION ALL
SELECT 'Address Types','ADDRESS_TYPES','ADDRESS_TYPE_ID',1
UNION ALL
SELECT 'Countries','COUNTRY','COUNTRY_ID',1
UNION ALL
SELECT 'Counties','COUNTY','COUNTY_ID',1
GO
INSERT INTO EXP_LIST_DETAILS
(
LIST_HEADER_ID,
DISPLAY_COLUMN_NAME,
DISPLAY_COLUMN_TYPE_ID,
DISPLAY_COLUMN_LENGTH,
VISIBLE,
DDL_LIST_DETAIL_ID,
COLUMN_NAME
)
SELECT 1,'Person Type',2,255,1,NULL,'TYPENAME'
UNION ALL
SELECT 2,'Address Type',2,255,1,NULL,'ADDRESS_TYPE'
UNION ALL
SELECT 3,'Country Code',2,3,1,NULL,'CODE'
UNION ALL
SELECT 3,'Country Name',2,255,1,NULL,'NAME'
UNION ALL
SELECT 4,'County Code',2,3,1,NULL,'CODE'
UNION ALL
SELECT 4,'County Name',2,255,1,NULL,'NAME'
UNION ALL
SELECT 4,'Country',5,3,1,3,'COUNTRY_ID'
GO
And 2 stored procedures that will output the title and details, something like this:
--GET LIST HEADER
IF OBJECT_ID(N'USP_GET_LIST_HEADER','P') IS NOT NULL
DROP PROCEDURE USP_GET_LIST_HEADER
GO
CREATE PROCEDURE USP_GET_LIST_HEADER @LIST_HEADER_ID INT
AS
BEGIN
SELECT DISTINCT
H.LIST_HEADER_ID
, H.DISPLAY_NAME
, H.TABLE_NAME
, H.TABLE_PRIMARY_KEY
FROM EXP_LIST_HEADER H
JOIN EXP_LIST_DETAILS D ON D.LIST_HEADER_ID = H.LIST_HEADER_ID
WHERE
H.LIST_HEADER_ID = @LIST_HEADER_ID
AND H.ACTIVE = 1
END
GO
--GET LIST DETAILS
IF OBJECT_ID(N'USP_GET_LIST_DETAILS','P') IS NOT NULL
DROP PROCEDURE USP_GET_LIST_DETAILS
GO
CREATE PROCEDURE USP_GET_LIST_DETAILS @LIST_HEADER_ID INT
AS
BEGIN
SELECT
D.LIST_DETAIL_ID
, D.DISPLAY_COLUMN_NAME
, D.VISIBLE
, CT.COLUMN_TYPE
, D.DISPLAY_COLUMN_LENGTH
, DDLH.TABLE_NAME + '.' + DDLH.TABLE_PRIMARY_KEY AS DDL_VALUE
, DDLH.TABLE_NAME + '.' + DDLD.COLUMN_NAME AS DDL_TEXT
, DDLH.TABLE_NAME AS DDL_TABLE
, D.COLUMN_NAME
FROM EXP_LIST_HEADER H
JOIN EXP_LIST_DETAILS D ON D.LIST_HEADER_ID = H.LIST_HEADER_ID
JOIN EXP_LIST_COLUMN_TYPE CT ON CT.COLUMN_TYPE_ID = D.DISPLAY_COLUMN_TYPE_ID
LEFT JOIN EXP_LIST_DETAILS DDLD ON DDLD.LIST_DETAIL_ID = D.DDL_LIST_DETAIL_ID
LEFT JOIN EXP_LIST_HEADER DDLH ON DDLH.LIST_HEADER_ID = DDLD.LIST_HEADER_ID
WHERE
H.LIST_HEADER_ID = @LIST_HEADER_ID
AND H.ACTIVE = 1
END
GO
These 2 sp will provide me with the gridview structure as such:
EXEC USP_GET_LIST_HEADER 4
EXEC USP_GET_LIST_DETAILS 4
Considering I have a web form in which I have a dropdown from which the user selects the list they would like to manipulate and based on the user's selection, the gridview will be dynamically generated based on the underlying data:
<asp:DropDownList ID="ddlLists" runat="server" DataSourceID="dsLists"
DataTextField="LIST_NAME" DataValueField="LIST_HEADER_ID" Width="100%"
Font-Bold="true" AutoPostBack="true"
onselectedindexchanged="ddlLists_SelectedIndexChanged" >
</asp:DropDownList>
<asp:UpdateProgress runat="server" ID="updpgrDynamicList" AssociatedUpdatePanelID="updpanDynamicList">
<ProgressTemplate>
<img src="../Images/ajax-loader.gif" alt="Please wait..." />
</ProgressTemplate>
</asp:UpdateProgress>
<asp:UpdatePanel runat="server" ID="updpanDynamicList" UpdateMode="Conditional">
<ContentTemplate>
<asp:GridView ID="gvDynamicList" runat="server"
Visible="true" ShowFooter="true"
AllowPaging="True" PageSize="5" PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Center" PagerStyle-NextPageText="Next" PagerStyle-PrevPageText="Prev"
BorderWidth="0" GridLines="Both" CellPadding="1" CellSpacing="0"
Font-Name="Verdana" Font-Size="7pt" ShowHeaderWhenEmpty="true"
HeaderStyle-BackColor="#aaaadd" AlternatingItemStyle-BackColor="#eeeeee"
AutoGenerateColumns="false"
onpageindexchanging="gvDynamicList_PageIndexChanging"
onrowcancelingedit="gvDynamicList_RowCancelingEdit"
onrowcommand="gvDynamicList_RowCommand"
onrowdeleting="gvDynamicList_RowDeleting"
onrowediting="gvDynamicList_RowEditing"
onrowupdating="gvDynamicList_RowUpdating"
>
<EmptyDataRowStyle HorizontalAlign="Center" BackColor="Red" Font-Bold="true"/>
<FooterStyle BackColor="#ececff" />
<HeaderStyle BackColor="#30308f" Font-Bold="True" ForeColor="White" Font-Size="7pt" HorizontalAlign="Center"/>
<Columns>
</Columns>
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
<br />
<asp:Label ID="lblError" runat="server" Text="" ForeColor="Red" Font-Bold="true"></asp:Label>
<asp:SqlDataSource ID="dsLists" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"
SelectCommand="SELECT LIST_HEADER_ID, DISPLAY_NAME AS LIST_NAME FROM EXP_LIST_HEADER WHERE ACTIVE=1 UNION ALL SELECT -1,'' ORDER BY 1 ASC">
</asp:SqlDataSource>
Code:
protected void ddlLists_SelectedIndexChanged(object sender, EventArgs e)
{
int ListHeaderId;
Int32.TryParse(ddlLists.SelectedItem.Value.ToString(), out ListHeaderId);
if (ListHeaderId != 0)
{
try
{
PopulateDynamicGridView(ListHeaderId);
}
catch (Exception ex)
{
lblError.Text = "Error: " + ex.Message;
}
}
}
protected void PopulateDynamicGridView(int ListHeaderId)
{
gvDynamicList.Columns.Clear();
gvDynamicList.DataBind();
using (SqlConnection sqlConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString))
{
SqlCommand sqlGetListHeader = new SqlCommand(@"EXEC USP_GET_LIST_HEADER @LIST_HEADER_ID", sqlConn);
sqlGetListHeader.Parameters.Add("@LIST_HEADER_ID", SqlDbType.Int);
sqlGetListHeader.Parameters["@LIST_HEADER_ID"].Value = ListHeaderId;
SqlDataAdapter daListHeader = new SqlDataAdapter(sqlGetListHeader);
DataTable dtListHeader = new DataTable("LIST_HEADER");
SqlCommand sqlGetListDetails = new SqlCommand(@"EXEC USP_GET_LIST_DETAILS @LIST_HEADER_ID", sqlConn);
sqlGetListDetails.Parameters.Add("@LIST_HEADER_ID", SqlDbType.Int);
sqlGetListDetails.Parameters["@LIST_HEADER_ID"].Value = ListHeaderId;
SqlDataAdapter daListDetails = new SqlDataAdapter(sqlGetListDetails);
DataTable dtListDetails = new DataTable("LIST_DETAILS");
try
{
sqlConn.Open();
daListHeader.Fill(dtListHeader);
if (dtListHeader.Rows.Count == 1)
{
daListDetails.Fill(dtListDetails);
if (dtListDetails.Rows.Count >= 1)
{
string sqlDynamicCommand = "SELECT " + dtListHeader.Rows[0]["TABLE_PRIMARY_KEY"].ToString() + ", ";
//add the primary key of the underlying table as a bound field to the gv
//useful later in edit mode
BoundField bfGVPk = new BoundField();
bfGVPk.DataField = dtListHeader.Rows[0]["TABLE_PRIMARY_KEY"].ToString();
bfGVPk.ReadOnly = true;
bfGVPk.HeaderText = dtListHeader.Rows[0]["TABLE_PRIMARY_KEY"].ToString();
bfGVPk.Visible = false;
gvDynamicList.Columns.Add(bfGVPk);
foreach(DataRow drDetails in dtListDetails.Rows)
{
string DisplayColumnName = drDetails["DISPLAY_COLUMN_NAME"].ToString();
int Visible = Convert.ToInt32(drDetails["VISIBLE"].ToString());
string ColumnType = drDetails["COLUMN_TYPE"].ToString();
int ColumnLength = Convert.ToInt32(drDetails["DISPLAY_COLUMN_LENGTH"].ToString());
string DDLValue = drDetails["DDL_VALUE"].ToString();
string DDLText = drDetails["DDL_TEXT"].ToString();
string DDLTable = drDetails["DDL_TABLE"].ToString();
string ColumnName = drDetails["COLUMN_NAME"].ToString();
if (ColumnType != "DropDownList")
{
TemplateField tmplfield = new TemplateField();
tmplfield.HeaderTemplate = new GridViewTemplate(ListItemType.Header, DisplayColumnName, Visible);
tmplfield.ItemTemplate = new GridViewTemplate(ListItemType.Item, ColumnName,"Label");
tmplfield.EditItemTemplate = new GridViewTemplate(ListItemType.EditItem, DisplayColumnName, ColumnName, ColumnType, Visible, ColumnLength);
tmplfield.FooterTemplate = new GridViewTemplate(ListItemType.Footer, DisplayColumnName, ColumnName, ColumnType, Visible, ColumnLength);
gvDynamicList.Columns.Add(tmplfield);
}
sqlDynamicCommand += ColumnName + ",";
}
//add the edit column
TemplateField tmplfieldEdit = new TemplateField();
tmplfieldEdit.HeaderTemplate = new GridViewTemplate(ListItemType.Header, "Edit", 1);
tmplfieldEdit.ItemTemplate = new GridViewTemplate(ListItemType.Item, "Edit", "ImageButton");
tmplfieldEdit.EditItemTemplate = new GridViewTemplate(ListItemType.EditItem, "", "", "ImageButton", 1, 0);
tmplfieldEdit.FooterTemplate = new GridViewTemplate(ListItemType.Footer, "", "", "ImageButton", 1, 0);
gvDynamicList.Columns.Add(tmplfieldEdit);
//add the delete column
TemplateField tmplfieldDelete = new TemplateField();
tmplfieldDelete.HeaderTemplate = new GridViewTemplate(ListItemType.Header, "Delete", 1);
tmplfieldDelete.ItemTemplate = new GridViewTemplate(ListItemType.Item, "Delete", "ImageButton");
gvDynamicList.Columns.Add(tmplfieldDelete);
//removing the final comma
sqlDynamicCommand = sqlDynamicCommand.Substring(0, sqlDynamicCommand.Length - 1);
//adding the table
sqlDynamicCommand += " FROM " + dtListHeader.Rows[0]["TABLE_NAME"].ToString();
using (SqlConnection sqlConnDynamicDS = new SqlConnection(WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString))
{
SqlCommand sqlGetDynamicDataSource = new SqlCommand(sqlDynamicCommand, sqlConnDynamicDS);
SqlDataAdapter daDynamic = new SqlDataAdapter(sqlGetDynamicDataSource);
DataTable dtDynamic = new DataTable("DYNAMIC_DATA_SOURCE");
daDynamic.Fill(dtDynamic);
gvDynamicList.DataSource = dtDynamic;
gvDynamicList.DataBind();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
And my ITemplate implementation:
public class GridViewTemplate : System.Web.UI.Page, ITemplate
{
ListItemType _templateType;
string _columnName;
string _controlType;
int _visible;
int _columnLength;
string _displayColumnName;
//Header constructor
public GridViewTemplate(ListItemType type, string displaycolumnname,int visible)
{
_templateType = type;
_displayColumnName = displaycolumnname;
_visible = visible;
}
//Item constructor
public GridViewTemplate(ListItemType type, string columnname, string controlType)
{
_templateType = type;
_columnName = columnname;
_controlType = controlType;
}
//EditItem and Footer constructor
public GridViewTemplate(ListItemType type,string displaycolumnname, string colname, string controltype, int visible, int columnlength)
{
_templateType = type;
_columnName = colname;
_controlType = controltype;
_visible = visible;
_columnLength = columnlength;
_displayColumnName = displaycolumnname;
}
void ITemplate.InstantiateIn(System.Web.UI.Control container)
{
switch (_templateType)
{
case ListItemType.Header:
try
{
//Headers will always be labels
Label lblHeader = new Label();
lblHeader.Text = _displayColumnName;
if (_visible != 1)
{
lblHeader.Visible = false;
}
container.Controls.Add(lblHeader);
}
catch (Exception ex)
{
throw ex;
}
break;
case ListItemType.Item:
try
{
//Items can be only Labels or ImageButtons
if (_controlType == "Label")
{
Label lblItem = new Label();
lblItem.ID = "lbl" + _columnName;
lblItem.Text = String.Empty;
lblItem.DataBinding += new EventHandler(OnDataBinding);
container.Controls.Add(lblItem);
}
if (_controlType == "ImageButton" && _columnName == "Edit")
{
ImageButton imgbtnItem = new ImageButton();
imgbtnItem.ID = "img_btnEdit";
imgbtnItem.CommandName = "Edit";
imgbtnItem.ImageUrl = "~/Images/document_edit.png";
imgbtnItem.AlternateText = _columnName;
imgbtnItem.CausesValidation = false;
imgbtnItem.Click += new ImageClickEventHandler(imgbtnItem_Edit);
container.Controls.Add(imgbtnItem);
}
if (_controlType == "ImageButton" && _columnName == "Delete")
{
ImageButton imgbtnItem = new ImageButton();
imgbtnItem.ID = "btnDelete";
imgbtnItem.CommandName = "Delete";
imgbtnItem.ImageUrl = "~/Images/document_delete.png";
imgbtnItem.AlternateText = _columnName;
imgbtnItem.CausesValidation = false;
imgbtnItem.OnClientClick = "return confirm('Are you sure you want to delete this record?')";
container.Controls.Add(imgbtnItem);
}
}
catch (Exception ex)
{
throw ex;
}
break;
case ListItemType.EditItem:
try
{
if (_controlType == "TextBox")
{
TextBox tbEditItem = new TextBox();
tbEditItem.ID = "tbEdit" + _columnName;
tbEditItem.Text = String.Empty;
tbEditItem.DataBinding += new EventHandler(OnDataBinding);
tbEditItem.MaxLength = _columnLength;
}
if (_controlType == "Label")
{
//Labels will basically render the control in a ReadOnly state
Label lblEditItem = new Label();
lblEditItem.ID = "lblEditItem" + _columnName;
lblEditItem.Text = String.Empty;
lblEditItem.DataBinding += new EventHandler(OnDataBinding);
}
if (_controlType == "Date")
{
TextBox tbDateEditItem = new TextBox();
tbDateEditItem.ID = "tbDateEdit" + _columnName;
tbDateEditItem.Text = String.Empty;
tbDateEditItem.MaxLength = _columnLength;
tbDateEditItem.DataBinding += new EventHandler(OnDataBinding);
AjaxControlToolkit.CalendarExtender ajCe = new AjaxControlToolkit.CalendarExtender();
ajCe.ID = "ceEdit" + _columnName;
ajCe.Format = "dd.MM.yyyy";
ajCe.TargetControlID = tbDateEditItem.ID;
}
if (_controlType == "DropDownList")
{
//work in progress :)
}
//Here we will add 2 ImageButtons inside the EditItemTemplate
//One will be to Save the edited date
//The other one will close the edit mode of the gridview (Cancel)
if (_controlType == "ImageButton")
{
ImageButton imgbtnEditItemSave = new ImageButton();
imgbtnEditItemSave.ID = "img_btnSave";
imgbtnEditItemSave.CommandName = "Update";
imgbtnEditItemSave.ImageUrl = "~/Images/document_save.png";
imgbtnEditItemSave.AlternateText = "Save";
imgbtnEditItemSave.CausesValidation = true;
imgbtnEditItemSave.ValidationGroup = "SaveRow"; //in case a validation is defined on any other EditItems
ImageButton imgbtnEditItemEditClose = new ImageButton();
imgbtnEditItemEditClose.ID = "img_btnCancel";
imgbtnEditItemEditClose.CommandName = "Cancel";
imgbtnEditItemEditClose.ImageUrl = "~/Images/document_cancel.png";
imgbtnEditItemEditClose.AlternateText = "Cancel";
imgbtnEditItemEditClose.CausesValidation = false;
}
}
catch (Exception ex)
{
throw ex;
}
break;
case ListItemType.Footer:
if (_controlType == "TextBox")
{
TextBox tbNewItem = new TextBox();
tbNewItem.ID = "tbNew" + _columnName;
tbNewItem.Text = String.Empty;
tbNewItem.MaxLength = _columnLength;
}
if (_controlType == "ImageButton")
{
ImageButton imgbtnAddNewItem = new ImageButton();
imgbtnAddNewItem.ID = "img_btnInsert";
imgbtnAddNewItem.CommandName = "AddNew";
imgbtnAddNewItem.ImageUrl = "~/Images/document_insert.png";
imgbtnAddNewItem.AlternateText = "Insert";
imgbtnAddNewItem.CausesValidation = true;
imgbtnAddNewItem.ValidationGroup = "AddRow";
}
if (_controlType == "DropDownList")
{
//work in progress :)
}
if (_controlType == "CheckBox")
{
//work in progress :)
}
if (_controlType == "Date")
{
TextBox tbNewItem = new TextBox();
tbNewItem.ID = "tbNew" + _columnName;
tbNewItem.Text = String.Empty;
tbNewItem.MaxLength = _columnLength;
AjaxControlToolkit.CalendarExtender ajCe = new AjaxControlToolkit.CalendarExtender();
ajCe.ID = "ceEdit" + _columnName;
ajCe.Format = "dd.MM.yyyy";
ajCe.TargetControlID = tbNewItem.ID;
}
break;
}
}
protected void imgbtnItem_Edit(object sender, ImageClickEventArgs e)
{
}
private void OnDataBinding(object sender, EventArgs e)
{
object bound_value_obj = null;
Control ctrl = (Control)sender;
IDataItemContainer data_item_container = (IDataItemContainer)ctrl.NamingContainer;
bound_value_obj = DataBinder.Eval(data_item_container.DataItem, _columnName);
switch (_templateType)
{
case ListItemType.Item:
Label field_ltrl = (Label)sender;
field_ltrl.Text = bound_value_obj.ToString();
break;
case ListItemType.EditItem:
TextBox field_txtbox = (TextBox)sender;
field_txtbox.Text = bound_value_obj.ToString();
break;
}
}
}
When I run the form, the header and element templates look more than normal, BUT I have nothing in the footer. I should have had 2 textboxes ImageButton
here too, but I don't:
Also, when I click the Change Image button on any of the lines, this is what I get:
From what I noticed, in edit mode it never fires an event OnDataBinding
when ListItemType
- EditItem
.
I know there is a lot of code to digest here, but if I can get this to work, I will post the entire code so everyone can benefit from it if they find it interesting.
Any ideas would be more than welcome!
+3
source to share