public JsonResult Edit(Player player)
{
string conn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
DataTable playerTable = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(conn))
{
sqlConn.Open();
string playerQuery = "UPDATE [dbo].[Player] SET FirstName = @FirstName, LastName = @LastName, HometownCity = @HometownCity, HometownState = @HometownState, Position = @Position " +
"WHERE PlayerKey = @PlayerKey;";
SqlCommand cmd = new SqlCommand(playerQuery, sqlConn);
cmd.Parameters.AddWithValue("@FirstName", player.FirstName.Trim());
cmd.Parameters.AddWithValue("@LastName", player.LastName.Trim());
cmd.Parameters.AddWithValue("@HometownCity", player.HometownCity.Trim());
cmd.Parameters.AddWithValue("@HometownState", player.HometownState.Trim());
cmd.Parameters.AddWithValue("@Position", player.Position.Trim());
cmd.Parameters.AddWithValue("@PlayerKey", player.PlayerKey);
cmd.ExecuteNonQuery();
return Json(playerTable, JsonRequestBehavior.AllowGet);
}
}
public PartialViewResult Edit(Int32 playerKey)
{
string conn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
DataTable playerTable = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(conn))
{
sqlConn.Open();
string playerQuery = "SELECT PlayerKey,FirstName,LastName,HometownCity,HometownState,Position FROM [dbo].[Player] WHERE " +
"PlayerKey = '" + playerKey + "';";
SqlCommand cmd = new SqlCommand(playerQuery, sqlConn);
playerTable.Load(cmd.ExecuteReader());
}
Player player = new Player();
player.PlayerKey = (Int32)playerTable.Rows[0]["PlayerKey"];
player.FirstName = playerTable.Rows[0]["FirstName"].ToString();
player.LastName = playerTable.Rows[0]["LastName"].ToString();
player.HometownCity = playerTable.Rows[0]["HometownCity"].ToString();
player.HometownState = playerTable.Rows[0]["HometownState"].ToString();
player.Position = playerTable.Rows[0]["Position"].ToString();
return PartialView(player);
}
public ActionResult Index()
{
try
{
DataTable schoolTable = new DataTable();
string conn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
#region School/PlayerInfo
string schoolQuery = "SELECT SchoolCode, SchoolShortName FROM [dbo].[School]";
using (SqlConnection sqlConn = new SqlConnection(conn))
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand(schoolQuery, sqlConn);
schoolTable.Load(cmd.ExecuteReader());
}
List<SchoolPlayerList> schoolList = new List<SchoolPlayerList>();
foreach (DataRow schoolRow in schoolTable.Rows)
{
SchoolPlayerList list = new SchoolPlayerList();
School school = new School();
school.SchoolCode = schoolRow["SchoolCode"].ToString().Trim();
school.SchoolShortName = schoolRow["SchoolShortName"].ToString().Trim();
list.school = school;
DataTable playerTable = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(conn))
{
sqlConn.Open();
string playerQuery = "SELECT PlayerKey,SchoolCode,FirstName,LastName,Jersey,Eligibility,HometownCity,HometownState,Height,Weight,Speed,Position FROM [dbo].[Player] WHERE " +
"SchoolCode = '" + schoolRow["SchoolCode"].ToString() + "';";
SqlCommand cmd = new SqlCommand(playerQuery, sqlConn);
playerTable.Load(cmd.ExecuteReader());
}
foreach (DataRow playerRow in playerTable.Rows)
{
Player player = new Player();
player.PlayerKey = (Int32)playerRow["PlayerKey"];
player.SchoolCode = (!playerRow["SchoolCode"].Equals(null) ? playerRow["SchoolCode"].ToString().Trim() : "");
player.FirstName = (!playerRow["FirstName"].Equals(null) ? playerRow["FirstName"].ToString().Trim() : "");
player.LastName = (!playerRow["LastName"].Equals(null) ? playerRow["LastName"].ToString().Trim() : "");
player.Jersey = (!playerRow["Jersey"].Equals(null) ? playerRow["Jersey"].ToString().Trim() : "");
player.Eligibility = (!playerRow["Eligibility"].Equals(null) ? playerRow["Eligibility"].ToString().Trim() : "");
player.HometownCity = (!playerRow["HometownCity"].Equals(null) ? playerRow["HometownCity"].ToString().Trim() : "");
player.HometownState = (!playerRow["HometownState"].Equals(null) ? playerRow["HometownState"].ToString().Trim() : "");
player.Height = (!playerRow["Height"].Equals(null) ? playerRow["Height"].ToString().Trim() : "");
player.Weight = (!playerRow["Weight"].Equals(null) ? playerRow["Weight"].ToString().Trim() : "");
player.Speed = (!playerRow["Speed"].Equals(null) ? playerRow["Speed"].ToString().Trim() : "");
player.Position = (!playerRow["Position"].Equals(null) ? playerRow["Position"].ToString().Trim() : "");
list.playerList.Add(player);
}
schoolList.Add(list);
}
ViewBag.SchoolItems = schoolList;
#endregion
}
catch (Exception ex) { throw ex; }
return View();
}