public IEnumerable <Employee> SelEmployee()
{
int intRC = 0;
objEmployees = new List <Employee>();
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeID: EmployeeID, EmployeeName: EmployeeName);
System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelEmployees", new ADOConnectionFactory().Connection);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add(objParams.Parmeters["RC"]);
try
{
objCmd.Connection.Open();
System.Data.SqlClient.SqlDataReader objDR = objCmd.ExecuteReader();
while (objDR.Read())
{
Employee objE = new Employee();
objE.objEmployeeID = (int)objDR["EmployeeID"];
objE.objEmployeeName = (string)objDR["EmployeeName"];
((List <Employee>)objEmployees).Add(objE);
}
objDR.Close(); //You cannot get the return value if the reader is not closed!
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString());
}
objCmd.Connection.Close();
}
catch (Exception) { throw; }
return(objEmployees);
}
public int DelEmployee(int EmployeeID)
{
int intRC = 0;
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeID: EmployeeID, EmployeeName: EmployeeName);
System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pDelEmployees", new ADOConnectionFactory().Connection);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add(objParams.Parmeters["RC"]);
objCmd.Parameters.Add(objParams.Parmeters["EmployeeID"]);
try
{
objCmd.Connection.Open();
objCmd.ExecuteNonQuery();
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString());
}
objCmd.Connection.Close();
}
catch (Exception) { throw; }
return(intRC);
}
private static int EFOption(string EmployeeName, out int NewRowID)
{
//Set up the variables
int intRC = 0; //Used to trap the Stored Procedure's return code
//Create a Connection object using Entity Framework (EF)
IObjectContextAdapter Context = new EFDbContext();
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeName: EmployeeName);
//-- Change the RC parameter from ReturnValue to Output, since the EF does not support ReturnValue (Crazy!)
objParams.Parmeters["RC"].Direction = ParameterDirection.Output;
//Create and configure an ADO.NET command object
//-- Create a SQL command string
string strSQLCode = @"Exec @RC = pInsEmployees" +
" @EmployeeName = '" + objParams.Parmeters["EmployeeName"] + "'" + // Don't forget the SINGLE Quotes!!!
",@NewRowID = @NewRowID out;";
//configure the command object and execute it
try
{
Context.ObjectContext.ExecuteStoreCommand(strSQLCode
, objParams.Parmeters["RC"]
, objParams.Parmeters["EmployeeName"]
, objParams.Parmeters["NewRowID"]
);
//Get the new row ID created by the table's Identity feature
if (objParams.Parmeters["NewRowID"].Value is DBNull)
{
NewRowID = 0;
} //if the insert has failed, then set this to an arbitrary number
else
{
NewRowID = (int)objParams.Parmeters["NewRowID"].Value;
} //else send it back as output
//Trap or return the Stored Procedure's return code
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString());
}
}
catch (Exception)
{
throw;
}
return(intRC);
}
private static int ADONetOption(string EmployeeName, out int NewRowID)
{
//Set up the variables
int intRC = 0; //Used to trap the Stored Procedure's return code
//Create a Connection object using my custom connection factory
System.Data.SqlClient.SqlConnection objCon = new ADOConnectionFactory().Connection;
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeName: EmployeeName);
//Create and configure an ADO.NET command object
System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("", objCon);
objCmd.Parameters.Add(objParams.Parmeters["RC"]);
objCmd.Parameters.Add(objParams.Parmeters["EmployeeName"]);
objCmd.Parameters.Add(objParams.Parmeters["NewRowID"]);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "pInsEmployees";
try
{
objCmd.Connection.Open();
objCmd.ExecuteNonQuery();
//Get the new row ID created by the table's Identity feature
if (objParams.Parmeters["NewRowID"].Value is DBNull)
{
NewRowID = 0;
} //if the insert has failed, then set this to an arbitrary number
else
{
NewRowID = (int)objParams.Parmeters["NewRowID"].Value;
} //else send it back as output
//Trap or return the Stored Procedure's return code
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString());
}
objCmd.Connection.Close();
}
catch (Exception) { throw; }
return(intRC);
}
private static int ADONetOption(string EmployeeName, out int NewRowID)
{
//Set up the variables
int intRC = 0; //Used to trap the Stored Procedure's return code
//Create a Connection object using my custom connection factory
System.Data.SqlClient.SqlConnection objCon = new ADOConnectionFactory().Connection;
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeName: EmployeeName);
//Create and configure an ADO.NET command object
System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("", objCon);
objCmd.Parameters.Add(objParams.Parameters["RC"]);
objCmd.Parameters.Add(objParams.Parameters["EmployeeName"]);
objCmd.Parameters.Add(objParams.Parameters["NewRowID"]);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "pInsEmployees";
try
{
objCmd.Connection.Open();
objCmd.ExecuteNonQuery();
//Get the new row ID created by the table's Identity feature
if (objParams.Parameters["NewRowID"].Value is DBNull)
{ NewRowID = 0; } //if the insert has failed, then set this to an arbitrary number
else { NewRowID = (int)objParams.Parameters["NewRowID"].Value; } //else send it back as output
//Trap or return the Stored Procedure's return code
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{ throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString()); }
objCmd.Connection.Close();
}
catch (Exception) { throw; }
return intRC;
}
public IEnumerable<Employee> SelEmployee()
{
//Set up the variables
int intRC = 0;
SqlDataReader dr;
List<Employee> retVal = new List<Employee>();
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeesParameterFactory();
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add((objParams.Parameters["RC"]));
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pSelEmployees";
dr = sqlCommand.ExecuteReader();
while (dr.Read())
{
Employee empl = new Employee();
empl.EmployeeID = (int) dr["EmployeeID"];
empl.EmployeeName = (string) dr["EmployeeName"];
retVal.Add(empl);
}
dr.Close();
//check the return code for errors
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return retVal;
}
public int DelEmployee(int EmployeeID)
{
//Set up the variables
int intRC = 0; //Used to trap the Stored Procedure's return code
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeID);
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
//add parameters to procedure
sqlCommand.Parameters.Add(objParams.Parameters["RC"]);
sqlCommand.Parameters.Add(objParams.Parameters["EmployeeID"]);
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pDelEmployees";
sqlCommand.ExecuteNonQuery();
//Trap or return the Stored Procedure's return code
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return intRC;
}
public IEnumerable<Employee> SelEmployee(int EmployeeID = -1)
{
int intRC = 0;
objEmployees = new List<Employee>();
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeID: EmployeeID, EmployeeName: EmployeeName);
System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelEmployees", new ADOConnectionFactory().Connection);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add(objParams.Parmeters["RC"]);
objCmd.Parameters.Add(objParams.Parmeters["EmployeeID"]);
try
{
objCmd.Connection.Open();
System.Data.SqlClient.SqlDataReader objDR = objCmd.ExecuteReader();
while (objDR.Read())
{
Employee objE = new Employee();
objE.intEmployeeID = (int)objDR["EmployeeID"];
objE.strEmployeeName = (string)objDR["EmployeeName"];
((List<Employee>)objEmployees).Add(objE);
}
objDR.Close(); //You cannot get the return value if the reader is not closed!
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{ throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString()); }
objCmd.Connection.Close();
}
catch (Exception) { throw; }
return objEmployees;
}
public int DelEmployee(int EmployeeID)
{
int intRC = 0;
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeID: EmployeeID, EmployeeName: EmployeeName);
System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pDelEmployees", new ADOConnectionFactory().Connection);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add(objParams.Parmeters["RC"]);
objCmd.Parameters.Add(objParams.Parmeters["EmployeeID"]);
try
{
objCmd.Connection.Open();
objCmd.ExecuteNonQuery();
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{ throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString()); }
objCmd.Connection.Close();
}
catch (Exception) { throw; }
return intRC;
}
private static int EFOption(string EmployeeName, out int NewRowID)
{
//Set up the variables
int intRC = 0; //Used to trap the Stored Procedure's return code
//Create a Connection object using Entity Framework (EF)
IObjectContextAdapter Context = new EFDbContext();
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeesParameterFactory(EmployeeName: EmployeeName);
//-- Change the RC parameter from ReturnValue to Output, since the EF does not support ReturnValue (Crazy!)
objParams.Parmeters["RC"].Direction = ParameterDirection.Output;
//Create and configure an ADO.NET command object
//-- Create a SQL command string
string strSQLCode = @"Exec @RC = pInsEmployees" +
" @EmployeeName = '" + objParams.Parmeters["EmployeeName"] + "'" + // Don't forget the SINGLE Quotes!!!
",@NewRowID = @NewRowID out;";
//configure the command object and execute it
try
{
Context.ObjectContext.ExecuteStoreCommand(strSQLCode
, objParams.Parmeters["RC"]
, objParams.Parmeters["EmployeeName"]
, objParams.Parmeters["NewRowID"]
);
//Get the new row ID created by the table's Identity feature
if (objParams.Parmeters["NewRowID"].Value is DBNull)
{ NewRowID = 0; } //if the insert has failed, then set this to an arbitrary number
else { NewRowID = (int)objParams.Parmeters["NewRowID"].Value; } //else send it back as output
//Trap or return the Stored Procedure's return code
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{ throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString()); }
}
catch (Exception)
{
throw;
}
return intRC;
}
