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