Sean Connolly

Hi,

I'm writing my application in C# and it sends and retrieves masses of data from many SQL server databases (using one as a master database). As the databases all vary in shapes and sizes, I have to make the C# program create all my columns and populate the database by sending commands to SQL server.

I have an INNER JOIN statement I am executing to return some rows of data from the same table which looks a little like this (self joins I think you call this):

SELECT F1.*, F2.* FROM MyDataBase
INNER JOIN MyDataBase F2 ON
F1.LineIndex < F2.LineIndex AND
...etc...etc... rest of my select statement

Now, because I am returning two selections, labelled with F1 and F2, I have to be able to read the columns of data returned in my DataReader.

My question is: Can I use the column names to read the data back, even though the same column name appears more than once If this is possible, how do I communicate this with the DataReader.

I currently read the data from specific columns/row like this:

string MyReturnedString = myReader.Read["SQL_Column_Name"].ToString();

If I cannot communicate with the second set of columns by name, do I then have to read the data with a column index, rather than by name

Thanks

Sean.



Re: .NET Framework Data Access and Storage Inner join selects and returned results with a datareader

kbradl1

I believe when you try to execute the SQL statement with two columns with the same name you will get an error.

Instead edit the SQL statement to include an alias for the dupicated field names.
i.e. Select Name, Name As SecondNameColumn FROM Table

string MyReturnedString = myReader.Read["Name"].ToString();
string MySecondReturnedString = myReader.Read["SecondNameColumn"].ToString();




Re: .NET Framework Data Access and Storage Inner join selects and returned results with a datareader

Sean Connolly

Thanks,

Does communicating the aliases in the DataReader work I haven't tried that before (I'm just in the office now) but I'll give it a try this morning.

As I am using inner joins on my selects, I already have aliases set up for both parts of the joins.

I'll let you know what happens!

Sean.





Re: .NET Framework Data Access and Storage Inner join selects and returned results with a datareader

Sean Connolly

Hi,

Because my column select method was a wildcard with an '*', I couldn't alias the column names because the INNER JOIN aliases had forbidden this method. I have had to work around my problem another way, using another table!

Thanks for the advice though!

Sean