Remove From My Forums

Additional Stored Procedure with Bi Directional Filtered Scope.

Question
0

Sign in to vote
Hi,

I am provisioning a scope with table CR_SERVICE_REQUEST. I need it Bi Directional.It is also filtered.

But I am not able to get both the things simultaneously.

If I use the code,I get the uniqueness.

serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

But I get only one Stored Procedure .

dbo.CR_SERVICE_REQUEST_selestchanges_some guid.

While If I put serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Create);

then I get all the Stored procedures.

dbo.CR_SERVICE_REQUEST_selectchanges
Sync.CR_SERVICE_REQUEST_delete
Sync.CR_SERVICE_REQUEST_insert
Sync.CR_SERVICE_REQUEST_selectrow
Sync.CR_SERVICE_REQUEST_update

But I loose the uniqueness of making selected changes of every additional scope.

I want to implement both the things.

Regards,

Sachin K
- Edited by sakulkarni83 Wednesday, May 8, 2013 6:40 AM
Wednesday, May 8, 2013 6:39 AM

All replies

0

Sign in to vote

the insert/update/delete stored procedures are shared among scopes, why would you want them separately? are you synching different column counts for each scope?

Wednesday, May 8, 2013 7:28 AM
0

Sign in to vote
Hi June,

I have requirement of where there will be multiple Clients with One Server.The Scope would be Bidirectional and filtered.The Column would be same with different value in where clause(or BILLING_UNIT_C ='4142' /BILLING_UNIT_C ='5886') with each scope with each client .

i.e Client A might have Scope with where clause as 4142 and Bidirectional while Client B have Scope with where Clause='5886' which is also bidirectional.

Below is the source code implementation with different scopes with different where clause.

serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip);
serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

They create different Stored Procedures but dont create insert update and delete SPs respectively.

Please let me know for any other concerns.

Regards,

Sachin K
- Edited by sakulkarni83 Wednesday, May 8, 2013 9:11 AM
Wednesday, May 8, 2013 9:06 AM
0

Sign in to vote

as I have mentioned, that's how it is. the insert, update and delete are shared by the scopes. those stored procedures don't need the filter value.

and as I have mentioned in your other thread, you should be using filtered scope templates so that the filter value is not hard coded in the select changes stored proc.

if you have a thousand scopes, I don't think you like to have a thousand stored procs.

Wednesday, May 8, 2013 12:46 PM

Hi June,

Can you provide me an example?

I have tried from url http://jtabadero.wordpress.com/2010/09/02/sync-framework-provisioning/.

I am getting issue at serverProvWA.PopulateFromTemplate(scopeName, templScopeName);

it says An SqlParameter with ParameterName '@BILLING_UNIT_C' is not contained by this SqlParameterCollection.

I can successfully create template.

