How to: Connect to Read-only embedded SQL Server compact edition (.sdf file) in WPF application?

See more:

embedded

SQL-CE

C#

Windows

, +

Hi, folks,

I am looking for a solution to the following programmatic challenge:

Find a properly formatted connection string to SQL server compact edition file (.sdf) used as embedded resource, containing reference (i.e. read-only) data in WPF application.

Note: please pay attention to the operative words [Build Action] set to "Embedded Resource" and "Do Not Copy" to the output directory settings. It means that the file will not be physically copied to the target computer as a stand-alone entity, but instead is embedded in app executable.

So far I have tested a solution that allows getting the .sdf file from embedded resource with simple code snippet (Listing 1):

Listing 1.

    Assembly _execAssembly = Assembly.GetExecutingAssembly();

    // helper snippet to find out all available embedded resource names
    string[] _resources = _execAssembly.GetManifestResourceNames();

    //.sdf included in IO.Stream

    System.IO.Stream _stream
= Assembly.GetExecutingAssembly().GetManifestResourceStream("MyAssemblyName.App_Data.MyDB.sdf");

.. need the rest of the code to convert _`stream` object to .sdf and to connect to this file using either `DataSet/TableAdapter`, or `System.Data.SqlServerCe` objects; `SqlCeConnection`, `SqlCeCommand`, `SqlCeDataReader` as shown in the following sample code snippet (Listing 2):

Listing 2.

#region private: Get DataTable using SqlCeDataReader
/// <summary>
/// Get DataTable using SqlCeDataReader
/// </summary>
/// <param name="strConn">string</param>
/// <param name="strSQL">string</param>
/// <returns>DataTable</returns>
private static DataTable GetDataTableFromFileCeReader(string strConn, string strSQL)
{
    try
    {
        using (SqlCeConnection _connSqlCe = new SqlCeConnection(strConn))
        {
            using (SqlCeCommand _commandSqlCe = new SqlCeCommand())
            {
                _commandSqlCe.CommandType = CommandType.Text;
                _commandSqlCe.Connection = _connSqlCe;
                _commandSqlCe.CommandText = strSQL;
                _connSqlCe.Open();

                using (SqlCeDataReader _drSqlCe = _commandSqlCe.ExecuteReader()) {
                    DataTable _dt = new DataTable();
                    _dt.Load(_drSqlCe);
                    _connSqlCe.Close();
                    return _dt;
                }
            }
        }
    }
    catch { throw; }
}
#endregion

Thanks and regards.