Store and Retrieve Word/Image/Excel files from SQL Server 2000

Hi,

To store and retrieve word/Image and Excel files from SQL Server 2000 using C# 2.0.

Please make sure that data is stored as “Image” datatype in the database.

I am going to use File Upload option, that is available in c#. I have converted the document as a byte array and then storing it to the database.

Aspx code is as below:

[CODE]

[/CODE]

Code Behind is as follows:

[CODE]

protected void btnUpload_Click(object sender, EventArgs e)

{

string strSql = “”;
String ConnectionString = strConnectionString;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConnectionString);

conn.Open();

strSql = “insert into tbl_DocUpload (ID,Name,Resume_Data) values(@ID,@Name, @Resume_Data) ;”;SqlCommand InsertCommand = new SqlCommand();

if (FileUpload1.PostedFile != null || (!String.IsNullOrEmpty(FileUpload1.PostedFile.FileName)) || FileUpload1.PostedFile.InputStream != null)

{

Stream fs = FileUpload1.PostedFile.InputStream; string ResumeFileName = Path.GetFileName(FileUpload1.PostedFile.FileName.ToString());

string ResumeContenttype = FileUpload1.PostedFile.ContentType;

int ResumeLength = FileUpload1.PostedFile.ContentLength;

BinaryReader br = new BinaryReader(fs);Byte[] bytes = br.ReadBytes((Int32)fs.Length);

InsertCommand.Parameters.Add(new SqlParameter(“@ID”, SqlDbType.Int));

InsertCommand.Parameters[“@ID”].Value = 1;

InsertCommand.Parameters.Add(new SqlParameter(“@Resume_Data”, SqlDbType.Image));

InsertCommand.Parameters[“@Resume_Data”].Value = (object) bytes;

InsertCommand.Parameters.Add(new SqlParameter(“@Name”, SqlDbType.VarChar, 50));

InsertCommand.Parameters[“@Name”].Value = ResumeFileName;

InsertCommand.CommandText = strSql;

InsertCommand.CommandType = CommandType.Text;

InsertCommand.Connection = conn;

InsertCommand.ExecuteNonQuery(); conn.Close();

}

}

protected void BtnDownload_Click(object sender, EventArgs e)

{

String ConnectionString = strConnectionString;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConnectionString);

conn.Open();

SqlCommand cmdSelect = new SqlCommand(“select Name,Resume_Data from tbl_DocUpload where ID=@ID”, conn);

cmdSelect.Parameters.Add( “@ID”, SqlDbType.Int, 4);

cmdSelect.Parameters[“@ID”].Value = 1;

SqlDataReader reader = cmdSelect.ExecuteReader(CommandBehavior.SingleRow);
while (reader.Read())

{

byte[] barrImg = (byte[])reader[“Resume_Data”];

string StrFileName = reader[“Name”].ToString();

Response.Clear();

Response.Charset = “utf-8″;

Response.AddHeader( “Accept-Header”, barrImg.Length.ToString());

Response.ContentEncoding = System.Text.Encoding.UTF8;

Response.AddHeader(“Accept-Ranges”, “bytes”);

Response.Buffer = true;

Response.AddHeader(“Content-Length”, barrImg.Length.ToString());

Response.AddHeader(“Expires”, “0″);

Response.AddHeader(“Cache-Control”, “must-revalidate, post-check=0, pre-check=0″);

Response.AddHeader(“Pragma”, “public”);

Response.AddHeader(“content-Transfer-Encoding”, “binary”);

Response.AddHeader(“Content-Disposition”, “attachment;filename=” + StrFileName);

Response.BinaryWrite(barrImg);

Response.Flush(); Response.End();

}

reader.Close();

}
[/CODE]
Hope this helps.