Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

Archive for SQL Server Data Tools (SSDT)

April 17, 2014

SSDT Data Editor Now Has Sorting and Filtering!

The SQL Server Data Tools (SSDT) provide access to many SQL Server features from within Visual Studio. One of those features, available from the SQL Server Object Explorer, is the visual Data Editor.

While the Data Editor has always been great for inserting, updating, and viewing data, it did not support any sorting or filtering … until now!

image

NOTE:

  • For an introduction to SSDT and the SQL Server Object Explorer, see this post.
  • For an introduction to the SSDT Data Editor, see this post.

The March 2014 release of SSDT added support for SQL Server 2014 databases. But it ALSO provided new features in VS 2012 and VS 2013 for sorting and filtering the data in the Data Editor!

If you are using VS 2012, you can use the update option to get this update (SQL | Check for Updates).

If you are using VS 2013, the update should appear in the Notification window when you click the notification flag:

image

If not, you can look for it under Tools | Extensions and Updates.

Once you have it installed, your Data Editor will have two additional buttons:

image

The first button disables sorting and filtering.

The second button displays a dialog for entry of the sorting and filtering criteria.

image

In this example, I set a sort on the FirstName column. Any number of sorts can be added, either ascending or descending. I also added a filter on the LastName so only customers with a last name that begins with B will be listed.

This dialog also allows you to uncheck columns to remove them from the Data Editor display. This helps you focus on only the columns you need to see.

Notice at the bottom of the dialog is the SQL expression SSDT will use to query the table. This expression changes as you modify the columns, sorting, sort order, or filter.

Clicking Apply immediately executes the query and re-populates the data in the Data Editor. This allows you to view the results without closing the Filter and Sort dialog.

image

When you have the results you need, click OK to close the dialog.

NOTE: When I attempted to edit the sorted and filtered data, I received an error message:

image

HOWEVER, the edits were actually made to the data.

Try out these new features any time you want more control over the data displayed in the Data Editor.

Enjoy!

February 12, 2014

My Latest Course Went Live Today!

I am happy to announce that my latest Pluralsight course: "Visual Studio Data Tools for Developers" went live today!

As C# or VB.NET developers, we often need to work with a database.

This course covers how to:

  • Use the many SQL Server Data Tools (SSDT) in Visual Studio
  • Manage SQL Server databases with Visual Studio
  • Build database scripts (including data scripts) with a Database Project
  • Publish Database Scripts
  • Unit Test stored procedures
  • Generate a DACPAC
  • Use a DACPAC to deploy database changes

You NEVER have to write database change scripts again!

Check it out and let me know what you think! Feel free to leave comments here or in the Discussion section for the course.

http://www.pluralsight.com/training/Courses/TableOfContents/visual-studio-data-tools-developers

image

Enjoy!

February 11, 2014

Deploying a DACPAC with DacFx API

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using the DacFx API.

The DacFx API is a set of classes that you can use in your code to perform operations on a DACPAC. This allows you to write your own DACPAC utility application or include DACPAC functionality in any application.

DacFx API Version 3.0 is defined in Microsoft.SqlServer.Dac, which is a different DLL than prior DacFx API versions. Along with a new DLL, the functionality in Version 3.0 changed significantly from prior versions. The information in this post is for Version 3.0 and won’t work with prior DacFx API versions.

NOTE: DacFx 3.0 can work with DACPACs from older versions, but only generate Dac 3.0 formats.

Why would you want to write your own code to process a DACPAC when you can deploy a DACPAC with existing tools?

  • You can completely control the target connection and database(s) used, the DACPAC that is used, and the deployment options.
  • You can repeat the processing of the DACPAC for multiple databases.

For example, say you have a set of testers, each with their own copy of the database so they can better verify their results. You can store their connections in a table or configuration file. Then write a DACPAC utility application that loops through each connection and deploys the DACPAC to each tester’s database in one operation.

The code below is in C#, but this technique works in VB.NET as well.

using System;
using System.Collections.Generic;
using Microsoft.SqlServer.Dac;

namespace DacpacUtility
{
    public class DacpacService
    {
        public List<string> MessageList { get; set; }

        public DacpacService()
        {
            MessageList = new List<string>();
        }

