Preface: I'm building an intranet site to build custom queries against
our SQL Server (2000) db;
The page is developed in ASP.net (2.0) with C# Codebehind.
I dynamically generate and populate checkboxlists for each table that
the user selects to query.
I disable selecting non-related tables, and am in the process of
creating inner joins when tables are related.... This is where I am
getting stuck, here is the logic which builds the join statements...
selectedTables is an array of (strings) which are the table names the
user wants to query.
So I query the database for each one of those, and get related tables,
and make sure those checkboxes are enabled, the rest get disabled.
protected void enabledRelated2()
{
string connStr =
ConfigurationManager.ConnectionStrings["myConnStr"].ToString();
using (SqlConnection myCon = new SqlConnection(connStr))
{
myCon.Open();
foreach (string selTables in selectedTables)
{
using (SqlCommand command = new SqlCommand("SELECT
u.column_name as PrimaryColumn,tc.TABLE_NAME AS
PrimaryKeyTable,u2.column_name as ForeignColumn,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE u on tc.CONSTRAINT_NAME
=u.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME
=rc1.CONSTRAINT_NAME join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u2 on
tc2.CONSTRAINT_NAME =u2.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE
='PRIMARY KEY' AND TC2.CONSTRAINT_TYPE ='FOREIGN KEY' AND
(tc.table_name = '" + selTables + "'" + " OR tc2.TABLE_NAME = '" +
selTables + "')", myCon))
{
using (SqlDataReader reader =
command.ExecuteReader())
{
while (reader.Read())
{
for (int i = 0; i <
CheckBoxListTables.Items.Count; i++)
{
// If an item is the primarykeytable or
foreignkeytable to a selected table
if (CheckBoxListTables.Items[i].Text ==
reader[1].ToString() || CheckBoxListTables.Items[i].Text ==
reader[3].ToString())
{
// For each selected checkbox,
enable the checkboxes for related tables
CheckBoxListTables.Items[i].Enabled
= true;
}
//If a table is selected and that table
is the primarykeytable
if
(CheckBoxListTables.Items[i].Selected &&
CheckBoxListTables.Items[i].Text == reader[1].ToString())
{
for (int x = 0; x <
CheckBoxListTables.Items.Count; x++)
{
// For each table selected,
cycle through and check for related tables, if those tables are
selected, build the JOIN statement
if
(CheckBoxListTables.Items[x].Selected && x != i &&
CheckBoxListTables.Items[x].Text == reader[3].ToString())
{
// Here is the part that fires more than I want it to, this occurs
twice for every relation. So if i select two tables, it generates two
inner join statements which are identical. If i select 3 related
tables, it creates 4 joins, 2 sets of identical ones
queryJoins += " INNER JOIN
" + reader[1].ToString() + " ON " + reader[3].ToString() + "." +
reader[2].ToString() + "=" + reader[1].ToString() + "." +
reader[0].ToString();
}
}
}
}
}
}
}
}
myCon.Close();
}
}
I understand that I'll be very lucky if anyone sees the issue from this
segment of code, without much more knowledge about the project than
I've included, so please feel free to ask questions, I can even supply
a screen shot of the interface etc...
