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 int deletion(int id)
{
logger.Info("Control Flow : deletion 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 delete command executed from table emp_detail for deleting the data");
string deleteString = @"DELETE FROM emp_details where UserId = @id";
SqlCommand d_cmd = new SqlCommand(deleteString, conn);
try
{
logger.Info("SQL delete Query parameters initialised");
d_cmd.Parameters.AddWithValue("@id", id);
sqlDBLog.sqlCmdLog(d_cmd);
logger.Info("SQL delete Query executed and the result is returned");
return (d_cmd.ExecuteNonQuery());
}
catch(Exception ex)
{
logger.Error("SQL delete Query parameter not initialised with valid value");
throw new myCustomException(304, ex.Message);
}
finally
{
d_cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
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;
}
