winform中使用SQLCE备忘

Install SQL Server Compact/SQLite Toolbox
First you need to download and installSQL Server Compact/SQLite Toolbox
Next restart Visual Studio if you had it open

Add a SQLCompact DB to aProject

Open Visual Studio

Create a new project if you don’t already have one
In Visual Studio from the Menu Select Tools–>SQL Server Compact/SQLite Toolbox and this will bring up a new window. The window might tell you to restart VS again, and if so you’ll want to do that.
Next right click on Data Connections and select Add SQL Server Compact 4.0 Connection and then fill in the fieldsFor the Filename field click the create button and choose the root of your project
For Size and Password you can set these to whatever you want, a password is not required
DON’T Forget to copy the connection string which will be helpful in connecting to the DB

compactdb1

Click OK when you are done

Create a Table in the Compact Database

Right click on the myDB.sdf connection that you just created and select Build Table (beta)…
Create a Customers table like below

scriptdb

Click on Script when you are done.
Then click the Execute button to create a table

runscript

Expand myDB.sdf and then expand the Tables folder and then right click on the Customers table and select Edit top 200 rows.

opentable

Add some records to the DB

addrows

Return to your Solutionand add the .SDF file to your project. To do so you will need to click the button to “Show All Files”

showall

Then right click on myDB.df and select “Include in Project”

Add the connection String

In my example, I have a console application so I’m adding the connection string to the App.config file:
`
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="SyncDB" providerName="System.Data.SqlServerCe.4.0" connectionString="Data Source=|DataDirectory|myDB.sdf;Max Database Size=256;Password=mypassword1" />
</connectionStrings>

</appSettings>
`

Query the Compact DB

First Right click on References and select Add Reference…
Add the System.Data.SqlServerCe reference to your project (NOTE: If you don’t see this in your list, Click Browse, and then go to C:\Program Files(x86)\Microsoft SQL Server Compact Edition\V4.0\Private and then select the dll file there).

sqlce

Then add the using statement using System.Data.SqlServerCe
Then youcan query the DB by doing the following:

try
{
using (SqlCeConnection cn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["myDB"].ToString()))
{
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Customers", cn);
cn.Open();
SqlCeDataReader reader1 = cmd.ExecuteReader();
while (reader1.Read())
{
Console.WriteLine(reader1[0] + " | " + reader1[1] + " | " + reader1[2] + " | " + reader1[3] + " | " + reader1[4]);
}
}

}
catch (Exception ex)
{
Console.WriteLine("Error Reading from myDB: " + ex.Message);
return;
}

上述过程中要注意的是SQLCE版本的问题，我的机器是win8.1 64bit，sqlce3.5/4.0的我都安装了，安装好了以后在引用里能够自动看到3.5版本的，4.0的看不到

Paste_Image.png

如果添加4.0的，则引用位置在C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Desktop