Hi all,

I have a question. Here is my scenario and the code that I am using :

I have hardcoded the logic for three conditions in my code. I want to make this recursive so that I can accomodate many levels (more or less that 3).

1. I will get all distinct values from a sql table column first.
2. Ths first time i will add the value and two blanks for a new row of final table
3. On each of thevalues of first i will get the dependent values of second column using where staement (= First value)
4. This time i will add the First vale, second value and blank for third value
5. On each of the values of second i will get the dependent values of third column using where staement (= Second value)
6. This time I will add First Value , Second Value and Third Value.

** Please note that this SQL Statement are hardcoded but this will be generated by another method if I pass the where condition value.

I want to make this recursive so that I donot have to write different logic if no of loops increase (more than three). Below is my code:


private void button1_Click(object sender, EventArgs e)

{

SqlConnection sqlConnection = new SqlConnection("Data Source=localhost;Initial Catalog=AdventureWorksDW;Integrated Security=SSPI;");






SqlCommand sqlCommand;

SqlDataAdapter sqlDataAdapter;






DataTable finalSqlTable = new DataTable();

finalSqlTable.Columns.Add("Level01");

finalSqlTable.Columns.Add("Level02");

finalSqlTable.Columns.Add("Level03");






DataRow dataRowNew;






sqlCommand = new SqlCommand("SELECT Convert(nvarchar(4000),DepartmentName) from (SELECT DISTINCT DepartmentName from DimEmployee) AS Z Order by DepartmentName", sqlConnection);

sqlDataAdapter = new SqlDataAdapter(sqlCommand);

DataTable sqlTable1 = new DataTable();

sqlDataAdapter.Fill(sqlTable1);






foreach (DataRow dataRow in sqlTable1.Rows)

{

dataRowNew = finalSqlTable.NewRow();

dataRowNew[0] = dataRow[0].ToString();

dataRowNew[1] = "";

dataRowNew[2] = "";






finalSqlTable.Rows.Add(dataRowNew);






sqlCommand = new SqlCommand("SELECT Convert(nvarchar(4000),Title) from (SELECT DISTINCT Title from DimEmployee WHERE DepartmentName = '" + dataRow[0].ToString() + "') AS Z Order by Title ", sqlConnection);

sqlDataAdapter = new SqlDataAdapter(sqlCommand);

DataTable sqlTable2 = new DataTable();

sqlDataAdapter.Fill(sqlTable2);










foreach (DataRow dataRow1 in sqlTable2.Rows)

{

dataRowNew = finalSqlTable.NewRow();

dataRowNew[0] = dataRow[0].ToString();

dataRowNew[1] = dataRow1[0].ToString();

dataRowNew[2] = "";






finalSqlTable.Rows.Add(dataRowNew);






sqlCommand = new SqlCommand("SELECT Convert(nvarchar(4000),EmployeeKey) from (SELECT DISTINCT [EmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID],[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone][SalariedFlag],[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag],[DepartmentName],[StartDate],[EndDate],[Status],[SalesTerritoryKey],[ParentEmployeeKey] from DimEmployee WHERE Title = '" + dataRow1[0].ToString() + " ') AS Z Order by EmployeeKey ", sqlConnection);

sqlDataAdapter = new SqlDataAdapter(sqlCommand);

DataTable sqlTable3 = new DataTable();

sqlDataAdapter.Fill(sqlTable3);




foreach (DataRow dataRow2 in sqlTable3.Rows)

{

dataRowNew = finalSqlTable.NewRow();

dataRowNew[0] = dataRow[0].ToString();

dataRowNew[1] = dataRow1[0].ToString();

dataRowNew[2] = dataRow2[0].ToString();






finalSqlTable.Rows.Add(dataRowNew);

}

}

}






finalSqlTable = addUnknown(finalSqlTable);






MessageBox.Show(Convert.ToString(finalSqlTable.Row s.Count));

}



Let me know if you need additional information. This is kind of important for me.

Regards..
Girija Shankar