private void btnSave_Click(object sender, EventArgs e)
{
DataGridView dgv = dgvEstimation;
Dictionary<int, ELEstimation> objDic = new Dictionary<int, ELEstimation>();
ELEstimation objEL;
int newID = 0;
for (int i = 0; i < dgv.Rows.Count; i++)
{
if(Convert.ToBoolean(dgv.Rows[i].Cells[0].Value))
{
objEL = new ELEstimation();
newID = Common.GetNewID(tableName);
objEL.ID = newID;
objEL.Code = "Estm" + newID.ToString();
objEL.Creator = 0;
objEL.Created = DateTime.Now;
objEL.IsActive = true;
objEL.QualityType =Convert.ToString(dgv.Rows[i].Cells["Quality"].Value);
objEL.RatePerUnit = Convert.ToInt16(dgv.Rows[i].Cells["Rate/Unit"].Value);
objEL.Site = "Site1";
objEL.TotalCost = Convert.ToInt16(dgv.Rows[i].Cells["Cost"].Value);
objEL.Units = Convert.ToInt16(dgv.Rows[i].Cells["Units"].Value);
objEL.UnitType = "Type1";
objDic.Add(i, objEL);
dgv.Rows[dgvEstimation.Rows.Count - 1].Cells[0].Value = false;
}
}
DLEstimation.AddALL(objDic);
}
public static ELEstimation FetchByID(int ID = 0, string Code = "")
{
SqlCommand cmd;
string qry = "";
ELEstimation ObjEL = new ELEstimation();
SqlDataReader dr;
try
{
conn.CreatConnection();
qry = "SELECT * FROM Estimation WHERE ";
if (Code != "")
qry = qry + " code =@Code ";
else if (ID > 0)
qry = qry + " ID =@ID";
else
qry = qry + " ID =@ID";
cmd = new SqlCommand(qry, conn.con);
SqlParameter param;
param = new SqlParameter("@ID", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = ID;
cmd.Parameters.Add(param);
param = new SqlParameter("@Code", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = Code;
cmd.Parameters.Add(param);
foreach (SqlParameter Parameter in cmd.Parameters)
{
if (Parameter.Value == null)
{
Parameter.Value = DBNull.Value;
}
}
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
ObjEL.ID = Convert.ToInt32(dr["id"]);
ObjEL.Code = dr["Code"].ToString();
ObjEL.Name = dr["Name"].ToString();
ObjEL.Site = dr["Site"].ToString();
ObjEL.QualityType = dr["QualityType"].ToString();
ObjEL.Units = Convert.ToInt32(dr["Units"]);
ObjEL.UnitType = dr["UnitType"].ToString();
ObjEL.RatePerUnit = Convert.ToInt32(dr["RatePerUnit"]);
ObjEL.TotalCost = Convert.ToInt32(dr["TotalCost"]);
ObjEL.IsActive = Convert.ToBoolean(dr["IsActive"]);
ObjEL.Creator = Convert.ToInt32(dr["Creator"]);
ObjEL.Created = Convert.ToDateTime(dr["Created"]);
}
dr.Close();
return ObjEL;
}
catch (Exception ex)
{
UtilityLayer.Common.ErrorLog(DateTime.Now.ToString() + ex.Message + " " + ex.StackTrace + " " + "DLCompany - FetchByID");
return null;
}
finally
{
conn.CloseConnection();
}
}
public static int Add(ELEstimation objEL)
{
int retValue = 0;
SqlCommand cmd;
string qry = "";
object value;
try
{
conn.CreatConnection();
qry = "";
qry = "SELECT COUNT(ID) FROM Estimation WHERE ID =@ID ";
cmd = new SqlCommand(qry, conn.con);
SqlParameter param;
param = new SqlParameter("@ID", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.ID;
cmd.Parameters.Add(param);
foreach (SqlParameter Parameter in cmd.Parameters)
{
if (Parameter.Value == null)
{
Parameter.Value = DBNull.Value;
}
}
value = cmd.ExecuteScalar();
if (value != null && value.ToString() != "0")
{
qry = "";
qry = "UPDATE Estimation SET Code=@Code, Site =@Site ,QualityType=@QualityType,Units=@Units, "
+ "UnitType=@UnitType,RatePerUnit=@RatePerUnit,TotalCost=@TotalCost,IsActive=@IsActive WHERE ID=@ID";
cmd = new SqlCommand(qry, conn.con);
param = new SqlParameter("@ID", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.ID;
cmd.Parameters.Add(param);
param = new SqlParameter("@Code", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = objEL.Code;
cmd.Parameters.Add(param);
param = new SqlParameter("@Site", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = objEL.Site;
cmd.Parameters.Add(param);
param = new SqlParameter("@QualityType", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = objEL.QualityType;
cmd.Parameters.Add(param);
param = new SqlParameter("@Units", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.Units;
cmd.Parameters.Add(param);
param = new SqlParameter("@UnitType", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = objEL.UnitType;
cmd.Parameters.Add(param);
param = new SqlParameter("@RatePerUnit", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.RatePerUnit;
cmd.Parameters.Add(param);
param = new SqlParameter("@TotalCost", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.TotalCost;
cmd.Parameters.Add(param);
param = new SqlParameter("@isActive", SqlDbType.Bit);
param.Direction = ParameterDirection.Input;
param.Value = objEL.IsActive;
cmd.Parameters.Add(param);
foreach (SqlParameter Parameter in cmd.Parameters)
{
if (Parameter.Value == null)
{
Parameter.Value = DBNull.Value;
}
}
retValue = cmd.ExecuteNonQuery();
}
else
{
qry = "";
qry = "INSERT INTO Estimation ([ID],[Code],[Site],[QualityType],[Units],[UnitType],[RatePerUnit],[TotalCost],[Creator],[Created],[IsActive]) " +
" VALUES(@ID, @Code, @Site, @QualityType, @Units, @UnitType,@RatePerUnit,@TotalCost,@Creator,Getdate(),@IsActive) ";
cmd = new SqlCommand(qry, conn.con);
param = new SqlParameter("@ID", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.ID;
cmd.Parameters.Add(param);
param = new SqlParameter("@Code", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = objEL.Code;
cmd.Parameters.Add(param);
param = new SqlParameter("@Site", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = objEL.Site;
cmd.Parameters.Add(param);
param = new SqlParameter("@QualityType", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = objEL.QualityType;
cmd.Parameters.Add(param);
param = new SqlParameter("@Units", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.Units;
cmd.Parameters.Add(param);
param = new SqlParameter("@UnitType", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = objEL.UnitType;
cmd.Parameters.Add(param);
param = new SqlParameter("@RatePerUnit", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.RatePerUnit;
cmd.Parameters.Add(param);
param = new SqlParameter("@TotalCost", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.TotalCost;
cmd.Parameters.Add(param);
param = new SqlParameter("@Creator", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = objEL.Creator;
cmd.Parameters.Add(param);
param = new SqlParameter("@Created", SqlDbType.DateTime);
param.Direction = ParameterDirection.Input;
param.Value = objEL.Created;
cmd.Parameters.Add(param);
param = new SqlParameter("@isActive", SqlDbType.Bit);
param.Direction = ParameterDirection.Input;
param.Value = objEL.IsActive;
cmd.Parameters.Add(param);
foreach (SqlParameter Parameter in cmd.Parameters)
{
if (Parameter.Value == null)
{
Parameter.Value = DBNull.Value;
}
}
retValue = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
UtilityLayer.Common.ErrorLog(DateTime.Now.ToString() + ex.Message + " " + ex.StackTrace + " " + "DLCompany - Add");
return 0;
}
finally
{
conn.CloseConnection();
}
return retValue;
}