public bool IsString(DataJuggler.Net.DataTable table, string fieldName)
{
// initial value
bool isString = false;
// Create Generic row
DataJuggler.Net.DataRow row = new DataRow();
// Get the Index Of Fieldname
int Index = SQLDatabaseConnector.FindFieldIndex(table.Fields, fieldName);
// If Index Was Found
if (Index >= 0)
{
// if this field is a string
if (table.Fields[Index].DataType == DataManager.DataTypeEnum.String)
{
// Use Quotes For Strings
isString = true;
}
}
// return value
return(isString);
}
/// <summary>
/// Returns a valid sql statement to load an object from a table based upon the primary key.
/// This is for a single field primary key.
/// </summary>
/// <param name="table">DataJuggler.Net.DataTable</param>
/// <param name="primaryKeyValue">A double value in where clause</param>
/// <returns>A valid sql statement if the table has a primary key</returns>
public string CreateSelectSQL(DataJuggler.Net.DataTable table, double primaryKeyValue)
{
// Verify table Has A Primary Key
if (table.PrimaryKey != null)
{
return("void");
}
// Verify table Has A Primary Key
if (table.PrimaryKey.FieldName != null)
{
return("void");
}
// Create StringBuilder
StringBuilder sb = new StringBuilder("Select * From [");
// Append tableName
sb.Append(table.Name);
// Append Where
sb.Append("] Where ");
// Append PrimaryKey.fieldName
sb.Append(table.PrimaryKey.FieldName);
// Append =
sb.Append(" = ");
// Append Value
sb.Append(primaryKeyValue.ToString());
// return SelectSQL
return(sb.ToString());
}
/// <summary>
/// Creates A Valid sql Statement To Select All Records From A Given table
/// </summary>
/// <param name="table">table To Create sql Statement For</param>
/// <returns>sql Statement To Select All Records From A Given table</returns>
public string CreateSelectAllSQL(DataJuggler.Net.DataTable dataTable)
{
// If tableName Is Null
if ((dataTable == null) || (dataTable.Name == null))
{
return("void");
}
StringBuilder sb = new StringBuilder();
// Create StringBuilder
string selectAllSQL = dataTable.SQLGenerator.CreateFieldList(dataTable, true);
// append
sb.Append(selectAllSQL);
// Append Open Brack
sb.Append(" From [");
// Append tableName
sb.Append(dataTable.Name);
// Append Where
sb.Append("]");
// return SelectSQL
return(sb.ToString());
}
public DataRow(DataJuggler.Net.DataTable ParentDataTable)
{
// Set Parent table
this.ParentTable = ParentDataTable;
// Create Fields Collection
fields = new List <DataField>();
}
/// <summary>
/// Returns a valid sql statement to load an object from a table based upon a fieldName &
/// field value passed in. This method is for a single field >> value pair.
/// </summary>
/// <param name="fieldName">field name that where clause will include</param>
/// <param name="fieldValue">value in where clause</param>
/// <returns>A valid sql statement to select a row with a certain field, value</returns>
public string CreateSelectSQL(DataJuggler.Net.DataTable table, string fieldName, string fieldValue)
{
// bool UseQuotes
bool UseQuotes = IsString(table, fieldName);
// If tableName Is Null
if (table.Name == null)
{
return("void");
}
// fieldValue Exists
if (fieldValue == null)
{
return("void");
}
// Verify fieldName Is Not Null
if (fieldName == null)
{
return("void");
}
// Create StringBuilder
StringBuilder sb = new StringBuilder("Select * From [");
// Append tableName
sb.Append(table.Name);
// Append Where
sb.Append("] Where ");
// Append field.fieldName
sb.Append(fieldName);
// Append =
sb.Append(" = ");
// If This Is A String
if (UseQuotes)
{
// Append Single Quote
sb.Append("'");
}
// Append Value
sb.Append(fieldValue);
// If This Is A String
if (UseQuotes)
{
// Append Single Quote
sb.Append("'");
}
// return SelectSQL
return(sb.ToString());
}
/// <summary>
/// This method prepares this control to be shown
/// </summary>
public void Setup(MethodInfo methodInfo, Project openProject, CustomReader customReader = null, DTNField orderByField = null, FieldSet orderByFieldSet = null)
{
// store the args
this.MethodInfo = methodInfo;
this.OpenProject = openProject;
// locals
DataJuggler.Net.DataField parameter = null;
List <DataJuggler.Net.DataField> parameters = null;
ProcedureWriter writer = null;
// If the MethodInfo object exists
if (this.HasMethodInfo)
{
// create a new
MethodsWriter methodsWriter = new MethodsWriter();
// get the StoredXml
StoredXml = methodsWriter.ExportMethodInfo(this.MethodInfo);
// if the OrderByFieldSet object exists
if (NullHelper.Exists(orderByFieldSet))
{
// set the gateway
Gateway gateway = new Gateway();
// load the orderByFields
orderByFieldSet.FieldSetFields = gateway.LoadFieldSetFieldViewsByFieldSetId(orderByFieldSet.FieldSetId);
}
// Set the Name of the Table
this.SelectedTableControl.Text = this.MethodInfo.SelectedTable.TableName;
// set the procedureName
this.ProcedureNameControl.Text = MethodInfo.ProcedureName;
// Check the button for Manual Update (user clicks Copy and goes to their SQL instance and executes).
this.ManualUpdateRadioButton.Checked = true;
// Check the box if UseCustomReader is true
this.CustomWhereCheckBox.Checked = MethodInfo.UseCustomWhere;
// Set the CustomWhereText (if any)
this.WhereTextBox.Text = MethodInfo.WhereText;
// convert the table
DataJuggler.Net.DataTable table = DataConverter.ConvertDataTable(MethodInfo.SelectedTable, this.OpenProject);
// if this is a Single Field Parameter and the Parameter Field exists
if ((MethodInfo.ParameterType == ParameterTypeEnum.Single_Field) && (MethodInfo.HasParameterField))
{
// convert the field from a DTNField to a DataJuggler.Net.DataField
parameter = DataConverter.ConvertDataField(MethodInfo.ParameterField);
// Create a new instance of a 'ProcedureWriter' object (in TextWriter mode).
writer = new ProcedureWriter(true);
// if this is a FindByField method
if (writer.HasTextWriter)
{
// If Delete By is the Method Type
if (MethodInfo.MethodType == MethodTypeEnum.Delete_By)
{
// Write out the Delete Proc
writer.CreateDeleteProc(table, MethodInfo.ProcedureName, parameter);
}
// If Find By is the Method Type
if (MethodInfo.MethodType == MethodTypeEnum.Find_By)
{
// create a find procedure
writer.CreateFindProc(table, false, MethodInfo.ProcedureName, parameter, customReader, orderByField, orderByFieldSet, methodInfo.OrderByDescending, methodInfo.TopRows);
}
// if Load By is the Method Type
else if (MethodInfo.MethodType == MethodTypeEnum.Load_By)
{
// If the orderByField object exists
if (NullHelper.Exists(orderByFieldSet))
{
// if there are not any fields loaded
if (!ListHelper.HasOneOrMoreItems(orderByFieldSet.Fields))
{
// load the Fields
orderByFieldSet.Fields = FieldSetHelper.LoadFieldSetFields(orderByFieldSet.FieldSetId);
}
}
// create a find procedure
writer.CreateFindProc(table, true, MethodInfo.ProcedureName, parameter, customReader, orderByField, orderByFieldSet, methodInfo.OrderByDescending, methodInfo.TopRows);
}
}
}
else if ((MethodInfo.ParameterType == ParameterTypeEnum.Field_Set) && (MethodInfo.HasParameterFieldSet) && (MethodInfo.ParameterFieldSet.HasFields))
{
// convert the DTNFields to DataFields
parameters = DataConverter.ConvertDataFields(MethodInfo.ParameterFieldSet.Fields);
// If the parameters collection exists and has one or more items
if (ListHelper.HasOneOrMoreItems(parameters))
{
// set the FieldSetName so the description writes the method description correctly
parameters[0].FieldSetName = MethodInfo.ParameterFieldSet.Name;
}
// Create a new instance of a 'ProcedureWriter' object (in TextWriter mode).
writer = new ProcedureWriter(true);
// if this is a FindByField method
if (writer.HasTextWriter)
{
// If Delete By is the Method Type
if (MethodInfo.MethodType == MethodTypeEnum.Delete_By)
{
// Write out the Delete Proc
writer.CreateDeleteProc(table, MethodInfo.ProcedureName, parameters);
}
// If Find By is the Method Type
if (MethodInfo.MethodType == MethodTypeEnum.Find_By)
{
// create a find procedure
writer.CreateFindProc(table, false, MethodInfo.ProcedureName, parameters, MethodInfo.CustomReader, orderByField, orderByFieldSet);
}
// if Load By is the Method Type
else if (MethodInfo.MethodType == MethodTypeEnum.Load_By)
{
// create a find procedure
writer.CreateFindProc(table, true, MethodInfo.ProcedureName, parameters, MethodInfo.CustomReader, orderByField, orderByFieldSet);
}
}
}
else if ((MethodInfo.ParameterType == ParameterTypeEnum.No_Parameters))
{
// Create a new instance of a 'ProcedureWriter' object (in TextWriter mode).
writer = new ProcedureWriter(true);
// if this is a FindByField method
if (writer.HasTextWriter)
{
// If Delete By is the Method Type
if (MethodInfo.MethodType == MethodTypeEnum.Delete_By)
{
// Write out the Delete Proc
writer.CreateDeleteProc(table, MethodInfo.ProcedureName, parameters);
}
// If Find By is the Method Type
if (MethodInfo.MethodType == MethodTypeEnum.Find_By)
{
// create a find procedure
writer.CreateFindProc(table, false, MethodInfo.ProcedureName, parameters, MethodInfo.CustomReader, orderByField, orderByFieldSet);
}
// if Load By is the Method Type
else if (MethodInfo.MethodType == MethodTypeEnum.Load_By)
{
// create a find procedure
writer.CreateFindProc(table, true, MethodInfo.ProcedureName, parameters, MethodInfo.CustomReader, orderByField, orderByFieldSet);
}
}
}
// if the writer exists
if (NullHelper.Exists(writer))
{
// get the procedureText
string procedureText = writer.TextWriter.ToString();
// Show the Where Panel if CustomWhere is true
WherePanel.Visible = MethodInfo.UseCustomWhere;
// if CustomWhere
if (MethodInfo.UseCustomWhere)
{
// now the existing where text must be replaced
int whereIndex = procedureText.ToLower().IndexOf("where [");
// if the WhereText does not exist yet
if ((!MethodInfo.HasWhereText) && (whereIndex > 0))
{
// Set the text as it is now
string whereText = procedureText.Substring(whereIndex);
// If the whereText string exists
if (TextHelper.Exists(whereText))
{
// get the textLines
List <TextLine> textLines = WordParser.GetTextLines(whereText);
// If the textLines collection exists and has one or more items
if (ListHelper.HasOneOrMoreItems(textLines))
{
// Create a new instance of a 'StringBuilder' object.
StringBuilder sb = new StringBuilder();
// add each textLine of the Where Clause except the last one
foreach (TextLine textLine in textLines)
{
// if this is the End line
if (!textLine.Text.ToLower().StartsWith("end"))
{
// Add this line
sb.Append(textLine.Text);
}
}
// Get the Where Clause
MethodInfo.WhereText = sb.ToString().Trim();
}
}
}
// Set the WhereText
WhereTextBox.Text = MethodInfo.WhereText;
// if the whereIndex was found
if (whereIndex >= 0)
{
// if the WhereText does not already exist
if (!TextHelper.Exists(MethodInfo.WhereText))
{
// Set the default WhereText
MethodInfo.WhereText = procedureText.Substring(whereIndex);
}
// set the new ProcedureText
procedureText = procedureText.Substring(0, whereIndex) + MethodInfo.WhereText + Environment.NewLine + Environment.NewLine + "END";
}
}
// Remove any double blank lines
procedureText = CodeLineHelper.RemoveDoubleBlankLines(procedureText);
// display the procedure As Is for now.
this.ProcedureTextBox.Text = procedureText;
}
}
}
