如何在连接的环境中从数据库显示图片到图片框. [英] How to show image from database to picture box in connected enviroment.
问题描述
private void button4_Click(object sender, EventArgs e)
{
SqlConnection connection2 = new SqlConnection("Data Source=.;Initial Catalog=VSM;Integrated Security=True");
string q = "select photo from employeeDetails where EmployeeId='" + textBox4.Text + "'";
connection2.Open();
SqlCommand cmd = new SqlCommand(q, connection2);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
Byte[] byteBLOBData = new Byte[0];
byteBLOBData = (Byte[])((byte[])reader["Photo"]);
System.IO.MemoryStream ms = new System.IO.MemoryStream(byteBLOBData);
image2.Image = System.Drawing.Image.FromStream(ms);
connection.Close();
}
}
推荐答案
我不确定您的问题是什么-您没有说-但是您的问题有两点代码.
1)完成连接，命令和阅读器后，应将其丢弃.同样适用于流.
2)不要连接字符串以构建SQL命令.它使您对意外或蓄意的SQL注入攻击敞开大门，这可能会破坏整个数据库.请改用参数化查询.
3)声明byteBLOBData时，无需创建空字节数组.
4)您不应该阅读这样的图像-这意味着流必须存在并且在图像的生命周期内是开放的.
5)Byte和byte相同-无需重铸！
6)如果打开连接，则应关闭它，而不是完全不同的连接！
I''m not sure what your problem is - you don''t say - but there are a couple of things wrong with your code.
1) You should Dispose of your connection, command and reader when you are finished with them. The same applies to streams.
2) Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
3) You don''t need to create an empty byte array when you declare byteBLOBData.
4) You shouldn''t read an image quite like that - it means that the stream must exist and be open for the lifetime of the image.
5)Byteandbyteare the same - you don''t need to recast!
6) If you open a connection, you should close it, not a totally different one!
private void button4_Click(object sender, EventArgs e)
{
using (SqlConnection connection2 = new SqlConnection("Data Source=.;Initial Catalog=VSM;Integrated Security=True"))
{
string q = "select photo from employeeDetails where EmployeeId=@EMPID";
connection2.Open();
using (SqlCommand cmd = new SqlCommand(q, connection2))
{
cmd.Parameters.AddWithValue("@EMPID", textBox4.Text);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
byte[] byteBLOBData =(byte[])reader["Photo"];
image2.Image = ToImage(byteBLOBData);
}
}
}
}
}
/// <summary>
/// Gets the Image from a byte array
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static Image ToImage(byte[] data)
{
if (data == null)
{
return null;
}
Image img;
using (MemoryStream stream = new MemoryStream(data))
{
using (Image temp = Image.FromStream(stream))
{
img = new Bitmap(temp);
}
}
return img;
}
先生，这是添加我使用的图像的代码."
还有你的问题.
"sir this is the code for adding image i used."
And there is your problem.
string Query = "Insert into EmployeeDetails (EmployeeId,Photo)select '" + txtEmployeeId.Text + "','" + pictureBoxEmployee.Image + "'";
您还记得几天前，我说过您不应该将字符串连接起来以形成SQL查询吗?
那正是你在做什么.
这种方法的问题之一(除了让我很容易从世界的另一端删除整个数据库之外)是，当您这样做时，它会给您带来意想不到的结果.
假设txtEmployeeId包含"1234"，而pictureBoxEmployee包含他或她的图像，则上面的语句给出了一个SQL字符串:
Do you remember that a couple of days ago, I said that you shouldn''t concatenate strings to form an SQL query?
That is exactly what you are doing.
One of the problems with that approach (other than it is easy for me to delete your whole database from the other side of the world) is that when you do it can give you results you don''t expect.
Assuming txtEmployeeId contains "1234", and pictureBoxEmployee contains an image of him or her, your statement above gives an SQL string of:
Insert into EmployeeDetails (EmployeeId,Photo)select '1234','System.Drawing.Bitmap'因为Image类的默认ToString实现返回类的名称，而不是图像数据-如您所愿.
参数化您的查询！再也不会再使用串联了！ :D
Because the default ToString implementation for the Image class returns the name of the class, rather than the image data - as you would expect.
Parametrise your query! And never, ever, ever use concatenation again! :D
string Query = "INSERT INTO EmployeeDetails (EmployeeId,Photo) VALUES (@ID, @IM)"; connection.Open();
SqlCommand cmd = new SqlCommand(Query, connection);
cmd.Parameters.AddWithValue("@ID", txtEmployeeId.Text);
cmd.Parameters.AddWithValue("@IM", ImageToByteArray(pictureBoxEmployee.Image));
int added = cmd.ExecuteNonQuery();
****基础知识****
您昨天显示的用于将图像加载到数据库中的代码没有做到这一点.通过将字符串加在一起(称为连接字符串")来构建SQL查询时，不一定能获得所需的结果.
试试吧！将此方法复制到您的代码中，然后运行它，并提供本地硬盘上图像文件的路径:
****The Basics****
The code you showed yesterday for loading an image into the database does no such thing. When you build an SQL query by adding strings together (it''s called "concatenating strings"), you don''t necessarily get what you expect.
Try it! Copy this method into your code, and run it, providing the path to an image file on your local hard disk:
ConcatenationExample(@"D:\Temp\MyPic.jpg");
...
private string ConcatenationExample(string pathToImage)
{
Image image = Image.FromFile(pathToImage);
string text = "This is text";
int number = 98765;
string result = "This is what happens when you add objects to strings: '" + text +
"' Numbers are good (" + number +
"), but images are not: '" + image +
"' See what I mean?";
Console.WriteLine(result);
return result;
}
打印并返回的内容是字符串:
What you get printed and returned is the string:
This is what happens when you add objects to strings: 'This is text' Numbers are good (98765), but images are not: 'System.Drawing.Bitmap' See what I mean?
原因是当您在字符串中添加非字符串时，系统会自动为您调用ToString方法-就像您已经编写了
The reason for this is that when you add non-strings to a string, the system automatically calls the ToString method for you - it''s as if you had written
string result = "This is what happens when you add objects to strings: '" + text +
"' Numbers are good (" + number.ToString() +
"), but images are not: '" + image.ToString() +
"' See what I mean?";
除非一个类显式重写ToString，否则将使用由object创建的默认版本-始终将对象的类型作为字符串返回:
Unless a class overrides ToString explicitly, the default version created by object is used - which always returns the type of the object as a string:
Button.ToString() returns "System.Windows.Forms.Button, Text: Text On The Button"
TextBox.ToString() returns "System.Windows.Forms.TextBox, Text: Text in the Textbox"
SqlCommand.ToString() returns "System.Data.SqlClient.SqlCommand"
然后Image返回其类型名称:"System.Drawing.Bitmap".
因此，当使用串联构建SQL命令时，将构建不包含Image数据的字符串，该字符串包含Image类型作为字符串，因此将其插入数据库中.当您尝试将其读出并将其转换回图像时，它会失败，因为字符串"System.Drawing.Bitmap"不是有效的图像！
有一种解决方法，它称为参数化查询":您告诉命令要使用什么变量，然后将值和值传递给变量.该值作为实际数据传递，而不必作为字符串传递，因此不需要转换.
为此，您可以像往常一样设置基于字符串的命令:
And an Image returns the name of it''s type: "System.Drawing.Bitmap".
So, when you use concatenation to build an SQL command, you build a string that doesn''t contain the Image data, it contains the Image type as a string, so that is what is inserted to your database. When you try to read that out and convert it back to an image it fails, because the string "System.Drawing.Bitmap" is not a valid image!
There is a way round this, and it is called a "Parametrized Query": You tell the command what variables to expect and then pass it the name of the variable, with the value. The value is passed as the actual data, not necessarily as a string, so no conversion is needed.
To do this, you set up a string based command as usual:
string Query = "INSERT INTO EmployeeDetails (EmployeeId,Photo) VALUES (@ID, @IM)";但是不提供值，而是提供变量名:在这种情况下，是@ID和@IM .
然后像正常一样将字符串传递给新命令:
But instead of providing the values, you provide the variable names instead: @ID and @IM in this case.
You then pass that string to the new command, just like normal:
SqlCommand cmd = new SqlCommand(Query, connection);然后您可以传入每个变量的实际值:
You can then pass in the actual values of each variable:
cmd.Parameters.AddWithValue("@ID", txtEmployeeId.Text);将@ID变量设置为文本框的内容.
Sets the @ID variable to the content of the Textbox.
cmd.Parameters.AddWithValue("@IM", ImageToByteArray(pictureBoxEmployee.Image));从PicturBox控件中获取图像，并将其传递给名为ImageToByteArray的方法，该方法将其转换为字节数组，并将@IM变量的值设置为字节数组. br/>
执行SQL命令时:
Takes the Image from your PicturBox control, passes it to a method called ImageToByteArray which converts it to an array of bytes, and sets the value of the @IM variable to the array of bytes.
When you execute the SQL command:
int added = cmd.ExecuteNonQuery(); SQL Server查看命令，找出两个变量并替换值你给了.然后，它将基于字符串的ID插入到EmployeeId字段中，并将字节数组(实际图像数据)插入到Photo字段中.
(这也意味着顽皮的人不能在您的文本框中输入信息并使用它来破坏您的数据库...这就是为什么您应该始终使用参数化查询的原因.)
现在有意义吗，还是您需要更多?我不介意您这样做，了解并正确处理它很重要.
长官，我想知道的一件事是"
SQL Server looks at the command, spots the two variables and substitutes the values you gave. It then inserts a string based ID into your EmployeeId field, and an array of bytes (the actual image data) into your Photo field.
(It also means that naughty people can''t enter information in your text box and use it to destroy your database...which is why you should always use parametrized queries.)
Does that make sense now, or do you need more? I don''t mind if you do, it is important to understand this, and get it right.
sir one thing i want to know at beigning my code was "
string q = "select photo from employeeDetails where EmployeeId='" + textBox4.Text + "'";
connection2.Open();
SqlCommand cmd = new SqlCommand(q, connection2);
SqlDataReader reader = cmd.ExecuteReader();
此代码也可以很好地用于插入数据，然后您为什么要改成这个"
this code also work fine for inserting data then why u have changed to this "
string q = "select photo from employeeDetails where EmployeeId=@EMPID";
connection2.Open();
using (SqlCommand cmd = new SqlCommand(q, connection2))
{
cmd.Parameters.AddWithValue("@EMPID", textBox4.Text);
using (SqlDataReader reader = cmd.ExecuteReader())
{
为什么使用参数?
基本上是因为那里有非常讨厌的人.
让我回到舞台左右.
您有一个文本框，用户可以在其中输入员工ID，是吗?
通过将它们添加在一起来构建SQL字符串时，无论用户键入什么内容，都将输入到SQL命令中，然后将其传递给SQL Server，后者将盲目地执行它.
如果用户不输入员工编号怎么办?或更糟糕的是，键入的不仅仅是员工ID?
从本要点开始，除非您已备份数据库，否则请不要尝试使用此代码.
为什么?因为我们正在进入"SQL注入"或"Bobby Tables"攻击的阴暗世界，这也众所周知.
您知道SQL命令可以链接在一起吗?因为它们可以，而且这是一个非常有用的功能-例如，它使您可以在单个命令中进行多个更新.
所以呢?如果用户知道该怎么办?也许他应该输入EmployeeId"1234"，但实际上输入
Why we use parameter?
Basically, because there are very nasty people out there.
Let me go back a stage or so.
You have a textbox, into which the user enters the employee ID, yes?
When you build your SQL string by adding it together, whatever the user typed goes into your SQL command, and you pass it to SQL Server which blindly executes it.
What if the user doesn''t type in an employee id? Or worse, types more than just an employee id?
FROM THIS POINT IN DO NOT TRY ANY OF THIS CODE UNLESS YOU HAVE BACKED UP YOUR DATABASE.
Why? Because we are entering the murky world of "Sql Injection"...or a "Bobby Tables" attack as it is also known.
Did you know that SQL Commands can be chained together? Because they can, and it''s a very useful feature - it lets you do multiple updates in a single command for example.
So what? What happens if the user knows that? And perhaps he should enter the EmployeeId "1234" but actually enters
1234';DROP TABLE employeeDetails;-- 汇编您的SQL命令，结果显示为:
You assemble your SQL command and it comes out as:
select photo from employeeDetails where EmployeeId='1234';DROP TABLE employeeDetails;--'
对于SQL，这是三个有效的链接命令:SELECT，DROP TABLE和注释.
当您告诉它执行选择时，它会执行-选择您要的记录并将其准备好返回给您.然后，它遵循第二条命令，并从数据库中删除EmployeeDetails表.然后，它查看第三个命令，将其确定为注释，并忽略其他所有内容.然后，它将准备好的记录发送到您的程序.
在您说没有人会那样做"之前，您是错误的.你最好的伴侣会试一试.您可能已经在考虑在最佳伴侣应用上尝试一下，只是为了看看他的脸...
人们也在现实世界中做到这一点. 2011年是英国人口普查可以在线完成而不是在纸面上完成的第一年，它使用SQL数据库.在网站开放的一个小时内，人们抱怨SQL Injection攻击没有用-因为编写该软件的人们知道他们在做什么，并采取措施确保Bobby Tables不起作用:laugh:
但是它经过尝试，并且尝试了很多.有些网站无法保护自己，因此数据库被盗，篡改或破坏.
如果您始终使用参数化查询，则SQL注入攻击将不起作用.
而且代码也更容易阅读！
To SQL that is three valid, chained commands: A SELECT, a DROP TABLE, and a comment.
When you tell it to do the select, it does - it selects the records you have asked for and gets them ready for return to you. Then it obeys the second command and deletes the EmployeeDetails table from the database. Then it looks at the third command, decides it''s a comment and ignores everything else. It then sends the records it prepared to your program.
Before you say "no-one would do that" you are wrong. Your best mate would try it for a laugh. You are probably already thinking of trying it on your best mates app just to see the look on his face...
People do it in the real world too. 2011 was the first year that the British Census could be completed online instead of on paper, and it used an SQL database. Within an hour of the site opening, people were complaining that SQL Injection attacks didn''t work - because the people who wrote the software knew what they were doing, and took steps to ensure that Bobby Tables had no effect :laugh:
But it was tried, and tried a lot. Some websites don''t protect themselves, and databases are stolen, altered, or destroyed because of it.
If you use parametrized queries at all times, SQL Injection attacks don''t work.
And the code is easier to read as well!
这篇关于如何在连接的环境中从数据库显示图片到图片框.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
