i'm trying to do a display database's item(tables, rows, fk,...).
I'm stucking at first few step. I loaded the db's names into a DropDownList. But i tried to load Tables from Selected db's name into CheckBoxList but it shows nothing.
Here are my codes
aspx:
<form id="form1" runat="server" method="get">
<div>
<asp:DropDownList ID="drpListDBName" runat="server" OnSelectedIndexChanged="drpListDBName_SelectedIndexChanged">
</asp:DropDownList>
</div>
<div>
<asp:CheckBoxList ID="chkListTable" runat="server">
</asp:CheckBoxList>
</div>
aspx.cs:
public static String dbname = "";
protected void Page_Load(object sender, EventArgs e)
{
String query = "SELECT * FROM SYS.sysdatabases WHERE name != 'master'"
" and name != 'tempdb'"
" and name != 'model'"
" and name != 'msdb'"
" and name != 'distribution'";
Program.Connect();
if(!Page.IsPostBack)
{
drpListDBName.DataSource = Program.ExecSqlDataReader(query);
drpListDBName.DataTextField = "name";
drpListDBName.DataBind();
}
}
protected void drpListDBName_SelectedIndexChanged(object sender, EventArgs e)
{
dbname = drpListDBName.SelectedValue.Trim();
String query = "USE " dbname
" SELECT * FROM SYS.tables"
" WHERE is_ms_shipped = 0";
Program.Connect();
if (chkListTable.Items.Count > 0)
chkListTable.Items.Clear();
chkListTable.DataSource = Program.ExecSqlDataReader(query);
chkListTable.DataTextField = "name";
chkListTable.DataBind();
Response.Write(chkListTable);
}
I'm still new to asp.net. Thanks in advance.
CodePudding user response：
Ok, while you could send your 2nd selection (the list of tables) to a check box list? (and if you needing to select multiple tables - perhaps yes).
but, lets do one better. Lets use two combo boxes. First one, select database, fill 2nd combo box with tables.
Then you select a table, and display the table in a grid.
So, first up is (possbile) is the connection string used. We can assume that you built or setup at least one working connection string to one of the databases on that sql server.
So, project->"my project name settings".
You get this:
So, I have a few already made, but note the [...] button, if you click on that, then VS will launch a connection builder for you (nice and easy). And you can test the connection.
So, we just start out by using TEST4. (note that this DOES put the connection string automatic in web config for you).
Ok, So lets drop in a combo box (to select database).
And then our check box list.
So, we have this markup:
<h4>Select Database</h4>
<asp:DropDownList ID="DropDownList1" runat="server" Width="177px"
DataTextField="name"
DataValueFeild="name"
AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList>
<br />
<asp:CheckBoxList ID="CheckBoxList1" runat="server"
DataTextField="TABLE_NAME"
DataValueField="TABLE_NAME" >
</asp:CheckBoxList>
And now we have this code:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadData();
}
void LoadData()
{
Session["MyCon"] = Properties.Settings.Default.TEST4;
string query = "SELECT * FROM SYS.sysdatabases WHERE name != 'master'"
" and name != 'tempdb'"
" and name != 'model'"
" and name != 'msdb'"
" and name != 'distribution' ORDER BY Name";
DropDownList1.DataSource = MyRst(query);
DropDownList1.DataBind();
}
public DataTable MyRst(string strSQL)
{
var rst = new DataTable();
using (SqlConnection conn = new SqlConnection((Session["MyCon"] as string)))
{
using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
{
conn.Open();
rst.Load(cmdSQL.ExecuteReader());
}
}
return rst;
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
// set connecting string from TEST4 to whatever database selected
string strCon = Session["MyCon"] as string;
string strDB = DropDownList1.SelectedItem.Value;
strCon = strCon.Replace("Initial Catalog = test4;", strDB);
Session["MyCon"] = strCon;
// now load check box list with all tables
string strSQL = @"SELECT TABLE_NAME FROM [" strDB "].INFORMATION_SCHEMA.TABLES "
"WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME";
CheckBoxList1.DataSource = MyRst(strSQL);
CheckBoxList1.DataBind();
}
So, now we get this:
So, yes, you can set a datatable to check box list. But, some of my databases have quite a few tables - too long (many) check boxes.
So, lets change our code to two combo boxes.
However, we need a "please select" for the combo boxes.
So, our mark up is now this:
<h4>Select Database</h4>
<asp:DropDownList ID="DropDownList1" runat="server" Width="180px"
DataTextField="name"
DataValueFeild="name"
AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList>
<br />
<h4>Select Table</h4>
<asp:DropDownList ID="DropDownList2" runat="server" Width="180"
DataTextField="TABLE_NAME"
DataValueField="TABLE_NAME"
AutoPostBack="true" OnSelectedIndexChanged="DropDownList2_SelectedIndexChanged"
>
</asp:DropDownList>
<asp:GridView ID="GridView1" runat="server" CssClass="table">
</asp:GridView>
Our code is now this:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadData();
}
void LoadData()
{
Session["MyCon"] = Properties.Settings.Default.TEST4;
string query = "SELECT * FROM SYS.sysdatabases WHERE name != 'master'"
" and name != 'tempdb'"
" and name != 'model'"
" and name != 'msdb'"
" and name != 'distribution' ORDER BY Name";
DropDownList1.DataSource = MyRst(query);
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, new ListItem("Please Select", ""));
}
public DataTable MyRst(string strSQL)
{
var rst = new DataTable();
using (SqlConnection conn = new SqlConnection((Session["MyCon"] as string)))
{
using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
{
conn.Open();
rst.Load(cmdSQL.ExecuteReader());
}
}
return rst;
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
// set connecting string from TEST4 to whatever database selected
string strCon = Session["MyCon"] as string;
string strDB = DropDownList1.SelectedItem.Value;
strCon = strCon.Replace("Initial Catalog = test4;", strDB);
Session["MyCon"] = strCon;
// now load 2nd cbo box list with all tables
string strSQL = @"SELECT TABLE_NAME FROM [" strDB "].INFORMATION_SCHEMA.TABLES "
"WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME";
DropDownList2.DataSource = MyRst(strSQL);
DropDownList2.DataBind();
DropDownList2.Items.Insert(0, new ListItem("Please Select", ""));
}
protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
{
// send results to the Grid
string strSQL = "SELECT * FROM [" DropDownList2.SelectedItem.Value "]";
GridView1.DataSource = MyRst(strSQL);
GridView1.DataBind();
}
And now we get this:
So, I used a working connection string to a given database. I quite much assumed that the same information could and would connect to all of them, so I swap out TEST4 database name with any one we select.