CODEWITHSUNDEEP
c#sqlwindowsopenfiledialogsavefiledialog
Nidu
Nidu

Reputation: 11

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

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);
}

Upvotes: 0

Views: 1557

Answers (1)

Daniel van Heerden
Daniel van Heerden

Reputation: 836

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.

Upvotes: 1

Related Questions