Prepare SQL Server Local Cache

  • Start command prompt
  • Check your localdb version, if you want deploy to 32 bit client, you should use version 12.xx (SQL server 2014 LocalDB)

Type sqllocaldb v

  • To check SqlLocalDB Instannce, use sqllocaldb i

If you have 2 version, now check your MSSQLLocalDB server version

If the version is not version 12.x, you have to create new instance and set version to 12

  • Now check again your synctestDB instance using correct version (12.0.2000.8)

  • Now you’re ready to go

Open your SQL Management Studio, and connect to localdb with server name : (localdb)\<instance name> (in this example (localdb)\synctestDB) to make sure that SQLLocalDB is running

Create new database and point to new location, in this case I’ll put into “C:\Data\”

  • Now go to your Server side SQLServer (you can use Azure / SQLEXpress) and create new database with name SyncTesting too (same with SQLLocalDB database)
  • Create new Table

    CREATE
    TABLE [dbo].[TestTable](

    [id] [uniqueidentifier] NOT
    NULL,

    [stationname] [nvarchar](50)
    NULL,

    [checkintime] [datetime] NULL,

    [randomdata] [int] NULL,

    CONSTRAINT [PK_TestTable] PRIMARY
    KEY
    CLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
    OFF, IGNORE_DUP_KEY
    =
    OFF, ALLOW_ROW_LOCKS
    =
    ON, ALLOW_PAGE_LOCKS
    =
    ON) ON [PRIMARY]

    )
    ON [PRIMARY]

    GO

    ALTER
    TABLE [dbo].[TestTable] ADD
    CONSTRAINT [DF_TestTable_id] DEFAULT (newid())
    FOR [id]

    GO

