Sunday, January 25, 2009

Binding SQL Compact data to WPF in less than 100 lines of code

In this walkthrough, I will show how you can bind a WPF Listview to data from a SQL Compact database table using LINQ to SQL, and also show how persist changes to the list in the SQL Compact database. I am using Visual Studio 2008 SP1 and SQL Compact 3.5 SP1.

Start by creating a new C# WPF project, called SQLCeWPF. Do not create a directory for the solution. You should now have a folder like this:

image 

Then copy the 3.5 Northwind.sdf from %ProgramFiles%\C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples to the project folder (in my case c:\projects\SQLCeWPF).

Then generate the LINQ to SQL dbml file:

1. Open a Visual Studio 2008 Command Prompt (shortcut under Programs, VS 2008, VS Tools)

2. Make the project directory folder the current folder:

cd c:\projects\sqlcewpf

and run this SQLMetal command:

sqlmetal /dbml:Northwind.dbml Northwind.sdf

and close the command window.

This will generate a LINQ to SQL dbml file in the project folder. Now include this file in your project, select Show All Files in Solution Explorer and include the dbml file (do NOT include the sdf file).

In the WPF Designer, add a listbox, a textbox and 3 buttons to the form. Rename the buttons from buttonx to add, delete and save. The form now looks like so in the designer:

image

Add event handlers for the 3 buttons in the code behind file by double clicking each button.

Now add a class file to the project, name it ObservableShippers.cs. This class connects the UI to the LINQ to SQL generated datacontext, and persists changes to the database.

using System;
using System.Linq;
using System.Collections.ObjectModel;
using System.Data.Linq;

namespace SQLCeWPF
{
public class ObservableShippers : ObservableCollection<Shippers>
{
private Northwind dataDC;
private bool ignoreEvents;

public ObservableShippers()
{
this.ignoreEvents = true;
this.dataDC = new Northwind("Data Source=C:\\Projects\\SQLCeWPF\\Northwind.sdf");
var shipperList = from shipper in dataDC.Shippers
orderby shipper.CompanyName
select shipper;
// Cannot add multiple items to ObservableCollection in single step :-(
foreach (Shippers shipper in shipperList)
{
this.Add(shipper);
}
this.ignoreEvents = false;
}

protected override void OnCollectionChanged(System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
{
if (!ignoreEvents)
{
switch (e.Action)
{
case System.Collections.Specialized.NotifyCollectionChangedAction.Add:
foreach (Shippers shipper in e.NewItems)
{
dataDC.Shippers.InsertOnSubmit(shipper);
}
break;
case System.Collections.Specialized.NotifyCollectionChangedAction.Remove:
foreach (Shippers shipper in e.OldItems)
{
dataDC.Shippers.DeleteOnSubmit(shipper);
}
break;
case System.Collections.Specialized.NotifyCollectionChangedAction.Replace:
break;
default:
break;
}
}
base.OnCollectionChanged(e);
}

public void Save()
{
if (this.dataDC != null)
{
this.dataDC.SubmitChanges();
}
}
}
}

 







This class inherits from ObservableCollection, which exposes the OnCollectionChanged event. Handling this events allows synchronization with the LINQ to SQL DataContext.



Now bind the WPF form to this collection like so, in the Window1.xaml.cs file (add the lines in bold):



private ObservableShippers shippers;



public Window1()

{


    InitializeComponent();


    ShowShippers();


}



private void ShowShippers()

{


      shippers = new ObservableShippers();


       this.listBox1.ItemsSource = shippers;


}



Press F5 to run the app, and it should look something like this:



image



(Don’t worry - We fix the Shipper name later).



Now add the button event handler code:



Add button:

if (textBox1.Text.Length > 0)

{


    Shippers newShipper = new Shippers();


    newShipper.CompanyName = textBox1.Text;


    shippers.Add(newShipper);


    textBox1.Text = "";


}



Delete button:

if (this.listBox1.SelectedItem != null)

           {


               shippers.Remove((Shipper)listBox1.SelectedItem);


           }



Save button:

shippers.Save();

ShowShippers();



Now to show the Supplier name in the list, override the Shippers class ToString() method. To do this, add  a new class file to the project, called Shippers.cs with the following content:



namespace SQLCeWPF

{


    public partial class Shippers


    {


        public override string ToString()


