//------------------------------------- // public string CreateGetAllMethod(StoredProcedureTypes type) { try { string MethodName = type.ToString(); string sqlDataProviderMethodName = type.ToString() + global.TableProgramatlyName; string MethodReturn = "DataTable"; //XML Documentaion string xmlDocumentation = "\t/// <summary>\n"; xmlDocumentation += "\t/// Gets All " + SqlProvider.obj.TableName + ".\n"; xmlDocumentation += "\t/// <example>[Example]DataTable dt" + global.TableProgramatlyName + "=" + ClassName + "." + MethodName + "();.</example>\n"; xmlDocumentation += "\t/// </summary>\n"; xmlDocumentation += "\t/// <returns>All " + SqlProvider.obj.TableName + ".</returns>"; //Method Body StringBuilder methodBody = new StringBuilder(); methodBody.Append(xmlDocumentation); methodBody.Append("\n\tpublic static " + MethodReturn + " " + MethodName + "()"); methodBody.Append("\n\t{"); methodBody.Append("\n\t\treturn " + global.SqlDataProviderClass + ".Instance." + sqlDataProviderMethodName + "();"); methodBody.Append("\n\t}"); methodBody.Append("\n\t" + Globals.MetthodsSeparator); return(methodBody.ToString()); } catch (Exception ex) { MessageBox.Show("My Generated Code Exception:" + ex.Message); return(""); } }
/// <summary> /// This method writes the Init method which /// instanicates each child controller /// </summary> private void WriteInitMethod(DataTable dataTable, StoredProcedureTypes procType) { // Begin Region For The Init Method BeginRegion("Init()"); // Write The Summary For The Init Method WriteLine("/// <summary>"); WriteLine("/// Set Procedure Properties"); WriteLine("/// </summary>"); // Write the method declaration for the Init() method. WriteLine("private void Init()"); // Write Open Bracket { WriteOpenBracket(true); // Write Comment Create Child Controllers WriteComment("Set Properties For This Proc"); // Write Blank Line WriteLine(); // local string temp = null; // if the dataTable exists if (dataTable != null) { // Write Comment Set ProcedureName WriteComment("Set ProcedureName"); // Set ProcedureName string procedureName = dataTable.ClassName + "_" + procType.ToString(); temp = GetStringVariableLine("ProcedureName", procedureName, true); // Write procedureName WriteLine(temp); // Write Blank Line WriteLine(); // Write Comment Set tableName WriteComment("Set tableName"); // get line to set table name string tableName = GetStringVariableLine("TableName", dataTable.ClassName, true); // Write tableName WriteLine(tableName); } // Write Close Bracket } WriteCloseBracket(true); // End Region EndRegion(); // Write Blank Line WriteLine(); }
/// <summary> /// This method gets the name of this class. /// </summary> /// <param name="dataTable"></param> /// <param name="procType"></param> /// <returns></returns> private string GetClassName(DataTable dataTable, StoredProcedureTypes procType) { // create StringBuilder StringBuilder sb = new StringBuilder(procType.ToString()); // set the class name string className = dataTable.ClassName; // if this is a fetch all proc if (procType == StoredProcedureTypes.FetchAll) { // get the plural name className = PluralWordHelper.GetPluralName(className, false); } // Append table name sb.Append(className); // append the words stored procedure sb.Append("StoredProcedure"); // set the return value className = sb.ToString(); // return value return(className); }
private string CreateGetAll4ShowMethod(StoredProcedureTypes type) { try { string ProcName = global.TableProgramatlyName + "_" + type.ToString(); string MethodName = type.ToString() + global.TableProgramatlyName; string MethodReturn = "DataTable"; //XML Documentaion string xmlDocumentation = "\t/// <summary>\n"; xmlDocumentation += "\t/// Gets All " + SqlProvider.obj.TableName + " Records.\n"; xmlDocumentation += "\t/// <example>[Example]DataTable dt" + global.TableProgramatlyName + "=" + ClassName + ".Instance." + MethodName + "();.</example>\n"; xmlDocumentation += "\t/// </summary>\n"; xmlDocumentation += "\t/// <returns>The result of query.</returns>"; //Method Body // StringBuilder methodBody = new StringBuilder(); methodBody.Append(xmlDocumentation); methodBody.Append("\n\tpublic " + MethodReturn + " " + MethodName + "()"); methodBody.Append("\n\t{"); methodBody.Append("\n\t\tusing( SqlConnection myConnection = GetSqlConnection()) "); methodBody.Append("\n\t\t{"); methodBody.Append("\n\t\t\tSqlCommand myCommand = new SqlCommand(\"" + ProcName + "\", myConnection);"); methodBody.Append("\n\t\t\tmyCommand.CommandType = CommandType.StoredProcedure;"); methodBody.Append("\n\t\t\tSqlDataAdapter da=new SqlDataAdapter(myCommand);"); methodBody.Append("\n\t\t\tDataTable dt=new DataTable();"); methodBody.Append("\n\t\t\t// Execute the command"); methodBody.Append("\n\t\t\tmyConnection.Open();"); methodBody.Append("\n\t\t\tda.Fill(dt);"); methodBody.Append("\n\t\t\tmyConnection.Close();"); methodBody.Append("\n\t\t\treturn dt;"); methodBody.Append("\n\t\t}"); methodBody.Append("\n\t}"); methodBody.Append("\n\t" + Globals.MetthodsSeparator); return(methodBody.ToString()); } catch (Exception ex) { MessageBox.Show("My Generated Code Exception:" + ex.Message); return(""); } }
/// <summary> /// Create the file name for this reader. /// </summary> /// <param name="dataTable"></param> /// <returns></returns> private string CreateFileName(DataTable dataTable, StoredProcedureTypes procType) { // Create StringBuilder StringBuilder sb = new StringBuilder(this.RootStoredProceduresPath); // If does not end with backslash if (!this.RootStoredProceduresPath.EndsWith(@"\")) { // Append Backslash \ sb.Append(@"\"); } // determine folder to add string procFolder = GetProcFolder(procType, false, true); // append procFold sb.Append(procFolder); // Append the word for proc type (Delete, FetchAll, Find, etc.) sb.Append(procType.ToString()); // set the clas name string className = dataTable.ClassName; // if this is a fetch all (a collection is retured) if (procType == StoredProcedureTypes.FetchAll) { // get the class name in plural className = PluralWordHelper.GetPluralName(dataTable.ClassName, false); } // Apend the class name sb.Append(className); // Add The Word DataOperationsManager sb.Append("StoredProcedure.cs"); // return value return(sb.ToString()); }
private string CreateGetOne4ShowMethod(StoredProcedureTypes type) { if (ID != null) { try { string id = Globals.GetProgramatlyName(ID.Name); id = Globals.ConvetStringToCamelCase(id); // string ProcName = global.TableProgramatlyName + "_" + type.ToString(); string MethodName = "Get" + global.TableProgramatlyName + "Object4Show"; string MethodReturn = global.TableEntityClass; //XML Documentaion string xmlDocumentation = "\t/// <summary>\n"; xmlDocumentation += "\t/// Gets single " + SqlProvider.obj.TableName + " object .\n"; xmlDocumentation += "\t/// <example>[Example]" + global.TableEntityClass + " " + global.EntityClassObject + "=" + ClassName + ".Instance." + MethodName + "(" + id + ");.</example>\n"; xmlDocumentation += "\t/// </summary>\n"; xmlDocumentation += "\t/// <param name=\"" + id + "\">The " + global.EntityClassObject + " id.</param>\n"; xmlDocumentation += "\t/// <returns>" + SqlProvider.obj.TableName + " object4Show.</returns>"; //Method Body StringBuilder methodBody = new StringBuilder(); methodBody.Append(xmlDocumentation); methodBody.Append("\n\tpublic " + MethodReturn + " " + MethodName + "(" + Globals.GetAliasDataType(ID.Datatype) + " " + id + ")"); methodBody.Append("\n\t{"); methodBody.Append("\n\t\t" + global.TableEntityClass + " " + global.EntityClassObject + " = null;"); methodBody.Append("\n\t\tusing( SqlConnection myConnection = GetSqlConnection()) "); methodBody.Append("\n\t\t{"); methodBody.Append("\n\t\t\tSqlCommand myCommand = new SqlCommand(\"" + ProcName + "\", myConnection);"); methodBody.Append("\n\t\t\tmyCommand.CommandType = CommandType.StoredProcedure;"); methodBody.Append("\n\t\t\t// Set the parameters"); methodBody.Append("\n\t\t\tmyCommand.Parameters.Add(\"@" + Globals.GetProgramatlyName(ID.Name) + "\", SqlDbType." + Globals.GetSqlDataType(ID.Datatype).ToString() + "," + ID.Length + ").Value = " + id + ";"); methodBody.Append("\n\t\t\t// Execute the command"); methodBody.Append("\n\t\t\tmyConnection.Open();"); methodBody.Append("\n\t\t\tusing(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))"); methodBody.Append("\n\t\t\t{"); methodBody.Append("\n\t\t\t\tif(dr.Read())"); methodBody.Append("\n\t\t\t\t{"); methodBody.Append("\n\t\t\t\t\t" + global.EntityClassObject + " = " + global.PopulateMethodName + "(dr);"); methodBody.Append("\n\t\t\t\t}"); methodBody.Append("\n\t\t\t\tdr.Close();"); methodBody.Append("\n\t\t}"); methodBody.Append("\n\t\t\tmyConnection.Close();"); //------------------------------------- methodBody.Append("\n\t\t\treturn " + global.EntityClassObject + ";"); methodBody.Append("\n\t\t}"); methodBody.Append("\n\t}"); methodBody.Append("\n\t" + Globals.MetthodsSeparator); return(methodBody.ToString()); } catch (Exception ex) { MessageBox.Show("My Generated Code Exception:" + ex.Message); return(""); } } else { return(""); } }
private string CreateInsertUpdateDeleteMethod(StoredProcedureTypes type) { string id = Globals.GetProgramatlyName(ID.Name); id = Globals.ConvetStringToCamelCase(id); // string ProcName = global.TableProgramatlyName + "_" + type.ToString(); string MethodName = MethodName = type.ToString(); string MethodReturn = "bool"; //XML Documentaion string xmlInsertDocumentation = "\t/// <summary>\n"; xmlInsertDocumentation += "\t/// Converts the " + SqlProvider.obj.TableName + " object properties to SQL paramters and executes the create " + SqlProvider.obj.TableName + " procedure \n"; xmlInsertDocumentation += "\t/// and updates the " + SqlProvider.obj.TableName + " object with the SQL data by reference.\n"; xmlInsertDocumentation += "\t/// <example>[Example]bool result=" + ClassName + ".Instance." + MethodName + "(" + global.EntityClassObject + ");.</example>\n"; xmlInsertDocumentation += "\t/// </summary>\n"; xmlInsertDocumentation += "\t/// <param name=\"" + global.EntityClassObject + "\">The " + SqlProvider.obj.TableName + " object.</param>\n"; xmlInsertDocumentation += "\t/// <returns>The result of create query.</returns>"; // string xmlUpdateDocumentation = "\t/// <summary>\n"; xmlUpdateDocumentation += "\t/// Converts the " + SqlProvider.obj.TableName + " object properties to SQL paramters and executes the update " + SqlProvider.obj.TableName + " procedure.\n"; xmlUpdateDocumentation += "\t/// <example>[Example]bool result=" + ClassName + ".Instance." + MethodName + "(" + global.EntityClassObject + ");.</example>\n"; xmlUpdateDocumentation += "\t/// </summary>\n"; xmlUpdateDocumentation += "\t/// <param name=\"" + global.EntityClassObject + "\">The " + SqlProvider.obj.TableName + " object.</param>\n"; xmlUpdateDocumentation += "\t/// <returns>The result of update query.</returns>"; // string xmlDeleteDocumentation = "\t/// <summary>\n"; xmlDeleteDocumentation += "\t/// Deletes single " + SqlProvider.obj.TableName + " object .\n"; xmlDeleteDocumentation += "\t/// <example>[Example]bool result=" + ClassName + ".Instance." + MethodName + "(" + id + ");.</example>\n"; xmlDeleteDocumentation += "\t/// </summary>\n"; xmlDeleteDocumentation += "\t/// <param name=\"" + id + "\">The " + global.EntityClassObject + " id.</param>\n"; xmlDeleteDocumentation += "\t/// <returns>The result of delete query.</returns>"; //method body try { StringBuilder methodBody = new StringBuilder(); if (type == StoredProcedureTypes.Create) { methodBody.Append(xmlInsertDocumentation); } else if (type == StoredProcedureTypes.Update) { methodBody.Append(xmlUpdateDocumentation); } else if (type == StoredProcedureTypes.Delete) { methodBody.Append(xmlDeleteDocumentation); } // if (type == StoredProcedureTypes.Delete) { methodBody.Append("\n\tpublic " + MethodReturn + " " + MethodName + "(" + Globals.GetAliasDataType(ID.Datatype) + " " + id + ")"); } else { methodBody.Append("\n\tpublic " + MethodReturn + " " + MethodName + "(" + global.TableEntityClass + " " + global.EntityClassObject + ")"); } methodBody.Append("\n\t{"); methodBody.Append("\n\t\tbool result=false;"); methodBody.Append("\n\t\tusing( SqlConnection myConnection = GetSqlConnection()) "); methodBody.Append("\n\t\t{"); methodBody.Append("\n\t\t\tSqlCommand myCommand = new SqlCommand(\"" + ProcName + "\", myConnection);"); methodBody.Append("\n\t\t\tmyCommand.CommandType = CommandType.StoredProcedure;"); methodBody.Append("\n\t\t\t// Set the parameters"); if (type == StoredProcedureTypes.Delete) { methodBody.Append("\n\t\t\tmyCommand.Parameters.Add(\"@" + Globals.GetProgramatlyName(ID.Name) + "\", SqlDbType." + Globals.GetSqlDataType(ID.Datatype).ToString() + "," + ID.Length + ").Value = " + id + ";"); } else { foreach (SQLDMO.Column colCurrent in Fields) { if (ID != null && colCurrent.Name == ID.Name && type == StoredProcedureTypes.Create && ID.IdentityIncrement > 0) { methodBody.Append("\n\t\t\tmyCommand.Parameters.Add(\"@" + Globals.GetProgramatlyName(colCurrent.Name) + "\", SqlDbType." + Globals.GetSqlDataType(colCurrent.Datatype).ToString() + "," + colCurrent.Length + ").Direction = ParameterDirection.Output;"); } else if (colCurrent.Datatype.ToLower() == SqlDbType.NText.ToString().ToLower()) { methodBody.Append("\n\t\t\tmyCommand.Parameters.Add(\"@" + Globals.GetProgramatlyName(colCurrent.Name) + "\", SqlDbType." + Globals.GetSqlDataType(colCurrent.Datatype).ToString() + ").Value = " + global.EntityClassObject + "." + Globals.GetProgramatlyName(colCurrent.Name) + ";"); } else { methodBody.Append("\n\t\t\tmyCommand.Parameters.Add(\"@" + Globals.GetProgramatlyName(colCurrent.Name) + "\", SqlDbType." + Globals.GetSqlDataType(colCurrent.Datatype).ToString() + "," + colCurrent.Length + ").Value = " + global.EntityClassObject + "." + Globals.GetProgramatlyName(colCurrent.Name) + ";"); } } } methodBody.Append("\n\t\t\t// Execute the command"); methodBody.Append("\n\t\t\tmyConnection.Open();"); methodBody.Append("\n\t\t\tif(myCommand.ExecuteNonQuery()>0)"); methodBody.Append("\n\t\t\t{"); methodBody.Append("\n\t\t\t\tresult=true;"); if (ID != null && type == StoredProcedureTypes.Create && ID.IdentityIncrement > 0) { methodBody.Append("\n\t\t\t\t//Get ID value from database and set it in object"); methodBody.Append("\n\t\t\t\t" + global.EntityClassObject + "." + Globals.GetProgramatlyName(ID.Name) + "= (" + Globals.GetAliasDataType(ID.Datatype) + ") myCommand.Parameters[\"@" + Globals.GetProgramatlyName(ID.Name) + "\"].Value;"); } if (ID.Name.ToLower() == "id" && global.EntityClassObject == "Site_RolesObj") { MessageBox.Show(SqlProvider.obj.ID.Name); } methodBody.Append("\n\t\t\t}"); methodBody.Append("\n\t\t\tmyConnection.Close();"); methodBody.Append("\n\t\t\treturn result;"); methodBody.Append("\n\t\t}"); methodBody.Append("\n\t}"); methodBody.Append("\n\t" + Globals.MetthodsSeparator); return(methodBody.ToString()); } catch (Exception ex) { MessageBox.Show("My Generated Code Exception:" + ex.Message); return(""); } }
//---------------------------------- #region Old and Unused code /// <summary> /// Generates code for an UPDATE or INSERT Stored Procedure /// </summary> /// <param name="sptypeGenerate">The type of SP to generate, INSERT or UPDATE</param> /// <param name="Fields">A ColumnCollection collection</param> /// <returns>The SP code</returns> private void Generate(StoredProcedureTypes sptypeGenerate, SqlProvider dmoMain) { try { StringBuilder sGeneratedCode = new StringBuilder(); StringBuilder sParamDeclaration = new StringBuilder(); StringBuilder sBody = new StringBuilder(); StringBuilder sINSERTValues = new StringBuilder(); string finnal = ""; // Setup SP code, begining is the same no matter the type sGeneratedCode.AppendFormat("CREATE PROCEDURE {0}.[{1}_{2}]", new string[] { SqlProvider.obj.DatabaseOwner, Globals.GetProgramatlyName(Table), sptypeGenerate.ToString() }); sGeneratedCode.Append(Environment.NewLine); // Setup body code, different for UPDATE and INSERT switch (sptypeGenerate) { case StoredProcedureTypes.Create: sBody.AppendFormat("INSERT INTO [{0}] (", Table); sBody.Append(Environment.NewLine); sINSERTValues.Append("VALUES ("); sINSERTValues.Append(Environment.NewLine); break; case StoredProcedureTypes.Update: sBody.AppendFormat("UPDATE [{0}]", Table); sBody.Append(Environment.NewLine); sBody.Append("SET"); sBody.Append(Environment.NewLine); break; case StoredProcedureTypes.GetAll: sBody.AppendFormat("Select * From [{0}] ", Table); sBody.Append(Environment.NewLine); break; case StoredProcedureTypes.GetOneByID: sBody.AppendFormat("Select * From [{0}] ", Table); sBody.Append(Environment.NewLine); break; case StoredProcedureTypes.Delete: sBody.AppendFormat("Delete * From [{0}] ", Table); sBody.Append(Environment.NewLine); break; } #region Add Parametars if (sptypeGenerate == StoredProcedureTypes.GetAll) { sGeneratedCode.Append("AS"); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append(sBody.Remove(sBody.Length - 3, 3)); } else if (sptypeGenerate != StoredProcedureTypes.GetOneByID || sptypeGenerate != StoredProcedureTypes.Delete) { //The finanal of procedure if (ID != null) { sParamDeclaration.AppendFormat(" @{0} {1}", new string[] { Globals.GetProgramatlyName(ID.Name), ID.Datatype }); finnal = "Where [" + ID.Name + "] =@" + Globals.GetProgramatlyName(ID.Name); } } else { foreach (Column colCurrent in Fields) { if (ID != null && colCurrent.Name == ID.Name) { sParamDeclaration.AppendFormat(" @{0} {1}", new string[] { Globals.GetProgramatlyName(colCurrent.Name), colCurrent.Datatype }); } if (ID != null && colCurrent.Name == ID.Name && Globals.CheckIsAddedBySql(colCurrent) && sptypeGenerate == StoredProcedureTypes.Create) { sParamDeclaration.AppendFormat(" out"); } // Only binary, char, nchar, nvarchar, varbinary and varchar may have their length declared if ( colCurrent.Datatype == "binary" || colCurrent.Datatype == "char" || colCurrent.Datatype == "nchar" || colCurrent.Datatype == "nvarchar" || colCurrent.Datatype == "varbinary" || colCurrent.Datatype == "varchar") { sParamDeclaration.AppendFormat("({0})", colCurrent.Length); } sParamDeclaration.Append(","); sParamDeclaration.Append(Environment.NewLine); // Body construction, different for INSERT and UPDATE switch (sptypeGenerate) { case StoredProcedureTypes.Create: //not Added BySQL if (ID == null && colCurrent.Name != ID.Name || !Globals.CheckIsAddedBySql(colCurrent)) { sINSERTValues.AppendFormat(" @{0},", Globals.GetProgramatlyName(colCurrent.Name)); sINSERTValues.Append(Environment.NewLine); sBody.AppendFormat(" [{0}],", colCurrent.Name); sBody.Append(Environment.NewLine); } break; case StoredProcedureTypes.Update: if (ID == null && colCurrent.Name != ID.Name) { sBody.AppendFormat(" [{0}] = @{1},", new string[] { colCurrent.Name, Globals.GetProgramatlyName(colCurrent.Name) }); sBody.Append(Environment.NewLine); } break; case StoredProcedureTypes.GetOneByID: // sBody.AppendFormat("Where {0} = @{0},", new string[]{colCurrent.Name, }); sBody.Append(Environment.NewLine); break; } } //The finanal of procedure if (ID != null) { if (sptypeGenerate == StoredProcedureTypes.Create && Globals.CheckIsAddedBySql(ID)) { finnal = "Set @" + Globals.GetProgramatlyName(ID.Name) + " = @@Identity"; } else if (sptypeGenerate == StoredProcedureTypes.Update) { finnal = "Where [" + ID.Name + "] =@" + Globals.GetProgramatlyName(ID.Name); } } #endregion // Now stitch the body parts together into the SP whole sGeneratedCode.Append(sParamDeclaration.Remove(sParamDeclaration.Length - 3, 3)); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append("AS"); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append(sBody.Remove(sBody.Length - 3, 3)); if (sptypeGenerate == StoredProcedureTypes.Create) { sGeneratedCode.Append(")"); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append(sINSERTValues.Remove(sINSERTValues.Length - 3, 3)); sGeneratedCode.Append(")"); } } sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append(finnal); WriteStoredProcedure(sGeneratedCode.ToString()); } catch (Exception ex) { MessageBox.Show("My Generated Code Exception:" + ex.Message); } }
public string Generate(StoredProcedureTypes sptypeGenerate, SQLDMO.Columns colsFields, string sTableName) { StringBuilder sGeneratedCode = new StringBuilder(); StringBuilder sParamDeclaration = new StringBuilder(); StringBuilder sBody = new StringBuilder(); StringBuilder sINSERTValues = new StringBuilder(); // Setup SP code, begining is the same no matter the type sGeneratedCode.AppendFormat("CREATE PROCEDURE {0}_{1}", new string[] { sTableName, sptypeGenerate.ToString() }); sGeneratedCode.Append(Environment.NewLine); // Setup body code, different for UPDATE and INSERT switch (sptypeGenerate) { case StoredProcedureTypes.INSERT: sBody.AppendFormat("INSERT INTO [{0}] (", sTableName); sBody.Append(Environment.NewLine); sINSERTValues.Append("VALUES ("); sINSERTValues.Append(Environment.NewLine); break; case StoredProcedureTypes.UPDATE: sBody.AppendFormat("UPDATE [{0}]", sTableName); sBody.Append(Environment.NewLine); sBody.Append("SET"); sBody.Append(Environment.NewLine); break; } // Param Declaration construction sParamDeclaration.Append("("); sParamDeclaration.Append(Environment.NewLine); foreach (SQLDMO.Column colCurrent in colsFields) { sParamDeclaration.AppendFormat(" @{0} {1}", new string[] { colCurrent.Name, colCurrent.Datatype }); // Only binary, char, nchar, nvarchar, varbinary and varchar may have their length declared if ( colCurrent.Datatype == "binary" || colCurrent.Datatype == "char" || colCurrent.Datatype == "nchar" || colCurrent.Datatype == "nvarchar" || colCurrent.Datatype == "varbinary" || colCurrent.Datatype == "varchar") { sParamDeclaration.AppendFormat("({0})", colCurrent.Length); } sParamDeclaration.Append(","); sParamDeclaration.Append(Environment.NewLine); // Body construction, different for INSERT and UPDATE switch (sptypeGenerate) { case StoredProcedureTypes.INSERT: sINSERTValues.AppendFormat(" @{0},", colCurrent.Name); sINSERTValues.Append(Environment.NewLine); sBody.AppendFormat(" {0},", colCurrent.Name); sBody.Append(Environment.NewLine); break; case StoredProcedureTypes.UPDATE: sBody.AppendFormat(" {0} = @{0},", new string[] { colCurrent.Name, }); sBody.Append(Environment.NewLine); break; } } // Now stitch the body parts together into the SP whole sGeneratedCode.Append(sParamDeclaration.Remove(sParamDeclaration.Length - 3, 3)); sGeneratedCode.Append(")"); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append("AS"); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append(sBody.Remove(sBody.Length - 3, 3)); if (sptypeGenerate == StoredProcedureTypes.INSERT) { sGeneratedCode.Append(")"); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append(sINSERTValues.Remove(sINSERTValues.Length - 3, 3)); sGeneratedCode.Append(")"); } sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append("GO"); return(sGeneratedCode.ToString()); }
// #region ClassMember public string CreateInsertUpdateDeleteMethod(StoredProcedureTypes type) { try { string id = Globals.GetProgramatlyName(ID.Name); id = Globals.ConvetStringToCamelCase(id); string MethodName = type.ToString(); string sqlDataProviderMethodName = type.ToString(); string MethodReturn = "bool"; string MethodParameters = "(" + global.TableEntityClass + " " + global.EntityClassObject + ")"; // if (type == StoredProcedureTypes.Delete) { MethodParameters = "(" + Globals.GetAliasDataType(ID.Datatype) + " " + id + ")"; } else { MethodParameters = "(" + global.TableEntityClass + " " + global.EntityClassObject + ")"; } //XML Documentaion string xmlInsertDocumentation = "\t/// <summary>\n"; xmlInsertDocumentation += "\t/// Creates " + SqlProvider.obj.TableName + " object by calling " + SqlProvider.obj.TableName + " data provider create method.\n"; xmlInsertDocumentation += "\t/// <example>[Example]bool result=" + ClassName + "." + MethodName + "(" + global.EntityClassObject + ");.</example>\n"; xmlInsertDocumentation += "\t/// </summary>\n"; xmlInsertDocumentation += "\t/// <param name=\"" + global.EntityClassObject + "\">The " + SqlProvider.obj.TableName + " object.</param>\n"; xmlInsertDocumentation += "\t/// <returns>The result of create operation.</returns>"; // string xmlUpdateDocumentation = "\t/// <summary>\n"; xmlUpdateDocumentation += "\t/// Updates " + SqlProvider.obj.TableName + " object by calling " + SqlProvider.obj.TableName + " data provider update method.\n"; xmlUpdateDocumentation += "\t/// <example>[Example]bool result=" + ClassName + "." + MethodName + "(" + global.EntityClassObject + ");.</example>\n"; xmlUpdateDocumentation += "\t/// </summary>\n"; xmlUpdateDocumentation += "\t/// <param name=\"" + global.EntityClassObject + "\">The " + SqlProvider.obj.TableName + " object.</param>\n"; xmlUpdateDocumentation += "\t/// <returns>The result of update operation.</returns>"; // string xmlDeleteDocumentation = "\t/// <summary>\n"; xmlDeleteDocumentation += "\t/// Deletes single " + SqlProvider.obj.TableName + " object .\n"; xmlDeleteDocumentation += "\t/// <example>[Example]bool result=" + ClassName + "." + MethodName + "(" + id + ");.</example>\n"; xmlDeleteDocumentation += "\t/// </summary>\n"; xmlDeleteDocumentation += "\t/// <param name=\"" + id + "\">The " + global.EntityClassObject + " id.</param>\n"; xmlDeleteDocumentation += "\t/// <returns>The result of delete operation.</returns>"; //method body StringBuilder methodBody = new StringBuilder(); if (type == StoredProcedureTypes.Create) { methodBody.Append(xmlInsertDocumentation); } else if (type == StoredProcedureTypes.Update) { methodBody.Append(xmlUpdateDocumentation); } else if (type == StoredProcedureTypes.Delete) { methodBody.Append(xmlDeleteDocumentation); } methodBody.Append("\n\tpublic static " + MethodReturn + " " + MethodName + MethodParameters); methodBody.Append("\n\t{"); // if (type == StoredProcedureTypes.Delete) { methodBody.Append("\n\t\treturn " + global.SqlDataProviderClass + ".Instance." + sqlDataProviderMethodName + "(" + id + ");"); } else { methodBody.Append("\n\t\treturn " + global.SqlDataProviderClass + ".Instance." + sqlDataProviderMethodName + "(" + global.EntityClassObject + ");"); } methodBody.Append("\n\t}"); methodBody.Append("\n\t" + Globals.MetthodsSeparator); return(methodBody.ToString()); } catch (Exception ex) { MessageBox.Show("My Generated Code Exception:" + ex.Message); return(""); } }
public string Generate(StoredProcedureTypes sptypeGenerate, ColumnCollection colsFields, string sTableName) { StringBuilder sGeneratedCode = new StringBuilder(); StringBuilder sParamDeclaration = new StringBuilder(); StringBuilder sBody = new StringBuilder(); StringBuilder sINSERTValues = new StringBuilder(); // Setup SP code, begining is the same no matter the type sGeneratedCode.AppendFormat($"CREATE PROCEDURE {sTableName}_{sptypeGenerate.ToString()}"); sGeneratedCode.Append(Environment.NewLine); // Setup body code, different for UPDATE and INSERT switch (sptypeGenerate) { case StoredProcedureTypes.Insert: sBody.AppendFormat($"INSERT INTO [{sTableName}] ("); sBody.Append(Environment.NewLine); sINSERTValues.Append("VALUES ("); sINSERTValues.Append(Environment.NewLine); break; case StoredProcedureTypes.Update: sBody.AppendFormat($"UPDATE [{sTableName}]"); sBody.Append(Environment.NewLine); sBody.Append("SET"); sBody.Append(Environment.NewLine); break; case StoredProcedureTypes.Delete: sBody.AppendFormat($"Delete From [{sTableName}]"); sBody.Append(Environment.NewLine); break; case StoredProcedureTypes.Get: sBody.AppendFormat($"Select * From [{sTableName}]"); sBody.Append(Environment.NewLine); break; case StoredProcedureTypes.GetList: sBody.AppendFormat($"Select * From [{sTableName}]"); break; } foreach (Column colCurrent in colsFields) { // Param Declaration construction //If Column is Identy and case Is Insert dont add it as parameter if (sptypeGenerate == StoredProcedureTypes.Insert && colCurrent.Identity) { continue; } if ((sptypeGenerate == StoredProcedureTypes.Delete || sptypeGenerate == StoredProcedureTypes.Get) && !colCurrent.InPrimaryKey) { continue; } if (sptypeGenerate == StoredProcedureTypes.GetList) { break; } { sParamDeclaration.AppendFormat($" @{colCurrent.Name} {colCurrent.DataType}"); // Only binary, char, nchar, nvarchar, varbinary and varchar may have their length declared if ( colCurrent.DataType.Name == "binary" || colCurrent.DataType.Name == "char" || colCurrent.DataType.Name == "nchar" || colCurrent.DataType.Name == "nvarchar" || colCurrent.DataType.Name == "varbinary" || colCurrent.DataType.Name == "varchar") { sParamDeclaration.AppendFormat($"({colCurrent.DataType.MaximumLength})"); } sParamDeclaration.Append(","); sParamDeclaration.Append(Environment.NewLine); // Body construction, different for INSERT and UPDATE switch (sptypeGenerate) { case StoredProcedureTypes.Insert: //Take care and don't take Identity if (!colCurrent.Identity) { sINSERTValues.AppendFormat($" @{colCurrent.Name},"); sINSERTValues.Append(Environment.NewLine); sBody.AppendFormat($" {colCurrent.Name},"); sBody.Append(Environment.NewLine); } break; case StoredProcedureTypes.Update: //Take care and don't take Primary Keys if (!colCurrent.InPrimaryKey) { sBody.AppendFormat($" {colCurrent.Name} = @{colCurrent.Name},"); sBody.Append(Environment.NewLine); } break; } } } //Remove last "," if it was Insert or Update if (sptypeGenerate == StoredProcedureTypes.Insert || sptypeGenerate == StoredProcedureTypes.Update) { sBody = new StringBuilder(sBody.ToString().TrimEnd (("," + Environment.NewLine).ToCharArray())); sBody.Append(Environment.NewLine); } //Now Append the Where Condition if there are primary keys List <Column> objPrimaryKeys = GetPrimaryKeys(colsFields); if (objPrimaryKeys.Count > 0) { switch (sptypeGenerate) { case StoredProcedureTypes.Update: case StoredProcedureTypes.Delete: case StoredProcedureTypes.Get: sBody.AppendFormat(" Where "); sBody.Append(Environment.NewLine); break; } //Now Append the Primary Keys foreach (Column obj in objPrimaryKeys) { switch (sptypeGenerate) { case StoredProcedureTypes.Update: case StoredProcedureTypes.Delete: case StoredProcedureTypes.Get: sBody.AppendFormat($" {obj.Name} = @{obj.Name}"); sBody.Append(Environment.NewLine); sBody.Append(" AND "); sBody.Append(Environment.NewLine); break; } } //Remove the Last AND switch (sptypeGenerate) { case StoredProcedureTypes.Update: case StoredProcedureTypes.Delete: case StoredProcedureTypes.Get: if (sBody.ToString().EndsWith(" AND " + Environment.NewLine)) { sBody = new StringBuilder(sBody.ToString().Remove(sBody.ToString().Length - 7, 7)); } break; } } // Now stitch the body parts together into the SP whole if (sptypeGenerate != StoredProcedureTypes.GetList) //there is no parameters { sGeneratedCode.Append(sParamDeclaration.Remove(sParamDeclaration.Length - 3, 3)); } sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append("AS"); sGeneratedCode.Append(Environment.NewLine); //sGeneratedCode.Append(sBody.Remove(sBody.Length -3, 3)); sGeneratedCode.Append(sBody); if (sptypeGenerate == StoredProcedureTypes.Insert) { sGeneratedCode.Append(")"); sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append(sINSERTValues.Remove(sINSERTValues.Length - 3, 3)); sGeneratedCode.Append(")"); } sGeneratedCode.Append(Environment.NewLine); sGeneratedCode.Append("GO"); return(sGeneratedCode.ToString()); }
public object GetMappingItem(object context) { StoredProcedureTypes storageFunction = (StoredProcedureTypes)context; return("test." + storageFunction.ToString() + "Test"); }