How to retrieve items from a database c#

Question

I have 3 tables "pics", "shows", "showpics"

I want to be able to edit the table "shows". In order to do this i need to retrive the pictures that the show contains (the pictures are stored in the table "pics")

the "showpics" table acts as a link

does anyone have any ideas as im completely lost and have no idea where to even start

Thanks

Answer 1

I think you need to learn about how to talk to the database - perhaps this tutorial on ADO.NET would be a good place to start.

ADO.NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file. For the purposes of this tutorial, we will look at ADO.NET as a way to interact with a data base.

Answer 2

Read on how to read and write BLOB with ADO.NET and C# here.

Answer 3

There are many different approaches to retrieve data from the database.

I would use LINQ to SQL for a Microsoft SqlServer database.

Here is a good tutorial: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

Answer 4

Also, here is a tutorial on Linq

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

If you have not used ADO.NET it might be a bit easier to go the declarative route.

Linq can query a database with much less code and Linq will perform much of the interface code for you.

Here is a link on how to read and write blobs in oracle http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/anonyblock/index.html

Answer 5

Using SQL, you would do something like this, e.g., for show ID 27:

select p.* 
from pics p
inner join showpics sp on p.PicID = sp.PicID
inner join shows s on sp.ShowID = s.ShowID
where s.ShowID = 27

Answer 6

If the pictures are large (more than 30Kb or so) you really don't want to put them into a DataSet... this was a reliable way to crash a server with OOM errors for me on an app I worked on. To deal with large pictures, you will want to stream them into and out of the database...

here is a link to get you started on streaming, or better yet, just don't use images more than 30kb or so, else put them on the filesystem, not in tables:

http://msdn.microsoft.com/en-us/library/3517w44b.aspx