Exemple #1
0
        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);
        }
Exemple #2
0
        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);
        }
Exemple #3
0
        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);
        }
Exemple #4
0
        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;
        }