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();
}
}
