在SQL SERVER 2005中存储和检索.ZIP文件

How to store .zip file in SQL SERVER 2005 database programatically?

如何以编程方式在SQL SERVER 2005数据库中存储.zip文件?

And how to retrieve it back?

以及如何找回它?

If you think that storing a .zip file into a database is not an elegant way, please tell me

如果您认为将.zip文件存储到数据库中并不是一种优雅的方式,请告诉我

the idea of making a better approach / standard practice for the same

为此做出更好的方法/标准实践的想法

I am using C#, Asp.net and Sql Server 2005

我正在使用C#,Asp.net和Sql Server 2005

Please send me the code.

请把代码发给我

3 个解决方案

#1

There are a couple of methods that you can employ. The simplest is to leave the files on the filesystem, and to store the file path inside SQL Server. Your app would retrieve the file path at runtime, and use said path to load the file. Storing in the filesystem also has it's disadvantages though - files aren't backed up with the database, changes to the file location, or file deletion won't update SQL Server, and so on.

您可以使用几种方法。最简单的方法是将文件保留在文件系统上,并将文件路径存储在SQL Server中。您的应用程序将在运行时检索文件路径,并使用所述路径加载文件。存储在文件系统中也有它的缺点 - 文件没有备份数据库,文件位置的更改或文件删除都不会更新SQL Server,等等。

Storing within SQL Server is certainly an option as well. You're on SQL Server 2005, so you won't be able to use the FILESTREAM feature (introduced in SQL Server 2008), but you will be able to store it in a native SQL Server blob type.

在SQL Server中存储当然也是一种选择。您使用的是SQL Server 2005,因此无法使用FILESTREAM功能(在SQL Server 2008中引入),但您可以将其存储在本机SQL Server blob类型中。

Here's a good introduction to blob types in SQL Server by Denny Cherry. Here's an example of writing blobs using C#.

这里是Denny Cherry对SQL Server中blob类型的一个很好的介绍。这是使用C#编写blob的示例。

#2

You can store a binary file in a VARBINARY(MAX) column in SQL Server 2005 or 2008. You can also use an IMAGE column (which was the only option until SQL Server 2005) but that will be less performant.

您可以将二进制文件存储在SQL Server 2005或2008中的VARBINARY(MAX)列中。您还可以使用IMAGE列(这是SQL Server 2005之前的唯一选项)但性能较差。

Here's the gist in C# 1.0 compatible code.

这是C#1.0兼容代码的要点。

create table TBL_ZIP_BLOB
(
    ID unqiuidentifier primary key clustered not null
        default newid()
    ,BLOB varbinary(max) not null,
    ,NAME nvarchar(255) not null
)

public void InsertZipBlob(Guid id, byte[] bytes, string name)
{
    SqlDbCommand.CommandText = @"insert into TBL_ZIP_BLOB(BLOB,NAME) values(@blob,@name)";

    using( SqlCommand cmd = MethodToGetValidCommandObject() )
    {
        cmd.CommandText = "insert into TBL_ZIP_BLOB(ID, BLOB,NAME) values(@id,@blob,@name)";
        cmd.Parameters.Add("@id",SqlDbType.UniqueIdentifier).Value  = id;
        cmd.Parameters.Add("@blob",SqlDbType.Image).Value       = bytes;
        cmd.Parameters.Add("@name",SqlDbType.NVarChar,128).Value    = name;
        cmd.ExecuteNonQuery();
    }
}

public void SendZipBlobToResponse(Guid id, HttpResponse response)
{
    byte[] bytes = new byte[0];
    string name = "file.zip";

    using( SqlCommand cmd = MethodToGetValidCommandObject() )   
    {
        cmd.ComandText = "select BLOB,NAME from TBL_ZIP_BLOB where ID = @id";
        cmd.Parameters.Add("@id",SqlDbType.UniqueIdentifier).Value  = id;
        using( IDataReader reader = cmd.ExecuteReader() )
        {
            if( reader.Read() )
            {
                name = (string)reader["NAME"];
                bytes = (byte[])reader["BLOBIMG"];
            }
        }
    }

    if (bytes.Length > 0)
    {
        response.AppendHeader( "Content-Disposition", string.Format("attachment; filename=\"{0}\""),name);
        response.AppendHeader( "Content-Type","application/zip" );

        const int CHUNK = 1024;
        byte[] buff = new byte[CHUNK];

        for( long i=0; i<Bytes.LongLength; i+=CHUNK )
        {
            if( i+CHUNK > bytes.LongLength )
                buff = new byte[Bytes.LongLength-i];
            Array.Copy( Bytes, i, buff, 0, buff.Length );
            response.OutputStream.Write( buff, 0, buff.Length );
            response.OutputStream.Flush();
        }
    }
}

#3

On SQL Server 2005, depending on the size of the zip file, you can use varbinary(max). Here is a decent example.

在SQL Server 2005上,根据zip文件的大小,您可以使用varbinary(max)。这是一个不错的例子。

#1