open word document (saved as binary) from database

Question

I'm making a program with c# and sql server and I have a problem , I hope if anyone help me .

I will but the database on pc and the program will be installed in other PCs , and app pcs' program connected to that database.

the program saving documents (word -excel) as binary ,using this code:

  byte[] ReadFile(string sPath)
    {
        //Initialize byte array with a null value initially.
        byte[] data = null;

        //Use FileInfo object to get file size.
        FileInfo fInfo = new FileInfo(sPath);
        long numBytes = fInfo.Length;

        //Open FileStream to read file
        FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

        //Use BinaryReader to read file stream into byte array.
        BinaryReader br = new BinaryReader(fStream);

        //When you use BinaryReader, you need to supply number of bytes to read from file.
        //In this case we want to read entire file. So supplying total number of bytes.
        data = br.ReadBytes((int)numBytes);
        return data;
    }

 private void button1_Click(object sender, EventArgs e)
    {
        string dt = dateTimePicker1.Value.ToShortDateString();

        byte[] red = ReadFile(textBox3.Text);
        con.Open();
        string qry = "insert into documents ([Account no],Name,[Phone number],Date,[Document name],Document,Type) values(@accon,@name,@phone,@date,@docname,@doc,@type)";

        //Initialize SqlCommand object for insert.
        SqlCommand SqlCom = new SqlCommand(qry, con);

        //We are passing Original Image Path and Image byte data as sql parameters.

        SqlCom.Parameters.Add(new SqlParameter("@accon", textBox1.Text));
        SqlCom.Parameters.Add(new SqlParameter("@name", textBox2.Text));
        SqlCom.Parameters.Add(new SqlParameter("@phone", textBox3.Text));
        SqlCom.Parameters.Add(new SqlParameter("@date", dt));
        SqlCom.Parameters.Add(new SqlParameter("@docname", textBox1.Text));
         SqlCom.Parameters.Add(new SqlParameter("@doc", (object)red));

        SqlCom.Parameters.Add(new SqlParameter("@type", (object)textBox2.Text));
        SqlCom.ExecuteNonQuery();
        con.Close();

        MessageBox.Show("done");
    }

the problem : that I don't know how to retrieve saved documents in database and open it with Microsoft word or Microsoft Excel according to their types.

I want to select specific document form database and open it

Thanks in advance

Answer 1

The crux is Response.ContentType:

Response.ContentType = "application/vnd.xls";  // for excel
Response.ContentType = "application/ms-word";  // for word
Response.ContentType = "image/jpg";//for jpg images

It is advised to store content type also in database so that your code will be generic and can display/store any type of file

System.Data.SqlClient.SqlDataReader rdr = null;
System.Data.SqlClient.SqlConnection conn = null;
System.Data.SqlClient.SqlCommand selcmd = null;
try
{
    conn = new System.Data.SqlClient.SqlConnection(
        System.Configuration.ConfigurationManager
        .ConnectionStrings["ConnectionString"].ConnectionString);
    selcmd = new System.Data.SqlClient.SqlCommand(
        "select pic1 from msg where msgid=" + Request.QueryString["imgid"],
        conn);

    conn.Open();
    rdr = selcmd.ExecuteReader();
    while (rdr.Read())
    {
        Response.ContentType = "image/jpg";
        Response.BinaryWrite((byte[])rdr["pic1"]);
    }
    if (rdr != null)
        rdr.Close();
}
finally
{
    if (conn != null)
        conn.Close();
}

Answer 2

After you have retrieved your document from the database (or any type of storage you care to use on the server) you should save the document in the windows temporary folder (Path.GetSpecialFolder) and use the Word Interop library to start word (or excel using its own interop library) with the document you just saved.

var temporayFileName = Path.GetRandomFileName();
var temporaryFileStream = File.Open(temporaryFileName, FileMode.Create);
var memoryStream = documentRepository.Get(...);
memoryStream.CopyTo(temporaryFileStream);

// Word App
dynamic wordApp = new Application { Visible = true };
var doc = wordApp.Documents.Add(TemplateName);
templatedDocument.Activate();

(See this document for more information about starting and manipulating word: http://msdn.microsoft.com/en-us/magazine/ff714583.aspx) .

Answer 3

String connStr = "connection string";

// add here extension that depends on your file type
string fileName = Path.GetTempFileName() + ".doc";

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    using (SqlCommand cmd = conn.CreateCommand())
    {
        // you have to distinguish here which document, I assume that there is an `id` column
        cmd.CommandText = "select document from documents where id = @id";
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                int size = 1024 * 1024;
                byte[] buffer = new byte[size];
                int readBytes = 0;
                int index = 0;

                using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.None))
                {
                    while ((readBytes = (int)dr.GetBytes(0, index, buffer, 0, size)) > 0)
                    {
                        fs.Write(buffer, 0, readBytes);
                        index += readBytes;
                    }
                }
            }
        }
    }
}

// open your file, the proper application will be executed because of proper file extension
Process prc = new Process();
prc.StartInfo.FileName = fileName;
prc.Start();