        {


            return this.CompanyName;


        }


    }


}



Now try to run the app. Shippers can be added and deleted from the Shippers table using WPF, and the project does not contain any reference to System.Data.SqlServerCe. All in less than 100 lines of (your own) code. The complete sample can be downloaded from here:



http://cid-47e59e4beb70472b.office.live.com/self.aspx/SQL%20Compact/SQLCeWPF.zip



Thanks to these 2 CodeProject articles for inspiration:



http://www.codeproject.com/KB/WPF/binding_in_linq-sql.aspx



and



http://www.codeproject.com/KB/linq/Compact_LINQ.aspx



Technorati Tags: ,

Friday, January 23, 2009

Inserting images from VBA

Inserting images into a SQL Compact from VB(A) does not appear to be possible using vanilla VBA code (please correct me if I am wrong). In order to work around this limitation, it is possible to create a function in C#, and call this function from VBA. This excellent post got me going. This is the VBA Code:

Sub InsertBlob()

Dim IMG_FILE As String
Dim intFile As Integer
Dim ImgBuff() As Byte
Dim ImgLen As Long
Dim ret As String

IMG_FILE = "C:\ErikEJ.jpg"
'Read/Store GIF file in ByteArray
intFile = FreeFile
Open IMG_FILE For Binary As #intFile
ImgLen = LOF(intFile)
ReDim ImgBuff(ImgLen) As Byte
Get #intFile, , ImgBuff()
Close #intFile

Dim sqlCeUtil As New SqlCompactUtility
ret = sqlCeUtil.SaveBlob(ImgBuff(), "Data Source=C:\Data\SQLCE\NorthWind2.sdf", "INSERT INTO Images (Picture) VALUES (@Blob)", "@Blob")

End Sub


First a Byte array with the image contents is created. Then the .NET based method (SaveBlob) is invoked.



The SaveBlob method takes 4 parameters:



1. The byte array



2. The SQL Compact connection string



3. The Insert statement



4. The parameter name



In order for this to work, add a reference to the .NET dll library from the VB(A) project, the code of which is shown below (created per the instructions in the "A Basic Walk Through" part of the post mentioned above). If you have followed the instruction in the blog, the DLL should appear in the Add Reference dialog once built.





using System;
using System.Data.SqlServerCe;
using System.Runtime.InteropServices;

namespace SqlCompactInterop
{
public class SqlCompactUtility
{
//SaveBlob(data, @"Data Source=C:\Data\SQLCE\NorthWind2.sdf", "INSERT INTO Images (Picture, Id) VALUES (@Blob, 2)", "@Blob");
public string SaveBlob(byte[] data, string connectionString, string insertStatement, string parameterName)
{
try
{
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
SqlCeCommand cmd = new SqlCeCommand(insertStatement);
cmd.Parameters.Add(
new SqlCeParameter(parameterName, System.Data.SqlDbType.Image, data.Length));
cmd.Parameters[parameterName].Value = data;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
return string.Empty;
}
}
catch (SqlCeException e)
{
return ShowErrors(e);
}
}

// Error handling routine that generates an error message
private string ShowErrors(SqlCeException e)
{
SqlCeErrorCollection errorCollection = e.Errors;

System.Text.
StringBuilder bld = new System.Text.StringBuilder();
Exception inner = e.InnerException;

if (null != inner)
{
bld.Append(inner.ToString());
}
// Enumerate the errors to a message box.
foreach (SqlCeError err in errorCollection)
{
bld.Append(
"\n Error Code: " + err.HResult.ToString("X"));
bld.Append(
"\n Message : " + err.Message);
bld.Append(
"\n Minor Err.: " + err.NativeError);
bld.Append(
"\n Source : " + err.Source);

// Enumerate each numeric parameter for the error.
foreach (int numPar in err.NumericErrorParameters)
{
if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
}

// Enumerate each string parameter for the error.
foreach (string errPar in err.ErrorParameters)
{
if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);
}


}
return bld.ToString();
}

}

}

 






"Hope this helps"


kick it on DotNetKicks.com

Wednesday, January 21, 2009

ExportSqlCE - new release with support for SQL Compact 3.0/3.1

The post title says it all! - ExportSqlCE release 1.6 is now available at CodePlex. The reason for the new release is support for exporting SQL Server 2005 Mobile Edition (3.0) and SQL Server Compact Edition (3.1) sdf files. Identity columns are not included in the CREATE TABLE statement, as "SET IDENTITY_INSERT" is not available with SQL Compact 3.0/3.1. Support for 3.1 is enabled by ading a new solution and project file to the source, which in turn generates a new exe: ExportSqlCe31.exe. This allows both versions to share a single codebase, but reference different versions of System.Data.SqlServerCe, with conditional compilation symbols. Please try it out and let me know what you think.

Mærker fra Technorati: ,,

Monday, January 19, 2009

