Dynamic Menu type Control Panel from DataBase
Creating a menu dynamically from a database, with a style type control panel
Introduction
This article concerns the development of a style menu control panel, whose structure is handled from a SQL server database. This menu can have sub-levels you want, whenever you specify in the database. Coding is in C # and the Repeater control is used.
Using the code
Database.
The Menu table can create a tree of menu items, where an item may be son of other items and also the father of other items. Important to remember that the database user used for the connection.
The Store Procedures.
Recall that the structure of the table menu, allows an item is a child of another item, and also the father of other items.
This stored procedure extracts the group menus. In the case of the primary parent, sp receives as a parameter a -1, for others, the sp will receive the ID of the group.
SQL | Snippet
ALTER PROCEDURE [dbo].[PA_OBTENER_MENU_X_ID] ---1
@ID int
AS
BEGIN
SET NOCOUNT ON;
if @ID <> -1
BEGIN
SELECT
M.ID,
M.ID_PADRE,
M.TITULO,
M.URL_IMAGEN,
M.URL_PAGINA,
M.ESTADO
FROM dbo.MENU M
WHERE M.[ID] = @ID AND ESTADO = 1
END
ELSE
BEGIN
SELECT
M.ID,
M.ID_PADRE,
M.TITULO,
M.URL_IMAGEN,
M.URL_PAGINA,
M.ESTADO
FROM dbo.MENU M
WHERE M.[ID_PADRE] IS NULL AND ESTADO = 1
ORDER BY TITULO ASC
END
END
This stored procedure extracts the items on each menu group, the sp takes the ID of the parent or group.
SQL | Snippet
ALTER PROCEDURE [dbo].[PA_OBTENER_ITEM_MENU_X_ID_PADRE]
@ID int
AS
BEGIN
SET NOCOUNT ON;
SELECT
M.ID,
M.ID_PADRE,
M.TITULO,
M.URL_IMAGEN,
M.URL_PAGINA,
M.ESTADO
FROM dbo.MENU M
WHERE M.[ID_PADRE] = @ID AND ESTADO = 1
END
Structure of the aspx page.
Css
This stylesheet design the appearance of the table containing the list and buttons.
HTML | Snippet
<style>
.btn {
background: #dfe3e6;
background-image: -webkit-linear-gradient(top, #dfe3e6, #c3c6c7);
background-image: -moz-linear-gradient(top, #dfe3e6, #c3c6c7);
background-image: -ms-linear-gradient(top, #dfe3e6, #c3c6c7);
background-image: -o-linear-gradient(top, #dfe3e6, #c3c6c7);
background-image: linear-gradient(to bottom, #dfe3e6, #c3c6c7);
-webkit-border-radius: 12;
-moz-border-radius: 12;
border-radius: 12px;
-webkit-box-shadow: 1px 1px 3px #666666;
-moz-box-shadow: 1px 1px 3px #666666;
box-shadow: 0px 1px 3px #666666;
font-family: Arial;
color: #050505;
font-size: 11px;
border: solid #999999 1px;
text-decoration: none;
width: 95px;
height: 70px;
display: block;
text-align: center;
margin: 5% auto;
}
.btn:hover {
background: #e0e2e3;
background-image: -webkit-linear-gradient(top, #e0e2e3, #eaedee);
background-image: -moz-linear-gradient(top, #e0e2e3, #eaedee);
background-image: -ms-linear-gradient(top, #e0e2e3, #eaedee);
background-image: -o-linear-gradient(top, #e0e2e3, #eaedee);
background-image: linear-gradient(to bottom, #e0e2e3, #eaedee);
text-decoration: none;
}
/******************************************************************************************/
.TableUnderline {
border-collapse: collapse;
border-spacing: 0;
width: 100%;
height: 100%;
margin: 0px;
padding: 0px;
}
.TableUnderline td {
vertical-align: middle;
border: 1px solid #000000;
border-width: 0px 0px 0px 0px;
text-align: left;
padding: 7px;
font-size: 10px;
font-family: Arial;
font-weight: normal;
color: #000000;
}
.TableUnderline tr:first-child th {
border-bottom: 1px solid #000000;
text-align: left;
border-width: 0px 0px 1px 1px;
font-size: 16px;
font-family: Arial;
font-weight: bold;
color: #033872;
}
/********************************************************************************************/
.menu {
max-width: 100%;
margin: 0 auto; /* Centers The Menu */
}
.menu ul {
margin: 0; /* removes the space added by default */
padding: 0; /* removes the space added by default */
padding: 0 20px;
list-style: none;
}
/* ******** LINKS/MENU ********* */
.menu ul li {
width: 110px;
height: 85px;
margin: 0px 5px 5px 0px;
position: relative;
text-align: center;
overflow: hidden;
-webkit-border-radius: 12px;
-moz-border-radius: 12px;
border-radius: 12px;
-webkit-box-shadow: 0 1px 2px #999;
-moz-box-shadow: 0 1px 2px #999;
box-shadow: 0 1px 2px #999;
/* background color */
background: #FFF;
background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#FFF), to(#EEE));
background: -webkit-linear-gradient(top, #FFF, #EEE);
background: -moz-linear-gradient(top, #FFF, #EEE);
background: -ms-linear-gradient(top, #FFF, #EEE);
background: -o-linear-gradient(top, #FFF, #EEE);
display: inline-block;
}
</style>
Aspx | Snippet
Menu structure
The structure comprises two repeaters together. The first sets the header menu group and the second sets the items in each group.
<body>
<form id="form1" runat="server">
<div>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<div style="width: 100%; margin: 10px">
<table style="width: 98%" class="TableUnderline">
<tbody>
<tr>
<th><%# Eval("TITULO")%></th>
<asp:Label ID="lblDocId" runat="server"
Text='<%# Eval("ID") %>' Visible="false"></asp:Label>
</tr>
<tr>
<td>
<div>
<nav class="menu">
<ul>
<asp:Repeater ID="Repeater2" runat="server">
<ItemTemplate>
<li>
<button type="button"
class="btn"
onclick="window.location.href='<%#Eval("URL_PAGINA") %>'">
<img src='http://www.codeproject.com/img/<%#Eval("URL_IMAGEN") %>'
alt='<%# Eval("TITULO")%>' />
<br>
<%# Eval("TITULO")%>
</button>
</li>
</ItemTemplate>
</asp:Repeater>
</ul>
</nav>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</ItemTemplate>
</asp:Repeater>
</div>
<div id="BackMenu" runat="server">
<table style="width: 100%">
<tr>
<td style="text-align: left">
<a href="javascript:history.go(-1)" title="Retroceder">
<img src="/img/back3.png" style="width:auto; height:auto;vertical-align: middle;"/>
</a>
</td>
</tr>
</table>
</div>
</form>
</body>
Behind Code.
The page_load function handles to define the URL of the application execution dynamically to complete the URL of the button icons. Also calls the functions that create groups and menu items.
C# | Snippet
protected void Page_Load(object sender, EventArgs e)
{
PathImage = Request.Url.Host + ":" + Request.Url.Port + HttpContext.Current.Request.ApplicationPath + "/";
Cargar_Menu();
Cargar_Item_Menu();
}
This function loads the first repeater, groups or subgroups of one-level menus
C# | Snippet
public void Cargar_Menu()
{
Repeater1.DataSource = null;
Repeater1.DataBind();
if (Request.QueryString["m"] == null)
{
var menu = Menu.ObtenerMenu_X_Id_Padre(-1);
if (menu.Count > 0)
{
Repeater1.DataSource = menu;
Repeater1.DataBind();
BackMenu.Visible = false;
}
}
else
{
int subMenu = Convert.ToInt32(Request.QueryString["m"].ToString());
var menu = Menu.ObtenerMenu_X_Id_Padre(subMenu);
if (menu.Count > 0)
{
Repeater1.DataSource = menu;
Repeater1.DataBind();
BackMenu.Visible = true;
}
}
}
This function, load the second repeater, the items on each menu group
C# | Snippet
public void Cargar_Item_Menu()
{
for (int i = 0; i <= Repeater1.Items.Count - 1; i++)
{
Label id = (Label)Repeater1.Items[i].FindControl("lblDocId");
Repeater rptItem = (Repeater)Repeater1.Items[i].FindControl("Repeater2");
var Lista = Menu.ObtenerItemMenu_X_Id_Padre(Int32.Parse(id.Text.ToString()));
rptItem.DataSource = Lista;
rptItem.DataBind();
}
}
Database Access
This section consists of three classes, the first defines the connection string of the database and the second and third, extract the structure menu defined in the database.
The configuration of the Connection string is in the webconfig.It uses a user must first be configured for the demo or example to work, or you configure it to your liking.
C# | Snippet
public static class BaseDatos
{
public static string StringDeConexion = ConfigurationManager.ConnectionStrings["BD_MENU"].ToString();
}
public class Item_Menu
{
public int ID { get; set; }
public int ID_PADRE { get; set; }
public string TITULO { get; set; }
public string URL_IMAGEN { get; set; }
public string URL_PAGINA { get; set; }
public bool ESTADO { get; set; }
}
public class Menu
{
private static string StringDeConexion = BaseDatos.StringDeConexion;
private static Item_Menu LoadMenu(IDataReader reader)
{
Item_Menu Item = new Item_Menu();
Item.ID = reader["ID"] as Int32? ?? -1;
Item.ID_PADRE = reader["ID"] as Int32? ?? -1;
Item.TITULO = Convert.ToString(reader["TITULO"]);
Item.URL_IMAGEN = Convert.ToString(reader["URL_IMAGEN"]);
Item.URL_PAGINA = Convert.ToString(reader["URL_PAGINA"]);
Item.ESTADO = reader["ID"] as bool? ?? false;
return Item;
}
public static List<item_menu> ObtenerMenu_X_Id_Padre(Int32 Id)
{
try
{
List<item_menu> list = new List<item_menu>();
using (SqlConnection conn = new SqlConnection(StringDeConexion.ToString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PA_OBTENER_MENU_X_ID";
cmd.Parameters.AddWithValue("ID", Id);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
list.Add(LoadMenu(reader));
}
return list;
}
}
catch (Exception)
{
throw;
}
}
public static List<item_menu> ObtenerItemMenu_X_Id_Padre(Int32 Id)
{
try
{
List<item_menu> list = new List<item_menu>();
using (SqlConnection conn = new SqlConnection(StringDeConexion.ToString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PA_OBTENER_ITEM_MENU_X_ID_PADRE";
cmd.Parameters.AddWithValue("ID", Id);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
list.Add(LoadMenu(reader));
}
return list;
}
}
catch (Exception)
{
throw;
}
}
}
Points of Interest
Initially use table to create the menu, but change for the list ul, li, because it allows the menu has a responsive effect.
History
Created the menu, the following are security coding that includes users and roles.