Query a dataset for binding (ADO.NET)

[TimVtoo]

Hi all and thanks for looking.

I need to make the move from ASP to ASP.NET, and as my sister has aked me to knock up a website for her, I thought this could be a nice starter project to help me learn.

The site displays thumnails of images she will upload. Each file needs some information stored in a database about it. In classic asp I would have created a list of images and realated info within a database and driven the site from this. However i thought it would be a nice idea to use the filesystem to list the images.

My plan was to create a DataSet with 2 DataTables. One DataTable full of filenames found in a folder and the other DataTable full of file info loaded from the database. Then I would create a relationship between these tables and (this is where it gets cloudy) bind this to some DataList or something?

So far I have got my dataset with both tables filled with the information as sugested, and created a relationship between them.
Now I'm stuck!!!

How can I bind this information? I still have 2 DataTables! Is it now possible to query this information to put it into a single bindable table or something, or is my whole approach not going to work?

thanks for your time!

[Frinavale]

@TimVtoo
This is exactly what you're going to have to do.

You need to write SQL that queries from both tables to create your "Bindable" table.

Then how you use the table is up to you...you could bind it to a GridView or just use a Repeater to display the images. There's a lot of options at that point.

To get you pointed in the right direction, check out the article on how to use a database in your program.

Also, the MSDN Library is a very powerful resource to use while developing .NET applications. There you can find information on GridViews, DataSets, Repeaters, and much more.

Cheers!

-Frinny

[TimVtoo]

Hi Frinny, thanks for your reply, but i think you may have slightly misunderstood me.

Both of the DataTables I have are ADO.NET tables in the ASP.net application itself as one of the tables is derived from the filesystem. So if I am to create a query to bring this data together, it needs to be done in the application and not a database using ado.net.
My intension here is really to get my head around what I can and cant do with .net AND ADO.net. I have a pretty good understanding of databases, but its the .net i'm new to !

[Frinavale]

Check out the DataView object and the DataViewManager object.

I think you're going to have to create DataViews for your tables and use the DataViewManager to join them together to be used as a data source for whatever control you're binding the data to.

There might be an easier way to do this....and I'm wondering if LINQ could help you in anyway...but give this a try and if it's not to your liking maybe we can find another solution :)

-Frinny

[TimVtoo]

Thanks Frinny, but as far as I can tell, a data view manager is for hadling Sorting and Filtering for multiple DataTables but they still dont help with combining tables as a join in a database query would. :(

Just to clarify what I'm trying to achieve here.
SIMPLIFIED:
I have an ADO DataTable that contains a list of Image Files found in a specific folder.
I have another ADO DataTable that contains a list of Alias's for these Images.

I am currently diplaying a thumbnail of each image with it's image name underneath it. What i would like to do is display the Alias (from the other table) instead of it's filename under each thumbnail.

Any more suggestions would be welcome!

Thanks

Tim

[Curtis Rutland]

OK, so just to be sure, it isn't possible to do this at the query level? Write one query that handles your join and returns one table?

If not, you might just have to create your own third DataTable with the columns you need, and loop through and fill in the cells manually.

Also, this might be worth reading:
HOW TO: Implement a Custom DataView Class in Visual Basic .NET

[AricC]

I'm a bit confused is the data for the folder coming from a DB? If so do what insertAlias said and join the two tables you should have a column in your DB that links the two or are they coming from the file system?

[TimVtoo]

@insertAlias
You are right in saying this can't be done at the query level.

The file/folder info is being done using the DirectoryInfo & FileInfo methods and filling an ado datatable with what it finds.

I took this approach so that it will only ever list files that actually exist AND because I thought it would be a good learning execise.

If nothing else I may have learned that this is not a good approach :).



I'll have a look at the link you posted insertAlias.

Thanks for getting back guys, thought this had dies a death!

[TimVtoo]

OK, it is becoming apparent that relating ado datatable is not going to work for me here.

It has just occured to me (duh) that rather than putting the filnames I find into an ado table I can just use them in a string to build an SQL statement to retreive my alias's right?

But, as I said, i was doing the related ado table thing as a learning excercise, so could someone please give me a good (simple) example of where Relating ADO DataTables is usefull ??

Thanks again for your time!

Tim