string xpath = null;
                    XPathNodeIterator scopeTmplNodeIt = null;
                    XPathNavigator scopeTmplNode = null;
                    xpath = DBSyncConstants.XPATH_SYNC_PROVIDER_TEMPLATE_CONFIG;
                    xpath += "[@templateName='" + scopeName + "']";
                    scopeTmplNodeIt = DBSyncXMLUtil.getNodeList(DBSyncXMLUtil.getDocumnetRoot(), xpath);
                    string templScopeName = "";
                    if (scopeTmplNodeIt.MoveNext())
                    {
                        scopeTmplNode = scopeTmplNodeIt.Current;
                        templScopeName = DBSyncXMLUtil.getAttributeValue(scopeTmplNode, "scopeName");                    
                    }
                    XPathNavigator tableNode = null;
                    XPathNodeIterator itTable = null;
                    string tableName = "";
                    itTable = DBSyncXMLUtil.getNodeList(scopeConfigNode, "Tables/TableName");                  
                    DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(templScopeName);
                    string colName = null;
                    string filterClause = "";

                    while (itTable.MoveNext())
                    {
                        tableNode = itTable.Current;
                        tableName = DBSyncXMLUtil.getAttributeValue(tableNode, "name");
                        Console.WriteLine("ScopeConfigHandler :: loadScopeTableInfo() :: tableName ==" + tableName);
                        filterClause = DBSyncXMLUtil.getNodeValue(tableNode, "FilterClause");                      
                        DbSyncTableDescription orderDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, conn);
                        scopeDesc.Tables.Add(orderDescription);
                        XPathNodeIterator colNodeIt = DBSyncXMLUtil.getNodeList(tableNode, "FilterColumns/Col");
                        XPathNavigator colNode = null;
                        while (colNodeIt.MoveNext())
                        {
                            colNode = colNodeIt.Current;
                            colName = DBSyncXMLUtil.getAttributeValue(colNode, "name");                         
                        }
                    }
                    XPathNodeIterator sqlNodeIt = DBSyncXMLUtil.getNodeList(tableNode, "FilterParameters/SqlParameter");
                    XPathNavigator sqlNode = null;
                    SqlParameter param = null;
                    string paramName = null;
                    string paramSize = null;
                    string paramType = null;
                    while (sqlNodeIt.MoveNext())
                    {
                        sqlNode = sqlNodeIt.Current;
                        paramName = DBSyncXMLUtil.getAttributeValue(sqlNode, "name");
                        paramType = DBSyncXMLUtil.getAttributeValue(sqlNode, "SqlDbType");
                        paramSize = DBSyncXMLUtil.getAttributeValue(sqlNode, "size");
                        if (paramSize != null)
                            param = new SqlParameter(paramName, DBSyncApplicationUtil.getSqlDbType(paramType), Convert.ToInt32(paramSize));
                        else
                            param = new SqlParameter(paramName, DBSyncApplicationUtil.getSqlDbType(paramType));
                      
                    }



                    scopeDesc.UserComment = "Filter template for Orders.OriginState";
                    DbSyncTableDescription ordersDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, conn);
                    //DbSyncTableDescription ordersDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", conn);
                    scopeDesc.Tables.Add(ordersDescription);
                    SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(conn, scopeDesc, SqlSyncScopeProvisioningType.Template);
                  
                    serverTemplate.SetUseBulkProceduresDefault(true);
                    serverTemplate.Tables[tableName].AddFilterColumn(colName);
                   
                    string originalfilterclause = filterClause;

                  
                    int firstindexof = filterClause.IndexOf("'");
                    filterClause = filterClause.Substring(0, firstindexof);
                    originalfilterclause = originalfilterclause.Substring(firstindexof, originalfilterclause.Length - firstindexof - 1);

                    filterClause = filterClause + paramName + ")";

                    serverTemplate.Tables[tableName].FilterClause = "[side].[BILLING_UNIT_C] ="+paramName+"";
                  
                    serverTemplate.Tables[tableName].FilterParameters.Add(param);
                  
                    serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
                
                    if(!serverTemplate.TemplateExists(templScopeName))
                    serverTemplate.Apply();
                  
                    SqlSyncScopeProvisioning serverProvWA = new SqlSyncScopeProvisioning(conn);
                    
                    serverProvWA.PopulateFromTemplate(scopeName, templScopeName);
                  
                    serverProvWA.Tables[tableName].FilterParameters[paramName].Value = originalfilterclause;

                  
                    serverProvWA.UserComment = "Orders data includes only WA.";
                  
                    serverProvWA.Apply();

Thanks,

Sachin K

Edited by sakulkarni83 Thursday, May 9, 2013 11:58 AM

Thursday, May 9, 2013 11:51 AM

0

Sign in to vote

hard to tell what the actual scope definition just by looking at above...

what's the value of paramName?

try serverTemplate.Script() or ServerProvWA.Script() to see what the generated SQL look like...

Thursday, May 9, 2013 11:28 PM

Asked by:

Additional Stored Procedure with Bi Directional Filtered Scope.

Question

All replies