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