Saturday, June 29, 2013

SQL Server Compact Toolbox 3.4–Visual Guide of new features

After nearly 190.000 downloads, version 3.4 of my free, open source SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker


Data compare

The major new feature of this release is the table based data compare feature, that will generate a script with INSERT, UPDATE and DELETE statements to make two tables contains the same data. The two tables must have compatible schemas and same names. The feature works across both SQL Server and SQL Server Compact tables.

To try out this new (beta) feature, right click on a table and select “Compare Data…”:

clip_image002

Select the target database:

clip_image003

A script with the required statements will then open in the SQL editor.

Database Information

This feature will script information about the selected database in the SQL editor, both general information about the database, including Locale ID and case sensitivity, and also list number of rows for all user tables.

clip_image004

clip_image005

Maximum column width in Edit grid

This new option allows you to set a limit on the column width in the edit grid, useful if you have some columns with very long text string, and you want them all to be visible.

clip_image007

After setting the option to for example 200 pixels:

clip_image009

Visual Studio 2013 support

Server Explorer in Visual Studio 2013 no longer supports SQL Server Compact 4.0, and other tools that depend on Server Explorer (DDEX) will no longer work with SQL Server Compact 4.0. However, you can still use the SQL Server Compact Toolbox in Visual Studio 2013, both with version 3.5 and 4.0 database files. The only requirement is that you have the relevant SQL Compact runtime MSIs installed. In addition, the Toolbox supports code generation of LINQ to SQL DataContext classes, both for Windows Phone 7.5/8 and Desktop apps. For Entity Framework, no code generation is required provided you use the Code First workflow.

clip_image010

Other improvements and bug fixes

Validate Connections improved
Merge Replication save properties fixed
CSV import unicode issue fixed + improved error message
milliseconds included in datetime in text results
SQLite script contained GO separators, they are no longer there
Avoided scripting spatial indexes (SQL Server scripting)

Sunday, June 23, 2013

SQL Server Compact Code Snippet of the Week #17 : using wildcards with a parameterized query

This “week”’s code snippet simply demonstrates how to use a parameterized query with LIKE and a search string containing wildcards. The simple solution is basically to add the wildcard character (% or ?) directly to the search string.

public static List<string> GetCompletionList(string prefixText = "%orch%")
{
//TODO Add error handling
List<string> Names = new List<string>();
using (SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\projects\Chinook\Chinook40.sdf"))
{
con.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT Name FROM Artist WHERE Name LIKE @Name", con))
{
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 120).Value = prefixText;
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Names.Add(reader[0].ToString());
}
}
}
}
return Names;
}

Wednesday, June 19, 2013

SQL Server Compact Code Snippet of the Week #16 : detect Entity Framework database type

This weeks code snippet is prompted by a StackOverflow question, regarding how to detect if the database that was backing an Entity Framework DbContext was SQL Server Compact.

My proposed check simply uses the "is" operator to determine if the Database.Connection property is compatible with the SqlCeConnection class.

so my check is:

context.Database.Connection is SqlCeConnection

Monday, June 3, 2013

INSERTing many rows with Entity Framework 6 beta 1 and SQL Server Compact

In this blog post I will demonstrate a couple of improvements for adding many entities to a Entity Framework based database. You can read more about the beta 1 release here, and Julie Lerman highlights some of the features that were available in the alpha here. For all full list of EF 6 features, see the list here.

Here we will look at getting started with Entity Framework 6 beta 1, and a couple of improvements that makes adding many rows to a SQL Server Compact database via Entity Framework feasible, and also have look at using my SqlCeBulkCopy library to do the same.

I will use a console app for this project in order to focus on the Entity Framework code. To get started, launch Visual Studio, and create a new Console Application. Lets call it EF6Test.

image

Now let’s add Entity Framework 6 beta 1 SQL Server Compact package. Launch the NuGet Package Mangager Console (from Tools, Other Windows) and run this command:

