Getting started with SQL Server Everywhere

eleooz

引言
This article explains how to programmatically use SQL Server Everywhere (embedded) in a C# program.这篇文章说明如何以编程方式使用SQLServer到处(embedded)在一个C#程序.

Background背景
Microsoft has released an embedded version of SQL Server, called SQL Server Everywhere.Microsoft已发布了一种嵌入式版本的SQLServer,称为SQLServer的四处. It is currently available in a CTP version (Community Technical Preview), and thus not ready for production code.它是目前在一版的CTP(社区技术预览),因此不准备生产代码. It's a small version of SQL Server that can be embedded into a program.这是个小版的SQLServer,可嵌入程序. It's actually a SQL Server Mobile edition that can be used everywhere (no longer limited to the mobile platform).它其实是一个SQLServer的移动版,可以用到处(不再仅限于移动平台).

Installing SQL Server Everywhere安装SQLServer到处
First download SQL Server Everywhere from its product homepage .第一下载SQLServer到处从其产品网页. As of this writing, there is a link in the top right corner to the CTP version.截至记者发稿时止,有一个环节,在右上角的ctp版本. There is also some documentation in a separate download.还有一些文件,在一个单独的下载. Simply download and install as usual.只要下载并安装如常.

There is a blog for SQL Server Everywhere.有一个博客用于SQLServer四处. There is also a FAQ .还有一种常见问题.

Creating a project创建项目
For this sample, I created a C# windows application project in Visual Studio.这样,我创造了一个C#Windows应用项目在VisualStudio. Then, I added some components such as a TextBox for the SQL query, and a DataGridView to show the query results in.然后,我又补充了一些部件,如一个textbox的SQL查询,一个datagridview以显示查询结果.

