上传/下载图片到/从SQL Server

• 下载演示项目 - 19 Kb
• 下载数据库脚本 - 0.7 Kb

引言

为了给你的应用程序提供酷炫的图片，你可以使用两种技术（至少）。其中一种是将图片保存在文件夹中，并在数据库或文件中存储每个图片的路径。另一种是将整个文件连同其文件名一起存储到数据库中。

它们各有优缺点

• 如果将文件保存到文件夹中，你可能会意外地从该文件夹中删除文件。如果发生这种情况，你最终会在数据库或配置文件中得到一个损坏的“链接”。但是，硬盘存储空间很便宜，因此你可以负担得起存储大量文件。
• 如果将文件存储到数据库中，则可以使用数据库的安全设置来强制执行安全性。此外，永远不会有损坏的链接。但是，数据库存储空间更昂贵。

另一个想法是，你可以在数据库中保存图像的缩略图以供快速访问，并将实际图片保存在硬盘驱动器上。

当然，每个应用程序都有其特殊性，你必须选择你要使用的哪一种。

好了，别再讨论哲学了！让我们深入了解好东西。

应用程序

将文件上传到数据库的问题并不难。创建表时，你必须在服务器上使用特定的数据类型。此数据类型必须能够存储大量二进制数据。

使用Microsoft SQL Server时，此数据类型称为image。

有关更多信息，请参阅BLOB (二进制大对象)以获取简短定义，并参阅联机丛书以获取完整参考。

客户端必须以二进制格式（字节数组）从文件中获取数据，并以该数组作为参数在服务器上调用过程。

SQL Server过程

在本演示中，我假设我在服务器上有一个名为Pictures的数据库，其中包含一个名为Pictures的表。 此表的结构如下

字段名	字段类型
kFileName	长整型
图片	Image
FileName	Varchar(250)

我还存储了用于上传、下载和检索上传文件列表的存储过程。 这些显示在下面。

用于上传文件：UploadFile

CREATE PROCEDURE [dbo].[UploadFile]
(
 @Picture image,
 @FileName varchar(250),
 @kFileName bigint output
)
AS
insert into Pictures(Picture, FileName) values (@Picture,@FileName)
select @kFileName = SCOPE_IDENTITY()
GO

用于下载文件：DownloadFile

CREATE PROCEDURE [dbo].[DownloadFile]
(
 @kFileName bigint,
 @FileName varchar(250) output
)
AS

select Picture, FileName
 from Pictures
 where kFileName=@kFileName
GO

用于检索上传文件列表：getUploadedFiles

CREATE PROCEDURE [dbo].[getUploadedFiles]AS
Select ltrim(str(kFileName)) + " - " + FileName as Name 
  from Pictures
GO

C# 类 - 带注释

using System;
using System.IO;
using System.Data;
using System.Text;
using System.Data.SqlClient;
/*
 * Autor: Ghiondea Alexandru
 * Date: 08 october 2004
 * Description: Implements methods for uploading and downloading files 
 *  with MS SQL Server
 * */
namespace PicturesInSQLServer
{
 /// <SUMMARY>
 /// This class manages uploads and downloads to and from an SQL Server
 /// </SUMMARY>
 public class TransferPictures
 {
  /// <SUMMARY>
  /// Gets from the server a list of uploaded files into a dataSet
  /// </SUMMARY>
  /// <PARAM name="ds">The dataset</PARAM>
  /// <PARAM name="table">The table in the dataset</PARAM>
  public void GetUploadedFiles(ref DataSet ds, string table)
  {
   //
   // The variables required for connecting to the server.
   //
   SqlConnection conn =null;
   SqlCommand cmd = null;
   SqlDataAdapter da = null;
   // ----------------------------------------------

   try
   {
    //
    // If the table already exists, cleares its content. Else adds a new table.
    //
    if (ds.Tables.Contains(table))
     ds.Tables[table].Clear();
    else
     ds.Tables.Add(table);
    // ----------------------------------------------

    
    //
    // Creates a connection to the database and initilizes the command
    //
    conn = new SqlConnection(ConnectionString());
    cmd = new SqlCommand("getUploadedFiles",conn);
    cmd.CommandType = CommandType.StoredProcedure;
    // ----------------------------------------------

    //
    // Initializes the DataAdapter used for retrieving the data
    //
    da = new SqlDataAdapter(cmd);
    // ----------------------------------------------

    //
    // Opens the connection and populates the dataset
    //
    conn.Open();
    da.Fill(ds,table);
    conn.Close();
    // ----------------------------------------------
   }
   catch(Exception e)
   {
    //
    // If an error occurs, we assign null to the result
    // and display the error to the user,
    // with information about the StackTrace for debugging purposes.
    //
    Console.WriteLine(e.Message + " - " + e.StackTrace);
   }
  }

