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();

In the end `xp_cmdshell` will also run `CMD.EXE` to invoke the command you pass it. The "only" difference being, that `xp_cmdshell` will execute on the database server itself, which is must not be the same machine as the one your application runs on - which of course only makes a difference, if you have a separate one. Also `xp_cmdshell` is often disabled for security reasons in production environments. Additionally, you would introduce a SQL injection attack vector if you simply use string concatenation. — Christian.K, Feb 13 '18 at 07:06
@Christian.K : Thanks for quick reply. So, What would be better way or alternative to sync databases using merge replication? — Dev, Feb 13 '18 at 07:12
For general questions about database you might be better off at [dba.stackexchange.com](http://dba.stackexchange.com). — Christian.K, Feb 13 '18 at 07:16

score 1 · Accepted Answer · answered Mar 07 '18 at 09:54

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);
             }

Execute xp_cmdshell using C#

1 Answers1