Database project diff SQL script utility (Using VSDBCMD)

• Download source code - 2.3 MB
• Download tool - 2.6 MB

Introduction

I was working on a database project that requires database project incremental build. The requirements were to change some Stored Procedures, add insert data script in the database project, and get the Install, Rollback SQL script using the VSDBCMD command. I was running VSDBCMD command manually using the command prompt. To avoid manual process the Solution I came up with is a database project diff SQL script UI tool which generates the SQL diff script from two database projects as well as the VSDBCMD commands that can be used in the automated build.

Using the code

I have created the DBDiffTool form which contains buttons, text boxes, check boxes etc.

When a user clicks on the Browse button, the following actions are invoked:

/// <summary>
/// Event called when SourceModelFile browse button clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSourceModelFile_Click(object sender, EventArgs e)
{
    DialogResult result = openDialog.ShowDialog(); 
    if (result == DialogResult.OK) 
       txtSourceModelFile.Text  =openDialog.FileName;
}

/// <summary>
/// Event called when TargetModelFile browse button clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnTargetModelFile_Click(object sender, EventArgs e)
{
    DialogResult result = openDialog.ShowDialog();
    if (result == DialogResult.OK) 
        txtTargetModelFile.Text   = openDialog.FileName;
}

/// <summary>
/// Event called when SoruceManifestFile browse button clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSoruceManifestFile_Click(object sender, EventArgs e)
{
    DialogResult result = openDialog.ShowDialog();
    if (result == DialogResult.OK) 
        txtSourceManifestFile.Text = openDialog.FileName;
}

When a user clicks on the Yes/No radio button, the following actions are invoked:

/// <summary>
/// Event called when SourceManifest 'Yes'radio button clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void rdoIncludeSourceManifest_CheckedChanged(object sender, EventArgs e)
{
    lblSourceManifest.Visible = true;
    txtSourceManifestFile.Visible = true;
    btnSoruceManifestFile.Visible = true; 
}
/// <summary>
/// Event called when SourceManifest 'No' radio button clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void rdoExcludeSourceManifest_CheckedChanged(object sender, EventArgs e)
{
    lblSourceManifest.Visible = false;
    txtSourceManifestFile.Visible = false;
    btnSoruceManifestFile.Visible = false; 
}

When a user clicks on the 'Execute' button, the following action is invoked:

