Hot File

Getting started with SQL Server Everywhere

View: 737    Dowload: 2   Comment: 0   Post by: hanhga  
Author: none   Category: SQL Server   Fields: Education - Schools

0 point/0 review File has been tested

Download   getting-started-with-sql.zip (13.47 KB)

You need to Sign In to download the file getting-started-with-sql.zip
If you do not have an account then Sign up for free here

Download error   Report copyright violation

This article explains how to programmatically use SQL Server Everywhere (embedded) in a C# program.

Introduction

Background

Microsoft has released an embedded version of SQL Server, called SQL Server Everywhere. It is currently available in a CTP version (Community Technical Preview), and thus not ready for production code. It's a small version of SQL Server that can be embedded into a program. It's actually a SQL Server Mobile edition that can be used everywhere (no longer limited to the mobile platform).

Installing SQL Server Everywhere

First download SQL Server Everywhere from its product homepage. As of this writing, there is a link in the top right corner to the CTP version. There is also some documentation in a separate download. Simply download and install as usual.

There is a blog for SQL Server Everywhere. There is also a FAQ.

Creating a project

For this sample, I created a C# windows application project in Visual Studio. Then, I added some components such as a TextBox for the SQL query, and a DataGridView to show the query results in.

Adding support for SQL Server Everywhere

Visual Studio needs to know how to access SQL Server Everywhere. 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").

Once the reference is added, SQL Server Everywhere elements can be accessed in the usual ways:

// By including the namespace:
using System.Data.SqlServerCe;

// By direct qualification:
System.Data.SqlServerCe.<something>

Creating a database

SQL Server Everywhere creates database files to hold databases. 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).

SqlCeEngine

The class SqlCeEngine is used to manage the database. 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();

The first line creates an instance of the engine, and then associates it with a database file "Test.sdf".

If the file does not exist already, then the second line creates it using the engine.CreateDatabase() call.

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

The class SqlCeConnection is used to create a connection to the database.

SqlCeConnection connection = 
        new 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

The class SqlCeCommand is used to send commands to the database through the connection.

SqlCeCommand command = connection.CreateCommand();
command.CommandText = "SELECT count(*) FROM customer";
int result = (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.

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.

Reading complex results with SqlCeDataReader

The class SqlCeDataReader can be used for query results with multiple rows and/or columns.

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();
    }
}

Data must be read one row at a time. The dataReader.Read() call returns true as long as a new row can be obtained. Each row contains dataReader.FieldCount columns. 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).

for (int i = 0; i < dataReader.FieldCount; i++)
{
    string column = dataReader.GetName(i);
}

The column names are read with 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.

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. Even with an embedded SQL Server, we want to use the tools and components that we are used to for other databases. This should be possible, but is left for another article.

Getting started with SQL Server Everywhere

Getting started with SQL Server Everywhere Posted on 09-04-2014  This article explains how to programmatically use SQL Server Everywhere (embedded) in a C# program. 5/10 737

Comment:

To comment you must be logged in members.

Files with category

  • Insufficient access rights error when doing backup in SQL Server

    View: 421    Download: 0   Comment: 0   Author: none  

    Insufficient access rights error when doing backup in SQL Server

    Category: SQL Server
    Fields: none

    2.25/2 review
    All of sudden SQL Server Management studio gives me the following error message when I'm trying to browse for the backup file location.

  • Migrate MySQL to Microsoft SQL Server

    View: 2493    Download: 0   Comment: 0   Author: none  

    Migrate MySQL to Microsoft SQL Server

    Category: SQL Server
    Fields: none

    0/2 review
    This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

  • COUNT of DISTINCT Rows in SQL Server

    View: 1335    Download: 0   Comment: 0   Author: none  

    COUNT of DISTINCT Rows in SQL Server

    Category: SQL Server
    Fields: Other

    0/4 review
    SQL Server does not support COUNT(DISTINCT *). For example, the below query fails.

  • Finding SQL Servers on the Network

    View: 675    Download: 0   Comment: 0   Author: none  

    Finding SQL Servers on the Network

    Category: SQL Server
    Fields: Other

    0/2 review
    I am a TSQL fanatic. The programs I code are highly dependent upon MS SQL stored procedures. I pay for this love of TSQL when a major overhaul of the system is necessary. Sometimes my code needs to be updated heavily in two places; client and...

  • SQL Server and Database Enumerator

    View: 654    Download: 0   Comment: 0   Author: none  

    SQL Server and Database Enumerator

    Category: SQL Server
    Fields: Other

    0/1 review
    This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has. The class CSQLInfoEnumerator encapsulates this functionality into it.

  • Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    View: 669    Download: 0   Comment: 0   Author: none  

    Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    Category: SQL Server
    Fields: Other

    0/2 review
    The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance....

  • Table Value Parameter in SQL Server 2008

    View: 588    Download: 0   Comment: 0   Author: none  

    Table Value Parameter in SQL Server 2008

    Category: SQL Server
    Fields: Computers - Technology

    0/1 review
    One of the fantastic new features of SQL Server 2008 is the Table value parameter. In previous versions of SQL Server, there wasn’t a native way to pass a table to a Stored Procedure or functions. The usual workaround was to pass a large varchar or...

  • SQL Server DO's and DONT's

    View: 574    Download: 0   Comment: 0   Author: none  

    SQL Server DO's and DONT's

    Category: SQL Server
    Fields: Computers - Technology

    0/0 review
    So, you are now the leader of a SQL Server based project and this is your first one, perhaps migrating from Access. Or maybe you have performance problems with your SQL Server and don't know what to do next. Or maybe you simply want to know of some...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd