原文地址:http://aspalliance.com/1083_CodeSnip_How_to_ReadWrite_an_Image_to_Database_in_Binary_Format
[原文×××]
[译者改后×××]


[翻译]代码片断:如何用二进制格式读写图片到数据库


原文发布日期:2006.12.7
作者:Sandeep Acharya
翻译:webabcd


摘要
从这段代码中我们将知道如何把一个图片格式的文件存储到数据库中而不是存储在某文件夹的物理路径中。开发环境为vb.net和ms access。(译者注:我把代码改成c#的了,在vs2005(website)中测试的;数据库用的是sqlserver)



介绍
我们在软件开发中,有相当多的时间花费在如何在UI中显示一些图片。通常,开发人员把图片存储到某个图片文件夹内,然后按要求显示它们。做这项工作,我们需要把图片路径存储到数据库中,而真正的图片文件存储到某个文件夹内。这样我们可以有效的减少数据库的大小,但是这样做将导致大量的磁盘空间被图片文件占用,也使得这些图片可以被轻易的访问到。

让我们来看一个解决方案。在接下来要介绍的代码中我们将知道如何把一个图片文件直接的存储到数据库中,而不是放到磁盘上的某个文件夹里。有一些解决方案使用的是.net和sqlserver数据库,这样就可以使用存储过程了。在这里我们用的是vb.net和ms access,所以我们将无法使用存储过程(译者注:我用vs2005(c#,website)+sqlserver,另:access里是可以使用存储过程的)


系统要求
Microsoft XP Professional
Visual Studio .NET 2003 or Visual Studio 2005


代码片断
我们已经讨论过了,在接下来给出的代码不再将图片存储到文件夹下。但是,我们会将两种存储图片的代码都写一遍。

列表1
VB.NET
Dim img As Image
Sub imgFile_FileOk() Sub imgFile_FileOk(ByVal sender As System.Object, _
                                                     ByVal e As System.ComponentModel.CancelEventArgs) _
                                                     Handles imgFile.FileOk
    Try
    img = Image.FromFile(imgFile.FileName)
    
    btnSave.Enabled = True
    picBox.Image = img
    
    Catch ex As Exception
    If InStr(UCase(ex.Message), UCase("Out of memory")) Then
        MsgBox("Please select p_w_picpaths only", _
                     MsgBoxStyle.OKOnly, "Error Occured")
    End If
    End Try
End Sub
 
c#(译者改)
protected void btnShow_Click(object sender, EventArgs e)
        {
                try
                {
                        // 上传的图片文件到Image
                        System.Drawing.Image img = System.Drawing.Image.FromFile(fileImg.PostedFile.FileName);

                        // 以Jpeg格式存到内存中
                        System.IO.MemoryStream ms = new System.IO.MemoryStream();
                        img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

                        // 输出Response.BinaryWrite(二进制)
                        Response.ClearContent();
                        Response.ContentType = "p_w_picpath/jpeg";
                        Response.BinaryWrite(ms.ToArray());

                        img.Dispose();
                        ms.Dispose();
                        ms.Flush();
                }
                catch (Exception ex)
                {
                        // 判断是否是图片格式文件不应该在这里
                        if (ex.Message == "内存不足。")
                        {
                                Response.Write("请选择图片文件");
                        }
                        else
                        {
                                Response.Write(ex.Message);
                        }
                }
        }
 
分析
上面的这段代码仅仅是在UI上根据图片路径显示一张图片。把代码放到 Try-Catch 块中保证了所读取文件只能是图片文件(译者注:这只是说明性的代码,实际应用中是不应该这样做的)。

接下来让我们来看看本文的要点,下面的这段代码实际上是将一个图片文件以二进制的形式存储在数据库中。

列表2
VB.NET
Sub btnSave_Click() Sub btnSave_Click(ByVal sender As System.Object, _
                                                    ByVal e As System.EventArgs) Handles btnSave.Click
    Dim oConn As OleDb.OleDbConnection
    Dim oComm As OleDb.OleDbCommand
    Dim Err As String
    Dim sInsertQuery As String
    Dim FSO As New System.IO.FileStream(imgFile.FileName, _
                                                                            IO.FileMode.Open, IO.FileAccess.Read)
    Try
    sInsertQuery = "insert into p_w_picpaths([p_w_picpath]) VALUES(?)"
    
    oConn = New OleDb.OleDbConnection( _
                    New Connection().getConnectionString)
    oConn.Open()
    oComm = New OleDb.OleDbCommand(sInsertQuery, oConn)
    Dim imgArrayByte(CType(FSO.Length() - 1, Integer)) As Byte
    FSO.Read(imgArrayByte, 0, imgArrayByte.Length)
    FSO.Close()
    
    Dim QueryParameter As New OleDb.OleDbParameter("@Picture", _
                                                                                                 OleDb.OleDbType.LongVarBinary, _
                                                                                                 imgArrayByte.Length, ParameterDirection.Input, _
                                                                                                 False, 0, 0, Nothing, DataRowVersion.Current, _
                                                                                                 imgArrayByte)
    oComm.Parameters.Add(QueryParameter)
    oComm.ExecuteNonQuery()
    MsgBox("Image is saved successfully to the Database", _
                 MsgBoxStyle.OKOnly, _
                 "Successfully Saved")
    getListOfImages()
    btnSave.Enabled = False
    Catch Ex As Exception
    Err = Ex.Message
    MsgBox("Error : " & Err, _
                 MsgBoxStyle.OKOnly, "Error Occured")
    Finally
    oConn = Nothing
    oComm = Nothing
    End Try
End Sub
 
c#(译者改)
protected void btnSave_Click(object sender, EventArgs e)
        {
                // 上传的图片文件到Image
                System.Drawing.Image img = System.Drawing.Image.FromFile(fileImg.PostedFile.FileName);

                // 以Jpeg格式存到内存中
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

                using (SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.MDF;Integrated Security=True;User Instance=True"))
                {
                        SqlCommand cmd = new SqlCommand("spInsertImage", con);
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.Add("@ImageBinary", System.Data.SqlDbType.Image);
                        // 图片的二进制数据
                        cmd.Parameters["@ImageBinary"].Value = ms.ToArray();

                        con.Open();
                        try
                        {
                                // 调存储过程
                                cmd.ExecuteNonQuery();
                                Response.Write("ok");
                        }
                        catch
                        {
                                Response.Write("error");
                        }
                }

                img.Dispose();
                ms.Dispose();
                ms.Flush();
        }
 
分析
上面这段代码实际上是将图片文件插入到数据库中,接下来我将对这段代码做一些说明。

你能看到变量“sInsertQuery”的初始值是“insert into p_w_picpaths([p_w_picpath]) VALUES(?).”,这是因为我们不能知道完整的查询语句。我们需要增加一个二进制参数,这个参数的值为图片文件转换成二进制后的值。(很明显,这个变量里不能增加这个参数)

因为这个原因,我们使用了一个占位符[?],它将被后来的参数“QueryParameter”代替。

因为需要创建一个二进制的OldDB参数,所以我们使用了FSO来创建。

当成功的创建了这个参数后,占为符[?]将被这个参数所代替并执行。

到现在为止,我们看到的代码实际上就是完成了把图片文件写进数据库的功能。现在,让我们来看看如何从数据库中读出图片文件。

列表3
VB.NET
Sub cmbImgIds_SelectedIndexChanged() Sub cmbImgIds_SelectedIndexChanged(ByVal sender As System.Object, _
                                                                                     ByVal e As System.EventArgs) Handles cmbImgIds.SelectedIndexChanged
    btnDelete.Enabled = False
    Dim sQuery As New System.Text.StringBuilder()
    Dim oDataTable As New DataTable()
    Dim dtRow As DataRow
    Dim p_w_picpathdata() As Byte
    Dim p_w_picpathBytedata As MemoryStream
    sQuery.Append("SELECT p_w_picpath FROM p_w_picpaths WHERE p_w_picpathId = " & _
                                cmbImgIds.SelectedItem)
    oDataTable = New Connection().doDBconnection(sQuery.ToString, "Image")
    
    For Each dtRow In oDataTable.Rows
        btnDelete.Enabled = True
        p_w_picpathdata = dtRow.Item("p_w_picpath")
        p_w_picpathBytedata = New MemoryStream(p_w_picpathdata)
    Next
    picBox.Image = Image.FromStream(p_w_picpathBytedata)
End Sub
 
c#(译者改)
protected void Page_Load(object sender, EventArgs e)
        {
                if (!Page.IsPostBack)
                {
                        InitData();
                }
        }

        void InitData()
        {
                // 数据库中图片的ID绑定到一个DropDownList
                using (SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.MDF;Integrated Security=True;User Instance=True"))
                {
                        SqlDataAdapter sda = new SqlDataAdapter("spSelectImage", con);

                        DataSet ds = new DataSet();

                        sda.Fill(ds);

                        ddlImage.DataSource = ds;
                        ddlImage.DataTextField = "ImageID";
                        ddlImage.DataValueField = "ImageID";
                        ddlImage.DataBind();

                        ddlImage.Items.Insert(0, new ListItem("清选择", "0"));

                        sda.Dispose();
                }
        }

        protected void ddlImage_SelectedIndexChanged(object sender, EventArgs e)
        {
                using (SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.MDF;Integrated Security=True;User Instance=True"))
                {
                        SqlCommand cmd = new SqlCommand("spSelectImage", con);
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.Add("@ImageID", SqlDbType.Int);
                        // 在DropDownList中所选的ImageId
                        cmd.Parameters["@ImageID"].Value = Int32.Parse(ddlImage.SelectedValue);

                        con.Open();
                        SqlDataReader sdr = cmd.ExecuteReader();

                        Response.ClearContent();
                        Response.ContentType = "p_w_picpath/jpeg";
                        while (sdr.Read())
                        {
                                // 读出相应记录的二进制并送显
                                Response.BinaryWrite((byte[])sdr[0]);
                        }

                        sdr.Close();
                        sdr.Dispose();
                }
        }
 
分析
以上代码实际上是从数据库中读取二进制数据,并在UI的picture box中显示成图片。接下来我们讨论一下上面的几行代码。

首先要导入System.IO命名空间以使用MemoryStream。

这里的“Connection()”是一个用户定义的类,它包含一个公共函数“doDBconnection()”。这个公共函数拿到参数和数据库名字之后便执行并返回结果。


下载
[原文×××]
[译者改后×××]


结论
我们已经知道了如何把图片文件以二进制的形式存储到数据库中,而不再使用传统的把图片保存到磁盘的做法。当然,两种方法个有个的优缺点。一方面,不能说把图片存储到数据库中是完全可以接受的,另一方面这样做确实能减少磁盘空间的使用(译者注:难道存在数据库里不占磁盘空间?),而且限制了用户对图片的访问,使得图片更加安全。