public static CodeStatementCollection BuildDetectChangedMembers(TableViewTableTypeBase table)
{
CodeStatementCollection ValidationSetStatement = new CodeStatementCollection();
String PocoTypeName = "this";
ValidationSetStatement.Add(new CodeSnippetExpression("Boolean bResult = new Boolean()"));
ValidationSetStatement.Add(new CodeSnippetExpression("bResult = false"));
foreach (Column c in table.Columns)
{
MemberGraph mGraph = new MemberGraph(c);
CodeConditionStatement csTest1 = new CodeConditionStatement();
if (mGraph.IsNullable)
{
csTest1.Condition = new CodeSnippetExpression(PocoTypeName + "." + mGraph.PropertyName() + ".HasValue == true");
csTest1.TrueStatements.Add(new CodeSnippetExpression("bResult = true"));
}
else
{
csTest1.Condition = new CodeSnippetExpression(PocoTypeName + "." + mGraph.PropertyName() + " == null");
csTest1.TrueStatements.Add(new CodeSnippetExpression(""));
csTest1.FalseStatements.Add(new CodeSnippetExpression("bResult = true"));
}
}
return ValidationSetStatement;
}
/// <summary>
/// Contains all Fields
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public CodeConstructor GraphFullContructor(TableViewTableTypeBase table)
{
CodeConstructor ccFull = this.GraphBasicConstructor();
foreach (Column c in table.Columns)
{
MemberGraph mGraph = new MemberGraph(c);
ccFull.Parameters.Add(mGraph.GetParameter());
ccFull.Statements.Add(new CodeSnippetExpression("this." + mGraph.FieldName() + "=" + mGraph.ParameterName()));
}
return ccFull;
}
/// <summary>
/// Contains only Identity Parameters
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public CodeConstructor GraphIdentityConstructor(TableViewTableTypeBase table)
{
CodeConstructor ccIdentity = this.GraphBasicConstructor();
foreach (Column c in table.Columns)
{
if (c.InPrimaryKey)
{
MemberGraph mGraph = new MemberGraph(c);
ccIdentity.Parameters.Add(mGraph.GetParameter());
ccIdentity.Statements.Add(new CodeSnippetExpression("this." + mGraph.FieldName() + "=" + mGraph.ParameterName()));
}
}
return ccIdentity;
}
/// <summary>
/// Builds List of Code Statmenets that converts a Type into a DAL Parameter Statement
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static CodeStatementCollection BuildAttributeSetStatement(TableViewTableTypeBase table)
{
CodeStatementCollection AttributeSetStatement = new CodeStatementCollection();
String PocoTypeName = table.Name;
String FullPocoTypeName = PocoTypeName;
foreach (Column c in table.Columns)
{
MemberGraph mGraph = new MemberGraph(c);
if (mGraph.IsReadOnly)
{
// nothing yet
}
else
{
String DotNetTypeName = TypeConvertor.ToNetType(c.DataType.SqlDataType).ToString();
System.CodeDom.CodeConditionStatement ccsField = new CodeConditionStatement();
if (mGraph.IsNullable)
{
ccsField.Condition = new CodeSnippetExpression("query." + mGraph.PropertyName() + ".HasValue");
ccsField.TrueStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\",query." + mGraph.PropertyName() + ".Value, ParameterDirection.Input)"));
ccsField.FalseStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\", null , ParameterDirection.Input)"));
}
else
{
ccsField.Condition = new CodeSnippetExpression("query." + mGraph.PropertyName() + " == null");
ccsField.TrueStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\", null , ParameterDirection.Input)"));
ccsField.FalseStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\",query." + mGraph.PropertyName() + ", ParameterDirection.Input)"));
}
AttributeSetStatement.Add(ccsField);
}
}
return AttributeSetStatement;
}
public static CodeStatementCollection BuildDetectChangedMembers(TableViewTableTypeBase table)
{
CodeStatementCollection ValidationSetStatement = new CodeStatementCollection();
String PocoTypeName = "this";
ValidationSetStatement.Add(new CodeSnippetExpression("Boolean bResult = new Boolean()"));
ValidationSetStatement.Add(new CodeSnippetExpression("bResult = false"));
foreach (Column c in table.Columns)
{
MemberGraph mGraph = new MemberGraph(c);
CodeConditionStatement csTest1 = new CodeConditionStatement();
if (mGraph.IsNullable)
{
csTest1.Condition = new CodeSnippetExpression(PocoTypeName + "." + mGraph.PropertyName() + ".HasValue == true");
csTest1.TrueStatements.Add(new CodeSnippetExpression("bResult = true"));
}
else
{
csTest1.Condition = new CodeSnippetExpression(PocoTypeName + "." + mGraph.PropertyName() + " == null");
csTest1.TrueStatements.Add(new CodeSnippetExpression(""));
csTest1.FalseStatements.Add(new CodeSnippetExpression("bResult = true"));
}
}
return(ValidationSetStatement);
}
/// <summary>
/// Contains all Fields
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public CodeConstructor GraphFullContructor(TableViewTableTypeBase table)
{
CodeConstructor ccFull = this.GraphBasicConstructor();
foreach (Column c in table.Columns)
{
MemberGraph mGraph = new MemberGraph(c);
ccFull.Parameters.Add(mGraph.GetParameter());
ccFull.Statements.Add(new CodeSnippetExpression("this." + mGraph.FieldName() + "=" + mGraph.ParameterName()));
}
return(ccFull);
}
/// <summary>
/// Contains only Identity Parameters
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public CodeConstructor GraphIdentityConstructor(TableViewTableTypeBase table)
{
CodeConstructor ccIdentity = this.GraphBasicConstructor();
foreach (Column c in table.Columns)
{
if (c.InPrimaryKey)
{
MemberGraph mGraph = new MemberGraph(c);
ccIdentity.Parameters.Add(mGraph.GetParameter());
ccIdentity.Statements.Add(new CodeSnippetExpression("this." + mGraph.FieldName() + "=" + mGraph.ParameterName()));
}
}
return(ccIdentity);
}
/// <summary>
/// Builds List of Code Statmenets that converts a Type into a DAL Parameter Statement
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static CodeStatementCollection BuildAttributeSetStatement(TableViewTableTypeBase table)
{
CodeStatementCollection AttributeSetStatement = new CodeStatementCollection();
String PocoTypeName = table.Name;
String FullPocoTypeName = PocoTypeName;
foreach (Column c in table.Columns)
{
MemberGraph mGraph = new MemberGraph(c);
if (mGraph.IsReadOnly)
{
// nothing yet
}
else
{
String DotNetTypeName = TypeConvertor.ToNetType(c.DataType.SqlDataType).ToString();
System.CodeDom.CodeConditionStatement ccsField = new CodeConditionStatement();
if (mGraph.IsNullable)
{
ccsField.Condition = new CodeSnippetExpression("query." + mGraph.PropertyName() + ".HasValue");
ccsField.TrueStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\",query." + mGraph.PropertyName() + ".Value, ParameterDirection.Input)"));
ccsField.FalseStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\", null , ParameterDirection.Input)"));
}
else
{
ccsField.Condition = new CodeSnippetExpression("query." + mGraph.PropertyName() + " == null");
ccsField.TrueStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\", null , ParameterDirection.Input)"));
ccsField.FalseStatements.Add(new CodeSnippetExpression("this.Access.AddParameter(\"" + mGraph.PropertyName() + "\",query." + mGraph.PropertyName() + ", ParameterDirection.Input)"));
}
AttributeSetStatement.Add(ccsField);
}
}
return(AttributeSetStatement);
}
public TSQL.StoredProcedure Generate(String ProcedureRepositoryPath)
{
TSQL.StoredProcedure procedure = null;
if (CheckRequiredObjects())
{
procedure = new Microsoft.SqlServer.Management.Smo.StoredProcedure(this._database, "ssp_" + this._table.Name, _table.Schema);
// Set the text mode to false and then set other properties;
procedure.TextMode = false;
#region [ Operation 1 - Search by Any Criteria ]
// Add Parameters;
System.Text.StringBuilder SelectSql = new StringBuilder();
SelectSql.AppendLine("SELECT ");
int Count = _table.Columns.Count - 1;
int Index = 0;
while (Index <= Count)
{
MemberGraph mGraph = new MemberGraph(_table.Columns[Index]);
TSQL.StoredProcedureParameter parameter = new Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(procedure, "@" + mGraph.PropertyName(), _table.Columns[Index].DataType);
parameter.DefaultValue = "NULL";
procedure.Parameters.Add(parameter);
// ntext Data types cna not be searched
if (!(mGraph.SqlColumn.DataType.SqlDataType == TSQL.SqlDataType.NText))
{
if (Index == Count)
{
SelectSql.AppendLine("[" + _table.Columns[Index].Name + "]");
}
else
{
SelectSql.AppendLine("[" + _table.Columns[Index].Name + "],");
}
}
Index = Index + 1;
}
SelectSql.AppendLine("FROM " + _table.Name);
SelectSql.AppendLine("WHERE ");
Count = _table.Columns.Count - 1;
Index = 0;
while (Index <= Count)
{
MemberGraph mGraph = new MemberGraph(_table.Columns[Index]);
String ParameterName = "@" + mGraph.PropertyName();
if (!(mGraph.SqlColumn.DataType.SqlDataType == TSQL.SqlDataType.NText))
{
if (Index == 0)
{ // This is the last column
if (_table.Columns[Index].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.Text)
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " LIKE " + ParameterName + ")");
}
else
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " =" + ParameterName + ")");
}
}
else
{
SelectSql.AppendLine("AND");
if (_table.Columns[Index].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.Text)
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " LIKE " + ParameterName + ")");
}
else
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " = " + ParameterName + ")");
}
}
}
Index = Index + 1;
}
SelectSql.AppendLine("\n");
#endregion
#region [ Build Script ]
string totalScript = SelectSql.ToString();
procedure.TextBody = totalScript;
TSQL.ScriptingOptions Options = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
StringBuilder sbSql = new StringBuilder();
foreach (var s in procedure.Script())
{
sbSql.AppendLine(s);
}
#endregion
File.WriteAllText(ProcedureRepositoryPath + @"\" + procedure.Name + ".sql", sbSql.ToString());
}
return(procedure);
}
