public void Add(DbConnector dbConn) {
if (ChangeID == Guid.Empty) {
ChangeID = Guid.NewGuid();
}
string q = string.Format("insert into A_SCHEMA_CHANGES_LOG (ChangeID, ChangeNo, ScriptName, DateInstall) values ('{0}', {1}, '{2}', '{3}')", ChangeID, ChangeNo, ScriptName, DateInstall.ToString(CultureInfo.InvariantCulture));
dbConn.SetQueryText(q);
dbConn.Execute();
}
public static DbTable GetByName(DbConnector dbConn, string name) {
dbConn.SetQueryText(string.Format("{0} where type = 'U' and name = '{1}'", _selectQuery, name));
dbConn.Execute();
if (!dbConn.NextRow()) {
return null;
}
var t = new DbTable();
t.FromDb(dbConn);
return t;
}
public static DbSchemaChange GetByNo(DbConnector dbConn, uint changeNo) {
dbConn.SetQueryText(string.Format("select ChangeID, ChangeNo, ScriptName, DateInstall from A_SCHEMA_CHANGES_LOG where ChangeNo = {0}", changeNo));
dbConn.Execute();
if (!dbConn.NextRow()) {
return null;
}
var sch = new DbSchemaChange();
sch.FromDb(dbConn);
return sch;
}
public static DbSchemaChange GetLast(DbConnector dbConn) {
dbConn.SetQueryText("select ChangeID, ChangeNo, ScriptName, DateInstall from A_SCHEMA_CHANGES_LOG order by ChangeNo desc");
dbConn.Execute();
if (!dbConn.NextRow()) {
return null;
}
var sch = new DbSchemaChange();
sch.FromDb(dbConn);
return sch;
}
public static List<DbFunction> GetAll(DbConnector dbConn) {
dbConn.SetQueryText(string.Format("{0} where type in (N'FN', N'IF', N'TF', N'FS', N'FT')", _selectQuery));
dbConn.Execute();
List<DbFunction> functions = new List<DbFunction>();
while (dbConn.NextRow()) {
var f = new DbFunction();
f.FromDb(dbConn);
functions.Add(f);
}
return functions;
}
public static List<DbView> GetAll(DbConnector dbConn) {
dbConn.SetQueryText(string.Format("{0} where type = N'V'", _selectQuery));
dbConn.Execute();
List<DbView> views = new List<DbView>();
while (dbConn.NextRow()) {
var v = new DbView();
v.FromDb(dbConn);
views.Add(v);
}
return views;
}
public static List<DbProcedure> GetAll(DbConnector dbConn) {
dbConn.SetQueryText(string.Format("{0} where type in (N'P', N'PC')", _selectQuery));
dbConn.Execute();
List<DbProcedure> procs = new List<DbProcedure>();
while (dbConn.NextRow()) {
var p = new DbProcedure();
p.FromDb(dbConn);
procs.Add(p);
}
return procs;
}
public static List<DbTrigger> GetAll(DbConnector dbConn) {
dbConn.SetQueryText(string.Format("{0} where type = N'TR'", _selectQuery));
dbConn.Execute();
List<DbTrigger> trigs = new List<DbTrigger>();
while (dbConn.NextRow()) {
var t = new DbTrigger();
t.FromDb(dbConn);
trigs.Add(t);
}
return trigs;
}
public static List<DbTable> GetAll(DbConnector dbConn) {
dbConn.SetQueryText(string.Format("{0} where type = 'U'", _selectQuery));
dbConn.Execute();
List<DbTable> tables = new List<DbTable>();
while (dbConn.NextRow()) {
var t = new DbTable();
t.FromDb(dbConn);
tables.Add(t);
}
return tables;
}
private static bool _executeScriptFolder(DbConnector dbConn, string scriptFolder) {
string scriptPath = Path.Combine(_project, scriptFolder);
if (!Directory.Exists(scriptPath)) {
Console.WriteLine("DbBuilder: Error: Script path {0} not found. STOP !", scriptPath);
return false;
}
var scriptFiles = Directory.GetFiles(scriptPath, "*.*", SearchOption.TopDirectoryOnly);
foreach (var scriptFile in scriptFiles) {
if (!_executeScript(dbConn, scriptFile)) {
return false;
}
}
return true;
}
private static bool _dropProcedures(DbConnector dbConn) {
int i = 0;
Console.WriteLine();
Console.WriteLine("DbBuilder: Deleting procedures...");
for (i = 0; i < 100; i++) {
var procs = DbProcedure.GetAll(dbConn);
if (procs.Count == 0) {
break;
}
foreach (var p in procs) {
try {
Console.WriteLine("DbBuilder: Deleting procedure {0}", p.Name);
p.Delete(dbConn);
} catch (System.Exception e) {
_printException(e);
}
}
}
return (i < 100) ? true : false;
}
private static bool _runPostBuildScripts(DbConnector dbConn) {
string postbuildScriptFolder = "PostBuildScripts";
Console.WriteLine();
Console.WriteLine("DbBuilder: Running postbuild scripts");
if (!Directory.Exists(Path.Combine(_project, postbuildScriptFolder))) {
Console.WriteLine("DbBuilder: {0} Postbuild scripts folder does not exists. Exit.", postbuildScriptFolder);
return true;
}
if (!_executeScriptFolder(dbConn, postbuildScriptFolder)) {
Console.WriteLine("DbBuilder: Error: Execute postbuild scripts failed, STOP !");
return false;
}
var mp = Path.Combine(postbuildScriptFolder, System.Environment.MachineName);
if (Directory.Exists(Path.Combine(_project, mp))) {
Console.WriteLine("DbBuilder: Running postbuild scripts from machine dependent directory");
if (!_executeScriptFolder(dbConn, mp)) {
Console.WriteLine("DbBuilder: Error: Execute machine dependent postbuild scripts failed, STOP !");
return false;
}
}
Console.WriteLine("DbBuilder: Done.");
return true;
}
private static bool _dropTriggers(DbConnector dbConn) {
int i = 0;
Console.WriteLine();
Console.WriteLine("DbBuilder: Deleting triggers...");
for (i = 0; i < 100; i++) {
var trigs = DbTrigger.GetAll(dbConn);
if (trigs.Count == 0) {
break;
}
foreach (var t in trigs) {
try {
Console.WriteLine("DbBuilder: Deleting trigger {0}", t.Name);
t.Delete(dbConn);
} catch (System.Exception e) {
_printException(e);
}
}
}
return (i < 100) ? true : false;
}
private static bool _clean(DbConnector dbConn) {
Console.WriteLine();
Console.WriteLine("DbBuilder: Cleaning database");
if (!_dropProgramObjects(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to drop programmable objects. STOP !");
return false;
}
if (!_dropSchemaObjects(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to drop schema objects. STOP !");
return false;
}
Console.WriteLine("DbBuilder: Done.");
return true;
}
private static bool _build(DbConnector dbConn) {
Console.WriteLine();
Console.WriteLine("DbBuilder: Building database");
if (!_dropProgramObjects(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to drop programmable objects. STOP !");
return false;
}
if (!_applySchemaChanges(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to apply schema changes. STOP !");
return false;
}
if (!_createProgramObjects(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to create programmable objects. STOP !");
return false;
}
Console.WriteLine("DbBuilder: Done.");
return true;
}
private static bool _applySchemaChanges(DbConnector dbConn) {
Console.WriteLine("DbBuilder: Applying schema changes...");
string changesPath = Path.Combine(_project, "Schema\\Changes");
if (!Directory.Exists(changesPath)) {
Console.WriteLine("DbBuilder: No changes directory found. Exit.");
return true;
}
var changeFiles = Directory.GetFiles(changesPath, "*.*", SearchOption.TopDirectoryOnly);
foreach (var changeFile in changeFiles) {
string cfn = Path.GetFileNameWithoutExtension(changeFile);
if (cfn.Length != 9) {
continue;
}
if (!_applySchemaChange(dbConn, changeFile)) {
return false;
}
}
Console.WriteLine("DbBuilder: Done.");
return true;
}
static bool _createProgramObjects(DbConnector dbConn) {
Console.WriteLine();
Console.WriteLine("DbBuilder: Creating programmable objects...");
Console.WriteLine("DbBuilder: Creating functions...");
if (!_executeScriptFolder(dbConn, "Program\\Functions")) {
Console.WriteLine("DbBuilder: Error: Failed to create functions, STOP !");
return false;
}
Console.WriteLine("DbBuilder: Creating views...");
if (!_executeScriptFolder(dbConn, "Program\\Views")) {
Console.WriteLine("DbBuilder: Error: Failed to create views, STOP !");
return false;
}
Console.WriteLine("DbBuilder: Creating stored procedures...");
if (!_executeScriptFolder(dbConn, "Program\\Procedures")) {
Console.WriteLine("DbBuilder: Error: Failed to create stored procedures, STOP !");
return false;
}
Console.WriteLine("DbBuilder: Creating triggers...");
if (!_executeScriptFolder(dbConn, "Program\\Triggers")) {
Console.WriteLine("DbBuilder: Error: Failed to create triggers, STOP !");
return false;
}
Console.WriteLine("DbBuilder: Done.");
return true;
}
internal void FromDb(DbConnector dbConn) {
_propMap.FromDb(dbConn);
}
private static bool _dropViews(DbConnector dbConn) {
int i = 0;
Console.WriteLine();
Console.WriteLine("DbBuilder: Deleting views...");
for (i = 0; i < 100; i++) {
var views = DbView.GetAll(dbConn);
if (views.Count == 0) {
break;
}
foreach (var v in views) {
try {
Console.WriteLine("DbBuilder: Deleting view {0}", v.Name);
v.Delete(dbConn);
} catch (System.Exception e) {
_printException(e);
}
}
}
return (i < 100) ? true : false;
}
public void Delete(DbConnector dbConn) {
dbConn.SetQueryText(string.Format("drop trigger [dbo].[{0}]", Name));
dbConn.Execute();
}
private static bool _executeScript(DbConnector dbConn, string scriptFile) {
try {
Console.WriteLine("DbBuilder: Executing script '{0}'", scriptFile);
if (!File.Exists(scriptFile)) {
Console.WriteLine("DbBuilder: Error: Script file'{0} not found, STOP !", scriptFile);
return false;
}
var script = File.ReadAllText(scriptFile);
var queries = script.Split(new []{ "GO\r\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (var qd in queries) {
string q = qd.Trim();
if (q.Length == 0) {
continue;
}
dbConn.SetQueryText(q);
dbConn.Execute();
}
} catch (System.Exception e) {
_printException(e);
Console.WriteLine("DbBuilder: Error: Exception in execution, STOP !");
return false;
}
return true;
}
private static bool _dropSchemaObjects(DbConnector dbConn) {
Console.WriteLine();
Console.WriteLine("DbBuilder: Deleting schema objects...");
if (!_dropTables(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to delete tables, STOP !");
return false;
}
Console.WriteLine("DbBuilder: Done.");
return true;
}
private static bool _dropProgramObjects(DbConnector dbConn) {
Console.WriteLine();
Console.WriteLine("DbBuilder: Deleting programmable objects...");
if (!_dropTriggers(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to delete triggers, STOP !");
return false;
}
if (!_dropProcedures(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to delete stored procedures, STOP !");
return false;
}
if (!_dropViews(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to delete views, STOP !");
return false;
}
if (!_dropFunctions(dbConn)) {
Console.WriteLine("DbBuilder: Error: Failed to delete functions, STOP !");
return false;
}
Console.WriteLine("DbBuilder: Done.");
return true;
}
private static bool _dropFunctions(DbConnector dbConn) {
int i = 0;
Console.WriteLine();
Console.WriteLine("DbBuilder: Deleting functions...");
for (i = 0; i < 100; i++) {
var funcs = DbFunction.GetAll(dbConn);
if (funcs.Count == 0) {
break;
}
foreach (var f in funcs) {
try {
Console.WriteLine("DbBuilder: Deleting function {0}", f.Name);
f.Delete(dbConn);
} catch (System.Exception e) {
_printException(e);
}
}
}
return (i < 100) ? true : false;
}
/*
public void ToDbAsParameter(DbConnector _DBconn) {
FieldInfo cField = null;
if(_object == null) {
return;
}
cField = _object.GetType().GetField(m_strPropertyName);
if(cField == null) {
return;
}
_DBconn.AddParameter("_" + m_strName,cField.GetValue(_object));
}*/
/*
public void ToXml(XmlNode _xmlNode) {
FieldInfo cField = null;
XmlNode xmlName;
object cValue;
if(_object == null) {
return;
}
cField = m_cObject.GetType().GetField(m_strPropertyName);
if(cField == null) {
return;
}
cValue = cField.GetValue(m_cObject);
xmlName = _xmlNode.OwnerDocument.CreateNode(XmlNodeType.Attribute, m_strName, "");
if(cValue != null) {
xmlName.Value = cValue.ToString();
}
_xmlNode.Attributes.SetNamedItem(xmlName);
}*/
public void FromDb(DbConnector _DBconn) {
FieldInfo cField = null;
string strFieldType = "";
object cFieldValue = null;
if (_object == null) {
return;
}
cField = _object.GetType().GetField(m_strPropertyName);
if (cField == null) {
return;
}
cFieldValue = _DBconn.GetFieldValue(m_strName);
if (cFieldValue is System.DBNull) {
cFieldValue = null;
}
strFieldType = cField.FieldType.Name;
switch (strFieldType) {
case "UInt32": {
var fieldValue = (int)((cFieldValue != null) ? cFieldValue : 0);
cField.SetValue(_object, (UInt32)fieldValue);
break;
}
default:
cField.SetValue(_object, cFieldValue);
break;
}
}
private static bool _createSchema(DbConnector dbConn) {
Console.WriteLine("DbBuilder: Creating schema changes log table...");
if (!_executeScript(dbConn, Path.Combine(_project, "Schema\\SchemaChangesLogTable.sql"))) {
Console.WriteLine("DbBuilder: Error: Failed to create schema changes log table. STOP !");
return false;
}
Console.WriteLine("DbBuilder: Creating schema...");
DbSchemaChange sch = new DbSchemaChange();
sch.ChangeID = Guid.NewGuid();
sch.ChangeNo = 0;
sch.ScriptName = "Schema.sql";
sch.DateInstall = DateTime.Now;
if (!_executeScript(dbConn, Path.Combine(_project, "Schema\\" + sch.ScriptName))) {
Console.WriteLine("DbBuilder: Error: Failed to create schema. STOP !");
return false;
}
sch.Add(dbConn);
Console.WriteLine("DbBuilder: Done.");
return true;
}
/*
public void ToXml(XmlNode _xmlNode)
{
foreach(PropertyProcessor cProc in m_cProcessors)
{
if((cProc.m_dwOperationFlags & (uint)OperationFlags.XmlWrite) != 0)
{
cProc.ToXml(_xmlNode);
}
}
}*/
public void FromDb(DbConnector _DBconn) {
foreach (PropertyProcessor cProc in m_cProcessors) {
if ((cProc.m_dwOperationFlags & (uint)OperationFlags.DbRead) != 0) {
cProc.FromDb(_DBconn);
}
}
}
private static bool _applySchemaChange(DbConnector dbConn, string changeFile) {
Console.WriteLine("DbBuilder: Applying schema change script {0}...", changeFile);
string cfn = Path.GetFileNameWithoutExtension(changeFile);
if (cfn.Length != 9) {
goto WrongScriptName;
}
string pfx = cfn.Substring(0, 4);
if (pfx != "Sch-") {
goto WrongScriptName;
}
uint chn = (uint)Convert.ToUInt32(cfn.Substring(4));
if (chn == 0) {
goto WrongScriptName;
}
var dbSch = DbSchemaChange.GetByNo(dbConn, chn);
if (dbSch != null) {
Console.WriteLine("DbBuilder: Change #{0} already installed. Exit.", chn);
return true;
}
dbSch = DbSchemaChange.GetLast(dbConn);
if (dbSch == null) {
Console.WriteLine("DbBuilder: Error: Schema seems not installed. STOP !");
return false;
}
if ((chn - 1) != dbSch.ChangeNo) {
Console.WriteLine("DbBuilder: Error: Bad changes sequence. STOP !");
return false;
}
if (!_executeScript(dbConn, changeFile)) {
Console.WriteLine("DbBuilder: Error: Script {0} failed. STOP !", changeFile);
return false;
}
dbSch = new DbSchemaChange();
dbSch.ChangeID = Guid.NewGuid();
dbSch.ChangeNo = chn;
dbSch.ScriptName = cfn;
dbSch.DateInstall = DateTime.Now;
dbSch.Add(dbConn);
Console.WriteLine("DbBuilder: Schema change #{0}...", chn);
return true;
WrongScriptName:
Console.WriteLine("DbBuilder: Error: Wrong script name. STOP !");
return false;
}
