Esempio n. 1
0
        public bool CheckAccessDuplicate(IdentityAccess identity)
        {
            var existed = false;

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    var parameters = new Dictionary <string, object>
                    {
                        { "@AccessId", identity.Id },
                        { "@AccessName", identity.AccessName }
                    };

                    var query  = @"SELECT 1 FROM aspnetaccess WHERE 1=1 AND AccessName = @AccessName AND Id != @AccessId";
                    var result = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, query, parameters);

                    if (Convert.ToBoolean(result))
                    {
                        existed = true;
                    }
                }
            }
            catch (Exception ex)
            {
                existed = false;
            }

            return(existed);
        }
Esempio n. 2
0
        public IdentityProjectCategory GetById(int Id)
        {
            var sqlCmd = @"ProjectCategory_GetById";

            IdentityProjectCategory info = null;

            var paramaters = new Dictionary <string, object>
            {
                { "Id", Id }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, paramaters))
                    {
                        if (reader.Read())
                        {
                            info = ExtractProjectCategory(reader);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute ProjectCategory_GetById. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(info);
        }
Esempio n. 3
0
        public IdentityProduct GetByCode(string code)
        {
            //Common syntax
            var             sqlCmd = @"Product_GetByCode";
            IdentityProduct info   = null;

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@Code", code }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
                    {
                        if (reader.Read())
                        {
                            info = ExtractProductData(reader);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute Product_GetByCode. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(info);
        }
Esempio n. 4
0
        public IdentityGroupProperty GetById(int Id)
        {
            var info   = new IdentityGroupProperty();
            var sqlCmd = @"GroupProperty_GetById";

            var parameters = new Dictionary <string, object>
            {
                { "@Id", Id }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
                    {
                        while (reader.Read())
                        {
                            info = ExtractGroupPropertyData(reader);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute GroupProperty_GetGroupPropertyById. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }
            return(info);
        }
Esempio n. 5
0
        public bool Update(IdentityProjectCategory identity)
        {
            var sqlCmd = @"ProjectCategory_Update";

            var paramaters = new Dictionary <string, object>
            {
                { "Id", identity.Id },
                { "Code", identity.Code },
                { "Name", identity.Name },
                { "Status", identity.Status },
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    MsSqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, sqlCmd, paramaters);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute ProjectCategory_Update. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(true);
        }
Esempio n. 6
0
        public int Insert(IdentityGroupProperty identity)
        {
            var newId  = 0;
            var sqlCmd = @"M_GroupProperty_Insert";

            var parameters = new Dictionary <string, object>
            {
                { "@Name", identity.Name },
                { "@Icon", identity.Icon },
                { "@Status", identity.Status },
                { "@Description", identity.Description },
                { "@CreatedBy", identity.CreatedBy }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters);
                    if (reader.Read())
                    {
                        newId = Utils.ConvertToInt32(reader[0]);
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to M_GroupProperty_Insert. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(newId);
        }
Esempio n. 7
0
        public int UpdateLang(IdentityGroupPropertyLang identity)
        {
            //Common syntax
            var sqlCmd = @"M_GroupProperty_UpdateLang";
            var newId  = 0;

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@Id", identity.Id },
                { "@GroupName", identity.GroupName },
                { "@LangCode", identity.LangCode },
                { "@Description", identity.Description }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);

                    newId = Convert.ToInt32(returnObj);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute M_GroupProperty_UpdateLang. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(newId);
        }
Esempio n. 8
0
        public IdentityPlaceTypeGroupLang GetLangDetail(int Id)
        {
            IdentityPlaceTypeGroupLang info = null;
            var sqlCmd = @"M_PlaceTypeGroup_GetLangDetail";

            var parameters = new Dictionary <string, object>
            {
                { "@Id", Id }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
                    {
                        if (reader.Read())
                        {
                            info = new IdentityPlaceTypeGroupLang();

                            info.Id        = Utils.ConvertToInt32(reader["Id"]);
                            info.LangCode  = reader["LangCode"].ToString();
                            info.GroupName = reader["GroupName"].ToString();
                            info.GroupId   = Utils.ConvertToInt32(reader["GroupId"]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute M_PlaceTypeGroup_GetLangDetail. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }
            return(info);
        }
Esempio n. 9
0
 static MsSqlOrdersDataProvider()
 {
     if (!MsSqlHelper.CheckDatabaseExists("OnionExample"))
     {
         MsSqlHelper.DeployDbInstance("OnionExample");
     }
 }
Esempio n. 10
0
        public List <IdentityProvider> GetListByUserId(string UserId)
        {
            var conn   = new SqlConnection(_connectionString);
            var sqlCmd = @"Provider_GetListByUserId";
            List <IdentityProvider> listData = new List <IdentityProvider>();
            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@UserId", UserId }
            };

            try
            {
                using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
                {
                    listData = ParsingListData(reader);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute Provider_GetListByUserId. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(listData);
        }
Esempio n. 11
0
        public bool Update(IdentityPlaceTypeGroup identity)
        {
            //Common syntax
            var sqlCmd = @"M_PlaceTypeGroup_Update";

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@Id", identity.Id },
                { "@GroupName", identity.GroupName },
                { "@GroupCode", identity.GroupCode },
                { "@FilterOnMap", identity.FilterOnMap },
                { "@SortOrder", identity.SortOrder },
                { "@Icon", identity.Icon },
                { "@Status", identity.Status }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    MsSqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, sqlCmd, parameters);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute M_PlaceTypeGroup_Update. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(true);
        }
Esempio n. 12
0
        public ActivityLog GetActivityLogById(string Id)
        {
            var info = (ActivityLog)Activator.CreateInstance(typeof(ActivityLog));

            using (var conn = new SqlConnection(_connectionString))
            {
                var parameters = new Dictionary <string, object>
                {
                    { "@Id", Id }
                };

                var reader = MsSqlHelper.ExecuteReader(conn, CommandType.Text,
                                                       @"SELECT a.*,b.Email FROM aspnetactivitylog as a    
                    LEFT JOIN aspnetusers as b on a.UserId = b.Id               
                    WHERE 1 = 1 AND ActivityLogId = @Id
                    ", parameters);
                while (reader.Read())
                {
                    info.ActivityLogId = (int)reader[0];
                    info.UserId        = reader[1].ToString();
                    info.ActivityText  = reader[2].ToString();
                    info.TargetType    = reader[3].ToString();
                    info.TargetId      = reader[4].ToString();
                    info.IPAddress     = reader[5].ToString();
                    info.ActivityDate  = (DateTime)reader[6];
                    info.ActivityType  = reader[7].ToString();
                    info.UserName      = reader[8].ToString();
                }
            }

            return(info);
        }
Esempio n. 13
0
        public int CountAllFilterActivityLog(ActivityLogQueryParms parms)
        {
            var total      = 0;
            var parameters = new Dictionary <string, object>
            {
                { "@Email", parms.Email },
                { "@ActivityText", parms.ActivityText },
                { "@ActivityType", parms.ActivityType },
                { "@FromDate", parms.FromDate },
                { "@ToDate", parms.ToDate }
            };

            using (var conn = new SqlConnection(_connectionString))
            {
                var reader = MsSqlHelper.ExecuteReader(conn, CommandType.Text,
                                                       @"SELECT count(*) as CountNum FROM aspnetactivitylog as a    
                LEFT JOIN aspnetusers as b on a.UserId = b.Id               
                WHERE 1 = 1
                AND b.Email LIKE CONCAT('%', @Email , '%') AND a.ActivityText LIKE CONCAT('%', @ActivityText , '%') 
                AND STR_TO_DATE(a.ActivityDate,'%Y-%m-%d') BETWEEN STR_TO_DATE(@FromDate,'%Y-%m-%d') AND STR_TO_DATE(@ToDate,'%Y-%m-%d')
                AND a.ActivityType LIKE CONCAT('%', @ActivityType , '%') 
                ", parameters);
                if (reader.Read())
                {
                    total = Utils.ConvertToInt32(reader["CountNum"], 0);
                }
            }
            return(total);
        }
Esempio n. 14
0
        //Create Access
        public bool CreateAccess(IdentityAccess identity)
        {
            var isSuccess = false;

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    var parameters = new Dictionary <string, object>
                    {
                        { "@AccessName", identity.AccessName },
                        { "@Description", identity.Description }
                    };

                    var query = @"INSERT INTO aspnetaccess(Id,AccessName,Active,Description) values(NEWID(),@AccessName,1,@Description)";
                    MsSqlHelper.ExecuteNonQuery(conn, query, parameters);

                    isSuccess = true;
                }
            }
            catch (Exception ex)
            {
                isSuccess = false;
            }

            return(isSuccess);
        }
Esempio n. 15
0
        public int Insert(IdentityDevice identity)
        {
            //Common syntax
            var sqlCmd = @"Device_Insert";
            var newId  = 0;

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@Name", identity.Name },
                { "@Code", identity.Code }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters);

                    newId = Convert.ToInt32(returnObj);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute Device_Insert. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(newId);
        }
Esempio n. 16
0
        public void Insert(TUser user)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                var parameters = new Dictionary <string, object>
                {
                    { "@Id", user.Id },
                    { "@Email", (object)user.Email ?? DBNull.Value },
                    { "@EmailConfirmed", user.EmailConfirmed },
                    { "@PasswordHash", (object)user.PasswordHash ?? DBNull.Value },
                    { "@SecurityStamp", (object)user.SecurityStamp ?? DBNull.Value },
                    { "@PhoneNumber", (object)user.PhoneNumber ?? DBNull.Value },
                    { "@PhoneNumberConfirmed", user.PhoneNumberConfirmed },
                    { "@TwoFactorEnabled", user.TwoFactorEnabled },
                    { "@LockoutEndDateUtc", (object)user.LockoutEndDateUtc ?? DBNull.Value },
                    { "@LockoutEnabled", user.LockoutEnabled },
                    { "@AccessFailedCount", user.AccessFailedCount },
                    { "@UserName", user.UserName }
                };

                MsSqlHelper.ExecuteNonQuery(conn, @"INSERT INTO AspNetUsers(Id,Email,EmailConfirmed,PasswordHash,SecurityStamp,
                PhoneNumber,PhoneNumberConfirmed,TwoFactorEnabled,LockoutEndDateUtc,LockoutEnabled,AccessFailedCount,UserName,CreatedDateUtc)
                VALUES(@Id,@Email,@EmailConfirmed,@PasswordHash,@SecurityStamp,@PhoneNumber,@PhoneNumberConfirmed,
                @TwoFactorEnabled,@LockoutEndDateUtc,@LockoutEnabled,@AccessFailedCount,@UserName, GETDATE())", parameters);
            }
        }
Esempio n. 17
0
        public bool Update(IdentityDevice identity)
        {
            //Common syntax
            var sqlCmd = @"Device_Update";

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@Id", identity.Id },
                { "@Name", identity.Name },
                { "@Code", identity.Code },
                //{"@LastUpdatedBy", identity.LastUpdatedBy},
                { "@Status", identity.Status }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    MsSqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, sqlCmd, parameters);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute Device_Update. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(true);
        }
Esempio n. 18
0
        public TUser GetByEmail(string email)
        {
            var user = (TUser)Activator.CreateInstance(typeof(TUser));

            using (var conn = new SqlConnection(_connectionString))
            {
                var parameters = new Dictionary <string, object>
                {
                    { "@Email", email }
                };

                var reader = MsSqlHelper.ExecuteReader(conn, CommandType.Text,
                                                       @"SELECT Id,Email,EmailConfirmed,
                PasswordHash,SecurityStamp,PhoneNumber,PhoneNumberConfirmed,TwoFactorEnabled,
                LockoutEndDateUtc,LockoutEnabled,AccessFailedCount,UserName, CreatedDateUtc FROM AspNetUsers WHERE Email=@Email", parameters);
                while (reader.Read())
                {
                    user.Id                   = reader["Id"].ToString();
                    user.Email                = reader["Email"].ToString();
                    user.EmailConfirmed       = Convert.ToBoolean(reader["EmailConfirmed"]);
                    user.PasswordHash         = reader["PasswordHash"].ToString();
                    user.SecurityStamp        = reader["SecurityStamp"].ToString();
                    user.PhoneNumber          = reader["PhoneNumber"].ToString();
                    user.PhoneNumberConfirmed = Convert.ToBoolean(reader["PhoneNumberConfirmed"]);
                    user.TwoFactorEnabled     = Convert.ToBoolean(reader["TwoFactorEnabled"]);
                    user.LockoutEndDateUtc    = reader["LockoutEndDateUtc"] == DBNull.Value ? null : (DateTime?)reader["LockoutEndDateUtc"];
                    user.LockoutEnabled       = Convert.ToBoolean(reader["LockoutEnabled"]);
                    user.AccessFailedCount    = Convert.ToInt32(reader["AccessFailedCount"]);
                    user.UserName             = reader["UserName"].ToString();

                    user.CreatedDateUtc = (DateTime)reader["CreatedDateUtc"];
                }
            }
            return(user);
        }
Esempio n. 19
0
        public bool Update(IdentityGroupProperty identity)
        {
            //Common syntax
            var sqlCmd = @"M_GroupProperty_Update";

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@Id", identity.Id },
                { "@Name", identity.Name },
                { "@Icon", identity.Icon },
                { "@Status", identity.Status },
                { "@Description", identity.Description },
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    MsSqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, sqlCmd, parameters);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute M_GroupProperty_Update. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(true);
        }
Esempio n. 20
0
        public void Update(TUser user)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                var parameters = new Dictionary <string, object>
                {
                    { "@NewId", user.Id },
                    { "@Email", (object)user.Email ?? DBNull.Value },
                    { "@EmailConfirmed", user.EmailConfirmed },
                    { "@PasswordHash", (object)user.PasswordHash ?? DBNull.Value },
                    { "@SecurityStamp", (object)user.SecurityStamp ?? DBNull.Value },
                    { "@PhoneNumber", (object)user.PhoneNumber ?? DBNull.Value },
                    { "@PhoneNumberConfirmed", user.PhoneNumberConfirmed },
                    { "@TwoFactorEnabled", user.TwoFactorEnabled },
                    { "@LockoutEndDateUtc", (object)user.LockoutEndDateUtc ?? DBNull.Value },
                    { "@LockoutEnabled", user.LockoutEnabled },
                    { "@AccessFailedCount", user.AccessFailedCount },
                    { "@UserName", user.UserName },
                    { "@Id", user.Id },
                    { "@ProviderId", user.ProviderId },
                };

                MsSqlHelper.ExecuteNonQuery(conn, @"UPDATE AspNetUsers 
                SET Id = @NewId,Email=@Email,EmailConfirmed=@EmailConfirmed,PasswordHash=@PasswordHash,SecurityStamp=@SecurityStamp,PhoneNumber=@PhoneNumber,PhoneNumberConfirmed=@PhoneNumberConfirmed,
                TwoFactorEnabled=@TwoFactorEnabled,LockoutEndDateUtc=@LockoutEndDateUtc,LockoutEnabled=@LockoutEnabled,AccessFailedCount=@AccessFailedCount,UserName=@UserName,ProviderId=@ProviderId
                WHERE Id=@Id", parameters);
            }
        }
Esempio n. 21
0
        public List <IdentityGroupProperty> GetAll()
        {
            //Common syntax
            var sqlCmd = @"M_GroupProperty_GetAll";
            List <IdentityGroupProperty> listData = new List <IdentityGroupProperty>();

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, null))
                    {
                        while (reader.Read())
                        {
                            var record = ExtractGroupPropertyData(reader);
                            listData.Add(record);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute M_GroupProperty_All. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(listData);
        }
Esempio n. 22
0
        public IQueryable <TUser> GetAll()
        {
            List <TUser> users = new List <TUser>();

            using (var conn = new SqlConnection(_connectionString))
            {
                var reader = MsSqlHelper.ExecuteReader(conn, CommandType.Text,
                                                       @"SELECT Id,Email,EmailConfirmed,
                PasswordHash,SecurityStamp,PhoneNumber,PhoneNumberConfirmed,TwoFactorEnabled,
                LockoutEndDateUtc,LockoutEnabled,AccessFailedCount,UserName,CreatedDateUtc FROM AspNetUsers", null);

                while (reader.Read())
                {
                    var user = (TUser)Activator.CreateInstance(typeof(TUser));
                    user.Id                   = reader["Id"].ToString();
                    user.Email                = reader["Email"].ToString();
                    user.EmailConfirmed       = Convert.ToBoolean(reader["EmailConfirmed"]);
                    user.PasswordHash         = reader["PasswordHash"].ToString();
                    user.SecurityStamp        = reader["SecurityStamp"].ToString();
                    user.PhoneNumber          = reader["PhoneNumber"].ToString();
                    user.PhoneNumberConfirmed = Convert.ToBoolean(reader["PhoneNumberConfirmed"]);
                    user.TwoFactorEnabled     = Convert.ToBoolean(reader["TwoFactorEnabled"]);
                    user.LockoutEndDateUtc    = reader["LockoutEndDateUtc"] == DBNull.Value ? null : (DateTime?)reader["LockoutEndDateUtc"];
                    user.LockoutEnabled       = Convert.ToBoolean(reader["LockoutEnabled"]);
                    user.AccessFailedCount    = Convert.ToInt32(reader["AccessFailedCount"]);
                    user.UserName             = reader["UserName"].ToString();

                    user.CreatedDateUtc = (DateTime)reader["CreatedDateUtc"];

                    users.Add(user);
                }
            }
            return(users.AsQueryable <TUser>());
        }
Esempio n. 23
0
        public int Insert(IdentityProjectCategory identity)
        {
            var sqlCmd = @"ProjectCategory_Insert";

            var newId = 0;

            var paramaters = new Dictionary <string, object>
            {
                { "Code", identity.Code },
                { "Name", identity.Name },
                { "Status", identity.Status },
            };

            try
            {
                using (var connn = new SqlConnection(_connectionString))
                {
                    var returnObj = MsSqlHelper.ExecuteScalar(connn, CommandType.StoredProcedure, sqlCmd, paramaters);

                    newId = Convert.ToInt32(returnObj);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute ProjectCategory_Insert. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }
            return(newId);
        }
Esempio n. 24
0
        public IdentityPlace GetPlaceById(int Id, bool getShort = false)
        {
            //Common syntax
            var           sqlCmd = @"Place_GetPlaceById";
            IdentityPlace record = null;

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@Id", Id }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
                    {
                        record = new IdentityPlace();
                        if (reader.Read())
                        {
                            record = ExtractPlaceData(reader, getShort);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute Place_GetPlaceByUrl. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(record);
        }
Esempio n. 25
0
        public List <IdentityProjectCategory> GetByPage(IdentityProjectCategory filter, int currentPage, int pageSize)
        {
            var sqlCmd = @"ProjectCategory_GetByPage";

            int offset = (currentPage - 1) * pageSize;

            List <IdentityProjectCategory> listData = null;

            var paramaters = new Dictionary <string, object>
            {
                { "@Keyword", filter.Keyword },
                { "@Status", filter.Status },
                { "@Offset", offset },
                { "@PageSize", pageSize },
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, paramaters))
                    {
                        listData = ParsingListFromReader(reader);
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute ProjectCategory_GetByPage. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(listData);
        }
Esempio n. 26
0
        public List <IdentityPlace> GetFromList(string placesList)
        {
            //Common syntax
            var sqlCmd = @"Place_GetFromList";
            List <IdentityPlace> listData = null;

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@ListPlaces", placesList }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
                    {
                        listData = new List <IdentityPlace>();
                        while (reader.Read())
                        {
                            var item = ExtractPlaceData(reader);
                            listData.Add(item);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute Place_GetFromList. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(listData);
        }
Esempio n. 27
0
        public List <IdentityProjectCategory> GetList()
        {
            var sqlCmd = @"ProjectCategory_GetList";

            List <IdentityProjectCategory> listData = new List <IdentityProjectCategory>();

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, null))
                    {
                        while (reader.Read())
                        {
                            var record = ExtractProjectCategory(reader);

                            listData.Add(record);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute ProjectCategory_GetList. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(listData);
        }
Esempio n. 28
0
        public IList <T> ExecuteStoredProcedure <T>(ISession session, string storedProcName, SqlParameter[] parameters, out int recordCount, bool keepSession) where T : new()
        {
            try
            {
                OpenConnect();
                DataTable dt   = MsSqlHelper.ExecuteDataTable(session.Connection as SqlConnection, CommandType.StoredProcedure, storedProcName, parameters);
                IList <T> list = JF.Common.Libary.ModelFunc.ModelConvertHelper <T> .ToList(dt);

                if (list == null)
                {
                    recordCount = 0;
                    return(null);
                }
                recordCount = list.Count;
                return(list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!keepSession)
                {
                    Disconnect();
                }
            }
        }
Esempio n. 29
0
        public bool Delete(int id)
        {
            //Common syntax
            var sqlCmd = @"Product_Delete";

            //For parameters
            var parameters = new Dictionary <string, object>
            {
                { "@Id", id }
            };

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    MsSqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, sqlCmd, parameters);
                }
            }
            catch (Exception ex)
            {
                var strError = "Failed to execute Product_Delete. Error: " + ex.Message;
                throw new CustomSQLException(strError);
            }

            return(true);
        }
Esempio n. 30
0
        //Delete Access: Delete menus, operations which linkages to the access
        public bool DeleteAccess(string AccessId)
        {
            var isSuccess = false;

            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    var parameters = new Dictionary <string, object>
                    {
                        { "@AccessId", AccessId }
                    };

                    var query = @"DELETE FROM aspnetoperations WHERE AccessId = @AccessId; DELETE FROM aspnetaccess WHERE Id = @AccessId; ";
                    MsSqlHelper.ExecuteNonQuery(conn, query, parameters);

                    isSuccess = true;
                }
            }
            catch (Exception ex)
            {
                isSuccess = false;
            }

            return(isSuccess);
        }