public List <string> SelectObjectByTable(string tableName, string fieldName, string value) { try { IDataReader dataReader = SqlHelper.ExecuteReader( MainConnection , CommandType.Text , "SELECT * FROM " + tableName + " WHERE " + fieldName + " = @Value" , new SqlParameter("@Value", value) ); if (dataReader.Read()) { try { List <string> list = new List <string>(); for (var i = 0; i < dataReader.FieldCount; i++) { string a = dataReader.GetName(i) + "-" + dataReader.GetValue(i); list.Add(a); } return(list); } catch (Exception ex) { throw ex; } finally { dataReader.Dispose(); } } else { return(null); } } catch (Exception ex) { throw new Exception(tableName + "::SelectByPrimaryKey::Error occured.\n" + ex.Message, ex); } finally { MainConnection.Close(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(CIcp_lecturas businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_icp_lecturas_Insert"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_id", businessObject.Id); sqlCommand.Parameters["p_id"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters["p_id"].Direction = ParameterDirection.InputOutput; sqlCommand.Parameters.AddWithValue("p_calibracion", businessObject.Calibracion); sqlCommand.Parameters["p_calibracion"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_elemento", businessObject.Elemento); sqlCommand.Parameters["p_elemento"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_idmuestra", businessObject.Idmuestra); sqlCommand.Parameters["p_idmuestra"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_muestra", businessObject.Muestra); sqlCommand.Parameters["p_muestra"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_lectura", businessObject.Lectura); sqlCommand.Parameters["p_lectura"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_dilucion2", businessObject.Dilucion2); sqlCommand.Parameters["p_dilucion2"].NpgsqlDbType = NpgsqlDbType.Varchar; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.Id = Convert.ToInt32(sqlCommand.Parameters["p_id"].Value); return(true); } catch (Exception ex) { throw new Exception("CIcp_lecturas::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// public bool Insert(AppUsersPro pro) { SqlCommand sqlCommand = new SqlCommand("dbo.[AppUsers_Insert]", MainConnection); sqlCommand.CommandType = CommandType.StoredProcedure; try { sqlCommand.Parameters.Add(new SqlParameter("@UsrLoginID", VchDB, 50, IN, false, 0, 0, "", DRV, pro.UsrLoginID)); sqlCommand.Parameters.Add(new SqlParameter("@UsrPassword", VchDB, 100, IN, false, 0, 0, "", DRV, pro.UsrPassword)); sqlCommand.Parameters.Add(new SqlParameter("@UsrFullName", VchDB, 200, IN, false, 0, 0, "", DRV, pro.UsrFullName)); if (!string.IsNullOrEmpty(pro.UsrStartDate)) { sqlCommand.Parameters.Add(new SqlParameter("@UsrStartDate", DtDB, 14, IN, false, 0, 0, "", DRV, DateFun.SaveDB(pro.UsrStartDateType, pro.UsrStartDate))); //sqlCommand.Parameters.Add(new SqlParameter("@UsrStartDateType", ChrDB, 1, IN, false, 0, 0, "", DRV, pro.UsrStartDateType)); } if (!string.IsNullOrEmpty(pro.UsrExpiryDate)) { sqlCommand.Parameters.Add(new SqlParameter("@UsrExpireDate", DtDB, 14, IN, false, 0, 0, "", DRV, DateFun.SaveDB(pro.UsrExpiryDateType, pro.UsrExpiryDate))); //sqlCommand.Parameters.Add(new SqlParameter("@UsrExpiryDateType", ChrDB, 1, IN, false, 0, 0, "", DRV, pro.UsrExpiryDateType)); } sqlCommand.Parameters.Add(new SqlParameter("@UsrStatus", BitDB, 1, IN, false, 0, 0, "", DRV, pro.UsrStatus)); sqlCommand.Parameters.Add(new SqlParameter("@UsrPermission", VchDB, 8000, IN, false, 0, 0, "", DRV, pro.UsrPermission)); sqlCommand.Parameters.Add(new SqlParameter("@UsrLanguage", VchDB, 50, IN, false, 0, 0, "", DRV, pro.UsrLanguage)); sqlCommand.Parameters.Add(new SqlParameter("@UsrEmailID", VchDB, 200, IN, false, 0, 0, "", DRV, pro.UsrEmailID)); //sqlCommand.Parameters.Add(new SqlParameter("@UsrEmpID", VchDB, 100, IN, false, 0, 0, "", DRV, pro.UsrEmpID)); sqlCommand.Parameters.Add(new SqlParameter("@UsrDescription", VchDB, 8000, IN, false, 0, 0, "", DRV, pro.UsrDescription)); sqlCommand.Parameters.Add(new SqlParameter("@TransactionBy", VchDB, 50, IN, false, 0, 0, "", DRV, pro.TransactionBy)); MainConnection.Open(); sqlCommand.ExecuteNonQuery(); return(true); } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(CCalib businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_calib_Insert"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_idcalib", businessObject.Idcalib); sqlCommand.Parameters["p_idcalib"].NpgsqlDbType = NpgsqlDbType.Bigint; sqlCommand.Parameters["p_idcalib"].Direction = ParameterDirection.InputOutput; sqlCommand.Parameters.AddWithValue("p_order_calib", businessObject.Order_calib); sqlCommand.Parameters["p_order_calib"].NpgsqlDbType = NpgsqlDbType.Smallint; sqlCommand.Parameters.AddWithValue("p_name_calib", businessObject.Name_calib); sqlCommand.Parameters["p_name_calib"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_concentration", businessObject.Concentration); sqlCommand.Parameters["p_concentration"].NpgsqlDbType = NpgsqlDbType.Numeric; sqlCommand.Parameters.AddWithValue("p_idtemplate_method", businessObject.Idtemplate_method); sqlCommand.Parameters["p_idtemplate_method"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_aliquot", businessObject.Aliquot); sqlCommand.Parameters["p_aliquot"].NpgsqlDbType = NpgsqlDbType.Numeric; sqlCommand.Parameters.AddWithValue("p_volumen", businessObject.Volumen); sqlCommand.Parameters["p_volumen"].NpgsqlDbType = NpgsqlDbType.Numeric; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.Idcalib = Convert.ToInt64(sqlCommand.Parameters["p_idcalib"].Value); return(true); } catch (Exception ex) { throw new Exception("CCalib::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public Int32 Insert(clsShazam businessObject) { SqlCommand sqlCommand = new SqlCommand(); try { MainConnection.Close(); sqlCommand.Dispose(); sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[sp_Digital_Shazam_Insert]"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; //sqlCommand.CommandTimeout = 60; sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ID)); sqlCommand.Parameters.Add(new SqlParameter("@Week", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Week)); sqlCommand.Parameters.Add(new SqlParameter("@Year", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Year)); sqlCommand.Parameters.Add(new SqlParameter("@Position", SqlDbType.NVarChar, 400, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Position)); sqlCommand.Parameters.Add(new SqlParameter("@Artist", SqlDbType.NVarChar, 400, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Artist)); sqlCommand.Parameters.Add(new SqlParameter("@Title", SqlDbType.NVarChar, 400, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Title)); MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.ID = (int)sqlCommand.Parameters["@ID"].Value; return(businessObject.ID); } catch (Exception ex) { businessObject.ID = -1; return(-1); //throw new Exception("clsSongs::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(CMatrix_item businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_matrix_item_Insert"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_idmatrix_item", businessObject.Idmatrix_item); sqlCommand.Parameters["p_idmatrix_item"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters["p_idmatrix_item"].Direction = ParameterDirection.InputOutput; sqlCommand.Parameters.AddWithValue("p_idmatrix_group", businessObject.Idmatrix_group); sqlCommand.Parameters["p_idmatrix_group"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_sigla", businessObject.Sigla); sqlCommand.Parameters["p_sigla"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_name_item", businessObject.Name_item); sqlCommand.Parameters["p_name_item"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_description", businessObject.Description); sqlCommand.Parameters["p_description"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_flag_acreditado", businessObject.Flag_acreditado); sqlCommand.Parameters["p_flag_acreditado"].NpgsqlDbType = NpgsqlDbType.Boolean; sqlCommand.Parameters.AddWithValue("p_status", businessObject.Status); sqlCommand.Parameters["p_status"].NpgsqlDbType = NpgsqlDbType.Boolean; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.Idmatrix_item = Convert.ToInt32(sqlCommand.Parameters["p_idmatrix_item"].Value); return(true); } catch (Exception ex) { throw new Exception("CMatrix_item::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(CDescription_sample businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_description_sample_Insert"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_cod_des_sample", businessObject.Cod_des_sample); sqlCommand.Parameters["p_cod_des_sample"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_description", businessObject.Description); sqlCommand.Parameters["p_description"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_usernew", businessObject.Usernew); sqlCommand.Parameters["p_usernew"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_datenew", businessObject.Datenew); sqlCommand.Parameters["p_datenew"].NpgsqlDbType = NpgsqlDbType.Date; sqlCommand.Parameters.AddWithValue("p_useredit", businessObject.Useredit); sqlCommand.Parameters["p_useredit"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_dateedit", businessObject.Dateedit); sqlCommand.Parameters["p_dateedit"].NpgsqlDbType = NpgsqlDbType.Date; sqlCommand.Parameters.AddWithValue("p_status", businessObject.Status); sqlCommand.Parameters["p_status"].NpgsqlDbType = NpgsqlDbType.Boolean; sqlCommand.Parameters.AddWithValue("p_priority_order", businessObject.Priority_order); sqlCommand.Parameters["p_priority_order"].NpgsqlDbType = NpgsqlDbType.Smallint; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); return(true); } catch (Exception ex) { throw new Exception("CDescription_sample::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(CTemplate_method_ah businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_template_method_ah_Insert"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_idtemplate_method", businessObject.Idtemplate_method); sqlCommand.Parameters["p_idtemplate_method"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_symbol", businessObject.Symbol); sqlCommand.Parameters["p_symbol"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_idunit_result", businessObject.Idunit_result); sqlCommand.Parameters["p_idunit_result"].NpgsqlDbType = NpgsqlDbType.Smallint; sqlCommand.Parameters.AddWithValue("p_usernew", businessObject.Usernew); sqlCommand.Parameters["p_usernew"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_datenew", businessObject.Datenew); sqlCommand.Parameters["p_datenew"].NpgsqlDbType = NpgsqlDbType.Date; sqlCommand.Parameters.AddWithValue("p_useredit", businessObject.Useredit); sqlCommand.Parameters["p_useredit"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_dateedit", businessObject.Dateedit); sqlCommand.Parameters["p_dateedit"].NpgsqlDbType = NpgsqlDbType.Date; sqlCommand.Parameters.AddWithValue("p_status", businessObject.Status); sqlCommand.Parameters["p_status"].NpgsqlDbType = NpgsqlDbType.Boolean; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); return(true); } catch (Exception ex) { throw new Exception("CTemplate_method_ah::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// update row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true for successfully updated</returns> public bool Update(CCompound businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_compound_Update"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_idcompound", businessObject.Idcompound); sqlCommand.Parameters["p_idcompound"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_name_compound", businessObject.Name_compound); sqlCommand.Parameters["p_name_compound"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_usernew", businessObject.Usernew); sqlCommand.Parameters["p_usernew"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_datenew", businessObject.Datenew); sqlCommand.Parameters["p_datenew"].NpgsqlDbType = NpgsqlDbType.Date; sqlCommand.Parameters.AddWithValue("p_useredit", businessObject.Useredit); sqlCommand.Parameters["p_useredit"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_dateedit", businessObject.Dateedit); sqlCommand.Parameters["p_dateedit"].NpgsqlDbType = NpgsqlDbType.Date; sqlCommand.Parameters.AddWithValue("p_status", businessObject.Status); sqlCommand.Parameters["p_status"].NpgsqlDbType = NpgsqlDbType.Boolean; MainConnection.Open(); if (Convert.ToInt32(sqlCommand.ExecuteScalar()) > 0) { return(true); } return(false); } catch (Exception ex) { throw new Exception("CCompound::Update::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// update row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true for successfully updated</returns> public bool Update(KHAMTHUONGXUYEN businessObject) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[KHAMTHUONGXUYEN_Update]"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ID)); sqlCommand.Parameters.Add(new SqlParameter("@BENHNHANID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BENHNHANID)); sqlCommand.Parameters.Add(new SqlParameter("@BACSIID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BACSIID)); sqlCommand.Parameters.Add(new SqlParameter("@THOIGIAN", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.THOIGIAN)); sqlCommand.Parameters.Add(new SqlParameter("@BENHID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BENHID)); sqlCommand.Parameters.Add(new SqlParameter("@CHANDOANSOBO", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CHANDOANSOBO)); sqlCommand.Parameters.Add(new SqlParameter("@CACHGIAIQUYET", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CACHGIAIQUYET)); sqlCommand.Parameters.Add(new SqlParameter("@THOIGIANCHUYEN", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.THOIGIANCHUYEN)); sqlCommand.Parameters.Add(new SqlParameter("@BENHVIENID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BENHVIENID)); sqlCommand.Parameters.Add(new SqlParameter("@THOIGIANVAO", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.THOIGIANVAO)); sqlCommand.Parameters.Add(new SqlParameter("@THOIGIANRA", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.THOIGIANRA)); sqlCommand.Parameters.Add(new SqlParameter("@PHUONGPHAPDIEUTRI", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.PHUONGPHAPDIEUTRI)); sqlCommand.Parameters.Add(new SqlParameter("@KETQUADIEUTRI", SqlDbType.VarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.KETQUADIEUTRI)); sqlCommand.Parameters.Add(new SqlParameter("@CHIPHIKHAM", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CHIPHIKHAM)); sqlCommand.Parameters.Add(new SqlParameter("@GHICHU", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.GHICHU)); sqlCommand.Parameters.Add(new SqlParameter("@TRIEUCHUNG", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TRIEUCHUNG)); MainConnection.Open(); sqlCommand.ExecuteNonQuery(); return(true); } catch //(Exception ex) { return(false); //throw new Exception("KHAMTHUONGXUYEN::Update::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// Select all rescords /// </summary> /// <returns>list of clsLabels</returns> public DataTable SelectAll(string FromMonth, string ToMonth, int CurrentYear, int IsAll) { SqlCommand sqlCommand = new SqlCommand(); if (IsAll == 1) { sqlCommand.CommandText = "dbo.[sp_GetPromotions]"; } else { sqlCommand.CommandText = "dbo.[sp_GetPromotionsByMonth]"; } sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { if (IsAll == 0) { sqlCommand.Parameters.Add(new SqlParameter("@FromMonth", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, FromMonth)); sqlCommand.Parameters.Add(new SqlParameter("@ToMonth", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, ToMonth)); sqlCommand.Parameters.Add(new SqlParameter("@Year", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, CurrentYear)); } MainConnection.Open(); SqlDataAdapter da = new SqlDataAdapter(sqlCommand); DataTable dt = new DataTable(); da.Fill(dt); return(dt); } catch (Exception ex) { throw new Exception("clsPromotion::SelectAll::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(CPrice_company_person businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_price_company_person_Insert"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_idprice_company_person", businessObject.Idprice_company_person); sqlCommand.Parameters["p_idprice_company_person"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters["p_idprice_company_person"].Direction = ParameterDirection.InputOutput; sqlCommand.Parameters.AddWithValue("p_idcompany", businessObject.Idcompany); sqlCommand.Parameters["p_idcompany"].NpgsqlDbType = NpgsqlDbType.Smallint; sqlCommand.Parameters.AddWithValue("p_idperson", businessObject.Idperson); sqlCommand.Parameters["p_idperson"].NpgsqlDbType = NpgsqlDbType.Smallint; sqlCommand.Parameters.AddWithValue("p_idprice", businessObject.Idprice); sqlCommand.Parameters["p_idprice"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_idprice_version", businessObject.Idprice_version); sqlCommand.Parameters["p_idprice_version"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_person_type", businessObject.Person_type); sqlCommand.Parameters["p_person_type"].NpgsqlDbType = NpgsqlDbType.Smallint; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.Idprice_company_person = Convert.ToInt32(sqlCommand.Parameters["p_idprice_company_person"].Value); return(true); } catch (Exception ex) { throw new Exception("CPrice_company_person::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// Select by primary key /// </summary> /// <param name="keys">primary keys</param> /// <returns>clsContacts business object</returns> public clsSkills GetSkillsProfByEmpID(clsSkillsKeys keys) { SqlDataAdapter da; DataTable dt; SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[sp_GetSkillsProfByEmpID]"; sqlCommand.CommandType = CommandType.StoredProcedure; da = new SqlDataAdapter(sqlCommand); dt = new DataTable(); da.Fill(dt); // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@EMP_ID", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.EMP_ID)); MainConnection.Open(); IDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.Read()) { clsSkills businessObject = new clsSkills(); PopulateBusinessObjectSkillsFromReader2(businessObject, dataReader); return(businessObject); } else { return(null); } } catch (Exception ex) { throw new Exception("clsSkills::GetSkillsProfByEmpID::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// update row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true for successfully updated</returns> public bool Update(BACSI businessObject) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[BACSI_Update]"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ID)); sqlCommand.Parameters.Add(new SqlParameter("@HOTEN", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.HOTEN)); sqlCommand.Parameters.Add(new SqlParameter("@GIOITINH", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.GIOITINH)); sqlCommand.Parameters.Add(new SqlParameter("@SDT", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.SDT)); sqlCommand.Parameters.Add(new SqlParameter("@QUEQUAN", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.QUEQUAN)); sqlCommand.Parameters.Add(new SqlParameter("@QUYEN", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.QUYEN)); sqlCommand.Parameters.Add(new SqlParameter("@NGAYSINH", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.NGAYSINH)); sqlCommand.Parameters.Add(new SqlParameter("@CAPBACID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CAPBACID)); sqlCommand.Parameters.Add(new SqlParameter("@CHUCVUID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CHUCVUID)); sqlCommand.Parameters.Add(new SqlParameter("@DONVI", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DONVI)); sqlCommand.Parameters.Add(new SqlParameter("@DANTOC", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DANTOC)); sqlCommand.Parameters.Add(new SqlParameter("@TRINHDOVANHOA", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TRINHDOVANHOA)); sqlCommand.Parameters.Add(new SqlParameter("@DOITUONGID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DOITUONGID)); sqlCommand.Parameters.Add(new SqlParameter("@DONVIID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DONVIID)); sqlCommand.Parameters.Add(new SqlParameter("@TAIKHOANID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TAIKHOANID)); sqlCommand.Parameters.Add(new SqlParameter("@GHICHU", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.GHICHU)); MainConnection.Open(); sqlCommand.ExecuteNonQuery(); return(true); } catch //(Exception ex) { return(false); //throw new Exception("BACSI::Update::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// public int Insert(CardsPro pro) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[Cards_Insert]"; sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@CardID", SqlDbType.Int, 10, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Proposed, pro.CardID)); sqlCommand.Parameters.Add(new SqlParameter("@EmpID", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.EmpID)); sqlCommand.Parameters.Add(new SqlParameter("@IsID", SqlDbType.Int, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.IsID)); sqlCommand.Parameters.Add(new SqlParameter("@CardCount", SqlDbType.Int, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.CardCount)); sqlCommand.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime, 14, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, DateFun.SaveDB('S', pro.StartDate))); sqlCommand.Parameters.Add(new SqlParameter("@ExpiryDate", SqlDbType.DateTime, 14, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, DateFun.SaveDB('S', pro.ExpiryDate))); sqlCommand.Parameters.Add(new SqlParameter("@TmpID", SqlDbType.Int, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.TmpID)); sqlCommand.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.Description)); sqlCommand.Parameters.Add(new SqlParameter("@CardStatus", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.CardStatus)); sqlCommand.Parameters.Add(new SqlParameter("@InActiveStatus", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.InActiveStatus)); sqlCommand.Parameters.Add(new SqlParameter("@IsApproved", SqlDbType.Int, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.IsApproved)); sqlCommand.Parameters.Add(new SqlParameter("@isPrinted", SqlDbType.Bit, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.isPrinted)); sqlCommand.Parameters.Add(new SqlParameter("@CreatedBy", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.TransactionBy)); sqlCommand.Parameters.Add(new SqlParameter("@CreatedDate", SqlDbType.DateTime, 14, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, pro.TransactionDate)); MainConnection.Open(); int rowsAffected = sqlCommand.ExecuteNonQuery(); rowsAffected = Convert.ToInt32(sqlCommand.Parameters["@CardID"].Value); return(rowsAffected); } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
public void SelectFromStore(out string ecode, out string edesc, string storeName, params object[] values) { try { DataSet ds = null; ds = SqlHelper.ExecuteDataset(MainConnection, storeName, values); ecode = ds.Tables[0].Rows[0]["ECODE"].ToString(); edesc = ds.Tables[0].Rows[0]["EDESC"].ToString(); } catch (Exception ex) { throw new Exception("SelectFromStore::'" + storeName + "'.\n" + ex.Message, ex); } finally { MainConnection.Close(); } }
public bool UpdateWithTransaction(SqlTransaction sqlTransaction, T businessObject) { List <SqlParameter> sqlParams = new List <SqlParameter>(); string tableName = businessObject.GetType().Name; string keyName = GetKeyName(); object keyValue = GetKeyValue(businessObject); string values = ""; try { foreach (var p in businessObject.GetType() .GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly) .Where(x => x.GetCustomAttribute(typeof(PrimaryKey), true) == null)) { values += p.Name + " = @" + p.Name + ","; SqlParameter temp = new SqlParameter("@" + p.Name, p.GetValue(businessObject, null)); sqlParams.Add(temp); } sqlParams.Add(new SqlParameter("@" + keyName, keyValue)); int count = SqlHelper.ExecuteNonQuery( sqlTransaction , CommandType.Text , "UPDATE " + tableName + " SET " + values.TrimEnd(',') + " WHERE " + keyName + " = @" + keyName , sqlParams.ToArray() ); if (count == 0) { return(false); } else { return(true); } } catch (Exception ex) { throw new Exception("UpdateWithTransaction::'" + tableName + "'.\n" + ex.Message, ex); } finally { MainConnection.Close(); } }
/// <summary> /// update row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true for successfully updated</returns> public bool Update(CPrice_company_person businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_price_company_person_Update"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_idprice_company_person", businessObject.Idprice_company_person); sqlCommand.Parameters["p_idprice_company_person"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_idcompany", businessObject.Idcompany); sqlCommand.Parameters["p_idcompany"].NpgsqlDbType = NpgsqlDbType.Smallint; sqlCommand.Parameters.AddWithValue("p_idperson", businessObject.Idperson); sqlCommand.Parameters["p_idperson"].NpgsqlDbType = NpgsqlDbType.Smallint; sqlCommand.Parameters.AddWithValue("p_idprice", businessObject.Idprice); sqlCommand.Parameters["p_idprice"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_idprice_version", businessObject.Idprice_version); sqlCommand.Parameters["p_idprice_version"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_person_type", businessObject.Person_type); sqlCommand.Parameters["p_person_type"].NpgsqlDbType = NpgsqlDbType.Smallint; MainConnection.Open(); if (Convert.ToInt32(sqlCommand.ExecuteScalar()) > 0) { return(true); } return(false); } catch (Exception ex) { throw new Exception("CPrice_company_person::Update::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
public clsProfile getProfileByEmailAddress(string emailAddress) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "[dbo].[sp_getProfileByEmail]"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@EMAILADDRESS", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, emailAddress)); MainConnection.Open(); IDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.Read()) { clsProfile businessObject = new clsProfile(); // PopulateBusinessObjectFromReader(businessObject, dataReader); // return(businessObject); } else { return(null); } } catch (Exception ex) { String mess = ex.Message; throw new Exception("clsProfile::getProfile::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// public bool InsertUpdate(ApplicationSetupPro pro) { SqlCommand sqlCommand = new SqlCommand("dbo.[ApplicationSetup_InsertUpdate]", MainConnection); sqlCommand.CommandType = CommandType.StoredProcedure; try { sqlCommand.Parameters.Add(new SqlParameter("@AppCompany", VchDB, 100, IN, false, 0, 0, "", DRV, pro.AppCompany)); sqlCommand.Parameters.Add(new SqlParameter("@AppDisplay", VchDB, 100, IN, false, 0, 0, "", DRV, pro.AppDisplay)); sqlCommand.Parameters.Add(new SqlParameter("@AppAddress1", VchDB, 255, IN, false, 0, 0, "", DRV, pro.AppAddress1)); sqlCommand.Parameters.Add(new SqlParameter("@AppAddress2", VchDB, 255, IN, false, 0, 0, "", DRV, pro.AppAddress2)); sqlCommand.Parameters.Add(new SqlParameter("@AppCity", VchDB, 50, IN, false, 0, 0, "", DRV, pro.AppCity)); sqlCommand.Parameters.Add(new SqlParameter("@AppCountry", VchDB, 50, IN, false, 0, 0, "", DRV, pro.AppCountry)); sqlCommand.Parameters.Add(new SqlParameter("@AppPOBox", VchDB, 50, IN, false, 0, 0, "", DRV, pro.AppPOBox)); sqlCommand.Parameters.Add(new SqlParameter("@AppTelNo1", VchDB, 50, IN, false, 0, 0, "", DRV, pro.AppTelNo1)); sqlCommand.Parameters.Add(new SqlParameter("@AppTelNo2", VchDB, 50, IN, false, 0, 0, "", DRV, pro.AppTelNo2)); sqlCommand.Parameters.Add(new SqlParameter("@AppFax", VchDB, 50, IN, false, 0, 0, "", DRV, pro.AppFax)); sqlCommand.Parameters.Add(new SqlParameter("@AppUrl", VchDB, 500, IN, false, 0, 0, "", DRV, pro.AppUrl)); sqlCommand.Parameters.Add(new SqlParameter("@AppEmail", VchDB, 50, IN, false, 0, 0, "", DRV, pro.AppEmail)); sqlCommand.Parameters.Add(new SqlParameter("@AppCalendar", VchDB, 1, IN, false, 0, 0, "", DRV, pro.AppCalendar)); sqlCommand.Parameters.Add(new SqlParameter("@AppLogo", SqlDbType.Image, 1000000, IN, false, 0, 0, "", DRV, pro.AppLogo)); sqlCommand.Parameters.Add(new SqlParameter("@AppLogoImageType", VchDB, 100, IN, false, 0, 0, "", DRV, pro.AppLogoImageType)); sqlCommand.Parameters.Add(new SqlParameter("@AppLogoImageLength", SqlDbType.Int, 20, IN, false, 0, 0, "", DRV, pro.AppLogoImageLength)); sqlCommand.Parameters.Add(new SqlParameter("@TransactionBy", VchDB, 50, IN, false, 0, 0, "", DRV, pro.TransactionBy)); MainConnection.Open(); sqlCommand.ExecuteNonQuery(); return(true); } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// Select by primary key /// </summary> /// <param name="keys">primary keys</param> /// <returns>VATTU business object</returns> public VATTU SelectByPrimaryKey(VATTUKeys keys) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[VATTU_SelectByPrimaryKey]"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.ID)); MainConnection.Open(); IDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.Read()) { VATTU businessObject = new VATTU(); PopulateBusinessObjectFromReader(businessObject, dataReader); return businessObject; } else { return null; } } catch //(Exception ex) { return null;//throw new Exception("VATTU::SelectByPrimaryKey::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(CTablempn businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_tablempn_Insert"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_idtablempn", businessObject.Idtablempn); sqlCommand.Parameters["p_idtablempn"].NpgsqlDbType = NpgsqlDbType.Smallint; sqlCommand.Parameters["p_idtablempn"].Direction = ParameterDirection.InputOutput; sqlCommand.Parameters.AddWithValue("p_combination_pos", businessObject.Combination_pos); sqlCommand.Parameters["p_combination_pos"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_mpn_index", businessObject.Mpn_index); sqlCommand.Parameters["p_mpn_index"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_limit_lower", businessObject.Limit_lower); sqlCommand.Parameters["p_limit_lower"].NpgsqlDbType = NpgsqlDbType.Numeric; sqlCommand.Parameters.AddWithValue("p_limit_high", businessObject.Limit_high); sqlCommand.Parameters["p_limit_high"].NpgsqlDbType = NpgsqlDbType.Numeric; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.Idtablempn = Convert.ToInt16(sqlCommand.Parameters["p_idtablempn"].Value); return(true); } catch (Exception ex) { throw new Exception("CTablempn::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
public List <T> SelectFromStoreOutParam(string storeName, out object obj, params object[] values) { try { DataSet ds = null; ds = SqlHelper.ExecuteDataset(MainConnection, storeName, values); IDataReader dataReader = ds.Tables[0].CreateDataReader(); obj = ds.Tables[1].Rows[0][0]; return(PopulateObjectsFromReader(dataReader)); } catch (Exception ex) { throw new Exception("SelectFromStoreOutParam::'" + storeName + "'.\n" + ex.Message, ex); } finally { MainConnection.Close(); } }
public bool CapNhatThongTinCaNhan(int TaiKhoan_ID, string TaiKhoan_HoTen, string TaiKhoan_Email, string TaiKhoan_SoDienThoai, string TaiKhoan_AnhDaiDien , DateTime?TaiKhoan_NgaySinh, int TaiKhoan_NguoiCapNhat, string Email_TenHienThi, string Email_MatKhau, string Email_MayChu, int?Email_Cong, string Email_ChuKy, bool ChangPass, string TaiKhoan_MatKhau) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[SP_TAIKHOAN_CAPNHAT_CHITIET]"; sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("@TaiKhoan_ID", TaiKhoan_ID).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@TaiKhoan_HoTen", TaiKhoan_HoTen).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@TaiKhoan_Email", TaiKhoan_Email).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@TaiKhoan_SoDienThoai", TaiKhoan_SoDienThoai).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@TaiKhoan_AnhDaiDien", TaiKhoan_AnhDaiDien).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@TaiKhoan_NgaySinh", TaiKhoan_NgaySinh).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@TaiKhoan_NguoiCapNhat", TaiKhoan_NguoiCapNhat).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@Email_TenHienThi", Email_TenHienThi).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@Email_MatKhau", Email_MatKhau).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@Email_MayChu", Email_MayChu).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@Email_Cong", Email_Cong).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@Email_ChuKy", Email_ChuKy).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@ChangPass", ChangPass).Direction = ParameterDirection.Input; sqlCommand.Parameters.AddWithValue("@TaiKhoan_MatKhau", TaiKhoan_MatKhau).Direction = ParameterDirection.Input; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); return(true); } catch (Exception ex) { return(false); } finally { if (MainConnection.State != ConnectionState.Closed) { MainConnection.Close(); } sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(CPrice_attach businessObject) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_price_attach_Insert"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.AddWithValue("p_idattach", businessObject.Idattach); sqlCommand.Parameters["p_idattach"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters["p_idattach"].Direction = ParameterDirection.InputOutput; sqlCommand.Parameters.AddWithValue("p_idprice", businessObject.Idprice); sqlCommand.Parameters["p_idprice"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_idprice_version", businessObject.Idprice_version); sqlCommand.Parameters["p_idprice_version"].NpgsqlDbType = NpgsqlDbType.Integer; sqlCommand.Parameters.AddWithValue("p_name_document", businessObject.Name_document); sqlCommand.Parameters["p_name_document"].NpgsqlDbType = NpgsqlDbType.Varchar; sqlCommand.Parameters.AddWithValue("p_type_document", businessObject.Type_document); sqlCommand.Parameters["p_type_document"].NpgsqlDbType = NpgsqlDbType.Varchar; MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.Idattach = Convert.ToInt32(sqlCommand.Parameters["p_idattach"].Value); return(true); } catch (Exception ex) { throw new Exception("CPrice_attach::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(KHAMDINHKI businessObject) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[KHAMDINHKI_Insert]"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ID)); sqlCommand.Parameters.Add(new SqlParameter("@BENHNHANID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BENHNHANID)); sqlCommand.Parameters.Add(new SqlParameter("@PHANLOAISUCKHOEID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.PHANLOAISUCKHOEID)); sqlCommand.Parameters.Add(new SqlParameter("@BACSIID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BACSIID)); sqlCommand.Parameters.Add(new SqlParameter("@KHAMTHELUCID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.KHAMTHELUCID)); sqlCommand.Parameters.Add(new SqlParameter("@KHAMCANLAMSANGID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.KHAMCANLAMSANGID)); sqlCommand.Parameters.Add(new SqlParameter("@KHAMCHUYENKHOAID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.KHAMCHUYENKHOAID)); sqlCommand.Parameters.Add(new SqlParameter("@KETLUAN", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.KETLUAN)); sqlCommand.Parameters.Add(new SqlParameter("@THOIGIANKHAM", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.THOIGIANKHAM)); sqlCommand.Parameters.Add(new SqlParameter("@LOAIHINHKHAM", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.LOAIHINHKHAM)); sqlCommand.Parameters.Add(new SqlParameter("@BENHHIEMNGHEO", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BENHHIEMNGHEO)); sqlCommand.Parameters.Add(new SqlParameter("@GHICHU", SqlDbType.NVarChar, 2147483647, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.GHICHU)); MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.ID = (int)sqlCommand.Parameters["@ID"].Value; return(true); } catch //(Exception ex) { return(false); //throw new Exception("KHAMDINHKI::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// Select by primary key /// </summary> /// <param name="keys">primary keys</param> /// <returns>CSolution_interm business object</returns> public CSolution_interm SelectByPrimaryKey(CSolution_intermKeys keys) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_solution_interm_SelectByPrimaryKey"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new NpgsqlParameter("p_idsolution_interm", NpgsqlDbType.Integer, 4, "", ParameterDirection.Input, false, 0, 0, DataRowVersion.Proposed, keys.Idsolution_interm)); MainConnection.Open(); NpgsqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.Read()) { CSolution_interm businessObject = new CSolution_interm(); PopulateBusinessObjectFromReader(businessObject, dataReader); return(businessObject); } else { return(null); } } catch (Exception ex) { throw new Exception("CSolution_interm::SelectByPrimaryKey::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// insert new row in the table /// </summary> /// <param name="businessObject">business object</param> /// <returns>true of successfully insert</returns> public bool Insert(clsEmployee businessObject) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[sp_EMPLOYEE_Insert]"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@EMP_ID", SqlDbType.Int, 4, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Proposed, businessObject.EMP_ID)); sqlCommand.Parameters.Add(new SqlParameter("@LAST_NAME", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.LAST_NAME)); sqlCommand.Parameters.Add(new SqlParameter("@FIRST_NAME", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.FIRST_NAME)); sqlCommand.Parameters.Add(new SqlParameter("@MIDDLE_INITIAL", SqlDbType.Char, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MIDDLE_INITIAL)); sqlCommand.Parameters.Add(new SqlParameter("@NICK_NAME", SqlDbType.VarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.NICK_NAME)); sqlCommand.Parameters.Add(new SqlParameter("@BIRTHDATE", SqlDbType.Text, 3, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BIRTHDATE)); sqlCommand.Parameters.Add(new SqlParameter("@POSITION", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.POSITION)); sqlCommand.Parameters.Add(new SqlParameter("@DATE_HIRED", SqlDbType.Text, 3, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DATE_HIRED)); sqlCommand.Parameters.Add(new SqlParameter("@STATUS", SqlDbType.SmallInt, 2, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.STATUS)); sqlCommand.Parameters.Add(new SqlParameter("@IMAGE_PATH", businessObject.IMAGE_PATH)); sqlCommand.Parameters.Add(new SqlParameter("@GRP_ID", SqlDbType.SmallInt, 2, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.GRP_ID)); MainConnection.Open(); sqlCommand.ExecuteNonQuery(); businessObject.EMP_ID = (int)sqlCommand.Parameters["@EMP_ID"].Value; return(true); } catch (Exception ex) { throw new Exception("clsEmployee::Insert::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// Select by primary key /// </summary> /// <param name="keys">primary keys</param> /// <returns>clsEmployee business object</returns> public clsEmployee SelectByPrimaryKey(clsEmployeeKeys keys) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandText = "dbo.[sp_EMPLOYEE_SelectByPrimaryKey]"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new SqlParameter("@EMP_ID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.EMP_ID)); MainConnection.Open(); IDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.Read()) { clsEmployee businessObject = new clsEmployee(); PopulateBusinessObjectFromReader(businessObject, dataReader); return(businessObject); } else { return(null); } } catch (Exception ex) { throw new Exception("clsEmployee::SelectByPrimaryKey::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }
/// <summary> /// Select by primary key /// </summary> /// <param name="keys">primary keys</param> /// <returns>CConvert_unit_measurement business object</returns> public CConvert_unit_measurement SelectByPrimaryKey(CConvert_unit_measurementKeys keys) { NpgsqlCommand sqlCommand = new NpgsqlCommand(); sqlCommand.CommandText = "public.sp_convert_unit_measurement_SelectByPrimaryKey"; sqlCommand.CommandType = CommandType.StoredProcedure; // Use connection object of base class sqlCommand.Connection = MainConnection; try { sqlCommand.Parameters.Add(new NpgsqlParameter("p_idconvert", NpgsqlDbType.Smallint, 2, "", ParameterDirection.Input, false, 0, 0, DataRowVersion.Proposed, keys.Idconvert)); MainConnection.Open(); NpgsqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.Read()) { CConvert_unit_measurement businessObject = new CConvert_unit_measurement(); PopulateBusinessObjectFromReader(businessObject, dataReader); return(businessObject); } else { return(null); } } catch (Exception ex) { throw new Exception("CConvert_unit_measurement::SelectByPrimaryKey::Error occured.", ex); } finally { MainConnection.Close(); sqlCommand.Dispose(); } }