Preparing Code

  • Download Microsoft Synchronize SDK here
  • Install the SDK
  • Open your Visual Studio
  • Create new Windows Project

  • Put Textbox
    • Name it with txtLog
    • Set MultiLine => True
    • Set Dock => Bottom
  • Put Timer
    • Set Interval = 15000
    • Set Enable = true
  • Put Button
    • Set Text = Add random data
  • Import DLL SDK

  • Put into your code

    using System.Data.SqlClient;

    using Microsoft.Synchronization.Data.SqlServer;

    using Microsoft.Synchronization.Data;

    using Microsoft.Synchronization;

  • Declare the connection and scopename (for more information about scope, click here

    public
    string sqlserverConnectionString = Data Source=.; Initial Catalog=S
    yncTesting; Integrated Security = true
    ;”;

    public
    string sqllocalConnectionString = @”Server = (localdb)\synctestDB; Integrated Security = true; Initial Catalog=SyncTesting;AttachDbFileName =c:\data\SyncTesting.mdf”;

    public
    string scopeName = System.Environment.MachineName;

  • Prepare database for synchronize

public
void Setup()

{


try

{

SqlConnection sqlLocalDBConn = new
SqlConnection(sqllocalConnectionString);

SqlConnection sqlServerConn = new
SqlConnection(sqlserverConnectionString);

DbSyncScopeDescription myScope = new
DbSyncScopeDescription(scopeName);

DbSyncTableDescription TestTable = SqlSyncDescriptionBuilder.GetDescriptionForTable(“TestTable”, sqlServerConn);

myScope.Tables.Add(TestTable);

SqlSyncScopeProvisioning sqlServerProv = new
SqlSyncScopeProvisioning(sqlServerConn, myScope);

if (!sqlServerProv.ScopeExists(scopeName))

{

txtLog.AppendText(“Provisioning SQL Server for sync “ + DateTime.Now + Environment.NewLine);

sqlServerProv.Apply();

txtLog.AppendText(“Done Provisioning SQL Server for sync “ + DateTime.Now + Environment.NewLine);

}


else

txtLog.AppendText(“SQL Server Database server already provisioned for sync “ + DateTime.Now + Environment.NewLine);

SqlSyncScopeProvisioning sqlAzureProv = new
SqlSyncScopeProvisioning(sqlLocalDBConn, myScope);

if (!sqlAzureProv.ScopeExists(scopeName))

{

txtLog.AppendText(“Provisioning SQL Azure for sync “ + DateTime.Now + Environment.NewLine);

sqlAzureProv.Apply();

txtLog.AppendText(“Done Provisioning SQL Azure for sync “ + DateTime.Now + Environment.NewLine);

}

else

txtLog.AppendText(“SQL Azure Database server already provisioned for sync “ + DateTime.Now + Environment.NewLine);

sqlLocalDBConn.Close();

sqlServerConn.Close();

}

catch (Exception ex)

{

txtLog.AppendText(ex.Message + Environment.NewLine);

}

}

  • Procedure for synchronize

    public
    void Sync()

    {

    try

    {

    SqlConnection sqlLocalDBConn = new
    SqlConnection(sqllocalConnectionString);

    SqlConnection sqlServerConn = new
    SqlConnection(sqlserverConnectionString);

    SyncOrchestrator orch = new
    SyncOrchestrator

    {

    LocalProvider = new
    SqlSyncProvider(scopeName, sqlLocalDBConn),

    RemoteProvider = new
    SqlSyncProvider(scopeName, sqlServerConn),

    Direction = SyncDirectionOrder.UploadAndDownload

    };

    txtLog.AppendText(String.Format(“ScopeName ={0} “, scopeName.ToUpper()) + Environment.NewLine);

    txtLog.AppendText(“Starting Sync “ + DateTime.Now + Environment.NewLine);

    ShowStatistics(orch.Synchronize());

    sqlLocalDBConn.Close();

    sqlServerConn.Close();

    }

    catch (Exception ex)

    {

    txtLog.AppendText(ex.Message + Environment.NewLine);

    }

    }

  • Procedure for synchronize statistic

public
void ShowStatistics(SyncOperationStatistics syncStats)

{

string message;

message = “Sync Start Time:” + syncStats.SyncStartTime.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Sync End Time:” + syncStats.SyncEndTime.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Upload Changes Applied:” + syncStats.UploadChangesApplied.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Upload Changes Failed:” + syncStats.UploadChangesFailed.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Upload Changes Total:” + syncStats.UploadChangesTotal.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Download Changes Applied:” + syncStats.DownloadChangesApplied.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Download Changes Failed:” + syncStats.DownloadChangesFailed.ToString();

txtLog.AppendText(message + Environment.NewLine);

message = “Download Changes Total:” + syncStats.DownloadChangesTotal.ToString();

txtLog.AppendText(message + Environment.NewLine);

txtLog.AppendText(Environment.NewLine + Environment.NewLine);

}

  • Double click on Form, and run Setup procedure

    private
    void Form1_Load(object sender, EventArgs e)

    {

    Setup();

    }

  • Double click on Timer, and run Sync

    private
    void timer1_Tick(object sender, EventArgs e)

    {

    Sync();

    }

  • Double click on Button, to add random data, assume I’ll add data on my LocalSQLData

    private
    void button1_Click(object sender, EventArgs e)

    {

    SqlConnection sqlLocalDBConn = new
    SqlConnection(sqllocalConnectionString);

    SqlCommand insert_data = new
    SqlCommand(String.Format(“insert into TestTable(stationname,checkintime,randomdata) values(‘{0}’,'{1}’,'{2}’)”,System.Environment.MachineName, DateTime.Now.ToString(“yyyy-MM-dd HH:mm:ss.fff”, CultureInfo.InvariantCulture),new
    Random().Next().ToString()), sqlLocalDBConn);

    insert_data.Connection.Open();

    insert_data.ExecuteNonQuery();

    insert_data.Connection.Close();

    }

  • Done.. and try to run

Please note, for deploy to client, make sure:

  1. Your client have .NET Framework 4.5.2
  2. SQLLocaldb Engine is already installed and setup
  3. Your client have copy MDF file (in this case SyncTesting.mdf that located at C:\Data) and your client has full access

Download my source code Here

 

Update January 8th, 2017

I’ve update my code, so it will create automatically instance and database file

What you need is:

  • install nuget package
Install-Package System.Data.SqlLocalDb
  • import sqllocaldb and system.io

using System.Data.SqlLocalDb;
using System.IO;

  • declare new connection string

public string sqlDBCheckConnectionString = @”Server = (localdb)\synctestDB; Integrated Security = true;”;

  • add this procedure

public void DBGenerator()
{
//check instance
//and create if
try
{
ISqlLocalDbProvider provider = new SqlLocalDbProvider();
ISqlLocalDbInstance instance = provider.GetInstance(instanceNames);
if (instance == null)
{
//if null, then create and start
System.Diagnostics.Process.Start(“sqllocaldb”, ” c ” + instanceNames + ” 12.0.2000.8 -s”);

}

//check database file
}
catch
{
//no instance, then create it
System.Diagnostics.Process.Start(“sqllocaldb”, ” c ” + instanceNames + ” 12.0.2000.8 -s”);

if (!File.Exists(“c:\\data\\SyncTesting.mdf”))
{
//if no database file, then create it
SqlConnection sqlLocalDBConn = new SqlConnection(sqlDBCheckConnectionString);

string str = “CREATE DATABASE SyncTesting ON PRIMARY ” +
“(NAME = SyncTesting, ” +
“FILENAME = ‘C:\\Data\\SyncTesting.mdf’, ” +
“SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) ” +
“LOG ON (NAME = SyncTesting_Log, ” +
“FILENAME = ‘C:\\Data\\SyncTesting_Log.ldf’, ” +
“SIZE = 5MB, ” +
“MAXSIZE = 5MB, ” +
“FILEGROWTH = 10%)”;

SqlCommand createdb = new SqlCommand(str, sqlLocalDBConn);
try
{
sqlLocalDBConn.Open();
createdb.ExecuteNonQuery();
txtLog.AppendText(“Database create Successfully at ” + System.DateTime.Now.ToLongDateString() + ” ” + Environment.NewLine);
createdb.Connection.Close();
}
catch (Exception ex)
{
txtLog.AppendText(“Database create failed at ” + System.DateTime.Now.ToLongDateString() + ” ” + Environment.NewLine);
txtLog.AppendText(ex.Message+Environment.NewLine);
txtLog.AppendText(“Please check manually or create manually from SQL Management Studio” + Environment.NewLine);

}
finally
{
if (sqlLocalDBConn.State == ConnectionState.Open)
{
sqlLocalDBConn.Close();
}
}
}
}
}

  • you’re almost there…,call DBGenerator insite setup procedure

public void Setup()
{
try
{
DBGenerator();

…..

  • F5 and take a coffee

get full source code Here