Adding support for SQL Server Everywhere加入支持的SQLServer到处
Visual Studio needs to know how to access SQL Server Everywhere.视觉工作室需要知道如何获取SQLServer的四处. This is done by adding a reference to the " System.Data.SqlServerCe.dll " file in the Solution Explorer window. The DLL file is located in the folder where SQL Server Everywhere is installed (on my machine, this is " E:\Program Files\Microsoft SQL Server Everywhere\v3.1 ".这是通过增加一个提到"system.data.sqlserverce.dll"档案在解决Explorer窗口.这个DLL文件位于该文件夹中的SQLServer到处安装(我的机器,这是"e:\programfiles\微软SQLServer到处\v3.1".

eleooz

一旦提到补充,所以SQLServer到处分子,可在通常的办法:

// By including the namespace: using System.Data.SqlServerCe; // By direct qualification: System.Data.SqlServerCe.<something>//由包括namespace:usingsystem.data.sqlserverce;//直接学历:system.data.sqlserverce.<something>
Creating a database创建一个数据库
SQL Server Everywhere creates database files to hold databases.SQLServer的到处造成档案数据库进行数据库. A single database is placed in a single file, with the extension " .sdf ". It is possible for a program to access multiple databases in multiple files at once. There are even locking facilities that allow multiple processes and/or programs to access the same database file at the same time (and with the usual concurrency issues).一个单一的数据库是放置在一个单一的档案,以延续".sdf".它可能有一个节目获得多个数据库,在多个文件一次.甚至有锁定设施,允许多进程和/或节目,享受同等的数据库文件在同一时刻(同常见并发问题).

SqlCeEnginesqlceengine
The class SqlCeEngine is used to manage the database.班级sqlceengine用来管理数据库. It allows you to create, modify, and destroy the database file.它允许你创造,修改和破坏数据文件.

For this sample, I use it to create a database:这样,我用它来建立一个数据库:

SqlCeEngine engine = new SqlCeEngine( "Data Source='Test.sdf';" ); if (!(File.Exists( "Test.sdf" )))     engine.CreateDatabase();sqlceengine引擎=新sqlceengine("数据源='test.sdf'";;如果(!(file.exists("test.sdf"))engine.createdatabase();
The first line creates an instance of the engine, and then associates it with a database file " Test.sdf ".第一行创建了一个对发动机,然后同伙同一个数据库文件"test.sdf".

If the file does not exist already, then the second line creates it using the engine.CreateDatabase() call.如果文件不存在了,那么第二线造成它用engine.createdatabase()调用.

Using the database使用数据库
These are the usual steps involved in using a database that is already created:这些都是正常步骤参与用一个数据库,已创建:

Connect to the database.连接到数据库.
Create and execute command.建立和执行命令.
Read results.阅读的结果.
Connecting with SqlCeConnection连接sqlceconnection
The class SqlCeConnection is used to create a connection to the database.班级sqlceconnection是用来建立一个连接到数据库.

SqlCeConnection connection = new SqlCeConnection(engine.LocalConnectionString); connection.Open();sqlceconnectionconnection=新sqlceconnection(engine.localconnectionstring);connection.open();
The first line creates a connection to the database using the same connection string as were used for the engine.第一行创建了连接数据库用相同的连接字符串被用作引擎. If the database is known to exist, there is actually no need to create the engine object just to access the data in the database.如果数据库是已知存在其实是没有必要创造的引擎对象只是为了获得数据库中的数据.

The second line opens the connection so that commands can be issued.第二行打开连接,使指挥部可以发行.

Remember to close the connection again when you are finished with it so that it doesn't unnecessary hold on to resources until garbage collection.记得要关闭连接当你完成它,使之不不必要秉持以资源,直到垃圾收集.

Executing commands with SqlCeCommand执行命令与sqlcecommand
The class SqlCeCommand is used to send commands to the database through the connection.班级sqlcecommand是用来发送命令到数据库,通过连接.

SqlCeCommand command = connection.CreateCommand(); command.CommandText = "SELECT count(*) FROM customer" ; int result = (System.Int32)(command.ExecuteScalar());sqlcecommand指挥=connection.createcommand();command.commandtext="selectcount(*)fromcustomer";intresult=(system.int32)(command.executescalar());
The first line uses the connection to create a command.第一线,用联系创造一个指挥部. This way, the command gets associated with the connection.这样,指挥部取得联系方面.

The second line sets the SQL command to perform.第二行设置SQL命令执行.

Finally, the command is executed against the database.最后,该命令是执行对数据库. The command.ExecuteScalar() call can be used when the query returns a simple value, like int in this example.该command.executescalar()调用可以用来当查询返回一个简单值,如int在这个例子.

Reading complex results with SqlCeDataReader读复杂的结果与sqlcedatareader
The class SqlCeDataReader can be used for query results with multiple rows and/or columns.班级sqlcedatareader可用于查询结果多行和/或栏目.

command.CommandText = "SELECT * FROM customer" ; SqlCeDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { for ( int i = 0  ; i < dataReader.FieldCount; i++)     { string value = dataReader.GetValue(i).ToString();     } }command.commandtext="select*fromcustomer";sqlcedatareaderdatareader=command.executereader();而(datareader.read())(for(inti=0;我"datareader.fieldcount;i++)(stringvalue=datareader.getvalue(一).tostring();
Data must be read one row at a time.数据必须经过一列排开,在一段时间. The dataReader.Read() call returns true as long as a new row can be obtained.该datareader.read()调用返回TRUE,只要是一个新的争论,可. Each row contains dataReader.FieldCount columns.每一行都包含datareader.fieldcount栏目. Each cell value can be read in various formats according to the results (an Int32 value must be read with dataReader.GetInt32() , and so on).每个单元值可以阅读各种格式根据结果(int32值必须阅读datareader.getint32(),等等).

for ( int i = 0 ; i < dataReader.FieldCount; i++) { string column = dataReader.GetName(i); }for(inti=0;我"datareader.fieldcount;i++)(string专栏=datareader.getname(一);
The column names are read with dataReader.GetName() .专栏名称改为与datareader.getname().

Points of interest旖旎
The database file " Test.sdf " is stored in the " \bin\Debug " folder but it is possible to change it. Changes made to the database are persistent since the program only creates the database file when it doesn't exist. To start over with a fresh database, just delete the database file.数据库文件"test.sdf"是存放在"\bin\debug"文件夹,但可以改变它.改动数据库是持久的,因为只有节目创建数据库文件时,它并不存在.重新开始了一个新的数据库,只是删除数据库文件.

The database is stored in a single file, and is not dependent on other files or anything.数据库是储存在一个单一的档案,而不是依赖其他文件,或什么事. This makes it possible to easily copy the database to another place for use in another program.这使得能够轻易拷贝数据库到另一个地方,使用另一个程序.

This article deals only with directly using SQL Server Everywhere from C# source, and not with any GUI component use.这篇文章只处理直接使用SQLServer处处从C#的来源,并没有与任何gui组件使用. Even with an embedded SQL Server, we want to use the tools and components that we are used to for other databases.即使在一个嵌入式SQLServer的,我们要利用各种工具和零件,我们是用来为其他数据库. This should be possible, but is left for another article.这应该是可能的,而且是留给另一篇文章.