DB, UI and MSI builds with TFS

I just finished an app called “C:\XYZ\Content
Center\Database\ABC_Dev\DbBuilder”
It’s a simple app that parses through a set of Build items (sql or
project files) and builds them against a DB.

The Build items, DB details and a few other things are configurable.

For ex, for a normal / daily build:
<add key="BuildItems"
value="Scripts\ABC50_ChangeScript.sql,Scripts\ABC52_ChangeScript.sql,Scrip
ts\ApABConfig.sql,Scripts\DeleteOnlyCode.sql,Views\Views.ssmssqlproj,Triggers\Triggers.ssm
ssqlproj,

Procedures\Procedures.ssmssqlproj,Functions\Functions.ssmssqlproj"/>

(I’ve also fixed DeleteOnlyCode.sql above and included dropping views)

More importantly, I’ve finished integrating it with the TFS build system
(which took longer than the actual application).

So we can fully automate our DB build now.
I’ve still not included it though since right now our DB build will
fail.

Please let me know if we should fix the DB errors first or if I should
go ahead and include it anyway.

Additional note:

With the above config, one can now do a core DB build on any DB just by

1. Changing the connection string
2. Taking a latest and
3. Running the exe

<add key="BasePath" value="C:\XYZ\Content Center\Database\ABC_Dev\"/>


<add key="ConnectionString" value="brazos;BuildDB;XYZ;yXYZy.01"/>


<add key="GenerateOnly" value="False"/>


<add key="BuildItemsOnce" value="Scripts\CLR_Installation.sql,Scripts\DeleteOnlyCode.sql,Scripts\ABC52_ChangeScript.sql,Scripts\ApABConfig.sql"/>
<add key="BuildItemsTwice" value="Views\Views.ssmssqlproj,Triggers\Triggers.ssmssqlproj,Procedures\Procedures.ssmssqlproj,Functions\Functions.ssmssqlproj"/>


class Program
{
const string BatchFileName = "FullScript.bat";
static string BasePath = ConfigurationManager.AppSettings["BasePath"];

static int Main(string[] args)
{
string[] BuildItemsOnce = ConfigurationManager.AppSettings["BuildItemsOnce"].Split(',');
string[] BuildItemsTwice = ConfigurationManager.AppSettings["BuildItemsTwice"].Split(',');
bool GenerateOnly = (ConfigurationManager.AppSettings["GenerateOnly"] == "True");

string[] ConnStrs;

if (args.Length < 1)
ConnStrs = ConfigurationManager.AppSettings["ConnectionString"].Split(';');
else
ConnStrs = args[0].Split(';');

using (StreamWriter SwFullScript = new StreamWriter(BatchFileName, false))
{
// ConnStrs comes in sets of 4
int SetCount = ConnStrs.Length/4;

for (int i = 0; i < SetCount; i++)
{
int StartPos = i * 4;

// not using SqlCommand because will have to check
// for BATCH separator in the file which requires parsing the file
string StrOsqlErr = string.Format("osql -n -r 1 -m-1 -S {0} -d {1} -U {2} -P {3} -i ",
ConnStrs[StartPos + 0],
ConnStrs[StartPos + 1],
ConnStrs[StartPos + 2],
ConnStrs[StartPos + 3]);

string StrOsqlClean = string.Format("osql -n -r 1 -m 25 -S {0} -d {1} -U {2} -P {3} -i ",
ConnStrs[StartPos + 0],
ConnStrs[StartPos + 1],
ConnStrs[StartPos + 2],
ConnStrs[StartPos + 3]);

parseItems(BuildItemsOnce, SwFullScript, StrOsqlErr);
parseItems(BuildItemsTwice, SwFullScript, StrOsqlClean);
parseItems(BuildItemsTwice, SwFullScript, StrOsqlErr);
}
}

if (GenerateOnly)
return 0;

// TFSbuild executes this console application
// which calls a batch file
// which calls the osql application

ProcessStartInfo PsiBatchFile = new ProcessStartInfo(BatchFileName);
PsiBatchFile.UseShellExecute = false;
PsiBatchFile.RedirectStandardError = true;

Process ProcBatchFile = Process.Start(PsiBatchFile);
string StandardError = ProcBatchFile.StandardError.ReadToEnd();

if (StandardError == null || StandardError.Length < 1)
{
return 0;
}
else
{
Console.WriteLine("STANDARDERROR BEGINS");
Console.WriteLine(StandardError);
Console.WriteLine("STANDARDERROR ENDS");
return 1;
}
}

static void parseItems(string[] BuildItems, StreamWriter Sw, string StrOsql)
{
foreach (string BuildItem in BuildItems)
{
if (BuildItem.EndsWith(".sql"))
{
// Single file
Sw.WriteLine(StrOsql + "\"" + BasePath + BuildItem + "\"");
}
else if (BuildItem.EndsWith(".ssmssqlproj"))
{
// Project file
FileInfo FiInput = new FileInfo(BasePath + BuildItem);
string DirectoryName = FiInput.DirectoryName;

using (StreamReader SrIn = FiInput.OpenText())
{
string Str = SrIn.ReadLine();
while (Str != null)
{
Str = Str.Trim(' ', '\t');
if (Str.StartsWith("<FileNode"))
{
Str = Str.Replace("<FileNode Name=\"", "");
Str = Str.Replace("\">", "");

Sw.WriteLine(StrOsql + "\"" + DirectoryName + "\\" + Str + "\"");
}
Str = SrIn.ReadLine();
}
}
}
}
}
}

<Target Name="AfterCompile">
<Exec Condition="'&(UI)'!='No'" Command=""D:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv" "C:\XYZ\Content Center\4.5\ABCWebComplete.sln" /Build Release"/>
<Copy Condition="'&(UI)'!='No'" SourceFiles="C:\XYZ\Content Center\4.5\ProductCenterSetup\Release\ProductCenter.msi" DestinationFolder="&(OutDir)" />
<Exec Condition="'&(JS)'!='No'" Command=""D:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv" "C:\XYZ\JobService\4.5\Job Service Solution\JobServiceComplete.sln" /Build Release"/>
<Copy Condition="'&(JS)'!='No'" SourceFiles="C:\XYZ\JobService\4.5\Job Service Solution\JobServiceSetup\Release\JobServiceSetup.msi" DestinationFolder="&(OutDir)" />
<Exec Condition="'&(DB)'!='No'" Command=""D:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv" "C:\XYZ\Content Center\Database\ABC_Dev\DbBuilder\DbBuilder.sln" /Build Release"/>
<Exec Condition="'&(DB)'!='No'" Command=""C:\XYZ\Content Center\Database\ABC_Dev\DbBuilder\DbBuilder\bin\Release\DbBuilder.exe" "&(ConnectionString)""/>
</Target>

Search

The Code is the Design

DB, UI and MSI builds with TFS

Comments

Post a Comment

Labels

Archive