Execute xp_cmdshell using C#

Question

We have created a Windows Forms application to synchronize databases using merge publication and merge pull subscription. We have created publication and subscription successfully. Now We want to start synchronize data.

For that we want to execute below SQL Command:

-- Declare the variables.  
DECLARE @publicationDB AS sysname;
DECLARE @Subscriber AS sysname;
DECLARE @Publisher AS sysname;
DECLARE @SubscriptionDB AS sysname;
DECLARE @Publication AS sysname;
DECLARE @sql VARCHAR(8000);
DECLARE @sqlDist VARCHAR(8000);
SET @Publisher = 'MSSQLSERVER2014';
SET @Subscriber = 'SQLEXPRESS2014'; 
SET @PublicationDB = 'ServerDB' ;
SET @SubscriptionDB = 'ClinetDB';
SET @Publication = 'ServerDB_PUBLICATION';

--Start the Merge Agent with concurrent upload and download processes.  
SET @sql = '"C:\Program Files\Microsoft SQL Server\130\COM\REPLMERG.EXE" -Publication ' + @Publication+ ' -Publisher ' + @Publisher + ' -Subscriber ' + @Subscriber +   ' -Distributor ' + @Publisher + ' -PublisherDB ' + @PublicationDB +   ' -SubscriberDB ' + @SubscriptionDB + ' -PublisherSecurityMode 0 -PublisherLogin sa -PublisherPassword Abc@1234  -OutputVerboseLevel 2  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 0   -DistributorLogin sa -DistributorPassword Abc@1234 -Validate 3  -ParallelUploadDownload 1'
EXEC master..xp_cmdshell @sql

How to execute above command in C#? We don't wont to use cmd or powershell.

We have tried below but get syntax error at @publication:

string SP_xp_cmdshell = "'C:\\Program Files\\Microsoft SQL Server\\130\\COM\\REPLMERG.EXE' -Publication " + mPublication + " -Publisher " + mPublisher + " -Subscriber " + mSubscriber + " -Distributor " + mPublisher + " -PublisherDB " + mPublicationDatabase + " -SubscriberDB " + mSubscriptionDatabase + " -PublisherSecurityMode 0 -PublisherLogin " + mLogin + " -PublisherPassword " + mPassword + " -OutputVerboseLevel 2 -SubscriberSecurityMode 1 -SubscriptionType 1 -DistributorSecurityMode 0 -DistributorLogin " + mLogin + " -DistributorPassword " + mPassword + " -Validate 3 -ParallelUploadDownload 1";
cmd.Connection = mConnection; //master connection
cmd.CommandText = "xp_cmdshell "+ SP_xp_cmdshell;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

Answer 1

xp_cmdshell is used to call Command prompt from Sql Server Management Studio. To execute REPLMERG.EXE from SSMS you need to execute xp_cmdshell with path and parameters.

To execute REPLMERG.EXE from C# see below code:

      string Query= @"""C:\\Program Files\\Microsoft SQL Server\\130\\COM\\REPLMERG.EXE"" " +
@"-Publisher [SQLSERVER] -PublisherDB [Server_Database] -Publication [PUBLICATION_NAME] " +
@"-Subscriber [SQLEXPRESS] -SubscriberDB [Express_Database] -SubscriptionType 1 " +
@"-SubscriberSecurityMode 0 -SubscriberLogin SQLLogin -SubscriberPassword SQLPass -Distributor [SQLSERVER] " +
@"-OutputVerboseLevel 2 -PublisherSecurityMode 0 -PublisherLogin SQLLogin -PublisherPassword SQLPass " +
@"-DistributorSecurityMode 0 -DistributorLogin SQLLogin -DistributorPassword SQLPass -Validate 1  -ParallelUploadDownload 1";

             try
             {
                 Process proc = new Process();
                 proc.StartInfo.FileName = "CMD.exe";
                 proc.StartInfo.Arguments = "/c " + Query;
                 proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;  //to hide console window
                 proc.Start();
                 proc.WaitForExit();

                 int result = proc.ExitCode;
                 if (result != 0) // exitcode is 0 for successful synchronization
                 {
                     throw new Exception();
                 }
             }
             catch (Exception ex)
             {
                 // Implement appropriate error handling here.
                 Console.WriteLine("error:" + ex.Message);
             }