public bool Install(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var createQuery = @"
CREATE TABLE IF NOT EXISTS `Ncc_Subscription_User` (
`Id` BIGINT NOT NULL AUTO_INCREMENT ,
`VersionNumber` INT NOT NULL ,
`Metadata` varchar(250) COLLATE utf8_unicode_ci NULL,
`Name` VARCHAR(250) NULL ,
`CreationDate` DATETIME NOT NULL ,
`ModificationDate` DATETIME NOT NULL ,
`CreateBy` BIGINT NOT NULL ,
`ModifyBy` BIGINT NOT NULL ,
`Status` INT NOT NULL ,
`Email` VARCHAR(255) NOT NULL,
`Mobile` VARCHAR(255) NULL,
`Remarks` VARCHAR(1000) NULL,
PRIMARY KEY (`Id`)) ENGINE = MyISAM;
";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = createQuery
};
var retVal = executeQuery(nccDbQueryText);
if (!string.IsNullOrEmpty(retVal))
{
return(true);
}
return(false);
}
public int ExecuteSqlCommand(NccDbQueryText query)
{
var queryText = "";
if (SetupHelper.SelectedDatabase == "SqLite")
{
queryText = query.SQLite_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
queryText = query.MSSql_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
queryText = query.MySql_QueryText;
}
else
{
return(-1);
}
var effRow = Context.Database.ExecuteSqlCommand(queryText);
return(effRow);
}
public List <NccSearchViewModel> SearchLoad(int from, int total, string keyword)
{
NccDbQueryText query = new NccDbQueryText();
query.MySql_QueryText = GetBaseSearchQuery(keyword);
query.MySql_QueryText = query.MySql_QueryText + " LIMIT " + from + ", " + total;
return(ExecuteSqlQuery <NccSearchViewModel>(query));
}
public long SearchCount(string keyword)
{
NccDbQueryText query = new NccDbQueryText();
query.MySql_QueryText = GetBaseSearchQuery(keyword);
query.MySql_QueryText = "SELECT COUNT(*) Count FROM ( " + query.MySql_QueryText + " ) AS a";
return(ExecuteSqlQuery <CountViewModel>(query).FirstOrDefault().Count);
}
public bool Uninstall(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var deleteQuery = @"DROP TABLE IF EXISTS `" + GlobalContext.GetTableName <HelloModel>() + @"`;";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = deleteQuery
};
var retVal = executeQuery(nccDbQueryText);
return(string.IsNullOrEmpty(retVal) == false);
}
public bool Uninstall(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var deleteQuery = @"DROP TABLE IF EXISTS `Ncc_Hello`;";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = deleteQuery
};
var retVal = executeQuery(nccDbQueryText);
return(string.IsNullOrEmpty(retVal) == false);
}
public bool Install(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var createQuery = @"
CREATE TABLE IF NOT EXISTS `" + GlobalContext.GetTableName <NccImageSlider>() + @"` (
`Id` BIGINT NOT NULL AUTO_INCREMENT ,
`VersionNumber` INT NOT NULL ,
`Metadata` varchar(250) COLLATE utf8_unicode_ci NULL,
`Name` VARCHAR(250) NOT NULL ,
`CreationDate` DATETIME NOT NULL ,
`ModificationDate` DATETIME NOT NULL ,
`CreateBy` BIGINT NOT NULL ,
`ModifyBy` BIGINT NOT NULL ,
`Status` INT NOT NULL ,
`ContainerStyle` TEXT NULL ,
`Interval` INT NOT NULL ,
`ShowNav` BIT(1) NOT NULL ,
`ShowSideNav` BIT(1) NOT NULL ,
`ImageWidth` VARCHAR(255) NULL ,
`ImageHeight` VARCHAR(255) NULL ,
PRIMARY KEY (`Id`)) ENGINE = MyISAM;
CREATE TABLE IF NOT EXISTS `" + GlobalContext.GetTableName <NccImageSliderItem>() + @"` (
`Id` BIGINT NOT NULL AUTO_INCREMENT ,
`VersionNumber` INT NOT NULL ,
`Metadata` varchar(250) COLLATE utf8_unicode_ci NULL,
`Name` VARCHAR(250) NOT NULL ,
`CreationDate` DATETIME NOT NULL ,
`ModificationDate` DATETIME NOT NULL ,
`CreateBy` BIGINT NOT NULL ,
`ModifyBy` BIGINT NOT NULL ,
`Status` INT NOT NULL ,
`Path` VARCHAR(1000) NOT NULL ,
`Description` TEXT NOT NULL ,
`Order` INT NOT NULL ,
`NccImageSliderId` BIGINT NOT NULL ,
PRIMARY KEY (`Id`)) ENGINE = MyISAM;
";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = createQuery
};
var retVal = executeQuery(nccDbQueryText);
if (!string.IsNullOrEmpty(retVal))
{
return(true);
}
return(false);
}
public bool Install(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var createQuery = @"
CREATE TABLE IF NOT EXISTS `Ncc_Ne_Category` (
`Id` BIGINT NOT NULL AUTO_INCREMENT ,
`VersionNumber` INT NOT NULL ,
`Metadata` varchar(250) COLLATE utf8_unicode_ci NULL,
`Name` VARCHAR(250) NOT NULL ,
`CreationDate` DATETIME NOT NULL ,
`ModificationDate` DATETIME NOT NULL ,
`CreateBy` BIGINT NOT NULL ,
`ModifyBy` BIGINT NOT NULL ,
`Status` INT NOT NULL ,
PRIMARY KEY (`Id`)) ENGINE = MyISAM;
CREATE TABLE IF NOT EXISTS `Ncc_Ne_News` (
`Id` BIGINT NOT NULL AUTO_INCREMENT ,
`VersionNumber` INT NOT NULL ,
`Metadata` varchar(250) COLLATE utf8_unicode_ci NULL,
`Name` VARCHAR(250) NOT NULL ,
`CreationDate` DATETIME NOT NULL ,
`ModificationDate` DATETIME NOT NULL ,
`CreateBy` BIGINT NOT NULL ,
`ModifyBy` BIGINT NOT NULL ,
`Status` INT NOT NULL ,
`Content` longtext NULL,
`Excerpt` VARCHAR(1000) NULL,
`HasDateRange` bit(1) NOT NULL ,
`PublishDate` DATETIME NULL ,
`ExpireDate` DATETIME NULL ,
`Order` INT NOT NULL ,
PRIMARY KEY (`Id`)) ENGINE = MyISAM;
CREATE TABLE IF NOT EXISTS `Ncc_Ne_News_Category` (
`NeCategoryId` BIGINT NOT NULL ,
`NeNewsId` BIGINT NOT NULL
) ENGINE = MyISAM;
";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = createQuery
};
var retVal = executeQuery(nccDbQueryText);
if (!string.IsNullOrEmpty(retVal))
{
return(true);
}
return(false);
}
public List <TagCloudItemViewModel> LoadTagCloud()
{
NccDbQueryText query = new NccDbQueryText();
query.MySql_QueryText = @"SELECT nt.Name, COUNT(*) TotalPost
FROM ncc_tag AS nt
INNER JOIN ncc_post_tag AS npt ON npt.TagId= nt.Id
INNER JOIN ncc_post AS np ON np.Id = npt.Postid
WHERE np.PublishDate<=CURRENT_TIME()
AND np.PostStatus = " + ((int)NccPost.NccPostStatus.Published).ToString() + @"
GROUP BY nt.Id
ORDER BY nt.Name ASC ";
return(ExecuteSqlQuery <TagCloudItemViewModel>(query).ToList());
}
public ArrayList ExecuteSqlQuery(NccDbQueryText query)
{
var list = new ArrayList();
var queryText = "";
if (SetupHelper.SelectedDatabase == "SqLite")
{
queryText = query.SQLite_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
queryText = query.MSSql_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
queryText = query.MySql_QueryText;
}
else
{
throw new Exception("No supported database found.");
}
var conn = Context.Database.GetDbConnection();
try
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = queryText;
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var objArr = new object[reader.FieldCount];
var value = reader.GetValues(objArr);
list.Add(objArr);
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(list);
}
public List <TagCloudItemViewModel> LoadTagCloud()
{
//var tablePrefix = SetupHelper.GetCoreModuleTablePrefix();
NccDbQueryText query = new NccDbQueryText();
query.MySql_QueryText = $@"SELECT nt.Name, COUNT(*) TotalPost
FROM {GlobalContext.GetTableName<NccTag>()} AS nt
INNER JOIN {GlobalContext.GetTableName<NccPostTag>()} AS npt ON npt.TagId= nt.Id
INNER JOIN {GlobalContext.GetTableName<NccPost>()} AS np ON np.Id = npt.Postid
WHERE np.PublishDate<=CURRENT_TIME()
AND np.Status = {(EntityStatus.Active).ToString()}
AND np.PostStatus = {((int)NccPost.NccPostStatus.Published).ToString()}
GROUP BY nt.Id
ORDER BY nt.Name ASC ";
return(ExecuteSqlQuery <TagCloudItemViewModel>(query).ToList());
}
public bool Uninstall(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var deleteQuery = @"
DROP TABLE IF EXISTS `Ncc_Subscription_User`;
";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = deleteQuery
};
var retVal = executeQuery(nccDbQueryText);
if (!string.IsNullOrEmpty(retVal))
{
return(true);
}
return(false);
}
public List <ArchiveItemViewModel> LoadArchive(bool decendingOrder = true)
{
NccDbQueryText query = new NccDbQueryText();
query.MySql_QueryText = @"SELECT DATE_FORMAT(`PublishDate`, '%Y') `Year`, DATE_FORMAT(`PublishDate`, '%M') `Month`, CAST(DATE_FORMAT(`PublishDate`, '%m') AS UNSIGNED) `MonthValue`, COUNT(*) TotalPost
FROM `ncc_post`
WHERE `PublishDate`<=CURRENT_TIME()
AND `PostStatus` = " + ((int)NccPost.NccPostStatus.Published).ToString() + @"
GROUP BY DATE_FORMAT(`PublishDate`, '%Y'), DATE_FORMAT(`PublishDate`, '%M') ";
if (decendingOrder)
{
query.MySql_QueryText += " ORDER BY PublishDate DESC ";
}
else
{
query.MySql_QueryText += " ORDER BY PublishDate ASC ";
}
return(ExecuteSqlQuery <ArchiveItemViewModel>(query).ToList());
}
public bool Uninstall(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var deleteQuery = @"
DROP TABLE IF EXISTS `" + GlobalContext.GetTableName <NccImageSliderItem>() + @"`;
DROP TABLE IF EXISTS `" + GlobalContext.GetTableName <NccImageSlider>() + @"`
;";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = deleteQuery
};
var retVal = executeQuery(nccDbQueryText);
if (!string.IsNullOrEmpty(retVal))
{
return(true);
}
return(false);
}
/// <summary>
/// Delete database table using Entity Model
/// </summary>
/// <param name="modelType"></param>
/// <returns></returns>
public int DeleteTable(Type modelType)
{
NccDbQueryText query = new NccDbQueryText();
if (SetupHelper.SelectedDatabase == "SqLite")
{
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
query.MySql_QueryText = "DROP TABLE IF EXISTS `" + GlobalContext.GetTableName(modelType) + @"`;";
}
else
{
throw new System.Exception("No supported database found.");
}
return(_entityRepository.ExecuteSqlCommand(query)); //from base repository
}
/// <summary>
/// Execure a raw SQL query
/// </summary>
/// <param name="query">Raw SQL query</param>
/// <returns></returns>
public string ExecuteQuery(NccDbQueryText query)
{
string retVal = "";
using (var txn = _entityRepository.BeginTransaction())
{
try
{
var ret = _entityRepository.ExecuteSqlCommand(query);
retVal = ret.ToString();
txn.Commit();
}
catch (Exception ex)
{
txn.Rollback();
}
}
return(retVal);
}
public List <ArchiveItemViewModel> LoadArchive(bool decendingOrder = true)
{
//var tablePrefix = SetupHelper.GetCoreModuleTablePrefix();
NccDbQueryText query = new NccDbQueryText();
query.MySql_QueryText = $@"SELECT DATE_FORMAT(`PublishDate`, '%Y') `Year`, DATE_FORMAT(`PublishDate`, '%M') `Month`, CAST(DATE_FORMAT(`PublishDate`, '%m') AS UNSIGNED) `MonthValue`, COUNT(*) TotalPost
FROM `{GlobalContext.GetTableName<NccPost>()}`
WHERE `PublishDate`<=CURRENT_TIME()
AND `Status` = {(EntityStatus.Active).ToString()}
AND `PostStatus` = {((int)NccPost.NccPostStatus.Published).ToString()}
GROUP BY DATE_FORMAT(`PublishDate`, '%Y'), DATE_FORMAT(`PublishDate`, '%M') ";
if (decendingOrder)
{
query.MySql_QueryText += " ORDER BY PublishDate DESC ";
}
else
{
query.MySql_QueryText += " ORDER BY PublishDate ASC ";
}
return(ExecuteSqlQuery <ArchiveItemViewModel>(query).ToList());
}
/// <summary>
/// Get table column list
/// </summary>
/// <param name="modelType">Entity Type [typeof(ModelT)]</param>
/// <returns></returns>
public List <DbTableViewModel> GetTableColumns(Type modelType)
{
NccDbQueryText query = new NccDbQueryText();
#region FindDatabaseName
string databaseName = "";
var connectionParameters = SetupHelper.ConnectionString.Split(";");
foreach (var item in connectionParameters)
{
if (item.StartsWith("database"))
{
databaseName = item.Split("=")[1];
}
}
if (databaseName.Trim() == "")
{
throw new Exception("Database Name not found.");
}
#endregion
string tableName = GlobalContext.GetTableName(modelType);
if (SetupHelper.SelectedDatabase == "SqLite")
{
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
query.MySql_QueryText = $"SELECT table_schema `Database`, table_name TableName, column_name ColumnName, column_type ColumnType, column_key ColumnKey, extra `Extra`, column_comment ColumnComment FROM information_schema.columns WHERE table_schema = '{databaseName}' AND table_name = '{tableName}'";
}
else
{
throw new Exception("No supported database found.");
}
return(_entityRepository.ExecuteSqlQuery <DbTableViewModel>(query));
}
public bool Uninstall(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var deleteQuery = @"DELETE FROM ncc_page_details;
ALTER TABLE ncc_page_details AUTO_INCREMENT = 1;
DELETE FROM ncc_page;
ALTER TABLE ncc_page AUTO_INCREMENT = 1;
DELETE FROM ncc_category_details;
ALTER TABLE ncc_category_details AUTO_INCREMENT = 1;
DELETE FROM ncc_post_category;
DELETE FROM ncc_category;
ALTER TABLE ncc_category AUTO_INCREMENT = 1;
DELETE FROM ncc_post_tag;
DELETE FROM ncc_tag;
ALTER TABLE ncc_tag AUTO_INCREMENT = 1;
DELETE FROM ncc_post_details;
ALTER TABLE ncc_post_details AUTO_INCREMENT = 1;
DELETE FROM ncc_post;
ALTER TABLE ncc_post AUTO_INCREMENT = 1;
DELETE FROM ncc_menu_item WHERE Metadata = 'DemoData';
DELETE FROM ncc_menu WHERE Metadata = 'DemoData';
";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = deleteQuery
};
var retVal = executeQuery(nccDbQueryText);
if (!string.IsNullOrEmpty(retVal))
{
return(true);
}
return(false);
}
public bool Install(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
var createQuery = @"
CREATE TABLE IF NOT EXISTS `" + GlobalContext.GetTableName <HelloModel>() + @"` (
`Id` BIGINT NOT NULL AUTO_INCREMENT ,
`VersionNumber` INT NOT NULL ,
`Metadata` varchar(250) COLLATE utf8_unicode_ci NULL,
`Name` VARCHAR(250) NOT NULL ,
`CreationDate` DATETIME NOT NULL ,
`ModificationDate` DATETIME NOT NULL ,
`CreateBy` BIGINT NOT NULL ,
`ModifyBy` BIGINT NOT NULL ,
`Status` INT NOT NULL ,
`Hello` TEXT NULL ,
PRIMARY KEY (`Id`)) ENGINE = MyISAM;";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = createQuery
};
var retVal = executeQuery(nccDbQueryText);
return(string.IsNullOrEmpty(retVal) == false);
}
/// <summary>
/// Update Database table using Entity Model
/// </summary>
/// <param name="modelType">Entity Type [typeof(ModelT)]</param>
/// <returns></returns>
public int UpdateTable(Type modelType, bool DeleteUnusedColumns = true)
{
NccDbQueryText query = new NccDbQueryText();
string currentTableName = GlobalContext.GetTableName(modelType);
string assemblyname = modelType.Assembly.GetName().Name;
List <DbTableViewModel> currentColumnList = GetTableColumns(modelType);
if (currentColumnList.Count <= 0)
{
throw new Exception($"{currentTableName} Table does not exists.");
}
if (currentColumnList.FirstOrDefault().ColumnComment != assemblyname)
{
throw new Exception($"{currentTableName} Table already exists in module {currentColumnList.FirstOrDefault().ColumnComment}.");
}
List <System.Reflection.PropertyInfo> unchangedCloumnList = new List <System.Reflection.PropertyInfo>();
List <System.Reflection.PropertyInfo> newCloumnList = new List <System.Reflection.PropertyInfo>();
var entityProperties = modelType.GetProperties().OrderBy(x => x.Name).ToArray();
var deletedColumnList = currentColumnList;
foreach (var item in entityProperties)
{
if (currentColumnList.Where(x => x.ColumnName == item.Name).Count() > 0)
{
unchangedCloumnList.Add(item);
deletedColumnList = deletedColumnList.Where(x => x.ColumnName != item.Name).ToList();
}
else if (currentColumnList.Where(x => string.IsNullOrEmpty(x.ColumnKey) == false && x.ColumnName == string.Concat(item.Name, "Id")).Count() > 0)
{
unchangedCloumnList.Add(item);
deletedColumnList = deletedColumnList.Where(x => string.IsNullOrEmpty(x.ColumnKey) == true && x.ColumnName != string.Concat(item.Name, "Id")).ToList();
}
else
{
newCloumnList.Add(item);
}
}
if (SetupHelper.SelectedDatabase == "SqLite")
{
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
List <string> columnList = new List <string>();
string qIndexkey = "";
string qForeginkey = "";
foreach (var item in newCloumnList)
{
var prop = item;
var propInfo = modelType.GetProperty(prop.Name);
//If property has NotMapped Attribute then skip column.
if (propInfo.GetCustomAttributes(true).Where(x => x is NotMappedAttribute).Count() > 0)
{
continue;
}
if (propInfo.PropertyType.Name.StartsWith("List"))
{
continue;
}
//If column already added then skip
if (columnList.Where(x => x == prop.Name).Count() > 0)
{
continue;
}
if (prop.PropertyType == typeof(int) || prop.PropertyType == typeof(Int16))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` INT AUTO_INCREMENT PRIMARY KEY COMMENT '{assemblyname}';";
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` INT NOT NULL COMMENT '{assemblyname}';";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(long))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '{assemblyname}';";
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` BIGINT NOT NULL COMMENT '{assemblyname}';";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(float))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` float PRIMARY KEY COMMENT '{assemblyname}';";
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` float DEFAULT '0' COMMENT '{assemblyname}';";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(double))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` double PRIMARY KEY COMMENT '{assemblyname}';";
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` double DEFAULT '0' COMMENT '{assemblyname}';";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(decimal))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` decimal(10,2) DEFAULT '0.00' PRIMARY KEY COMMENT '{assemblyname}';";
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` decimal(10,2) DEFAULT '0.00' COMMENT '{assemblyname}';";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(string))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
if (propInfo.GetCustomAttributes(true).Where(x => x is MaxLengthAttribute).Count() > 0)
{
var temp = (MaxLengthAttribute)propInfo.GetCustomAttributes(true).Where(x => x is MaxLengthAttribute).FirstOrDefault();
long length = temp.Length;
if (length > 4000)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` longtext COLLATE utf8_unicode_ci NOT NULL PRIMARY KEY COMMENT '{assemblyname}';";
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` VARCHAR({temp.Length}) COLLATE utf8_unicode_ci NOT NULL PRIMARY KEY COMMENT '{assemblyname}';";
}
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` VARCHAR(1024) COLLATE utf8_unicode_ci NOT NULL PRIMARY KEY COMMENT '{assemblyname}';";
}
}
else
{
if (propInfo.GetCustomAttributes(true).Where(x => x is MaxLengthAttribute).Count() > 0)
{
var temp = (MaxLengthAttribute)propInfo.GetCustomAttributes(true).Where(x => x is MaxLengthAttribute).FirstOrDefault();
long length = temp.Length;
if (length > 4000)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` longtext COLLATE utf8_unicode_ci NULL COMMENT '{assemblyname}';";
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` VARCHAR({temp.Length}) COLLATE utf8_unicode_ci NULL COMMENT '{assemblyname}';";
}
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` VARCHAR(1024) COLLATE utf8_unicode_ci NULL COMMENT '{assemblyname}';";
}
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(bool))
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` bit(1) NOT NULL COMMENT '{assemblyname}';";
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(DateTime))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` datetime(6) NOT NULL PRIMARY KEY COMMENT '{assemblyname}';";
}
else
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` datetime(6) NULL COMMENT '{assemblyname}';";
}
columnList.Add(prop.Name);
}
else
{
if (prop.PropertyType.BaseType == typeof(Enum))
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` ADD `{prop.Name}` INT NOT NULL COMMENT '{assemblyname}';";
columnList.Add(prop.Name);
}
else
{
if (columnList.Where(x => x == string.Concat(prop.Name, "Id")).Count() <= 0)
{
var propTypeTablename = GlobalContext.GetTableName(prop.PropertyType);
var tempIndexKeyName = $"IX_{propTypeTablename}_{prop.Name}Id";
if (tempIndexKeyName.Length > 60)
{
tempIndexKeyName = tempIndexKeyName.Replace("_", "");
}
if (tempIndexKeyName.Length > 60)
{
tempIndexKeyName = $"IX_{Guid.NewGuid().ToString()}";
}
if (tempIndexKeyName.Length > 60)
{
tempIndexKeyName = tempIndexKeyName.Substring(0, 60);
}
var temp = "";
var tempi = 0;
while (true)
{
if (qIndexkey.Contains(tempIndexKeyName + temp) == false)
{
tempIndexKeyName = tempIndexKeyName + temp;
break;
}
tempi++;
temp = "_" + tempi.ToString();
}
var tempForeginKeyName = $"FK_{currentTableName}_{propTypeTablename}_{prop.Name}Id";
if (tempForeginKeyName.Length > 60)
{
tempForeginKeyName = tempForeginKeyName.Replace("_", "");
}
if (tempForeginKeyName.Length > 60)
{
tempForeginKeyName = $"FK_{Guid.NewGuid().ToString()}";
}
if (tempForeginKeyName.Length > 60)
{
tempForeginKeyName = tempForeginKeyName.Substring(0, 60);
}
temp = "";
tempi = 0;
while (true)
{
if (qForeginkey.Contains(tempForeginKeyName + temp) == false)
{
tempForeginKeyName = tempForeginKeyName + temp;
break;
}
tempi++;
temp = "_" + tempi.ToString();
}
query.MySql_QueryText += $"ALTER TABLE `{currentTableName}` {Environment.NewLine}ADD COLUMN `{prop.Name}Id` BIGINT NULL COMMENT '{assemblyname}', {Environment.NewLine}ADD INDEX (`{prop.Name}Id`) {Environment.NewLine}ADD CONSTRAINT `{tempForeginKeyName}` FOREIGN KEY(`{prop.Name}Id`) {Environment.NewLine}REFERENCES `{propTypeTablename}`(`Id`); ";
columnList.Add(string.Concat(prop.Name, "Id"));
}
}
}
}
if (DeleteUnusedColumns)
{
foreach (var item in deletedColumnList)
{
query.MySql_QueryText += $"{Environment.NewLine}ALTER TABLE `{currentTableName}` DROP COLUMN `{item.ColumnName}`;";
}
}
}
else
{
throw new System.Exception("No supported database found.");
}
return(_entityRepository.ExecuteSqlCommand(query)); //from base repository
}
public ArrayList ExecuteSqlQuery(NccDbQueryText query, int timeout = 60, CommandType commandType = CommandType.Text)
{
var list = new ArrayList();
var isNewConnection = false;
var queryText = "";
if (SetupHelper.SelectedDatabase == "SqLite")
{
queryText = query.SQLite_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
queryText = query.MSSql_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
queryText = query.MySql_QueryText;
}
else
{
throw new Exception("No supported database found.");
}
var conn = Context.Database.GetDbConnection();
try
{
if (conn.State == ConnectionState.Closed)
{
conn.OpenAsync().Wait();
isNewConnection = true;
}
using (var command = conn.CreateCommand())
{
command.CommandTimeout = timeout;
command.CommandType = commandType;
command.CommandText = queryText;
using (DbDataReader reader = command.ExecuteReader())
{
try
{
while (reader.Read())
{
var objArr = new object[reader.FieldCount];
var value = reader.GetValues(objArr);
list.Add(objArr);
}
}
catch (Exception ex)
{
reader.Close();
throw ex;
}
reader.Close();
}
}
if (isNewConnection && conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (Exception ex)
{
if (isNewConnection && conn.State != ConnectionState.Closed)
{
conn.Close();
}
throw ex;
}
return(list);
}
public List <T> ExecuteSqlQuery <T>(NccDbQueryText query)
{
var entityType = typeof(T);
var list = new List <T>();
var queryText = "";
if (SetupHelper.SelectedDatabase == "SqLite")
{
queryText = query.SQLite_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
queryText = query.MSSql_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
queryText = query.MySql_QueryText;
}
else
{
throw new System.Exception("No supported database found.");
}
var conn = Context.Database.GetDbConnection();
try
{
var entityProperties = entityType.GetProperties();
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = queryText;
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var objArr = new object[reader.FieldCount];
var value = reader.GetValues(objArr);
var obj = Activator.CreateInstance <T>();
for (int i = 0; i < entityProperties.Length; i++)
{
var prop = entityProperties[i];
var ordinal = reader.GetOrdinal(prop.Name);
if (ordinal >= 0)
{
if (prop.PropertyType == typeof(Int16))
{
var val = reader.GetInt16(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(int))
{
var val = reader.GetInt32(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(long))
{
var val = reader.GetInt64(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(string))
{
var val = reader.GetString(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(float))
{
var val = reader.GetFloat(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(double))
{
var val = reader.GetDouble(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(decimal))
{
var val = reader.GetDecimal(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(bool))
{
var val = reader.GetBoolean(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(DateTime))
{
var val = reader.GetDateTime(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(double))
{
var val = reader.GetDouble(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
}
}
list.Add(obj);
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(list);
}
public List <T> ExecuteSqlQuery <T>(NccDbQueryText query, int timeout = 60, CommandType commandType = CommandType.Text)
{
var isNewConnection = false;
var entityType = typeof(T);
var list = new List <T>();
var queryText = "";
if (SetupHelper.SelectedDatabase == "SqLite")
{
queryText = query.SQLite_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
queryText = query.MSSql_QueryText;
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
queryText = query.MySql_QueryText;
}
else
{
throw new Exception("No supported database found.");
}
var conn = Context.Database.GetDbConnection();
try
{
var entityProperties = entityType.GetProperties();
if (conn.State == ConnectionState.Closed)
{
conn.OpenAsync().Wait();
isNewConnection = true;
}
using (var command = conn.CreateCommand())
{
command.CommandTimeout = timeout;
command.CommandType = commandType;
command.CommandText = queryText;
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
var objArr = new object[reader.FieldCount];
var value = reader.GetValues(objArr);
var obj = Activator.CreateInstance <T>();
for (int i = 0; i < entityProperties.Length; i++)
{
var prop = entityProperties[i];
var ordinal = reader.GetOrdinal(prop.Name);
if (ordinal >= 0)
{
if (prop.PropertyType == typeof(Int16))
{
var val = reader.GetInt16(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(int))
{
var val = reader.GetInt32(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(long))
{
var val = reader.GetInt64(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(string))
{
var val = reader.GetString(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(float))
{
var val = reader.GetFloat(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(double))
{
var val = reader.GetDouble(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(decimal))
{
var val = reader.GetDecimal(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(bool))
{
var val = reader.GetBoolean(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(DateTime))
{
var val = reader.GetDateTime(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
else if (prop.PropertyType == typeof(double))
{
var val = reader.GetDouble(ordinal);
var propInfo = entityType.GetProperty(prop.Name);
propInfo.SetValue(obj, val);
}
}
}
list.Add(obj);
}
catch (Exception ex)
{
reader.Close();
throw ex;
}
}
reader.Close();
}
}
if (isNewConnection && conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (Exception ex)
{
if (isNewConnection && conn.State != ConnectionState.Closed)
{
conn.Close();
}
throw ex;
}
return(list);
}
public string ExecuteQuery(NccDbQueryText query)
{
var retVal = _entityRepository.ExecuteSqlCommand(query);
return(retVal.ToString());
}
public bool Install(NccSettingsService settingsService, Func <NccDbQueryText, string> executeQuery)
{
int totalPageCreation = 10;
int totalPostCreation = 500;
#region Page Initialization
string demoTitleEn = "Test Page ";
string demoSlugEn = "Test-Page-";
string demoTitleBn = "টেস্ট পৃষ্ঠা ";
string demoSlugBn = "টেস্ট-পৃষ্ঠা-";
string pagePreQuery = "insert into `ncc_page`(`CreateBy`,`CreationDate`,`Layout`,`ModificationDate`,`ModifyBy`,`Name`,`PageOrder`,`PageStatus`,`PageType`,`ParentId`,`PublishDate`,`Status`,`VersionNumber`) values ";
string pageDetailsPreQuery = "insert into `ncc_page_details`(`Content`,`CreateBy`,`CreationDate`,`Language`,`MetaDescription`,`MetaKeyword`,`ModificationDate`,`ModifyBy`,`Name`,`PageId`,`Slug`,`Status`,`Title`,`VersionNumber`) values ";
#endregion
#region Category Initialization
string categoryTitleEn = "Test Category ";
string categorySlugEn = "Test-Category-";
string categoryTitleBn = "টেস্ট ক্যাটাগরি ";
string categorySlugBn = "টেস্ট-ক্যাটাগরি-";
string categoryPreQuery = "insert into `ncc_category`(`CategoryImage`,`CreateBy`,`CreationDate`,`ModificationDate`,`ModifyBy`,`Name`,`ParentId`,`Status`,`VersionNumber`) values ";
string categoryDetailsPreQuery = "insert into `ncc_category_details`(`CategoryId`,`CreateBy`,`CreationDate`,`Language`,`MetaDescription`,`MetaKeyword`,`ModificationDate`,`ModifyBy`,`Name`,`Slug`,`Status`,`Title`,`VersionNumber`) values ";
#endregion
#region Tag Initialization
string tagTitle = "Test Tag ";
string tagPreQuery = "insert into `ncc_tag`(`CreateBy`,`CreationDate`,`ModificationDate`,`ModifyBy`,`Name`,`Status`,`VersionNumber`) values ";
#endregion
#region Blog Post Initialization
string postTitleEn = "Test Post ";
string postSlugEn = "Test-Post-";
string postTitleBn = "টেস্ট পোস্ট ";
string postSlugBn = "টেস্ট-পোস্ট-";
string postPreQuery = "insert into `ncc_post`(`AllowComment`,`AuthorId`,`CreateBy`,`CreationDate`,`IsFeatured`,`IsStiky`,`Layout`,`ModificationDate`,`ModifyBy`,`Name`,`ParentId`,`PostStatus`,`PostType`,`PublishDate`,`RelatedPosts`,`Status`,`ThumImage`,`VersionNumber`) values ";
string postDetailsPreQuery = "insert into `ncc_post_details`(`Content`,`CreateBy`,`CreationDate`,`Language`,`MetaDescription`,`MetaKeyword`,`ModificationDate`,`ModifyBy`,`Name`,`PostId`,`Slug`,`Status`,`Title`,`VersionNumber`) values ";
#endregion
#region Post_Category & Post_Tag Initialization
string postCategoryPreQuery = "insert into `ncc_post_category`(`PostId`,`CategoryId`) values ";
string postTagPreQuery = "insert into `ncc_post_tag`(`PostId`,`TagId`) values ";
#endregion
#region QueryGeneration
#region Region
string pageQuery = "";
string pageDetailsQuery = "";
string pageName = "Test-Page-";
string categoryQuery = "";
string categoryDetailsQuery = "";
string categoryName = "Test-Category-";
string tagQuery = "";
string postQuery = "";
string postDetailsQuery = "";
string postName = "Test-Post-";
string postCategoryQuery = "";
string postTagQuery = "";
#endregion
if (GlobalConfig.WebSite.Language == "bn")
{
pageName = "টেস্ট-পৃষ্ঠা-";
categoryName = "টেস্ট-ক্যাটাগরি-";
tagTitle = "টেস্ট ট্যাগ ";
postName = "টেস্ট-পোস্ট-";
}
for (int i = 1; i <= totalPageCreation; i++)
{
if (i == 1)
{
#region Page
pageQuery += string.Format("(1,{0},'SiteLayout',{0},1,'{1}',0,2,0,NULL,{0},0,1)", "CURRENT_DATE", pageName + i.ToString());
pageDetailsQuery += string.Format("('<div><h1 style=\"text-align:center\">{1}</h1><hr /><h2>লরমেম ইপ্সাম কি?</h2><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div><div><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div><div><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div>', 1, {0}, 'bn', '{1}', '{1}',{0},1,'{1}',{5},'{2}',0,'{1}',1), ('<div><h1 style=\"text-align:center\">{3}</h1><hr /><h2>What is Lorem Ipsum?</h2><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div><div><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div><div><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div>', 1, {0}, 'en', '{3}', '{3}', {0}, 1, '{3}', {5},'{4}',0,'{3}',1)"
, "CURRENT_DATE"
, demoTitleBn + i.ToString()
, demoSlugBn + i.ToString()
, demoTitleEn + i.ToString()
, demoSlugEn + i.ToString()
, i.ToString());
#endregion
#region Category
categoryQuery += string.Format("('/media/Images/2017/06/image-slider-0.jpg',1,{0},{0},1,'{1}',NULL,0,1)", "CURRENT_DATE", categoryName + i.ToString());
categoryDetailsQuery += string.Format("({5},1,{0},'bn',NULL,NULL,{0},1,'{1}','{2}',0,'{1}',1), ({5}, 1, {0}, 'en', NULL, NULL, {0}, 1, '{3}', '{4}', 0, '{3}', 1)"
, "CURRENT_DATE"
, categoryTitleBn + i.ToString()
, categorySlugBn + i.ToString()
, categoryTitleEn + i.ToString()
, categorySlugEn + i.ToString()
, i.ToString());
#endregion
tagQuery += string.Format("(1,'{0}','{0}',1,'{1}',0,1)", "2017-10-01 00:00:00.000000", tagTitle + i.ToString());
}
else
{
#region Page
pageQuery += string.Format(", (1,{0},'SiteLayout',{0},1,'{1}',0,2,0,NULL,{0},0,1)", "CURRENT_DATE", pageName + i.ToString());
pageDetailsQuery += string.Format(", ('<div><h1 style=\"text-align:center\">{1}</h1><hr /><h2>লরমেম ইপ্সাম কি?</h2><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div><div><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div><div><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div>', 1, {0}, 'bn', '{1}', '{1}',{0},1,'{1}',{5},'{2}',0,'{1}',1), ('<div><h1 style=\"text-align:center\">{3}</h1><hr /><h2>What is Lorem Ipsum?</h2><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div><div><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div><div><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div>', 1, {0}, 'en', '{3}', '{3}', {0}, 1, '{3}', {5},'{4}',0,'{3}',1)"
, "CURRENT_DATE"
, demoTitleBn + i.ToString()
, demoSlugBn + i.ToString()
, demoTitleEn + i.ToString()
, demoSlugEn + i.ToString()
, i.ToString());
#endregion
#region Category
categoryQuery += string.Format(", ('/media/Images/2017/06/image-slider-{2}.jpg',1,{0},{0},1,'{1}',NULL,0,1)", "CURRENT_DATE", categoryName + i.ToString(), (i % 4).ToString());
categoryDetailsQuery += string.Format(", ({5},1,{0},'bn',NULL,NULL,{0},1,'{1}','{2}',0,'{1}',1), ({5}, 1, {0}, 'en', NULL, NULL, {0}, 1, '{3}', '{4}', 0, '{3}', 1)"
, "CURRENT_DATE"
, categoryTitleBn + i.ToString()
, categorySlugBn + i.ToString()
, categoryTitleEn + i.ToString()
, categorySlugEn + i.ToString()
, i.ToString());
#endregion
tagQuery += string.Format(", (1,{0},{0},1,'{1}',0,1)", "CURRENT_DATE", tagTitle + i.ToString());
}
}
for (int i = 1; i <= totalPostCreation; i++)
{
if (i == 1)
{
#region Post
postQuery += string.Format("(TRUE,1,1,{0},FALSE,FALSE,'SiteLayout',{0},1,'{1}',NULL,2,0,{0},NULL,0,'/media/Images/2017/06/image-slider-{2}.jpg',1)", "CURRENT_DATE", postName + i.ToString(), (i % 4).ToString());
postDetailsQuery += string.Format("('<div><h1 style=\"text-align:center\">{1}</h1><hr /><h2>লরমেম ইপ্সাম কি?</h2><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div><div><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div><div><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div>',1,{0},'bn','{1}','{1}',{0},1,NULL,{5},'{2}',0,'{1}',1), ('<div><h1 style=\"text-align:center\">{3}</h1><hr /><h2>What is Lorem Ipsum?</h2><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div><div><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div><div><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div>',1,{0},'en','{3}','{3}',{0},1,NULL,{5},'{4}',0,'{3}',1)"
, "CURRENT_DATE"
, postTitleBn + i.ToString()
, postSlugBn + i.ToString()
, postTitleEn + i.ToString()
, postSlugEn + i.ToString()
, i.ToString());
#endregion
postCategoryQuery += string.Format("({0},{1})", i.ToString(), ((i % 9) + 1).ToString());
postTagQuery += string.Format("({0},{1})", i.ToString(), ((i % 9) + 1).ToString());
}
else
{
#region Post
postQuery += string.Format(", (TRUE,1,1,{0},FALSE,FALSE,'SiteLayout',{0},1,'{1}',NULL,2,0,{0},NULL,0,'/media/Images/2017/06/image-slider-{2}.jpg',1)", "CURRENT_DATE", postName + i.ToString(), (i % 4).ToString());
postDetailsQuery += string.Format(", ('<div><h1 style=\"text-align:center\">{1}</h1><hr /><h2>লরমেম ইপ্সাম কি?</h2><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div><div><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div><div><p><strong>Lorem Ipsum</strong> শুধু মুদ্রণ এবং typesetting শিল্পের ডামি টেক্সট। 1500 এর দশক থেকে লরমেম ইপসাম শিল্পের আদর্শ ডামি টেক্সট হয়ে উঠেছে, যখন একটি অজানা মুদ্রণযন্ত্রটি একটি গলি টাইপ করে এবং এটি একটি টাইপ নমুনা বই তৈরি করতে scrambled। এটা শুধুমাত্র পাঁচ শতাব্দী ধরেই বেঁচে গেছে, কিন্তু ইলেকট্রনিক টাইপসেটিংয়ের মধ্যেও লাফানো, মূলত অপরিবর্তিত রয়েছে। এটি লরমেম ইপ্সাম প্যাসেজগুলির সাথে লেটাসেট পত্রকের মুক্তির সাথে 1960 সালে জনপ্রিয়তা লাভ করে এবং সম্প্রতি ডেস্কটপ পাবলিশিং সফটওয়্যারের মত অ্যালডাস প্যাটারমেকার যেমন লরমেম ইপসামের সংস্করণ সহ।</p></div>',1,{0},'bn','{1}','{1}',{0},1,NULL,{5},'{2}',0,'{1}',1), ('<div><h1 style=\"text-align:center\">{3}</h1><hr /><h2>What is Lorem Ipsum?</h2><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div><div><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div><div><p><strong>Lorem Ipsum</strong> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industries standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p></div>',1,{0},'en','{3}','{3}',{0},1,NULL,{5},'{4}',0,'{3}',1)"
, "CURRENT_DATE"
, postTitleBn + i.ToString()
, postSlugBn + i.ToString()
, postTitleEn + i.ToString()
, postSlugEn + i.ToString()
, i.ToString());
#endregion
postCategoryQuery += string.Format(", ({0},{1})", i.ToString(), ((i % 9) + 1).ToString());
postTagQuery += string.Format(", ({0},{1})", i.ToString(), ((i % 9) + 1).ToString());
}
}
#endregion
#region Test Menu Generation
string menuQuery = @"INSERT INTO `ncc_menu`(`CreateBy`,`CreationDate`,`MenuIconCls`,`MenuLanguage`,`MenuOrder`,`Metadata`,`ModificationDate`,`ModifyBy`,`Name`,`Position`,`Status`,`VersionNumber`) VALUES (1, '2017-10-04 09:35:59.374356', NULL, '', 1, 'DemoData', '2017-10-04 09:35:59.374356', 1, 'DemoData Menu', 'Navigation', 0, 1);
INSERT INTO `ncc_menu_item`(`Action`,`Controller`,`CreateBy`,`CreationDate`,`Data`,`MenuActionType`,`MenuFor`,`MenuIconCls`,`MenuOrder`,`Metadata`,`ModificationDate`,`ModifyBy`,`Module`,`Name`,`NccMenuId`,`NccMenuItemId`,`NccMenuItemId1`,`ParentId`,`Position`,`Status`,`Target`,`Url`,`VersionNumber`) VALUES ('Index', 'Home', 1, CURRENT_DATE, '', 0, 0, NULL, 1, 'DemoData', CURRENT_DATE, 1, '', 'Home', (SELECT MAX(Id) FROM ncc_menu WHERE MetaData = 'DemoData'),NULL,NULL,NULL,0,0,'','/Home/Index',1), ('', '', 1, CURRENT_DATE, '/Post', 4, 0, NULL, 2, 'DemoData', CURRENT_DATE, 1, '', 'Blog Posts', (SELECT MAX(Id) FROM ncc_menu WHERE MetaData = 'DemoData'),NULL,NULL,NULL,0,0,'_self','/Post',1), ('', '', 1, CURRENT_DATE, '/Category', 4, 0, NULL, 3, 'DemoData', CURRENT_DATE, 1, '', 'Blog Categories', (SELECT MAX(Id) FROM ncc_menu WHERE MetaData = 'DemoData'),NULL,NULL,NULL,0,0,'_self','/Category',1), ('', '', 1, CURRENT_DATE, '/Tags', 4, 0, NULL, 4, 'DemoData', CURRENT_DATE, 1, '', 'Post Tags', (SELECT MAX(Id) FROM ncc_menu WHERE MetaData = 'DemoData'),NULL,NULL,NULL,0,0,'_self','/Tags',1); ";
#endregion
var finalQuery = @"";
finalQuery = pagePreQuery + pageQuery + "; " + pageDetailsPreQuery + pageDetailsQuery + ";"
+ categoryPreQuery + categoryQuery + "; " + categoryDetailsPreQuery + categoryDetailsQuery + ";"
+ tagPreQuery + tagQuery + "; "
+ postPreQuery + postQuery + "; " + postDetailsPreQuery + postDetailsQuery + ";"
+ postCategoryPreQuery + postCategoryQuery + "; " + postTagPreQuery + postTagQuery + ";"
+ menuQuery + " ";
var nccDbQueryText = new NccDbQueryText()
{
MySql_QueryText = finalQuery
};
var retVal = executeQuery(nccDbQueryText);
if (!string.IsNullOrEmpty(retVal))
{
return(true);
}
return(false);
}
private string ExecuteQuery(NccDbQueryText query)
{
return(_moduleService.ExecuteQuery(query));
}
/// <summary>
/// Generate Database table using Entity Model
/// </summary>
/// <param name="modelType">Entity Type [typeof(ModelT)]</param>
/// <returns></returns>
public int CreateOrUpdateTable(Type modelType)
{
NccDbQueryText query = new NccDbQueryText();
string currentTableName = GlobalContext.GetTableName(modelType);
string assemblyname = modelType.Assembly.GetName().Name;
var currentColumnList = GetTableColumns(modelType);
if (currentColumnList.Count > 0)
{
if (currentColumnList.FirstOrDefault().ColumnComment == assemblyname)
{
return(UpdateTable(modelType, false));
}
throw new Exception($"{currentTableName} Table already exists in module {currentColumnList.FirstOrDefault().ColumnComment}.");
}
if (SetupHelper.SelectedDatabase == "SqLite")
{
}
else if (SetupHelper.SelectedDatabase == "MSSQL")
{
}
else if (SetupHelper.SelectedDatabase == "MySql")
{
bool primaryFound = false;
List <string> columnList = new List <string>();
string qStart = $"CREATE TABLE `{currentTableName}` ( ";//IF NOT EXISTS
string qPrimaryColumn = "";
string qBody = "";
string qPrimarykey = "";
string qIndexkey = "";
string qForeginkey = "";
string qEnd = ") ENGINE = InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_unicode_ci;";
var entityProperties = modelType.GetProperties().OrderBy(x => x.Name).ToArray();
for (int i = 0; i < entityProperties.Length; i++)
{
var prop = entityProperties[i];
var propInfo = modelType.GetProperty(prop.Name);
//If property has NotMapped Attribute then skip column.
if (propInfo.GetCustomAttributes(true).Where(x => x is NotMappedAttribute).Count() > 0)
{
continue;
}
if (propInfo.PropertyType.Name.StartsWith("List"))
{
continue;
}
//If column already added then skip
if (columnList.Where(x => x == prop.Name).Count() > 0)
{
continue;
}
if (qBody.Trim() != "" && qBody.Trim().EndsWith(",") == false)
{
qBody += ", ";
}
if (prop.PropertyType == typeof(int) || prop.PropertyType == typeof(Int16))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
qPrimaryColumn = $"{Environment.NewLine} `{prop.Name}` INT NOT NULL AUTO_INCREMENT COMMENT '{assemblyname}', ";
qPrimarykey += (qPrimarykey == "") ? $"`{prop.Name}`" : $", `{prop.Name}`";
primaryFound = true;
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` INT NOT NULL COMMENT '{assemblyname}' ";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(long))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
qPrimaryColumn = $"{Environment.NewLine} `{prop.Name}` BIGINT NOT NULL AUTO_INCREMENT COMMENT '{assemblyname}', ";
qPrimarykey += (qPrimarykey == "") ? $"`{prop.Name}`" : $", `{prop.Name}`";
primaryFound = true;
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` BIGINT NOT NULL COMMENT '{assemblyname}' ";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(float))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
qPrimaryColumn = $"{Environment.NewLine} `{prop.Name}` float DEFAULT '0' COMMENT '{assemblyname}', ";
qPrimarykey += (qPrimarykey == "") ? $"`{prop.Name}`" : $", `{prop.Name}`";
primaryFound = true;
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` float DEFAULT '0' COMMENT '{assemblyname}' ";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(double))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
qPrimaryColumn = $"{Environment.NewLine} `{prop.Name}` double DEFAULT '0' COMMENT '{assemblyname}', ";
qPrimarykey += (qPrimarykey == "") ? $"`{prop.Name}`" : $", `{prop.Name}`";
primaryFound = true;
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` double DEFAULT '0' COMMENT '{assemblyname}' ";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(decimal))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
qPrimaryColumn = $"{Environment.NewLine} `{prop.Name}` decimal(10,2) DEFAULT '0.00' COMMENT '{assemblyname}', ";
qPrimarykey += (qPrimarykey == "") ? $"`{prop.Name}`" : $", `{prop.Name}`";
primaryFound = true;
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` decimal(10,2) DEFAULT '0.00' COMMENT '{assemblyname}' ";
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(string))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
if (propInfo.GetCustomAttributes(true).Where(x => x is MaxLengthAttribute).Count() > 0)
{
var temp = (MaxLengthAttribute)propInfo.GetCustomAttributes(true).Where(x => x is MaxLengthAttribute).FirstOrDefault();
long length = temp.Length;
if (length > 4000)
{
qPrimaryColumn = $"{Environment.NewLine} `{prop.Name}` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '{assemblyname}', ";
}
else
{
qPrimaryColumn = $"{Environment.NewLine} `{prop.Name}` VARCHAR({temp.Length}) COLLATE utf8_unicode_ci NOT NULL COMMENT '{assemblyname}', ";
}
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` VARCHAR(1024) COLLATE utf8_unicode_ci NOT NULL COMMENT '{assemblyname}' ";
}
qPrimarykey += (qPrimarykey == "") ? $"`{prop.Name}`" : $", `{prop.Name}`";
primaryFound = true;
}
else
{
if (propInfo.GetCustomAttributes(true).Where(x => x is MaxLengthAttribute).Count() > 0)
{
var temp = (MaxLengthAttribute)propInfo.GetCustomAttributes(true).Where(x => x is MaxLengthAttribute).FirstOrDefault();
long length = temp.Length;
if (length > 4000)
{
qBody += $"{Environment.NewLine} `{prop.Name}` longtext COLLATE utf8_unicode_ci NULL COMMENT '{assemblyname}' ";
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` VARCHAR({temp.Length}) COLLATE utf8_unicode_ci NULL COMMENT '{assemblyname}' ";
}
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` VARCHAR(1024) COLLATE utf8_unicode_ci NULL COMMENT '{assemblyname}' ";
}
}
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(bool))
{
qBody += $"{Environment.NewLine} `{prop.Name}` bit(1) NOT NULL COMMENT '{assemblyname}' ";
columnList.Add(prop.Name);
}
else if (prop.PropertyType == typeof(DateTime))
{
if (propInfo.GetCustomAttributes(true).Where(x => x is KeyAttribute).Count() > 0)
{
qPrimaryColumn = $"{Environment.NewLine} `{prop.Name}` datetime(6) NOT NULL COMMENT '{assemblyname}', ";
qPrimarykey += (qPrimarykey == "") ? $"`{prop.Name}`" : $", `{prop.Name}`";
primaryFound = true;
}
else
{
qBody += $"{Environment.NewLine} `{prop.Name}` datetime(6) NULL COMMENT '{assemblyname}' ";
}
columnList.Add(prop.Name);
}
else
{
if (prop.PropertyType.BaseType == typeof(Enum))
{
qBody += $"{Environment.NewLine} `{prop.Name}` INT NOT NULL COMMENT '{assemblyname}' ";
columnList.Add(prop.Name);
}
else
{
if (columnList.Where(x => x == string.Concat(prop.Name, "Id")).Count() <= 0)
{
//var temp = prop.PropertyType.GetProperties().OrderBy(x => x.Name).ToArray();
var propTypeTablename = GlobalContext.GetTableName(prop.PropertyType);
var tempIndexKeyName = $"IX_{propTypeTablename}_{prop.Name}Id";
if (tempIndexKeyName.Length > 60)
{
tempIndexKeyName = tempIndexKeyName.Replace("_", "");
}
if (tempIndexKeyName.Length > 60)
{
tempIndexKeyName = $"IX_{Guid.NewGuid().ToString()}";
}
if (tempIndexKeyName.Length > 60)
{
tempIndexKeyName = tempIndexKeyName.Substring(0, 60);
}
var temp = "";
var tempi = 0;
while (true)
{
if (qIndexkey.Contains(tempIndexKeyName + temp) == false)
{
tempIndexKeyName = tempIndexKeyName + temp;
break;
}
tempi++;
temp = "_" + tempi.ToString();
}
var tempForeginKeyName = $"FK_{currentTableName}_{propTypeTablename}_{prop.Name}Id";
if (tempForeginKeyName.Length > 60)
{
tempForeginKeyName = tempForeginKeyName.Replace("_", "");
}
if (tempForeginKeyName.Length > 60)
{
tempForeginKeyName = $"FK_{Guid.NewGuid().ToString()}";
}
if (tempForeginKeyName.Length > 60)
{
tempForeginKeyName = tempForeginKeyName.Substring(0, 60);
}
temp = "";
tempi = 0;
while (true)
{
if (qForeginkey.Contains(tempForeginKeyName + temp) == false)
{
tempForeginKeyName = tempForeginKeyName + temp;
break;
}
tempi++;
temp = "_" + tempi.ToString();
}
qIndexkey += $", {Environment.NewLine} KEY `{tempIndexKeyName}` (`{prop.Name}Id`) ";
qForeginkey += $", {Environment.NewLine} CONSTRAINT `{tempForeginKeyName}` FOREIGN KEY (`{prop.Name}Id`) REFERENCES `{propTypeTablename}` (`Id`) ON DELETE NO ACTION ";
qBody += $"{Environment.NewLine} `{prop.Name}Id` BIGINT NULL COMMENT '{assemblyname}' ";
columnList.Add(string.Concat(prop.Name, "Id"));
continue;
}
}
}
}
if (primaryFound)
{
qPrimarykey = $",{Environment.NewLine}PRIMARY KEY ({qPrimarykey})";
}
query.MySql_QueryText = qStart + qPrimaryColumn + qBody + qPrimarykey + qIndexkey + qForeginkey + qEnd;
}
else
{
throw new System.Exception("No supported database found.");
}
return(_entityRepository.ExecuteSqlCommand(query)); //from base repository
}