        public bool ProcessDacPac(string connectionString,
                                    string databaseName,
                                    string dacpacName)
        {
            bool success = true;

            MessageList.Add("*** Start of processing for " +
                            
databaseName);

            var dacOptions = new DacDeployOptions();
            dacOptions.BlockOnPossibleDataLoss = false;

            var dacServiceInstance = new DacServices(connectionString);
            dacServiceInstance.ProgressChanged +=
              new EventHandler<DacProgressEventArgs>((s, e) =>
                            MessageList.Add(e.Message));
            dacServiceInstance.Message +=
              new EventHandler<DacMessageEventArgs>((s, e) =>
                            MessageList.Add(e.Message.Message));

            try
            {
                using (DacPackage dacpac = DacPackage.Load(dacpacName))
                {
                    dacServiceInstance.Deploy(dacpac, databaseName,
                                            upgradeExisting: true,
                                            options: dacOptions);
                }

            }
            catch (Exception ex)
            {
                success = false;
                MessageList.Add(ex.Message);
            }

            return success;
        }
    }
}

Walking through this code:

  • A MessageList property retains any messages generated by the process. The code using this class can display the contents of this list.
  • The constructor initializes the MessageList.
  • The only method in this class deploys a DACPAC file.
  • The parameters to the method define the appropriate target connection, target database, and DACPAC path and file name.
  • The process kicks off with a starting message in the MessageList.
  • If desired, you can define deployment options. In this example, the only option that is set is the BlockOnPossibleDataLoss.
  • An instance of the DacFx DacServices class is then initialized.
  • Optionally, you can elect to respond to ProgressChanged events. These events are invoked when the state of the operation changes. In this case, any ProgressChanged message are added to the MessageList.
  • Optionally, you can elect to respond to Message events. These events are invoked when an operation reports status updates or errors.
  • Within a Try block, the code loads the DACPAC using the DacFx DacPackage class. The argument is the full path and file name to the DACPAC file.
  • Finally, the DACPAC is deployed using the Deploy method of the DacServices class.

To see how this method is called, here is an automated code test:

using DacpacUtility;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace DacpacUtility.Test
{
    [TestClass()]
    public class DacpacServiceTest
    {
        public TestContext TestContext { get; set; }

        [TestMethod()]
        public void ProcessDacPacTest()
        {
            // Arrange
            var target = new DacpacUtility.DacpacService();

            // Act
            var success = target.ProcessDacPac(
                @"Data Source=.\sqlexpress;Integrated Security=True;",
                 "TestACM",
                @"<path>\ACM.Database.dacpac");

            // Assert
            Assert.AreEqual(true, success);

            // Display the messages
            foreach (var item in target.MessageList)
            {
                TestContext.WriteLine(item);
            }
        }
    }
}

Walking through this code:

  • A TestContext property is defined to write out to the TestContext.
  • The arrange process sets up the instance of our DacpacService class.
  • The act process calls the ProcessDacPac method of our DacpacService class and passes the appropriate arguments. Be sure to replace <path> with the path to your DACPAC.
  • The assert process asserts that the deployment was successful.
  • It then writes out all of the messages.

For this example, the messages appear as follows:

image

Notice that there is some message duplication here. That is because we put both the Messages and the Process Changed text in the list.

Enjoy!

For more information on this and other SQL Server Data Tools (SSDT) features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

February 10, 2014

Deploying a DACPAC with PowerShell

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using Windows PowerShell.

Windows PowerShell is a task automation and configuration management framework that aids in performing Windows administrative tasks.

Here is a script that will deploy a DACPAC.

add-type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"

$dacService = new-object Microsoft.SqlServer.Dac.DacServices "server=.\sqlexpress"

$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load("<Path> \ACM.Database.dacpac")

$dacService.deploy($dp, "TestACM", "True")

image

The first line adds the Dac DLL to the PowerShell session. Add-Type is a Utility Cmdlet that adds any .NET Framework type to a PowerShell session. Change the directory as appropriate for your system.

The second line creates an instance of the DacServices object and defines the SQL Server instance for the connection. In this example, we are using SqlExpress.

The third line loads the DACPAC. Be sure to change <Path> to the path of your DACPAC and that it is all on one line.

The last line performs the deployment. The first argument is the loaded DACPAC. The second argument is the database that is the target of the deployment. The third argument is whether to allow update of an existing schema. This is "True" because we want to allow this script to upgrade the TestACM database if it already exists.

