在WPF应用程序中连接到只读嵌入式SQL Server精简版（.sdf文件） [英] Connection to Read-only embedded SQL Server compact edition (.sdf file) in WPF application
问题描述
我正在寻找以下解决方案：
查找用作嵌入式的SQL Server压缩版文件（.sdf）的格式正确的连接字符串资源，包含WPF应用程序中的引用（即只读）数据。
注意：请注意操作词[构建操作]设置为嵌入资源和不要复制到输出目录设置。这意味着文件不会作为独立实体物理复制到目标计算机，而是嵌入到应用可执行文件中。
到目前为止我测试了一个解决方案，允许从简单的代码片段（清单1）获取.sdf文件从嵌入式资源：
清单1。 / p>
装配_execAssembly = Assembly.GetExecutingAssembly（）;
//帮助程序片段来找出所有可用的嵌入资源名称
string [] _resources = _execAssembly.GetManifestResourceNames（）;
//.sdf包含在IO.Stream中
System.IO.Stream _stream
= Assembly.GetExecutingAssembly（）。GetManifestResourceStream（MyAssemblyName.App_Data.MyDB .sdf）;
...需要其余的代码转换_ code>对象到.sdf并使用 DataSet / TableAdapter 或 System.Data.SqlServerCe 连接到此文件$ c> objects; SqlCeConnection ， SqlCeCommand ， SqlCeDataReader 代码片段（清单2）：
感谢和问候。 这是我在SQL Server Compact Toolbox中使用的代码： I am looking for a solution to the following: 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. ... need the rest of the code to convert _ Listing 2. Thanks and regards. This is the code I use in my SQL Server Compact Toolbox:
这篇关于在WPF应用程序中连接到只读嵌入式SQL Server精简版（.sdf文件）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ #region private：使用SqlCeDataReader获取DataTable
///< summary>
///使用SqlCeDataReader获取DataTable
///< / summary>
///< param name =strConn> string< / param>
///< param name =strSQL> string< / param>
///< return> DataTable< / returns>
private static DataTable GetDataTableFromFileCeReader（string strConn，string strSQL）
{
try
{
使用（SqlCeConnection _connSqlCe = new SqlCeConnection（strConn））
{
using（SqlCeCommand _commandSqlCe = new SqlCeCommand（））
{
_commandSqlCe.CommandType = CommandType.Text;
_commandSqlCe.Connection = _connSqlCe;
_commandSqlCe.CommandText = strSQL;
_connSqlCe.Open（）;
使用（SqlCeDataReader _drSqlCe = _commandSqlCe.ExecuteReader（））{
DataTable _dt = new DataTable（）;
_dt.Load（_drSqlCe）;
_connSqlCe.Close（）;
return _dt;
}
}
}
}
catch {throw; }
}
#endregion
private static string CreateStore（）
{
var factory = System.Data.Common.DbProviderFactories.GetFactory（Resources.SqlCompact35InvariantName）;
string fileName = GetSdfName（）;
string connString = string.Format（Data Source = {0};，fileName）;
bool created = false;
if（！File.Exists（fileName））
{
using（Stream stream = new MemoryStream（Resources.SqlCe35AddinStore））
{
//创建FileStream对象写流到文件
使用（FileStream fileStream = File.Create（fileName，（int）stream.Length））
{
//填充bytes []数组流数据
byte [] bytesInStream = new byte [stream.Length];
stream.Read（bytesInStream，0，（int）bytesInStream.Length）;
//使用FileStream对象写入指定的文件
fileStream.Write（bytesInStream，0，bytesInStream.Length）;
created = true;
}
}
}
使用（var conn = factory.CreateConnection（））
{
if（created）
{
conn.ConnectionString = connString;
conn.Open（）;
using（var cmd = factory.CreateCommand（））
{
cmd.Connection = conn;
cmd.CommandText =CREATE TABLE Databases（ID INT IDENTITY，Source nvarchar（2048）NOT NULL，FileName nvarchar（512）NOT NULL，CeVersion int NOT NULL）;
cmd.ExecuteNonQuery（）;
}
}
}
return connString;
}
私人静态字符串GetSdfName（）
{
string fileName = System.IO.Path.Combine（Environment.GetFolderPath（Environment.SpecialFolder.LocalApplicationData）， SqlCe35AddinStore.sdf）;
return fileName;
}
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");
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):#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
private static string CreateStore()
{
var factory = System.Data.Common.DbProviderFactories.GetFactory(Resources.SqlCompact35InvariantName);
string fileName = GetSdfName();
string connString = string.Format("Data Source={0};", fileName);
bool created = false;
if (!File.Exists(fileName))
{
using (Stream stream = new MemoryStream(Resources.SqlCe35AddinStore))
{
// Create a FileStream object to write a stream to a file
using (FileStream fileStream = File.Create(fileName, (int)stream.Length))
{
// Fill the bytes[] array with the stream data
byte[] bytesInStream = new byte[stream.Length];
stream.Read(bytesInStream, 0, (int)bytesInStream.Length);
// Use FileStream object to write to the specified file
fileStream.Write(bytesInStream, 0, bytesInStream.Length);
created = true;
}
}
}
using (var conn = factory.CreateConnection())
{
if (created)
{
conn.ConnectionString = connString;
conn.Open();
using (var cmd = factory.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = "CREATE TABLE Databases (Id INT IDENTITY, Source nvarchar(2048) NOT NULL, FileName nvarchar(512) NOT NULL, CeVersion int NOT NULL)";
cmd.ExecuteNonQuery();
}
}
}
return connString;
}
private static string GetSdfName()
{
string fileName = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "SqlCe35AddinStore.sdf");
return fileName;
}
