i'm building a web form that show Database's item(Tables, Rows, FK,...)
I have a CheckBoxList of Tables (chkListTable) which will show a new CheckBoxList of Rows (chkListRow) everytime I SelectedIndexChanged from chkListTable. The problem is i can show the items from chkListTable with 1 selected item. But i don't know how to show chkListRow if multiple item from chkListTable are selected.
Here are my codes:
aspx:
<div>
<asp:Label ID="Label2" runat="server" Text="Table: "></asp:Label>
<asp:CheckBoxList ID="chkListTable" runat="server"
DataTextField="name"
DataValueFeild="name"
AutoPostBack="true"
OnSelectedIndexChanged="chkListTable_SelectedIndexChanged">
</asp:CheckBoxList>
</div>
<div>
<asp:CheckBoxList ID="chkListRow" runat="server"
DataTextField="COLUMN_NAME"
DataValueField="COLUMN_NAME"
RepeatDirection="Horizontal">
</asp:CheckBoxList>
</div>
aspx.cs:
protected void chkListTable_SelectedIndexChanged(object sender, EventArgs e)
{
tableName.Clear();
foreach (ListItem item in chkListTable.Items)
{
if(item.Selected)
{
tableName.Add(item.Text.Trim());
}
}
for(int i = 0; i < tableName.Count; i )
{
String query = "USE " dbname
" SELECT * FROM information_schema.columns"
" WHERE table_name = '" tableName[i] "'"
" AND COLUMN_NAME != 'rowguid'";
chkListRow.DataSource = Program.ExecSqlDataReader(query);
chkListRow.DataBind();
Program.conn.Close();
}
}
Program.cs:
public static bool Connect()
{
if (Program.conn != null && Program.conn.State == ConnectionState.Open)
Program.conn.Close();
try
{
Program.conn.ConnectionString = Program.constr;
Program.conn.Open();
return true;
}
catch (Exception e)
{
return false;
}
}
public static SqlDataReader ExecSqlDataReader(String query)
{
SqlDataReader myreader;
SqlCommand sqlcmd = new SqlCommand(query, Program.conn);
sqlcmd.CommandType = CommandType.Text;
if (Program.conn.State == ConnectionState.Closed) Program.conn.Open();
try
{
myreader = sqlcmd.ExecuteReader();
return myreader;
myreader.Close();
}
catch (SqlException ex)
{
Program.conn.Close();
return null;
}
}
I want my display to be like this:
[x]Table1 [x]Table2 [ ]Table3
[ ]Row1(Table1) [ ]Row2(Table1) [ ]Row3(Table1)
[ ]Row1(Table2) [ ]Row2(Table2)
CodePudding user response：
Ok, this is a rather cute little problem.
So, if we select 1 table, then we need to have one "child" or so called ONE check box list.
but, if we select 2 tables, (or 5), then we need 2 (or 5) child check box lists.
In other words, we would not use the same check box list (child), or try to "mangle" that "N" number of check box lists we need.
So, this "child" sets (one for each checked table) is NOT known ahead of time.
So, we have "repeating" set of those child check box lists, right?
So, we can (and should) then use a "repeater". All a repeater does is "repeat" the whatever we want, and we "feed" the repeater the main list of tables.
So, our markup will now look like this:
<style>
.rBut input {margin-right: 5px; }
.rBut label {margin-right: 15px; }
</style>
<asp:Label ID="Label2" runat="server" Text="Table: "></asp:Label>
<div >
<asp:CheckBoxList ID="chkListTable" runat="server"
DataTextField="TABLE_NAME"
DataValueFeild="TABLE_NAME"
AutoPostBack="true" RepeatDirection="Horizontal" OnSelectedIndexChanged="chkListTable_SelectedIndexChanged" >
</asp:CheckBoxList>
</div>
</div>
<div>
<asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater1_ItemDataBound">
<ItemTemplate>
Table: <%# Eval("Table") %> -
<div >
<asp:CheckBoxList ID="chkListRow" runat="server"
RepeatDirection="Horizontal">
</asp:CheckBoxList>
</div>
</ItemTemplate>
</asp:Repeater>
Note for the 2nd repeter, we do NOT set the Value and text columns - WE DO NOT know them yet. You did not mention which columns to display, but most of my tables always have a PK "ID" for the first column, so lets make that the value , and the display (DataTextField, the 2nd column in the given table).
So, now we build up a "list" of the first selections, (tables), and then pass that thing to the repeater, and it will repeat.
the code now looks like this:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadData();
}
void LoadData()
{
string strSQL = "SELECT TABLE_NAME FROM VideoGames.INFORMATION_SCHEMA.TABLES "
"WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME";
chkListTable.DataSource = MyRst(strSQL);
chkListTable.DataBind();
}
protected void chkListTable_SelectedIndexChanged(object sender, EventArgs e)
{
DataTable MyTables = new DataTable();
MyTables.Columns.Add("Table", typeof(string));
foreach (ListItem OneTable in chkListTable.Items)
{
if (OneTable.Selected)
{
DataRow OneRow = MyTables.NewRow();
OneRow["Table"] = OneTable.Value;
MyTables.Rows.Add(OneRow);
}
}
// ok, we have a list of tables, send that to repeater
Repeater1.DataSource = MyTables;
Repeater1.DataBind();
}
public DataTable MyRst(string strSQL)
{
var rst = new DataTable();
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.VideoGames))
{
using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
{
conn.Open();
rst.Load(cmdSQL.ExecuteReader());
}
}
return rst;
}
protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item
| e.Item.ItemType == ListItemType.AlternatingItem)
{
DataRowView rItem = e.Item.DataItem as DataRowView; // get binding data row
CheckBoxList chkListRow = e.Item.FindControl("chkListRow") as CheckBoxList;
string strSQL = "SELECT * FROM " rItem["Table"].ToString();
DataTable MyTable = MyRst(strSQL);
chkListRow.DataValueField = MyTable.Columns[0].ColumnName;
chkListRow.DataTextField = MyTable.Columns[1].ColumnName;
chkListRow.DataSource = MyRst(strSQL);
chkListRow.DataBind();
}
}
So, now we see this:
If I click one, then I see this:
But, say I click 3, then I see this:
So, note how I feed the Repeater a table (could have been sql query, but in this case, we create table in code). Pass it to repeater.
For each table, the itemdatabound triggers. Because there are multiple copies of the tables, then we need to use find control.
And if you wanted to test/get all of the checked items, then we do a for each on the Repeater items - and again use find control to get each check box control.
But note how we only have two check box lists, but the 2nd one is inside of that repeater, and is data driven.
As a result, this will work for 1, or "N" tables.