//-------------------------------------
//
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 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);
}
/// <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 creates the insert proc for a data table.
/// </summary>
/// <param name="dataTable"></param>
private void CreateInsertProc(DataTable dataTable)
{
// procType
StoredProcedureTypes procType = StoredProcedureTypes.Insert;
// Create StroredProcedure
CreateStoredProcedure(dataTable, procType);
}
/// <summary>
/// This method creates the Find stored procedure object
/// for a DataTable.
/// </summary>
/// <param name="dataTable"></param>
private void CreateFindProc(DataTable dataTable)
{
// procType
StoredProcedureTypes procType = StoredProcedureTypes.Find;
// Create StroredProcedure
CreateStoredProcedure(dataTable, procType);
}
/// <summary>
/// This method creates the DeleteStoredProcedureObject
/// for a data table.
/// </summary>
private void CreateDeleteProc(DataTable dataTable)
{
// procType
StoredProcedureTypes procType = StoredProcedureTypes.Delete;
// Create StroredProcedure
CreateStoredProcedure(dataTable, procType);
}
/// <summary>
/// This method writes the summary for the stored proecedure object.
/// </summary>
/// <param name="dataTable"></z>
private void WriteClassSummary(DataTable dataTable, StoredProcedureTypes procType)
{
// Write summary for this class
WriteLine("/// <summary>");
// if this is a fetch all proc
if (procType == StoredProcedureTypes.FetchAll)
{
WriteLine("/// This class is used to " + procType + " '" + dataTable.ClassName + "' objects.");
}
else
{
WriteLine("/// This class is used to " + procType + " a '" + dataTable.ClassName + "' object.");
}
WriteLine("/// </summary>");
}
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("");
}
}
private void tvwServerExplorer_AfterSelect(object sender, System.Windows.Forms.TreeViewEventArgs e)
{
TreeNode tnodeSelected = (TreeNode)e.Node;
if (tnodeSelected.ImageIndex == 2)
{
this.Cursor = Cursors.WaitCursor;
statbarpnlMain.Text = "Generating Stored Procedure, please wait...";
// SP selected, generate SP
TreeNode tnodeTable = (TreeNode)tnodeSelected.Parent;
dmoMain.Table = tnodeTable.Text;
StoredProcedure spgen = new StoredProcedure();
StoredProcedureTypes spType = new StoredProcedureTypes();
dmoMain.Connect();
if (tnodeSelected.Text.IndexOf("INSERT") != -1)
{
txtGeneratedCode.Text = spgen.GenerateInsert(dmoMain.Fields, dmoMain.Table);
}
if (tnodeSelected.Text.IndexOf("UPDATE") != -1)
{
txtGeneratedCode.Text = spgen.GenerateUpdate(dmoMain.Fields, dmoMain.Table);
}
if (tnodeSelected.Text.IndexOf("DELETE") != -1)
{
txtGeneratedCode.Text = spgen.GenerateDelete(dmoMain.Fields, dmoMain.Table);
}
if (tnodeSelected.Text.IndexOf("SELECT") != -1)
{
txtGeneratedCode.Text = spgen.GenerateSelect(dmoMain.Fields, dmoMain.Table);
}
dmoMain.DisConnect();
this.Cursor = Cursors.Default;
statbarpnlMain.Text = "Stored Procedure generated...";
txtGeneratedCode.Focus();
txtGeneratedCode.SelectAll();
}
}
/// <summary>
/// This method writes the properties for each
/// DataController and the ErrorProcessor and
/// DataManager objects.
/// </summary>
private void WriteProperties(DataTable dataTable, StoredProcedureTypes procType)
{
// Write Region For Properties
BeginRegion("Properties");
// Write Blank Line
WriteLine();
// Increase Indent
Indent++;
// Decrease Indent
Indent--;
// End Properties Region
EndRegion();
// Write Blank Line After Properties Region
WriteLine();
}
/// <summary>
/// This method writes the constructor for this object.
/// </summary>
/// <param name="dataTable"></param>
private void WriteConstructor(DataTable dataTable, StoredProcedureTypes procType)
{
// Begin Region Static Methods
BeginRegion("Constructor");
// get className
string className = GetClassName(dataTable, procType);
// Write Constructor Summary
WriteLine("/// <summary>");
WriteLine("/// Create a new instance of a '" + className + "' object.");
WriteLine("/// </summary>");
// Get Constructor Line
string constructorLine = "public " + className + "()";
// Write constructorLine Line
WriteLine(constructorLine);
// Write Open Bracket
WriteOpenBracket(true);
// Write Comment Create Child Controllers
WriteComment("Perform Initialization");
// Write Call to Init Method
WriteLine("Init();");
// Decrease Indent
Indent--;
// Write Close Bracket
WriteCloseBracket();
// Write endregion
EndRegion();;
// Write Blank Line
WriteLine();
}
/// <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());
}
//
public string CreateGetOneMethod(StoredProcedureTypes type)
{
if (ID != null)
{
try
{
string id = Globals.GetProgramatlyName(ID.Name);
id = Globals.ConvetStringToCamelCase(id);
//
string MethodName = "Get" + global.TableProgramatlyName + "Object";
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 + "." + 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 + " object.</returns>";
//Method Body
StringBuilder methodBody = new StringBuilder();
methodBody.Append(xmlDocumentation);
methodBody.Append("\n\tpublic static " + MethodReturn + " " + MethodName + "(" + Globals.GetAliasDataType(ID.Datatype) + " " + id + ")");
methodBody.Append("\n\t{");
methodBody.Append("\n\t\treturn " + global.SqlDataProviderClass + ".Instance." + MethodName + "(" + id + ");");
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("");
}
}
/// <summary>
/// Write the methods region and the Init method.
/// </summary>
private void WriteMethods(DataTable dataTable, StoredProcedureTypes procType)
{
// Begin Region For Methods
BeginRegion("Methods");
// Increase Indent
Indent++;
// Write Blank Line
WriteLine();
// Write Out The Init method
WriteInitMethod(dataTable, procType);
// Decrease Indent
Indent--;
// End Region
EndRegion();
// Write Blank Line
WriteLine();
}
//Scimple Creation Method-----------------------------------
public string CreateInsertUpdateDeleteMethod(StoredProcedureTypes type, string MethodName, Hashtable allParameters)
{
string id = Globals.GetProgramatlyName(ID.Name);
id = Globals.ConvetStringToCamelCase(id);
//
string ProcName = global.TableProgramatlyName + "_" + MethodName;
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 (!allParameters.Contains(colCurrent.Name))
{
continue;
}
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);
}
}
/// <summary>
/// Obtains a stored procedure name used to call a stored procedure in SQL Server
/// </summary>
/// <typeparam name="T">The type of the model used for CRUD operations</typeparam>
/// <param name="context">List, Insert, Update, Delete, or other operation</param>
/// <returns></returns>
public object GetMappingItem <T>(object context)
{
string storedProcedureName = string.Empty;
StoredProcedureTypes storageFunction = (StoredProcedureTypes)context;
switch (storageFunction)
{
case StoredProcedureTypes.List:
if (typeof(T) == typeof(Color))
{
storedProcedureName = "Stock.GetColors";
}
else if (typeof(T) == typeof(Size))
{
storedProcedureName = "Stock.GetSizes";
}
else if (typeof(T) == typeof(Gadget))
{
storedProcedureName = "Stock.GetAllGadgets";
}
else
{
throw new ArgumentException(String.Format("{0} is not a supported model for the {1} function", typeof(T).Name, storageFunction), "model");
}
break;
case StoredProcedureTypes.Insert:
if (typeof(T) == typeof(GadgetInsertData))
{
storedProcedureName = "Stock.InsertGadget";
}
else
{
throw new ArgumentException(String.Format("{0} is not a supported model for the {1} function", typeof(T).Name, storageFunction), "model");
}
break;
case StoredProcedureTypes.Update:
if (typeof(T) == typeof(GadgetInsertData))
{
storedProcedureName = "Stock.UpdateGadget";
}
else
{
throw new ArgumentException(String.Format("{0} is not a supported model for the {1} function", typeof(T).Name, storageFunction), "model");
}
break;
case StoredProcedureTypes.Delete:
if (typeof(T) == typeof(Gadget))
{
storedProcedureName = "Stock.DeleteGadget";
}
else
{
throw new ArgumentException(String.Format("{0} is not a supported model for the {1} function", typeof(T).Name, storageFunction), "model");
}
break;
case StoredProcedureTypes.ById:
if (typeof(T) == typeof(Gadget))
{
storedProcedureName = "Stock.GetGadgetById";
}
else
{
throw new ArgumentException(String.Format("{0} is not a supported model for the {1} function", typeof(T).Name, storageFunction), "model");
}
break;
default:
throw new ArgumentException(String.Format("{0} is not a supported function", storageFunction), "function");
}
return(storedProcedureName);
}
public static string GetProcedureName(StoredProcedureTypes spType)
{
return("");
}
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 string Generate(StoredProcedureTypes sptypeGenerate, FieldCollection fieldCollection, string tableName)
{
Session.Keys = GetInfoFieldSqlServer.GetKeysDictionary(tableName);
StringBuilder sGeneratedCode = new StringBuilder();
StringBuilder sParamDeclaration = new StringBuilder();
StringBuilder sBody = new StringBuilder();
StringBuilder sINSERTValues = new StringBuilder();
bool isFirst = true;
string functionName = string.Empty;
switch (sptypeGenerate)
{
case StoredProcedureTypes.Insert: functionName = string.Format(Session.InsertSql, tableName); break;
case StoredProcedureTypes.Clone: functionName = string.Format(Session.CloneSql, tableName); break;
case StoredProcedureTypes.Update: functionName = string.Format(Session.UpdateSql, tableName); break;
case StoredProcedureTypes.Delete: functionName = string.Format(Session.DeleteSql, tableName); break;
case StoredProcedureTypes.GetCollection: functionName = string.Format(Session.GetCollectionSql, tableName); break;
case StoredProcedureTypes.GetByCode: functionName = string.Format(Session.GetByCodeSql, tableName); break;
case StoredProcedureTypes.GetByKeyuni: functionName = string.Format(Session.GetByKeyuniSql, tableName); break;
}
if (sptypeGenerate != StoredProcedureTypes.View)
{
if (sptypeGenerate != StoredProcedureTypes.ViewPlus)
{
sGeneratedCode.AppendFormat(string.Format("if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[{0}]", functionName));
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append("GO");
sGeneratedCode.Append(Environment.NewLine);
}
}
else
{
sGeneratedCode.AppendFormat(string.Format("if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[{0}]", string.Concat(Session.View, tableName)));
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append("GO");
sGeneratedCode.Append(Environment.NewLine);
}
// Setup SP code, begining is the same no matter the type
//if (sGeneratedCode.Length != 0) sGeneratedCode.Append("\\n");
if (Session.Comment)
{
sGeneratedCode.AppendFormat(string.Concat("-- TABLE : ", tableName));
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.AppendFormat(string.Format("-- AUTHOR : {0}", Session.AuthorName));
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.AppendFormat(string.Concat("-- DATE : ", DateTime.Now.ToString("dd MMM yyyy HH:mm:ss")));
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append(Environment.NewLine);
}
if ((sptypeGenerate != StoredProcedureTypes.View) && (sptypeGenerate != StoredProcedureTypes.ViewPlus))
{
sGeneratedCode.AppendFormat("CREATE PROCEDURE {0}", functionName);
}
else
{
if (sptypeGenerate == StoredProcedureTypes.View)
{
sGeneratedCode.AppendFormat("CREATE VIEW {0}{1}", Session.View, tableName);
}
else
{
sGeneratedCode.AppendFormat("CREATE VIEW {0}{1}Plus", Session.View, tableName);
}
}
sGeneratedCode.Append(Environment.NewLine);
switch (sptypeGenerate)
{
case StoredProcedureTypes.Insert:
sBody.AppendFormat("INSERT INTO [{0}] (", tableName);
sBody.Append(Environment.NewLine);
sINSERTValues.Append("VALUES (");
sINSERTValues.Append(Environment.NewLine);
break;
case StoredProcedureTypes.Clone:
sBody.AppendFormat("INSERT INTO [{0}] (", tableName);
sBody.Append(Environment.NewLine);
sINSERTValues.Append("VALUES (");
sINSERTValues.Append(Environment.NewLine);
break;
case StoredProcedureTypes.Update:
sBody.AppendFormat("UPDATE [{0}]", tableName);
sBody.Append(Environment.NewLine);
sBody.Append("SET");
sBody.Append(Environment.NewLine);
break;
}
string sqlMax = string.Empty;
bool isFirstInsert = true;
bool isFirstClone = true;
bool isFirstUpdate = true;
switch (sptypeGenerate)
{
case StoredProcedureTypes.Insert:
case StoredProcedureTypes.Clone:
case StoredProcedureTypes.Update:
foreach (Field colCurrent in fieldCollection)
{
bool isFieldValid = true;
if (Session.GuidCreator == "Database")
{
if ((Session.Keyuni) && (sptypeGenerate == StoredProcedureTypes.Insert) && (colCurrent.Name.Equals(Session.TableKey)))
{
isFieldValid = false;
}
if ((!Session.Keyuni) && (sptypeGenerate == StoredProcedureTypes.Insert) && (colCurrent.Name.Equals(Session.TableKey)))
{
isFieldValid = false;
}
if ((Session.Keyuni) && (sptypeGenerate == StoredProcedureTypes.Update) && (colCurrent.Name.Equals(Session.TableKey)))
{
isFieldValid = false;
}
}
if (Session.GuidCreator == "Framework")
{
if ((sptypeGenerate == StoredProcedureTypes.Insert) && (Session.Keys.ContainsKey(colCurrent.Name)))
{
isFieldValid = true;
}
if ((sptypeGenerate == StoredProcedureTypes.Update) && (Session.Keys.ContainsKey(colCurrent.Name)))
{
isFieldValid = false;
}
isFieldValid = true;
}
if (colCurrent.Name.Equals("Timespan"))
{
isFieldValid = false;
}
if (isFieldValid)
{
// Declaration
sParamDeclaration.AppendFormat(" @{0} {1}", new string[] { colCurrent.Name, colCurrent.Datatype });
if (
colCurrent.Datatype == "binary" ||
colCurrent.Datatype == "char" ||
colCurrent.Datatype == "nchar" ||
colCurrent.Datatype == "nvarchar" ||
colCurrent.Datatype == "varbinary" ||
colCurrent.Datatype == "varchar")
{
if (colCurrent.Length == -1)
{
sParamDeclaration.AppendFormat("(max)");
}
else
{
sParamDeclaration.AppendFormat("({0})", colCurrent.Length);
}
}
sParamDeclaration.Append(",");
sParamDeclaration.Append(Environment.NewLine);
//Insert Value
if (!colCurrent.Name.Equals("Keyuni"))
{
switch (sptypeGenerate)
{
case StoredProcedureTypes.Insert:
if (Session.GuidCreator == "Database")
{
if (!colCurrent.Name.Equals(Session.TableKey))
{
sINSERTValues.AppendFormat(" @{0},", colCurrent.Name);
sINSERTValues.Append(Environment.NewLine);
}
}
if (Session.GuidCreator == "Framework")
{
sINSERTValues.AppendFormat(" @{0},", colCurrent.Name);
sINSERTValues.Append(Environment.NewLine);
}
if (isFirstInsert)
{
isFirstInsert = false;
}
else
{
sBody.Append(Environment.NewLine);
}
sBody.AppendFormat(" {0},", colCurrent.Name);
break;
case StoredProcedureTypes.Clone:
sINSERTValues.AppendFormat(" @{0},", colCurrent.Name);
sINSERTValues.Append(Environment.NewLine);
if (isFirstClone)
{
isFirstClone = false;
}
else
{
sBody.Append(Environment.NewLine);
}
sBody.AppendFormat(" {0},", colCurrent.Name);
break;
case StoredProcedureTypes.Update:
if (colCurrent.Name.Equals(Session.TableKey))
{
}
else
{
if (isFirstUpdate)
{
isFirstUpdate = false;
}
else
{
sBody.Append(Environment.NewLine);
}
sBody.AppendFormat(" {0} = @{0},", new string[] { colCurrent.Name, });
}
break;
}
}
}
}
break;
case StoredProcedureTypes.View:
sBody.Append("AS");
sBody.Append(Environment.NewLine);
string fields = string.Empty;
//foreach (SQLDMO.Column colCurrent in colsFields)
foreach (Field colCurrent in fieldCollection)
{
if (!fields.Equals(string.Empty))
{
fields = string.Concat(fields, ",");
}
fields = string.Concat(fields, colCurrent.Name);
}
sBody.AppendFormat(string.Concat("SELECT ", fields));
sBody.Append(Environment.NewLine);
sBody.AppendFormat(string.Format("FROM [dbo].[{0}]", tableName));
sBody.Append(Environment.NewLine);
sBody.Append("GO");
sBody.Append(Environment.NewLine);
sGeneratedCode.Append(sBody);
break;
case StoredProcedureTypes.ViewPlus:
sBody.Append("AS");
sBody.Append(Environment.NewLine);
fields = string.Empty;
//foreach (SQLDMO.Column colCurrent in colsFields)
foreach (Field colCurrent in fieldCollection)
{
if (!fields.Equals(string.Empty))
{
fields = string.Concat(fields, ",");
}
fields = string.Concat(fields, colCurrent.Name);
}
sBody.AppendFormat(string.Concat("SELECT ", fields));
sBody.Append(Environment.NewLine);
sBody.AppendFormat(string.Format("FROM [dbo].[{0}]", tableName));
sBody.Append(Environment.NewLine);
sBody.Append("GO");
sBody.Append(Environment.NewLine);
sGeneratedCode.Append(sBody);
break;
case StoredProcedureTypes.Delete:
if (Session.Keyuni)
{
sBody.AppendFormat(" (@Keyuni [uniqueidentifier])");
sBody.Append(Environment.NewLine);
sBody.Append("AS");
sBody.Append(Environment.NewLine);
sBody.Append("SET NOCOUNT ON");
sBody.Append(Environment.NewLine);
sBody.AppendFormat(string.Format("DELETE [dbo].[{0}]", tableName));
sBody.Append(Environment.NewLine);
sBody.AppendFormat("WHERE ( [Keyuni] = @Keyuni)");
sBody.Append(Environment.NewLine);
sBody.Append("GO");
sBody.Append(Environment.NewLine);
sGeneratedCode.Append(sBody);
}
else
{
bool isFirstParameter = true;
foreach (KeyValuePair <string, KeyColumn> kvp in Session.Keys)
{
if (!isFirstParameter)
{
sBody.Append(",");
sBody.Append(Environment.NewLine);
}
else
{
isFirstParameter = false;
}
sBody.AppendFormat(string.Format(" @{0} {1}", kvp.Key, kvp.Value.Type));
}
sBody.Append(Environment.NewLine);
sBody.Append("AS");
sBody.Append(Environment.NewLine);
sBody.Append("SET NOCOUNT ON");
sBody.Append(Environment.NewLine);
sBody.AppendFormat(string.Format("DELETE [dbo].[{0}]", tableName));
sBody.Append(Environment.NewLine);
sBody.AppendFormat("WHERE ( ");
isFirst = true;
foreach (KeyValuePair <string, KeyColumn> kvp in Session.Keys)
{
if (!isFirst)
{
sBody.AppendFormat(" AND ");
}
sBody.AppendFormat(string.Format(" [{0}] = @{0} ", kvp.Key));
isFirst = false;
}
sBody.AppendFormat(" )");
sBody.Append(Environment.NewLine);
sBody.Append("GO");
sBody.Append(Environment.NewLine);
sGeneratedCode.Append(sBody);
}
break;
case StoredProcedureTypes.GetByCode:
//sBody.AppendFormat(" (@Code [int])");
bool isFirstParameterGetByCode = true;
foreach (KeyValuePair <string, KeyColumn> kvp in Session.Keys)
{
if (!isFirstParameterGetByCode)
{
sBody.Append(",");
sBody.Append(Environment.NewLine);
}
else
{
isFirstParameterGetByCode = false;
}
sBody.AppendFormat(string.Format(" @{0} {1}", kvp.Key, kvp.Value.Type));
}
sBody.Append(Environment.NewLine);
sBody.Append("AS");
sBody.Append(Environment.NewLine);
sBody.Append("SET NOCOUNT ON");
sBody.Append(Environment.NewLine);
sBody.AppendFormat(string.Format("SELECT * FROM [dbo].[{0}{1}]", Session.View, tableName));
sBody.Append(Environment.NewLine);
//sBody.AppendFormat(string.Format("WHERE ( [{0}] = @Code)", Session.TableKey));
sBody.AppendFormat("WHERE ( ");
isFirst = true;
foreach (KeyValuePair <string, KeyColumn> kvp in Session.Keys)
{
if (!isFirst)
{
sBody.AppendFormat(" AND ");
}
sBody.AppendFormat(string.Format(" [{0}] = @{0} ", kvp.Key));
isFirst = false;
}
sBody.AppendFormat(" )");
sBody.Append(Environment.NewLine);
sBody.Append("RETURN");
sBody.Append(Environment.NewLine);
sBody.Append("GO");
sBody.Append(Environment.NewLine);
sGeneratedCode.Append(sBody);
break;
case StoredProcedureTypes.GetByKeyuni:
sBody.AppendFormat(" (@Keyuni [uniqueidentifier])");
sBody.Append(Environment.NewLine);
sBody.Append("AS");
sBody.Append(Environment.NewLine);
sBody.Append("SET NOCOUNT ON");
sBody.Append(Environment.NewLine);
sBody.AppendFormat(string.Format("SELECT * FROM [dbo].[{0}{1}]", Session.View, tableName));
sBody.Append(Environment.NewLine);
sBody.AppendFormat("WHERE ( [Keyuni] = @Keyuni)");
sBody.Append(Environment.NewLine);
sBody.Append("RETURN");
sBody.Append(Environment.NewLine);
sBody.Append("GO");
sBody.Append(Environment.NewLine);
sGeneratedCode.Append(sBody);
break;
case StoredProcedureTypes.GetCollection:
sBody.Append("AS");
sBody.Append(Environment.NewLine);
sBody.Append("SET NOCOUNT ON");
sBody.Append(Environment.NewLine);
sBody.AppendFormat(string.Format("SELECT * FROM [dbo].[{0}{1}]", Session.View, tableName));
sBody.Append(Environment.NewLine);
sBody.Append("RETURN");
sBody.Append(Environment.NewLine);
sBody.Append("GO");
sBody.Append(Environment.NewLine);
sGeneratedCode.Append(sBody);
break;
}
switch (sptypeGenerate)
{
case StoredProcedureTypes.Insert:
case StoredProcedureTypes.Clone:
case StoredProcedureTypes.Update:
if (Session.GuidCreator == "Database")
{
sParamDeclaration.Append("@Identity int OUTPUT,");
sParamDeclaration.Append(Environment.NewLine);
}
sGeneratedCode.Append(sParamDeclaration.Remove(sParamDeclaration.Length - 3, 3));
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append("AS");
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append("SET NOCOUNT ON");
sGeneratedCode.Append(Environment.NewLine);
//sGeneratedCode.Append(sBody);
sGeneratedCode.Append(sBody.Remove(sBody.Length - 1, 1));
//sGeneratedCode.Append(sBody.Remove(sBody.Length - 3, 3));
if ((sptypeGenerate == StoredProcedureTypes.Insert) || (sptypeGenerate == StoredProcedureTypes.Clone))
{
sGeneratedCode.Append(")");
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append(sINSERTValues.Remove(sINSERTValues.Length - 3, 3));
//sGeneratedCode.Append(sINSERTValues);
sGeneratedCode.Append(")");
if (Session.GuidCreator == "Database")
{
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append("SELECT @Identity = @@IDENTITY");
}
}
else
{
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.AppendFormat("WHERE ( ");
isFirst = true;
foreach (KeyValuePair <string, KeyColumn> kvp in Session.Keys)
{
if (!isFirst)
{
sGeneratedCode.AppendFormat(" AND ");
}
sGeneratedCode.AppendFormat(string.Format(" [{0}] = @{0} ", kvp.Key));
isFirst = false;
}
sGeneratedCode.AppendFormat(" )");
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append("GO");
sGeneratedCode.Append(Environment.NewLine);
//if (Session.Keyuni)
// sGeneratedCode.AppendFormat("WHERE ( [Keyuni] = @Keyuni)");
//else sGeneratedCode.AppendFormat(string.Format("WHERE ( [{0}] = @{0})", Session.TableKey));
}
sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append("GO");
sGeneratedCode.Append(Environment.NewLine);
break;
}
return(sGeneratedCode.ToString());
}
/// <summary>
/// This method creates all the stored procedures
/// </summary>
/// <param name="dataTable"></param>
/// <param name="procType"></param>
private void CreateStoredProcedure(DataTable dataTable, StoredProcedureTypes procType)
{
// Indent = 0
Indent = 0;
// Create file name for a delete
string fileName = CreateFileName(dataTable, procType);
// if there is an _ in file name replace it.
fileName = fileName.Replace("_", "");
// Create file
CreateFile(fileName, DataManager.ProjectTypeEnum.DAC);
// Update 12.23.2020: Stored procedures do not need any using statements
// Write 2 Blank Lines
WriteLine();
WriteLine();
// Write NameSpace
StringBuilder sb = new StringBuilder("namespace ");
sb.Append(this.NameSpaceName);
// get prod folder
string procFolder = GetProcFolder(procType, true, false);
sb.Append(procFolder);
string nameSpace = sb.ToString();
WriteLine(nameSpace);
// Write Open Brack
WriteOpenBracket(true);
// Write Blank Line
WriteLine();
// Get ClassName
string className = GetClassName(dataTable, procType);
// Write Region for this reader
BeginRegion("class " + className);
// Write Object Reader Summary
WriteClassSummary(dataTable, procType);
// get class line
string classLine = "public class " + className + " : StoredProcedure";
// Write class line
WriteLine(classLine);
// Write Open Bracket
WriteOpenBracket(true);
// Write Blank Line
WriteLine();
// Write Private Variables
WritePrivateVariables(dataTable);
// Write Constructor
WriteConstructor(dataTable, procType);
// Write Methods
WriteMethods(dataTable, procType);
// Write Properties
WriteProperties(dataTable, procType);
// write Close Bracket
WriteCloseBracket(true);
// Write endregion
EndRegion();
// Write Blank Line
WriteLine();
// write Close Bracket
WriteCloseBracket(true);
// Close This File
this.Writer.Close();
}
/// <summary>
/// This method gets the folder to place this proc in.
/// </summary>
/// <param name="procType"></param>
/// <returns></returns>
private string GetProcFolder(StoredProcedureTypes procType, bool addPrecedingDot, bool addBackslashToEnd)
{
// Get StringBuilder
StringBuilder sb = new StringBuilder();
// if AddDot.
if (addPrecedingDot)
{
// Add Dot.
sb.Append(".");
}
// local
string procFolder = null;
// determine procFolder by data type
switch (procType)
{
case StoredProcedureTypes.Delete:
// set procFolder to Delete
procFolder = "Delete";
// required
break;
case StoredProcedureTypes.FetchAll:
case StoredProcedureTypes.Find:
// set procFolder to Fetch
procFolder = "Fetch";
// required
break;
case StoredProcedureTypes.Insert:
// set procFolder to Insert
procFolder = "Insert";
// required
break;
case StoredProcedureTypes.Update:
// set procFolder to Insert
procFolder = "Update";
// required
break;
}
// return value
sb.Append(procFolder);
// Now Append Procedures
sb.Append("Procedures");
if (addBackslashToEnd)
{
// append back slash
sb.Append(@"\");
}
// 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("");
}
}
public object GetMappingItem(object context)
{
StoredProcedureTypes storageFunction = (StoredProcedureTypes)context;
return("test." + storageFunction.ToString() + "Test");
}
