Select IMAGE from SQL BASE C#

Question

1.78/5 (3 votes)

See more:

This code working fine.

Column name for image in sql is data.

In this code I need to put inside picture form database to picturebox

Some help

What I have tried:

private void prijavaAction()  //04.04. final doradjena perfect
       {

           {
               SqlConnection con = new SqlConnection(cs);

               if (textBox1.Text.All(char.IsDigit))

               {
                   string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";// pronaci radnika u bazi
                   using (SqlConnection connection = new SqlConnection(cs))
                   {
                       SqlCommand command = new SqlCommand(queryString, connection);
                       connection.Open();
                       SqlDataReader reader = command.ExecuteReader();



                       string commanda = "select case when (SELECT (DATEDIFF(SECOND, Max(vrijemeprijave), GETDATE()) / 60) from dbo.prijava_radnika where status='prijavljen' and radnik= (" + queryString + ")) < 5 THEN 1 ELSE 0 END";
                       SqlConnection connection3 = new SqlConnection(cs);
                       SqlCommand command2 = new SqlCommand(commanda, connection3);
                       connection3.Open();

                       int broj = Convert.ToInt32(command2.ExecuteScalar());



                       string commanda2 = "select case when (SELECT (DATEDIFF(SECOND, Max(vrijemeodjave), GETDATE()) / 60) from dbo.prijava_radnika where status='odjavljen' and radnik= (" + queryString + ")) < 5 THEN 1 ELSE 0 END";
                       SqlConnection connection4 = new SqlConnection(cs);
                       SqlCommand command4 = new SqlCommand(commanda2, connection4);
                       connection4.Open();

                       int broj2 = Convert.ToInt32(command4.ExecuteScalar());

                       if (broj == 1 || broj2 == 1)

                       {
                           notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
                           messageLabel.Text = "Prijava i odjava su blokirani 5 minuta \nza ovog radnika";
                           textBox1.Text = "";
                       }
                       else
                       {




                           try
                           {
                               if (reader.Read())

                               {

                                   string prijava = "SELECT count(*) from dbo.prijava_radnika where vrijemeprijave is not null and vrijemeodjave is null and radnik = '" + readerresultTextbox.Text + "' and redni_broj is NULL";
                                   SqlCommand provjeraprijaveradnika = new SqlCommand(prijava, con);
                                   con.Open();
                                   int result = Convert.ToInt32(provjeraprijaveradnika.ExecuteScalar());

                                   con.Close();
                                   if ((result == 0))

                                   {
                                       String saveStaff = "INSERT INTO dbo.prijava_radnika (vrijemeprijave, status, radnik,idfakture) VALUES(@vrijemeprijave,'prijavljen', '" + readerresultTextbox.Text + "',@idfakture)";
                                       using (SqlConnection openCon2 = new SqlConnection(cs))
                                       {
                                           using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
                                           {
                                               querySaveStaff.Connection = openCon2;
                                               querySaveStaff.Parameters.Add("@vrijemeprijave", SqlDbType.DateTime).Value = DateTime.Now;
                                               querySaveStaff.Parameters.Add("@idfakture", SqlDbType.Int).Value = id_fakture;
                                               openCon2.Open();
                                               querySaveStaff.ExecuteNonQuery();
                                               openCon2.Close();

                                               notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#2EFF36");
                                           //  messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                                               messageLabel.Text = ("Dobro došli na posao...\n" + reader["ime_prezime"].ToString()) + " \nPrijavljeni ste od\n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                                               readerresultTextbox.Text = (reader["ime_prezime"].ToString());
                                           }
                                       }
                                   }
                                   else
                                   {

                                       String updateStaff = " declare @maxNo integer = 0 select @maxNo = isnull(max(redni_broj), 0) from[dbo].[prijava_radnika] UPDATE[dbo].[prijava_radnika] SET"
                                           + " [vrijemeodjave] = @vrijemeodjave,[redni_broj] = (@maxNo+1),[status] = 'odjavljen' WHERE radnik = '" + readerresultTextbox.Text + "'and [status] = 'prijavljen'";



                                       using (SqlConnection connection1 = new SqlConnection(cs))
                                       {
                                           SqlCommand command1 = new SqlCommand(queryString, connection1);
                                           connection1.Open();
                                           SqlDataReader reader1 = command1.ExecuteReader();
                                           connection.Close();
                                           try
                                           {
                                               if (reader1.Read())
                                               {


                                                   using (SqlConnection openCon3 = new SqlConnection(cs))
                                                   {
                                                       using (SqlCommand queryupdateStaff = new SqlCommand(updateStaff))
                                                       {
                                                           queryupdateStaff.Connection = openCon3;
                                                           queryupdateStaff.Parameters.Add("@vrijemeodjave", SqlDbType.DateTime).Value = DateTime.Now;
                                                           openCon3.Open();
                                                           queryupdateStaff.ExecuteNonQuery();
                                                           openCon3.Close();

                                                       }
                                                   }

                                                   notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FFE11A");
                                                   messageLabel.Text = (reader1["ime_prezime"].ToString()) + " je odjavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                                               }
                                           }
                                           catch { }


                                       }



                                       reader.Close();
                                   }

                               }
                               else
                               {
                                   notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
                                   messageLabel.Text = "Radnik ne postoji u bazi! \nProverite vašu karticu ili probajte ponovo!";
                                   textBox1.Text = "";


                               }
                           }
                           finally
                           {

                           }
                       }



                   }
               }

               else
               {
                   notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
                   messageLabel.Text = "Bar kod nije važeci ili nije bar kod";
                   textBox1.Text = "";
               }





           }
       }

Posted 29-Apr-18 8:52am

Goran Bibic

Updated 29-Apr-18 9:40am

Add a Solution

Comments

Richard Deeming 30-Apr-18 12:11pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

using (SqlConnection connection2 = new SqlConnection(cs))
using (SqlCommand command3 = new SqlCommand("SELECT [data] FROM dbo.radnici WHERE ime_prezime = @ime_prezime", connection2))
{
    command3.Parameters.AddWithValue("@ime_prezime", readerresultTextbox.Text);
    
    // No need to use ExecuteReader for a single value:
    object data = command3.ExecuteScalar();
    if (data != null && !Convert.IsDBNull(data))
    {
        using (var stream = new MemoryStream((byte[])data))
        {
            pictureBox.Image = Image.FromStream(stream);
        }
    }
}

Richard Deeming 30-Apr-18 12:16pm

Various experienced developers have been telling you about this major security vulnerability in your code for at least a month, and you're just ignoring our advice.

I give up. You clearly don't want our help, so why bother asking for it?

3 solutions

Solution 3

As already pointed out you need to handle the data in binary format. For a complete example, have a look at c# - Retrieve Images from sql server database - Stack Overflow[^]

Other issues
- You should use parameters with your queries. As pointed out you're going to end up with problems when just concatenating strings into the SQL statement
- Empty catch block, if an error occurs you omit all exceptions because you have an empty catch block. You should at least show a message to the user to inform about unsuccessful execution
- Multiple connections. It looks like you're opening multiple connections during the execution, why? This only adds unnecessary code and slows down the program

Posted 29-Apr-18 9:41am

Wendelius

Solution 1

On finally I add but I have error

Error CS0029 Cannot implicitly convert type 'string' to 'System.Drawing.Image'

string slika = "SELECT [data] FROM dbo.radnici WHERE ime_prezime = '" + readerresultTextbox.Text + "'";// pronaci radnika u bazi
using (SqlConnection connection2 = new SqlConnection(cs))
{
    SqlCommand command3 = new SqlCommand(slika, connection2);
    connection2.Open();
    SqlDataReader reader4 = command3.ExecuteReader();
    connection2.Close();
    if(reader4.Read())
    {
        pictureBox.Image = (reader4["data"].ToString());
    }
    else
    {
        //
    }
}

Posted 29-Apr-18 9:18am

Goran Bibic

Comments

Richard Deeming 30-Apr-18 12:12pm

If you want to update your question, then click the green "Improve question" link and edit your question.

DO NOT post your update as a "solution".

Goran Bibic 30-Apr-18 12:51pm

Thank you

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Eric Lynch · Accepted Answer · 2018-04-29T09:27:00

Solution 2

You'll have to look in the debugger, but generally the data type for image data is byte[]. So, you're problem is to convert from byte[] to Image. If so, something like the following should do the trick:

C#

using (var stream = new MemoryStream((byte[])reader4["data"]))
  pictureBox.Image = Image.FromStream(stream);

Good luck, hope that helps.

Posted 29-Apr-18 9:27am

Eric Lynch

Comments

Goran Bibic 30-Apr-18 2:21am

Thank you

Goran Bibic 30-Apr-18 2:24am

Litle aditional question...

If is data column un sql null(no have uploaded pictures...)?

To show some default picture or?

Some recomandition

Eric Lynch 30-Apr-18 7:30am

You're welcome. Simply test if reader4["data"] == DBNull.Value. If so, set pictureBox.Image to a different value. I recommend adding an image to the resource file and using it.

Goran Bibic 30-Apr-18 11:46am

Thank you...but if I have multiple workers an dont have all pictures...thanks for help

Eric Lynch 30-Apr-18 14:01pm

Since you accepted this answer, I wanted to take a moment to re-iterate a point made by a couple of other respondents.

You really should make it a habit to use SQL parameters. Eventually, you'll write a web application. If you don't make it a habit, even in your "play" code, you'll eventually make a mistake, and let it slip into your "real" code.

This makes it very, very easy (way too easy) for even an unskilled hacker to make unpleasant things happen. Its one of the top few techniques hackers exploit.

I know this may seem like something not to worry about in your "play" code. However, its really quite dangerous.

This is why the other respondents seem so concerned. They're honestly trying to do you a big favor, by helping you avoid an unpleasant outcome.

Anyhow, end of lecture...I hope you're able to resolve any remaining problems and get your application working the way you want. Good luck and happy coding :)