Ejemplo n.º 1
0
 internal List <DMLoaiThuChiInfor> Search(DMLoaiThuChiInfor dmLoaiThuChiInfor)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiThuChiSearch);
     Parameters.AddWithValue("@KyHieu", dmLoaiThuChiInfor.KyHieu);
     Parameters.AddWithValue("@Ten", dmLoaiThuChiInfor.Ten);
     return(FillToList <DMLoaiThuChiInfor>());
 }
Ejemplo n.º 2
0
        public bool updateMember([FromBody] FamilyMember familyMember)
        {
            SqlConnection cn  = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ParentalAppConnection"].ConnectionString);
            SqlCommand    cmd = new SqlCommand();
            DataSet       ds  = new DataSet();

            cmd.Connection  = cn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "USP_UpdateFamilyMember"; qweeeeeeeeeeeeeeeeeeeeeeeeetrkkkkkk3tr452225888888888888888888
            cmd.Parameters.AddWithValue("@id", familyMember.id);

            cmd.Parameters.AddWithValue("@firstname", familyMember.firstName);
            cmd.Parameters.AddWithValue("@lastname", familyMember.lastName);
            cmd.Parameters.AddWithValue("@adress", familyMember.address);
            cmd.Parameters.AddWithValue("@mobilenumber", familyMember.mobileNumber);
            cmd.Parameters.AddWithValue("@gender", familyMember.gender);
            cn.Open();
            int effectedRows = cmd.ExecuteNonQuery();

            if (effectedRows > 0)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Ejemplo n.º 3
0
 public void InsertChucNang(int idNhomNguoiDung, int idChucNang)
 {
     CreateCommonCommand(Declare.StoreProcedureNamespace.spNhomNguoiDungInsertCN);
     Parameters.AddWithValue("@p_IdNhomNguoiDung", idNhomNguoiDung);
     Parameters.AddWithValue("@p_IdChucNang", idChucNang);
     ExecuteNoneQuery();
 }
Ejemplo n.º 4
0
 internal void Delete(DMQuyenHoaDonInfor dmQuyenHoaDonInfor)
 {
     CreateCommonCommand(Declare.StoreProcedureNamespace.spHoaDonDelete);
     Parameters.AddWithValue("@KyHieuHoaDon", dmQuyenHoaDonInfor.KyHieuHoaDon);
     Parameters.AddWithValue("@KyTuDauSerie", dmQuyenHoaDonInfor.KyTuDauSerie);
     ExecuteNoneQuery();
 }
Ejemplo n.º 5
0
        public void DeleteMember(int memberId)
        {
            try
            {
                /*Opening database connection*/
                OpenConn(true);

                /*Beginning database transaction, which helps to rollback the deleted data if anything went wrong.
                 * This database call has multiple deletes in it.
                 */
                BeginTran();
                Parameters.AddWithValue("@MemberId", memberId);
                ExecuteNonQuery(DbCommands.sp_DeleteMemberById);
                /*If all actions in procedure are performed well then commit the transaction.*/
                CommitTran();
            }
            catch
            {
                /*If anything went wrong in deleting records from any of the parent or child table then rollback the transaction.*/
                RollBackTran();
            }
            finally
            {
                /*Close Connection, this code will always executes..*/
                OpenConn(false);
            }
        }
Ejemplo n.º 6
0
 internal List <DMChucNangInfor> Search(DMChucNangInfor dmChucNangInfor)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spChucNangSearch);
     Parameters.AddWithValue("@MaChucNang", dmChucNangInfor.MaChucNang);
     Parameters.AddWithValue("@TenChucNang", dmChucNangInfor.TenChucNang);
     return(FillToList <DMChucNangInfor>());
 }
Ejemplo n.º 7
0
 internal List <DMQuyenHoaDonInfor> Search(DMQuyenHoaDonInfor dmQuyenHoaDonInfor)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spHoaDonSearch);
     Parameters.AddWithValue("@KyHieuHoaDon", dmQuyenHoaDonInfor.KyHieuHoaDon);
     Parameters.AddWithValue("@KyTuDauSerie", dmQuyenHoaDonInfor.KyTuDauSerie);
     return(FillToList <DMQuyenHoaDonInfor>());
 }