For more information on using PowerShell with DACPACs, see this blog post.

Enjoy!

For more information on this and other SQL Server Data Tools (SSDT) features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

February 7, 2014

Deploying a DACPAC with SqlPackage

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using SqlPackage.

SqlPackage is a command line utility that automates DACPAC operations, including publishing a DACPAC to a target database. On my system, I found SqlPackage.exe in: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin.

SqlPackage has many parameters, properties, and variables you can use to perform DACPAC operations. See this MSDN link for details.

The benefits of using SqlPackage are:

  • Its straight-forward and the API is clear.
  • You can include it in a batch file.
  • You can use it to automate your deployment process.

Here are the basic steps for deploying a DACPAC using SqlPackage from the command line:

  • Open a command prompt (cmd.exe).
  • Execute SqlPackage.exe with the desired parameters.

For example: to deploy a DACPAC using a publishing profile, the command would look like this:

“<Path>\SqlPackage.exe”
/Action:Publish
/SourceFile:“<Path>\ACM.Database.dacpac”
/Profile:”<Path>\ACM.Database.publish.xml"

Where <Path> is the appropriate path to your files.

Executing the command displays status messages as it performs the publish operation as shown in the screen shot below.

image

As another example: to deploy a DACPAC by specifying the target database information instead of using a publishing profile, the command would look like this:

“<Path>\SqlPackage.exe”
/Action:Publish
/SourceFile:“<Path>\ACM.Database.dacpac”
/TargetDatabaseName:TestACM
/TargetServerName:".\sqlExpress"

Where <Path> is the appropriate path to your files.

The result is shown below.

image

Use SqlPackage to deploy the DACPAC anytime you want to perform the deployment using the Command window or within a script.

Enjoy!

For more information on this and other SQL Server Data Tools (SSDT) features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

Deploying a DACPAC with SQL Server Management Studio

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using SQL Server Management Studio.

Many DBA’s are more comfortable using SQL Server Management Studio. So if the developers provide a DBA with a DACPAC, the DBA may prefer to use SQL Server Management Studio to deploy it.

This post covers the steps for using SQL Server Management Studio 2012 to deploy a DACPAC. For SQL Server Management Studio 2008 R2, the steps are different and it only supports older versions of DACPACs. However, you CAN use SQL Server Management Studio 2012 to connect to an older SQL Server instance, such as SQL Server 2005 or 2008 R2, and deploy a DACPAC.

Here are the steps for deploying a DACPAC with SQL Server Management Studio 2012:

  • Open SQL Server Management Studio.
  • Connect to the SQL Server Instance containing the database to deploy to.
    • Notice that in the example below, I am connecting to a SQL Server 2008 R2 SQL Express instance.
  • Navigate the tree in the Object Explorer to the database to deploy to.
  • Right-click on the database name and select Tasks | Upgrade Data-tier Application…

image

  • The Upgrade Data-tier Application wizard is displayed. It presents the set of steps that will be executed by the wizard.

image

  • Click Next.

image

  • Use the Browse button to find and select the DACPAC file. It is located in the Bin\Debug directory of the database project on the developer’s machine. Or where ever it was put if the file was provided to a DBA or other individual.
    • The default expected location is: C:\Users\<userName>\ Documents\SQL Server Management Studio\DAC Packages
  • Click Next.

image

  • The information in the DACPAC is compared with the target database.
    • If the target database was changed externally from a DACPAC deployment (by making changes directly to the database), you are warned that the database was changed.
    • You can optional proceed anyway, or exit the wizard and research the issue.
  • Click Next.

image

  • The deployment script is automatically generated.
  • From the above dialog, you can optionally select to save the deployment script to a file.
  • When ready, click Next.

image

  • The Upgrade Plan is then displayed for review.
    • You can elect to save the report.
    • You again have the option to save the generated script.
  • Click Next.

image

  • A summary is displayed containing the information that will be used during the deployment.
    • Note that in the above screen shot, I opened each node in the summary so you can see the details.
  • When you are happy with the summary information, click Next and the deployment process (finally!) begins.

image

  • The generated script is executed, completing the DACPAC deployment process.
  • If desired, click Save Report.
  • Then click Finish.

If you compare this process to the steps for deployment using Visual Studio, you can see that there are MANY more steps. But the DBA may be happy with all of these extra steps because they provide additional verification before deploying the DACPAC.

