public static EnumInputType GetStringElementType(CustomColumn column)
{
if (column.ProgramatlyName.ToLower().EndsWith("url"))
{
return(EnumInputType.url);
}
else if (column.ProgramatlyName.ToLower().EndsWith("mobile"))
{
return(EnumInputType.tel);
}
else if (column.ProgramatlyName.ToLower().EndsWith("email"))
{
return(EnumInputType.email);
}
else if (column.ProgramatlyName.ToLower().EndsWith("date"))
{
return(EnumInputType.date);
}
else if (column.ProgramatlyName.ToLower().IndexOf("date") > -1)
{
return(EnumInputType.date);
}
else if (column.ProgramatlyName.ToLower().EndsWith("password"))
{
return(EnumInputType.password);
}
else if (column.MaxLength > 0 && column.MaxLength > 150)
{
return(EnumInputType.textarea);
}
else
{
return(EnumInputType.text);
}
}
public void GetRelatedTable(ForeignKeyCollection fkc, CustomColumn clmn)
{
foreach (ForeignKey relation in fkc)
{
foreach (ForeignKeyColumn c in relation.Columns)
{
if (c.Name == clmn.dbObject.Name)
{
clmn.ReferanceTable = GetTable(relation.ReferencedTable);
}
}
}
}
public static bool IsMatchIdentifier(string Identifier, CustomColumn c)
{
int identifierIndex = c.NameInDatabase.IndexOf(Identifier);
int indexIfMatches = (c.NameInDatabase.Length - Identifier.Length);
if (identifierIndex > -1 && identifierIndex == indexIfMatches)
{
return(true);
}
else
{
return(false);
}
}
//---------------------------------
private string GenerateGet()
{
StringBuilder procedureStatement = new StringBuilder();
try
{
StringBuilder parametersDecleration = new StringBuilder();
StringBuilder whereParameter = new StringBuilder();
CustomColumn c = CurrentTable.ID;
if (c != null)
{
switch (c.SqlDataType)
{
case Microsoft.SqlServer.Management.Smo.SqlDataType.Binary:
case Microsoft.SqlServer.Management.Smo.SqlDataType.Char:
case Microsoft.SqlServer.Management.Smo.SqlDataType.NChar:
case Microsoft.SqlServer.Management.Smo.SqlDataType.NVarChar:
case Microsoft.SqlServer.Management.Smo.SqlDataType.VarBinary:
case Microsoft.SqlServer.Management.Smo.SqlDataType.VarChar:
parametersDecleration.AppendFormat("\n\t@{0} {1}({2}) = NULL", new string[] { c.ProgramatlyName, c.SqlDbTypeString, c.MaxLength.ToString() });
break;
default:
parametersDecleration.AppendFormat("\n\t@{0} {1} = NULL", new string[] { c.ProgramatlyName, c.SqlDbTypeString });
break;
}
whereParameter.AppendFormat("\n\tWhere (@{1} IS NULL OR [{0}] = @{1})", c.NameInDatabase, c.ProgramatlyName);
}
procedureStatement.AppendFormat("\n\nCREATE PROCEDURE {0}_Get", CurrentTable.ProgramatlyName);
procedureStatement.Append(parametersDecleration.ToString());
procedureStatement.Append("\nAS");
procedureStatement.AppendFormat("\n\tSELECT * FROM {0}.[{1}] ", AppConfig.Instance.dbOwner, CurrentTable.NameInDatabase);
procedureStatement.Append(whereParameter.ToString());
procedureStatement.Append("\nGO");
}
catch (Exception ex)
{
//MessageBox.Show("My Generated Code Exception:" + ex.Message);
Console.WriteLine("My Generated Code Exception:" + ex.Message);
}
return(procedureStatement.ToString());
}
public static EnumInputType GetElementInputType(CustomColumn column)
{
if (column.InPrimaryKey)
{
return(EnumInputType.hidden);
}
else if (column.IsForeignKey)
{
return(EnumInputType.select);
}
else
if (column.SqlDataType == SqlDataType.Text || column.SqlDataType == SqlDataType.NText)
{
return(EnumInputType.textarea);
}
else if (column.DotNetType == typeof(System.Enum))
{
return(EnumInputType.select);
}
else if (column.DotNetType == typeof(System.DateTime))
{
return(EnumInputType.date);
}
else if (column.DotNetType == typeof(System.String))
{
return(GetStringElementType(column));
}
else if (Globals.IsNumericType(column.DotNetType))
{
return(EnumInputType.number);
}
else if (column.DotNetType == typeof(System.Boolean))
{
return(EnumInputType.checkbox);
}
else
{
return(EnumInputType.text);
}
}
private void init()
{
///////////////////////////////////////////////////////////////////////
//Calculate flexigrid column width
int flexigridColumns = CurrentTable.Columns.Count;
if (CurrentTable.ID != null)
{
--flexigridColumns;
}
int columnWidth = (ResourcesParameters.FlexiGridWidth / flexigridColumns);
///////////////////////////////////////////////////////////////////////
string flwxiGridColumnsPattern = "\t\t\t\tnew FlexigridColumn(\"{0}\",\"{0}\",{1},true,\"left\",false,false)";
string ParametersToCreateFlexiJsonPattern = "\t\t\t\t\t\t\t\t\t\t\t\t\t\tx.{0}";
//string forignkeysCodePatern = "\t\t\t\tViewData[\"{0}\"] = {1}.{2}().Select(x => new SelectListItem { Text = x.{3}, Value = x.{4}.ToString() }).ToList();";
CustomColumn c = null;
for (int i = 0; i < CurrentTable.Columns.Count - 1; i++)
{
c = CurrentTable.Columns[i];
if (!c.InPrimaryKey)
{
c = CurrentTable.Columns[i];
FlexiGridColumns.AppendFormat(flwxiGridColumnsPattern, new string[] { c.ProgramatlyName, columnWidth.ToString() });
FlexiGridColumns.Append(",\n");
if (c.DotNetType == typeof(DateTime))
{
ParametersToCreateFlexiJson.AppendFormat(ParametersToCreateFlexiJsonPattern + ".ToString(\"MM/dd/yyyy\")", new string[] { c.ProgramatlyName });
}
else
{
ParametersToCreateFlexiJson.AppendFormat(ParametersToCreateFlexiJsonPattern, new string[] { c.ProgramatlyName });
}
ParametersToCreateFlexiJson.Append(",\n");
}
}
c = CurrentTable.Columns[CurrentTable.Columns.Count - 1];
FlexiGridColumns.AppendFormat(flwxiGridColumnsPattern, new string[] { c.ProgramatlyName, columnWidth.ToString() });
if (c.DotNetType == typeof(DateTime))
{
ParametersToCreateFlexiJson.AppendFormat(ParametersToCreateFlexiJsonPattern + ".ToString(\"MM/dd/yyyy\")", new string[] { c.ProgramatlyName });
}
else
{
ParametersToCreateFlexiJson.AppendFormat(ParametersToCreateFlexiJsonPattern, new string[] { c.ProgramatlyName });
}
//-----------------------------------------------------------------------------------------------------------------
CustomTable tableOfRelation = null;
string columnOFTextFeild = "";
string forignkyText = "";
string primaryKeyText = "";
ForeignKeyColumn rc = null;
//-------------------------------
foreach (ForeignKey r in CurrentTable.dbObject.ForeignKeys)
{
tableOfRelation = null;
columnOFTextFeild = "";
forignkyText = "";
primaryKeyText = "";
rc = null;
if (r != null)
{
rc = r.Columns[0];
forignkyText = Globals.GetProgramatlyName(rc.Name);
primaryKeyText = Globals.GetProgramatlyName(rc.ReferencedColumn);
tableOfRelation = ProjectBuilder.db.GetTable(r.ReferencedTable);
columnOFTextFeild = tableOfRelation.FirstStringColumnName;
ForignkeysCode.Append("\n\t\t\tViewData[\"" + forignkyText + "\"] = " + tableOfRelation.FactoryClass + "." + tableOfRelation.MethodGet + "().Select(x => new SelectListItem { Text = x." + columnOFTextFeild + ", Value = x." + primaryKeyText + ".ToString() }).ToList();");
}
}
//------------------------------------------------------------------------
}
//---------------------------------
private string GenerateInsertProcedure()
{
StringBuilder procedureStatement = new StringBuilder();
try
{
StringBuilder parametersDecleration = new StringBuilder();
StringBuilder insertColumns = new StringBuilder();
StringBuilder insertParameters = new StringBuilder();
StringBuilder outPutParametersValue = new StringBuilder();
for (int i = 0; i < CurrentTable.Columns.Count; i++)
{
CustomColumn c = CurrentTable.Columns[i];
if (!c.NotPassedToSql)
{
parametersDecleration.AppendFormat("\n\t@{0} {1}", new string[] { c.ProgramatlyName, c.SqlDbTypeString });
switch (c.SqlDataType)
{
case Microsoft.SqlServer.Management.Smo.SqlDataType.Binary:
case Microsoft.SqlServer.Management.Smo.SqlDataType.Char:
case Microsoft.SqlServer.Management.Smo.SqlDataType.NChar:
case Microsoft.SqlServer.Management.Smo.SqlDataType.NVarChar:
case Microsoft.SqlServer.Management.Smo.SqlDataType.VarBinary:
case Microsoft.SqlServer.Management.Smo.SqlDataType.VarChar:
parametersDecleration.AppendFormat("({0})", c.MaxLength);
break;
}
if (c.IsNullable)
{
parametersDecleration.Append(" = NULL");
}
if (c.IsOutoutIninsert)
{
parametersDecleration.AppendFormat(" out");
outPutParametersValue.AppendFormat("\n\tSet @{0} = @@Identity", c.ProgramatlyName);
}
else
{
insertColumns.AppendFormat("\n\t\t[{0}]", c.NameInDatabase);
insertParameters.AppendFormat("\n\t\t@{0}", c.ProgramatlyName);
if (i < CurrentTable.Columns.Count - 1)
{
insertColumns.Append(",");
insertParameters.Append(",");
}
}
if (i < CurrentTable.Columns.Count - 1)
{
parametersDecleration.Append(",");
}
}
}
procedureStatement.AppendFormat("\n\nCREATE PROCEDURE {0}_Create", CurrentTable.ProgramatlyName);
procedureStatement.Append(parametersDecleration.ToString());
procedureStatement.Append("\nAS");
procedureStatement.AppendFormat("\n\tINSERT INTO {0}.[{1}] ", AppConfig.Instance.dbOwner, CurrentTable.NameInDatabase);
procedureStatement.Append("\n\t(");
procedureStatement.Append(insertColumns.ToString());
procedureStatement.Append("\n\t)");
procedureStatement.AppendFormat("\n\tValues({0})", insertParameters.ToString());
procedureStatement.AppendFormat("\n\n\t{0}", outPutParametersValue.ToString());
procedureStatement.Append("\nGO");
}
catch (Exception ex)
{
//MessageBox.Show("My Generated Code Exception:" + ex.Message);
Console.WriteLine("My Generated Code Exception:" + ex.Message);
}
return(procedureStatement.ToString());
}
//---------------------------------
//---------------------------------
private string GenerateGetPageByPage()
{
StringBuilder procedureStatement = new StringBuilder();
try
{
string proc = @"
Create PROCEDURE {2}
@PageIndex int,
@PageSize int,
@SortName nvarchar(128) =null,
@SortOrder nvarchar(128) =null,
@QType nvarchar(128) =null,
@Query nvarchar(128) =null,
@TotalRecords int out
As
BEGIN
/***************************************************************************************************************/
/*Conditions Regions*/
/***************************************************************************************************************/
declare @Conditions varchar(Max)
declare @OrderByStatement varchar(Max)
Set @Conditions =' (0=0) ';
Set @OrderByStatement =' ';
----------------------------------------------------------------------------------------------------------
IF @QType is not null And @Query is not null
Set @Conditions = @Conditions + ' And ( '+@QType+' Like ''%'+@Query+''' )'
/***************************************************************************************************************/
/*Order BY Regions*/
/***************************************************************************************************************/
IF @SortName is not null and @SortOrder is not null
Set @OrderByStatement = ' ORDER BY '+@SortName+' '+ @SortOrder
/***************************************************************************************************************/
/*Sql Statement*/
/***************************************************************************************************************/
declare @SqlStatement nvarchar(Max)
----------------------------------------------------------
DECLARE @PageLowerBound int
SET @PageIndex = @PageIndex-1
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
----------------------------------------------------------
-- Create a temp table TO store the SELECT results
CREATE TABLE #PageIndexTable
(
IndexId int IDENTITY (0, 1) NOT NULL,
ID {4}
)
----------------------------------------------------------
Set @SqlStatement='-------------------------------------------
-------------------------------------------
INSERT INTO #PageIndexTable (ID)
SELECT {3} FROM [{0}].[{1}]
-------------------------------------------
Where '+@Conditions+
@OrderByStatement
/**************************************************************************************************************/
print (@SqlStatement)
EXEC (@sqlStatement)
-------------------------------------------
SELECT @TotalRecords= @@ROWCOUNT
-------------------------------------------
SELECT [{1}].*
FROM #PageIndexTable inner join [{0}].{1}
on [{0}].{1}.{3} =#PageIndexTable.ID
WHERE #PageIndexTable.IndexId >= @PageLowerBound AND #PageIndexTable.IndexId <= @PageUpperBound
-------------------------------------------
/**************************************************************************************************************/
End
GO
";
string procedureGetPageByPage = "[dbo].[{0}_GetPageByPage]";
procedureGetPageByPage = string.Format(procedureGetPageByPage, CurrentTable.ProgramatlyName);
CustomColumn c = CurrentTable.ID;
if (c != null)
{
switch (c.SqlDataType)
{
case Microsoft.SqlServer.Management.Smo.SqlDataType.Binary:
case Microsoft.SqlServer.Management.Smo.SqlDataType.Char:
case Microsoft.SqlServer.Management.Smo.SqlDataType.NChar:
case Microsoft.SqlServer.Management.Smo.SqlDataType.NVarChar:
case Microsoft.SqlServer.Management.Smo.SqlDataType.VarBinary:
case Microsoft.SqlServer.Management.Smo.SqlDataType.VarChar:
procedureStatement.AppendFormat(proc, new string[] { AppConfig.Instance.dbOwner, CurrentTable.NameInDatabase, procedureGetPageByPage, c.NameInDatabase, c.SqlDbTypeString + "(" + c.MaxLength.ToString() + ")" });
break;
default:
procedureStatement.AppendFormat(proc, new string[] { AppConfig.Instance.dbOwner, CurrentTable.NameInDatabase, procedureGetPageByPage, c.NameInDatabase, c.SqlDbTypeString });
break;
}
}
}
catch (Exception ex)
{
//MessageBox.Show("My Generated Code Exception:" + ex.Message);
Console.WriteLine("My Generated Code Exception:" + ex.Message);
}
return(procedureStatement.ToString());
}
//---------------------------------
private string GenerateUpdatePrcedure()
{
StringBuilder procedureStatement = new StringBuilder();
try
{
StringBuilder parametersDecleration = new StringBuilder();
StringBuilder parameters = new StringBuilder();
StringBuilder whereParameter = new StringBuilder();
for (int i = 0; i < CurrentTable.Columns.Count; i++)
{
CustomColumn c = CurrentTable.Columns[i];
if (!c.NotPassedToSql)
{
parametersDecleration.AppendFormat("\n\t@{0} {1}", new string[] { c.ProgramatlyName, c.SqlDbTypeString });
switch (c.SqlDataType)
{
case Microsoft.SqlServer.Management.Smo.SqlDataType.Binary:
case Microsoft.SqlServer.Management.Smo.SqlDataType.Char:
case Microsoft.SqlServer.Management.Smo.SqlDataType.NChar:
case Microsoft.SqlServer.Management.Smo.SqlDataType.NVarChar:
case Microsoft.SqlServer.Management.Smo.SqlDataType.VarBinary:
case Microsoft.SqlServer.Management.Smo.SqlDataType.VarChar:
parametersDecleration.AppendFormat("({0})", c.MaxLength);
break;
}
if (c.IsNullable)
{
parametersDecleration.Append(" = NULL");
}
if (c.InPrimaryKey)
{
whereParameter.AppendFormat("\n\tWhere [{0}] = @{1}", c.NameInDatabase, c.ProgramatlyName);
}
else
{
parameters.AppendFormat("\n\t\t[{0}]=@{1}", c.NameInDatabase, c.ProgramatlyName);
if (i < CurrentTable.Columns.Count - 1)
{
parameters.Append(",");
}
}
if (i < CurrentTable.Columns.Count - 1)
{
parametersDecleration.Append(",");
}
}
}
procedureStatement.AppendFormat("\n\nCREATE PROCEDURE {0}_Update", CurrentTable.ProgramatlyName);
procedureStatement.Append(parametersDecleration.ToString());
procedureStatement.Append("\nAS");
procedureStatement.AppendFormat("\n\tUPDATE {0}.[{1}] ", AppConfig.Instance.dbOwner, CurrentTable.NameInDatabase);
procedureStatement.AppendFormat("\n\tSET");
procedureStatement.AppendFormat("\n{0}", parameters.ToString());
procedureStatement.AppendFormat("\n\n\t{0}", whereParameter.ToString());
procedureStatement.Append("\nGO");
}
catch (Exception ex)
{
//MessageBox.Show("My Generated Code Exception:" + ex.Message);
Console.WriteLine("My Generated Code Exception:" + ex.Message);
}
return(procedureStatement.ToString());
}
public CustomTable_Old(Table table)
{
_dbObject = table;
_ProgramatlyName = Globals.GetProgramatlyName(NameInDatabase);
_Columns = new List <CustomColumn>();
int index = 0;
CustomColumn temp = null;
foreach (Column c in _dbObject.Columns)
{
temp = new CustomColumn(c, NameInDatabase, _ProgramatlyName);
_Columns.Add(temp);
if (c.InPrimaryKey)
{
primaryKeyIndex = index;
}
if (firstStringColumnIndex < 0 && temp.DotNetType == typeof(string))
{
firstStringColumnIndex = index;
_FirstStringColumnName = temp.ProgramatlyName;
}
index++;
}
if (firstStringColumnIndex < 0)
{
firstStringColumnIndex = 0;
Column c = _dbObject.Columns[0];
temp = new CustomColumn(c, NameInDatabase, _ProgramatlyName);
_FirstStringColumnName = temp.ProgramatlyName;
}
_SqlDataPrviderClass = string.Format(_SqlDataPrviderClass, _ProgramatlyName);
_ModelClass = string.Format(_ModelClass, _ProgramatlyName);
_FactoryClass = string.Format(_FactoryClass, _ProgramatlyName);
_ControllerClass = string.Format(_ControllerClass, _ProgramatlyName);
_ModelObject = string.Format(_ModelObject, _ProgramatlyName);
_ModelList = string.Format(_ModelList, _ProgramatlyName);
_ProcedureInsert = string.Format(_ProcedureInsert, _ProgramatlyName);
_ProcedureUpdate = string.Format(_ProcedureUpdate, _ProgramatlyName);
_ProcedureDelete = string.Format(_ProcedureDelete, _ProgramatlyName);
_ProcedureGetPageByPage = string.Format(_ProcedureGetPageByPage, _ProgramatlyName);
_ProcedureGet = string.Format(_ProcedureGet, _ProgramatlyName);
_FlixyGridID = string.Format(_FlixyGridID, _ProgramatlyName);
if (ID != null)
{
_ModelID = ID.ProgramatlyName;
_ModelIdDotNetType = ID.DotNetTypeAlias;
if (ID.DotNetType == typeof(int) ||
ID.DotNetType == typeof(short) ||
ID.DotNetType == typeof(long))
{
_ModelIDNullValue = "-1";
}
}
_DialogBox = string.Format(_DialogBox, _ProgramatlyName);
_DialogBoxTitle = string.Format(_DialogBoxTitle, _ProgramatlyName);
_DialogBoxFormID = string.Format(_DialogBoxFormID, _ProgramatlyName);
_JsObject = string.Format(_JsObject, _ProgramatlyName);
_JsForm = string.Format(_JsForm, _ProgramatlyName);
_PathOfModelClass = string.Format(_PathOfModelClass, _ProgramatlyName, _ModelClass);
_PathOfFactoryClass = string.Format(_PathOfFactoryClass, _ProgramatlyName, _FactoryClass);
_PathOfSqlDataPrviderClass = string.Format(_PathOfSqlDataPrviderClass, _ProgramatlyName, _SqlDataPrviderClass);
_PathOfControllerClass = string.Format(_PathOfControllerClass, _ControllerClass);
_PathOfStoredProcerduresFile = string.Format(_PathOfStoredProcerduresFile, _ProgramatlyName);
_PathOfIndexView = string.Format(_PathOfIndexView, _ProgramatlyName);
_PathOfDialogBoxView = string.Format(_PathOfDialogBoxView, _ProgramatlyName);
}
public CustomTable(Table table)
{
_dbObject = table;
_ProgramatlyName = Globals.GetProgramatlyName(NameInDatabase);
PluralizationService pluralizationService = PluralizationService.CreateService(CultureInfo.GetCultureInfo("en-us"));
_pluralCase = pluralizationService.Pluralize(_ProgramatlyName);
_smallSinglerCase = Globals.GetProgramatlyCamelCase(_ProgramatlyName);
_Columns = new List <CustomColumn>();
int index = 0;
CustomColumn customColumn = null;
foreach (Column c in _dbObject.Columns)
{
customColumn = new CustomColumn(c, this, NameInDatabase, _ProgramatlyName);
_Columns.Add(customColumn);
if (c.InPrimaryKey)
{
primaryKeyIndex = index;
}
if (firstStringColumnIndex < 0 && customColumn.DotNetType == typeof(string))
{
firstStringColumnIndex = index;
_FirstStringColumnName = customColumn.ProgramatlyName;
}
if (c.IsForeignKey)
{
this.ForeignKeys.Add(customColumn);
}
index++;
}
if (firstStringColumnIndex < 0)
{
firstStringColumnIndex = 0;
Column c = _dbObject.Columns[0];
customColumn = new CustomColumn(c, this, NameInDatabase, _ProgramatlyName);
_FirstStringColumnName = customColumn.ProgramatlyName;
}
if (ID != null)
{
_ModelID = ID.ProgramatlyName;
_ModelIdDotNetType = ID.DotNetTypeAlias;
if (ID.DotNetType == typeof(int) ||
ID.DotNetType == typeof(short) ||
ID.DotNetType == typeof(long))
{
_ModelIDNullValue = "-1";
}
}
//pathes
_PathOfControllerFile = string.Format(_PathOfControllerFile, _ProgramatlyName);
_PathOfApplicatioServiceFile = string.Format(_PathOfApplicatioServiceFile, _ProgramatlyName);
_PathOfStoredProcerduresFile = string.Format(_PathOfStoredProcerduresFile, _ProgramatlyName);
_PathOfRepositoryFile = string.Format(_PathOfRepositoryFile, _ProgramatlyName);
_PathOfModelClass = string.Format(_PathOfModelClass, _ProgramatlyName);
_PathOfDtoClass = string.Format(_PathOfDtoClass, _ProgramatlyName);
_PathOfIndexView = string.Format(_PathOfIndexView, _ProgramatlyName);
_PathOfDialogBoxView = string.Format(_PathOfDialogBoxView, _ProgramatlyName);
}
