Storing Files/Documents in Database

Storing Files/Documents in Database

Steps:

1. Convert files (such as image, document, stream files) as Byte Array  and insert into database in format of BLOB data .

Thus, convert word document into Byte Array before inserting into database, and convert back to file after retrieving from database.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Images]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Images]

GO

CREATE TABLE [dbo].[Images] (

    [docid] [int] IDENTITY (1, 1) NOT NULL ,

    [docname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [document] [image] NOT NULL ,

    [doctype] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Stored Procedure:

CREATE proc sp_adddoc(@docname nvarchar(50), @document image, @doctype nvarchar(50))
as
begin
 insert into Docs(docuid, docname, document, doctype)
  valueS(newid(), @docname, @document, @doctype)
end
GO

ASP.NET code to uploading document to database:

protected void btnUpload_Click(object sender, EventArgs e)
{
    //Validation to make sure that the file 
    //upload control has a valid file
    if (!FileUpload1.HasFile) return;

    //Get the binary array directly from the control
    byte[] binary = new byte[FileUpload1.PostedFile.ContentLength];
    binary = FileUpload1.FileBytes;
    //SQL connection and paramters to dump 
    //the document via stored procedure
    SqlParameter param = null; 
    SqlConnection conn = new SqlConnection(
      ConfigurationManager.ConnectionStrings["menu"].ToString());

    SqlCommand cmd = new SqlCommand("sp_adddoc", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    //Parameters and values 
    param = new SqlParameter("@docname", SqlDbType.NVarChar, 50, 
            ParameterDirection.Input, false, 0, 0, "", 
            DataRowVersion.Default, FileUpload1.FileName); 
    cmd.Parameters.Add(param);
    param = new SqlParameter("@document", SqlDbType.Image);
    param.Direction = ParameterDirection.Input;
    param.Value = binary;
    cmd.Parameters.Add(param);
    param = new SqlParameter("doctype", SqlDbType.NVarChar, 50, 
                ParameterDirection.Input, false, 0, 0, "", 
                DataRowVersion.Default, FileUpload1.PostedFile.ContentType);
    cmd.Parameters.Add(param);
    //Connection open and execute
    conn.Open();
    cmd.ExecuteNonQuery();
}