Enjoy!

For more information on this and other SQL Server Data Tools (SSDT) features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

February 5, 2014

Deploying a DACPAC with Visual Studio

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using Visual Studio.

Starting with Visual Studio 2013, the SQL Server data tools (SSDT) are available in*every* edition of Visual Studio, including the Express editions. So the DBA or other deployment personnel can use one of the free Express editions of Visual Studio to manage database projects and deploy DACPACs.

If you have Visual Studio 2012 or older, see this blog post for information on obtaining the appropriate SSDT.

Here are the steps for deploying a DACPAC with Visual Studio:

  • Open the SQL Server Object Explorer window (View | SQL Server Object Explorer).
  • Navigate to the database you wish to deploy to. This is the database that will be updated. In this case it is TestACM to deploy the DACPAC to the test database.
  • Right-click on the database and select Publish Data-tier Application… from the context menu.

image

  • The Publish Data-tier Application dialog is displayed (see below).
  • Use the Browse button to find and select the DACPAC file. It is located in the Bin\Debug directory of the database project.

image

  • When you are finished specifying the publish parameters, click the Publish button to publish the DACPAC to the target database.

The status of the deployment appears in the Data Tools Operations window.

image

Use the SQL Server Object Explorer within Visual Studio to deploy a DACPAC to any database.

Enjoy!

For more information on this and other SQL Server Data Tools (SSDT) features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

Deploying a DACPAC

If you are using Visual Studio 2012 or above, you can create a SQL Server Database Project to include all of your table, view, stored procedure, and data scripts. (See #1 below)

When you build the Database Project (#2), Visual Studio generates a DACPAC file (#3). The DACPAC is a single unit of deployment that you, your DBA, or another individual can use to deploy database schema changes or reference data.

When deployed, the SQL Server Data Tools (SSDT) perform some magic. They compare (#4) the contents of the "master" database as defined in the DACPAC (#3) to the target deployment database.

SSDT then automatically generates (#5) a change script (#6). The change script includes all of the changes to apply to the target database to bring its schema up to match the schema defined in the DACPAC and to deploy any reference data defined in the DACPAC.

The SSDT then executes (#7) the script against the target database and the deployment is complete.

COOL!

image

So I created my database project, built it to generate the DACPAC. Now what? How do I make it do all of that magic? How do I (or my DBA) actually deploy the DACPAC?

There are many different ways to deploy a DACPAC:

Click a link above to view details on deploying a DACPAC with each of these techniques.

Enjoy!

For more information on this and other SSDT features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

October 17, 2013

Who Moved My Cheese: SQL Menu

Getting a new version of Visual Studio can be fun, especially when there are cool new features. But a new version can also be frustrating when original features are moved or unexpectedly changed.

The first noticeable change is the Visual Studio menu.

Visual Studio 2012 included a SQL menu (shown in red below) that provided access to some of the SQL Server Data Tools (SSDT).

image

When you first open Visual Studio 2013, the SQL menu is gone:

image

Well, it’s not really gone. It has moved to the Tools menu:

image

When in doubt … just type it into the Quick Launch bar in the upper right corner:

image

That will take you directly to the SQL menu options.

Enjoy!

For more information on the new Visual Studio 2013 features, check out my upcoming Pluralsight course: Mastering Visual Studio 2013.

August 12, 2013

Transaction-SQL Editor

The Transact-SQL Editor option in Visual Studio 2012 allows you to query your database directly from Visual Studio. It is part of the SQL Server Data Tools (SSDT) provided in Visual Studio.

See this prior post for more information on SSDT.

With the Transact-SQL Editor option, you can connect to any running SQL Server instance. You can then write any T-SQL statement (or set of statements) and execute them.

To execute a query:

1) Select SQL | Transact-SQL Editor | New Query…

A standard connect dialog is displayed for you to connect to the desired instance of SQL Server.

image

2) Enter the appropriate connection information and click Connect.

The Transact-SQL Editor window is then opened.

image

3) Type in one or more T-SQL statements.

image

4) Click the Execute button (left most icon in the toolbar).

The results appear in the bottom pane.

image

Disappointingly, the results are not editable. So you can’t edit the data from this window. You can edit the T-SQL and run the query again.

Use the Transact-SQL Editor option any time you need to execute a query from within Visual Studio.

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

Next Page »

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs