Sending Uploaded Image To Data Access Class When Storing Image In SQL Server 2005
Apr 20, 2007
I am using the 3-tiered architecture design (presentation, business laws, and data acess layers). I am stuck on how to send the image the user selects in the upload file control to the BLL and then to the DAL because the DAL does all the inserts into the database. I would like to be able to check the file type in the BLL to make sure the file being uploaded is indeed a picture. Is there a way I can send the location of the file to the BLL, check the filetype, then upload the file and have the DAL insert the image into the database? I have seen examples where people use streams to upload the file directly from their presentation layer, but I would like to keep everything seperated in the three classes if possible. I also wasn't sure what variable type the image would be in the function in the BLL that receive the image from the PL. If there are any examples or tips anyone can give me that would be appreciated.
This is my first time to create web site by using Visual web developer express 2005. I want to create company's activity member profile web page by use sql database. But I don't know how to create SQL DB and Table and I don't know code for connect to sql server, code for upload image files, data file, and code for displays images. So please tell me how to do it step by step. (I use VS web developer express 2005 with vb.net) Thank you
I have a very large array of floating point numbers which I am tryingtostore in an image type field. I am using the dataset class todirectly enter the data but am having trouble trying to get this typeinto the image field. I guess the question is how do I get a largefloating point array into an image (byte)field of a database using thedataset class for new row entry.Thanks
I'm storing thumbnails in SQL CE and accessing them from my WPF application. The save seems to take place ok and there's binary data in the image field however when it comes back out it appears to be corrupted somehow. Can someone have a look at the code below and verify I'm doing the conversion correctly before storing in my image field? Thanks!
I did some quick Googling and didn't find the article of my dreams here. Any experience with storing files in the db in 2005? Any opinions?
Here's the deal. We have our new 'Enterprise' software being designed and written in St. Petersburg (not FL) and I'm reviewing the design for dealing with letters and forms that are generated as hard copy from our business (by the thousands per month) and sent to clients for review, signature etc. Then they are returned by fax or snail mail.
The current design has all of these being stored in image data type columns in the database--storing saved files of the actual outgoing stuff and saved files of the stuff that comes in (scanned copies or the files from our fax server).
This screams 'BAD IDEA' based on our experience doing this in SQL Server 2000. We get things like 23 page faxes from Dr's offices, large packets of FMLA forms returned, etc. In 2000 they tended to get rather large when stored in the DB and caused us all kinds of headaches when Microsoft changed how it recognized and displayed certain types of files, e.g. tiffs.
Further, the outgoing stuff is largely boilerplate with added fields from the db--name, address, a few dates. My proposed design for outbound notifications and forms was to keep only references to the template used, the date created, and an id to link it to the personal info of the schmoe to whom it was sent--essentially, store which document template and then just enough data to map to the variable content that is pulled from the db. Then you can recreate the file that was printed and mailed at any time in the future, but you don't actually save the .doc (or Word .xml) anywhere, not in the file system, not in the db.
The current developers feel this is way too much work to design--and it is certainly more effort than just generating .docs/.xml files via a merge with Word and then stuffing those into the database. They also argue that having them in the db makes them 'more secure' and more 'accessible' to users around the globe.
Does anyone know if the image storage is any better in 2005 than 2000? The .docs & .tifs we used to store just about tripled in size when we put them in the db in 2000. Plus there were the defrag issues and generally it was an unsatisfying experience.
Does anyone know what the advantages/disadvantages of generating Word .xml files might be? Can they be stored as xml data type in SQL Server? Perhaps this is a better option?
If anyone has the time to digest this, please send opinions.
Just starting to investigate this. I need to store digital pictures in my database for use in claims and disputes. I have played a bit with the image datatype but notice that one picture takes up 1Mb as a TIFF and 3Mb as a JPEG. Does anybody work with this stuff and if you do what is the best picture format to use in regards to keeping clarity but minimizing size used in the db. Is there any load conversion routines that would shrink the size of the file.
Any suggestions or comments on this topic are appreciated.
Just starting to investigate this. I need to store digital pictures in my database for use in claims and disputes. I have played a bit with the image datatype but notice that one picture takes up 1Mb as a TIFF and 3Mb as a JPEG. Does anybody work with this stuff and if you do what is the best picture format to use in regards to keeping clarity but minimizing size used in the db. Is there any load conversion routines that would shrink the size of the file.
Any suggestions or comments on this topic are appreciated.
I am using FileUpload control in ASP.net 2.0 to upload files and store them into SQL server database as an image. I am fine with MS office files, image files and etc. We have Product Center (Proe) engineering software to configure parts and the files generated through this software have PLT extension when I store these files, the file type is Plian/text. I am using Fileupload.PostedFile.Content to get the file type. How can I store PLT, TIF files in SQL server? Please help.
This just happened today and I dont know why. I have a database on a SQL server 2005 database, that has a table called photos. It contains image data, but not the column says <Unable to read data>. But before that I get an error when I try to execute the table to retreive the data. The error is below. SQL Execution Error:Executed SQL statement: SELECT ID, AlbumID, Caption, BytesOriginal, BytesFull, BytesThumb FROM PhotosError Source: System.DataError Message: Invalid attempt to Read when reader is closed. This happens with SQL Server Management Studio, and Visual Studio 2005 when I try to access it. I tried two computers so its not that. Any help or insight would be appreciated.
Hay Friend's Can u plese send me the way how to save image in sql server and display that images in datagrid or other control also like Image control or Image control Button?? Plese send the coding in C#. Thank's Amit
Ok, the problem is that , i have a field called "Attach" in sql of type image, when selecting it , the field is getting data of type BYTE(). which am being unable to display them on an Image on the panel.
using the following vb.net code:
'Dim sel2 As String
'Dim myCom As SqlCommand
'Dim conn As New SqlConnection
'Dim drr As SqlDataReader
'Dim image As System.Drawing.Image
'sel2 = "select * from attach where att_desc = '" & DropDownList1.SelectedItem().Text & "' and doc_code = " & w_doc_code & " and subcode = " & w_doc_subcode & " and doc_num= " & w_doc_num & " "
I'v been looking for a sample that illustrate how to send an Image(jpeg/gif/bmp) using SSB. The initiating client should pick an image stored locally on a folder in that machine and send it over to the target broker instance using TCP. The target should verify the contents of the message and then insert the image in a table, which i assume should have a column of type varbinary(max).
hi, I want to Display the image records of each employee of having 10 images of each.the number of records are more then one billion....... please provid me the optimized query that can i used in SP and display in Gridview.
1. How can I upload new pictures into the database through the browser. 2. Can you describe how the new picture of the product would be uploaded into the database. 3. How do you handle different formats that pictures come in gif, tif, jpeg, etc.
4. How can you say that SQL server does support images well, because on microsoft site they loaded up the satellite data of the earth into sqlserver which is only picture data
What are the negatives, challenges and issues going to be if we choose to store 300-1000 GB worth of image, xml and PDF files (avg size 1 mb) in SQL server 2005 db.
I have got this issue when I am trying to store an Image on a database through a stored procedure. I give you the code in C# and the stored procedure
Code Snippet
//I have got these two variables with the ID(foreign key) and the array of bytes of the Image (the values are OK) int ID_Inmueble byte[] imagen -- dimension 26246
SqlConnection conn = new SqlConnection(...) //The conexion works fine (I tested it).
hi friends, i want to show images in repeater control dynamically. i want to store the image url in database like i have stored the images on onefolder c:images this will contain images how can i store and use the urls in database or atleast how can i show the images from the folder dynamically please help me out
I am using an image datatype to store multiple filetypes includeing plaintext data. it works except for pdf Data. When I retrieve the the filecouldn't be opened in acrobat reader and the file was 3KB bigger than theoriginal.Any suggestions would be helpful.
Hi everyone, I have an odd problem. I have a generic upload/download ASP.net page that allows the upload and download of and type of file. I have so far tested the following file types: XLS, MDB, JPG, DLL, EXE, PDF, TXT, SWF, and GIF All of these upload and download fine, EXCEPT PDF's. I have tried 4 different PDF's and all open prior to upload/download, but after uploading and downloading, I get the following (from Adobe Reader) error upon trying to open: "There was an error opening this document. The file is damaged and could not be repaired" Here's my current code: 9 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs)10 11 If e.CommandName = "open" Then12 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)13 Dim sSQL As New StringBuilder14 Dim sqlCmd As SqlCommand15 Dim sqlReader As SqlDataReader16 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte17 18 sSQL.Append(" SELECT * ")19 sSQL.Append(" FROM [survey_document] ")20 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ")21 22 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)23 24 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument)25 26 sqlConn.Open()27 sqlReader = sqlCmd.ExecuteReader28 29 While sqlReader.Read30 31 Response.ContentType = sqlReader("sd_mime_type").ToString()32 Response.BinaryWrite(sqlReader("sd_document"))33 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString())34 End While35 36 sqlReader.Close()37 sqlConn.Close()38 39 End If40 41 End Sub42 43 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click44 45 'Make sure a file has been successfully uploaded46 If UploadedFile.HasFile Then47 48 'Connect to the database and insert a new record into Products49 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)50 Dim sSQL As New StringBuilder51 Dim sqlCmd As SqlCommand52 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte53 54 'Read the files binary data into a Byte array55 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length)56 57 sSQL.Append(" INSERT INTO [survey_document] ")58 sSQL.Append(" ( sd_document ")59 sSQL.Append(" , sd_title ")60 sSQL.Append(" , sd_file_name ")61 sSQL.Append(" , sd_upload_date ")62 sSQL.Append(" , sd_mime_type ")63 sSQL.Append(" ) VALUES ( @sd_document ")64 sSQL.Append(" , @sd_title ")65 sSQL.Append(" , @sd_file_name ")66 sSQL.Append(" , @sd_upload_date ")67 sSQL.Append(" , @sd_mime_type ")68 sSQL.Append(" ) ")69 70 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)71 72 sqlCmd.Parameters.AddWithValue("@sd_title", FileTitle.Text.Trim())73 sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType)74 sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName))75 sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now)76 sqlCmd.Parameters.AddWithValue("@sd_document", byteArray)77 78 sqlConn.Open()79 sqlCmd.ExecuteNonQuery()80 sqlConn.Close()81 82 Else83 84 'Either the file upload failed or no file was selected85 86 End If87 88 End SubI have also tried the following code as a result of searching far and wide, trying other peoples methods: 1 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs) 2 3 If e.CommandName = "open" Then 4 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString) 5 Dim sSQL As New StringBuilder 6 Dim sqlCmd As SqlCommand 7 Dim sqlReader As SqlDataReader 8 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte 9 10 sSQL.Append(" SELECT * ") 11 sSQL.Append(" FROM [survey_document] ") 12 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ") 13 14 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn) 15 16 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument) 17 18 sqlConn.Open() 19 sqlReader = sqlCmd.ExecuteReader 20 21 While sqlReader.Read 22 23 Dim buffer() As Byte = sqlReader("sd_document") 24 Dim blen As Integer = CType(sqlReader("sd_document"), Byte()).Length 25 26 Response.ContentType = sqlReader("sd_mime_type").ToString() 27 Response.OutputStream.Write(buffer, 0, blen,) 28 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString()) 29 End While 30 31 sqlReader.Close() 32 sqlConn.Close() 33 34 End If 35 36 End Sub 37 38 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click 39 40 'Make sure a file has been successfully uploaded 41 If UploadedFile.HasFile Then 42 43 'Connect to the database and insert a new record into Products 44 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString) 45 Dim sSQL As New StringBuilder 46 Dim sqlCmd As SqlCommand 47 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte 48 49 'Read the files binary data into a Byte array 50 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length) 51 52 sSQL.Append(" INSERT INTO [survey_document] ") 53 sSQL.Append(" ( sd_document ") 54 sSQL.Append(" , sd_title ") 55 sSQL.Append(" , sd_file_name ") 56 sSQL.Append(" , sd_upload_date ") 57 sSQL.Append(" , sd_mime_type ") 58 sSQL.Append(" ) VALUES ( @sd_document ") 59 sSQL.Append(" , @sd_title ") 60 sSQL.Append(" , @sd_file_name ") 61 sSQL.Append(" , @sd_upload_date ") 62 sSQL.Append(" , @sd_mime_type ") 63 sSQL.Append(" ) ") 64 65 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn) 66 67 'sqlCmd.Parameters.AddWithValue("@sd_title", PictureTitle.Text.Trim()) 68 'sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType) 69 'sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName)) 70 'sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now) 71 'sqlCmd.Parameters.AddWithValue("@sd_document", byteArray) 72 73 sqlCmd.Parameters.Add(New SqlParameter("@sd_mime_type", SqlDbType.VarChar)) 74 sqlCmd.Parameters.Add(New SqlParameter("@sd_title", SqlDbType.VarChar)) 75 sqlCmd.Parameters.Add(New SqlParameter("@sd_upload_date", SqlDbType.DateTime)) 76 77 sqlCmd.Parameters.Add(New SqlParameter("@sd_file_name", SqlDbType.VarChar)) 78 79 sqlCmd.Parameters.Add(New SqlParameter("@sd_document", SqlDbType.Image)) 80 81 Dim bArray(UploadedFile.PostedFile.ContentLength - 1) As Byte 82 83 84 85 86 87 88 UploadedFile.PostedFile.InputStream.Read(bArray, 0, UploadedFile.PostedFile.ContentLength) 89 90 sqlCmd.Parameters("@sd_mime_type").Value = UploadedFile.PostedFile.ContentType 91 sqlCmd.Parameters("@sd_title").Value = PictureTitle.Text.Trim() 92 sqlCmd.Parameters("@sd_upload_date").Value = Now 93 94 sqlCmd.Parameters("@sd_file_name").Value = System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName).ToLower 95 96 sqlCmd.Parameters("@sd_document").Value = bArray 97 98 99 sqlConn.Open() 100 sqlCmd.ExecuteNonQuery() 101 sqlConn.Close() 102 103 Else 104 105 'Either the file upload failed or no file was selected 106 107 End If 108 109 End Sub 110
This method atleast gives a different error: "Adobe Reader could not open '132-171510.pdf' because it is either not a supported file type or the file has been damaged (for examplc, it was sent as an email attachment and wasn't correctly decoded)." Please help, it would be appreciated. Thanks
I have learned lots of informative thing from your forums. I have little problem regarding “Display image from SQL Server on ASP.NET� I have done it and image display on my page from SQL Server. I have cleared you here I have adopt two different methods which are following for displaying picture.
but in both above methods I have faced little problem when image display on my page all other information can not display and I also want to display picture on my specific location on the page. My second question is can use any web control like “Image1� to display image from SQL Server where my pictures are stored.
hi,i have inserted the image present in mydocuments using alter commandcreate table aa(a int, d image)insert into aa values (1,'F:prudhviaba 002.jpg')when i doselect * from aai am getting the result in the column d as0x463A5C707275646876695C70727564687669203030322E6A 7067how i can i view the image?pls clarify my doubtsatish
In my asp.net application I have a local report with an image control in thedetail row of the table and the Value attribute set as="File://" & Fields!FQPhotoFileName.ValueThe first row in the table always shows the wrong image and it's always thesame wrong image. The problem is there even when I change the sort order orthe criteria for the underlying dataset. For example, I ran a small testthat populated the dataset with 2 rows and 2 images. When I sort by anycolumn (e.g. ID) in ascending ascending order the ID=1 row (the 1st row)shows the wrong image and the ID=2 row shows the correct image. When I rerunthe report sorting in descending order the ID=2 row (which is now the 1strow) shows the wrong image and the ID=1 shows the correct image.Any suggestions?
Hi, I have a website and i am uploading the gif image to the database. i have used varchar(500) as the datatype and i am saving the file in the webserver so the path to it c:intepub....a.gif
my upload table has the folliwing feilds UploadId Int Identity, Description, FileName, DiskPath varchar(500), weblocation varchar(500). I have a main sproc for the report where i am doing a inner join with other table to get the path of the gif..
So my question is how can i get a picture to show up on the report. . What kinda datatype the gif file should be stored in the database? If it is stored as a varchar how can i access it and what is best way to reference that particular.