We have stored worddocument (office doc/docx files) as varbinary(max) in SQL Server, but when we display its shows data in unformatted way and some encrypted text too...
Document content like:
Description about the document : (a) SQL Server 2016 to be used for all upcoming projects; (b) SQL Server 2016 Enterprise edition will be used on all servers (c) Assumptions: (i) SQL Server will be installed on VM instances Saved in SQL Server with below code: create table Documents(id int identity(1,1), document VARBINARY(MAX)) go --Insert Word document into database INSERT INTO Documents(document) SELECT * FROM OPENROWSET(BULK 'D:\found.docx', SINGLE_BLOB) AS doc select * from documents string sConn = @"server=.; database=DocumentManagement; Integrated Security=True"; SqlConnection objConn = new SqlConnection(sConn); objConn.Open(); SqlCommand objCmd = new SqlCommand("BLOBViewer", objConn); objCmd.CommandType = CommandType.StoredProcedure; SqlDataReader dr = objCmd.ExecuteReader(); while (dr.Read()) { Response.BinaryWrite((byte[])dr["Document"]); } Response.Flush(); Response.Close(); Response.End(); 2 Answers
Answers 1
Native .NET does not support reading word documents. You'll have to use some third party tools to achieve this. We mostly use Aspose.Word It allows you to even do more than just reading its content; you can manipulate it too. But there is a price to it.
If you are looking for something free then I'd suggest you to convert your word documents into PDF and then store them in DB. There are tons of free libraries you can use to read a PDF file's content. PDFSharp and iTextSharp are just two examples.
Answers 2
This is how you can neatly download a file. This is assuming it is stored correctly in SQL.
However this only works for 1 file at a time. You cannot select all files with select * from documents and expect them to be downloaded as individual files, it does not work like that. So you have to create a Stored Procedure that selects one file at a time: select * from documents WHERE (Name = 'FileName') or something similar.
//declare an empty byte array and filename byte[] bin = new byte[0]; string fileName = ""; //use 'using' to properly close and dispose connections and objects using (SqlConnection objConn = new SqlConnection(sConn)) using (SqlCommand objCmd = new SqlCommand("BLOBViewer", objConn)) { objCmd.CommandType = CommandType.StoredProcedure; objConn.Open(); using (SqlDataReader dr = objCmd.ExecuteReader()) { while (dr.Read()) { bin = (byte[])dr["Document"]; fileName = dr["Name"].ToString(); } } } //check if there is a file if (bin.Length == 0) { Label1.Text = "No file found"; return; } //send the file to the browser Response.ClearHeaders(); Response.Clear(); Response.Buffer = true; Response.ContentType = "application/msword"; Response.AddHeader("content-length", bin.Length.ToString()); Response.AddHeader("content-disposition", "attachment; filename=\"" + fileName + "\""); Response.OutputStream.Write(bin, 0, bin.Length); Response.Flush(); HttpContext.Current.ApplicationInstance.CompleteRequest(); 
0 comments:
Post a Comment