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; } } }