i am putting together an application for a client of mine that involves image heavy data handling. i have a couple of possible methods in mind of how to go about this, and would like some outside opinions, especially from any of you who may have done anything
similar before.
to quickly sum up the application's desired results: a photo gallery that contains four sections of job before/after photos. 1)residential 2)comercial 3) automotive and 4)specialty. when a site browser clicks a section, it will bring up a gridview of the
"key" or ideal photo (one each)from several different jobs. the site browser can then click the photo, and a new page (or new control, does'nt make much difference) will come up showing all photos for that particular job.) past and current customers will
create user logins to have ability to upload photos of their specific job. admin needs ability to 1)upload photos for many jobs 2)review all photos before posting them to the gallery 3) would like to give admin ability to order the photos within the gallery
and within each job 4)admin needs ability to create tagline with short description either for each photo or at least for each job.
as to the design of the data, i can either store the images themselves in the database, or create tables to tag the images with various identifying data, then simple store and call the images in a directory on the server. i have code to do either. i am
trying to construct a database design to handle all issues. i like the idea of storing photos in the database more, just not sure how to architect it. it would be the 4 albums, each with infinitely create-able 'job' albums within, each job album housing
job photos.
i am thinking the images maybe get a table for their specific info (ID as primary key, image itself, file type, length, and a foriegn key to a job table that has jobID primary key) the job table could have a job name that user inputs, thus identifying all
photos to the same job. the job table would also foriegn key to the album table id, with its four categories of residential, automotive, commercial, and specialty. does this method seem feasible? if this system seems workable, i can throw in a boolean default
to false for the admin to set true to display the job photos as a whole, with admin power to delete photos on an individual basis. but i am a little stumped how he can maybe order them? maybe an int field that he can edit and just manually put the jobs in
the order he wants? and then put the photos under the jobs in the order he wants as well?
it seems i have somewhat thought this out just now while writing, so it seems i have a somewhat workable solution, if awkward, but if anybody knows a simpler, or tried and true, method, please let me know
any ideas, suggestions, and help is greatly appreciated.
These projects will demonstrate a lot of what you require.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
thank you for your help, the articles were informative on the level of coding the pictures into and out of the database.
what i need is some help on how to work a more complex database scheme than i have seen on any of the tutorials and walkthroughs that i have thus far found.
here is how i am setting it up.
tblAlbums with albumID primary key and album names,etc. then tblJobs with jobID primary key and albumID foriegn key, jobName,etc. and finally tblPhotos with photoID primary key, and jobID foriegn key, and photoData(the binary img),photoType, photoLength,
etc.
on the upload photo page, i first have dropdown control that is databound to albumID and displaying albumName. next i have same dropdown for job, and next to it a txtbox to create new job. then i will have the fileupload and comment fields to import tblPhotos
information.
what i need at this point is a little help in how to code so that each photo is associated with a specific job, and each job is associated with a given album. i am working this out in a very rough around the edges manner, but a little suggestion, help,
or refinement would be a great help.
i am slowly working this out. i have databound the dropdownJob and put a where clause to display only jobs that are in the album that is chosen in dropdownAlbums. then, i have included txtJob to create a new job, and btnJob next to it. i will wire the btnJob
click event to create the new job in tblJobs, then the page will refresh. then the user will be free to fileupload their photos under their newly created jobs. i will code in the jobID when writing the file info to tblPhotos.
i believe (writing, coding, and testing now) that this should give me all necessary associations.
Please drop me a note on Saturday, and I will look into this more. I will create a SQL Script. Please let me know what version of SQL Server you are using. Also whether c# or vb.net.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
ok, i will attempt to explain here quickly. i have the photo upload page complete and working perfectly. the photo display is another matter. i am using a gridview, and i believe i have all the proper code in place aside from a single sql statement. the
tables are:
tblAlbums albumID - int - PK
AlbumName - varchar
tblJobs
jobID - int - PK
albumID - int - FK
jobName - varchar - unique constraint
jobComment - varchar
tblPhotos
photoID - int - PK
jobID - int - FK
photoData - image
photoType - varchar
photoLength - bigint
photoComment - varchar
all 3 tables have 2 columns each named albumOrder, jobOrder, and photoOrder, AND albumVisible, jobVisible, and photoVisible. the order columns are datatype int so the site Admin can place items in the order he chooses. the visible columns are datatype bit,
default to False, so site Admin can approve and mark True all photos before they post to site. finally, tblPhotos has a bit datatype called photoThumbnail, with the intent that site Admin only marks True on one photo per job.
the set-up for the photo gallery page of the site is a dropdown to select the album, then the gridview will display a gridview populated with the thumbnail for all jobs belonging to that album. as i said, i believe i have code in place to display the images
in gridview (using a go between imgGrab.aspx file). i need help writing the SQL SELECT statement to pull only photos with thumbnail=True, only from jobID that belongs to chosen albumID. the statement i tried is this:
strSQL =
"Select tblPhotos.photoId,tblPhotos.photoComment FROM tblPhotos,tblJobs WHERE tblPhotos.jobID=tblJobs.jobID AND tblAlbums.albumName=albumChoice"
i am getting errors on this statement that seem to me that it is invalid to put the variable into the query. (the albumChoice variable is a string that contains the dropdown.SelectedItem.
i assume my query is the problem, and need help with it. if it seems ok, and the problem could be something else, let me know and i can paste the error and stack trace that i am getting.
and after this phase, i need to create each thumbnail into a link to display all photos(not just the thumbnail) in a separate gridview or on a separate page. but i am barely thinking about that just yet. once that is all accomplished, i put together the
set of Admin pages to control all this. but i am getting there....[;)] ..slowly but deliberately.
and my SQL leaves much to be desired, i realize already that i completely omitted any check for photoThumbnail="True"
but still, i am quite proud of this baby thus far, (considering i have only seriously been developing with html, aspx, VB, C#, and SQL for about 2 months.
this section of this project is my proudest moment to date!
ok, i have altered and cleaned a few things up a bit. i set dropdownAlbum to display albumName but hold the value of albumID, then changed the corresponding variable to intAlbum. i have redone the query, and it feels like it is so close, but it is telling
me that intAlbum is an invalid column name. below is the VB code behind page for reference.
Partial Class PublicPages_PhotoGalleries_Gallery
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender
As Object,
ByVal e As System.EventArgs)
Handles Me.Load
dropdownAlbum.DataBind()
Dim intAlbum
As Integer
intAlbum = dropdownAlbum.SelectedValue
Dim ds As
New DataSet
Dim da As SqlClient.SqlDataAdapter
Dim strSQL
As String
strSQL =
"Select tblPhotos.photoId,tblPhotos.photoComment FROM tblPhotos,tblJobs WHERE tblPhotos.photoThumbnail='True' AND tblPhotos.jobID=tblJobs.jobID AND tblJobs.albumID=intAlbum"
Dim connString
As String =
"Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\ASPNETDB.mdf"";Integrated Security=True;User Instance=True"
da = New SqlClient.SqlDataAdapter(strSQL, connString)
da.Fill(ds)
ds.Tables(0).Columns.Add("imgFile")
For Each tempRow
As DataRow In ds.Tables(0).Rows
tempRow.Item("imgFile") = ("imgGrab.aspx?id=" & tempRow.Item("imgID"))
Next
i clean up a couple syntax issues, and rewrite the query to the following:
strSQL =
"Select tblPhotos.photoID,tblJobs.jobComment FROM tblPhotos,tblJobs WHERE tblPhotos.jobID=tblJobs.jobID AND tblJobs.albumID=" & intAlbum &
"AND tblPhotos.photoThumbnail='True'"
and it now partially works, it is pulling the correct jobComment and photos as per album dropdown, but the gridview displays the comment but no image, just that dreaded little red x-box.
doKtorhallam
Member
251 Points
222 Posts
SQL table, key, field design
Dec 06, 2007 12:16 AM|LINK
i am putting together an application for a client of mine that involves image heavy data handling. i have a couple of possible methods in mind of how to go about this, and would like some outside opinions, especially from any of you who may have done anything similar before.
to quickly sum up the application's desired results: a photo gallery that contains four sections of job before/after photos. 1)residential 2)comercial 3) automotive and 4)specialty. when a site browser clicks a section, it will bring up a gridview of the "key" or ideal photo (one each)from several different jobs. the site browser can then click the photo, and a new page (or new control, does'nt make much difference) will come up showing all photos for that particular job.) past and current customers will create user logins to have ability to upload photos of their specific job. admin needs ability to 1)upload photos for many jobs 2)review all photos before posting them to the gallery 3) would like to give admin ability to order the photos within the gallery and within each job 4)admin needs ability to create tagline with short description either for each photo or at least for each job.
as to the design of the data, i can either store the images themselves in the database, or create tables to tag the images with various identifying data, then simple store and call the images in a directory on the server. i have code to do either. i am trying to construct a database design to handle all issues. i like the idea of storing photos in the database more, just not sure how to architect it. it would be the 4 albums, each with infinitely create-able 'job' albums within, each job album housing job photos.
i am thinking the images maybe get a table for their specific info (ID as primary key, image itself, file type, length, and a foriegn key to a job table that has jobID primary key) the job table could have a job name that user inputs, thus identifying all photos to the same job. the job table would also foriegn key to the album table id, with its four categories of residential, automotive, commercial, and specialty. does this method seem feasible? if this system seems workable, i can throw in a boolean default to false for the admin to set true to display the job photos as a whole, with admin power to delete photos on an individual basis. but i am a little stumped how he can maybe order them? maybe an int field that he can edit and just manually put the jobs in the order he wants? and then put the photos under the jobs in the order he wants as well?
it seems i have somewhat thought this out just now while writing, so it seems i have a somewhat workable solution, if awkward, but if anybody knows a simpler, or tried and true, method, please let me know
any ideas, suggestions, and help is greatly appreciated.
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: SQL table, key, field design
Dec 06, 2007 02:56 AM|LINK
Have a look at
- Shows how to store images in SQL Database Image Storage & create Thumnails easiliy from http://www.codeproject.com/KB/web-image/EasyThumbs.aspx
- This article explains an ASP.NET application to view and share photos online.http://www.codeproject.com/KB/applications/NetPix.aspx
These projects will demonstrate a lot of what you require.This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
doKtorhallam
Member
251 Points
222 Posts
Re: SQL table, key, field design
Dec 06, 2007 09:25 PM|LINK
thank you for your help, the articles were informative on the level of coding the pictures into and out of the database.
what i need is some help on how to work a more complex database scheme than i have seen on any of the tutorials and walkthroughs that i have thus far found.
here is how i am setting it up.
tblAlbums with albumID primary key and album names,etc. then tblJobs with jobID primary key and albumID foriegn key, jobName,etc. and finally tblPhotos with photoID primary key, and jobID foriegn key, and photoData(the binary img),photoType, photoLength, etc.
on the upload photo page, i first have dropdown control that is databound to albumID and displaying albumName. next i have same dropdown for job, and next to it a txtbox to create new job. then i will have the fileupload and comment fields to import tblPhotos information.
what i need at this point is a little help in how to code so that each photo is associated with a specific job, and each job is associated with a given album. i am working this out in a very rough around the edges manner, but a little suggestion, help, or refinement would be a great help.
thanks again.
doKtorhallam
Member
251 Points
222 Posts
Re: SQL table, key, field design
Dec 06, 2007 09:40 PM|LINK
i am slowly working this out. i have databound the dropdownJob and put a where clause to display only jobs that are in the album that is chosen in dropdownAlbums. then, i have included txtJob to create a new job, and btnJob next to it. i will wire the btnJob click event to create the new job in tblJobs, then the page will refresh. then the user will be free to fileupload their photos under their newly created jobs. i will code in the jobID when writing the file info to tblPhotos.
i believe (writing, coding, and testing now) that this should give me all necessary associations.
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: SQL table, key, field design
Dec 06, 2007 10:02 PM|LINK
Please drop me a note on Saturday, and I will look into this more. I will create a SQL Script. Please let me know what version of SQL Server you are using. Also whether c# or vb.net.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
doKtorhallam
Member
251 Points
222 Posts
Re: SQL table, key, field design
Dec 06, 2007 11:11 PM|LINK
qa note?
doKtorhallam
Member
251 Points
222 Posts
Re: SQL table, key, field design
Dec 07, 2007 06:29 PM|LINK
ok, i will attempt to explain here quickly. i have the photo upload page complete and working perfectly. the photo display is another matter. i am using a gridview, and i believe i have all the proper code in place aside from a single sql statement. the tables are:
tblAlbums
albumID - int - PK
AlbumName - varchar
tblJobs
jobID - int - PK
albumID - int - FK
jobName - varchar - unique constraint
jobComment - varchar
tblPhotos
photoID - int - PK
jobID - int - FK
photoData - image
photoType - varchar
photoLength - bigint
photoComment - varchar
all 3 tables have 2 columns each named albumOrder, jobOrder, and photoOrder, AND albumVisible, jobVisible, and photoVisible. the order columns are datatype int so the site Admin can place items in the order he chooses. the visible columns are datatype bit, default to False, so site Admin can approve and mark True all photos before they post to site. finally, tblPhotos has a bit datatype called photoThumbnail, with the intent that site Admin only marks True on one photo per job.
the set-up for the photo gallery page of the site is a dropdown to select the album, then the gridview will display a gridview populated with the thumbnail for all jobs belonging to that album. as i said, i believe i have code in place to display the images in gridview (using a go between imgGrab.aspx file). i need help writing the SQL SELECT statement to pull only photos with thumbnail=True, only from jobID that belongs to chosen albumID. the statement i tried is this:
strSQL =
"Select tblPhotos.photoId,tblPhotos.photoComment FROM tblPhotos,tblJobs WHERE tblPhotos.jobID=tblJobs.jobID AND tblAlbums.albumName=albumChoice"i am getting errors on this statement that seem to me that it is invalid to put the variable into the query. (the albumChoice variable is a string that contains the dropdown.SelectedItem.
i assume my query is the problem, and need help with it. if it seems ok, and the problem could be something else, let me know and i can paste the error and stack trace that i am getting.
and after this phase, i need to create each thumbnail into a link to display all photos(not just the thumbnail) in a separate gridview or on a separate page. but i am barely thinking about that just yet. once that is all accomplished, i put together the set of Admin pages to control all this. but i am getting there....[;)] ..slowly but deliberately.
thanks for any and all help here.
doKtorhallam
Member
251 Points
222 Posts
Re: SQL table, key, field design
Dec 07, 2007 06:35 PM|LINK
and my SQL leaves much to be desired, i realize already that i completely omitted any check for photoThumbnail="True"
but still, i am quite proud of this baby thus far, (considering i have only seriously been developing with html, aspx, VB, C#, and SQL for about 2 months.
this section of this project is my proudest moment to date!
thanks again for any help and suggestions.
doKtorhallam
Member
251 Points
222 Posts
Re: SQL table, key, field design
Dec 07, 2007 07:13 PM|LINK
ok, i have altered and cleaned a few things up a bit. i set dropdownAlbum to display albumName but hold the value of albumID, then changed the corresponding variable to intAlbum. i have redone the query, and it feels like it is so close, but it is telling me that intAlbum is an invalid column name. below is the VB code behind page for reference.
Partial Class PublicPages_PhotoGalleries_Gallery Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoaddropdownAlbum.DataBind()
Dim intAlbum As IntegerintAlbum = dropdownAlbum.SelectedValue
Dim ds As New DataSet Dim da As SqlClient.SqlDataAdapter Dim strSQL As StringstrSQL =
"Select tblPhotos.photoId,tblPhotos.photoComment FROM tblPhotos,tblJobs WHERE tblPhotos.photoThumbnail='True' AND tblPhotos.jobID=tblJobs.jobID AND tblJobs.albumID=intAlbum" Dim connString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\ASPNETDB.mdf"";Integrated Security=True;User Instance=True" da = New SqlClient.SqlDataAdapter(strSQL, connString)da.Fill(ds)
ds.Tables(0).Columns.Add("imgFile") For Each tempRow As DataRow In ds.Tables(0).Rows tempRow.Item("imgFile") = ("imgGrab.aspx?id=" & tempRow.Item("imgID")) NextgalleryGrid.DataSource = ds
galleryGrid.DataBind()
End SubEnd
ClassdoKtorhallam
Member
251 Points
222 Posts
Re: SQL table, key, field design
Dec 07, 2007 09:23 PM|LINK
....inching closer and closer....
i clean up a couple syntax issues, and rewrite the query to the following:
strSQL =
"Select tblPhotos.photoID,tblJobs.jobComment FROM tblPhotos,tblJobs WHERE tblPhotos.jobID=tblJobs.jobID AND tblJobs.albumID=" & intAlbum & "AND tblPhotos.photoThumbnail='True'"and it now partially works, it is pulling the correct jobComment and photos as per album dropdown, but the gridview displays the comment but no image, just that dreaded little red x-box.
....inching closer and closer....