10 Replies - 13536 Views - Last Post: 02 August 2010 - 05:42 PM Rate Topic: -----

#1 Guchie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-May 09

Saving Images into an SQL database using VB.net 2005

Post icon  Posted 15 May 2009 - 08:43 AM

Dear all,
My name is Guchie Francis i have been doing VB.net and SQL programming to design databases but with no images involved. When i involved images, i got the error "No mapping exists from object type System.Drawing.Bitmap to a known managed provider native type"

I have a form with a picture box named Photo, a save button called Save below is the code i used

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click

		Dim fsr As New FileStream(Application.StartupPath & "\" & "user.txt", FileMode.Open)
		Dim r As New StreamReader(fsr)
		Dim FirstLine, SecondLine As String   ', SecondLine As Integer
		FirstLine = r.ReadLine()
		SecondLine = r.ReadLine()
		Dim ConnectionString As String = "Data Source=" & FirstLine & ";user id=sa;password=tested;" & _
		"Initial Catalog=" & SecondLine & ";"
		r.Close()
		Dim con As New SqlClient.SqlConnection(ConnectionString)
		
		con.Open()
		Try
			Dim cmd As New SqlClient.SqlCommand("Sp_AddStaffMember", con)
			cmd.CommandType = CommandType.StoredProcedure
			Dim param As SqlClient.SqlParameter
			
			param = cmd.Parameters.AddWithValue("@Image", Pphoto.Image)


			cmd.ExecuteNonQuery()


		Catch ex As IOException
			MsgBox(ex.Message)
		Catch ex As NullReferenceException
			MsgBox(ex.Message)
		Catch ex As SqlClient.SqlException
			MsgBox(ex.Message)
		Catch ex As ArgumentException
			MsgBox(ex.Message)
		End Try
		con.Close()

	End Sub




when i click the save button i get the error

"No mapping exists from object type System.Drawing.Bitmap to a known managed provider native type" as the ArgumentException error
i need help

Is This A Good Question/Topic? 0
  • +

Replies To: Saving Images into an SQL database using VB.net 2005

#2 Joranth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 09-October 08

Re: Saving Images into an SQL database using VB.net 2005

Posted 15 May 2009 - 09:35 AM

Try something like

_image = System.IO.File.ReadAllBytes(_ImageFileName)

mySQLParameter = New SqlClient.SqlParameter("@ImageData", SqlDbType.Image, _image.Length)
mySQLParamter= _image
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9487
  • View blog
  • Posts: 35,773
  • Joined: 12-June 08

Re: Saving Images into an SQL database using VB.net 2005

Posted 15 May 2009 - 10:21 AM

what sort of column data type are you using?
Was This Post Helpful? 0
  • +
  • -

#4 Joranth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 09-October 08

Re: Saving Images into an SQL database using VB.net 2005

Posted 15 May 2009 - 10:59 AM

View Postmodi123_1, on 15 May, 2009 - 09:21 AM, said:

what sort of column data type are you using?


An image type column, the main thing is to pass the data as a byte array i.e Dim _image() As Byte
Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Saving Images into an SQL database using VB.net 2005

Posted 15 May 2009 - 11:11 AM

This may help you out :)
Was This Post Helpful? 0
  • +
  • -

#6 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: Saving Images into an SQL database using VB.net 2005

Posted 15 May 2009 - 01:15 PM

View PostGuchie, on 15 May, 2009 - 09:43 AM, said:

Dear all,
My name is Guchie Francis i have been doing VB.net and SQL programming to design databases but with no images involved. When i involved images, i got the error "No mapping exists from object type System.Drawing.Bitmap to a known managed provider native type"

