protected override BangoCommand GetSearchCommandTemplate(string selectClause, bool count = false, string tableAlias = null) { //return base.GetSearchCommandTemplate(selectClause, count, tableAlias); AssignedRolesModel _model = new AssignedRolesModel(); System.Text.StringBuilder Sql = new System.Text.StringBuilder(); string alias = DbServiceUtility.GetTableAliasForTable(tableAlias); BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder); if (count) { return(base.GetSearchCommandTemplate(selectClause, count, tableAlias)); } else { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" SELECT C .*, t1.code, t1.name_np, t1.name_en FROM rbac_role_roles C LEFT JOIN rbac_role_master t1 ON C .assigned_role_id = t1. ID /**where**/ /**orderby**/" , selectClause, _model.GetTableName(), alias)); } return(cmd); }
public virtual BangoCommand GetItemCommand(DynamicDictionary data_param, string tableAlias = null) { BangoCommand cmd = new BangoCommand(); TModel mdl = new TModel(); tableAlias = tableAlias == null ? MainTableAlias : tableAlias; cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" SELECT * FROM {0} {1} /**where**/ /**orderby**/" , mdl.GetTableName(), tableAlias)); //PropertyInfo prop = mdl.GetKeyPropertyInfo(); //cmd.SqlBuilder.Where(string.Format("{0}=@{0}", prop.Name), DbServiceUtility.ToDynamicParameter(prop.Name, id, DbServiceUtility.TypeMap[prop.PropertyType])); DbServiceUtility.BindDeleteParameter(cmd, mdl, tableAlias); DbServiceUtility.BindParameters(cmd, mdl, data_param, tableAlias, SearchTypes.Equal); if (CheckClientID) { PropertyInfo client_id = mdl.GetType().GetProperty("client_id"); if (client_id != null) { DbServiceUtility.BindClientIdParameter(cmd, mdl, tableAlias, DisplayMasterDataFromSystem); //string col = DbServiceUtility.SetColumnAlias(tableAlias, "client_id"); //cmd.SqlBuilder.Where("(client_id = 1 OR client_id = @client_id)"); //DynamicParameters param = new DynamicParameters(); //param.Add("@client_id", SessionData.client_id, DbType.Int32); //cmd.SqlBuilder.AddParameters(param); } } return(cmd); }
public virtual BangoCommand GetGridFilterItemsCommand(DbConnect con, DynamicDictionary data_param, int page = -1, int pageSize = 20, string sort_by = null, bool count = false) { sort_by = sort_by == null ? string.Empty : sort_by; ComboFieldsAttribute comboAttrib = _model.GetComoFields(); if (sort_by.Trim().Length == 0 && comboAttrib.OrderBy.Length > 0) { sort_by = DbServiceUtility.SetColumnAlias(TableDetail.Alias, comboAttrib.OrderBy); } BangoCommand cmd = GetGridFilterItemsCommandTemplate(comboAttrib.ToString(), count, TableDetail.Alias); cmd = GetSearchCommand(SearchScenario.GridFilter, con, cmd, data_param, comboAttrib.ToString() , sort_by, page, pageSize, count, TableDetail.Alias); //adding the query clause string or_query = string.Empty; Dapper.DynamicParameters param = new Dapper.DynamicParameters(); if (data_param.ContainsKey("q") || data_param.ContainsKey("query")) { string query = data_param.GetValueAsString("q"); if (query.Trim().Length == 0) { query = data_param.GetValueAsString("query"); } AppendQueryAsOr(cmd, query, new string[] { "code", "name_np", "name_en" }); } return(cmd); }
public override BangoCommand GetComboItemsCommand(DbConnect con, DynamicDictionary data_param, int page = -1, int pageSize = 20, string sort_by = null, bool count = false) { sort_by = sort_by == null ? string.Empty : sort_by; ComboFieldsAttribute comboAttrib = _model.GetComoFields(); if (sort_by.Trim().Length == 0 && comboAttrib.OrderBy.Length > 0) { sort_by = DbServiceUtility.SetColumnAlias(TableDetail.Alias, comboAttrib.OrderBy); } BangoCommand cmd = GetCombotItemsCommandTemplate(comboAttrib.ToString(), count, TableDetail.Alias); if (comboAttrib.Status != null || comboAttrib.Status.Length > 0) { data_param.Add(comboAttrib.Status, true); //for Combo Status=True mains Active Recors load in ComboBOx } cmd = GetSearchCommand(SearchScenario.Combo, con, cmd, data_param, comboAttrib.ToString() , sort_by, page, pageSize, count, TableDetail.Alias); //adding the query clause string or_query = string.Empty; Dapper.DynamicParameters param = new Dapper.DynamicParameters(); return(cmd); }
protected override BangoCommand GetSearchCommandTemplate(string selectClause, bool count = false, string tableAlias = null) { System.Text.StringBuilder Sql = new System.Text.StringBuilder(); string alias = DbServiceUtility.GetTableAliasForTable(tableAlias); BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder); if (count) { return(base.GetSearchCommandTemplate(selectClause, count, tableAlias)); } else { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" select u.first_name || ' ' || u.last_name as full_name,u.contact_number,u.email, dt.name_np as donation_type_name, c.* from donation_details c LEFT JOIN donation_request_links r on r.donation_id = c.id LEFT JOIN rbac_user u on u.id=r.requested_user_id LEFT JOIN donation_type dt on dt.id = c.donation_type_id /**where**/ " , selectClause, _model.GetTableName(), alias)); } return(cmd); }
protected virtual BangoCommand GetChildNodesCommandTemplate1(int parent_id, bool count = false, string tableAlias = null, string sort_by = null) { string alias = DbServiceUtility.GetTableAliasForTable(tableAlias), columnAlias = DbServiceUtility.GetTableAliasForColumn(tableAlias); BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder); if (count) { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" SELECT count(1) total_records FROM {0} {1} /**where**/ " , _model.GetTableName(), alias)); } else { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" SELECT {3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id, {3}code || ' - ' || {3}name_np || ' - ' || {3}name_en as text, true as leaf FROM {0} {2} /**where**/ /**orderby**/" , _model.GetTableName(), string.Empty, alias, columnAlias)); } return(cmd); }
protected override BangoCommand GetSearchCommandTemplate(string selectClause, bool count = false, string tableAlias = null) { //MyroCommand cmd = base.GetSearchCommandTemplate(selectClause, count, tableAlias); //return cmd; var _model = new UserModel(); var Sql = new StringBuilder(); var alias = DbServiceUtility.GetTableAliasForTable(tableAlias); var cmd = new BangoCommand(MyroCommandTypes.SqlBuilder); if (count) { return(base.GetSearchCommandTemplate(selectClause, count, tableAlias)); } cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" SELECT c.*, a.name_en as client_en, a.name_np as client_np, a.id as client_id FROM rbac_user c LEFT JOIN app_client a ON c.client_id = a.id /**where**/ /**orderby**/" , selectClause, _model.GetTableName(), alias)); return(cmd); }
public override BangoCommand GetItemCommand(DynamicDictionary data_param, string tableAlias = null) { BangoCommand cmd = new BangoCommand(); donation_request_links mdl = new donation_request_links(); tableAlias = tableAlias == null ? MainTableAlias : tableAlias; cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" select u.first_name || ' ' || u.last_name as full_name,u.contact_number,u.email from donation_details c INNER JOIN donation_request_links r on r.donation_id = c.id INNER JOIN rbac_user u on u.id=r.requested_user_id /**where**/ /**orderby**/" , mdl.GetTableName(), tableAlias)); //PropertyInfo prop = mdl.GetKeyPropertyInfo(); //cmd.SqlBuilder.Where(string.Format("{0}=@{0}", prop.Name), DbServiceUtility.ToDynamicParameter(prop.Name, id, DbServiceUtility.TypeMap[prop.PropertyType])); DbServiceUtility.BindDeleteParameter(cmd, mdl, tableAlias); DbServiceUtility.BindParameters(cmd, mdl, data_param, tableAlias, SearchTypes.Equal); if (CheckClientID) { PropertyInfo client_id = mdl.GetType().GetProperty("client_id"); if (client_id != null) { DbServiceUtility.BindClientIdParameter(cmd, mdl, tableAlias, DisplayMasterDataFromSystem); //string col = DbServiceUtility.SetColumnAlias(tableAlias, "client_id"); //cmd.SqlBuilder.Where("(client_id = 1 OR client_id = @client_id)"); //DynamicParameters param = new DynamicParameters(); //param.Add("@client_id", SessionData.client_id, DbType.Int32); //cmd.SqlBuilder.AddParameters(param); } } return(cmd); }
//public bool CheckClientID { get; set; } = true; protected override BangoCommand GetSearchCommandTemplate(string selectClause, bool count = false, string tableAlias = null) { string alias = DbServiceUtility.GetTableAliasForTable(tableAlias); BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder); if (count) { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" SELECT count(1) total_records FROM {0} {1} /**where**/ " , _model.GetTableName(), alias)); } else { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" SELECT {0} FROM {1} {2} LEFT JOIN {1} p on c.parent_id = p.id /**where**/ /**orderby**/" , selectClause, _model.GetTableName(), alias)); } return(cmd); }
public virtual BangoCommand GetSearchCommand(SearchScenario scenario, DbConnect con, BangoCommand cmd, DynamicDictionary data_param, string selectClause, string orderByClause, int page = -1, int pageSize = 20, bool count = false, string tableAlias = null, string scenarioOthers = null) { TableDetailAttribute tableDetail = _model.GetTableDetail(); //clear the params whic are empty or null List <string> keys = new List <string>(data_param.KeyList.Cast <String>()); foreach (string key in keys) { object value = data_param.GetValue(key); if (value == null || data_param.GetValueAsString(key).Length == 0) { data_param.Remove(key); } } //BangoCommand cmd = GetSearchCommandTemplate(selectClause, count, tableAlias); //cmd.Sql.AppendLine("FROM " + model.GetTableName()); IDbExpression dbExp = App.Container.GetInstance <IDbExpression>(); if (data_param.GetCount() == 0) { return(cmd); } string append = DbServiceUtility.GetTableAliasForColumn(tableAlias); if (!(scenario == SearchScenario.TreeNode && count == false)) { //check & adding delete flag check sql DbServiceUtility.BindDeleteParameter(cmd, _model, tableAlias); if (CheckClientID) { DbServiceUtility.BindClientIdParameter(cmd, _model, tableAlias, DisplayMasterDataFromSystem); } //add remaining default search criteria cmd = BeforeBindingParameter(scenario, con, cmd, data_param, count, tableAlias); cmd = DbServiceUtility.BindParameters(cmd, _model, data_param, tableAlias); cmd = AfterBindingParameter(scenario, con, cmd, data_param, count, tableAlias); //check & adding order by clause if (count == false) { cmd = BeforeBindingOrderBy(scenario, con, cmd, data_param, count, tableAlias); cmd = DbServiceUtility.BindOrderBy(cmd, orderByClause); cmd = AfterBindingOrderBy(scenario, con, cmd, data_param, count, tableAlias); cmd = DbServiceUtility.BindPagination(cmd, page, pageSize); } } return(cmd); }
public virtual BangoCommand GetChildNodesCommand(DbConnect con, DynamicDictionary data_param, int?parent_id, string sort_by = null) { sort_by = sort_by == null ? string.Empty : sort_by; if (sort_by.Length == 0 && TableDetail.OrderByField.Length > 0) { sort_by = DbServiceUtility.GetTableAliasForColumn(TableDetail.Alias) + TableDetail.OrderByField; } BangoCommand cmd = GetTreeNodeCommandTemplate(Convert.ToInt32(parent_id), false, TableDetail.Alias, sort_by); return(GetSearchCommand(SearchScenario.ChildNodes, con, cmd, data_param, GetAllFields() , sort_by, -1, -1, false, TableDetail.Alias)); }
//Client Name End public virtual List <String> LoadAssignedRoles(DbConnect con, int user_id) { IEnumerable <DynamicDictionary> list = DbServiceUtility.ExecuteList(con, GetAllAssignedRolesQuery(user_id).ToString()); List <string> rights = new List <string>(); if (list != null) { foreach (DynamicDictionary dict in list) { rights.Add(dict.GetValueAsString("role_id")); } } return(rights); }
public virtual BangoCommand GetSearchItemsCommand(DbConnect con, DynamicDictionary data_param, int page, int pageSize, string sort_by = null, bool count = false) { sort_by = sort_by == null ? string.Empty : sort_by; TableDetailAttribute tableDetail = _model.GetTableDetail(); if (sort_by.Trim().Length == 0 && tableDetail.OrderByField != null && tableDetail.OrderByField.Length > 0) { sort_by = DbServiceUtility.SetColumnAlias(tableDetail.Alias, tableDetail.OrderByField); } BangoCommand cmd = GetSearchCommandTemplate(_model.GetAllFields(TableDetail.Alias, false), count, TableDetail.Alias); return(GetSearchCommand(SearchScenario.Search, con, cmd, data_param, GetAllFields() , sort_by, page, pageSize, count, TableDetail.Alias)); }
protected virtual BangoCommand GetGridFilterItemsCommandTemplate(string selectClause, bool count = false, string tableAlias = null) { string alias = DbServiceUtility.GetTableAliasForTable(tableAlias); BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder); if (count) { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format("SELECT count(1) total_records FROM {0} {1} /**where**/ ", _model.GetTableName(), alias)); } else { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format("SELECT {0} FROM {1} {2} /**where**/ /**orderby**/" , selectClause, _model.GetTableName(), alias)); } return(cmd); }
//Client name start public virtual string LoadClientName(DbConnect con, int client_id) { DynamicDictionary list = DbServiceUtility.ExecuteItem(con, GetClientNameQuery(client_id).ToString()); if (list == null) { return(null); } var clientName = list.GetValueAsString("client_name"); if (clientName == null) { return(null); } return(clientName); }
public virtual BangoCommand GetComboItemsCommand(DbConnect con, DynamicDictionary data_param, int page = -1, int pageSize = 20, string sort_by = null, bool count = false) { sort_by = sort_by == null ? string.Empty : sort_by; ComboFieldsAttribute comboAttrib = _model.GetComoFields(); if (sort_by.Trim().Length == 0 && comboAttrib.OrderBy.Length > 0) { sort_by = DbServiceUtility.SetColumnAlias(TableDetail.Alias, comboAttrib.OrderBy); } BangoCommand cmd = GetCombotItemsCommandTemplate(comboAttrib.ToString(), count, TableDetail.Alias); if (comboAttrib.Status != null || comboAttrib.Status.Length > 0) { data_param.Add(comboAttrib.Status, true); //for Combo Status=True mains Active Recors load in ComboBOx } cmd = GetSearchCommand(SearchScenario.Combo, con, cmd, data_param, comboAttrib.ToString() , sort_by, page, pageSize, count, TableDetail.Alias); //adding the query clause string or_query = string.Empty; Dapper.DynamicParameters param = new Dapper.DynamicParameters(); if (data_param.ContainsKey("query")) { string query = data_param.GetValueAsString("query"); if (query != null && query.Trim().Length > 0) { var q = query; string[] arr = q.Split('-'); string searchParams = arr[0].ToString().Trim(); //string name = arr[1].ToString(); AppendQueryAsOr(cmd, searchParams, new string[] { "code", "name_np", "name_en" }); } else { AppendQueryAsOr(cmd, query, new string[] { "code", "name_np", "name_en" }); } //query = data_param.GetValueAsString("query"); } return(cmd); }
public virtual string LoadPhotoURL(DbConnect con, int user_id) { DynamicDictionary list = DbServiceUtility.ExecuteItem(con, GetPhotoPathQuery(user_id).ToString()); if (list == null) { return(null); } var photoPath = list.GetValueAsString("name_photo_url"); if (photoPath == null) { return(null); } string[] formatedPhotoPath = photoPath.Split('\\'); string absolutePhotoPath = string.Join("/", formatedPhotoPath); return(absolutePhotoPath); }
public override BangoCommand GetItemCommand(DynamicDictionary data_param, string tableAlias = null) { CheckClientID = false; BangoCommand cmd = new BangoCommand(); UserModel mdl = new UserModel(); tableAlias = tableAlias ?? MainTableAlias; cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"select c.*,t1.photo_url name_photo_url,t1.id photo_id,t1.file_name userfilename from rbac_user c LEFT JOIN tax_photos_files t1 on (c.id = t1.ref_id) /**where**/ AND t1.ref_id = @id /**orderby**/" , mdl.GetTableName(), tableAlias)); //PropertyInfo prop = mdl.GetKeyPropertyInfo(); //cmd.SqlBuilder.Where(string.Format("{0}=@{0}", prop.Name), DbServiceUtility.ToDynamicParameter(prop.Name, data_param.GetValueAsInt("id"), DbServiceUtility.TypeMap[prop.PropertyType])); DbServiceUtility.BindDeleteParameter(cmd, mdl, tableAlias); DbServiceUtility.BindParameters(cmd, mdl, data_param, tableAlias, SearchTypes.Equal); if (CheckClientID) { PropertyInfo client_id = mdl.GetType().GetProperty("client_id"); if (client_id != null) { DbServiceUtility.BindClientIdParameter(cmd, mdl, tableAlias, DisplayMasterDataFromSystem); //string col = DbServiceUtility.SetColumnAlias(tableAlias, "client_id"); //cmd.SqlBuilder.Where("(client_id = 1 OR client_id = @client_id)"); //DynamicParameters param = new DynamicParameters(); //param.Add("@client_id", SessionData.client_id, DbType.Int32); //cmd.SqlBuilder.AddParameters(param); } } return(cmd); }
public override BangoCommand AfterBindingParameter(SearchScenario searchFor, DbConnect con, BangoCommand cmd, DynamicDictionary data_param, bool count = false, string tableAlias = null) { cmd = base.AfterBindingParameter(searchFor, con, cmd, data_param, count, tableAlias); cmd = DbServiceUtility.BindParameters(cmd, new donation_request_links(), data_param, "r"); return(cmd); }
public virtual BangoCommand GetTreeNodeCommandTemplate(int start_node_id, bool count = false, string tableAlias = null, string sort_by = null) { string alias = DbServiceUtility.GetTableAliasForTable(tableAlias), columnAlias = DbServiceUtility.GetTableAliasForColumn(tableAlias); BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder); string start_condition = string.Empty; TModel model = new TModel(); string secondary_condition = string.Empty; PropertyInfo delFlag = Models.ModelService.GetDeleteFieldProperty(model); if (delFlag != null) { start_condition = "c." + delFlag.Name + " = false"; } PropertyInfo field_client_id = model.GetType().GetProperty("client_id"); if (field_client_id != null) { start_condition += string.Format(" AND (c.client_id = 1 OR c.client_id = {0})", SessionData.client_id); } secondary_condition = start_condition; if (start_node_id == 0) { start_condition += string.Format(" AND COALESCE(c.parent_id,0) = {0}", start_node_id); } else { start_condition += string.Format(" AND c.id = {0}", start_node_id); } if (count) { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" SELECT count(1) total_records FROM {0} {1} /**where**/ " , _model.GetTableName(), alias)); } else { cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@" WITH RECURSIVE nodes(id, code, name_np, name_en, parent_id , node_path, parent_node_path, node_level) as( SELECT {3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id --, null::text parent_code, null::text parent_name_np, null::text parent_name_en --, c.parent_id, p.code parent_code, p.name_np parent_name_np, p.name_en parent_name_en , ARRAY[{3}id], ARRAY[{3}id], 1 FROM {0} {2} WHERE {1} UNION ALL SELECT {3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id --, p.code parent_code, p.name_np parent_name_np, p.name_en parent_name_en , node_path || c.id, node_path, nd.node_level + 1 FROM {0} {2} JOIN {0} p ON c.parent_id = p.id JOIN nodes nd ON p.id = nd.id WHERE {4} ) SELECT array_to_string(node_path , '-') node_path, array_to_string(parent_node_path , '-') parent_node_path, id node_id, code, name_np , name_en, parent_id, node_level , code || ' - ' || name_np || ' - ' || name_en as text FROM nodes order by node_path /**where**/ /**orderby**/" , _model.GetTableName(), start_condition, alias, columnAlias, secondary_condition)); } return(cmd); }
public ResponseAuth AuthenticateUserNamePasword(DbConnect con, int client_id, string username, string password) { ResponseAuth resp = new ResponseAuth(); string template = @" SELECT id, client_id, username, password, name_en, name_np,email, status FROM rbac_user u /**where**/ AND u.status=true AND u.is_deleted=false"; //creating command & preparing command string alias = DbServiceUtility.GetTableAliasForTable("u"); BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder); cmd.Template = cmd.SqlBuilder.AddTemplate(template); UserModel mdl = new UserModel(); DbServiceUtility.BindDeleteParameter(cmd, mdl, alias); DynamicDictionary data_param = new DynamicDictionary(); data_param.Add("client_id", client_id); data_param.Add("username", username); DbServiceUtility.BindParameters(cmd, mdl, data_param, alias, SearchTypes.Equal); //executing the command string finalSql = cmd.FinalSql; if (finalSql.Length > 0) { IEnumerable <SqlMapper.DapperRow> items = null; try { items = con.DB.Query <SqlMapper.DapperRow>(finalSql, cmd.FinalParameters, true); } catch (Npgsql.NpgsqlException ex) { LogTrace.WriteErrorLog(ex.ToString()); LogTrace.WriteDebugLog(string.Format("Select SQL which gave exception:\r{0}", ex.Routine)); } Errors = con.DB.GetErros(); if (items != null && items.Count() > 0) { DynamicDictionary data = Conversion.ToDynamicDictionary(items.FirstOrDefault()); if (data.GetValueAsString("password") == EncryptPassword(password)) { resp.success = true; resp.user_id = data.GetValueAsInt("id"); resp.email = data.GetValueAsString("email"); resp.message = "Login successfull"; } else { resp.message = "Username and/or Password is invalid."; } } else { if (Errors.Count > 0) { resp.message = "Technical Problem occurred."; } else { resp.message = "Please provide a valid Username."; } } } return(resp); }
public virtual bool SoftDelete(DbConnect con, TKey id) { Is_Child_Records_Exists = false; //Default child record off //pull old data TModel oldData = new TModel(); ITable <TModel, TKey> tbl = con.GetModelTable <TModel, TKey>(); oldData = tbl.Get(id); //checking if the data is editable by current login or not if (CheckClientID) { if (ValidateForClientData(oldData) == false) { return(false); } } TableDetailAttribute tableDatail = oldData.GetTableDetail(); //09 feb 2016 Foreign key refrence table data exists or not checke.. #region todo:shivahwor StringBuilder Sql = new StringBuilder(); Sql.AppendFormat(@" SELECT --tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name --,ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='{0}' ", tableDatail.Name); IEnumerable <DynamicDictionary> foreignkey_Table = DbServiceUtility.ExecuteList(con, Sql.ToString()); Sql.Length = 0; DynamicDictionary rec = null; if (foreignkey_Table != null) { foreach (DynamicDictionary item in foreignkey_Table) { object tbl_Name = item.GetValue("table_name"); object col_name = item.GetValue("column_name"); if (Sql.Length > 0) { Sql.AppendLine("\n\t UNION ALL "); } Sql.AppendFormat(" SELECT 1 from {0} Where {1}={2} AND is_deleted ='F' ", tbl_Name.ToString(), col_name.ToString(), id); } rec = DbServiceUtility.ExecuteItem(con, Sql.ToString()); } if (rec != null) { if (rec.KeyList.Count > 0) { Is_Child_Records_Exists = true; //if child records exists than do not remove... return(false); } } #endregion //data = new TModel(); DynamicDictionary data_param = new DynamicDictionary(); ChangeHistoryHelper <TModel> chngHlpr = null; chngHlpr = new ChangeHistoryHelper <TModel>(AuditActivityTypes.SOFTDELETE); //if CREATED_BY, CREATED_on field exists then update those fields PropertyInfo by = oldData.GetType().GetProperty("deleted_by"); if (by != null) { data_param.SetValue("deleted_by", SessionData.user_id); //by.SetValue(data, Bango.GetCurrentUserId()); } PropertyInfo on = oldData.GetType().GetProperty("deleted_on"); if (on != null) { //on.SetValue(data, DateTime.Now); data_param.SetValue("deleted_on", DateTime.Now); } PropertyInfo uq_code = oldData.GetType().GetProperty("deleted_uq_code"); if (on != null) { //on.SetValue(data, DateTime.Now); data_param.SetValue("deleted_uq_code", DateTime.Now.ToString("MMddHHmmss")); } PropertyInfo is_deleted = oldData.GetType().GetProperty(tableDatail.DeleteFlagField); if (is_deleted != null) { //is_deleted.SetValue(data, true); data_param.SetValue(tableDatail.DeleteFlagField, true); } chngHlpr.CheckChangeChanges(oldData, data_param); //chngHlpr.Diff.Add("id", id, DbType.Int32, ParameterDirection.Input); DynamicParameters where = new DynamicParameters(); where.Add("id", id); if (CheckClientID) { PropertyInfo client_id = oldData.GetType().GetProperty("client_id"); if (client_id != null) { where.Add("client_id", SessionData.client_id); } } try { int?savedId = tbl.Update(where, chngHlpr.Diff); if (TrackChanges) { //save the changes chngHlpr.LogChanges(con); } } catch (Npgsql.NpgsqlException ex) { LogTrace.WriteErrorLog(ex.ToString()); LogTrace.WriteDebugLog(string.Format("SQL which gave exception:\r{0}", ex.Routine)); throw ex; } return(true); }
public virtual bool Update(DbConnect con, TKey id, DynamicDictionary data) { //pull old data TModel oldData = new TModel(); ITable <TModel, TKey> tbl = con.GetModelTable <TModel, TKey>(); oldData = tbl.Get(id); //checking if the data is editable by current login or not if (CheckClientID) { if (ValidateForClientData(oldData) == false) { return(false); } } ChangeHistoryHelper <TModel> chngHlpr = null; chngHlpr = new ChangeHistoryHelper <TModel>(AuditActivityTypes.UPDATE); //if CREATED_BY, CREATED_on field exists then update those fields PropertyInfo by = oldData.GetType().GetProperty("updated_by"); if (by != null) { data.SetValue("updated_by", SessionData.user_id); } PropertyInfo on = oldData.GetType().GetProperty("updated_on"); if (on != null) { data.SetValue("updated_on", DateTime.Now); } dynamic cloned = data.Clone(); chngHlpr.CheckChangeChanges(oldData, data); //if no changes then return true if (chngHlpr.Diff.ParameterNames.Count() == 0) { return(true); } int?savedId = null; //chngHlpr.Diff.Add("id", id, DbType.Int32, ParameterDirection.Input); DynamicParameters where = new DynamicParameters(); where.Add("id", id, DbServiceUtility.GetDbType(typeof(TKey))); if (CheckClientID) { PropertyInfo client_id = oldData.GetType().GetProperty("client_id"); if (client_id != null) { where.Add("client_id", SessionData.client_id, DbServiceUtility.GetDbType(client_id.PropertyType)); } } try { savedId = tbl.Update(where, chngHlpr.Diff); if (TrackChanges) { //save the changes chngHlpr.LogChanges(con); } } catch (Npgsql.NpgsqlException ex) { LogTrace.WriteErrorLog(ex.ToString()); LogTrace.WriteDebugLog(string.Format("SQL which gave exception:\r{0}", ex.Routine)); throw ex; } if (savedId > 0) { return(true); } else { return(false); } }
/// <summary> /// Performs validation of unique-constraints. Detail on the failure are stored in UniqueErrors Property /// It will use the help of TModel for performing the unique-constraint check. /// </summary> /// <param name="item"></param> /// <returns>Returns the status of the validation.</returns> public virtual bool ValidateUniqueValue(DbConnect con, DynamicDictionary item, IModel validatorModel, bool skipFieldsNotProvided = false) { /** * TODO * 1) disable null check or check if unique constraint is not composite constraint * 2) check unique constraint in edit mode, load all data **/ PropertyInfo key = validatorModel.GetKeyPropertyInfo(); TKey id = item.GetValue <TKey>(key.Name); //if (Conversion.ToInt32(id.ToString()) > 0) // skipFieldsNotProvided = false; //if edit mode checke if (validatorModel.GetType().GetProperty("deleted_uq_code") != null) //tod not equal.. { item["deleted_uq_code"] = 1; } BangoCommand cmd = new BangoCommand(commandType: MyroCommandTypes.StringBuilder); string union = string.Empty; //List<PropertyInfo> uniqueFields = new List<PropertyInfo>(); DictionaryFx <string, PropertyInfo> uniqueFields = new DictionaryFx <string, PropertyInfo>(); //preparing sql DynamicDictionary data_param = null; Bango.Models.Attributes.TableDetailAttribute tabelDetail = validatorModel.GetTableDetail(); foreach (KeyValuePair <string, UniqueConstraint> unique in validatorModel.UniqueFields) { if (unique.Value.Fields.Count == 0) { continue; } bool value_not_provided = false; foreach (string fld in unique.Value.Fields) { if (!item.ContainsKey(fld)) { //1) disable null check or check if unique constraint is not composite constraint ///if (unique.Value.Fields.Count <= 1) if (unique.Value.Fields.Count <= 1) //TODO:Shivashwor modify... for client duplicate data insert OFF... { value_not_provided = true; break; } if (!skipFieldsNotProvided) { //If fld name not exists in validatorModel then if (validatorModel.GetValue(fld) == null) { ///item.Add(fld, null); value_not_provided = true; } else { Type t = validatorModel.GetType().GetProperty(fld).PropertyType; if (t.IsValueType) { item.Add(fld, Activator.CreateInstance(t)); } else { item.Add(fld, null); } } } else { //TODO:Shivashwor modify... for client duplicate data insert OFF... value_not_provided = true; } break; } } if (value_not_provided) { continue; } data_param = (DynamicDictionary)item.Clone(); ///TODO:SHIVASHWOR 15 nov 2015 for Unique value is empty or not... object data_val = data_param.GetValue(unique.Key); if (data_val != null) { if (data_val.ToString().Trim().Length == 0) { continue; } } if (union.Length > 0) { cmd.SqlString.AppendLine(union); } string and = string.Empty; cmd.SqlString.AppendLine(String.Format("SELECT distinct '{0}' unique_constraint, '{2}' error_message FROM {1} {3} WHERE 1=1 " , DbServiceUtility.SafeDBString(unique.Value.Name), tabelDetail.Name , DbServiceUtility.SafeDBString(unique.Value.ErrorMessage) , tabelDetail.Alias)); //CHECKING In if client_id exists in the model for adding the client_id in unique contraint check if the developer has forgot to added PropertyInfo prop_client_id = validatorModel.GetType().GetProperty("client_id"); if (prop_client_id != null) { if (!unique.Value.Fields.Contains("client_id")) { unique.Value.Fields.Add("client_id"); } } foreach (string fld in unique.Value.Fields) { if (validatorModel.GetType().GetProperty(fld) != null) { DbServiceUtility.BindParameter(cmd, fld, data_param, validatorModel.GetType().GetProperty(fld).PropertyType, tabelDetail.Alias, SearchTypes.Equal | SearchTypes.CaseSensetive, string.Empty, true, validatorModel.GetType().GetProperty(fld)); } //cmd.SqlString.AppendFormat(" {1} {0} = @{0}", fld, and);//uniqueFields[fld] = validatorModel.GetType().GetProperty(fld); } if (key.Name.Trim().Length > 0)//PRIMARY KEY Check if n { if (id != null) { //var obj_updateBy = data_param.GetValue("updated_by"); //if (obj_updateBy!= null) DbServiceUtility.BindParameter(cmd, key.Name, data_param, System.Data.DbType.Int32, tabelDetail.Alias, SearchTypes.NotEqual, string.Empty, true, key); } } union = " UNION ALL"; } string finalSql = cmd.FinalSql; IEnumerable <dynamic> lst = null; if (finalSql.Length > 0) { try { lst = con.DB.Query <dynamic>(finalSql, cmd.FinalParameters); } catch (NpgsqlException ex) { Errors.Add(ex.ToString()); LogTrace.WriteErrorLog(ex.ToString()); LogTrace.WriteDebugLog(string.Format("sql which gave exception:\r{0}", ex.Routine)); return(false); } catch (Exception ex) { } //checking for the unique constraint if (lst.Count() > 0) { foreach (DapperRow dr in lst) { DynamicDictionary dic = Conversion.ToDynamicDictionary(dr); DynamicDictionary err = new DynamicDictionary(); err.Add(dic.GetValueAsString("unique_constraint"), dic.GetValue("error_message")); ModelService.PushValidationErros(err, ValidationErrors); } return(false); } } else { //TODO:Shivashwor 01 Nov 2015/ //if edit mode nothing changed after save data occurs // throw new NoSqlStringProvidedException(); } return(true); }