Ejemplo n.º 8
0
        public LocationInfo GetLocationById(int locationId)
        {
            LocationInfo locationInfo = new LocationInfo();

            try
            {
                /*Opening database connection*/
                OpenConn(true);
                Parameters.AddWithValue("@LocationId", locationId);
                SqlDataReader dr = ExecReader(DbCommands.sp_GetLocationById);
                if (dr.Read())
                {
                    /*Assigning values to locationInfo object*/
                    locationInfo.LocationId   = Convert.ToInt32(dr["LocationId"]);
                    locationInfo.LocationName = dr["LocationName"].ToString();
                    locationInfo.Description  = dr["Description"].ToString();
                    locationInfo.LocationUrl  = dr["LocationUrl"].ToString();
                }
            }
            finally
            {
                /*Close Connection, this code will always executes..*/
                OpenConn(false);
            }

            /*Returning filled object.*/
            return(locationInfo);
        }
Ejemplo n.º 9
0
 public DMQuyenHoaDonInfor GetHoaDonInfo(string kytudau, string kyhieuhoadon)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spHoaDonGetById);
     Parameters.AddWithValue("@KyTuDau", kytudau);
     Parameters.AddWithValue("@KyHieuHoaDon", kyhieuhoadon);
     return(FillToObject <DMQuyenHoaDonInfor>());
 }
 internal List <CauHinh_LoaiSanPhamInfo> Search(CauHinh_LoaiSanPhamInfo info)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spCauHinhLoaiSanPhamSearch);
     Parameters.AddWithValue("@TenChucNang", info.TenCauHinh);
     Parameters.AddWithValue("@MaChucNang", info.MaCauHinh);
     return(FillToList <CauHinh_LoaiSanPhamInfo>());
 }
Ejemplo n.º 11
0
 internal List <DMTaxCodeInfor> Search(DMTaxCodeInfor dmMaLoiInfor)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spTaxCodeSearch);
     Parameters.AddWithValue("@TenLoi", dmMaLoiInfor.Name);
     Parameters.AddWithValue("@IdLoaiItem", dmMaLoiInfor.Code);
     return(FillToList <DMTaxCodeInfor>());
 }
Ejemplo n.º 12
0
         public int UpdateEmployeeSalary(int percentage, int deptid, int posid)
         {
             OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
             OracleCommand cmd = new OracleCommand("GIVE_RAISE_SP", con);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.AddWithValue("PV_PERCENTAGE_RAISE_I", OracleType.Int32).Value = percentage;
             cmd.Parameters.AddWithValue("PV_POSITIONID_I", OracleType.Int32).Value = posid;
 stac            cmd.Parameters.AddWithValue("PV_DEPTID_I", OracleType.Int32).Value = deptid;
             cmd.Parameters.AddWithValue("PV_NUM_EMPLOYEES_O", OracleType.Int32).Direction = ParameterDirection.Output;
 
             OracleDataAdapter da = new OracleDataAdapter(cmd);
 
             try
             {
                 con.Open();
                 da.UpdateCommand = cmd;
                 cmd.ExecuteNonQuery();
             }
             catch (Exception)
             {
 
             }
             finally
             {
                 con.Close();
             }
 
             return Convert.ToInt32(cmd.Parameters["PV_NUM_EMPLOYEES_O"].Value);
         }
Ejemplo n.º 13
0
 internal List <DMTienTeInfor> Search(DMTienTeInfor dmTienTeInfor)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spTienTeSearch);
     Parameters.AddWithValue("@TenTienTe", dmTienTeInfor.TenTienTe);
     Parameters.AddWithValue("@KyHieu", dmTienTeInfor.KyHieu);
     return(FillToList <DMTienTeInfor>());
 }
Ejemplo n.º 14
0
        public bool Exist(string pMaNhom)
        {
            CreateCommonCommand(Declare.StoreProcedureNamespace.spNhomNguoiDungExistNND);
            Parameters.AddWithValue("@p_Count", 0).Direction = ParameterDirection.Output;
            Parameters.AddWithValue("@p_MaNhom", pMaNhom);
            ExecuteNoneQuery();

            return(Common.IntValue(Parameters["@p_Count"].Value) == 1);
        }