  /// <SUMMARY>
  /// Uploads a file to the database server.
  /// </SUMMARY>
  /// <PARAM name="fileName">The filename of the picture to be uploaded</PARAM>
  /// <RETURNS>The id of the file on the server.</RETURNS>
  public long UploadFile(string FileName)
  {
   if (!File.Exists(FileName))
   {
    return -1;
   }

   FileStream fs=null;
   try
   {
    #region Reading file

    fs = new FileStream(FileName,FileMode.Open);

    //
    // Finding out the size of the file to be uploaded
    //
    FileInfo fi = new FileInfo(FileName);
    long temp = fi.Length;
    int lung = Convert.ToInt32(temp);
    // ------------------------------------------

    //
    // Reading the content of the file into an array of bytes.
    //
    byte[] picture=new byte[lung];
    fs.Read(picture,0,lung);
    fs.Close();
    // ------------------------------------------
    #endregion
    long result = uploadFileToDatabase(picture,fi.Name);

    return result;
   }
   catch(Exception e)
   {
    Console.WriteLine(e.Message + " - " + e.StackTrace);
    return -1;
   }
  }

  /// <SUMMARY>
  /// Wrapper for downloading a file from a database.
  /// </SUMMARY>
  /// <PARAM name="kFileName">The Unique ID of the file in database</PARAM>
  /// <PARAM name="fileName">The file name as it was stored
  ///                              in the database.</PARAM>
  /// <RETURNS>The byte array required OR null if the ID is not found</RETURNS>
  public byte[] DownloadFile(long kFileName, ref string fileName)
  {
   byte[] result = downloadFileFromDatabase(kFileName, ref fileName);
   return result;
  }

  /// <SUMMARY>
  /// Returns the connection string for connecting to the database
  /// </SUMMARY>
  /// <RETURNS>The Connection string.</RETURNS>
  public static string ConnectionString()
  {
   //
   // We consider that the database is situated
   // on the same computer that runs the program.
   // To connect to a remote server, replace 'Data Source'
   // with the name of that server.
   //
   return "Connect Timeout=600;Integrated Security=SSPI;" + 
          "Persist Security Info=False;Initial Catalog=Pictures;" + 
          "Packet Size=4096;Data Source=" + 
          System.Environment.MachineName.Trim();
  }

  /// <SUMMARY>
  /// Uploades a file to an SQL Server.
  /// </SUMMARY>
  /// <PARAM name="picture">A byte array that contains
  ///        the information to be uploaded.</PARAM>
  /// <PARAM name="fileName">The file name asociated
  ///                  with that byte array.</PARAM>
  /// <RETURNS>The unique ID of the file on the server
  ///              OR -1 if an error occurs.</RETURNS>
  private long uploadFileToDatabase(byte[] picture, string fileName)
  {
   //
   // Defining the variables required for accesing the database server.
   //
   SqlConnection conn = null;
   SqlCommand cmd =null;
   SqlParameter kFileName =null;
   SqlParameter FileName =null;
   SqlParameter pic =null;
   // By default, we assume we have an error. If we succed in uploading
   // the file, we'll change this 
   // to the unique id of the file
   long result=-1; 
    
   try
   {
    //
    // Connecting to database.
    //
    conn = new SqlConnection(ConnectionString());
    cmd = new SqlCommand("UploadFile",conn);
    // We assume there is a stored procedure called UploadFile

    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    // ----------------------------------------------

    //
    // Initializing parameters and assigning
    // the values to be sent to the server
    //
    kFileName = new SqlParameter("@kFileName", 
                    System.Data.SqlDbType.BigInt,8);
    kFileName.Direction = ParameterDirection.Output;
    // This parameter does not have a size because
    // we do not know what the size is going to be.
    pic = new SqlParameter("@picture",SqlDbType.Image); 
    pic.Value = picture; 
    
    FileName = new SqlParameter("@FileName",SqlDbType.VarChar,250);
    FileName.Value = fileName;
    // ----------------------------------------------
   
    //
    // Adding the parameters to the database.
    // Remember that the order in which the parameters 
    //  are added is VERY important!
    //
    cmd.Parameters.Add(pic);
    cmd.Parameters.Add(FileName);
    cmd.Parameters.Add(kFileName);
    // ----------------------------------------------

    //
    // Opening the connection and executing the command.
    //
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    // ----------------------------------------------

    //
    // The result is the unique identifier created on the database.
    //
    result = (long)kFileName.Value;
    // ----------------------------------------------

    //
    // Disposing of the objects so we don't occupy memory.
    //
    conn.Dispose();
    cmd.Dispose();
    // ----------------------------------------------
   }
   catch(Exception e)
   {
    //
    // If an error occurs, we report it to the user,
    // with StackTrace for debugging purposes
    //
    Console.WriteLine(e.Message + " - " + e.StackTrace);
    result = -1;
    // ----------------------------------------------
   }

   return result; 
  }


