Try to load images (categorized by city) from local DB to flowLayoutPanel

Question

I'm trying to build a Form that can insert images to local DB and load them to flowLayoutPanel. Here is the layout of my form, I insert images in 'admin' tab (pic.1).

In 'Browse Photos' tab I try to load photos of a specific city to flowLayoutPanel by clicking the button (pic.2).

Insert images

Load images according to button pressed

My local database (data type)

I inserted photos smoothly but got a problem when I tried to load them.

here is my code:

    public Frm_MyAlbum()
    {
        InitializeComponent();
    }

    private void loadImage (int id, FlowLayoutPanel flp)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Settings.Default.Database1ConnectionString;

                SqlCommand command = new SqlCommand();
                command.CommandText = $"Select * from Photos where ID = {id}";
                command.Connection = conn;

                conn.Open();
                SqlDataReader DR = command.ExecuteReader();

                this.flowLayoutPanel1.Controls.Clear();

                while (DR.Read())
                {
                    byte[] bytes = (byte[])DR["Image"];
                    MemoryStream MS = new MemoryStream(bytes);

                    PictureBox pics;
                    pics = new PictureBox();
                    pics.Image = Image.FromStream(MS);
                    pics.Size = new Size(200, 160);
                    pics.SizeMode = PictureBoxSizeMode.StretchImage;

                    this.flowLayoutPanel1.Controls.Add(pics);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }


    private void GetID(string City, FlowLayoutPanel flp)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Settings.Default.Database1ConnectionString;
                conn.Open();

                SqlCommand command = new SqlCommand();
                command.CommandText = $"Select * from Photos where City = {City}";
                command.Connection = conn;

                
                SqlDataReader DR = command.ExecuteReader();        

                while (DR.Read())
                {
                    loadImage((int)DR["PhotoID"], flp);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }       

    private void button1_Click(object sender, EventArgs e) //button London
    {
        this.flowLayoutPanel1.Controls.Clear();
        GetID("London", flowLayoutPanel1);
    }

    private void button7_Click(object sender, EventArgs e) //button Add to DB
    {
        try
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = Settings.Default.Database1ConnectionString;

                SqlCommand command = new SqlCommand();
                command.CommandText = $"Insert into Photos(City, Description, Image) values(@City, @Desc, @Image)";
                command.Connection = conn;

                byte[] bytes;

                MemoryStream MS = new MemoryStream();
                this.pictureBox1.Image.Save(MS, System.Drawing.Imaging.ImageFormat.Jpeg);
                bytes = MS.GetBuffer();

                command.Parameters.Add("@City", SqlDbType.Text).Value = this.textBox2.Text;
                command.Parameters.Add("@Desc", SqlDbType.Text).Value = this.textBox1.Text;
                command.Parameters.Add("@Image", SqlDbType.Image).Value = bytes;

                conn.Open();
                command.ExecuteNonQuery();
                MessageBox.Show("Adding Successfully");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
    private void button8_Click(object sender, EventArgs e) //button browse
    {
        if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
        {
            this.pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);
        }
    }

When I ran the code, I pressed London (button1) and an exception showed up as "Invalid column name 'London'." VS indicates there's something wrong in the line 'command.CommandText = $"Select * from Photos where City = {City}";'.

the bug

Tried many ways to rewrite it but haven't figure it out. What should I do to solve this issue?

Thank you guys in advance!!

Answer 1

**Short answer: ** the problem is in the commandtext of your query. You also forgot to add the parameter that contains the name of the city

Separate your data from your form (MVVM)

In modern programming there is a tendency to separate your data (=model) from the way that your data is displayed (=view).

Advantages of the separation is that you can reuse the model in other Views, you can change the model without having to change the view. For instance, if you decide to fetch your images from a file instead of a database, your Form won't have to notice. You can also change your View without having to change your model. For instance, if you plan not to show the Image, the model doesn't have to change. Finally, it will be easier to unit test the Model without the Forms. You can mock the data in the Model so you can show your View to others, without real data.

Quite often you need an adapter class, quite often called the ViewModel to connect the Model to the View. Together these three classes are abbreviated as MVVM. Consider to read some background information about MVVM.

Your model

So you need a class that represents your Photo. Probably something similar to this:

class Photo
{
    public int Id {get; set;}
    public string City {get; set;}
    public string Description {get; set;}
    public Image Image {get; set;}
}

Apparently you are able to store Photos somewhere, where you can retrieve them later on, even after you restarted the computer. Such a class is quite often called a Repository (a warehouse where you can store items and retrieve them later on)

interface IPhotoRepository
{
    int AddP(Photo photo);         // returns the Id of the added Photo
    Photo Fetch(int photoId);      // returns Photo with Id or null
    ...
}

Consider to Add functions to Update and Delete Photos. This is out of scope of your question.

You'll also need a method that has as input a string name of a city, and returns all Photos taken in this city:

    IEnumerable<Photo> FetchByCity(string cityName);

Implementation of the interface:

class PhotoRepository : IPhotoRepository
{
    private string ConnectionString => ...

    public IEnumerable<Photo> FetchByCity(string cityName)
    {
        using (var dbConnection = new SqlConnection(this.ConnectionString))
        {
            using (var dbCommand = dbConnection.CreateCommand())
            {
                const string sqlText = "Select Id, Description, Image"
                + " from Photos where City = @City";

                dbCommand.CommandText = sqlText;
                dbCommand.Parameters.AddWithValue("@City", cityName);
                dbConnection.Open();
                
                // execute the command and return the fetched Photos:
                using (var dbReader = dbCommand.ExecuteReader())
                {
                    while (dbReader.Read())
                    {
                        // There is still a fetched row to process:
                        Photo fetchedPhoto = new Phto
                        {
                            Id = dbReader.GetInt64(0),
                            City = cityName,
                            Description = dbReader.GetString(1),
                            Image = (Image) dbReader.GetValue(2),
                            // I'm not not sure how to read an Image
                        };
                        yield return fetchedPhoto;
                    }
                }
            }
        }
    }

    // TODO: implement other methods
}

In your form:

private IPhotoRepository PhotoRepository {get;} ...
// fill this in the constructor with a new PhotoRepository

private ICollection<Photo> FetchPhotosByCity(string cityName)
{
    return this.PhotoRepository.FetchByCity(cityName).ToList();
}

Because you separated your Mode (= classes Photo and PhotoRepository) from your View (= your form), you are able to unit test the database access without the form. You also see that your errors have nothing to do with your form.

If you want to show your Form with some mock data, you just create a class that implements IPhotoRepository and fill it with some mock images without a database.

It is easy to see, that if you later decide to save your Photos in a file, that your form won't have to change. If you add properties to your Photo, the form won't have to change.