Ejemplo n.º 15
0
        internal bool Exist(DMTaxCodeInfor dmTaxCodeInfor)
        {
            CreateCommonCommand(Declare.StoreProcedureNamespace.spTaxCodeExist);
            Parameters.AddWithValue("@Count", 0).Direction = ParameterDirection.Output;
            Parameters.AddWithValue("@IdTaxCode", dmTaxCodeInfor.IdTaxCode);
            Parameters.AddWithValue("@Code", dmTaxCodeInfor.Code);
            ExecuteNoneQuery();

            return(Convert.ToInt32(Parameters["@Count"].Value) == 1);
        }
Ejemplo n.º 16
0
        internal bool Exist(DMLoaiItemInfor dmLoaiItemInfor)
        {
            CreateCommonCommand(Declare.StoreProcedureNamespace.spLoaiItemExist);
            Parameters.AddWithValue("@Count", 0).Direction = ParameterDirection.Output;
            Parameters.AddWithValue("@IdLoaiItem", dmLoaiItemInfor.IdLoaiItem);
            Parameters.AddWithValue("@MaLoaiItem", dmLoaiItemInfor.MaLoaiItem);
            ExecuteNoneQuery();

            return(Convert.ToInt32(Parameters["@Count"].Value) == 1);
        }
        internal bool Exist(CauHinh_LoaiSanPhamInfo info)
        {
            CreateCommonCommand(Declare.StoreProcedureNamespace.spCauHinhLoaiSanPhamExist);
            Parameters.AddWithValue("@Count", 0).Direction = ParameterDirection.Output;
            Parameters.AddWithValue("@IdChucNang", info.IdCauHinh);
            Parameters.AddWithValue("@TenChucNang", info.MaCauHinh);
            ExecuteNoneQuery();

            return(Convert.ToInt32(Parameters["@Count"].Value) == 1);
        }
Ejemplo n.º 18
0
        internal bool Exist(DMQuyenHoaDonInfor dmQuyenHoaDonInfor)
        {
            CreateCommonCommand(Declare.StoreProcedureNamespace.spHoaDonExist);
            Parameters.AddWithValue("@Count", 0).Direction = ParameterDirection.Output;
            Parameters.AddWithValue("@KyHieuHoaDon", dmQuyenHoaDonInfor.KyHieuHoaDon);
            Parameters.AddWithValue("@KyTuDauSerie", dmQuyenHoaDonInfor.KyTuDauSerie);
            ExecuteNoneQuery();

            return(Convert.ToInt32(Parameters["@Count"].Value) == 1);
        }
Ejemplo n.º 19
0
 /// <summary>
 /// Fetch Output Parameter values include input values
 /// </summary>
 /// <param name="collection"></param>
 /// <param name="parameterPrefixText"></param>
 protected virtual void GetOutputParamterValuesIncludeInput(DbParameterCollection collection, string parameterPrefixText)
 {
     Parameters.Clear();
     foreach (DbParameter param in collection)
     {
         if (param.Direction == ParameterDirection.Output || param.Direction == ParameterDirection.InputOutput)
         {
             Parameters.AddWithValue(param.ParameterName.Substring(parameterPrefixText.Length), param.Value);
         }
     }
 }
Ejemplo n.º 20
0
        /// <summary>
        /// Fetch Output parameter values
        /// </summary>
        /// <param name="collection">DbParameterCollection</param>
        /// <param name="parameterPrefixText">parameter prefix Text</param>
        protected void GetOutputParamterValues(DbParameterCollection collection, string parameterPrefixText)
        {
            var dbparams = from DbParameter d in collection
                           where d.Direction == ParameterDirection.Output ||
                           d.Direction == ParameterDirection.InputOutput
                           select new { ParamName = d.ParameterName, Value = d.Value };

            foreach (var param in dbparams)
            {
                Parameters.AddWithValue(param.ParamName.Substring(parameterPrefixText.Length), param.Value);
            }
        }
Ejemplo n.º 21
0
 private void FillParmeters(MemberInfo memberInfo, string action)
 {
     Parameters.AddWithValue("@MemberId", memberInfo.MemberId.ToString());
     Parameters.AddWithValue("@AddressId", memberInfo.AddressInfo.AddressId.ToString());
     Parameters.AddWithValue("@Lastname", memberInfo.LastName);
     Parameters.AddWithValue("@FirstName", memberInfo.FirstName);
     Parameters.AddWithValue("@MiddleName", memberInfo.MiddleName);
     Parameters.AddWithValue("@PhoneNo", memberInfo.PhoneNo);
     Parameters.AddWithValue("@MobileNo", memberInfo.MobileNo);
     Parameters.AddWithValue("@Email", memberInfo.Email);
     Parameters.AddWithValue("@MemberType", memberInfo.MemberType);
     Parameters.AddWithValue("@Action", action);
 }
