public string insertDetail(personEntity p)
{
try
{
logger.Info("Control Flow : insertDetail service interface Method started");
personBAL insertOp = new personBAL();
logger.Info("Insert operation called from Bussiness logic class");
string value = insertOp.Insert(p);
logger.Info("Result of the Insert opertaion is returned");
logger.Info("Control Flow : insertDetail service interface Method stopped");
return value;
}
catch (Exception ex)
{
logger.Error("Error occured in insert operation of personBAL class");
logger.Info("Control Flow : insertDetail service interface Method stopped");
throw(ex);
}
}
public string updateDetail(personEntity p)
{
try
{
logger.Info("Control Flow : updateDetail service interface Method Started");
personBAL updateOp = new personBAL();
logger.Info("Update operation called from Bussiness logic class");
string value = updateOp.Update(p);
logger.Info("Result of the Update opertaion is returned");
logger.Info("Control Flow : updateDetail service interface Method Stopped");
return value;
}
catch (Exception ex)
{
logger.Error("Error occured in update operation of personBAL class");
logger.Info("Control Flow : updateDetail service interface Method Stopped");
throw (ex);
}
}
public int insertion(personEntity p)
{
logger.Info("Control Flow : insertion method of personDAL.cs");
logger.Info("SQL Connection to the database");
DataBaseLayer dbconstr = new DataBaseLayer();
string connStr = dbconstr.connectionInfo;
//string connStr = "data source=BGHWF4002\\SQLEXPRESS;Initial Catalog=sample; Integrated Security =True;";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
logger.Info("SQL Query insert command executed from table emp_detail for inserting the data");
string insertString = @"INSERT into emp_details(FirstName,LastName,Age) VALUES (@fname,@lname,@age)";
SqlCommand i_cmd = new SqlCommand(insertString, conn);
try
{
logger.Info("SQL insert Query parameters initialised");
i_cmd.Parameters.AddWithValue("@fname", p.firstName);
i_cmd.Parameters.AddWithValue("@lname", p.lastName);
i_cmd.Parameters.AddWithValue("@age", p.age);
sqlDBLog.sqlCmdLog(i_cmd);
return (i_cmd.ExecuteNonQuery());
}
catch(Exception ex)
{
logger.Error("SQL insert Query parameter not initialised with valid value");
throw new myCustomException(302, ex.Message);
}
finally
{
i_cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
public string Insert(personEntity person)
{
personDAL insertOP = new personDAL();
try
{
logger.Info("Insert operation called from Data Access Layer Class");
if ( person.firstName == string.Empty || person.lastName == string.Empty || person.age == 0)
{
logger.Warn("Invalid parameters passed so delete opertion unsuccessfull");
return "Error while inserting the data as parameters passed are not valid";
}
int res = insertOP.insertion(person);
if (res == 0)
{
logger.Error("Error in inserting data to the emp_detail table");
return "Error while inserting the data";
}
else
{
logger.Info("Successfully entered the data into the emp_detail table");
return "Successfully inserted the data";
}
}
catch
{
throw;
}
finally
{
insertOP = null;
}
}
public string insertDetail( personEntity p)
{
try
{
logger.Info("Control Flow : insertDetail web Method started");
string access = AuthHeader.authenticate();
string result = string.Empty;
if (AuthHeader.authFlag == 1)
{
logger.Info("Authenticated successfully : " + access);
serviceInterface insertDetailSI = new serviceInterface();
result = insertDetailSI.insertDetail(p);
}
else
{
result = access;
logger.Fatal("Authentication failed : " + access);
}
logger.Info("Control Flow : insertDetail web Method stopped");
return result;
}
catch (Exception ex)
{
logger.Error("Control Flow : insertDetail web Method stopped");
ThrowSoapException soapExp = new ThrowSoapException();
soapExp.convertToSoapException(ex);
return string.Empty;
}
}
public int updation(personEntity p, int[] entry)
{
logger.Info("Control Flow : updation method of personDAL.cs");
logger.Info("SQL Connection to the database");
DataBaseLayer dbconstr = new DataBaseLayer();
string connStr = dbconstr.connectionInfo;
//string connStr = "data source=BGHWF4002\\SQLEXPRESS;Initial Catalog=sample; Integrated Security =True;";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
logger.Info("SQL Query update command executed from table emp_detail for updating the data");
string updateString = "UPDATE emp_details set ";
if (entry[0] == 1)
updateString = updateString + "FirstName = @fname ";
if (entry[1] == 1)
updateString = updateString + ",LastName = @lname, ";
if (entry[2] == 1)
updateString = updateString + "Age = @age ";
updateString = updateString + "where UserId = @id ";
SqlCommand u_cmd = new SqlCommand(updateString, conn);
try
{
logger.Info("SQL update Query parameters initialised");
u_cmd.Parameters.AddWithValue("@id", p.userId);
u_cmd.Parameters.AddWithValue("@fname", p.firstName);
u_cmd.Parameters.AddWithValue("@lname", p.lastName);
u_cmd.Parameters.AddWithValue("@age", p.age);
sqlDBLog.sqlCmdLog(u_cmd);
logger.Info("SQL update Query executed and the result is returned");
return (u_cmd.ExecuteNonQuery());
}
catch(Exception ex)
{
logger.Error("SQL update Query parameter not initialised with valid value");
throw new myCustomException(303, ex.Message);
}
finally
{
u_cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
public personEntity[] retrieve()
{
logger.Info("Control Flow : retrieve method of personDAL.cs");
logger.Info("SQL Connection to the database");
DataBaseLayer dbconstr = new DataBaseLayer();
//string connStr = dbconstr.getConnectionStringDetails();
string connStr = dbconstr.connectionInfo;
//string connStr = "data source=BGHWF4002\\SQLEXPRESS;Initial Catalog=sample; Integrated Security =True;";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
// dbConnection db = new dbConnection();
// SqlConnection conn = db.db_user_details;
SqlDataAdapter da = new SqlDataAdapter();
logger.Info("SQL Query select command executed from table emp_detail for retrieving the data");
string selectString = @"SELECT * FROM emp_details ORDER BY UserId";
//SqlParameter param = new SqlParameter();
//param.ParameterName = "@id";
//param.Value = 11;
SqlCommand s_cmd = new SqlCommand(selectString, conn);
sqlDBLog.sqlCmdLog(s_cmd);
// logger.Debug("SQL Query : " + selectString);
//cmd.Parameters.Add(param);
da.SelectCommand = s_cmd;
DataSet dSet = new DataSet();
da.Fill(dSet);
personEntity[] per = new personEntity[dSet.Tables[0].Rows.Count];
int i = 0;
try
{
logger.Info("Data from the dataset (da) is loaded into person object");
logger.Debug("Values retrived from the table : ");
foreach (DataRow dr in dSet.Tables[0].Rows)
{
personEntity p = new personEntity();
p.userId = Convert.ToInt32(dr[0]);
p.firstName = dr[1].ToString();
p.lastName = dr[2].ToString();
p.age = Convert.ToInt32(dr[3]);
logger.Debug("Person "+i+" : "+ p);
per[i++] = p;
}
}
catch(Exception ex)
{
logger.Error("Error occured while loading data from dataset (dSet) into person object");
throw new myCustomException(301,ex.Message);
}
finally
{
s_cmd.Dispose();
dSet.Dispose();
conn.Close();
conn.Dispose();
}
logger.Info("Array of person objects containing all details in the emp_detail table returned");
return per;
}
public string Update(personEntity person)
{
personDAL updateOP = new personDAL();
try
{
int[] entry = { 0, 0, 0 };
if (person.userId == 0)
return "Error while updating the data please enter a empId";
if (!(person.firstName == string.Empty))
entry[0] = 1;
if (!(person.lastName == string.Empty))
entry[1] = 1;
if (!(person.age == 0))
entry[2] = 1;
logger.Info("Update operation called from Data Access Layer Class");
int res = updateOP.updation(person, entry);
if (res == 0)
{
logger.Error("Error in updating data to the emp_detail table");
return "Error while updating the data please enter a valid empId";
}
else
{
logger.Info("Successfully updated the data into the emp_detail table");
return "Successfully updated the data";
}
}
catch
{
throw;
}
finally
{
updateOP = null;
}
}
