C# SQL Server CE binary data recovery

Question

I am trying to save and read back pdf files in a SQL Server CE local database. I was able to save them as binary data in an Image column, but I don't know how to read them back and open the pdf file (original format).

Table Schema

Here is how I stored the PDF files:

try
{
    SqlCeConnection con = new SqlCeConnection(@"Data Source = D:\C# Projects\StoreFileSqlCe\StoreFileSqlCe\Test_File_Stotage.sdf");
    con.Open();

    string filePath = textBox1.Text.ToString();
    string filename = Path.GetFileName(filePath);

    FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

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

    fs.Close();

    string strQuery = "insert into tblFiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)";

    SqlCeCommand cmd = new SqlCeCommand(strQuery, con);
    cmd.Parameters.AddWithValue("@Name", filename);
    cmd.Parameters.AddWithValue("@ContentType", "application/vnd.ms-word");
    cmd.Parameters.AddWithValue("@Data",bytes);

    cmd.ExecuteNonQuery();
}            }
catch (Exception ex)
{
    MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
    con.Close();
    con.Dispose();
}

score 0 · Accepted Answer · answered Dec 25 '15 at 03:30

Try this

            try
            {

                SqlCeConnection con = new SqlCeConnection(@"Data Source = D:\C# Projects\StoreFileSqlCe\StoreFileSqlCe\Test_File_Stotage.sdf");
                con.Open();

                string strQuery = "select Data where Name = @Name and ContentType = @ContentType";
                SqlCeCommand cmd = new SqlCeCommand(strQuery, con);

                cmd.Parameters.AddWithValue("@Name", filename);

                cmd.Parameters.AddWithValue("@ContentType", "application/vnd.ms-word");


                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    if (reader.Read())
                    {

                        byte[] pdf = (byte[])reader["Data"];
                        File.WriteAllBytes(filename, pdf);

                    }
                }
            }

C# SQL Server CE binary data recovery

1 Answers1