//-------------------------------------
//
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");
}
