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