Store and Retrieve .ZIP file in SQL SERVER 2005

问题:

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

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

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

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

Please send me the code.

回答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.

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.

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

回答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.

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

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.