  /// <SUMMARY>
  /// Downloades a file from a database according
  // to the unique id in that database.
  /// </SUMMARY>
  /// <PARAM name="kFile">The ID of the file in the database</PARAM>
  /// <PARAM name="FileName">The filename of the file
  ///                         as it was stored in the database.</PARAM>
  /// <RETURNS>A byte array containing
  ///                            the information required.</RETURNS>
  private byte[] downloadFileFromDatabase(long kFile, ref string FileName)
  {
   SqlConnection conn =null;
   SqlCommand cmd = null;
   SqlParameter kFileName = null;
   SqlParameter fileName = null;
   SqlDataReader dr=null;
   byte[] result=null;

   try
   {
    //
    // Connecting to database.
    //
    conn = new SqlConnection(ConnectionString());
    cmd = new SqlCommand("DownloadFile",conn);
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    // ----------------------------------------------

    //
    // Initializing parameters and assigning the values to be sent to the server
    //
    kFileName= new SqlParameter("@kFileName",System.Data.SqlDbType.BigInt,8);
    kFileName.Value = kFile;
    fileName = new SqlParameter("@FileName",SqlDbType.VarChar,250);
    fileName.Direction = ParameterDirection.Output;
    // ----------------------------------------------
   
    //
    // Adding the parameters to the database.
    // Remember that the order in which the parameters 
    // are added is VERY important!
    //
    cmd.Parameters.Add(kFileName);
    cmd.Parameters.Add(fileName);
    // ----------------------------------------------

    //
    // Opening the connection and executing the command.
    // The idea behind using a dataReader is that,
    // on the SQL Server, we cannot assign to a
    // variable the value of an image field.
    // So, we use a querry to select the record we want 
    // and we use a datareader to read that query.
    // Because we are returnig information based
    // on a primary key, we are always returning
    // only one row of data.
    //
    conn.Open();
    dr = cmd.ExecuteReader();
    dr.Read();
    //
    // We are casting the value returned
    // by the datareader to the byte[] data type.
    //
    result = (byte[])dr.GetValue(0);
    //
    // We are also returning the filename associated with the byte array.
    //
    FileName = (string)dr.GetValue(1);

    //
    // Closing the datareader and the connection
    //
    dr.Close();
    conn.Close();
    // ------------------------------------------

    //
    // Disposing of the objects so we don't occupy memory.
    //
    conn.Dispose();
    cmd.Dispose();
    // ------------------------------------------
   }
   catch(Exception e)
   {
    //
    // If an error occurs, we assign null
    // to the result and display the error to the user,
    // with information about the StackTrace for debugging purposes.
    //
    Console.WriteLine(e.Message + " - " + e.StackTrace);
    result = null;
   }
   return result;    
  }
 }
}

示例应用程序

我还编写了一个小型应用程序来演示如何使用这些方法。 它的屏幕截图如下所示

该应用程序将文件上传到服务器并显示服务器上的文件列表。 双击列表中的文件名时，该文件将被下载并显示在图片框中。

以下是一些相关代码片段

private void UploadedFiles_DoubleClick(object sender, System.EventArgs e)
  {
   //
   // Finds the unique id of the file.
   //
   DataRowView drv = (DataRowView) UploadedFiles.SelectedItem;
   string selectedText = drv.Row["Name"].ToString();
   long id=-1;
   id = long.Parse(selectedText.Substring(0,selectedText.IndexOf(" - ",0)).Trim());

   string filename=null;
   TransferPictures up = new TransferPictures();
   byte[] result = up.DownloadFile(id,ref filename);
   up = null;
   try
   {
    //
    // We cannot assign a byte array directly to an image. 
    // We use MemoryStream, an object that creates a file in memory
    //  and than we pass this to create the image object.
    //
    MemoryStream ms= new MemoryStream(result,0,result.Length);
    Image im = Image.FromStream(ms);
    Picture.Image = im;
   }
   catch(Exception ee)
   {
    MessageBox.Show("An error has occured.\n" + ee.Message);
   }
  }
  private void UploadFile_Click(object sender, System.EventArgs e)
  {
   //
   // Gets the file to be uploaded
   //
   OpenFileDialog ofd = new OpenFileDialog();
   ofd.ShowDialog();
   if (ofd.FileName=="" || !File.Exists(ofd.FileName))
   {
    //
    // If the requested file is not ok...
    //
    return;
   }

   TransferPictures up = new TransferPictures();
   long id =up.UploadFile(ofd.FileName);
   string msg=null;
   if (id >0)
   {
    msg = "Upload succesful";
    LoadInformationFromDataBase();
   }
   else
   { 
    msg = "An error has occured";
   }
   MessageBox.Show(msg);
  }

  private void LoadInformationFromDataBase()
  {
   TransferPictures up = new TransferPictures();
   up.GetUploadedFiles(ref ds,"Pictures");
 
   UploadedFiles.DataSource = ds.Tables["Pictures"];
   UploadedFiles.DisplayMember = "Name";
  }

  private void frmMain_Load(object sender, System.EventArgs e)
  {
   LoadInformationFromDataBase();
  }

结论

嗯，选择哪种类型的图像存储技术取决于设计特定应用程序的人员。 我在这里尝试向你展示如何将它们存储在数据库中。 祝你编码愉快！

参考文献

1. 联机丛书
2. MSDN