I have a form with a picture box named Photo, a save button called Save below is the code i used

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click

		Dim fsr As New FileStream(Application.StartupPath & "\" & "user.txt", FileMode.Open)
		Dim r As New StreamReader(fsr)
		Dim FirstLine, SecondLine As String   ', SecondLine As Integer
		FirstLine = r.ReadLine()
		SecondLine = r.ReadLine()
		Dim ConnectionString As String = "Data Source=" & FirstLine & ";user id=sa;password=tested;" & _
		"Initial Catalog=" & SecondLine & ";"
		r.Close()
		Dim con As New SqlClient.SqlConnection(ConnectionString)
		
		con.Open()
		Try
			Dim cmd As New SqlClient.SqlCommand("Sp_AddStaffMember", con)
			cmd.CommandType = CommandType.StoredProcedure
			Dim param As SqlClient.SqlParameter
			
			param = cmd.Parameters.AddWithValue("@Image", Pphoto.Image)


			cmd.ExecuteNonQuery()


		Catch ex As IOException
			MsgBox(ex.Message)
		Catch ex As NullReferenceException
			MsgBox(ex.Message)
		Catch ex As SqlClient.SqlException
			MsgBox(ex.Message)
		Catch ex As ArgumentException
			MsgBox(ex.Message)
		End Try
		con.Close()

	End Sub




when i click the save button i get the error

"No mapping exists from object type System.Drawing.Bitmap to a known managed provider native type" as the ArgumentException error
i need help


Hey Guchie,
You may want to refrain from storing the images inside the DBMS, as if you upgrade the DBMS (sql2k5 -> sql2k8) alot of times those binary image fields dont translate very good and images get corrupted. You be best storing them in a protected image folder and making a path reference to them. That way the path is stores as text/string and if you upgrade to a newer version of your DBMS the likely hood of breaking your application that uses that field is reduced and your queries perform alot faster.
Was This Post Helpful? 0
  • +
  • -

#7 Guchie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-May 09

Re: Saving Images into an SQL database using VB.net 2005

Posted 18 May 2009 - 12:38 AM

View Postmodi123_1, on 15 May, 2009 - 09:21 AM, said:

what sort of column data type are you using?


Let me start by saying thank you all for the quick responses ( unfortunately i was out of touch since friday till now Monday)

I my tables the data type for the images column is Image
data type
Was This Post Helpful? 0
  • +
  • -

#8 Guchie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-May 09

Re: Saving Images into an SQL database using VB.net 2005

Posted 18 May 2009 - 01:27 AM

View Postwoodjom, on 15 May, 2009 - 12:15 PM, said:

View PostGuchie, on 15 May, 2009 - 09:43 AM, said:

Dear all,
My name is Guchie Francis i have been doing VB.net and SQL programming to design databases but with no images involved. When i involved images, i got the error "No mapping exists from object type System.Drawing.Bitmap to a known managed provider native type"

I have a form with a picture box named Photo, a save button called Save below is the code i used

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click

		Dim fsr As New FileStream(Application.StartupPath & "\" & "user.txt", FileMode.Open)
		Dim r As New StreamReader(fsr)
		Dim FirstLine, SecondLine As String   ', SecondLine As Integer
		FirstLine = r.ReadLine()
		SecondLine = r.ReadLine()
		Dim ConnectionString As String = "Data Source=" & FirstLine & ";user id=sa;password=tested;" & _
		"Initial Catalog=" & SecondLine & ";"
		r.Close()
		Dim con As New SqlClient.SqlConnection(ConnectionString)
		
		con.Open()
		Try
			Dim cmd As New SqlClient.SqlCommand("Sp_AddStaffMember", con)
			cmd.CommandType = CommandType.StoredProcedure
			Dim param As SqlClient.SqlParameter
			
			param = cmd.Parameters.AddWithValue("@Image", Pphoto.Image)


			cmd.ExecuteNonQuery()


		Catch ex As IOException
			MsgBox(ex.Message)
		Catch ex As NullReferenceException
			MsgBox(ex.Message)
		Catch ex As SqlClient.SqlException
			MsgBox(ex.Message)
		Catch ex As ArgumentException
			MsgBox(ex.Message)
		End Try
		con.Close()

	End Sub




when i click the save button i get the error

"No mapping exists from object type System.Drawing.Bitmap to a known managed provider native type" as the ArgumentException error
i need help


Hey Guchie,
You may want to refrain from storing the images inside the DBMS, as if you upgrade the DBMS (sql2k5 -> sql2k8) alot of times those binary image fields dont translate very good and images get corrupted. You be best storing them in a protected image folder and making a path reference to them. That way the path is stores as text/string and if you upgrade to a newer version of your DBMS the likely hood of breaking your application that uses that field is reduced and your queries perform alot faster.



Woodjom
Thank you very much for the advice,
But that means i create a folder on the server and save the images there. How about backing up the Data Base??
Was This Post Helpful? 0
  • +
  • -

#9 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: Saving Images into an SQL database using VB.net 2005

Posted 18 May 2009 - 06:44 AM

View PostGuchie, on 18 May, 2009 - 02:27 AM, said:

Woodjom
Thank you very much for the advice,
But that means i create a folder on the server and save the images there. How about backing up the Data Base??


Whats your backup procedure. At my company we use Symantec Backup Exec. It backups the whole DBMS, database, system information, and installation; not to mention, same application backsup over 500GB onto a LTO3 tape, and uses only 300GB of the tape, gotta love compression. Also, you might want to get to know your field datatypes for your DBMS and only use the bit/byte size you need and account for potentially at least 5 years of service of the database. By planning for 5 years instead of today, you allow yourself growth and maybe in the future table expansion. By designing your tables in the database based on the bit/byte size you need, you allow yourself to be size reduced and dont have to worry about database size (that much) and can reduce your communication bandwidth between the website and the database.

As far as storage of the images, you can store them anywhere as long as your Web Service has been setup to map to the directory. IIS allows you to have your web files on the local harddrive (network drive) and your image files in another location (local or network). As well, if at all possible, may want to install your DBMS on the same server as your Web Service to reduce communication lag and have, relative, instant access to the data.

It took me about 4 years to get to the point im at, which isnt saying much. And i actually had to recover a database that had the Images stored in a field. When i migrated the images out of the database (retook every single last one of them, ~1000), the database went from 20GB down to 500MB. Albeit the benefit to having the Binary Image information put into the database field is advantages as having it right there, sometimes the size difference will out weigh the benefit and most put them in a image directory for that difference. If your only storing about 50 to 100 images, you will probably be fine with putting them in the database as the storage difference will not be that bad, but once you start hitting the 200+, the difference in database size will start to become real evident. As you probably already know, depending on the image size and type will depict how big the file becomes, and your incase how big the database will grow. You can compress web directories alot easier than you can a database file.

Lastly, its your database and you do what you want, this is just advise. Remember, "....Just because you can, does not always mean you should..."

This post has been edited by woodjom: 18 May 2009 - 06:49 AM

Was This Post Helpful? 0
  • +
  • -

#10 Guest_dreeZ*


Reputation:

Re: Saving Images into an SQL database using VB.net 2005

Posted 02 August 2010 - 04:44 PM

I've heard this for the last 20 years. Save your picture in a folder. BS!! SQL is as fast to retrieve a picture as Windows is - Probably faster since it wont check for types in folder or load dlls based on the os.

You use a database because it's mission critical, fast and secure, not because it is cool. Just use byte[] to save your image. It will be fast, and guaranteed to be there when you need it. Also, you can have as many versions as you have backups.

When sql 3010 comes to scene, you will be able to restore your images too. (more bs the image will be saved as an array of bytes by sql).

create your database field as Image (Sql Type)
convert your image to byte[] when you need to store it, and back to image after you retrieve it. (Not really a conversion, so don't worry about resources, make sure to close your streams and dispose any iDisposable object.

best,
dreeZ
Was This Post Helpful? 0

#11 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9487
  • View blog
  • Posts: 35,773
  • Joined: 12-June 08

Re: Saving Images into an SQL database using VB.net 2005

Posted 02 August 2010 - 05:42 PM

View PostdreeZ, on 02 August 2010 - 05:44 PM, said:

I've heard this for the last 20 years. Save your picture in a folder. BS!! SQL is as fast to retrieve a picture as Windows is - Probably faster since it wont check for types in folder or load dlls based on the os.

You use a database because it's mission critical, fast and secure, not because it is cool. Just use byte[] to save your image. It will be fast, and guaranteed to be there when you need it. Also, you can have as many versions as you have backups.

When sql 3010 comes to scene, you will be able to restore your images too. (more bs the image will be saved as an array of bytes by sql).

create your database field as Image (Sql Type)
convert your image to byte[] when you need to store it, and back to image after you retrieve it. (Not really a conversion, so don't worry about resources, make sure to close your streams and dispose any iDisposable object.

best,
dreeZ


Yes because database sizes are not important any more... :rolleyes:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1