4 Replies - 6022 Views - Last Post: 02 November 2009 - 01:32 PM Rate Topic: -----

#1 MarmiteX1  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 159
  • Joined: 17-October 09

Storing images using SQL Server Compact

Posted 31 October 2009 - 11:12 AM

Hello,

I am trying to store images into a local database with *.sdf extension. I have created a database with one table. The table is to store some WildLife images. I would like to store the title, comment, thumbnail and path information. I've breifly used SQL compact.

I would like to store the images at run time into the database table called Animals and also present the images as thumbnails. The thumbnails part is already been done.

Connection has been declared globally.
SqlCeConnection myConnection = new SqlCeConnection();


In my loop below I create the thumbnails in a method called ShowAnimals_Click

  flowLayoutPanel1.Controls.Clear();
			if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
			{
				String [] files = Directory.GetFiles(folderBrowserDialog1.SelectedPath, "*.jpg");
				foreach(string file in files)
				{

					Image image = Image.FromFile(file);
					PictureBox pb = new PictureBox();
					myConnection.ConnectionString = "Data Source=Animals.sdf;";
					myConnection.Open();
					MessageBox.Show("test connection");
					myConnection.Close();
					MessageBox.Show("Connection closed"); 

			 
					Image theThumb = GetThumbImage(file, 500, 500, true); 
					pb.Size = new Size(100,100)
					pb.Image = theThumb;
					flowLayoutPanel1.Controls.Add(pb);
					//imgThumb.Dispose();
				}





The thing is that when i tested my connection the "test connection" and "connection closed" message box appeared for each image thats been loaded so I can test the connection.

So I then moved the myConnection.close() outisde the loop because I thought it would be a good idea because you dont want to open and close connection for each image when loading picture in picturebox and storing the image in the database or am I wrong? But anyway I got this error message below :(

Quote

InvalidOperationException
Not allowed to change the 'ConnectionString' property while the connection (state=Open)


So could anyone please advise me how I could store the images into the database at run time with my showAnimals method?

Do i need to use a Memorystream to store the images? If so, how could I use a memorystream to store the images to the database in an efficient way please?

Thanks in advance

This post has been edited by MarmiteX1: 31 October 2009 - 11:14 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Storing images using SQL Server Compact

#2 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: Storing images using SQL Server Compact

Posted 31 October 2009 - 12:54 PM

Move the connection string outside the loop. Test if the connection is open before you try to open it.
Was This Post Helpful? 1
  • +
  • -

#3 MarmiteX1  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 159
  • Joined: 17-October 09

Re: Storing images using SQL Server Compact

Posted 01 November 2009 - 08:36 AM

View PostMomerath, on 31 Oct, 2009 - 11:54 AM, said:

Move the connection string outside the loop. Test if the connection is open before you try to open it.


Hi thanks for replying, i moved the connection string outside the foreach loop and when i set up a break point on it it says "my connection is closed". I have also moved the open() statement outisde the foreach loop.

So i then stopped the program and opened Server explorer. I right clicked my database and selected "Open Connection" and the the red cross dissapeard. Then when i click run the cross appears beside database. I then set up a break point on connection string that it says my connection string is empty and an exception was thrown.

Am i doing something wrong here?

By the way in my connection string I have specified the full path to the database. The database is stored on my external HDD.


Attempt 2
However I did the following(see below code) but when I run the program the red cross appears beside my local database just like my previous attempt. I set up a watch point and it seems that the "State" is closed?

Does this mean the database is closed? I did click "Test Connection" in "Add Connection" dialog and it the test did succeed

Also the path to the database now points to my "bin" directory. Is this always a good idea? Because the default Copy to Output directory is set to "Copy if Newer".

String connection = Properties.Settings.Default.AnimalsConnectionString;
	using (SqlCeConnection con = new SqlCeConnection(connection))
	{
		  if (conn.State ==ConnectionState.Closed)
			 {
		conn.Open()
			 }
	}



I set up a Break point and watchpoint and the Exception was thrown

Quote

DatabaseIdentifier = 'conn.DatabaseIdentifier' threw an exception of type 'System.InvalidOperationException'



The connection string wasnt empty either. Why is this happening?

Thanks in advance.

This post has been edited by MarmiteX1: 01 November 2009 - 09:04 AM

Was This Post Helpful? 0
  • +
  • -

#4 Core  Icon User is offline

  • using System.Linq;
  • member icon

Reputation: 774
  • View blog
  • Posts: 5,097
  • Joined: 08-December 08

Re: Storing images using SQL Server Compact

Posted 01 November 2009 - 01:07 PM

I would recommend you building the connection string a bit differently to specify the location (like here).

Also, take a look at this code sample that shows how to correctly retrieve images from SQL Server.
Was This Post Helpful? 1
  • +
  • -

#5 MarmiteX1  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 159
  • Joined: 17-October 09

Re: Storing images using SQL Server Compact

Posted 02 November 2009 - 01:32 PM

View PostCore, on 1 Nov, 2009 - 12:07 PM, said:

I would recommend you building the connection string a bit differently to specify the location (like here).

Also, take a look at this code sample that shows how to correctly retrieve images from SQL Server.


Thanks, sorry but i am still having difficulties setting up the connection successfully. How come everytime i connect it tries to connect to the one in the "bin\debug" folder eventhough there is a sdf file in the top level directory e.g myapp/Animals.sdf as oppose to myapp\myapp\bin\debug\Animals.sdf. Is it because the exe is in the bin folder.

Does the setting for Copy for Output Directory have something to do with this?

The connection strings on that site are confusing to me because I have never connected to SQLServerCompact before. Could you provide me a working example please?

I have done the following but the connection is still closed and the exception still occurs.

			 
string connection = Properties.Settings.Default.MyImagesConnectionString;
SqlCeConnection myConnection = new SqlCeConnection("Data Source=Animals.sdf; Persist Security Info=False;"); 
myConnection.ConnectionString = connection;
myConnection.Open();



Thanks

Thanks.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1