locked
TIME-DataType, SqlServer2008 and Sql Server Compact 3.5 with Sync Framework 2.1 RRS feed

  • Question

  • Hello,
    I want to synchronize some tables from my Sql Server 2008 to some clients with Sql Compact 3.5.
    But there is a problem: It rises an exception.
    The datatype TimeSpan (Time(0)) from Sql Server 2008 can't be converted to sql compact.
    It's really urgent.

    Thanks for your help.

    Ronny - From Germany ;)

    Wednesday, March 16, 2011 2:41 PM

Answers

  • Hello,

    i've solve the problem and now I just singing a song for me :)
    It's a little-bit dirty but it works.
    I've added a not computed nullable datetime-column like "ConvertedFrom"
    and then I've added a Trigger for Update and Insert.

    UPDATE [dbo].[ScheduleItem]
    SET [ConvertedFrom]=CAST([From] AS DATETIME)
    WHERE [ID] IN ( SELECT [ID] FROM INSERTED ) 

    For the current rows I've executed a little sql-update-action,
    And know the synchronization works.
    I have removed the "From"-column from the server-provision and now I've only the ConvertedFrom-DateTime-Helper-Column.

    Thanks for your Help! :)
    Bye, Ronny

    Wednesday, March 23, 2011 7:53 AM

