public IEnumerable <Project> SelProjects() { int intRC = 0; objProjects = new List <Project>(); IParameterFactory objParams = new ProjectsParameterFactory(ProjectID: ProjectID, ProjectName: ProjectName, ProjectDescription: ProjectDescription); System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelProjects", 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()) { Project objP = new Project(); objP.objProjectID = (int)objDR["ProjectID"]; objP.objProjectName = (string)objDR["ProjectName"]; ((List <Project>)objProjects).Add(objP); } objDR.Close(); 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(objProjects); }
public int DelProject(int ProjectID) { int intRC = 0; IParameterFactory objParams = new ProjectsParameterFactory(ProjectID: ProjectID, ProjectName: ProjectName); System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pDelProjects", new ADOConnectionFactory().Connection); objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add(objParams.Parmeters["RC"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectID"]); 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); }
public int InsProject(string ProjectName, string ProjectDescription, out int NewRowID) { 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 ProjectsParameterFactory(ProjectName: ProjectName, ProjectDescription: ProjectDescription); //Create and configure an ADO.NET command object System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pInsProjects", objCon); objCmd.Parameters.Add(objParams.Parmeters["RC"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectName"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectDescription"]); objCmd.Parameters.Add(objParams.Parmeters["NewRowID"]); objCmd.CommandType = CommandType.StoredProcedure; 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); }
public int InsProject(string ProjectName, string ProjectDescription, out int NewRowID) { int intRC = 0; System.Data.SqlClient.SqlConnection objCon = new ADOConnectionFactory().Connection; IParameterFactory objParams = new ProjectsParameterFactory(ProjectName: ProjectName, ProjectDescription: ProjectDescription); System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("", objCon); objCmd.Parameters.Add(objParams.Parmeters["RC"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectName"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectDescription"]); objCmd.Parameters.Add(objParams.Parmeters["NewRowID"]); objCmd.CommandType = CommandType.StoredProcedure; objCmd.CommandText = "pInsProjects"; try { objCmd.Connection.Open(); objCmd.ExecuteNonQuery(); if (objParams.Parmeters["NewRowID"].Value is DBNull) { NewRowID = 0; } else { NewRowID = (int)objParams.Parmeters["NewRowID"].Value; } 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); }
public IEnumerable<Project> SelProjects() { //Set up the variables int intRC = 0; SqlDataReader dr; List<Project> retVal = new List<Project>(); //Set up the parameters using my custom parameter factory IParameterFactory objParams = new ProjectsParameterFactory(); 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 = "pSelProjects"; dr = sqlCommand.ExecuteReader(); while (dr.Read()) { Project prjt = new Project(); prjt.ProjectID = (int)dr["ProjectID"]; prjt.ProjectName = (string)dr["ProjectName"]; prjt.ProjectDescription = (string)dr["ProjectDescription"]; retVal.Add(prjt); } 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 DelProject(int ProjectID) { //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 ProjectsParameterFactory(ProjectID); 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["ProjectID"]); //call stored procedure to insert record sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = "pDelProjects"; 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 int InsProject(string ProjectName, string ProjectDescription, out int NewRowID) { //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 ProjectsParameterFactory(ProjectName : ProjectName, ProjectDescription: ProjectDescription); 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["ProjectName"]); sqlCommand.Parameters.Add(objParams.Parameters["ProjectDescription"]); sqlCommand.Parameters.Add(objParams.Parameters["NewRowID"]); //call stored procedure to insert record sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = "pInsProjects"; sqlCommand.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()); } } } } catch (Exception) { throw; } return intRC; }
public IEnumerable<Project> SelProject(int ProjectID = -1) { int intRC = 0; objProjects = new List<Project>(); IParameterFactory objParams = new ProjectsParameterFactory(ProjectID: ProjectID, ProjectName: ProjectName); System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelProjects", new ADOConnectionFactory().Connection); objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add(objParams.Parmeters["RC"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectID"]); try { objCmd.Connection.Open(); System.Data.SqlClient.SqlDataReader objDR = objCmd.ExecuteReader(); while (objDR.Read()) { Project objP = new Project(); objP.intProjectID = (int)objDR["ProjectID"]; objP.strProjectName = (string)objDR["ProjectName"]; objP.strProjectDescription = (string)objDR["ProjectDescription"]; ((List<Project>)objProjects).Add(objP); } 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 objProjects; }
public int DelProject(int ProjectID) { int intRC = 0; IParameterFactory objParams = new ProjectsParameterFactory(ProjectID: ProjectID); System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pDelProjects", new ADOConnectionFactory().Connection); objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add(objParams.Parmeters["RC"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectID"]); 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; }
public int InsProject(string ProjectName, string ProjectDescription, out int NewRowID) { 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 ProjectsParameterFactory(ProjectName: ProjectName, ProjectDescription: ProjectDescription); //Create and configure an ADO.NET command object System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pInsProjects", objCon); objCmd.Parameters.Add(objParams.Parmeters["RC"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectName"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectDescription"]); objCmd.Parameters.Add(objParams.Parmeters["NewRowID"]); objCmd.CommandType = CommandType.StoredProcedure; 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; }