PM> Install-Package EntityFramework.SqlServerCompact -Pre

(The –Pre switch allows you to install pre-release packages)

You should now see several messages in the window, the last one being:

Successfully added 'EntityFramework.SqlServerCompact 6.0.0-beta1-20603' to EF6Test.

This process has added a number of DLL references to the project, and added an app.config file to the project, with an entityFramework section that specifies the SQL Server Compact default connection factory:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="System.Data.SqlServerCe.4.0" />
    </parameters>
  </defaultConnectionFactory>
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
  </providers>
</entityFramework>

Now add a using statement:

using System.Data.Entity;
And add the following 2 classes before “class Program”, these define our single test table and our DbContext:
    public class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
}

public class StudentContext : DbContext
{
public DbSet<Student> Students { get; set; }
}

Now add the following code to the Main method:


   1:              Stopwatch sw = new Stopwatch();
   2:              bool useSqlCeBulkCopy = false;
   3:              var students = CreateStudents();
   4:   
   5:              Database.SetInitializer(new DropCreateDatabaseAlways<StudentContext>());
   6:              
   7:              using (var db = new StudentContext())
   8:              {
   9:                  db.Database.Initialize(true);
  10:                  if (!useSqlCeBulkCopy)
  11:                  {
  12:                      sw.Restart();
  13:                      //AddRange rulez, no need for db.Configuration.AutoDetectChangesEnabled = false;
  14:                      db.Students.AddRange(students);
  15:                      sw.Stop();
  16:                      
  17:                      Console.WriteLine(
  18:                          "Added 8000 entities in {0}", sw.Elapsed.ToString());
  19:                      
  20:                      sw.Restart();
  21:                      int recordsAffected = db.SaveChanges();
  22:                      sw.Stop();
  23:   
  24:                      Console.WriteLine(
  25:                          "Saved {0} entities in {1}", recordsAffected, sw.Elapsed.ToString());
  26:                      
  27:                  }
  28:                  Console.ReadKey();
  29:              }
  30:          }
  31:   
  32:          private static List<Student> CreateStudents()
  33:          {
  34:              var students = new List<Student>();
  35:              for (int i = 0; i < 8000; i++)
  36:              {
  37:                  var student = new Student { Name = Guid.NewGuid().ToString() };
  38:                  students.Add(student);
  39:              }
  40:              return students;
  41:          }



The CreateStudents method simply creates a List object with 8000 Student objects. A new database is created on each run (line 5) and the students are added to the StudentContext DbContext, using the excellent new AddRange method, similar to the LINQ to SQL InsertAllOnSubmit method. With EF5 you only had the Add method, and to get reasonable performance, you had to use the cryptic db.Configuration.AutoDetectChangesEnabled = false statement.


With SQL Server Compact and EF5, inserting 8000 rows takes about 58 seconds on my PC, and it may even time out on yours… Thanks to the fact that Entity Framework is now open source on CodePlex I was able to submit  a bug fix, which  got accepted for EF6, so the process now takes about 8 seconds on my PC.


To compare, let’s add the SqlCeBulkCopy NuGet package and perform the same process using that. In the Package Manager Console. type


PM> Install-Package ErikEJ.SqlCeBulkCopy


Before Console.ReadKey, paste the following code:

                else
{
using (SqlCeBulkCopy bcp = new SqlCeBulkCopy(db.Database.Connection.ConnectionString))
{
bcp.DestinationTableName = "Students";
sw.Restart();
bcp.WriteToServer(students);
sw.Stop();

Console.WriteLine(
"Saved {0} entities using SqlCeBulkCopy in {1}", students.Count, sw.Elapsed.ToString());
}
}




And set useSqlCeBulkCopy = true.On my machine this takes about 150 ms! So despite the improvements made in EF6 beta 1, for larger data loads, I suggest you use SqlCeBulkCopy, and as you can see from the code above, it is very easy to integrate in an Entity Framework context.


You can download the completed project from here: http://sdrv.ms/18NaRmW