Thursday, October 23, 2008

Accessing SQL Compact from SQL Server "Linked Server"

SQL Compact users would like to have access to SQL Compact data (read only) from within SQL Server for various reasons: Import the data to a SQL Server table, use the data in Reporting Services, join SQL Compact data with SQL Server data etc. I have made a sample which I will describe in this blog post, that demonstrates two types of access: Using a SQLCLR table-valued function (UDF) and a SQLCLR stored procedure.

Due to security restrictions of SQLCLR, the solution uses the OLEDB provider and not the ADO.NET provider. This is the cause for the following limitations of the sample: "ntext" and "image" columns are not supported, as data in those BLOB type fields cannot be retrieved from the OLDEB provider.

The sample consists of 2 main methods, the one for the table valued UDF is: GetDataFromSqlCe.

        [SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read)]
public static IEnumerable GetDataFromSqlCe(string dataSource)
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" + dataSource);
conn.Open();
using (OleDbCommand cmd = new OleDbCommand("SELECT [Category Id], [Category Name] FROM Categories", conn))
{
// Ensure that the connection is closed when the reader is closed
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}

 











The sample method uses a SQL statement, which output must match the parameters of the FillRow method, meaning you will have to code a function for each table you would like to expose.





 private static void FillRow(Object obj,
out SqlInt32 catId,
out SqlString catName
)
{
DbDataRecord row = (DbDataRecord)obj;
int column = 0;
catId = (row.IsDBNull(column)) ?
SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
catName = (row.IsDBNull(column)) ?
SqlString.Null : new SqlString(row.GetString(column)); column++;
}

 











The advantage of using the UDF approach is that you can use the output from the function as if it was an ordinary SQL Server table, since the output is "strongly typed", so to speak.



The second approach GetTable returns a SQL result set, and works for any table in the sdf file (minus ntext and image fields).





public static void GetTable(String connectionString, String tableName)

 





Based on metadata from INFORMATION_SCHEMA.TABLES a SqlMetaData array is constructed and used for defining the SqlRecord to be returned. The records are then dynamically built and returned.



To test the sample, do as follows:



1. Download the zip file below with the source code for the SQLCLR dll file.



2. Open and compile in Visual Studio 2008



3. Use the indcluded install.sql script to configure, install and test the SQLCLR dll on your SQL Server instance.



Hope you will find this useful...

















Tuesday, October 21, 2008

Going to PDC

I am attending PDC in Los Angeles. There are a few sessions relating to SQL Compact, that I will consider attending:

SQL Server Compact: Embedding in Desktop and Device Applications
Wed 10/29 | 3:00 PM-4:15 PM | 402A
Presenter: Steve Lasker

Learn how SQL Server Compact can be used to create data files for your applications, run applications directly from DVD, capture user activity, and sync "back home." Learn the different deployment options, including the newly released 64-bit support and best practices for performance.

Offline-Enabled Data Services and Desktop Applications
Wed 10/29 | 3:00 PM-4:15 PM | 408A
Presenter: Pablo Castro

The ADO.NET Data Services Framework (a.k.a. Project "Astoria") introduced a way of creating and consuming flexible, data-centric REST services. By combining data services with the Microsoft Sync Framework, learn how to create offline-capable applications that have a local replica of their data, how to synchronize that replica with an online data service when a network connection becomes available, and how replicas can be used with the ADO.NET Entity Framework. Also, hear us talk about our plans, see the tools that help client- and server-side setup, and discuss the runtime components and APIs.

Microsoft Sync Framework Advances
Mon 10/27 | 1:45 PM-3:00 PM | 515B
Presenter: Lev Novik

This session shows you how the next version of the Microsoft Sync Framework makes it easier to synchronize distributed copies of data across desktops, devices, services, or anywhere else they may be stored.

Sync Framework: Enterprise Data in the Cloud and on Devices
Tue 10/28 | 5:15 PM-6:30 PM | 408A
Presenter: Liam Cavanagh

See how synchronization plays a pivotal role in transitioning to a managed cloud environment by creating a central hub of information in the cloud. Using synchronization, organizations can enable more efficient mobile and enterprise-to-enterprise scenarios.

Hope to see you there!

Blog Bling 1