internal List <DMLoaiThuChiInfor> Search(DMLoaiThuChiInfor dmLoaiThuChiInfor) { CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiThuChiSearch); Parameters.AddWithValue("@KyHieu", dmLoaiThuChiInfor.KyHieu); Parameters.AddWithValue("@Ten", dmLoaiThuChiInfor.Ten); return(FillToList <DMLoaiThuChiInfor>()); }
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); } }
public void InsertChucNang(int idNhomNguoiDung, int idChucNang) { CreateCommonCommand(Declare.StoreProcedureNamespace.spNhomNguoiDungInsertCN); Parameters.AddWithValue("@p_IdNhomNguoiDung", idNhomNguoiDung); Parameters.AddWithValue("@p_IdChucNang", idChucNang); ExecuteNoneQuery(); }
internal void Delete(DMQuyenHoaDonInfor dmQuyenHoaDonInfor) { CreateCommonCommand(Declare.StoreProcedureNamespace.spHoaDonDelete); Parameters.AddWithValue("@KyHieuHoaDon", dmQuyenHoaDonInfor.KyHieuHoaDon); Parameters.AddWithValue("@KyTuDauSerie", dmQuyenHoaDonInfor.KyTuDauSerie); ExecuteNoneQuery(); }
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); } }
internal List <DMChucNangInfor> Search(DMChucNangInfor dmChucNangInfor) { CreateGetListCommand(Declare.StoreProcedureNamespace.spChucNangSearch); Parameters.AddWithValue("@MaChucNang", dmChucNangInfor.MaChucNang); Parameters.AddWithValue("@TenChucNang", dmChucNangInfor.TenChucNang); return(FillToList <DMChucNangInfor>()); }
internal List <DMQuyenHoaDonInfor> Search(DMQuyenHoaDonInfor dmQuyenHoaDonInfor) { CreateGetListCommand(Declare.StoreProcedureNamespace.spHoaDonSearch); Parameters.AddWithValue("@KyHieuHoaDon", dmQuyenHoaDonInfor.KyHieuHoaDon); Parameters.AddWithValue("@KyTuDauSerie", dmQuyenHoaDonInfor.KyTuDauSerie); return(FillToList <DMQuyenHoaDonInfor>()); }
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); }
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>()); }
internal List <DMTaxCodeInfor> Search(DMTaxCodeInfor dmMaLoiInfor) { CreateGetListCommand(Declare.StoreProcedureNamespace.spTaxCodeSearch); Parameters.AddWithValue("@TenLoi", dmMaLoiInfor.Name); Parameters.AddWithValue("@IdLoaiItem", dmMaLoiInfor.Code); return(FillToList <DMTaxCodeInfor>()); }
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); }
internal List <DMTienTeInfor> Search(DMTienTeInfor dmTienTeInfor) { CreateGetListCommand(Declare.StoreProcedureNamespace.spTienTeSearch); Parameters.AddWithValue("@TenTienTe", dmTienTeInfor.TenTienTe); Parameters.AddWithValue("@KyHieu", dmTienTeInfor.KyHieu); return(FillToList <DMTienTeInfor>()); }
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); }
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); }
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); }
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); }
/// <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); } } }
/// <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); } }
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); }
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); }
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); }
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())); }
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)); }
public DMLoaiItemInfor GetTrungTamByIdInfo(int id) { CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiItemGetById); Parameters.AddWithValue("@IdLoaiItem", id); return(FillToObject <DMLoaiItemInfor>()); }
internal List <DMLoaiItemInfor> Search(DMLoaiItemInfor dmLoaiItemInfor) { CreateGetListCommand(Declare.StoreProcedureNamespace.spLoaiItemSearch); Parameters.AddWithValue("@MaLoaiItem", dmLoaiItemInfor.MaLoaiItem); return(FillToList <DMLoaiItemInfor>()); }