MSBuild task to query database output multiple columns?

Question

I need to query a database and return a list of server and database names, that will be used in my TFS 2008 Team Build database build to deploy copies of our database to the servers and target database names returned from the query.  I would think there's a way to get these into an ItemGroup, and then I can use batch processing from there.  I just haven't found a way to get multiple columns returned yet.

Any help is appreciated, thanks.

Answer 1

Hello mr_burns,

Glad to work with you.

Do you want to convert data from database into an ItemGroup so that you can use them in MSBuild? If I misunderstood you, please let me know.

If you are writing a Custom Task to query data from database, you can return multile columns by setting them as meta data in the ItemGroups.
The SDK is documented at: http://msdn.microsoft.com/en-us/library/microsoft.build.framework.itaskitem.setmetadata.aspx

After you build an ITaskItem[] list, you can return them by Output attribute.

Here is an example code: http://msdn.microsoft.com/en-us/library/microsoft.build.framework.outputattribute.aspx

If you are not writing Custom Task for msbuild, can you let me know how you get the DB data? Thanks.

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hongye,

Yes you are correct, I am trying to convert database column output from a SQL query into an ItemGroup in my Team Build project.  I washoping to find that some clever developer already developed an MSBuild task to do this, but so far I haven't found any. No help from the ExtensionPack or the Community Tasks.  So it looks like I may need to write one myself.  I have seen discussions of people writing to a file and then reading from the file, but that seems more error prone and registers a bit high on the hackometer for me.

I'll review your links to the SDK and give it a shot.

Thanks,

Matt

Answer 3

 Hongye,

I'm still trying to find a shortcut and avoid building a custom task.  Is there a way to get the output from an Exec task running a SqlCmd.exe call?  The build log file contains the row output from the database, so it's so close! 

Snippet from the teambuild.proj:

<Exec ContinueOnError="false" Command="&quot;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE&quot; -S $(ImagingSystemServer) -d $(ImagingSystemDatabase) -q $(ServerDatabaseQuery)" />

And this is what I see in the build log file:

Using "Exec" task from assembly "Microsoft.Build.Tasks.v3.5, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a".
Task "Exec"
  Command:
  "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S IISDevSQL01 -d ImagingSystemDev1 -q "SELECT Column1, Column2 FROM Table 1"
  ServerName                                                                       DatabaseName                                                                    
  -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
  [IISDevSQL02]                                                                    [HS549901]                                                                      
  
  (1 rows affected)
Done executing task "Exec".

Can I get that output and parse it?

Answer 4

If anyone is interested I did write a custom task for this.  You pass it a connection string and query command, and it returns the rows and columns to an ItemGroup in the output.

Task code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;
using System.Data;

namespace Drc.MSBuild.CustomTasks
{
    public class QueryOutputTask : Task
    {

        [Required]
        public string ConnectionString { get; set; }

        [Required]
        public string Query { get; set; }       

        [Output]
        public ITaskItem[] QueryResults { get; set; }

        public override bool Execute()
        {
            try
            {
                Log.LogMessageFromText("Executing requested query...", MessageImportance.Normal);
                GetData();
                return true;
            }
            catch (Exception ex)
            {
                Log.LogMessageFromText(String.Format("QueryOutput failed: {0}", ex.ToString()), MessageImportance.Normal);
                return false;
            }
           
        }

        private void GetData()
        {
            List<ITaskItem> outputRows = new List<ITaskItem>();
            ITaskItem newRow;
            DataSet dataSet = new DataSet();

            using (SqlCommand command = new SqlCommand(Query, new SqlConnection(ConnectionString)))
            {
                command.Connection.Open();   
             
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    string[] tables = { "Table1" };
                    dataSet.Load(reader, LoadOption.OverwriteChanges, tables);

                    for(int rowIndex=0; rowIndex < dataSet.Tables[0].Rows.Count; rowIndex++)
                    {                            
                        newRow = new TaskItem(String.Format("Row{0}", rowIndex + 1));

                        for(int columnIndex=0; columnIndex < dataSet.Tables[0].Columns.Count; columnIndex++)
                        {
                            newRow.SetMetadata(String.Format("Column{0}", columnIndex + 1), dataSet.Tables[0].Rows[rowIndex][columnIndex].ToString());
                        }

                        outputRows.Add(newRow);

                    }
                }
            }

            QueryResults = outputRows.ToArray();

        }


    }
}

And here is a sample testbuild.proj file to use it:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Demo" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="3.5">
	<UsingTask AssemblyFile="$(MSBuildProjectDirectory)\bin\Debug\Drc.MSBuild.CustomTasks.dll" TaskName="QueryOutputTask" />

	<Target Name="Demo">
		<Message Text="MSBuildProjectDirectory: $(MSBuildProjectDirectory)" />

		<QueryOutputTask ConnectionString="Data Source=YourServer%3bIntegrated Security=True%3bPooling=False;Initial Catalog=YourDatabase;"
						 Query="SELECT This, That, TheOtherThing FROM SomeTable">
			<Output ItemName="OutputRows" TaskParameter="QueryResults" />
		</QueryOutputTask>

		<Message Text="Output is: @(OutputRows)" />

		<Message Text="Row ID: %(OutputRows.Identity)" />
		<Message Text="Column 1: %(OutputRows.Column1)" />
		<Message Text="Column 2: %(OutputRows.Column2)" />
		<Message Text="Column 3: %(OutputRows.Column3)" />


	</Target>
</Project>

Hope it helps someone!

- Matt

Answer 5

Thanks for your sharing, Matt.

It is certainly very useful.

Have a nice day.


Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.