Ejemplo n.º 22
0
 public void Deletelocation(int locationId)
 {
     try
     {
         /*Opening database connection*/
         OpenConn(true);
         Parameters.AddWithValue("@LocationId", locationId);
         ExecuteNonQuery(DbCommands.sp_DeleteLocationById);
     }
     finally
     {
         /*Close Connection, this code will always executes..*/
         OpenConn(false);
     }
 }
        public IEnumerable <double> FetchLocationData(string tag, DateTime startDate, DateTime endDate, bool isFreqFetch)
        {
            string colName = "mwh";

            if (isFreqFetch)
            {
                colName = "freq";
            }
            List <double> res = new List <double>();
            // Connect to a PostgreSQL database
            NpgsqlConnection conn = new NpgsqlConnection(_configuration["ConnectionStrings:MeterConnection"]);

            conn.Open();

            // Define a query
            NpgsqlCommand command = new NpgsqlCommand(@$ "SELECT data_time, {colName} FROM public.location_energy_data 
                                                        where location_id=@locationId and data_time 
                                                        between @startTime and @endTime order by data_time", conn);

            command.Parameters.AddWithValue("@locationId", tag);
            command.Parameters.AddWithValue("@startTime", startDate);
            command.Parameters.AddWithValue("@endTime", endDate);

            // Execute the query and obtain a result set
            NpgsqlDataReader dr = command.ExecuteReader();

            while (dr.HasRows)
            {
                while (dr.Read())
                {
                    DateTime dt  = dr.GetDateTime(0);
                    double   ts  = TimeUtils.ToMillisSinceUnixEpoch(dt);
                    double   val = dr.GetDouble(1);
                    if (!isFreqFetch)
                    {
                        val *= 4;
                    }
                    res.Add(ts);
                    res.Add(val);
                }
                dr.NextResult();
            }

            dr.Dispose();

            conn.Close();
            return(res);
        }
Ejemplo n.º 24
0
        private void FillParameters(LocationInfo locationInfo, string action)
        {
            Parameters.AddWithValue("@LocationName", locationInfo.LocationName);
            Parameters.AddWithValue("@Description", locationInfo.Description);
            Parameters.AddWithValue("@LocationUrl", locationInfo.LocationUrl);
            Parameters.AddWithValue("@Action", action);

            /*Declaring output parameters which helps to retrieve the ids after performing any database operations*/
            SqlParameter locationId = new SqlParameter();

            locationId.ParameterName = "@LocationId";
            locationId.Value         = locationInfo.LocationId;
            locationId.Direction     = ParameterDirection.InputOutput;
            locationId.DbType        = DbType.Int32;
            Parameters.Add(locationId);
        }
Ejemplo n.º 25
0
        public int Insert(NhomNguoiDungInfor nhomNguoiDung)
        {
            CreateCommonCommand(Declare.StoreProcedureNamespace.spNhomNguoiDungInsert);
            Parameters.AddWithValue("@p_IdNhomNguoiDung", 0).Direction = ParameterDirection.Output;
            Parameters.AddWithValue("@p_MaNhom", nhomNguoiDung.MaNhom);
            Parameters.AddWithValue("@p_TenNhom", nhomNguoiDung.TenNhom);
            Parameters.AddWithValue("@p_MoTa", nhomNguoiDung.MoTa);
            Parameters.AddWithValue("@p_SuDung", (nhomNguoiDung.SuDung ? 1 : 0));
            Parameters.AddWithValue("@p_XemTon", nhomNguoiDung.XemTon);
            Parameters.AddWithValue("@p_QuanTri", nhomNguoiDung.QuanTri);
            Parameters.AddWithValue("@p_DieuChuyen", nhomNguoiDung.DieuChuyen);
            Parameters.AddWithValue("@p_SuaChungTu", nhomNguoiDung.SuaChungTu);
            Parameters.AddWithValue("@p_SaleAdmin", nhomNguoiDung.SaleAdmin);
            Parameters.AddWithValue("@p_XemBaoCao", nhomNguoiDung.XemBaoCao);
            Parameters.AddWithValue("@p_ShowNotify", nhomNguoiDung.ShowNotify);
            Parameters.AddWithValue("@p_NhomQuyenHan", nhomNguoiDung.NhomQuyenHan);
            Parameters.AddWithValue("@p_ChonTrungTamHienTai", nhomNguoiDung.ChonTrungTamHienTai);
            ExecuteNoneQuery();

            return(Common.IntValue(Parameters["@p_IdNhomNguoiDung"].Value.ToString()));
        }
Ejemplo n.º 26
0
 public MemberInfo GetMemberById(int memberId)
 {
     try
     {
         /*Opening database connection*/
         OpenConn(true);
         Parameters.AddWithValue("@MemberId", memberId);
         SqlDataReader dr = ExecReader(DbCommands.sp_GetMemberById);
         /*Creating object to fill the data*/
         MemberInfo memberInfo = new MemberInfo();
         if (dr.Read())
         {
             FillMember(memberInfo, dr);
         }
         /*Return filled object.*/
         return(memberInfo);
     }
     finally
     {
         OpenConn(false);
     }
 }
 internal void Delete(CauHinh_LoaiSanPhamInfo info)
 {
     CreateCommonCommand(Declare.StoreProcedureNamespace.spCauHinhLoaiSanPhamDelete);
     Parameters.AddWithValue("@IdLoaiSP", info.IdCauHinh);
     ExecuteNoneQuery();
 }
        public async Task <List <double> > GetHistDeviceData(string measTag, DateTime startTime, DateTime endTime)
        {
            List <double> res      = new List <double>();
            List <string> measSegs = measTag.Split('|').ToList();

            // device name can be like Server Room|Temperature or UPS Room|Humidity
            if (measSegs.Count != 2)
            {
                return(new List <double>());
            }

            string deviceName = measSegs[0];

            deviceName = deviceName.Replace("_", " ");
            string measType     = measSegs[1];
            string tempMeasType = "Temperature";
            string humMeasType  = "Humidity";

            if (!new List <string>()
            {
                tempMeasType, humMeasType
            }.Any(s => measType.ToLower().Equals(s.ToLower())))
            {
                return(new List <double>());
            }

            // Connect to a PostgreSQL database
            NpgsqlConnection conn = new NpgsqlConnection(_scadaArchiveConnStr);

            conn.Open();

            // field1 is temp, field2 is humidity
            string valColName = (measType == tempMeasType) ? "temp_val" : "hum_val";
            // Define a query
            NpgsqlCommand command = new NpgsqlCommand(@$ "SELECT data_time, {valColName} FROM public.devices_time_data 
                                                        where device_name=@deviceName and data_time 
                                                        between @startTime and @endTime order by data_time", conn);

            command.Parameters.AddWithValue("@deviceName", deviceName);
            command.Parameters.AddWithValue("@startTime", startTime);
            command.Parameters.AddWithValue("@endTime", endTime);

            // Execute the query and obtain a result set
            NpgsqlDataReader dr = command.ExecuteReader();

            while (dr.HasRows)
            {
                while (dr.Read())
                {
                    DateTime dt  = dr.GetDateTime(0);
                    double   ts  = TimeUtils.ToMillisSinceUnixEpoch(dt);
                    double   val = dr.GetDouble(1);
                    res.Add(ts);
                    res.Add(val);
                }
                dr.NextResult();
            }

            dr.Dispose();

            conn.Close();
            return(await Task.FromResult(res));
        }
Ejemplo n.º 29
0
 public DMLoaiItemInfor GetTrungTamByIdInfo(int id)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiItemGetById);
     Parameters.AddWithValue("@IdLoaiItem", id);
     return(FillToObject <DMLoaiItemInfor>());
 }
Ejemplo n.º 30
0
 internal List <DMLoaiItemInfor> Search(DMLoaiItemInfor dmLoaiItemInfor)
 {
     CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiItemSearch);
     Parameters.AddWithValue("@MaLoaiItem", dmLoaiItemInfor.MaLoaiItem);
     return(FillToList <DMLoaiItemInfor>());
 }