All replies

  • what is the data type specified in your sql compact?
    Wednesday, March 16, 2011 2:54 PM
  • what is the data type specified in your sql compact?

    Hi,

    I make a client provision with SqlCeSyncScopeProvisioning.
    It automaticly creates the compact-db.
    The created datatype in the compact-db is varchar. But that's not what I need.
    I'd like to convert sql server 2008 -> Time-Datatype to Sql Compact -> DateTime-Datatype in sql compact.
    Because the time-datatype is not support in sql compact.

    (Time(0)) 7:00:00 ->  ( DateTime) 7:00:00 1/1/1900 or somthing like that.

    How to convert or anything else?

    Thanks 

     

    Tuesday, March 22, 2011 9:59 AM
  • you can specify the the datatype for a column using the following approach:

    DbSyncTableDescription newTableDescription = new DbSyncTableDescription("Sales.NewTable");
    
    DbSyncColumnDescription newTableIdCol = new DbSyncColumnDescription();
    DbSyncColumnDescription newTableContentCol = new DbSyncColumnDescription();
    
    newTableIdCol.UnquotedName = "NewTableId";
    newTableIdCol.Type = "int";
    newTableIdCol.IsPrimaryKey = true;
    
    newTableContentCol.UnquotedName = "NewTableContent";
    newTableContentCol.Type = "nvarchar";
    newTableContentCol.Size = "100";
    newTableContentCol.IsPrimaryKey = false;
    
    newTableDescription.Columns.Add(newTableIdCol);
    newTableDescription.Columns.Add(newTableContentCol);
    scopeDesc.Tables.Add(newTableDescription);
    
    
    Tuesday, March 22, 2011 10:19 AM
  • I have changed the Type from "Time" to "DateTime" but it rises an exception on Synchronize.

    Exception: {"Es ist keine Zuordnung vom DbType-Wert System.TimeSpan zu einem bekannten Wert für SqlCeType vorhanden."}

    In English: There is no assignment of DbType value System.TimeSpan available at a known value for SqlCeType.

    // Server-Provision

    using ( var con = new SqlConnection ( connectionString ) ) {
       var readOnlyScope = new DbSyncScopeDescription ( "TerminalReadOnlyScope" );
       var scheduleItemTable = SqlSyncDescriptionBuilder.GetDescriptionForTable ( "ScheduleItem", con );
       scheduleItemTable.Columns["From"].Type = "datetime"; // Before this, the type was "Time"
    
       readOnlyScope.Tables.Add(scheduleItemTable);
       readOnlyScope.Apply ( );
    } 
    

    // Client-Provision

                using ( var serverConn = new SqlConnection ( serverConnectionString ) ) {
                    using ( var clientConn = new SqlCeConnection ( clientConnectionString ) ) {
                        var readOnlyScope = SqlSyncDescriptionBuilder.GetDescriptionForScope ( "TerminalReadOnlyScope", serverConn );
                        var readOnlyProvision = new SqlCeSyncScopeProvisioning ( clientConn, readOnlyScope );
                        readOnlyProvision.Apply ( );
                    }
                }
    

    // Synchronization

          using ( var serverConn = new SqlConnection ( serverConnectionString ) ) {
            using ( var clientConn = new SqlCeConnection ( clientConnectionString ) ) {
              var readOnlySync = new SyncOrchestrator ( );
              readOnlySync.LocalProvider = new SqlCeSyncProvider ( "TerminalReadOnlyScope", clientConn );
              readOnlySync.RemoteProvider = new SqlSyncProvider ( "TerminalReadOnlyScope", serverConn );
              readOnlySync.Direction = SyncDirectionOrder.Download;
              readOnlySync.Synchronize ( );
            }
          }
    

    Tuesday, March 22, 2011 11:01 AM
  • i actually meant changing the data type on the sql ce side, not on the sql server side.

    have you tried mapping it to a nvarchar(16)?

    Tuesday, March 22, 2011 2:08 PM
  • Hello,

    with nvarchar, it works. But I need to calculate with it.
    I've a complex Linq to Entities query and there is no 
    supported function to convert the nvarchar to DateTime in SqlCe !?

    The second way I've tried, is to add a computed column to sql server, like this, -> ComputedFrom = CAST ( 'From' AS DATETIME ),
    but if i apply the server provision, it rises an exception.

    -> "'ComputedFrom'-Column can not be changed because it is a computed column or because it is the result of a UNION operator"

    It also does not work. :(

    Tuesday, March 22, 2011 2:24 PM
  • sorry, i've tried with nchar(16) again, it doesn't work. It rises an ArgumentException on Synchronize().

    Exception: "There is no assignment of DbType value System.TimeSpan available at a known value for SqlCeType"

    It is really really urgent and I will sing a song for everyone who solve the problem :)

    Tuesday, March 22, 2011 2:50 PM
  • if you're fine with storing it as varchar on the CE side and just manipulating it back when you do your calculation, provision your server as it is, provision your client specifying the column type as nvarchar(16), then  subscribe to the server's ChangesSelected event, then manually assign the time column to the varchar column then remove it.

    static void ServerChangesSelected(object sender, DbChangesSelectedEventArgs e)
    {
      //let's check if we're synching the table we're interested
      if (e.Context.DataSet.Tables.Contains("testtable2"))
      {
        var dataTable = e.Context.DataSet.Tables["testtable2"];
    
        //let's add the new column
        dataTable.Columns.Add(new DataColumn("timevarchar"));
        foreach (DataRow row in dataTable.Rows)
        {
          // if it's an update or an insert, assign the time column to the varchar column
          if (row.RowState == DataRowState.Modified || row.RowState == DataRowState.Added)
          {
            row["timevarchar"] = row["timecolumn"];
          }
          
        }
    
        var columns = e.Context.DataSet.Tables["testtable2"].Columns;
        //remove the time column
        columns.Remove("timecolumn");
    
        
      }
    }
    
    Tuesday, March 22, 2011 3:31 PM
  • Sorry it's not working. The ChangesSelected-Event was not triggered.
    It failes on Synchronize with the above Exception. "There is not assignment of DbType value System.TimeSpan...."
    It only works if I remove the Time-Datatype-Column 'From' from the  Server-Provision.
    It would satisfy me even if I could synchronize the computed DateTime-helper-column. As I previously descriped.

    Wednesday, March 23, 2011 7:13 AM
  • Hello,

    i've solve the problem and now I just singing a song for me :)
    It's a little-bit dirty but it works.
    I've added a not computed nullable datetime-column like "ConvertedFrom"
    and then I've added a Trigger for Update and Insert.

    UPDATE [dbo].[ScheduleItem]
    SET [ConvertedFrom]=CAST([From] AS DATETIME)
    WHERE [ID] IN ( SELECT [ID] FROM INSERTED ) 

    For the current rows I've executed a little sql-update-action,
    And know the synchronization works.
    I have removed the "From"-column from the server-provision and now I've only the ConvertedFrom-DateTime-Helper-Column.

    Thanks for your Help! :)
    Bye, Ronny

    Wednesday, March 23, 2011 7:53 AM
  • Sorry it's not working. The ChangesSelected-Event was not triggered.
    It failes on Synchronize with the above Exception. "There is not assignment of DbType value System.TimeSpan...."
    It only works if I remove the Time-Datatype-Column 'From' from the  Server-Provision.
    It would satisfy me even if I could synchronize the computed DateTime-helper-column. As I previously descriped.


    are you subscribing to the provider that has the Time column? i have this working even on bidirectional sync with time to datetime mapping and even vice versa.

     

    Wednesday, March 23, 2011 8:01 AM
  • Then I do something wrong.
    Can You please post a sample for synching a time to datetime-column?
    I subscribe the time-column, on server-provision, if i make no changes on the server-provision
    the client provsision will automaticly create a sql compact db with a nchar(16) for the time-column.
    But I cannot sync. It rises an exception. Above descriped.


    Wednesday, March 23, 2011 8:17 AM
  • i'll post the code when i get home. am assuming when casting time to datetime, you're just going to use the time part of the datetime right?
    Wednesday, March 23, 2011 8:22 AM
  • yes, that's right. thanks ;)
    Wednesday, March 23, 2011 8:25 AM
  • last question, do you require the column name to be the same on both client and server?
    Wednesday, March 23, 2011 8:43 AM
  • No Problem on Client-Side. On Server-Side I cannot change the ColumnName oder DataType. :) 

    Wednesday, March 23, 2011 9:04 AM
  • here you go

    static void Main(string[] args)
    {
      //setup the server connection
      var serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=Aggregator; Integrated Security=True");
    
      // define a new scope named TimeTest
      var scopeDesc = new DbSyncScopeDescription("TimeTest");
    
      // get the description of the TestTable table from the server database
      var tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("TestTable2", serverConn);
    
      // add the table description to the sync scope definition
      scopeDesc.Tables.Add(tableDesc);
    
      // set scope to be provisioned based on the scope definition
      var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
    
      if (!serverProvision.ScopeExists("TimeTest"))
      {
        //apply the scope definition
        serverProvision.Apply();
      }
    
      //setup the client connection
      var clientConn = new SqlConnection("Data Source=localhost; Initial Catalog=Client1; Integrated Security=True");
    
      // retrieve scope definition from server
      var clientScope = SqlSyncDescriptionBuilder.GetDescriptionForScope("TimeTest", serverConn);
    
      // lets change the column with time data type to a datetime column on the client
      clientScope.Tables["TestTable2"].Columns["timecolumn"].Type = "datetime";
    
      // set scope to be provisioned based on the scope definition
      var clientProvision = new SqlSyncScopeProvisioning(clientConn, clientScope);
    
      if (!clientProvision.ScopeExists("TimeTest"))
      {
        //apply the scope definition
        clientProvision.Apply();
      }
    
      // create the sync orchestrator
      var syncOrchestrator = new SyncOrchestrator();
    
      // set local provider of orchestrator 
      syncOrchestrator.LocalProvider = new SqlSyncProvider("TimeTest", clientConn);
    
      // set the remote provider of orchestrator 
      syncOrchestrator.RemoteProvider = new SqlSyncProvider("TimeTest", serverConn);
    
      // set the direction of sync session to Download
      syncOrchestrator.Direction = SyncDirectionOrder.Download;
    
      // subscribe to changes selected event of the remote provider (server with time column)
      ((SqlSyncProvider)syncOrchestrator.RemoteProvider).ChangesSelected += ServerChangesSelected;
    
      // execute the synchronization process
      var syncStats = syncOrchestrator.Synchronize();
    
      // print statistics
      Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
      Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
      Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
      Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
      Console.WriteLine(String.Empty);
      Console.ReadKey();
    }
    
    static void ServerChangesSelected(object sender, DbChangesSelectedEventArgs e)
    {
      //let's check if we're synching the table we're interested
      if (e.Context.DataSet.Tables.Contains("testtable2"))
      {
        var dataTable = e.Context.DataSet.Tables["testtable2"];
    
        //rename the old column (we cant change the column's datatype if it has data)
        dataTable.Columns["timecolumn"].ColumnName = "OldTimeColumn";
    
        //let's add the new column having the name of the original column but with a datetime type
        dataTable.Columns.Add(new DataColumn("timecolumn", typeof(DateTime)));
    
        foreach (DataRow row in
          dataTable.Rows.Cast<DataRow>().Where(row => row.RowState == DataRowState.Modified || row.RowState == DataRowState.Added))
        {
          //convert the time data to datetime
          row["timecolumn"] = DateTime.Parse(row["oldtimecolumn"].ToString());
        }
    
        var columns = e.Context.DataSet.Tables["testtable2"].Columns;
    
        //remove the time column
        columns.Remove("oldtimecolumn");
      }
    }
    
    

     

    Wednesday, March 23, 2011 10:41 AM
  • Hello, 

    thanks for your code :)
    But it doesn't work on my pc :(

    // lets change the column with time data type to a datetime column on the client
    clientScope.Tables["TestTable2"].Columns["timecolumn"].Type = "datetime";
    

    Und Synchronize - It rises an exception. Above descriped.

    On Client-Side, I've Sql Compact 3.5 SP2 and Windows 7 SP1, VS2010 and on Server-Side,
    there is Windows Server 2008 R2 and Sql Server 2008,

    It works with my dirty code. 
    Do not worry anymore.

    Bye :) 

     

    Thursday, March 24, 2011 12:21 PM