How to retrieve any kind of files from sql database using C#

Question

I tried a code from the internet and it's working fine. It can upload any kind of file to a SQL database and can retrieve it from SQL database.

But my problem is how can i open any kind of file from SQL database without saving it in the computer.I want to open stored file without saving. like if it is a excel file i want to open it in excel. And then user can save it or not. thank you Here is my code..

private void button6_Click(object sender, EventArgs e)
{
    SaveAttachment(sfdMain, gridViewMain);
    FillDataGrid(gridViewMain, strQuery_AllAttachments);  // refresh grid
}

private void SaveAttachment(SaveFileDialog objSfd, DataGridView objGrid)
{
    string strId = objGrid.SelectedRows[0].Cells["ID"].Value.ToString();
    if (!string.IsNullOrEmpty(strId))
    {
        SqlCommand sqlCmd = new SqlCommand(strQuery_GetAttachmentById, objConn);
        sqlCmd.Parameters.AddWithValue("@attachId", strId);
        SqlDataAdapter objAdapter = new SqlDataAdapter(sqlCmd);
        DataTable objTable = new DataTable();
        DataRow objRow;
        objAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(objAdapter);
        objAdapter.Fill(objTable);
        objRow = objTable.Rows[0];

        byte[] objData;
        objData = (byte[])objRow["attachment"];

        if (objSfd.ShowDialog() != DialogResult.Cancel)
        {
            string strFileToSave = objSfd.FileName;
            FileStream objFileStream =
               new FileStream(strFileToSave, FileMode.Create, FileAccess.Read);
            objFileStream.Write(objData, 0, objData.Length);
            objFileStream.Close();
        }
    }
}

private void button5_Click(object sender, EventArgs e)
{
    if (ofdMain.ShowDialog() != DialogResult.Cancel)
    {
        CreateAttachment(ofdMain.FileName);  //upload the attachment
    }
    FillDataGrid(gridViewMain, strQuery_AllAttachments);  //refresh grid
}

private void CreateAttachment(string strFile)
{
    SqlDataAdapter objAdapter =
        new SqlDataAdapter(strQuery_AllAttachments_AllFields, objConn);
    objAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    SqlCommandBuilder objCmdBuilder = new SqlCommandBuilder(objAdapter);
    DataTable objTable = new DataTable();
    FileStream objFileStream =
        new FileStream(strFile, FileMode.Open, FileAccess.Read);
    int intLength = Convert.ToInt32(objFileStream.Length);
    byte[] objData;
    objData = new byte[intLength];
    DataRow objRow;
    string[] strPath = strFile.Split(Convert.ToChar(@"\"));
    objAdapter.Fill(objTable);

    objFileStream.Read(objData, 0, intLength);
    objFileStream.Close();

    objRow = objTable.NewRow();
    //clip the full path - we just want last part!
    objRow["fileName"] = strPath[strPath.Length - 1];
    objRow["fileSize"] = intLength / 1024; // KB instead of bytes
    objRow["attachment"] = objData;  //our file
    objTable.Rows.Add(objRow); //add our new record
    objAdapter.Update(objTable);
}

What restrict you from simply saving the file into a temporary folder, pass it to shell so it's opened by appropriate app, monitor the process, then re-upload the file if there's any modification? While it's possible to save it into a memory stream and interact with it in your app, other app like Excel isn't designed to do so. Or just see how every other email app like Microsoft's own Outlook do it, they just dump the file into temporary folder, mark it as "Downloaded File", and allow you to edit/save it to other folder. — Martheen, Aug 19 '15 at 09:55
You're already able to save the file right? Just lookup how to [save it into temporary file](http://stackoverflow.com/questions/16656/creating-temporary-folders). If you're feeling generous, you can [send](http://stackoverflow.com/questions/811521/how-do-i-open-a-file-using-the-shells-default-handler) it to shell so the user will have the appropriate app opened. As for the next step (monitoring the file and reupload them), skip it (most email apps don't offer such feature anyway) because otherwise you're creating a cloud storage app, use existing OneDrive/Google Drive API for it — Martheen, Aug 19 '15 at 10:13
"if you're feeling generous, you can send it to shell so the user will have the appropriate app opened" how do i do this — Nidu, Aug 24 '15 at 11:30

score 1 · Answer 1 · answered Aug 19 '15 at 08:35

1

Just as a warning, you may be going down the wrong route. Putting files in your database is rarely the way to go. It can cause you problems, not just in size but speed and load on your database.

Instead, why don't you investigate one of the many options of cloud storage? Azure for example has private blob storage that you can use, and it is built for this purpose. It also has an emulator so you can test it locally.

answered Aug 19 '15 at 08:35

Daniel van Heerden

836
1
7
25

As for the request of this application i need to do it in this way. So please help – Nidu Aug 19 '15 at 08:55

How to retrieve any kind of files from sql database using C#

1 Answers1