ExportSQLCe – support for SQL Compact 3.1

The source (not a release yet) for ExportSQLCe has been updated to support SQL Compact 3.1 – please feel free to try it out.

Mobile Application Pocket Guide v1.1

This guide has just been updated, and has advice on design of Mobile Applications, including advice on using SQL Compact in mobile applications. A very useful guide with a practical approach. Get it at: http://www.codeplex.com/AppArch/Release/ProjectReleases.aspx?ReleaseId=19798

Technorati Tags: ,,

Monday, January 12, 2009

ExportSqlCe version 1.5 released

The latest version of the ExportSqlCe command line utility for scripting entire SQL Compact database files has been released on CodePlex.

Since I last blogged about this utility, the following missing features have been added:

Unicode support – the script file is now in Unicode format, which means that all text data is scripted “properly”, also for non-English text.

Image/binary/varbinary in INSERTs – binary and image fields are now scripted with their proper contents (as a hex string), and not “System.Byte[]”

INSERT with IDENTITY columns – CREATE TABLE now properly scripts IDENTITY columns, and using the SET IDENTITY INSERT feature of SQL Compact 3.5, it is now possible to do inserts on the table with the IDENTITY column.Numeric fraction and precision

INSERT statement improvements – INSERTs are now scripted without single quotes around numeric fields, which improves readability, INSERT performance and script size.
Support for rowversion – rowversion (timestamp) is a read-only column and cannot be updated. Therefore the generated script has been improved to exclude rowversion fields in INSERT statements.
Foreign keys with multiple fields – were not properly scripted as reported (and partly fixed) by user hugo on CodePlex.
Handle merge replicated databases – Merge replicated database files contains a number of system fields and tables. These should not be scripted – this  has been implemented.
Handle large tables – scripts of large tables are now flushed to disk in several files, to save memory.

Go and get the latest release/source, and give feedback at http://www.codeplex.com/ExportSqlCE

Sunday, January 4, 2009

Working with Case Sensitive SQL Compact databases

Up until version 3.5 SP1, SQL Compact have always been case in-sensitive, meaning that a string stored as "Albert" is considered the same as a string stored as "albert" in terms of sorting and selecting etc.

With 3.5 is is now possible to create Case Sensitive SQL Compact databases. This is always done a file creation time, and covers data in all tables in the entire database file.

When working with databases fro SQL Server 2008 Management Studio and VS 2008 SP1, the new database dialog has been updated to include a checkmark for "Case sensitive". In code, add "Case sensitive=true" to the connection string used for creating the database file.

image

image

The following testing is done using SQL 2008 SSMS (RC0).

Let's create a table:

CREATE TABLE CsTest ( TestVal nvarchar(50) NOT NULL )

And add some rows:

INSERT CsTest (TestVal) VALUES ('Albert')
INSERT CsTest (TestVal) VALUES ('albert')

So previously the statement below would have returned 2 rows:

SELECT * FROM CsTest WHERE TestVal = N'albert'

But as the database is case sensitive, only a single row is returned!

If we try to open this database in VS 2008 RTM (with only SQL Compact 3.5 RTM (version 3.5.5386.0) installed) - the engine will throw error 28609:
You are trying to access an older version of a SQL Server Compact 3.5 database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact 3.5 database, run Compact/Repair. (Not extremely helpful)

UPDATE: Converting a database to case sensitive:

This can be done with the Compact command as follows (in this sample Northwind.sdf is converted to a case sensitive database):

SqlCeEngine engine = new SqlCeEngine("Data Source=Northwind.sdf");
engine.Compact("Data Source=; Case Sensitive=True;");

Friday, January 2, 2009

Dynamics Mobile – SQL Compact used in MS Dynamics application

Microsoft has recently released version 1.5 of their Dynamics Mobile solution, which is a set of tools, device applications and server tools to enable synchronization of data between mobile devices and Dynamics AX (formerly Axapta) and Dynamics NAV (formerly Navision). It includes tools to develop your own application as well as a ready made application called “Dynamics Mobile Sales”. It uses SQL Compact 3.5 extensively, such a Merge Replication, and boasts features such as:

Complete no-coding customization of the application (based on XML), on a per-role basis.

Ability to queue web services requests when off-line.

Merge replication of lookup data from NAV and AX ERP servers.

Server based deployment and monitoring tools.

Well worth considering if you need to integrate with one of these backend systems.

Read more here: http://msdn.microsoft.com/en-

Technorati Tags: ,

us/library/bb986981.aspx and http://www.microsoft.com/dynamics/ax/product/mobilesolutions.mspx