///<summary>
/// Event called when Execute button clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreateCommand_Click(object sender, EventArgs e)
{
    //Check SourceModelFile empty
    if (txtSourceModelFile.Text == string.Empty)
    {
         MessageBox.Show("Enter the Source Model File path ");
        return;
    }

    //Check TargetModelFile empty
    if (txtTargetModelFile.Text  == string.Empty)
    {
        MessageBox.Show("Enter the Target Model File path ");
        return;
    }

    //Check SourceManifestFile empty
    if (rdoIncludeSourceManifest.Checked)
    {
        if (txtSourceManifestFile.Text  == string.Empty)
        {
            MessageBox.Show("Enter the Source Manifest File path ");
            return;
        }
    }

    //Check Database name empty
    if (txtDBName.Text == string.Empty)
    {
        MessageBox.Show("Enter the Database name");
        return;
    }
    //Getting root path
    string baseDirectoryPath = AppDomain.CurrentDomain.BaseDirectory;
    //DiffScript.txt already exist delete it
    if (File.Exists(baseDirectoryPath + "DiffScript.txt"))
        File.Delete(baseDirectoryPath + "DiffScript.txt");
    //Creating VBDMCMD command string 
    StringBuilder sb = new StringBuilder();
    sb.Append("/a:Deploy /dsp:Sql /dd- ");
    sb.Append("/ModelFile:" + txtSourceModelFile.Text + " ");
    sb.Append("/TargetModelFile:" + txtTargetModelFile.Text + " ");
    if (rdoIncludeSourceManifest.Checked)
        sb.Append("/Manifest:" + txtSourceManifestFile.Text + " ");
    sb.Append("/p:TargetDatabase=" + txtDBName.Text + " ");
    sb.Append("/p:AbortOnFirstError=" + chkAbortOnFirstError.Checked.ToString() + " ");
    sb.Append("/p:AlwaysCreateNewDataBase=" + 
       chkAlwaysCreateNewDatabase.Checked.ToString() + " ");
    sb.Append("/p:BlockIncrementalDeploymentIfDataLoss=" + 
       chkBlockIncrementalDeploymentIfDataLoss.Checked.ToString() + " ");
    sb.Append("/p:CheckNewConstraints=" + chkCheckNewConstraints.Checked.ToString() + " ");
    sb.Append("/p:CommentOutSetVarDeclarations=" + 
       chkCommentOutSetVarDeclarations.Checked.ToString() + " ");
    sb.Append("/p:DeployDatabaseProperties=" + 
       chkDeployDatabaseProperties.Checked.ToString() + " ");
    sb.Append("/p:DropConstraintsNotInSource=" + 
       chkDropConstraintsNotInSource.Checked.ToString() + " ");
    sb.Append("/p:DropIndexesNotInSource=" + 
       chkDropIndexesNotInSource.Checked.ToString() + " ");
    sb.Append("/p:EnforceMinimalDependencies=" + 
       chkEnforceMinimalDependencies.Checked.ToString() + " ");
    sb.Append("/p:GenerateDeployStateChecks=" + 
       chkGenerateDeployStateChecks.Checked.ToString() + " ");
    sb.Append("/p:GenerateDropsIfNotInProject=" + 
       chkGenerateDropsIfNotInProject.Checked.ToString() + " ");
    sb.Append("/p:IgnoreComments=" + chkIgnoreComments.Checked.ToString() + " ");
    sb.Append("/p:IgnoreDefaultSchema=" + chkIgnoreDefaultSchema.Checked.ToString() + " ");
    sb.Append("/p:IgnoreExtendedProperties=" + chkIgnoreExtendedProperties.Checked.ToString() + " ");
    sb.Append("/p:IgnorePermissions=" + chkIgnorePermissions.Checked.ToString() + " ");
    sb.Append("/p:IgnoreWhitespace=" + chkIgnoreWhitespace.Checked.ToString() + " ");
    sb.Append("/p:IncludeTransactionalScripts=" + chkIncludeTransactionalScripts.Checked.ToString() + " ");
    sb.Append("/p:IgnoreAnsiNulls=" + chkIgnoreAnsiNull.Checked.ToString() + " ");
    sb.Append("/p:IgnoreFilegroupPlacement=" + chkIgnoreFilegroupPlacement.Checked.ToString() + " ");
    sb.Append("/p:IgnoreFillFactor=" + chkIgnoreFillFactor.Checked.ToString() + " ");
    sb.Append("/p:IgnoreLoginSids=" + chkIgnoreLoginSids.Checked.ToString() + " ");
    sb.Append("/DeploymentScriptFile:DiffScript.txt");

    try
    {
        //Executing vsdbcmd command 
        ProcessStartInfo startInfo = new ProcessStartInfo();
        startInfo.Arguments  =sb.ToString();
        startInfo.CreateNoWindow =false ;
        startInfo.FileName = baseDirectoryPath + "\\UtilityDlls\\vsdbcmd.exe ";
        startInfo.UseShellExecute = false;
        startInfo.WindowStyle = ProcessWindowStyle.Hidden ;
        using(Process exeProcess= Process.Start(startInfo)  ) 
             exeProcess.WaitForExit();  
        System.IO.StreamReader readFile = new System.IO.StreamReader(baseDirectoryPath + "DiffScript.txt");
        //Read the sql script from DiffScript.txt file and show into text box
        txtDiffScript.Text = readFile.ReadToEnd();
        readFile.Close();
        //Show the VBDBCMD command int the textbox
        txtCommand.Text = "vsdbcmd.exe " + sb.ToString();
        
    }
    catch(System.Exception ex ) 
    {
        txtDiffScript.Text = ex.ToString(); 
    }
}

Attached source code

• DatabaseProjectDiffScriptTool.zip contains the database project diff script tool and sample database projects.
• DatabaseProjectDiffScriptToolCode.zip contain the tool code.