locked
Create SDF with Initial Sync-Does it Bring Relationships? RRS feed

  • Question

  • Hi,

    I am using Synchronization Services to sync SQL CE 3.5 SP1. SDF is created on initial sync. Do the sync services bring down all the PK-FK relationships when the inital SDF is created?

    I am trying to use SQLMetal for an SDF that I created using SYnc Services but I do not see any relationships in the dbml file.

    Appreicate your help.

    Thanks
    • Moved by Tina_Tian Friday, April 22, 2011 8:06 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, October 8, 2008 9:22 PM

Answers

  • Had to use following code to make it work

     SqlCeCommand alterTable = new SqlCeCommand();
                    alterTable.Connection = (SqlCeConnection)e.Connection;
                    alterTable.Transaction = (SqlCeTransaction)e.Transaction;
                    alterTable.CommandText = String.Empty;
                                   

                        //for (int k = 0; k < e.Schema.Tables.Count; k++)
                        //{
                            if (e.Table.TableName == "gisadmin.tbl_timesheet_entries")
                            {

                                alterTable.CommandText = "ALTER TABLE [gisadmin.tbl_timesheet_entries] ADD CONSTRAINT FK_tbl_timeentry_tbl_timesheet FOREIGN KEY([ts_id]) REFERENCES [gisadmin.tbl_timesheets] ([ts_id])";
                                int num = alterTable.ExecuteNonQuery();

    }



    Thursday, October 9, 2008 7:07 PM

All replies

  • I suspect the PK/FK relationship is created on the client database during initial sync.  There is a way to fix it -

     

    in the sync application add

    clientProvider.SchemaCreated += new EventHandler<SchemaCreatedEventArgs>(clientProvider_SchemaCreated);

     

    for event handler

    static void clientProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)

    {

        // add a foreign key constraint on the local SQL CE database

    }

     

    Thanks.

     

    Wednesday, October 8, 2008 10:32 PM
    Answerer
  • Thank you so much. However when I try to add constraint in this event, I get error message saying "table doesnt exist"

    Thanks
    Thursday, October 9, 2008 3:55 PM
  • So I added the foreign key constraint using following statement:

    e.Schema.Tables["gisadmin.tbl_timesheet_entries"].ForeignKeys.Add("FK_tbl_timeentry_tbl_timesheet", "gisadmin.tbl_timesheets", "ts_id", "gisadmin.tbl_timesheet_entries", "ts_id")
                            
    But when I generate the dbml using SQLMetal, I dont see any relationship between gisadmin.tbl_timesheets and gisadmin.tbl_timesheet_entries.

    Thanks
    Thursday, October 9, 2008 5:24 PM
  • Had to use following code to make it work

     SqlCeCommand alterTable = new SqlCeCommand();
                    alterTable.Connection = (SqlCeConnection)e.Connection;
                    alterTable.Transaction = (SqlCeTransaction)e.Transaction;
                    alterTable.CommandText = String.Empty;
                                   

                        //for (int k = 0; k < e.Schema.Tables.Count; k++)
                        //{
                            if (e.Table.TableName == "gisadmin.tbl_timesheet_entries")
                            {

                                alterTable.CommandText = "ALTER TABLE [gisadmin.tbl_timesheet_entries] ADD CONSTRAINT FK_tbl_timeentry_tbl_timesheet FOREIGN KEY([ts_id]) REFERENCES [gisadmin.tbl_timesheets] ([ts_id])";
                                int num = alterTable.ExecuteNonQuery();

    }



    Thursday, October 9, 2008 7:07 PM