private string ProcessSnippetChilds(SnippetInnerObject Obj, string ProcessedSnippet, string Type, bool All)
{
string sep, buff = "", searchString, fname, bkup = ProcessedSnippet;
int ocstart, oclen;
ChildType CurType;
List<string> PosibleChilds = null;
ChildSelector Cs = null;
if (Type.Equals("Fields", StringComparison.CurrentCultureIgnoreCase))
{
//fields
CurType = ChildType.Field;
searchString = All ? Obj.AllFieldsText : Obj.FieldsText;
}
else
{
//parameters
CurType = ChildType.Parameter;
searchString = All ? Obj.AllParamsText : Obj.ParamsText;
}
while (ProcessedSnippet.IndexOf(searchString) > 0)
{
ocstart = ProcessedSnippet.IndexOf(searchString);
oclen = (ProcessedSnippet.IndexOf('$', ocstart + 1) - ocstart) + 1;
if (oclen <= 0 || ProcessedSnippet.IndexOf('$', ocstart + 1) == -1)
{
MessageBox.Show("Error while processing the snippet, check sintax(not found ending $ for object " + Type + ")", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return "";
}
fname = ProcessedSnippet.Substring(ocstart, oclen).Trim('$');
sep = fname.Split(':')[2];
if (String.IsNullOrEmpty(sep) || sep.Trim().Length == 0)
{
MessageBox.Show("Error while processing the snippet, check sintax(not found object separator for object " + Type + ")", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return "";
}
sep = sep.Replace("\\n", Environment.NewLine).Replace("\\t", "\t");
if (All)
{
foreach (ISqlChild child in Obj.Object.Childs.Where(X => X.Kind == CurType))
{
if (String.IsNullOrEmpty(Obj.Alias))
{//object without an alias, must be a procedure or a scalar function
if (String.IsNullOrEmpty(buff))
buff += String.Format("{0}", child.Name);
else
buff += String.Format("{0}{1}", sep, child.Name);
}
else
{//object with an alias, must be a table, a view, or a table function
if (String.IsNullOrEmpty(buff))
buff += String.Format("{0}.{1}", Obj.Alias, child.Name);
else
buff += String.Format("{0}{1}.{2}", sep, Obj.Alias, child.Name);
}
}
ProcessedSnippet = ProcessedSnippet.Replace(String.Format("${0}$", fname), buff);
buff = "";
}
else
{
if (PosibleChilds == null || PosibleChilds.Count == 0)
{
PosibleChilds = Obj.Object.Childs.Where(X => X.Kind == CurType).Select(X => X.Name).ToList();
Cs = new ChildSelector("Select field to use in the snippet", String.Format("Select field of DB Object: {0}.{1}", Obj.Object.Schema, Obj.Object.Name), PosibleChilds, false, 1);
if (Cs.ShowDialog() == DialogResult.OK)
{
foreach (string child in Cs.SelectedChilds)
{
if (String.IsNullOrEmpty(Obj.Alias))
{//object without an alias, must be a procedure or a scalar function
if (String.IsNullOrEmpty(buff))
buff += String.Format("{0}", child);
else
buff += String.Format("{0}{1}", sep, child);
}
else
{//object with an alias, must be a table, a view, or a table function
if (String.IsNullOrEmpty(buff))
buff += String.Format("{0}.{1}", Obj.Alias, child);
else
buff += String.Format("{0}{1}.{2}", sep, Obj.Alias, child);
}
}
}
else
{//cancelled the snippet processing
return bkup;
}
}
else
{
foreach (string child in Cs.SelectedChilds)
{
if (String.IsNullOrEmpty(Obj.Alias))
{//object without an alias, must be a procedure or a scalar function
if (String.IsNullOrEmpty(buff))
buff += String.Format("{0}", child);
else
buff += String.Format("{0}{1}", sep, child);
}
else
{//object with an alias, must be a table, a view, or a table function
if (String.IsNullOrEmpty(buff))
buff += String.Format("{0}.{1}", Obj.Alias, child);
else
buff += String.Format("{0}{1}.{2}", sep, Obj.Alias, child);
}
}
}
ProcessedSnippet = ProcessedSnippet.Replace(String.Format("${0}$", fname), buff);
buff = "";
}
}
return ProcessedSnippet;
}
private void InsertSnippet(string SnippetScript)
{
string ProcessedSnippet = SnippetScript;
List<string> Objs;
List<SnippetInnerObject> InnerObjects = new List<SnippetInnerObject>();
if (ProcessedSnippet.IndexOf("$OBJ:") > 0)
{
Objs = DataProvider.DbObjects.Where(X => X.Kind != ObjectType.Schema && X.Kind != ObjectType.Alias).Select(X => X.Schema + "." + X.Name).ToList();
while (ProcessedSnippet.IndexOf("$OBJ:") > 0)
{
try
{
int start = ProcessedSnippet.IndexOf("$OBJ:");
int length = (ProcessedSnippet.IndexOf('$', start + 1) - start) + 1;
if (length <= 0 || ProcessedSnippet.IndexOf('$', start + 1) == -1)
{
MessageBox.Show("Error while processing the snippet, check sintax(not found ending $ for object)", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
string Id = ProcessedSnippet.Substring(start, length);
Id = Id.Trim('$').Split(':')[1];
if (String.IsNullOrEmpty(Id) || Id.Trim().Length == 0)
{
MessageBox.Show("Error while processing the snippet, check sintax(not found object id for object)", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
SnippetInnerObject Obj;
ObjectSelector Os = new ObjectSelector("Objects on the current database connection", "Select a database object", Objs, false, 5);
if (Os.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Obj = new SnippetInnerObject()
{
Id = Id,
Object = DataProvider.DbObjects.Find
(X => String.Format("{0}.{1}", X.Schema, X.Name).Equals(Os.SelectedObject)
&& X.Kind != ObjectType.Schema
&& X.Kind != ObjectType.Alias
)
};
switch (Obj.Object.Kind)
{
default:
case ObjectType.Table:
case ObjectType.View:
case ObjectType.TableFunction:
if (Obj.Id.StartsWith("na", StringComparison.CurrentCultureIgnoreCase))
{//this object must be managed without an alias, so we use <schema>.<object name> as an alias
Obj.Alias = String.Format("{0}.{1}", Obj.Object.Schema, Obj.Object.Name);
}
else
{
Obj.Alias = Obj.Object.Name.GetUpperCasedLetters(2).GetAsSentence();//camel cased objects FTW!!!
if (String.IsNullOrEmpty(Obj.Alias))
Obj.Alias = Obj.Object.Name.Substring(0, 2);//if not camel cased object, get the first 2 letters in the name
if (InnerObjects.Any(X => X.Alias.Equals(Obj.Alias)))//making sure this alias is unique
Obj.Alias += InnerObjects.Count + 1;
}
break;
case ObjectType.Procedure:
case ObjectType.ScalarFunction:
Obj.Alias = "";
break;
}
if (String.IsNullOrEmpty(Obj.Alias) || Obj.Alias.Contains("."))
{
ProcessedSnippet = ProcessedSnippet.Replace(Obj.Name, String.Format("{0}.{1}", Obj.Object.Schema, Obj.Object.Name));
}
else
{
ProcessedSnippet = ProcessedSnippet.Replace(Obj.Name, String.Format("{0}.{1} AS {2}", Obj.Object.Schema, Obj.Object.Name, Obj.Alias));
}
InnerObjects.Add(Obj);
//check for all fields
ProcessedSnippet = ProcessSnippetChilds(Obj, ProcessedSnippet, "Fields", true);
//check for field selector
ProcessedSnippet = ProcessSnippetChilds(Obj, ProcessedSnippet, "Fields", false);
//check for all Params
ProcessedSnippet = ProcessSnippetChilds(Obj, ProcessedSnippet, "Parameters", true);
//check for param selector
ProcessedSnippet = ProcessSnippetChilds(Obj, ProcessedSnippet, "Parameters", false);
}
else
{//cancelled the snippet processing
return;
}
}
catch (Exception)
{
MessageBox.Show("Error while processing the snippet, check sintax", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
}
Query.InsertString(ProcessedSnippet);
}
