コード例 #1
0
        public IHttpActionResult EFSaveLeavetoExceed(SaveLeave saveleave)
        {
            string sqlQuery;

            SqlParameter[] sqlParams;

            List <SqlParameter> parameter = new List <SqlParameter>();


            SaveLeaveReturns result;



            if (!ModelState.IsValid)
            {
                var errors = ModelState.SelectMany(x => x.Value.Errors.Select(z => z.Exception));

                return(BadRequest());
            }



            try
            {
                //uncomment
                sqlQuery = "spSaveLeaveZenith @employee_number, @daysAppliedFor, @startDate, @EndDate, @resumption_date, @reasonForLeave, @address, " +
                           "@phoneNo, @leave_id, @addMoredays, @AddDaysPrevYr, @leaveUnique_id, @org_id, @backofficer_number, @errorcode, @message";


                parameter.Add(new SqlParameter("@employee_number", saveleave.employee_number));
                parameter.Add(new SqlParameter("@daysAppliedFor", saveleave.daysAppliedFor));
                parameter.Add(new SqlParameter("@startDate", saveleave.startDate));
                parameter.Add(new SqlParameter("@EndDate", saveleave.EndDate));
                parameter.Add(new SqlParameter("@resumption_date", saveleave.resumption_date));
                parameter.Add(new SqlParameter("@reasonForLeave", saveleave.reasonForLeave));
                parameter.Add(new SqlParameter("@address", saveleave.address));
                parameter.Add(new SqlParameter("@phoneNo", saveleave.phoneNo));
                parameter.Add(new SqlParameter("@leave_id", saveleave.leave_id));
                parameter.Add(new SqlParameter("@addMoredays", saveleave.addMoredays));
                parameter.Add(new SqlParameter("@AddDaysPrevYr", saveleave.AddDaysPrev));
                parameter.Add(new SqlParameter("@leaveUnique_id", saveleave.leaveUnique_id));
                parameter.Add(new SqlParameter("@org_id", saveleave.org_id));
                parameter.Add(new SqlParameter("@backofficer_number", saveleave.backofficer_number));

                var outputParam = new SqlParameter("@errorcode", SqlDbType.Int, 10);
                outputParam.Direction = ParameterDirection.Output;
                // outputParam.Value = 0;
                parameter.Add(outputParam);

                var outputParam2 = new SqlParameter("@message", SqlDbType.VarChar, 200);
                outputParam2.Direction = ParameterDirection.Output;
                //outputParam2.Value = "";
                parameter.Add(outputParam2);

                //end of uncomment

                //     sqlParams = new SqlParameter[]
                //     {
                //         new SqlParameter{ParameterName = "@employee_number", Value = saveleave.employee_number, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@daysAppliedFor", Value = saveleave.daysAppliedFor, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@startDate", Value = saveleave.startDate, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@EndDate", Value = saveleave.EndDate, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@resumption_date", Value = saveleave.resumption_date, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@reasonForLeave", Value = saveleave.reasonForLeave, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@address", Value = saveleave.address, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@phoneNo", Value = saveleave.phoneNo, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@leave_id", Value = saveleave.leave_id, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@addMoredays", Value = saveleave.addMoredays, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@AddDaysPrevYr", Value = saveleave.AddDaysPrev, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@leaveUnique_id", Value = saveleave.leaveUnique_id, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@org_id", Value = saveleave.org_id, Direction = System.Data.ParameterDirection.Input},
                //         new SqlParameter{ParameterName = "@backofficer_number", Value = saveleave.backofficer_number, Direction = System.Data.ParameterDirection.Input},
                //         //new SqlParameter{ParameterName = "@errorcode", Value = saveleave.errorcode, Direction = System.Data.ParameterDirection.InputOutput},
                //         //new SqlParameter{ParameterName = "@message", Value = saveleave.message, Direction = System.Data.ParameterDirection.InputOutput},

                //};



                var rStructure = _context.Database.SqlQuery <SaveLeaveReturns>(sqlQuery, parameter.ToArray()).Single();

                //var rStructure = _context.Database.ExecuteSqlCommand(sqlQuery, parameter.ToArray());

                var errcode = (outputParam.SqlValue).ToString();
                var errMsg  = (outputParam2.SqlValue).ToString();

                //Console.WriteLine(rStructure);
            }
            catch (Exception ex)
            {
                throw;
            }


            return(Ok());
        }
コード例 #2
0
        public IHttpActionResult SaveLeavetoExceed(SaveLeave saveleave)
        {
            string sqlQuery;

            SqlParameter[] sqlParams;

            List <SqlParameter>     parameter = new List <SqlParameter>();
            List <SaveLeaveReturns> returns   = new List <SaveLeaveReturns>();

            SaveLeaveReturns result;

            string connectionString = ConfigurationManager.ConnectionStrings["XceedTestLeaveDB"].ToString();


            if (!ModelState.IsValid)
            {
                var errors = ModelState.SelectMany(x => x.Value.Errors.Select(z => z.Exception));

                return(BadRequest());
            }



            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("spSaveLeaveZenith", connection);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@employee_number", saveleave.employee_number));
                    cmd.Parameters.Add(new SqlParameter("@daysAppliedFor", saveleave.daysAppliedFor));
                    cmd.Parameters.Add(new SqlParameter("@startDate", saveleave.startDate));
                    cmd.Parameters.Add(new SqlParameter("@EndDate", saveleave.EndDate));
                    cmd.Parameters.Add(new SqlParameter("@resumption_date", saveleave.resumption_date));
                    cmd.Parameters.Add(new SqlParameter("@reasonForLeave", saveleave.reasonForLeave));
                    cmd.Parameters.Add(new SqlParameter("@address", saveleave.address));
                    cmd.Parameters.Add(new SqlParameter("@phoneNo", saveleave.phoneNo));
                    cmd.Parameters.Add(new SqlParameter("@leave_id", saveleave.leave_id));
                    cmd.Parameters.Add(new SqlParameter("@addMoredays", saveleave.addMoredays));
                    cmd.Parameters.Add(new SqlParameter("@AddDaysPrevYr", saveleave.AddDaysPrev));
                    cmd.Parameters.Add(new SqlParameter("@leaveUnique_id", saveleave.leaveUnique_id));
                    cmd.Parameters.Add(new SqlParameter("@org_id", saveleave.org_id));
                    cmd.Parameters.Add(new SqlParameter("@backofficer_number", saveleave.backofficer_number));


                    var outputParam = new SqlParameter("@errorcode", SqlDbType.Int, 10);
                    outputParam.Direction = ParameterDirection.Output;
                    outputParam.Value     = 0;
                    cmd.Parameters.Add(outputParam);

                    var outputParam2 = new SqlParameter("@message", SqlDbType.VarChar, 200);
                    outputParam2.Direction = ParameterDirection.Output;
                    outputParam2.Value     = "";
                    cmd.Parameters.Add(outputParam2);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        var count1 = cmd.Parameters["@errorcode"].Value.ToString();

                        result = new SaveLeaveReturns
                        {
                            errorcode = int.Parse(cmd.Parameters["@errorcode"].Value.ToString()),
                            message   = cmd.Parameters["@message"].Value.ToString()
                        };
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }


            return(Created(new Uri(Request.RequestUri.ToString()), result));
        }