If you use CodeSmith Tools and are heavily dependent on the use of Stored Procedures as I am, then perhaps this little program will be of use to you. It's kind of a hack as I threw it together in less than an hour, but it serves to solve a fairly unique problem. Rather than going out and buying an expensive tool that compares your databases together and produces some sort of update script, I decided that it didn't make sense to make wholesale changes as there maybe some procs that aren't updated that have been deprecated or just aren't ready yet.
This little program will traverse your solution file and find all of your .csproj files. It will then read through that and look for any .csp files, which are codesmith project files. This is where I keep all of the procedures that are used for that specific Domain project. I edited my CodeSmith csp files and included two new variables at the very top that are very similar to the Connection String item. I name them the same in all my projects. Test and Live. This way when I run through the project file I will always know which are the Test Servers and which are the Production Servers.
After it has gone through all of the project files and built up a few lists of all the items that it needs to compare it utilizes the SQLDMO object to pull the actual Source Code for each procedure. I run it through a couple of string normalization steps so as not to confuse whitespace issues as well as mixed capitalization. Depending on who the DBA is they might decide to make some textual edits and I just don't want to have false-positives come up.
You will need a couple of references added.
System.Web
SQLDMO
I might update this and make it a bit more user friendly but this is just the first rough draft.
void Main()
{
string solutionFile = @"path to your solution file here";
string solutionfldr = @"path to your solution folder here";
List<string> projectFiles = new List<string>();
List<string> cspFiles = new List<string>();
List<DatabaseProcs> dbs = new List<DatabaseProcs>();
using (FileStream fs = new FileStream(solutionFile, FileMode.Open, FileAccess.Read))
{
using (StreamReader sr = new StreamReader(fs))
{
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (line.StartsWith("Project") && line.Contains(".csproj"))
{
var arr = line.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
projectFiles.Add(string.Format("{0}{1}", solutionfldr, arr[1].Replace("\"", "").Trim()));
}
}
}
}
//projectFiles.Dump();
foreach (string projfile in projectFiles)
{
string projFldr = projfile.Substring(0, projfile.LastIndexOf("\\"));
using (FileStream fs = new FileStream(projfile, FileMode.Open, FileAccess.Read))
{
using (StreamReader sr = new StreamReader(fs))
{
var xml = XElement.Parse(string.Format("<root>{0}</root>", sr.ReadToEnd()
.Replace("<?xml version=\"1.0\" encoding=\"utf-8\"?>","")
.Replace("xmlns=\"http://schemas.microsoft.com/developer/msbuild/2003\"", "")));
xml.Elements("Project")
.Elements("ItemGroup")
.Elements("Generate")
.Select(x => x.Attribute("Include"))
.Where (x => x.Value.Contains(".csp"))
.ToList()
.ForEach(a => cspFiles.Add(string.Format("{0}\\{1}", projFldr, a.Value)));
xml.Elements("Project")
.Elements("ItemGroup")
.Elements("None")
.Select (x => x.Attribute("Include"))
.Where (x => x.Value.Contains(".csp"))
.ToList()
.ForEach(a => cspFiles.Add(string.Format("{0}\\{1}", projFldr, a.Value)));
}
}
}
//cspFiles.Dump();
foreach (string csp in cspFiles)
{
DatabaseProcs db = new DatabaseProcs();
db.CSPName = csp.Substring(csp.LastIndexOf("\\")+1).Replace(".csp", "");
using (FileStream fs = new FileStream(csp, FileMode.Open, FileAccess.Read))
{
using (StreamReader sr = new StreamReader(fs))
{
var xml = XElement.Parse(sr.ReadToEnd().Replace("xmlns=\"http://www.codesmithtools.com/schema/csp.xsd\"", ""));
xml.Elements("propertySets")
.Elements("propertySet")
.Elements("property")
.Where (x => x.Attribute("name").Value == "IncludeList")
.Elements("stringList")
.Elements("string").Select (x => x.Value).ToList()
.ForEach(a => db.Procedures.Add(a.ToString().Replace("\\b", "")));
var srvrs = xml.Elements("variables").Elements("add").Where (x => x.Attribute("key").Value == "Live" || x.Attribute("key").Value == "Test");
//srvrs.Dump();
var test = srvrs.Where(s => s.Attribute("key").Value == "Test").Select (s => s.Attribute("value").Value).FirstOrDefault().Replace(';','&');
NameValueCollection testObj = HttpUtility.ParseQueryString(test);
var live = srvrs.Where(s => s.Attribute("key").Value == "Live").Select (s => s.Attribute("value").Value).FirstOrDefault().Replace(';','&');
NameValueCollection liveObj = HttpUtility.ParseQueryString(live);
db.LiveServer.Server = liveObj["Server"];
db.LiveServer.User = liveObj["User"];
db.LiveServer.Password = liveObj["Password"];
db.LiveServer.Database = liveObj["Database"];
db.TestServer.Server = testObj["Server"];
db.TestServer.User = testObj["User"];
db.TestServer.Password = testObj["Password"];
db.TestServer.Database = testObj["Database"];
}
}
dbs.Add(db);
}
//dbs.Dump();
string sep = Environment.NewLine + "GO;" + Environment.NewLine;
foreach (var srvr in dbs)
{
string grant = string.Empty;
grant = "grant exec on {0} to your_db_user_goes_here";
grant += Environment.NewLine + "go;" + Environment.NewLine;
List<ProcedureData> test = GetProcedureData(srvr.Procedures, srvr.TestServer);
List<ProcedureData> live = GetProcedureData(srvr.Procedures, srvr.LiveServer);
string filePath = string.Format("c:\\LinqPad\\SQLDeployments\\{0}.sql", srvr.CSPName);
Console.WriteLine(filePath);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
{
using (StreamWriter sw = new StreamWriter(fs))
{
test.GroupJoin(live, t => t.ProcedureName, l => l.ProcedureName, ( t, l ) => new { t, l})
.SelectMany (t => t.l.DefaultIfEmpty(new ProcedureData { ProcedureName = string.Empty, Script = string.Empty}), (t, l) => new { test = t.t, live = l})
.Select (t => new { ProcName = t.test.ProcedureName, TestProcScript = t.test.Script, LiveProcScript = t.live.Script})
.Where (x => Flatten(x.LiveProcScript) != Flatten(x.TestProcScript))
.ToList()
.ForEach(x => sw.Write(
(x.LiveProcScript.Length > 0
? x.TestProcScript.Replace("CREATE","ALTER").Replace("create","alter") + sep
: x.TestProcScript + sep + string.Format(grant, x.ProcName))));
test.GroupJoin(live, t => t.ProcedureName, l => l.ProcedureName, ( t, l ) => new { t, l})
.SelectMany (t => t.l.DefaultIfEmpty(new ProcedureData { ProcedureName = string.Empty, Script = string.Empty}), (t, l) => new { test = t.t, live = l})
.Select (t => new { ProcName = t.test.ProcedureName, TestProcScript = t.test.Script, LiveProcScript = t.live.Script})
.Select (n => new
{
ProcName = n.ProcName,
TestEqualsLive = Flatten(n.LiveProcScript) == Flatten(n.TestProcScript),
TestProcSize = Flatten(n.TestProcScript).Length,
LiveProcSize = Flatten(n.LiveProcScript).Length//,
//testScript = Flatten(n.TestProcScript),
//liveScript = Flatten(n.LiveProcScript)
})
.ToList()
.Dump();
}
}
}
}
public List<ProcedureData> GetProcedureData(List<string> procs, ServerData srvr)
{
List<ProcedureData> procedures = new List<ProcedureData>();
SQLDMO.SQLServer server = new SQLServer();
server.Connect(srvr.Server, srvr.User, srvr.Password);
foreach (SQLDMO.Database db in server.Databases)
{
if (db.Name.ToLower() == srvr.Database.ToLower())
{
foreach (SQLDMO.StoredProcedure procedure in db.StoredProcedures)
{
foreach (var proc in procs)
{
if (procedure.Name.ToLower() == proc.ToLower())
{
procedures.Add(new ProcedureData() { ProcedureName = proc, Script = procedure.Script() });
}
}
}
}
}
return procedures;
}
private static readonly Regex normalizeSpace = new Regex(@"\s+", RegexOptions.Compiled);
public string Flatten(string proc)
{
proc = proc.ToLower().Replace("[", "").Replace("]", "").Replace("\t", "").Trim();
var procarr = proc.Split(new char[] {'\n'}, StringSplitOptions.RemoveEmptyEntries);
return string.Join("\n", procarr.Select (p => p.Trim()).Where(p => p.Length > 1).Select(d => normalizeSpace.Replace(d, " ")).ToArray());
}
public string GetProcedure(SQLDMO.StoredProcedures procs, string procName)
{
foreach (SQLDMO.StoredProcedure proc in procs)
{
if (proc.Name.ToLower() == procName.ToLower())
{
return proc.Script();
}
}
return string.Empty;
}
public string GetFunction(SQLDMO.UserDefinedFunctions funcs, string funcName)
{
foreach (SQLDMO.UserDefinedFunction func in funcs)
{
if (func.Name.ToLower() == funcName.ToLower())
{
return func.Script();
}
}
return string.Empty;
}
public class DatabaseProcs
{
public List<string> Procedures;
public ServerData TestServer { get; set; }
public ServerData LiveServer { get; set; }
public string CSPName { get; set; }
public DatabaseProcs()
{
Procedures = new List<string>();
TestServer = new ServerData();
LiveServer = new ServerData();
}
}
public class ProcedureData
{
public string ProcedureName { get; set; }
public string Script { get; set; }
}
public class ServerData
{
public string Server { get; set; }
public string User { get; set; }
public string Password { get; set; }
public string Database { get; set; }
}
